Formule générique
=MATCH(bignum,range)
Sommaire
Pour obtenir la dernière position relative (c'est-à-dire la dernière ligne, la dernière colonne) des données numériques (avec ou sans cellules vides), vous pouvez utiliser la fonction MATCH avec un soi-disant «grand nombre».
Dans l'exemple illustré, la formule en E5 est:
=MATCH(9.99E+307,B4:B9)
Dernière position * relative *, pas de ligne sur la feuille de calcul
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:
Explication
Cette formule utilise la fonction MATCH en mode de correspondance approximative pour localiser la dernière valeur numérique 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 "grand nombre" (parfois abrégé "bignum") qui est intentionnellement plus grand que toute valeur qui apparaîtra dans la plage.
Le résultat est que MATCH "reviendra" à la dernière valeur numérique de la plage et retournera cette position.
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.
À propos de bignum
Le plus grand nombre qu'Excel peut gérer est 9,99999999999999E + 307.
Lorsque vous utilisez MATCH de cette façon, vous pouvez utiliser n'importe quel grand nombre qui est garanti supérieur à toute valeur de la plage, par exemple:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
L'avantage d'utiliser 9.99E + 307 ou similaire, c'est que c'est (1) un nombre énorme et (2) reconnaissable comme un espace réservé pour un "grand nombre". Vous le verrez utilisé dans diverses formules Excel avancées.
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».
