Tutoriel Excel : formules matricielles

Dans ce didacticiel, vous apprendrez ce qu'est une formule matricielle Excel, comment la saisir correctement dans vos feuilles de calcul et comment utiliser les constantes et les fonctions de matrice dans Excel.

Les formules de matrices dans Excel sont un outil extrêmement puissant et l’un des plus difficiles à maîtriser. Une seule formule matricielle Excel peut effectuer plusieurs calculs et remplacer des milliers de formules habituelles. Et pourtant, 90% des utilisateurs d'Excel n'ont jamais utilisé de fonctions de matrices dans leurs feuilles de calcul simplement parce qu'ils ont peur de commencer à les apprendre.

Dans Excel, une formule de matrice vous permet d'effectuer des calculs puissants sur un ou plusieurs jeux de valeurs. Le résultat peut tenir dans une seule cellule ou il peut s'agir d'une matrice.

Les formules matricielles sont fréquemment utilisées pour l'analyse de données, les sommes et les recherches conditionnelles, l'algèbre linéaire, les mathématiques et la manipulation matricielles, et bien plus encore. Un nouvel utilisateur Excel peut rencontrer des formules matricielles dans les feuilles de calcul d'autres personnes, mais la création de formules matricielles est généralement une rubrique intermédiaire à avancer.

Vous pouvez travailler avec une matrice dans Excel, tout comme avec une plage. C'est-à-dire un ensemble de cellules adjacentes occupant une zone rectangulaire.

Une formule matricielle effectue une opération sur plusieurs valeurs au lieu d'une seule valeur. Le résultat final d'une formule matricielle peut être un élément ou un tableau d'éléments, en fonction de la structure de la formule.

En effet, les formules matricielles sont l’une des fonctionnalités les plus déroutantes d’Excel à apprendre. L'objectif de ce tutoriel est de rendre la courbe d'apprentissage aussi simple et fluide que possible.

Qu'est-ce qu'une formule matricielle dans Excel ?

Avant de commencer avec les fonctions de tableau et les formules Excel, voyons ce que signifie le terme « matrice ». Essentiellement, une matrice dans Excel est une collection d'éléments. Les éléments peuvent être du texte ou des chiffres et ils peuvent résider dans une seule ligne ou une seule colonne, ou dans plusieurs lignes et plusieurs colonnes.

Par exemple, si vous mettez votre liste d'épicerie hebdomadaire dans un format de tableau Excel, cela ressemblera à :

{"Lait". "Oeufs". "Beurre". "Farine"}

Ensuite, si vous sélectionnez les cellules A1 à D1, entrez le tableau ci-dessus précédé d'un signe égal (=) dans la barre de formule et appuyez sur CTRL + MAJ + ENTRÉE pour obtenir le résultat suivant :

Ce que vous venez de faire est de créer une matrice horizontal unidimensionnel. Rien de terrible jusqu'à présent, non ?

Maintenant, quelle est une formule matricielle Excel ? La différence entre une formule matricielle et des formules Excel classiques réside dans le fait qu’une formule matricielle traite plusieurs valeurs au lieu d’une seule. En d'autres termes, une formule matricielle dans Excel évalue toutes les valeurs individuelles d'un tableau et effectue plusieurs calculs sur un ou plusieurs éléments en fonction des conditions exprimées dans la formule.

Une formule matricielle ne traite pas seulement plusieurs valeurs simultanément, mais elle peut également renvoyer plusieurs valeurs à la fois. Ainsi, les résultats renvoyés par une formule matricielle Excel constituent également un tableau.

Les formules de tableau sont disponibles dans toutes les versions d'Excel 2016, Excel 2013, Excel 2010 et versions antérieures.

Et maintenant, il semble que ce soit le bon moment pour vous de créer votre première formule matricielle.

Exemple simple de formule matricielle Excel

Supposons que certains éléments figurent dans la colonne B, leurs prix dans la colonne C et que vous souhaitiez calculer le total général de toutes les ventes.

Bien sûr, rien ne vous empêche de calculer les sous-totaux de chaque ligne avec quelque chose d'aussi simple que = B2 * C2, puis d'additionner ces valeurs :

