Les formules matricielles Excel permettent d’effectuer des calculs sur plusieurs cellules simultanément, là où une formule classique n’en traiterait qu’une seule. Résultat : des calculs conditionnels, des sommes de produits, des comptages complexes, le tout sans colonnes intermédiaires. Que vous soyez sur une version ancienne d’Excel ou sur Microsoft 365, le fonctionnement diffère sensiblement. Ce guide couvre les deux cas.
Ce qu’est vraiment une formule matricielle
Une formule classique opère sur une valeur et retourne une valeur. Une formule matricielle, elle, opère sur un tableau de valeurs et peut retourner soit un résultat unique, soit un tableau de résultats. Excel traite chaque valeur du tableau en mémoire, sans qu’il soit nécessaire de créer des colonnes de calcul intermédiaires.
Le signe distinctif d’une formule matricielle dans la barre de formules : les accolades { } qui entourent la formule. Ces accolades ne se saisissent pas manuellement. Elles apparaissent automatiquement lorsque la formule est validée correctement.
- Les formules matricielles à résultat unique : un seul calcul agrégé est affiché dans une cellule.
- Les formules matricielles à résultats multiples : les résultats se répartissent sur plusieurs cellules sélectionnées au préalable.
Validation avec Ctrl+Maj+Entrée : la règle des versions antérieures
Sur les versions d’Excel antérieures à Microsoft 365 (Excel 2019, 2016, 2013, 2010), les formules matricielles s’activent via un raccourci spécifique : Ctrl + Maj + Entrée. C’est pour cette raison qu’on les appelle aussi formules CSE (Ctrl-Shift-Enter).
- Sélectionnez la plage de cellules qui accueillera les résultats (ex. B2:B5).
- Saisissez la formule, par exemple =A2:A5*1,20.
- Validez avec Ctrl + Maj + Entrée au lieu d’Entrée seul.
- Excel entoure automatiquement la formule d’accolades : {=A2:A5*1,20}.
Si vous oubliez le raccourci et appuyez simplement sur Entrée, Excel interprète la formule comme une formule classique et ne calcule que la première valeur du tableau. Aucune accolade n’apparait, et le résultat est incomplet.
Autre contrainte sur ces versions : il est impossible de modifier une seule cellule d’une plage matricielle. Toute la plage est protégée en bloc. Pour supprimer ou modifier, il faut sélectionner l’ensemble de la plage, puis faire la modification.
Microsoft 365 : les tableaux dynamiques changent tout
Avec Microsoft 365, le moteur de calcul a été entièrement revu. Les formules matricielles deviennent des tableaux dynamiques : il suffit de saisir la formule et d’appuyer sur Entrée. Excel diffuse automatiquement les résultats dans les cellules adjacentes, sans sélection préalable de la plage.
Cette diffusion automatique s’appelle le déversement. Si une cellule de la zone de déversement est déjà occupée, Excel affiche l’erreur #SPILL!. La solution : vider la cellule qui bloque.
- FILTRE : extrait les lignes d’un tableau selon des critères.
- TRIER / TRIERPAR : trie un tableau sans modifier la source.
- UNIQUE : extrait les valeurs distinctes d’une plage.
- SEQUENCE : génère une série de nombres en tableau.
- ALEA.TABLEAU : remplit une plage de nombres aléatoires.
Exemples concrets de formules matricielles utiles
Somme de produits (quantités x prix) : =SOMMEPROD(B2:B6*C2:C6). Cette formule fonctionne sans Ctrl+Maj+Entrée car SOMMEPROD est nativement matricielle.
Somme des 3 valeurs les plus élevées : =SOMME(GRANDE.VALEUR(A1:A6;{1;2;3})). Le tableau constant {1;2;3} demande à GRANDE.VALEUR de renvoyer les trois premières valeurs, que SOMME additionne ensuite.
Comptage conditionnel avec plusieurs critères en OU : =SOMME(NB.SI.ENS(B2:B9; »Ahmed »;C2:C9;{« Pommes »; »Oranges »})). La constante matricielle évalue les deux critères séparément.
Transposition d’un tableau : =TRANSPOSE(A1:D4). Sur versions antérieures, sélectionnez la plage de destination avec les dimensions inversées avant de valider avec Ctrl+Maj+Entrée.
Les opérateurs logiques dans les matrices
Dans une formule matricielle, les opérateurs logiques classiques (ET, OU) ne fonctionnent pas directement sur des plages. On les remplace par des opérateurs arithmétiques :
- Opérateur ET (*) : multiplie deux conditions booléennes. Exemple : (A2:A10= »Paris »)*(B2:B10>1000).
- Opérateur OU (+) : additionne deux conditions. Le résultat est VRAI si au moins l’une est vraie.
- Double négatif (–) : convertit les valeurs VRAI/FAUX en 1/0. Exemple : =SOMME(–(A2:A10>500)) compte les cellules supérieures à 500.
Erreurs fréquentes et comment les éviter
Oublier Ctrl+Maj+Entrée sur les versions antérieures à 365 est l’erreur la plus courante. La formule retourne un résultat partiel ou incorrect sans aucun message d’erreur apparent.
L’erreur #SPILL! sur Microsoft 365 signale qu’une cellule bloque la zone de déversement. Supprimez le contenu de la cellule concernée, qu’Excel indique en pointillés.
Utiliser des formules matricielles dans un Tableau structuré est impossible sur les versions CSE. Les formules matricielles classiques ne sont pas compatibles avec les Tableaux structurés d’Excel.
Questions fréquentes
Une formule classique traite une seule valeur et retourne un seul résultat. Une formule matricielle traite simultanément plusieurs valeurs et peut retourner un résultat unique ou un tableau de résultats, sans colonnes auxiliaires.
Les accolades indiquent qu’Excel interprète la formule comme matricielle. Elles sont insérées automatiquement après validation avec Ctrl+Maj+Entrée. Ne les saisissez jamais manuellement : cela n’activerait pas le mode matriciel.
Il faut sélectionner l’intégralité de la plage matricielle, modifier la formule et revalider avec Ctrl+Maj+Entrée. Pour supprimer, sélectionnez toute la plage et appuyez sur Suppr.
L’erreur #SPILL! apparait quand une formule à tableau dynamique tente de déverser ses résultats dans des cellules déjà occupées. Videz la cellule qui bloque, indiquée par Excel en pointillés.
Non. SOMMEPROD est nativement matricielle et accepte des plages sans validation spéciale.
