Formule générique
(=MATCH(2,1/(range"")))
Sommaire
Pour obtenir la dernière position relative (c'est-à-dire la dernière ligne, la dernière colonne) des données mixtes qui peuvent contenir des cellules vides, vous pouvez utiliser la fonction MATCH comme décrit ci-dessous.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Dans l'exemple illustré, la formule en E5 est:
(=MATCH(2,1/(B4:B10"")))
Dernière position * relative *, pas de ligne sur la feuille de calcul
Lors de la construction de formules plus avancées, 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. Nous voulons la dernière * position relative * à l'intérieur d'une plage donnée et non le numéro de ligne sur la feuille de calcul:
Explication
Cette formule utilise la fonction MATCH configurée pour trouver la position de la dernière cellule non vide dans une plage.
En travaillant de l'intérieur vers l'extérieur, le tableau de recherche à l'intérieur de MATCH est construit comme ceci:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Remarque: toutes les valeurs du tableau sont 1 ou # DIV / 0! Erreur.
MATCH est alors réglé pour correspondre à la valeur 2 en "mode de correspondance approximative", en omettant le 3ème argument est omis.
Comme la valeur de recherche de 2 ne sera jamais trouvée, MATCH trouvera toujours le dernier 1 dans le tableau de recherche, qui correspond à la dernière cellule non vide.
Cette approche fonctionnera avec tout type de données, y compris les nombres, le texte, les dates, etc. Elle fonctionne également avec des chaînes de texte nulles renvoyées par des formules comme celle-ci:
=IF(A1<100,"")
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 fait un excellent travail en expliquant le concept de "dernière position relative".
