extraire des valeurs d'un tableau sous condition

[Arais]

Bonjour à tous,

Je me casse le bec depuis pas mal de temps sur une formule qui permettrait d' obtenir le résultat suivant :

Tableau source :

16
1
3
52
90
1
67
2
1
9


Tableau recherché :

3
67
9

(voir dans le fichier Excel joint les vrais tableaux en exemple)

Je cherche en fait à lister toutes les valeurs qui suivent la valeur "1" dans mon tableau source.
J'imagine qu'il faut en passer par une formule matricielle, si vous voyez une macro qui peut fonctionner je prends aussi (j'y connais pas grand chose).

Dans le doc Excel joint vous verrez que je cherche à appliquer cette formule sur plusieurs colonnes avec des nombres de lignes variables.


Voilà, si cela peut inspirer des esprits curieux et surtout vifs et avisés, eh bien ce serait une aide précieuse, parce que là, je sèche.

Sur ce bonne soirée à tous,
et vive Excel !


PS : maintenant que j'arrive à décrire mon problème je souhaite modifier le titre de ce topic car il n'est pas très transparent, mais j'ai bien l'impression que c'est impossible.
Le titre aurait été "extraire des valeurs d'un tableau sous condition"

 

[ROGER2327]

Re : Défi pour les pros des fonctions Excel

Bonjour Arais

Acceptez-vous une fonction matricielle ?
Si oui, en attendant les "pros" :

[COLOR="DarkSlateGray"]Function sous_1(plg As Range)
[COLOR="SeaGreen"]' *********************** Fonction matricielle ***********************
' L'argument est un plage unicolonne contenant au moins deux cellules.[/COLOR]
Dim oCel As Range, tDat, nDat As Long
   Application.Volatile
   ReDim tDat(1 To plg.Rows.Count, 1 To 1)
   For Each oCel In plg.Resize(plg.Rows.Count - 1, 1).Cells
      If oCel.Value = 1 And Not IsEmpty(oCel.Offset(1, 0).Value) Then
         nDat = nDat + 1
         tDat(nDat, 1) = oCel.Offset(1, 0).Value
      End If
   Next oCel
   For nDat = 1 + nDat To UBound(tDat): tDat(nDat, 1) = "": Next nDat
   sous_1 = tDat
End Function[/COLOR]

ROGER2327
#1807

 

[Tibo]

Re : Défi pour les pros des fonctions Excel

Bonjour Arais, salut Roger,

Un essai avec une formule matricielle en F5 :

=SI(LIGNE()-4>SOMME(SI((B$5:B$23=1)*1;1));"";INDEX(B$1:B$23;
PETITE.VALEUR(SI((B$1:B$23=1);(B$1:B$23=1)*LIGNE($B$1:$B$23));
LIGNE()-4)+1))

Formule matricielle à valider par CTRL + MAJ + ENTREE

@+

 

[Tibo]

Re : Défi pour les pros des fonctions Excel

re,

Pour ton 2ème onglet, une autre formule matricielle en F4 assez proche de la tienne :

=SI(LIGNE()-3>NB(B$4:B$27);"";INDEX(B$1:B$27;PETITE.VALEUR(SI((B$4:B$27<>"");
(B$4:B$27<>"")*LIGNE($B$4:$B$27));LIGNE()-3)))

@+

 

[job75]

Re : Défi pour les pros des fonctions Excel

Bonjour Arais, Roger, Tibo,

Une variante en F5 :

=SI(LIGNES(F$5:F5)>NB.SI(B:B;1);"";INDEX(B:B;PETITE.VALEUR(SI(B$1:B$22=1;LIGNE(B$2:B$23));LIGNES(F$5:F5))))

Formule matricielle à valider par Ctrl+Maj+Entrée.

Pour le 2ème onglet, formule matricielle en F4:

=SI(LIGNES(F$4:F4)>NBVAL(B$4:B$27);"";INDEX(B:B;PETITE.VALEUR(SI(B$1:B$27<>"";LIGNE(B$1:B$27));LIGNES(F$4:F4))))

Edit : j'ai supposé que les cellules au-dessus du tableau sont toujours vides...

A+

 

[ROGER2327]

Re : Défi pour les pros des fonctions Excel

Bonjour Arais, Tibo, job75

Pour la deuxième feuille :

[COLOR="DarkSlateGray"]Function horreur_du_vide(plg As Range)
[COLOR="SeaGreen"]' *********************** Fonction matricielle ***********************
' L'argument est un plage unicolonne contenant au moins deux cellules.[/COLOR]
Dim oCel As Range, tDat, nDat As Long
   Application.Volatile
   ReDim tDat(1 To plg.Rows.Count, 1 To 1)
   For Each oCel In plg.Resize(plg.Rows.Count, 1).Cells
      If Not IsEmpty(oCel.Value) Then
         nDat = nDat + 1
         tDat(nDat, 1) = oCel.Value
      End If
   Next oCel
   For nDat = 1 + nDat To UBound(tDat): tDat(nDat, 1) = "": Next nDat
   horreur_du_vide = tDat
End Function[/COLOR]

(Evidemment matricielle.)​

ROGER2327
#1808

 

[PascalXLD]

Re : extraire des valeurs d'un tableau sous condition

Bonjour

Titre modifié

 

[Arais]

Re : extraire des valeurs d'un tableau sous condition

Bonjour ROGER2327, Tibo, job75,

Merci à tous pour vos contributions, et merci à PascalXLD (et bonjour) pour le changement de titre du topic.

J'ai testé toutes vos fonctions matricielles, je n'ai rencontré aucun problème avec celles de Tibo et job75, ça fonctionne du feu de Dieu. Un grand merci !

En revanche je n'arrive pas à utiliser tes fonctions matricielles définies par macro, ROGER2327, pour qu'elles fonctionnent comme je l'aurais souhaité. Je joins donc à ce post un doc Excel avec l'utilisation que j'en ai faite, qui n'est peut-être pas la bonne.

Bonne journée à tous,
ça fait du bien un vrai bon forum.

 

[ROGER2327]

Re : extraire des valeurs d'un tableau sous condition

Re...

Il s'agit de véritables formules matricielles : pour les utiliser, par exemple dans la plage F2:F20, il faut, dans l'ordre :

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

Voyez le résultat dans le classeur joint.​

ROGER2327
#1813

 

[Arais]

Re : extraire des valeurs d'un tableau sous condition

Merci ROGER2327,

Effectivement je ne savais pas comment l'utiliser. Je vous remercie pour toutes vos indications, ainsi que pour cette macro. Maintenant j'ai compris le principe de sélectionner la plage en premier.

Merci encore à Tibo et job75 également !

Bon week-end à tous,
Arais

 

[Lii]

Re : extraire des valeurs d'un tableau sous condition

Bonsoir,

avec de simples macros (pour éviter d'écrire les formules).
A noter que la dernière, celle qui supprime les cellules vides, garde les formats des autres.

 

[Arais]

Re : extraire des valeurs d'un tableau sous condition

Merci Lii,
et bonsoir,

Cette macro relève de la performance pour moi qui n'ai jamais touché au VB !
Donc un énorme merci à tous pour vos contributions. Je ne pensais pas avoir autant de solutions différentes pour ce cas.

Du coup ceci m'amène une nouvelle question, entre ces 3 manips :

• la commande bouton VB de Lii
• la fonction matricielle VB de ROGER2327
• les formules matricielles de Tibo et job75

Laquelle est la moins gourmande en ressource a priori, si je devais appliquer une de ces solutions à un tableau de centaines de milliers de cellules ?

Je vais de toutes manières les tester toutes à plus grande échelle, donc je verrais bien celle qui est la plus rapide, par contre je ne saurais pas dire pourquoi telle solution est plus ou moins gourmande que telle autre. Si votre expérience peut éclairer ma lanterne... je ne connais pas suffisamment le fonctionnement d'Excel.

Excellente soirée/nuit à tous,

Arais

 

[job75]

Re : extraire des valeurs d'un tableau sous condition

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+

 

[ROGER2327]

Re : extraire des valeurs d'un tableau sous condition

Bonjour à tous

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+

Je viens de me pencher (longuement !) sur les remarques de job75 en explorant diverses situations. Je constate finalement quelques petites choses :

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.

   [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 ("").

Le classeur joint permet la comparaison des procédures. Les fonctions en sont absentes, pour alléger le classeur.
Les colonnes A, E et J contiennent des formules "parasites" : en les modifiant, les effaçant en tout ou partie, ou les remplaçant par d'autres, on peut se rendre compte de leur influence sur le comportement des procédures.
Vos avis m'intéressent...​

ROGER2327
#1820

 

[Lii]

Re : extraire des valeurs d'un tableau sous condition

RE

...

1. 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.

...

Il est vrai que je n'ai pas cherché la rapidité.
Pour gagner un peu de temps, j'ajouterais des lignes du style :

Application.ScreenUpdating = False
 Application.Calculation = xlManual
Application.EnableEvents = False

surtout la deuxième (avec la fonction ALEA).
Mais tu as raison, Roger. En terme de rapidité, il vaut mieux passer par des tableaux !