Les formules matricielles
La grande majorité des fonctions d’Excel affiche leur résultat dans une seule formule. Mais certaines fonctions requièrent plusieurs cellules pour afficher les résultats demandés. Cette étendue de cellules ayant des résultats est appelée une « matrice ». Donc, les formules dont le résultat s’étend sur plusieurs cellules s’appellent des fonctions matricielles. Retournez voir les fonctions Droitereg(), Fréquence(), et Transpose() pour avoir des exemples.
Afin de s’exécuter correctement, il faut utiliser la combinaison de touches CTRL + MAJ + ENTRÉE. Sinon, le résultat ne sera pas une matrice. Aussi, vous allez remarquer que la formule matricielle en encadré par { et }.
En plus des fonctions mentionnées précédemment, il est encore possible d’utiliser d’autres fonctions et de la combiner pour créer des formules matricielles. Cela répond à des besoins que les autres fonctions ne peuvent pas répondre. Vous allez trouver des exemples ci-dessous ainsi que comment utiliser les opérateurs logiques Et et Ou dans des formules matricielles.
Des vidéos de formations sont disponibles sous les liens suivants :
Fonctions matricielles – partie 1 :
http://www.youtube.com/watch?v=vO_nLeonyXU .
Excel – fonctions matricielles – partie 2 :
http://www.youtube.com/watch?v=CXjNurKPSIs .
Excel fonctions matricielles – partie 3 : http://www.youtube.com/watch?v=f4hwl_fH1Fs .
Vous venez de voir et d’appliquer les fonctions de ce manuel. Vous pouvez devenir encore plus performant en appliquant des formules matricielles. L’un des grands avantages de ces formules est qu’elles combinent des fonctions et analyse une masse de données en prenant très peu d’espace dans votre chiffrier.
Excel utilise le terme « matrice » pour décrire une étendue de cellules inséparables. Toute l’étendue est requise pour trouver la solution. Le logiciel va reconnaître une formule matricielle seulement si vous la validez en utilisant les touches Ctrl, MAJ (Shift) et Entrée. L’application va vous retourner le message d’erreur #VALEUR si vous appuyez seulement sur la touche Entrée. Pour vous démontrer qu’il s’agit d’une formule matricielle, Excel va placer des accolades ( { } ) devant et après celle-ci. Par exemple : {=somme(si(A1:A5>= "c";B1:B5))} . N’essayez pas de placer des accolades par vous-même. Excel va les placer lorsque vous validerez une formule matricielle avec les touches Ctrl, MAJ (Shift) et Entrée.
Les formules avec un critère
Les formules matricielles sont souvent utilisées pour trouver un résultat sur une masse de données que l’on doit premièrement filtrer. Pour le prochain exemple, il faut trouver la somme des lignes où le texte de la première colonne est de « c » ou plus grand.
|
|
- Placez le pointeur dans la cellule B7
- Entrez la formule : =somme(si(A1:A5>="c";B1:B5))
- Validez la formule en utilisant les touches Ctrl, MAJ (Shift) et Entrée.
Le résultat sera de 12. Il faut maintenant expliquer cette formule. Vous connaissez bien les fonctions =somme() et =si(). Dans une formule matricielle, on applique le critère sur une étendue de cellules (A1 :A5) au lieu d’une seule cellule à la fois. Si le résultat d’une ligne est vrai, le chiffre de la ligne du bloc de B1 à B5 sera ajouté à la somme recherchée. Dans cet exemple, les lignes 3, 4 et 5 répondent au critère (>= « c »). La formule va donc chercher les chiffres de ces mêmes lignes de la colonne B (3, 4, et 5) pour les additionner. Cette formule fait donc la somme de chiffres tout en filtrant les données.
Vous pourriez recréer ce résultat sans utiliser de formules matricielles. Il faudrait ajouter une formule =si(Ax>= « c »;Bx;0) pour les lignes 1 à 5. Ensuite, il faut créer une formule qui utilise le fonction somme pour additionner les résultats de la colonne avec les formules =si(). Pour ce petit exemple, il faudrait prendre une colonne et 6 cellules de plus pour arriver au même résultat que la formule matricielle!
La fonction =somme.si() aurait aussi donné le résultat voulu avec la formule =SOMME.SI(A1:A5;">=c";B1:B5) . Mais cette fonction n’aurait pas aidé si on désirait utiliser d’autres fonctions. Voici une courte liste de formules matricielles qui utilisent d’autres fonctions.
=MOYENNE(SI(A1:A5>="C";B1:B5))
=MIN(SI(A1:A5>="C";B1:B5))
=MAX(SI(A1:A5>="C";B1:B5))
=MEDIANE(SI(A1:A5>="C";B1:B5))
Les formules avec plusieurs critères
Il est aussi possible de créer des formules matricielles qui utilisent plusieurs critères. Mais avant de vous montrer la formulation requise, vous devez savoir que le résultat d’un critère qui donne VRAI équivaut au chiffre 1. Tout résultat de critère qui est FAUX équivaut à 0. Ceci est important parce que le principe pour filtrer les données consiste à multiplier les résultats des critères aux données. Si l’un des critères est faux, la donnée sera multipliée par 0 et ne sera pas prise en considération par la fonction.
|
Le prochain exercice à faire la somme des chiffres qui répondent à deux critères. Dans ce cas, le contenu de la première colonne doit être plus grand que « a ». Le contenu de la deuxième colonne doit être « bb ». |
- Entrez les données dans les cellules appropriées.
- Placez le pointeur dans la cellule A11.
- Entrez la formule =SOMME((A1:A9>"a")*(B1:B9="bb")*(C1:C9)) .
N’oubliez pas de placer des parenthèses autour de chaque critère. Excel place la priorité aux opérateurs mathématiques (+,-,*,/,^,()) avant les opérateurs logiques (=,>,>,>=,>=,<>).
- Validez la formule en appuyant sur les touches Ctrl, MAJ (Shift) et Entrée.
Le résultat devrait être de 16 (2+6+8).
Prenons l’exemple de la ligne 5. Le premier critère de la formule est vrai et donne un résultat de 1. Mais le second critère est faux et donne un comme résultat la valeur 0. Le résultat de la multiplication des critères donne donc 0.
Il est possible d’appliquer cette formulation avec d’autres fonctions et avec plus de critères si vous ajustez la formulation. Cependant, il y a certains points pour lesquels vous devez faire très attention.
Par exemple, la fonction =min() donnera un résultat de 0 à moins que les données contiennent une valeur négative. Le calcul requis pour déterminer peut donner des valeurs de 0 si les critères ne sont pas respectés. Aussi, la fonction =médiane() ne donnera pas le bon résultat.
La formule matricielle qui utilise la fonction =moyenne() avec plusieurs critères ne donnera pas le bon résultat. Celle-ci va diviser le total des valeurs qui répondent aux critères par le nombre total de données. Il faut trouver la somme des valeurs et le diviser par le nombre de valeurs qui répondent aux critères. La formulation ressemblerait à ceci : =SOMME((A1:A9>"a")*(B1:B9="bb")*(C1:C9))/SOMME((A1:A9>"a")*(B1:B9="bb")*1).
La seconde partie de la formule calcule le nombre de lignes qui répondent aux critères.
De ET à OU
La dernière formulation implique qu’elles peuvent seulement s’appliquer qui on ajoute l’élément ET entre les critères. C’est l’équivalent de (critère1) ET (critère 2). Mais que faire si on veut utiliser l’élément OU entre les critères? La formulation ressemblerait à ceci : =SOMME(((A1:A9>"a")+(B1:B9="bb"))*(C1:C9))
Le problème avec cette dernière formulation est qu’il est possible qu’une ligne réponde aux deux critères, le résultat de la somme des critères serait 2, Ce qui fausserait le résultat. Il faut donc ajuster cette formule de cette façon :
=SOMME(SI((A1:A9>"a")+(B1:B9="bb")>0;1)*(C1:C9))
Le résultat est de 37 (2+3+4+5+6+8+9).