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

pat66

XLDnaute Impliqué
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
 

Pièces jointes

  • Classeur1.xlsm
    8.3 KB · Affichages: 14
Solution
Bonjour,

Sylvanu, vous avez raison chaque mot a son importance, quoi qu'il en soit grâce a votre aide mon problème est résolu, voici les 2 solutions que je retiens pour ce problème :

sylvanu =INDEX(B1:B18;SOMMEPROD(MAX((E1:E18>0)*(LIGNE(E1:E18))))) (ou <>0)
et
Hasco =AGREGAT(14;6;B4:B18/(E4:E18<>0);1)

J'ai noté toutes les autres formules, matricielles ou pas, dans un classeur, cela me servira certainement à l'avenir

Un grand merci à tous pour tous ces conseils et en particulier à sylvanu et à Hasco qui ma fait découvrir AGREGAT et toutes ces fonctions, c'est pour cette raison que je la coche comme solution

pat66

mapomme

XLDnaute Barbatruc
Supporter XLD
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:

pat66

XLDnaute Impliqué
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
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
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

mapomme

XLDnaute Barbatruc
Supporter XLD
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:
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:

pat66

XLDnaute Impliqué
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:

mapomme

XLDnaute Barbatruc
Supporter XLD
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.
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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:

Discussions similaires

Réponses
5
Affichages
324
Réponses
7
Affichages
485

Statistiques des forums

Discussions
313 281
Messages
2 096 784
Membres
106 748
dernier inscrit
Abdel93