Cédric a besoin de déterminer la valeur de l’avant-dernière cellule de la colonne à laquelle il ajoute régulièrement des valeurs. Il a vu des formules pour saisir la dernière valeur, mais pas l’avant-dernière.

Il existe de nombreuses façons de répondre à ce besoin. À bien des égards, la méthode que vous choisissez dépendra des caractéristiques des données de la colonne. Supposons que vos données se trouvent dans la colonne A, qu’elles soient numériques et qu’il n’y ait aucun espace vide dans aucune des cellules de la colonne. Dans ce cas, vous pouvez utiliser la formule suivante:

=INDEX(A:A,COUNT(A:A)-1)

Notez que cette formule ne renverra pas le résultat correct s’il y a des valeurs de texte dans la colonne A, même si cette valeur de texte est dans un en-tête. Si vous souhaitez compenser le titre, vous pouvez modifier la formule de cette manière. (Notez qu’il ne vérifie que la plage de A2: A1000. Si vos données dépassent la ligne 1 000, vous devrez modifier la plage spécifiée.)

=INDEX(A2:A1000,COUNT(A2:A1000)-1)

Si vos données numériques peuvent contenir des cellules vides, alors l’une des formules suivantes fonctionnera parfaitement:

=OFFSET(INDIRECT("A"&MATCH(9^9,A:A)),-1,0)

=INDEX(A:A,MATCH(9.9E+23,A:A,1)-1,1)

=INDEX(A:A,(MATCH(LOOKUP(10000,A:A),A:A)-1),1)

=INDEX(A:A,AGGREGATE(14,6,ROW(A:A)*A:A/A:A,2))

Vous devez reconnaître que si l’avant-dernière cellule de la colonne est vide, ces formules renverront 0. (Enfin, sauf pour la formule qui utilise la fonction AGGREGATE. Elle renvoie la valeur de la première cellule non vide avant l’avant-dernière cellule.)

Si vos données dans la colonne A peuvent contenir des nombres ou du texte (mais toujours pas de cellules vides), vous avez besoin d’une formule différente. L’un ou l’autre fera:

=INDEX(A:A,COUNTA(A:A)-1,1)

=INDIRECT("A"&COUNTA(A:A)-1)

S’il peut y avoir des cellules vides dans vos données, vous pouvez utiliser l’une des méthodes suivantes:

=INDEX(A:A,MATCH(LOOKUP(2,1/(A:A<>""),A:A),A:A,0)-1)

=INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))-1)

Encore une fois, si l’avant-dernière cellule est vide, la formule renvoie 0.

Enfin, vous pouvez également utiliser le type de formule suivant, à condition que vos cellules de la colonne utilisent une plage nommée (dans ce cas MyData).

N’oubliez pas, cependant, que lorsque vous ajoutez plus d’informations à la colonne, vous devez vous assurer que la plage nommée inclut les données ajoutées.

=INDEX(MyData,ROWS(MyData)-1)

Vous devez noter que pratiquement toutes les formules mentionnées dans cette astuce utilisent un -1. Cela indique que vous ne voulez pas de la dernière cellule de la plage, mais de l’avant-dernière (« une en haut » à partir du bas de la plage).

Si vous souhaitez un décalage différent à partir du bas de la plage, vous pouvez modifier cette partie de chaque formule. Par exemple, si vous vouliez 2 cellules en haut à partir du bas de la plage, vous changeriez toutes les instances de -1 à -2.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (1523) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.