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

[pat66]

Bonsoir le forum,
j'ai besoin d'un coup de main car je n'arrive pas à récupérer le numéro de la dernière ligne différente de zéro, je vous ai fait un exemple ci dessous ou aussi dans la pièce jointe, dans cet exemple le numéro de ligne 5 doit s'afficher puisque la ligne 6 est à zéro
merci de votre aide
1 0
2 10
3 11
4 12
5 13
6 0
7 0

bonne soirée

 

[mapomme]

Bonsoir @pat66 😉;

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

=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):

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

 

[pat66]

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

 

[pat66]

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

 

[sylvanu]

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

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

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

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

 

[sylvanu]

Re,
Ou encore avec une fonction perso :

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.

 

[Phil69970]

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

Une version en vba

Merci de ton retour

@Phil69970

 

[mapomme]

Bonsoir à tous,

Une fonction plus générique:

= DernierDiffZero ( plageCol ; plageRetour )

• 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:

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

🙂

 

[pat66]

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

 

[mapomme]

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

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

 

[mapomme]

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 🙂

 

[sylvanu]

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.

 

[sylvanu]

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

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

 

[mapomme]

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

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

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.

 

[sylvanu]

Le sommeprod est une formule matricielle

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.