Quelles sont les avantages de passer par une forme "matricielle" ?
Une formule
matricielle est utile quand on veut travailler dans une seule formule sur une plage contigüe de cellules (en général une partie de colonne).
Exemple : chercher dans une plage de valeurs (ex A5:A25) la dernière valeur multiple de 3.
Pour une cellule, c'est facile de savoir si une valeur est multiple de 3. On utilise la fonction MOD :
=MOD(A5;3)=0. Si A5 est multiple de 3, alors l'expression vaut VRAI sinon elle vaut FAUX.
Pour savoir le numéro de ligne, on écrira : SI(MOD(A5;3)=0;LIGNE(A5);"") qui retourne le numéro de la la ligne de A5 si A5 est multiple de 3.
Mais, nous ce qu'on cherche, c'est la dernière valeur de A5:A25 qui soit multiple de 3.
Il faut donc évaluer cette formule pour A5, A6, A7, .. , A25. On aura un tableau d'entier correspondant aux lignes qui contiennent une valeur multiple de 3 parsemé de valeur "" correspondant aux valeurs qui ne sont pas multiples de 3.
Pour indiquer à Excel qu'on veut faire cela pour chaque élément de la plage, on voudrait écrire :
SI(MOD(A5:A25;3)=0;LIGNE(A5:A25);""). Mais Excel ne comprend pas ce qu'on veut de lui. Excel n'évaluera cette formule que pour A5.
En effet MOD ne renvoie qu'une valeur et LIGNE ne renvoie qu'une valeur aussi.
Pour indiquer à Excel qu'on veut en retour, un tableau ou matrice, il faut valider par Ctrl+Maj+Entrée.
Alors, Excel évaluera la formule pour chacune des cellules de A5:A25 et renverra une matrice des résultats du type : {5;"";"";"";9;"";"";"";"";10;11; ... ;"";24;""}
A partir de là, on cherche la plus grande valeur de cette matrice :
GRANDE.VALEUR({5;"";"";"";9;"";"";"";"";10;11; ... ;"";24;""} ; 1) qui retourne 24
On sait donc que 24 est la dernière ligne contenant une valeur multiple de 3 dans la plage A5:A25
Pour trouver cette valeur, on utilisa INDEX : =INDEX (A:A ; 24)
Ce qui donne, avec
validation matricielle:
= INDEX (A:A ; 24)
= INDEX ( A:A ; GRANDE.VALEUR({5;"";"";"";9;"";"";"";"";10;11; ... ;"";24;""} ; 1))
= INDEX ( A:A ; GRANDE.VALEUR( SI(MOD(A5:A25;3)=0;LIGNE(A5:A25);"") ; 1))
Les formules matricielles sont rapidement gourmandes en ressources:
- ne pas les multiplier
- les limiter aux plages strictement nécessaires (ne pas utiliser la plage A:A si A5:A25 suffit)
- certaines fonctions ne sont pas compatibles avec la forme matricielle
Les formules matricielles peuvent la plupart du temps être évitées en utilisant des colonnes auxiliaires.
remarque : dans la formule, on trouve la colonne A:A. Ce n'est pas en contradiction avec la limitation des plages. En effet dans cette formule matricielle, les fonctions concernées par un retour matriciel sont MOD et LIGNE et en aucun cas la fonction INDEX.
Bonnet de nuit