Le calcul matriciel est un outil à la fois très puissant et très méconnu.
Une matrice est un tableau de chiffre qui est considéré par Excel comme une entité indissociable.
![]() |
|
Pour sélectionner l'ensemble d'une matrice (avant de la modifier par exemple), sélectionner l'une des cellules de la matrice et taper CTRL+/ . Ce raccourci est très utile pour sélectionner les grandes matrices (il remplace Edition / Atteindre / Cellules / Matrice en cours).
La validation par CTRL+Entrée permet de recopier la même formule dans une plage Excel. Utile pour éviter des recopies vers le bas ou vers la droite.
Le calcul matriciel permet de faire des opérations entre matrices.
Le signe égal permet de recopier une plage de données d'un bloc.
|
La formule de la matrice est simplement {=C3:C7/6.55957}. On aurait pu insérer le tableau converti sur une autre feuille ou bien dans un autre classeur. |
|
Formule matricielle : {=F4:G8/D4:E8-1} |
C'est dans les calculs complexes mettant en jeu des opérations sous conditions sur de grandes bases de données que les formules matricielles prennent tout leur intérêt.
Par exemple, dans une base de données de personnel, calculer le salaire moyen des femmes d'age compris entre 35 et 42 ans et ayant plus de 12 ans d'ancienneté.
Dans une base de données commerciale, calculer le CA moyen des journées ensoleillées qui sont un lundi et dont la température est inférieure à 18°C.
L'utilisation de filtres permet d'extraire les fiches de la base de données correspondant aux critères.
L'utilisation des fonctions de bases de données permet également de résoudre une partie de ces problèmes.
Mais le calcul matriciel est de portée plus large, et ne nécessite généralement pas d'ajout de cellules supplémentaires (zones d'extraction, zones de critères…).
Le principe est simple. Le calcul est basé sur le fait que 1*VRAI = 1 et 1*FAUX = 0.
Dans le tableau ci-dessus, le temps est indiqué dans la plage D7:D37.
La matrice {=D6:D37="soleil"} (en colonne I) va donc renvoyer une matrice contenant VRAI pour chaque jour de soleil, FAUX pour les autres.
Et la matrice {=1*(D6:D37="soleil")} (colonne J) va renvoyer 1 pour soleil et 0 sinon.
La somme des cellules de la colonne J sera donc égale au nombre de 1, c'est à dire au nombre de jours de soleil.
Il suffit donc d'écrire {=SOMME(1*(D6:D37="soleil"))} (formule matricielle) pour obtenir, sur une seule cellule de calcul, le nombre de jour de soleil.
De la même façon, la formule {=SOMME((D7:D37="soleil")*F7:F37)} va renvoyer le chiffre d'affaires des jours ensoleillés.
En effet, pour les jours de soleil, la matrice {=D6:D37="soleil"} renvoie VRAI,
et la matrice {=(D7:D37="soleil")*F7:F37} renvoie la valeur de la colonne F, c'est à dire le CA.
On peut ajouter autant de conditions que souhaité :
La somme des CA des lundis au soleil :
{=SOMME((D7:D37="soleil")*F7:F37*(JOURSEM(C7:C37)=2))}
Et le CA moyen des lundis au soleil :
{=SOMME((D7:D37="soleil")*F7:F37*(JOURSEM(C7:C37)=2))
/SOMME((D7:D37="soleil")*(JOURSEM(C7:C37)=2))}
(Attention à l'utilisation de la fonction MOYENNE qui prendrait en compte pour une valeur nulle les cellules ne répondant pas aux critères. Il vaut mieux faire la somme divisée par le nombre).
La somme des CA des lundis ensoleillés dont la température est inférieure à 10°C :
{=SOMME((D7:D37="soleil")*F7:F37*(JOURSEM(C7:C37)=2)*(E7:E37<10))}
Il est d'ailleurs plus simple d'utiliser des plages nommées ("temps" pour D7:D37, CA pour F7:F37…). la formule devient très facile à comprendre :
{=SOMME(CA*(temps="soleil")*(JOURSEM(date)=2)*(température<10)*(région="sud"))}
{=SOMME(CA*(temps="soleil")*((JOURSEM(date)=7)+(JOURSEM(date)=1)))} va donner le cumul des CA des jours de week-end ensoleillés (noter l'utilisation du signe + pour prendre en compte les samedis et les dimanches).
Autre exemple, le tableau ci-dessous, directement exporté de Datastream vers Excel, donne l'évolution du prix du film polyéthylène mois par mois depuis 1986.
Comment calculer les prix moyens annuels ?
La matrice {=ANNEE(A3:A175)} donne l'année de chaque donnée.
La matrice {=ANNEE(A3:A175)=1990} donne donc VRAI pour toutes les données de 1990.
La somme des prix de 1990 s'écrit donc {=SOMME(B3:B175*(ANNEE(A3:A175)=1990))}
et le nombre de données de l'année 1990 {=SOMME(1*(ANNEE(A3:A175)=1990))}
d'où la moyenne
{=SOMME(B3:B175*(ANNEE(A3:A175)=1990))/SOMME(1*(ANNEE(A3:A175)=1990))}
Il suffit de construire un tableau avec en en-tête les années, et de remplacer 1990 par l'année :
Formule en E3 :
{=SOMME(B$3:B$175*(ANNEE(A$3:A$175)=D3))/SOMME(1*(ANNEE(A$3:A$175)=D3))}
recopiée vers le bas jusqu'en E17.
On peut utiliser une formule matricielle pour comparer le contenu de deux plages de cellules :
la matrice {=A1:A7=B1:B7} est une matrice de 7 lignes sur une colonne contenant des VRAI dans les
lignes pour lesquelles les valeurs saisies en colonnes A et B sont égales et FAUX dans les autres.
La formule matricielle (sur une cellule) {=ET(A1:A7=B1:B7)} renvoie donc VRAI si les deux plages A1:A7 et
B1:B7 contiennent les mêmes valeurs, et FAUX sinon.
Pour la manipulation de matrices, on utilise souvents des matrices ne contenant que des 1 et des 0.
cliquez ici