Cependant, une formule matricielle peut vous épargner ces frappes supplémentaires, car Excel doit stocker les résultats intermédiaires en mémoire plutôt que dans une colonne supplémentaire. Donc, tout ce qu'il faut, c'est une formule de matrice unique en 2 étapes rapides :

Sélectionnez une cellule vide et entrez la formule suivante :

= Somme(B2:B6 * C2:C6)

Appuyez sur le raccourci clavier « CTRL + MAJ + ENTRÉE » pour compléter la formule de matrice.

Une fois cette opération effectuée, Microsoft Excel entoure la formule avec des {accolades}, ce qui est une indication visuelle d'une formule matricielle.

La formule multiplie les valeurs de chaque ligne individuelle du tableau spécifié (cellules B2 à C6), additionne les sous-totaux et génère le grand total :

Cet exemple simple montre à quel point une formule matricielle Excel peut être puissante. Lorsque vous travaillez avec des centaines et des milliers de lignes de données, imaginez combien de temps vous pouvez gagner en entrant une formule matricielle dans une seule cellule.

Pourquoi utiliser des formules matricielles dans Excel ?

Les formules matricielles Excel sont l'outil le plus pratique pour effectuer des calculs complexes et effectuer des tâches complexes. Une formule matricielle unique peut remplacer littéralement des centaines de formules habituelles. Les formules de matrice sont très utiles pour des tâches telles que :

-          Somme des nombres qui remplissent des certaines conditions, par exemple la somme des N valeurs les plus grandes ou les plus petites d'une plage.

-          Calculer la somme de tous les deux rangs, ou chaque Nième rangée ou colonne.

-          Compter le nombre de tous ou de certains caractères dans une plage spécifiée.

Comment entrer une formule matricielle dans Excel (Ctrl + Maj + Entrée)

Comme vous le savez déjà, la combinaison des 3 touches du clavier « CTRL + SHIFT + ENTRÉE » est une touche magique qui transforme une formule standard en une formule matricielle.

Lors de la saisie d'une formule matricielle dans Excel, il convient de garder à l'esprit 4 points importants :

-          Une fois que vous avez terminé de taper la formule et appuyé simultanément sur les touches « CTRL SHIFT ENTER », Excel entoure automatiquement la formule entre {accolades}. Lorsque vous sélectionnez une ou plusieurs cellules de ce type, vous pouvez voir les accolades dans la barre de formule, ce qui vous indique la présence d’une formule matricielle.

-          Taper manuellement les accolades autour d'une formule ne la convertira pas en une formule matricielle. Vous devez appuyer sur le raccourci « Ctrl + Maj + Entrée » pour compléter une formule matricielle.

-          Chaque fois que vous modifiez une formule matricielle, les accolades disparaissent et vous devez appuyer à nouveau sur « Ctrl + Maj + Entrée » pour enregistrer les modifications apportées à votre formule.

-          Si vous oubliez d'appuyer sur « Ctrl + Maj + Entrée », votre formule se comportera comme une formule Excel habituelle et ne traitera que la ou les premières valeurs du ou des tableaux spécifiés.

Comme toutes les formules de matrices d’Excel nécessitent d'appuyer sur « Ctrl + Maj + Entrée », elles sont parfois appelées formules CSE.

Utilisez la touche F9 pour évaluer des parties d'une formule matricielle

Lorsque vous utilisez des formules matricielles dans Excel, vous pouvez observer comment elles calculent et stockent leurs éléments (matrices internes) pour afficher le résultat final que vous voyez affiché dans une cellule. Pour ce faire, sélectionnez un ou plusieurs arguments dans les parenthèses d'une fonction, puis appuyez sur la touche F9. Pour quitter le mode d’évaluation de formule, appuyez sur la touche Echap « ESC ».

Dans l'exemple ci-dessus, pour voir les sous-totaux de tous les produits, cliquez sur la cellule C7 qui contient le totale et sélectionnez B2: B6 * C2: C6 dans la formule comme indiqué dans la capture ci-dessous.

 Appuyez en suite sur la touche clavier « F9 » et obtenez le résultat suivant.

