FORMULES MATRICIELLES

C'est une formule qui s'effectue sur un ou plusieurs tableaux, elle renvoie soit un nouveau tableau, soit un seul résultat. Un tableau (ou matrice) peut-être une suite de données ou une plage de cellules.

matrice de données : vous devez mettre entre crochet cette matrice et séparé chaque données par un point, les données de type texte doivent être mis entre guillemets. Exemple :

• {1,5.2,3.3.4.5} qui comprend les valeurs 1,5 et 2,3 et 3 et 4 et 5
• {"Cathy"."Philippe"}

En fait dans Excel, nous utiliserons essentiellement des formules sur des plages de cellules.

Pour comprendre le principe d'une tel formule, nous prendrons un exemple tout simple : 1 plage de cellules est égal au produit de 2 autres :

Habituellement, vous effectuez en D2 : =B2*C2, puis vous recopier la formule sur la plage D3:D10. La formule matricielle va calculer instantanément toutes les valeurs de la plage D2:D10. Pour l'établir :

• Sélectionnez la plage D2:D10
• tapez = sur votre clavier
• sélectionnez la plage B2:B10
• tapez *
• sélectionnez la plage C2:C10
• Appuyez sur Ctrl+Maj+Entrée (c'est cette combinaison de touche qui indique que la formule est matricielle)
• Vous constatez alors que la formule s'écrit {=B2:B10*C2:C10}.

Qu'a donc fait cette formule matricielle : elle a appliqué la formule sur chaque ligne et renvoyé le résultat sous forme d'un tableau, c'est pour cela que vous deviez au préalable sélectionnez la plage D2:D10 :

Une formule matricielle peut également ne renvoyer qu'une seule valeur. Reprenons notre exemple : nous pouvons établir directement l'équivalent de la somme de la plage D2:D10 :

En C11, tapez la formule =SOMME(B2:B10*C2:C10), puis Ctrl+Maj+Entrée Cette formule effectue d'abord le travail sur les matrices comme indiquées ci-dessus. Elle renvoie donc un tableau de valeurs contenant le produit de chaque ligne. Ensuite la fonction SOMME effectue la somme de ces données. Vous pouvez utiliser ainsi toutes fonctions qui requière 1 ou plusieurs matrices dans ses paramètres ou une suite de données.

Compter les cellules contenant du texte

La formule est : {=SOMME(SI(ESTTEXTE(A1:C11);1;0))}
Celle-ci établit d'abord un SI sur chaque cellule de la plage, si la cellule contient du texte, ce SI renvoit 1 sinon 0. La somme de ces 1 donne donc le nombre de cellules contenant du texte.

Moyenne

La moyenne standard : =MOYENNE(B2:B10) tient compte des valeurs égales à 0. Si vous ne voulez pas en tenir compte, il suffit d'établir la formule matricielle suivante :
{=MOYENNE(SI(B2:B10<>0;B2:B10))}
Le SI renvoit uniquement les données si la cellule de la plage B2:B10 est différente de 0. La moyenne ne se fait donc que sur les valeurs non nulles.

Compter sur plusieurs conditions

Vous connaissez déjà la fonction NB.SI, celle-ci ne permet qu'une seule condition. Ici nous allons calculer le nombre de cellules dont l'article est A et la quantité est 2 :
{=SOMME(SI(A2:A10="A";1;0)*SI(B2:B10=2;1;0))}
la formule SI(A2:A10="A";1;0) renvoie 1 lorsque la cellule de A2:A10 est égale à "A".
la formule SI(B2:B10=2;1;0) renvoie 1 lorsque la cellule de B2:B10 est égale à 2.

Nous pourrions simplifier cette formule ainsi :
{=SOMME((A2:A10="A")*(B2:B10=2))}
(A2:A10="A") renvoie VRAI si le contenu de la cellule de A2:A10 si celle-ci est égale à "A", FAUX dans le cas contraire.
(B2:B10=2) renvoie VRAI si le contenu de la cellule de B2:B10 si celle-ci est égale à 2, FAUX dans le cas contraire
Le tableau ci-dessous vous explique comment se comporte le produit entre des valeurs VRAI et FAUX dans Excel.

Sommer sur plusieurs conditions

Dans notre 1er exemple nous établirons la somme des PU pour les articles A dont la quantité est 2 :
{=SOMME((A2:A10="A")*(B2:B10=2)*C2:C10) }
Cette formule reprend celle expliquée ci-dessus, mais nous multiplions le résultat par la valeur de la cellule située dans la colonne C. Analysons chaque partie de la formule :

Dans notre 1er exemple nous établirons le total des articles A dont la quantité est 2 :
{=SOMME((A2:A10="A")*(B2:B10=2)*C2:C10*B2:B10) }
Le tableau suivant vous indique comment se comporte la formule