XL 2019 afficher le numéro de ligne de la dernière valeur différente de zéro

mapomme

XLDnaute Barbatruc

Supporter XLD

• 1 Octobre 2022

• #2

Bonsoir @pat66 ;

Si on veut le numéro absolu de ligne (formule matricielle à valider par Ctrl+Maj+Entrée):

VB:

=MAX((E4:E17<>0)*LIGNE(E4:E17))

Si on veut le numéro relatif (ligne n°1= ligne de E4) (formule matricielle à valider par Ctrl+Maj+Entrée):

Code:

=MAX((E4:E17<>0)*LIGNE(E4:E17))-LIGNE(E4)+1

 

Dernière édition: 1 Octobre 2022

Vote positif 0 Vote négatif

P

pat66

XLDnaute Impliqué

• 1 Octobre 2022

• #3

bonsoir mapomme,

j'ai écris ta formule dans le classeur ci joint, mais cela me donne le numéro de la première ligne <>0

=MAX((E4:E17<>0)*LIGNE(E4:E17)) = 4, soit le premier chiffre différent de zéro, ce dont j'ai besoin c'est que la formule affiche 9 car après 15 il y a zéro

merci de votre aide

 

Vote positif 0 Vote négatif

P

pat66

XLDnaute Impliqué

• 1 Octobre 2022

• #4

Houps, désolé je n'avais pas activé formule matricielle, mais n'y aurait pas une autre solution
sans passer par formule matricielle ?
merci

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 1 Octobre 2022

• #5

Bonsoir Pat, Mapomme,
Si les nombres en colonne E sont croissant comme le montre la PJ alors on pet faire :

VB:

=EQUIV(MAX(E:E);E:E;0)

qui donne le N° de ligne.
Pour le contenu de la colonne B correspondant :

Code:

=INDEX(B:B;EQUIV(MAX(E:E);E:E;0))

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 1 Octobre 2022

• #6

Re,
Ou encore avec une fonction perso :

VB:

Function ChercherDernier(N%)
    Dim PL%, DL%, L%
    Application.Volatile
    PL = [E1].End(xlDown).Row: DL = [E65500].End(xlUp).Row
    For L = PL To DL
        If Cells(L, "E") <> 0 And Cells(L + 1, "E") = 0 Then
            Select Case N
                Case 1: ChercherDernier = L
                Case 2: ChercherDernier = Cells(L, "E")
                Case 3: ChercherDernier = Cells(L, "B")
            End Select
        End If
    Next L
End Function

Qui donne au choix la dernière ligne non nulle, la valeur ou la valeur en B.

 

Pièces jointes

• Classeur1 (11).xlsm

  15.3 KB · Affichages: 5

Vote positif 0 Vote négatif

Phil69970

XLDnaute Barbatruc

• 1 Octobre 2022

• #7

Bonjour à tous
Edit : pas vu ta version en vba Sylvain

Une version en vba

Merci de ton retour

@Phil69970

 

Pièces jointes

• Quelle ligne V1.xlsm

  15.3 KB · Affichages: 3

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 1 Octobre 2022

• #8

Bonsoir à tous,

Une fonction plus générique:

= DernierDiffZero ( plageCol ; plageRetour )

Cliquez pour agrandir...

• PlageCol est la plage de recherche
• PlageRetour est la plage où se situe la valeur à retourner

Si plageCol est omis alors on retourne le numéro de ligne.
plageCol peut-être réduit à une seule cellule. En fait, on considère la colonne correspondant à la première cellule de la plage plageCol (voir les exemples dans le fichier).

nota: Bonsoir @Usine à gaz et bonne-nuit

Code de la fonction:

VB:

Function DernierDiffZero(plageCol, Optional plageRetour)
Dim t, x, i&
   t = plageCol.Columns(1)
   If Not IsArray(t) Then x = t: ReDim t(1 To 1, 1 To 1): t(1, 1) = x
   For i = UBound(t) To LBound(t) Step -1
      If t(i, 1) <> "" And t(i, 1) <> 0 Then Exit For
   Next i
   If i < LBound(t) Then DernierDiffZero = CVErr(xlErrNA): Exit Function
   i = i + plageCol.Row - 1
   If IsMissing(plageRetour) Then
      DernierDiffZero = i
   Else
      DernierDiffZero = plageRetour(1, 1).EntireColumn.Cells(i, 1)
   End If