Remarque : Faites bien attention que vous devez sélectionner une partie de la formule avant d'appuyer sur la touche clavier « F9 », sinon la touche F9 remplacera simplement votre formule de tableau Excel par la ou les valeurs calculées.

Formules matricielles monocellulaires et multicellulaires dans Excel

Les formules matricielles Excel peuvent renvoyer un résultat dans une cellule unique ou dans plusieurs cellules. Une formule matricielle entrée dans une plage de cellules est appelée une formule multicellulaire. Une formule matricielle résidant dans une cellule unique est appelée formule à cellule unique.

Il existe quelques fonctions de tableau Excel conçues pour renvoyer des matrices multi cellules, par exemple TRANSPOSE, TENDANCE, FREQUENCE, DROITEREG, etc.

D'autres fonctions, telles que SOMME, MOYENNE, AGREGAT, MAX et MIN, peuvent calculer des expressions de matrice lorsqu'elles sont entrées dans une seule cellule à l'aide du raccourci « Ctrl + Maj + Entrée ».

Les exemples suivants montrent comment utiliser une formule matricielle à cellule unique et à cellules multiples dans Excel.

Exemple 1. Une formule de matrice à cellule unique

Supposons que vous ayez deux colonnes indiquant le nombre d'articles vendus au cours de deux mois différents, par exemple les colonnes B et C, et que vous souhaitiez connaître l'augmentation maximale des ventes.

Normalement, vous ajoutez une colonne supplémentaire, par exemple la colonne D, qui calcule la variation des ventes de chaque produit à l'aide d'une formule telle que = C2-B2, puis recherchez la valeur maximale dans cette colonne supplémentaire = MAX (D: D).

Une formule de tableau Excel n'a pas besoin de colonne supplémentaire car elle stocke parfaitement les résultats intermédiaires en mémoire. Donc, vous entrez simplement la formule suivante et appuyez sur « Ctrl + Maj + Entrée » :

= MAX (C2: C6-B2: B6)

Exemple 2. Une formule matricielle à cellules multiples dans Excel

Dans l'exemple précédent, supposons que vous deviez payer une taxe de 10% sur chaque vente et que vous souhaitiez calculer le montant de la taxe pour chaque produit avec une formule.

Sélectionnez la plage de cellules dans une colonne vide, dites D2: D6, et entrez la formule suivante dans la barre de formule:

= B2: B6 * C2: C6 * 0,1

Lorsque vous appuyez sur les touches clavier « Ctrl + Maj + Entrée », Excel place une instance de votre formule matricielle dans chaque cellule de la plage sélectionnée et vous obtiendrez le résultat suivant :

Exemple 3. Utilisation d'une fonction de matrice Excel pour renvoyer un tableau à cellules multiples

Comme déjà mentionné, Microsoft Excel fournit quelques « fonctions de matrice » spécialement conçues pour fonctionner avec des matrices multi cellulaires. Excel TRANSPOSE est l’une de ces fonctions et nous allons l’utiliser pour transposer le tableau ci-dessus, c’est-à-dire convertir des lignes en colonnes.

La fonction « TRANSPOSE » transposera une matrice et retournera une formule matricielle.

-          La transposition d'une matrice convertit les lignes en colonnes et les colonnes en lignes.

-          La transposition d'un vecteur colonne sera un vecteur ligne.

-          Ceci est généralement entré comme une fonction de tableau et doit donc être entré avec la raccourcie « Ctrl + Maj + Entrée ».

-          La première ligne de la matrice en entrée devient la première colonne de la matrice en sortie.

Sélectionnez une plage de cellules vide dans laquelle vous voulez sortir le tableau transposé. Dans la mesure où nous convertissons des lignes en colonnes, veillez à sélectionner le même nombre de lignes et de colonnes que votre table source contient respectivement des colonnes et des lignes. Dans cet exemple, nous sélectionnons 6 colonnes et 4 lignes.

Appuyez sur la touche clavier « F2 » pour entrer en mode édition.

Entrez la fonction de tableau = TRANSPOSE (tableau) et appuyez sur « Ctrl + Maj + Entrée ». Dans notre exemple, la formule est = TRANSPOSE($A$1: $D$6).

Le résultat va ressembler à ceci :

