Les formules matricielles

Vous avez peut-être déjà entendu parler de "formules matricielles" sans pour autant savoir de quoi il s'agit. Voici de quoi éclairer votre lanterne !.

Prenons l'exemple suivant : Vous avez une liste de valeurs dans la colonne A, comme ceci :

A
1
3
2
6
3
2
4
1
5
9
6
5
7
8
8
3
9
1
10
8

Admettons que vous désiriez avoir la somme totale des chiffres supérieurs ou égaux à 5. Inutile de chercher une utilité quelconque dans cet exemple, il n'y en a pas... On va dire que ce sont des notes par exemple, et qu'on aimerait le total des élèves qui ont eu une note supérieure ou égale à 5. Bref... Il s'agit de comprendre les formules matricielles.

Les notes à additionner sont le suivantes (En jaune)

A
1
3
2
6
3
2
4
1
5
9
6
5
7
8
8
3
9
1
10
8

Si on fait le calcul de cette somme à la main, nous trouvons : 6 + 9 + 5 + 8 + 8 = 36. Il va s'agir de trouver 36 avec notre formule matricielle.

Mais justement... Est-il nécessaire de créer une formule matricielle pour obtenir ce résultat. Non, pas obligatoirement. On pourrait utiliser une fonction Si, comme ceci :

Dans la colonne B, on demande si dans la colonne A le chiffre est supérieur ou égal à 5, et, si oui, on l'écrit, sinon on écrit 0

A
B
1
3
=SI(A1>=5;A1;0)
2
6
3
2
4
1
5
9
6
5
7
8
8
3
9
1
10
8

On n'a plus qu'à recopier vers le bas :

A
B
1
3
=SI(A1>=5;A1;0)
2
6
=SI(A2>=5;A2;0)
3
2
=SI(A3>=5;A3;0)
4
1
=SI(A4>=5;A4;0)
5
9
=SI(A5>=5;A5;0)
6
5
=SI(A6>=5;A6;0)
7
8
=SI(A7>=5;A7;0)
8
3
=SI(A8>=5;A8;0)
9
1
=SI(A9>=5;A9;0)
10
8
=SI(A10>=5;A10;0)

Ce qui donnera le résultat suivant :

A
B
1
3
0
2
6
6
3
2
0
4
1
0
5
9
9
6
5
5
7
8
8
8
3
0
9
1
0
10
8
8

Et après ? Et bien il suffit de demander la somme de la colonne B, comme ceci :

A
B
1
3
0
2
6
6
3
2
0
4
1
0
5
9
9
6
5
5
7
8
8
8
3
0
9
1
0
10
8
8
11
 
=SOMME(B1:B10)

Ce qui donnera bien 36. Et bien voilà ! Nul besoin de formules matricielles pour calculer ce résultat !

C'est vrai. Mais par contre, pour trouver ce résultat, nous avons dû créer 10 formules : =SI(A1>=5;A1;0), =SI(A2>=5;A2;0), etc. Bon, là ça va parce que nous n'avons que 10 valeurs, mais si c'était une colonne de 35000 chiffres ? Et bien il aurait fallu 35000 formules ! Quelque peu inélégant... Et mangeur de place sur le disque dur !

Et bien, c'est celà une formule matricielle : C'est la possibilité d'obtenir le même résultat avec UNE SEULE formule !

En fait, il n'y a pas le choix : Pour chaque ligne, il faut qu'Excel définisse s'il s'agit d'une valeur supérieure ou égale à 5 ou non... Ca c'est certain ! Mais on va lui dire d'une manière un peu spéciale : On va lui demander de copmparer toutes les lignes d'un seul coup, comme ceci :

=SI(A1:A10>=5;A1:A10;0)

Ah oui, mais l'histoire des 2 points, on connait : C'est comme ça qu'on fait une somme par exemple : =Somme(A1:A10), c'est facile : C'est la somme des chiffres de A1 JUSQU'A A10... Mais la fonction SI, comment va-t-elle faire ??? Elle doit donner un résultat différent par ligne !!!

Eh oui ! C'est ça la puissance des formules matricielles : Vous avez raison : On ne peut pas utiliser l afonctioin SI comme ça ! Mais par contre, on peut la COUPLER avec la fonction Somme ! Oui : N'est ce pas finalement ce qu'on veut : Un seul chiffre qui est la somme de tous les chiffres qui sont égaux ou supérieurs à 5 ?

Alors voici comment on fait :

=SOMME(SI(A1:A10>=5;A1:A10))

Ah Ahhhh ! Oui mais ça ne change rien : Le SI, il ne va pas pouvoir calculer toutes les lignes ensemble ! D'ailleurs, si on essaie d'entrer cette formule, on aura une erreur !

Eh oui, SAUF... Si quand vous avez terminé d'écrire =SOMME(SI(A1:A10>=5;A1:A10)), au lieu d'appuyer sur ENTER, vous appuyez sur CTRL SHIFT ENTER (Contrôle-Majuscule-Enter), alors, la formule se transforme en

{=SOMME(SI(A1:A10>=5;A1:A10))}

ET CA MARCHE ! Voici ou l'écrire :

A
B
1
3
0
2
6
6
3
2
0
4
1
0
5
9
9
6
5
5
7
8
8
8
3
0
9
1
0
10
8
8
11
 
{=SOMME(SI(A1:A10>=5;A1:A10))}

Donc, qu'on soit bien d'accord : CE N'EST PAS VOUS qui écrivez les accolades : Vous écrivez =SOMME(SI(A1:A10>=5;A1:A10)), et CTRL SHIFT ENTER.

Voilà l'utilité des formules matricielles : Résumer en une seule cellule toute une série de formules !

Exercice :

Vous avez un classeur Excel avec dans les cellules de A1 jusqu'à A12 les valeurs suivantes :

A
1
31
2
6
3
8
4
13
5
7
6
9
7
53
8
6
9
20
10
9
11
15
12
98
13
 

Dans la cellule A13, arriveriez-vous a donner le nombre de cellules qui ont un nombre compris entre 10 et 20 (ici : 3) ?

Un filon : On utilise la fonction SI combinée à la fonction NB (Et pas Nb.Si).

Alors vous avez trouvé ? Sinon, la solution se trouve ici.