extraire des valeurs d'un tableau sous condition

1. Sélectionner la plage F2:F20
2. Saisir la fonction en F2 ( =sous_1(B2:B20) ).
3. Valider par Ctrl + Maj + Entrée.

job75 à dit:

Bonjour Arais, le fil, le forum,

C'est en effet une question de ressources mémoire et de temps de calcul.

Les formules matricielles (y compris celle de Roger2327) sont gourmandes de l'un et/ou de l'autre si elles sont appliquées à un grand nombre de cellules et/ou si elles travaillent sur des plages de grande dimension.

Une procédure Sub avec boucles en VBA, bien construite comme celle de Lii, n'a pas ces inconvénients.

A+

Cliquez pour agrandir...

1. Je suis incapable de dire exactement ce qu'il en est de l'appétit de ressources des différentes solutions. Les formules matricielles semblent assez gourmandes. Dire si les fonctions intégrées ou les fonctions personnalisées sont les plus gourmandes échappent à ma compétence.
2. Il m'est plus facile de faire des constatations sur le temps d'exécution.
   En fait, le temps d'exécution dépend beaucoup de l'environnement (ce n'est pas une découverte). Dans le cas présent, j'ai testé les formules et la procédure de Lii.
   • Dans un classeur ne contenant rien d'autre que les données et la procédure ou une fonction, tout autre classeur étant fermé, les diverses solutions sont rapides. Les formules (de job75 ou la mienne) étant légèrement plus rapides que la procédure de Lii.
   • Dans les mêmes conditions, mais avec un classeur contenant quelques formules et deux graphiques ouvert en arrière plan, les formules restent performantes, alors que les performances de la procédure se dégradent.
   • J'ai donc refermé ce classeur "perturbateur" et j'ai regardé ce qui se passait avec quelques milliers de données qu'on trouvera dans le classeur joint. Là encore les formules se comportent bien. La procédure est assez rapide si le nombre de "1" est faible dans le tableau de données.
     J'ai donc mis une colonne de quatre mille données toutes égales à 1. La procédure devient lente dans ce cas extrême.
     Pour corser le tout j'ai ajouté des formules (colonnes A, E et J du classeur joint) et là, la procédure est très lente.
     Dans le cas où les données comportent moins de valeurs 1, la procédure va plus vite, et d'autant plus vite qu'il y a moins de valeurs 1.
     Dans tous les cas évoqués ci-dessus les formules sont peu sensibles à la variation du nombre de valeurs 1.
3. En analysant la procédure de Lii, j'ai constaté qu'elle écrivait les valeurs trouvées une à une, déclenchant à chaque fois le rafraîchissement de tout l'affichage. Les formules sont recalculées des milliers de fois, sans parler des éventuelles procédures événementielles qui peuvent se trouver dans l'environnement. Je propose donc une autre procédure (dérivée de la fonction que j'ai proposé plus haut) travaillant sur des tableaux internes et provoquant beaucoup moins de rafraîchissements.
   Code:

   [COLOR="DarkSlateGray"]Sub sous()
   Dim oPlg, nPlg As Long, tDat, nDat As Long, nLig As Long, nCol As Long, nOff As Long
   Dim t As Single [COLOR="SeaGreen"]'***[/COLOR]
      nOff = 8 [COLOR="SeaGreen"]' Paramètre.[/COLOR]
      nLig = 5 [COLOR="SeaGreen"]' Paramètre.[/COLOR]
      With ActiveSheet
      For nCol = 2 To 4 [COLOR="SeaGreen"]' Paramètres.[/COLOR]
         t = Timer [COLOR="SeaGreen"]'***[/COLOR]
         nDat = 0
         oPlg = .Range(.Cells(nLig, nCol), .Cells(.Rows.Count, nCol).End(xlUp)).Value [COLOR="SeaGreen"]' Données[/COLOR]
         ReDim tDat(1 To 1, 1 To 1)
         For nPlg = 1 To -1 + UBound(oPlg, 1)
   [COLOR="SeaGreen"]' Au choix, une seule des trois lignes suivantes.
   '         If oPlg(nPlg, 1) = 1 And Not IsEmpty(oPlg(nPlg + 1, 1)) Then
   '         If oPlg(nPlg, 1) = 1 And oPlg(nPlg + 1, 1) <> "" Then[/COLOR]
            If oPlg(nPlg, 1) = 1 Then
               nDat = 1 + nDat
               tDat(1, nDat) = oPlg(nPlg + 1, 1)
               ReDim Preserve tDat(1 To 1, 1 To 1 + nDat)
            End If
         Next nPlg
         .Cells(nLig, nOff + nCol) = " " [COLOR="SeaGreen"]' Pour le cas où la colonne serait vide.[/COLOR]
         .Range(.Cells(nLig, nOff + nCol), .Cells(.Rows.Count, nOff + nCol).End(xlUp)).ClearContents
         .Cells(nLig, nOff + nCol).Resize(UBound(tDat, 2), 1) = Application.Transpose(tDat)
         .Cells(1, nOff + nCol).Value = Int(100 * (Timer - t) + 0.5) / 100 [COLOR="SeaGreen"]'***[/COLOR]
      Next nCol
      End With
   End Sub[/COLOR]

   Elle est associée au bouton ROGER dans le classeur joint.
   Les lignes marquées '*** servent au chronométrage pour le test. Elles doivent être supprimées en usage normal.
   Les deux lignes "If..." mises en commentaire sont des alternatives permettant d'affiner la gestion des cellules vides ou contenant une chaîne vide ("").