Voici comment utiliser une fonction de tableau dans Excel.

Comment travailler avec des formules de matrice multi cellules

Lorsque vous utilisez des formules de matrice à cellules multiples dans Excel, veillez à suivre ces règles pour obtenir les résultats appropriés :

-          Sélectionnez la plage de cellules dans laquelle vous souhaitez générer les résultats avant de saisir la formule.

-          Pour supprimer une formule matricielle à plusieurs cellules, sélectionnez toutes les cellules qui la contiennent et appuyez sur la touche clavier « Suppr » ou sélectionnez la formule entière dans la barre de formule, appuyez sur la touche clavier « Suppr », puis sur « Ctrl + Maj + Entrée ».

-          Vous ne pouvez pas modifier ou déplacer le contenu d'une cellule individuelle dans une formule matricielle Excel, vous ne pouvez pas non plus insérer de nouvelles cellules ou supprimer des cellules existantes d'une formule matricielle à plusieurs cellules. Chaque fois que vous essayez de le faire, Microsoft Excel affiche l'avertissement « Vous ne pouvez pas modifier une partie de la matrice ».

-          Pour réduire une formule matricielle, c'est-à-dire pour l'appliquer à moins de cellules, vous devez d'abord supprimer la formule existante, puis en entrer une nouvelle.

-          Pour développer une formule matricielle, c'est-à-dire l'appliquer à plusieurs cellules, sélectionnez toutes les cellules contenant la formule actuelle ainsi que les cellules vides où vous souhaitez l'avoir, appuyez sur la touche clavier « F2 » pour passer en mode édition, ajustez les références dans la formule et appuyez sur le raccourcie « Ctrl + Maj. + Entrée » pour le mettre à jour.

-          Vous ne pouvez pas utiliser de formules de matrice à cellules multiples dans les tableaux Excel.

-          Vous devez entrer une formule de matrice à cellules multiples dans une plage de cellules de la même taille que le tableau résultant renvoyé par la formule. Si votre formule de matrice Excel génère un tableau plus grand que la plage sélectionnée, les valeurs excédentaires n'apparaîtront pas dans la feuille de calcul. Si un tableau renvoyé par la formule est inférieur à la plage sélectionnée, des erreurs #N/A apparaîtront dans des cellules supplémentaires.

-          Si votre formule peut renvoyer un tableau avec un nombre variable d'éléments, entrez-le dans une plage égale ou supérieure au tableau maximum renvoyé par la formule et encapsulez votre formule dans la fonction SIERREUR.

Matrice de constantes Excel

Dans Microsoft Excel, une matrice de constante est simplement un ensemble de valeurs statiques. Ces valeurs ne changent jamais lorsque vous copiez une formule dans d'autres cellules ou valeurs.

Vous avez déjà vu un exemple de constante de tableau créée à partir d'une liste d'épicerie au tout début de ce tutoriel. Voyons maintenant quels autres types de tableaux existent et comment vous les créez.

Il existe 3 types de matrices de constante dans Excel:

1. Matrice de constantes horizontale

Une matrice de constante horizontale réside dans une ligne. Pour créer une matrice de constante de lignes, saisissez les valeurs séparées par des points et mettez-les ensuite entre accolades, par exemple {1.2.3.4}.

Remarque : Lors de la création d'une matrice de constante, vous devez saisir manuellement les accolades d'ouverture et de fermeture.

Pour entrer une matrice horizontale dans une feuille de calcul, sélectionnez le nombre correspondant de cellules vides dans une ligne, tapez la formule = {1.2.3.4} dans la barre de formule, puis appuyez sur le raccourcie « Ctrl + Maj + Entrée ». Le résultat sera similaire à ceci :

Comme vous le voyez sur la capture d'écran, Microsoft Excel encapsule une constante de la matrice dans un autre ensemble d'accolades, exactement comme lorsque vous entrez une formule matricielle dans Excel.

2. Matrice de constantes verticale

Une matrice de constante vertical réside dans une colonne. Vous la créez de la même manière qu'une matrice horizontale avec la seule différence que vous délimitez les éléments avec des points-virgules, par exemple :

= {5; 6; 7; 8}

3. Matrice de constantes à deux dimensions

