Comment faire une matrice dans Excel

Quelle est la différence entre une matrice et un tableau dans Excel

Une matrice est un ensemble de valeurs que la formule manipule en bloc, alors qu’un tableau est une zone de la feuille que tu as organisée. La première n’a pas d’existence visible et ne dure que le temps d’un calcul, quand le second survit à la fermeture du classeur.

La confusion vient de ce qu’Excel emploie le mot tableau pour trois objets qui n’ont rien à voir. Le tableau structuré est la zone que tu obtiens avec Ctrl+L, avec son nom, ses filtres et ses bandes de couleur. Le tableau dynamique est au contraire le résultat d’une formule qui renvoie plusieurs valeurs et remplit les cellules voisines toute seule. Le tableau à double entrée, enfin, n’est qu’une mise en page, celle qui croise les mois en colonnes et les catégories en lignes, et qui se monte plutôt avec un tableau croisé dynamique.

Beaucoup de gens appellent aussi matrice une grille de ce dernier genre, la matrice des risques ou celle des compétences, et cherchent alors un modèle à remplir plutôt qu’une formule. Ce n’est pas le sujet de cette page, qui parle du calcul. Si tu voulais une grille prête à l’emploi, les modèles Excel en proposent plusieurs.

Écrire une formule matricielle qui calcule en une seule cellule

Une formule matricielle applique le même calcul à toute une plage, puis réduit le résultat à un seul nombre. Le classeur d’exemple porte douze ventes sur sa feuille Ventes, avec la région, le produit, la quantité et le prix unitaire, et une seconde feuille réservée au calcul matriciel proprement dit.

Multiplier deux colonnes sans passer par une colonne de calcul

Le réflexe habituel consiste à ajouter une colonne Montant, à y écrire =C2*D2, à la recopier sur douze lignes, puis à en faire la somme. La colonne E du classeur fait exactement cela, et elle reste utile puisqu’on lit le montant de chaque vente. Quand seul le total t’intéresse, la matricielle te dispense pourtant de la créer.

Ce qui se passe à l’intérieur tient en deux temps. Excel multiplie d’abord les deux plages terme à terme, ce qui lui fabrique en mémoire une liste de douze montants, puis SOMME additionne cette liste. C’est cette liste intermédiaire, jamais écrite dans la feuille, qu’on appelle une matrice, et c’est elle qui remplace la colonne E.

Valider avec Ctrl+Maj+Entrée sur un Excel plus ancien

Sur Excel 365 et 2021, la formule ci-dessus se valide par un simple Entrée et tout se passe bien. Sur les versions antérieures, elle réclame en revanche Ctrl + Maj + Entrée, et Excel l’entoure alors d’accolades dans la barre de formule pour signaler qu’il l’a comprise comme une matricielle. Ces accolades, il les écrit lui-même, et les taper au clavier transformerait la formule en texte.

Lire le calcul morceau par morceau avec F9

Quand le résultat ne ressemble à rien, la touche F9 ouvre la formule et montre ce qu’Excel a réellement calculé. Clique dans la barre de formule, sélectionne un morceau, par exemple C2:C13*D2:D13, puis appuie sur F9. Excel remplace le morceau par la liste des douze montants, et tu vois d’un coup d’œil si la plage est la bonne. La touche Échap annule ensuite l’affichage sans rien changer à la formule.

Calculer une somme matricielle avec plusieurs conditions

C’est le vrai terrain des matricielles, celui où elles font ce qu’aucune fonction simple ne fait. L’idée repose sur une propriété du test logique, car une comparaison comme A2:A13="Sud" ne rend pas un mot mais une liste de VRAI et de FAUX, et Excel compte chaque VRAI pour 1 et chaque FAUX pour 0 dès qu’un calcul s’en mêle.

Croiser deux critères par une multiplication

Multiplier deux listes de 0 et de 1 ne laisse un 1 que là où les deux critères sont remplis en même temps, ce qui revient à un ET. Les cases jaunes G4 et G5 du classeur portent la région et le produit cherchés, et la formule les lit plutôt que de les écrire en dur.

