Formule matricielle Excel

Une formule matricielle est une formule qui pourra aussi bien effectuer des opérations entre plusieurs plages que d’effectuer une même opération sur plusieurs valeurs.

Dans notre exemple, la formule en F5 est :

= SOMME( (B5:B12>DATE(2024;7;1)) * (D5:D12) )

Cette formule effectue une opération entre la plage des dates et la plage des valeurs afin de faire la somme des valeurs dont la date est supérieure au 01/07/2024.

Dans notre exemple, la formule en F10 est :

= SOMME( NBCAR( C5:C12 ) )

Cette formule compte le nombre de caractères pour chaque cellule de la plage C5:C12 et fait la somme des valeurs.

Attention, selon votre version d’Excel, il faudra peut être valider la formule via ‘ctrl+alt+entrée’.

PDF : 7 méthodes pour gagner 2h/semaine sur Excel

Remplissez le formulaire et découvrez les pratiques qui m’ont permis de réduire jusqu’à 80% ma charge de travail.

PDF – Méthodes – CTA

Prénom

Recevoir mon support PDF

Explications : comprendre les formules matricielles

Les formules matricielles effectuent des traitement sur des plages de données (tableau). Il peut s’agir de plages de cellules ou de constantes de tableau.

On rencontrera souvent deux cas : les opérations entre matrices et une opération simple sur une plage de valeurs.

Les matrices

Une matrice est une liste de valeurs à une ou deux dimensions. En termes Excel, une matrice à une dimension sera une sélection de cellules sur une même ligne ou une même colonne. Une matrice à deux dimensions sera un tableau composé de plusieurs lignes et plusieurs colonnes.

A1:C1  --> matice à une dimension composée de 3 valeurs (en ligne)
A1:A6  --> matrice à une dimension composée de 6 valeurs (en colonne)
A1:C3  --> matrice à 2 dimensions composée de 3x3 valeurs

Les constantes de tableaux permettent également de saisir directement dans une formule une matrice.

={"1"."2"."3"}
--> matice à une dimension composée de 3 valeurs (en ligne)

={"1";"2";"3";"1";"2";"3"}
--> matrice à une dimension composée de 6 valeurs (en colonne)

={"1"."2"."3";"4"."5"."6";"7"."8"."9"}
--> matrice à 2 dimensions composée de 3x3 valeurs

Opérations entre matrices

Excel nous laisse la possibilité d’effectuer des opérations entre plusieurs matrices. Par exemple, nous pouvons effectuer des tests logiques sur une matrice :

= B5:B12 > DATE(2024;7;1)
--> Résultat: {FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI}

Cette formule renvoie une plage de données composées de VRAI/FAUX suivant que le résultat du test se révèle exact ou non.

Il est possible de multiplier la matrice générée par une autre :

= (B5:B12>DATE(2024;7;1)) * (D5:D12)
= ({FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI}) * 
  ({119;492;481;228;416;246;140;179})

Lorsqu’on multiplie des matrices entre elles, Excel transforme les VRAI/FAUX en 1/0. On obtient :

= ({0;0;0;0;0;0;0;1}) * 
  ({119;492;481;228;416;246;140;179})

Ensuite, il multiplie les éléments de chaque matrice :

= {0*119;0*492;0*481;0*228;0*416;0*246;0*140;1*179}
= {0;0;0;0;0;0;179}

Dans notre exemple, nous avons fait la somme de cette matrice :

= SOMME( {0;0;0;0;0;0;179} )
--> Résultat: 179

Le résultat est la somme des valeurs pour tous les articles dont la date est supérieure au 01/07/2024.

Effectuer une opération simple sur une plage de données

L’autre cas rencontré avec les formules matricielles est d’effectuer une opération simple sur une plage de données. Dans notre exemple, nous voulons calculer pour chaque cellule le nombre de caractères dans le code article. La formule pour un article est :

= NBCAR( article )

Plutôt que créer une colonne et ensuite faire la somme, nous pouvons directement obtenir le résultat souhaité en indiquant une plage de données :

= SOMME( NBCAR( C5:C12 ) )
= SOMME( {4;4;5;4;4;5;4;5} )
--> Résultat: 35

Depuis Excel 365, Microsoft a introduit une nouvelle génération de fonctions dites à propagation dynamique (dynamic array functions). Contrairement aux formules matricielles classiques validées par Ctrl+Maj+Entrée, ces fonctions s’étendent automatiquement dans les cellules adjacentes dès qu’on appuie sur Entrée. Voici les 10 incontournables à connaître.

1. FILTRE() — Extraire des données selon des critères

FILTRE, c’est la fonction la plus puissante de cette nouvelle génération. Elle remplace les filtres manuels en retournant dynamiquement les lignes correspondant à une ou plusieurs conditions.