Pour créer une matrice à deux dimensions dans Excel, vous devez séparer les éléments de chaque ligne par des points et délimiter chaque colonne par des points-virgules.

= {"a". "b". "c"; 1.2.3}

Travailler avec des matrices de constantes Excel

Les matrices de constantes sont l'une des pierres angulaires des formules de matrices Excel. Les informations et conseils suivants peuvent vous aider à les utiliser le plus efficacement possible.

Eléments d'une matrice de constantes

-          Une matrice de constantes Excel peut contenir des nombres, des valeurs de texte, des booléens (TRUE et FALSE) et des valeurs d'erreur, séparés par des points ou des points-virgules.

-          Vous pouvez entrer une valeur numérique sous forme d'entier, décimal ou en notation scientifique. Si vous utilisez des valeurs de texte, elles doivent être entourées de guillemets (") comme dans toute formule Excel.

-          Une matrice de constantes ne peut pas inclure d'autres matrices, références de cellules, plages, dates, noms définis, formules ou fonctions Excel.

Nommer les constantes d'une matrice

Pour rendre une matrice plus facile à utiliser, nommez-la :

Passez à l'onglet « Formules » puis dans le groupe « Noms définis » et cliquez sur le bouton « Définir un nom ». Sinon, appuyez sur « Ctrl + F3 » et cliquez sur « Nouveau ».

Tapez le nom dans la zone « nom »

Dans la zone Fait « référence à », entrez les éléments de votre matrice de constante entourés d'accolades avec le signe d'égalité précédent (=). Par exemple :

= {"Sa". "Di". "Lu"."Ma". "Me"."Je". "Ve"}

Cliquez sur OK pour enregistrer votre matrice nommé et fermez la fenêtre.

Pour entrer la matrice de constante nommée dans une feuille, sélectionnez autant de cellules dans une ligne ou une colonne qu'il y a d'éléments dans votre matrice, tapez le nom de la matrice dans la barre de formule précédée du signe = et appuyez sur « Ctrl + Maj + Entrée ».

Le résultat devrait ressembler à ceci :

Prévenir les erreurs

Si votre matrice de constante ne fonctionne pas correctement, recherchez les problèmes suivants :

-          Délimitez les éléments de votre matrice de constante avec le caractère approprié : point dans les matrices de constante horizontales et point-virgule dans celles verticales.

-          Sélection d'une plage de cellules correspondant exactement au nombre d'éléments de votre matrice, constante. Si vous sélectionnez plus de cellules, chaque cellule supplémentaire portera l'erreur # N / A. Si vous sélectionnez moins de cellules, seule une partie de la matrice sera insérée.

Utilisation de constantes sous forme de matrice dans les formules Excel

Maintenant que vous connaissez le concept de matrice de constante, voyons comment vous pouvez utiliser des matrices dans des formules Excel pour résoudre vos tâches pratiques.

Exemple 1. Somme des N plus grand / plus petit nombres dans une plage

Vous commencez par créer une matrice de constante vertical contenant autant de nombres que vous souhaitez en faire la somme. Par exemple, si vous souhaitez additionner 3 nombres les plus petits ou les plus grands dans une plage, la matrice de constante est {1.2.3}.

Ensuite, vous utilisez la fonction GRANDE.VALEUR ou PETITE.VALEUR, spécifiez toute la plage de cellules dans le premier paramètre et incluez la matrice de constante dans le second. Enfin, intégrez-le dans la fonction SOMME, comme ceci :

Formule matricielle Excel pour la somme des N plus grands / plus petits nombres de la plage :

Somme des 3 plus grands nombres : =SOMME(GRANDE.VALEUR(A1:A6;{1.2.3})) 

Somme les 3 plus petits nombres: = SOMME (PETITE.VALEUR (range ; {1.2.3}))

Vous devez remplacer « range » par la plage des cellules contenant les nombres. Pour moi c’est A1 :A6

N'oubliez pas d'appuyer sur le raccourcie « Ctrl + Maj + Entrée » puisque vous entrez la formule matricielle Excel et vous obtiendrez le résultat suivant :

De la même manière, vous pouvez calculer la moyenne des N valeurs les plus petites ou les plus grandes dans une plage:

Moyenne des 3 premiers chiffres: = MOYENNE(GRANDE.VALEUR(A1:A6;{1.2.3}))

Moyenne des 3 derniers chiffres: = MOYENNE(PETITE.VALEUR(A1:A6;{1.2.3}))

Exemple 2. Formule matricielles pour compter les cellules avec plusieurs conditions

Supposons que vous ayez une liste de commandes et que vous souhaitiez savoir combien de fois un vendeur fournisseur a vendu des produits donnés.

Le moyen le plus simple serait d'utiliser une formule NB.SI.ENS avec plusieurs conditions. Toutefois, si vous souhaitez inclure de nombreux produits, votre formule NB.SI.ENS peut devenir trop volumineuse. Pour le rendre plus compact, vous pouvez utiliser NB.SI.ENS avec SOMME et inclure une matrice de constantes dans un ou plusieurs arguments, par exemple :

= SOMME(NB.SI.ENS(plage1 ; "critère1" ; plage2 ; {"critère1". "critère2"}))

La vraie formule peut ressembler à ceci :

=SOMME(NB.SI.ENS(B2:B9;"Ahmed";C2:C9;{"Pommes"."Oranges"}))

Notre tableau d'échantillons ne comprend que deux éléments, l'objectif étant de démontrer l'approche. Dans vos formules matricielles réelles, vous pouvez inclure autant d'éléments que votre logique métier l'exige, à condition que la longueur totale de la formule n'excède pas 8 192 caractères dans Excel 2013, 2010 et 2007 (1 024 caractères dans Excel 2003 et inférieur) et que votre ordinateur soit assez puissant pour traiter de grands tableaux. Veuillez consulter les limites des formules matricielles dans Excel pour plus de détails.

Opérateurs « ET » et « OU » dans des formules matricielles Excel

Un opérateur indique à la formule comment vous souhaitez traiter les matrices - en utilisant la logique « ET » ou « OU ».

L'opérateur ET est le symbole de multiplication, c'est-à-dire un astérisque (*). Il demande à Excel de renvoyer VRAI si TOUTES les conditions sont évaluées à VRAI.

L'opérateur OU est le signe plus (+). Il retourne VRAI si l’une des conditions d’une expression donnée est évaluée à VRAI.

Formule matricielle avec l'opérateur ET

Dans cet exemple, nous trouvons la somme des ventes lorsque le vendeur est « Ahmed » ET que le produit est « Pommes » :

= SOMME ((A2: A9 = "Ahmed") *  (B2: B9 = "Pommes") * (C2: C9))

Ou

= SOMME (SI (((A2: A9 = "Ahmed") * (B2: B9 = "Pommes")) ; (C2:C9)))

Techniquement, cette formule multiplie trois matrices. Le résultat de la multiplication est également une matrice dont chaque élément est le produit des éléments correspondants des matrices en cours de multiplication. Les deux premières matrices sont des séries de valeurs VRAI ou FAUX résultant de la comparaison de A2: A9 à « Ahmed » et de B2: B9 à " Pommes ". La troisième matrice contient les numéros de vente de la plage C2: C9. Les valeurs VRAI et FAUX sont utilisées dans toutes les opérations arithmétiques, elles sont converties respectivement en 1 et 0. Et comme multiplier par 0 donne toujours zéro, la matrice résultante a 0 même si aucune des conditions (les deux premières matrices) n'est remplie. Si les deux conditions sont remplies, il a l'élément correspondant de la troisième matrice (par exemple 1 * 1 * C2 = 10). Ainsi, le résultat de la multiplication est le suivant: {10; 0; 0; 0; 0; 0; 0; 0} Enfin, la fonction SOMME additionne les éléments de la matrice et renvoie un résultat de 10.

Formule matricielle Excel avec l'opérateur OU

La formule matricielle suivante avec l'opérateur OU (+) additionne toutes les ventes où le vendeur est « Ahmed » OU le produit est « Pommes » :

=SOMME(SI(((A2:A9="Ahmed") + (B2:B9 ="Pommes"));(C2:C9)))