Chaque ligne du Sud qui vend un casque donne 1 multiplié par 1, donc son montant entier, et toutes les autres donnent 0. La somme ne retient ainsi que les deux ventes concernées. Un critère chiffré s’écrit de la même façon, avec (C2:C13>$G$6) pour ne garder que les quantités au-dessus du seuil de la case jaune G6.

Passer de ET à OU par une addition

Remplacer la multiplication par une addition change complètement le sens du filtre, puisqu’une somme de 0 et de 1 vaut 1 dès que l’un des deux critères est rempli. La formule ci-dessous additionne donc le Sud et le Nord d’un seul coup, là où deux SOMME.SI.ENS auraient demandé deux formules mises bout à bout.

Les parenthèses autour de l’addition comptent beaucoup ici, car sans elles Excel multiplierait avant d’additionner et le résultat n’aurait plus de sens. Attention aussi aux critères qui se recouvrent, parce qu’une ligne qui remplirait les deux donnerait 2 et compterait double.

Trouver la plus grosse vente avec MAX et SI

MAX ne connaît pas de version conditionnelle sur les vieilles versions d’Excel, et c’est une matricielle qui lui en donne une. SI reçoit ici la liste des VRAI et des FAUX, puis renvoie le montant pour les lignes retenues, et MAX prend le plus grand de cette liste.

Depuis Excel 2019, MAX.SI.ENS fait la même chose sans matricielle, et la colonne H du classeur l’emploie pour que tu compares les deux écritures. La matricielle garde l’avantage dès que le critère est un calcul plutôt qu’une valeur, comme une différence de dates ou un rapport entre deux colonnes, que MAX.SI.ENS ne sait pas exprimer.

La dernière ligne mérite un mot, parce que SOMMEPROD obtient exactement le même 765,00 € sans réclamer la moindre validation particulière. Cette fonction travaille sur des plages entières par construction, si bien qu’elle se comporte pareil sur toutes les versions d’Excel. Sur un classeur qui circule entre plusieurs générations d’Excel, c’est donc elle qu’il faut préférer à la matricielle.

Multiplier et inverser une matrice avec PRODUITMAT et INVERSEMAT

Le calcul matriciel au sens de l’algèbre, lui, passe par quatre fonctions dédiées que rien ne remplace. La feuille Matrices du classeur porte trois blocs de nombres, A sur deux lignes et trois colonnes en B4:D5, B sur trois lignes et deux colonnes en B8:C10, et C carrée sur trois lignes et trois colonnes en B13:D15.

Faire un produit matriciel avec PRODUITMAT

PRODUITMAT multiplie deux matrices selon la règle de l’algèbre, où chaque case du résultat est la somme des produits d’une ligne de la première par une colonne de la seconde. Écris la formule dans la case jaune F4, et Excel remplit les trois cellules voisines tout seul.

Les dimensions obéissent à une règle stricte, puisque le nombre de colonnes de la première matrice doit égaler le nombre de lignes de la seconde. A compte trois colonnes et B compte trois lignes, donc le produit existe et mesure deux lignes sur deux colonnes. Multiplier deux matrices de trois lignes et deux colonnes renvoie en revanche #VALEUR!, faute de cette correspondance. L’ordre compte lui aussi, car B multipliée par A donne une tout autre grille, de trois lignes sur trois colonnes.

Retourner une matrice avec TRANSPOSE

Quand les dimensions ne tombent pas juste, TRANSPOSE bascule les lignes en colonnes et rend le produit possible. Elle sert aussi toute seule, pour récupérer un tableau dans l’autre sens sans le ressaisir, et elle reste liée à la source contrairement au collage transposé.

Obtenir l’inverse et le déterminant d’une matrice carrée

INVERSEMAT calcule l’inverse d’une matrice carrée, c’est-à-dire la matrice qui, multipliée par la première, redonne la matrice identité. Toutes les matrices carrées n’en ont pas, et DETERMAT dit en un nombre laquelle est dans ce cas, puisqu’un déterminant nul signifie qu’il n’y a pas d’inverse. La matrice C du classeur a pour déterminant 1, donc son inverse existe et tombe même en nombres entiers.