= FILTRE(A2:D20 ; C2:C20="Paris" ; "Aucun résultat")

Un des grands avantages : les résultats se mettent à jour automatiquement dès que les données source changent. Elle accepte aussi des conditions multiples en combinant * (ET) et + (OU) :

= FILTRE(A2:D20 ; (C2:C20="Paris") * (D2:D20>1000) ; "")

2. UNIQUE() — Supprimer les doublons en une formule

UNIQUE extrait automatiquement les valeurs distinctes d’une plage, sans passer par Données > Supprimer les doublons et sans formule matricielle complexe.

= UNIQUE(B2:B100)

Un troisième argument permet même de n’afficher que les valeurs apparaissant une seule fois dans la liste :

= UNIQUE(B2:B100 ; FAUX ; VRAI)

3. TRIER() — Trier une plage dynamiquement

Elle retourne une copie triée d’une plage, sans toucher aux données d’origine. Le tri se met à jour automatiquement si les données changent.

= TRIER(A2:C50 ; 2 ; -1)
→ Trie le tableau par la 2e colonne, en ordre décroissant

4. TRIERPAR() — Trier selon une colonne externe

Variante de TRIER(), TRIERPAR permet de trier un tableau en se basant sur une colonne extérieure à celui-ci, ou selon plusieurs niveaux de tri simultanément.

= TRIERPAR(A2:B20 ; C2:C20 ; -1 ; D2:D20 ; 1)
→ Tri principal décroissant sur C, secondaire croissant sur D

5. SEQUENCE() — Générer une liste de nombres ou de dates

Elle génère automatiquement une série de valeurs consécutives sur une ou plusieurs dimensions, ce qui remplace avantageusement les colonnes auxiliaires numérotées à la main.

= SEQUENCE(12 ; 1 ; DATE(2024;1;1) ; 30)
→ Génère 12 dates espacées de 30 jours à partir du 01/01/2024

6. RECHERCHEX() — Le remplaçant de RECHERCHEV

RECHERCHEX() (ou XLOOKUP en anglais) remplace RECHERCHEV, RECHERCHEH et même la combinaison INDEX/EQUIV. Elle n’impose plus que la colonne de recherche soit à gauche, gère nativement les valeurs non trouvées et peut retourner plusieurs colonnes en une seule formule.

= RECHERCHEX(F2 ; A2:A100 ; B2:D100 ; "Non trouvé")
→ Cherche la valeur de F2 dans la colonne A et retourne les colonnes B, C et D

7. SOMMEPROD() — La matricielle classique toujours indispensable

Présente depuis les anciennes versions, SOMMEPROD() reste l’une des fonctions les plus polyvalentes pour les calculs conditionnels sur des plages. Elle effectue nativement des opérations entre matrices sans nécessiter Ctrl+Maj+Entrée, même sur les versions antérieures à Excel 365.

= SOMMEPROD( (C2:C100="Paris") * (D2:D100) )
→ Somme les valeurs de D uniquement pour les lignes où C = "Paris"

8. DANSCOL() — Aplatir un tableau en une seule colonne

Elle transforme une plage à deux dimensions en une liste verticale. Particulièrement utile pour consolider des données issues de tableaux multi-colonnes avant de les passer à UNIQUE() ou TRIER().

= DANSCOL(A1:D10)
→ Retourne les 40 valeurs du tableau en une seule colonne

9. PRENDRE() et EXCLURE() — Découper un tableau

Ces deux fonctions permettent de sélectionner ou d’éliminer un nombre défini de lignes ou de colonnes en début ou en fin de tableau — idéal pour afficher les N premiers ou derniers résultats d’un FILTRE ou d’un TRIER.

= PRENDRE( TRIER(A2:B50 ; 2 ; -1) ; 5 )
→ Retourne le top 5 d'un tableau trié par ordre décroissant

= EXCLURE(A2:B50 ; 1)
→ Retourne le tableau sans sa première ligne (utile pour ignorer les en-têtes)

10. TABLEAU.ALEA() — Générer des données aléatoires dynamiques

Elle génère un tableau de valeurs aléatoires sur les dimensions choisies. Moins connue que les autres, elle est précieuse pour créer des jeux de données de test, des simulations ou des tirages aléatoires.

= TABLEAU.ALEA(5 ; 3 ; 1 ; 100 ; VRAI)
→ Génère un tableau de 5 lignes × 3 colonnes d'entiers aléatoires entre 1 et 100

Tableau de compatibilité

💡 Bon à savoir : toutes ces fonctions peuvent être combinées entre elles. Par exemple =UNIQUE(FILTRE(A2:A100 ; B2:B100>500)) retourne la liste unique des valeurs de A pour lesquelles B dépasse 500 — en une seule formule, sans colonne intermédiaire.

Pour aller plus loin