Dans cette formule, vous ajoutez les éléments des deux premières matrices (qui sont les conditions que vous souhaitez tester) et obtenez VRAI (> 0) si l'une des conditions est VRAIE. Le résultat est FAUX (0) uniquement lorsque les deux conditions sont FAUX. Ensuite, SI vérifie si le résultat de l'addition est supérieur à 0 et, si c'est le cas, SOMME additionne un élément correspondant de la troisième matrice (C2: C9).

Remarque : Dans les versions modernes d'Excel, l'utilisation de formules matricielles dans de tels scénarios est en réalité superflue et une simple formule SOMME.SI pourrait produire exactement les mêmes résultats. Néanmoins, les opérateurs ET et OU dans les formules matricielles peuvent s'avérer utiles dans d'autres scénarios plus complexes, sans parler d'une très bonne gymnastique d'esprit:)

Opérateur double unaire dans les formules matricielles Excel

Si vous avez déjà travaillé avec des formules matricielles dans Excel, vous avez probablement rencontré quelques-unes contenant un double tiret (-) et vous vous êtes peut-être demandé à quoi il était utilisé.

Un double tiret, techniquement appelé opérateur double unaire, est utilisé pour convertir les valeurs booléennes non numériques (VRAI / FAUX) renvoyées par certaines expressions en 1 et 0 qu'une fonction matricielle Excel peut comprendre.

J'espère que l'exemple suivant facilitera la compréhension des choses. Supposons que vous ayez une liste de dates dans la colonne A et que vous souhaitiez savoir combien de dates ont lieu en janvier, quelle que soit l'année.

La formule suivante fonctionnera :

= SOMME (--(MOIS (A2: A10) = 1))

Comme il s’agit d’une formule matricielle Excel, n’oubliez pas d’appuyer sur « Ctrl + Maj + Entrée » pour la compléter.

Si vous êtes intéressé par un autre mois, remplacez 1 par un numéro correspondant. Par exemple, 2 signifie février, 3 signifie mars, etc. Pour rendre la formule plus flexible, vous pouvez spécifier le numéro du mois dans une cellule, comme illustré dans la capture d'écran :

=SOMME(--(MOIS(A2:A10)=C1))

Et maintenant, analysons le fonctionnement de cette formule matricielle Excel. La fonction MOIS renvoie le mois de chaque date dans les cellules A2 à A10, représentée par un numéro de série qui produit la matrice {2; 1; 4; 2; 12; 1; 2; 12; 1}.

Ensuite, chaque élément de la matrice est comparé à la valeur de la cellule D1, qui est le numéro 1 dans cet exemple. Le résultat de cette comparaison est matrice de valeurs booléennes VRAI et FAUX. Comme vous vous en souvenez, vous pouvez sélectionner une certaine partie d'une formule matricielle et appuyer sur F9 pour voir en quoi cette partie correspond:

Sélectionnez une partie d'une formule matricielle et appuyez sur la touche clavier « F9 » pour voir à quoi cette partie correspond.

Enfin, vous devez convertir ces valeurs booléennes en 1 et en 0 que la fonction SOMME peut comprendre. Et c’est pour cela que l’opérateur double unaire est nécessaire. Le premier unaire force VRAI/ FAUX sur -1/0, respectivement. Le deuxième unaire annule les valeurs, c’est-à-dire inverse le signe en les transformant en +1 et 0, que la plupart des fonctions Excel peuvent comprendre et utiliser. Si vous supprimez le double unaire de la formule ci-dessus, cela ne fonctionnera pas.

La somme des matrices

Vous pouvez résumer des matrices avec le même nombre d'éléments. Le nombre de lignes et de colonnes de la première plage doit être égal au nombre de lignes et de colonnes de la deuxième plage.

Dans la première cellule de la matrice résultante, vous devez entrer une formule de la forme suivante: = le premier élément de la première matrice + le premier élément de la seconde: (= A1 + E1). Appuyez sur la touche clavier « Entrée » et étendez la formule à la plage complète.

La multiplication de matrices dans Excel

La tâche est la suivante :