Une matrice au déterminant nul renvoie #NOMBRE! plutôt qu’une grille de chiffres, et c’est le signe que ses lignes se déduisent les unes des autres. Une plage qui n’est pas carrée, ou qui contient du texte, donne de son côté #VALEUR!. La fonction MATRICE.UNITAIRE complète la famille en fabriquant la matrice identité de la taille que tu lui demandes.

Résoudre un système d’équations à trois inconnues

Un système s’écrit sous la forme C multipliée par X égale Y, où C porte les coefficients et Y le second membre, et sa solution vaut l’inverse de C multipliée par Y. La case jaune F21 du classeur attend donc =PRODUITMAT(INVERSEMAT(B13:D15);B18:B20), qui rend les trois inconnues d’un coup et se recalcule dès qu’un coefficient change. Les fiches PRODUITMAT et INVERSEMAT déroulent chacune un cas chiffré, une facturation de traiteur et un système d’ingénierie.

Renvoyer une matrice entière et la reprendre avec l’opérateur #

Jusqu’ici la matrice restait invisible, réduite à un nombre par SOMME ou par MAX. Depuis Excel 365, une formule qui renvoie plusieurs valeurs les écrit au contraire dans la feuille, sur les cellules voisines, et ce remplissage automatique porte le nom de déversement. Une fine bordure bleue entoure alors la zone remplie.

La formule ne vit que dans J2, et les onze cellules en dessous n’appartiennent qu’à elle. Clique dans l’une d’elles, la barre de formule te la montre en grisé et ne te laisse la corriger que depuis J2. Rien ne t’empêche en revanche d’y taper autre chose, et J2 bascule alors en erreur, parce que la zone d’arrivée n’est plus libre. La zone s’ajuste aussi toute seule, donc une treizième vente ajoutée à la source allongerait la liste d’une ligne.

Reste à réutiliser ce bloc ailleurs, et c’est le rôle de l’opérateur dièse. Un # collé derrière l’adresse de la première cellule désigne la zone entière, quelle que soit sa taille du moment, ce qui évite de figer une plage qui bouge.

Le même J2# sert de source à un graphique ou à une liste déroulante qui doivent suivre les données, et =LIGNES(J2#) compte les douze lignes du moment. Les fonctions qui se déversent ainsi sont nombreuses, à commencer par UNIQUE, qui rend les quatre régions sans doublon, FILTRE, qui extrait les lignes d’un critère, TRIER et SEQUENCE. Elles remplacent avantageusement les matricielles alambiquées qu’il fallait monter avant elles.

Pourquoi une formule matricielle ne fonctionne pas dans Excel

La panne la plus coûteuse est celle qui ne s’affiche pas. Sur un Excel d’avant 2021, le total du classeur validé par un simple Entrée rend 135,00 € au lieu de 3 948,50 €, et celui du Sud rend 0,00 € au lieu de 1 650,50 €, puisqu’Excel s’en est tenu à la ligne où la formule se trouve. Aucune couleur, aucun triangle vert, juste un nombre crédible. Regarde donc la barre de formule, où les accolades doivent entourer la formule, et revalide avec les trois touches si elles manquent.

L’erreur #PROPAGATION! quand la zone d’arrivée n’est pas libre

Une formule ne peut pas se déverser dans une cellule déjà occupée, et elle affiche #PROPAGATION! à la place de son résultat. Un espace invisible suffit à tout bloquer, donc regarde les cellules situées sous la formule avant de chercher plus loin.

Un losange d’avertissement apparaît près de la cellule en erreur, et il ouvre un panneau qui propose d’afficher les étapes du calcul ou de sélectionner directement les cellules en obstruction, avec un bouton Ignorer pour le refermer sans y toucher.

Le message « Vous ne pouvez pas modifier une partie de matrice »

Une matricielle validée par Ctrl+Maj+Entrée sur plusieurs cellules forme un bloc indivisible, et Excel refuse d’en effacer une seule cellule comme de supprimer une ligne qui la traverse. Sélectionne le bloc entier avant d’effacer, car rien de moins ne passe, alors qu’un tableau déversé d’Excel 365 se vide en effaçant sa première cellule. La fiche formule matricielle du lexique détaille ces deux blocages et les autres symptômes qu’on croise en chemin.