End Function

 

Pièces jointes

• pat66- cherche diff 0- v1.xlsm

  20.6 KB · Affichages: 4

Dernière édition: 2 Octobre 2022

Vote positif 0 Vote négatif

P

pat66

XLDnaute Impliqué

• 2 Octobre 2022

• #9

Bonjour à tous, mapomme, sylvanu, Phil69970

quelle démonstration, je suis toujours ébahi devant tant de connaissances, je n'ai que l'embarras du choix, juste une précision pour sylvanu et sa formule : =INDEX(B:B;EQUIV(MAX(E:E);E:E;0))

La liste de la colonne B est croissante mais malheureusement celle de la Colonne E n'est pas forcément croissante, peut on adapter cette formule ?

merci à tous pour votre générosité et vos compétences

Pat66

 

Dernière édition: 2 Octobre 2022

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 2 Octobre 2022

• #10

sylvanu à dit:

i les nombres en colonne E sont croissant comme le montre la PJ alors on pet faire :

Cliquez pour agrandir...

@sylvanu a bien précisé ci-dessus que les nombres devaient être disposés en ordre croissant pour sa formule sans validation matricielle. Connaissant @sylvanu, s'il avait pu le faire pour un ordre quelconque, il nous l'aurait communiqué depuis longtemps.

Si on veut absolument éviter une formule matricielle (qui est très gourmande en temps de calcul si la colonne E est grande), en utilisant une colonne auxiliaire, on peut y arriver bien souvent. Encore faut-il que vous acceptiez une colonne auxiliaire. J'y réfléchis et revient.

 

Dernière édition: 2 Octobre 2022

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 2 Octobre 2022

• #11

Re,

Voir dans le fichier joint une solution avec une colonne auxiliaire en F.
Personnellement je préfère la solution en N° absolu (formules plus simples)
Bien qu'on multiplie les formules en colonne auxiliaire F, les calculs sont simplissimes pour Excel et très rapides et surtout non matricielles.

nota : Bonjour @sylvanu

 

Pièces jointes

• pat66- cherche diff 0- v2.xlsm

  165.2 KB · Affichages: 6

Dernière édition: 2 Octobre 2022

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 2 Octobre 2022

• #12

Bonjour tout le monde,
Je ne pense pas qu'on puisse couper à l'ajout d'une colonne si on veut éviter formule matricielle ou VBA.
Mais comme la PJ de Pat est un xlsm, je pense qu'un peu de VBA est plus efficace.

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 2 Octobre 2022

• #13

Re,
Pouvez vous vérifier cette PJ, cela semble fonctionner sans matriciel :

VB:

=SOMMEPROD(MAX((E1:E2000>0)*(LIGNE(E1:E2000))))

 

Pièces jointes

• Classeur1 (14).xlsm

  9.1 KB · Affichages: 4

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 2 Octobre 2022

• #14

sylvanu à dit:

Re,
Pouvez vous vérifier cette PJ, cela semble fonctionner sans matriciel :

VB:

=SOMMEPROD(MAX((E1:E2000>0)*(LIGNE(E1:E2000))))

Cliquez pour agrandir...

Le sommeprod est une formule matricielle mais dont seulement la validation matricielle a été contournée.
Ceci a été généralisé en Office 365 où la validation matricielle (détecté par Excel) est implicite.

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 2 Octobre 2022

• #15

mapomme à dit:

Le sommeprod est une formule matricielle

Cliquez pour agrandir...

Exact , je voulais dire sans le fameux Alt+Ctrl+Entére. Mais c'est vrai, c'est intrinsèquement du matriciel.
Mais ça reste très rapide. Je ne pense pas qu'on puisse s'en sortir en formule non matricielle pour analyser une plage.

 

Vote positif 0 Vote négatif