Téléchargez l'exemple de classeur
Ce didacticiel vous apprendra à rechercher la dernière valeur en colonne ou en ligne dans Excel.
Dernière valeur dans la colonne
Vous pouvez utiliser la fonction LOOKUP pour trouver la dernière cellule non vide d'une colonne.
| 1 | =RECHERCHE(2,1/(B:B""),B:B) |
Parcourons cette formule.
La partie de la formule B:B”” renvoie un tableau contenant les valeurs True et False : {FALSE, TRUE, TRUE,… }, en testant chaque cellule de la colonne B est vide (FALSE).
| 1 | =RECHERCHE(2,1/({FAUX;VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;… ),B:B) |
Ces valeurs booléennes sont converties en 0 ou 1 et sont utilisées pour diviser 1.
| 1 | =RECHERCHE(2,{#DIV/0!;1;1;1;1;1;#DIV/0!;,B:B) |
C'est le lookup_vector pour la fonction LOOKUP. Dans notre cas, la valeur lookup_value est 2, mais la plus grande valeur dans lookup_vector est 1, donc la fonction LOOKUP correspondra au dernier 1 du tableau et renverra la valeur correspondante dans result_vector.
Si vous êtes sûr de n'avoir que des valeurs numériques dans votre colonne, vos données commencent à partir de la ligne 1 et votre plage de données en continu, vous pouvez utiliser une formule légèrement plus simple avec les fonctions INDEX et COUNT.
| 1 | =INDICE(B:B,COUNT(B:B)) |
La fonction COUNT renvoie le nombre de cellules remplies de données dans la plage continue (4) et la fonction INDEX donne ainsi la valeur de la cellule dans cette ligne correspondante (4ème).
Pour éviter d'éventuelles erreurs lorsque votre plage de données contient un mélange de valeurs numériques et non numériques, ou même des cellules vides, vous pouvez utiliser la fonction RECHERCHE avec les fonctions ISBLANK et NOT.
| 1 | =RECHERCHE(2,1/(PAS(ISBLANK(B:B))),B:B) |
La fonction ISBLANK renvoie un tableau contenant les valeurs True et False, correspondant aux 1 et aux 0. La fonction NOT change True (c'est-à-dire 1) en False et False (c'est-à-dire 0) en True. Si nous inversons ce tableau résultant (en divisant 1 par ce tableau), nous obtenons un tableau résultat contenant à nouveau #DIV/0 ! erreurs et 1, qui peuvent être utilisés comme tableau de recherche (lookup_vector) dans notre fonction LOOKUP. La fonctionnalité de la fonction LOOKUP est alors la même que dans notre premier exemple : elle renvoie la valeur du vecteur résultat à la position du dernier 1 dans le tableau de recherche.
Lorsque vous avez besoin du numéro de ligne avec la dernière entrée à renvoyer, vous pouvez modifier la formule utilisée dans notre premier exemple avec la fonction ROW dans votre result_vector.
| 1 | =RECHERCHE(2,1/(B:B""),LIGNE(B:B)) |
Dernière valeur de la ligne
Pour obtenir la valeur de la dernière cellule non vide d'une ligne remplie de données numériques, vous pouvez utiliser une approche similaire mais avec des fonctions différentes : la fonction OFFSET avec les fonctions MATCH et MAX.
| 1 | =OFFSET(Référence, Lignes, Colonnes) |
| 1 | =OFFSET(B2,0,MATCH(MAX(B2:XFD2)+1,B2:XFD2,1)-1) |
Voyons comment fonctionne cette formule.
Fonction MATCH
Nous utilisons la fonction MATCH pour « compter » combien de valeurs de cellules sont inférieures à 1 + le maximum de toutes les valeurs de la ligne2 à partir de B2.
| 1 | = MATCH(lookup_value, lookup_array, [match_type]) |
| 1 | = CORRESPONDANCE(MAX(B2:XFD2)+1,B2:XFD2,1) |
La valeur lookup_value de la fonction MATCH est le maximum de toutes les valeurs de la ligne2 + 1. Étant donné que cette valeur n'existe évidemment pas dans la ligne2 et que match_type est défini sur 1 (inférieur ou égal à lookup_value), la fonction MATCH renverra le la position de la dernière cellule « vérifiée » dans le tableau, c'est-à-dire le nombre de cellules remplies de données dans la plage B2:XFD2 (XFD est la toute dernière colonne dans les nouvelles versions d'Excel).
Fonction OFFSET
Ensuite, nous utilisons la fonction OFFSET pour obtenir la valeur de cette cellule, dont la position a été renvoyée par la fonction MATCH.
| 1 | =DÉCALAGE(B2,0,C4-1) |
