Formule générique
=MATCH(bigtext,range)
Sommaire
Pour obtenir la dernière position relative (c'est-à-dire la dernière ligne, la dernière colonne) des données textuelles (avec ou sans cellules vides), vous pouvez utiliser la fonction MATCH. Dans l'exemple illustré, la formule en D5 est:
=MATCH(REPT("z",255),B4:B11)
Explication
Cette formule utilise la fonction MATCH en mode de correspondance approximative pour localiser la dernière valeur de texte dans une plage. Correspondance approximative activée en définissant le 3ème argument dans MATCH sur 1 ou en omettant cet argument, qui vaut par défaut 1.
La valeur de recherche est un soi-disant "gros texte" (parfois abrégé "bigtext") qui est intentionnellement une valeur "plus grande" que toute valeur qui apparaîtra dans la plage. Lorsque vous travaillez avec du texte, qui trie par ordre alphabétique, cela signifie une valeur de texte qui apparaîtra toujours à la fin de l'ordre de tri alphabétique.
Puisque cette formule correspond au texte, l'idée est de construire une valeur de recherche qui ne se produira jamais dans le texte réel, mais qui sera toujours la dernière. Pour ce faire, nous utilisons la fonction REPT pour répéter la lettre «z» 255 fois. Le nombre 255 représente le plus grand nombre de caractères autorisé par MATCH dans une valeur de recherche.
Lorsque MATCH ne peut pas trouver cette valeur, il "revient" à la dernière valeur de texte de la plage et renvoie la position de cette valeur.
Remarque: cette approche fonctionne correctement avec les cellules vides de la plage, mais n'est pas fiable avec des données mixtes comprenant à la fois des nombres et du texte.
Dernière position relative par rapport au numéro de la dernière ligne
Lors de la création de formules avancées qui créent des plages dynamiques, il est souvent nécessaire de déterminer le dernier emplacement des données dans une liste. Selon les données, cela peut être la dernière ligne avec des données, la dernière colonne avec des données ou l'intersection des deux. Remarque: nous voulons la dernière position relative dans une plage donnée, pas le numéro de ligne sur la feuille de calcul:
Plage dynamique
Vous pouvez utiliser cette formule pour créer une plage dynamique avec d'autres fonctions telles que INDEX et OFFSET. Voir les liens ci-dessous pour des exemples et des explications:
- Plage dynamique avec INDEX et COUNTA
- Plage dynamique avec OFFSET et COUNTA
L'inspiration pour cet article est venue de l'excellent livre de Mike Girvin Control + Shift + Enter, où Mike explique le concept de «dernière position relative».
