Téléchargez l'exemple de classeur
Ce didacticiel montrera comment trouver la dernière ligne non vide dans un ensemble de données dans Excel et Google Sheets.
Trouver la dernière ligne avec des données
Il est souvent utile de savoir à quelle ligne se terminent vos données. Si votre plage a ou peut avoir des cellules vides, vous pouvez trouver la dernière ligne non vide en utilisant l'une des méthodes ci-dessous.
Méthode universelle
La première méthode utilise les fonctions ROW et MAX et peut être utilisée avec tout type de données :
| 1 | =MAX((B:B"")*(LIGNE(B:B))) |
Analysons cette formule.
Nous commençons par utiliser un test logique sur la colonne de données. Notre formule examine la colonne entière (B:B) et génère TRUE pour les cellules non vides et FALSE pour les cellules vides.
| 1 | =B:B"" |
La fonction ROW produit le numéro de ligne d'une cellule donnée. Si nous ne lui donnons pas une entrée de cellule spécifique, il donne le numéro de ligne de la cellule dans laquelle il se trouve.
| 1 | =LIGNE() |
En multipliant chaque numéro de ligne par les valeurs correspondantes VRAI (= 1) ou FAUX (= 0), renvoie le numéro de ligne pour une cellule remplie et zéro pour une cellule vide.
| 1 | =E3*F3 |
La fonction MAX donne le maximum d'un ensemble de nombres. Dans cet exemple, étant donné que toutes les cellules vides produisent une valeur nulle, le maximum est le numéro de ligne le plus élevé.
| 1 | =MAX(G3:G12) |
La combinaison de ces étapes nous donne notre formule originale :
| 1 | =MAX((B:B"")*(LIGNE(B:B))) |
Veuillez noter qu'il s'agit d'une formule matricielle, donc si vous utilisez Excel 2022 ou une version antérieure, vous devez appuyer sur CTRL + MAJ + ENTRÉE pour le déclencher.
Méthode pour la plage de texte
Si votre plage (non continue) ne contient que des valeurs de texte et des cellules vides, vous pouvez utiliser une formule moins compliquée contenant les fonctions REPT et MATCH :
| 1 | = MATCH(REPT("z",50),B:B) |
Voyons comment fonctionne cette formule.
Fonction REPT
La fonction REPT répète une chaîne de texte un nombre donné de fois. Pour cet exemple, nous pouvons l'utiliser pour créer une chaîne de texte qui serait la dernière d'une liste triée par ordre alphabétique. Si nous répétons « z » 50 fois, cela devrait fonctionner pour presque toutes les colonnes de données textuelles; aucune entrée ne viendrait avant « zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz » par ordre alphabétique.
| 1 | =REPT("z",50) |
Fonction MATCH
La fonction MATCH trouve une valeur de recherche donnée dans un tableau.
Nous effectuons une recherche de la colonne de données entière pour notre chaîne de texte 50-z. L'omission de l'entrée du type de correspondance dans la fonction MATCH lui indique de trouver une correspondance approximative plutôt qu'exacte.
| 1 | = MATCH(REPT("z",50),B:B) |
La fonction MATCH recherche dans la colonne B et recherche notre chaîne de texte de 50 "z". Comme elle ne la trouve pas, la formule renvoie la position de la dernière cellule non vide. Cette cellule contient la dernière valeur du tableau de recherche qui est inférieure (ou égale à) la valeur de recherche.
Gardez à l'esprit que cette formule ne fonctionne que lorsque votre plage contient exclusivement du texte et des cellules vides (ou au moins la valeur de la dernière cellule n'est pas numérique).
Trouver la dernière ligne avec des données dans Google Sheets
Ces formules fonctionnent exactement de la même manière dans Google Sheets que dans Excel.