Pour multiplier une matrice par un nombre, vous devez multiplier chacun de ses éléments par ce nombre. La formule dans Excel : = A1 * $ E $ 3 (une référence à une cellule avec un nombre doit être absolue). Saisissez cette formule dans la cellule F1 et cliquez sur la touche clavier Entrée en suit vous pouvez étendre la formule à la plage complète.

Multiplions la matrice avec différentes plages. Il est possible de ne trouver le produit de matrices que si le nombre de colonnes de la première matrice est égal au nombre de lignes de la seconde. Pour cela nous allons utiliser la fonction PRODUITMAT.

La fonction Excel PRODUITMAT renvoie le produit matrice de deux tableaux. La matrice résultante contient le même nombre de lignes que matrice1 et le même nombre de colonnes que matrice2. Si vous renvoyez plusieurs résultats dans un tableau de la feuille de calcul, entrez-le sous la forme d'une formule matricielle avec « control + shift + enter ».

Prenons l’exemple ci-dessous. Nous avons deux matrices et on veut effectuer la multiplication :

Dans la matrice résultante, le nombre de lignes est égal au nombre de lignes du premier tableau et le nombre de colonnes est égal au nombre de colonnes du second.

Pour plus de commodité, nous sélectionnons la plage dans laquelle les résultats de la multiplication seront placés. Nous rendons active la première cellule du champ résultant. Puis allez sur l’onglet « FORMULES » en suite cliquez sur «Math et Trigonométrie» et choisissez la fonction « PRODUITMAT » dans la liste des fonctions proposées. Nous introduisons la formule: =PRODUITMAT (A1: C5 ; E1: H3). Entrez les plages de cellules comme indiqué dans la capture ci-dessous et valider la formule sous forme de formule matricielle (CTRL + MAJ + Entrée).

Et voilà le résultat obtenu :

Calculer l’inverse d’une matrices dans Excel

La fonction INVERSEMAT renvoie l'inverse de la matrice sous forme de formule matricielle.

Ceci est généralement entré comme une fonction de matrice et doit donc être entré avec le raccourcie « Ctrl + Maj + Entrée ».

Seules les matrices carrées "peuvent avoir" des inverses et pas toutes les matrices carrées ont des inverses. Il est logique de le trouver s'il s'agit d'une matrice carrée (le nombre de lignes et de colonnes est identique).

La dimension de la matrice inverse correspond à la taille de l'original. Sélectionnez la première cellule de la plage vide pour la matrice inverse. Nous utilisons la fonction INVERSEMAT: allez sur l’onglet « FORMULES » puis cliquez sur «Math and Trigonométrie » et sélectionnez la fonction « INVERSEMAT » dans la liste de fonctions proposées.

Nous introduisons la formule « =INVERSEMAT(A1: D4) » en tant que fonction de jeu de données. Le seul argument est la plage avec l'original. Et comme cela nous aurons la matrice  inverse dans Excel comme dans la capture ci-dessous :

Et voilà le résultat à obtenir :

Trouver le déterminant d’une matrice

C'est un nombre unique trouvé pour une matrice carrée. Nous utilisons: « FORMULES » - « Math and Trigonométrie » - « DETERMAT ».

Nous plaçons le curseur dans n'importe quelle cellule de la feuille ouverte. Entrez la formule: = DETERMAT(A1: D4).

Important

-          Lors de l'utilisation des fonctions « DETERMAT », « INVERSEMAT » et « PRODUITMAT », chaque cellule du tableau doit contenir une valeur numérique. Sinon, la fonction retournera #VALEUR!

-          Une matrice avec « m » lignes et « n » colonnes est dite d'ordre (m * n). Lorsque « m » et « n » sont égaux, la matrice est dite carrée.

-          Deux matrices sont dites identiques si chaque élément d'une matrice est égal à l'élément correspondant de l'autre matrice.

-          La multiplication de matrice n'est pas commutative, donc (A * B) n'est pas égal à (B * A).

-          La division matricielle n'est pas définie.

-          Une matrice d'identité est une matrice carrée contenant tous les zéros, sauf la diagonale principale contenant la valeur 1.

Ainsi, nous avons effectué des actions avec les matrices en utilisant les fonctionnalités intégrées d'Excel. J'espère que ce court tutoriel s'est révélé utile pour votre maîtrise des formules matricielles Excel.