Bien que n'ayant pas encore utilisé cette fonction dans un vrai cas de figure, je voudrais apporter quelques éléments de réflexion qu'il me semble capital de maitriser.
Dans la très grande majorité des cas, utiliser des tableaux structurés est beaucoup plus avantageux et lisible (le nom du tableau et de la colonne sont automatiquement dans la formule).
RECHERCHEX et INDEX/EQUIV
Donc, SUPPR.PLAGE et .:. réduisent la plage de travail, mais si on travaille sur deux colonnes devant avoir le même nombre de lignes, on prend un gros risque.
Par exemple...
Pour la première formule, la valeur qu'on aurait attendu serait 0 (ou vide avec & "").
Pour la deuxième formule, la valeur retournée est #VALEUR, ce qui est logique (mais pas forcément attendu) puisque les deux plages dans recherchex n'ont pas le même nombre de lignes.
Les troisièmes et quatrièmes formules illustrent le même problème, mais avec la combinaison INDEX/EQUIV. On voit d'ailleurs comment la dernière formule renvoie une valeur carrément fausse.
Les cellules de fin ou début de plage contenant un espace ne sont pas "supprimées" avec Suppr.Plage.
Par exemple, j'ai ici ajouté un espace en B5 et remis le 1 en A1.
Regardez l'impact sur les formules.
Ou bien encore cet espace ajouté malencontreusement en B9 qui engendre quelques subtilités.
Subtilité 1 : les cellules de fin ou début de plage contenant un espace ne sont pas "supprimées" avec Suppr.Plage.
Subtilité 2 : Lorsqu'une référence dynamique rencontre des cellules vides, elle va les transformer en zéro. Bien ou pas bien, je ne sais pas, mais il faut juste en être conscient. On a un exemple en C1, qui va retourner les cellules vides avec des zéros.
Subtilité 3 : Ensuite, vous pouvez vous amuser avec les fonctions NB et NBVAL pour comprendre les impacts et les utiliser à bon escient en fonction de ce que vous voulez retourner !
La fonction NBVAL compte le nombre de cellules qui ne sont pas vides dans une plage. Donc 5 (A,B,C,D,Espace) pour NBVAL(A.:.A) et 9 (on ajoute les 4 zéros) pour NBVAL(C1#).
La fonction NB() retourne les valeurs numériques.
NB(A.:.A) retourne donc 0 valeurs numériques. NB(C1#) retourne 4 pour la plage dynamique propagée réduite de A:A avec un remplacement des cellules vides par un zéro - donc du numérique. Argh !
Mini bonus : =LIGNES(A.:.A) renvoit bien 9. Ouf !
En conclusion, j'aime beaucoup avoir cette nouvelle fonction pour des cas tordus, mais pour le moment rien ne remplace un bon tableau structuré en raison de ces effets de bord !
L'occasion de relire ce post