Guide : Comprendre et Utiliser les Formules Matricielles dans Excel

Les formules matricielles permettent de réaliser des calculs avancés en travaillant sur des plages de données ou des tableaux dans Excel. Elles sont particulièrement utiles pour simplifier les calculs complexes et automatiser les analyses de données.

1. Qu’est-ce qu’une Formule Matricielle ?

Une formule matricielle est une formule qui peut effectuer plusieurs calculs sur un ensemble de données (plages ou matrices) et renvoyer un seul résultat ou plusieurs résultats.

Exemple :

• Sans formule matricielle : Vous additionnez deux colonnes en traitant chaque cellule séparément.
• Avec une formule matricielle : Vous additionnez directement deux colonnes entières en une seule formule.

2. Saisir une Formule Matricielle

Excel Moderne (365 ou 2021) :

• Les formules matricielles sont saisies normalement (sans étapes supplémentaires).
• Excel “renverse” automatiquement les résultats multiples dans des plages adjacentes.

Anciennes Versions :

1. Tapez la formule.
2. Appuyez sur Ctrl + Maj + Entrée (au lieu d’Entrée seul).
3. Excel affichera des crochets {} autour de la formule pour indiquer qu’il s’agit d’une formule matricielle.

3. Formules Matricielles Simples

a. Ajouter Deux Colonnes

Ajoutez les valeurs de deux colonnes et retournez les résultats ligne par ligne :

=A1:A10 + B1:B10

b. Somme des Produits

Multipliez les valeurs correspondantes de deux colonnes, puis additionnez les résultats :

=SOMME(A1:A10 * B1:B10)

4. Formules Matricielles Avancées

a. Trouver des Données avec des Critères Multiples

Calculez la somme de valeurs répondant à plusieurs critères :

=SOMME((A1:A10="Ventes") * (B1:B10>100) * C1:C10)

• Explication :
  • (A1:A10="Ventes") renvoie 1 pour les cellules où “Ventes” est trouvé.
  • (B1:B10>100) renvoie 1 pour les valeurs supérieures à 100.
  • La multiplication combine les critères, et SOMME additionne les résultats.

b. Trouver la Valeur Maximale sous une Condition

Trouver la valeur maximale dans une plage qui correspond à un critère :

=MAX(SI(A1:A10="Ventes", C1:C10))

• Explication :
  • SI(A1:A10="Ventes", C1:C10) crée une matrice des valeurs dans C1:C10 où A1:A10 contient “Ventes”.
  • MAX retourne la plus grande valeur.

c. Extraire des Données Uniques

Créer une liste sans doublons :

=UNIQUE(A1:A10)

• Disponible dans Excel 365 et 2021.

5. Fonctions Associées aux Formules Matricielles

a. SOMMEPROD

• Combine plusieurs plages en les multipliant et retourne leur somme.

=SOMMEPROD(A1:A10, B1:B10)

b. FILTRE

• Retourne uniquement les valeurs correspondant à un critère :

=FILTRE(A1:A10, B1:B10>100)

• Exemple : Toutes les valeurs de A1:A10 où B1:B10 est supérieur à 100.

c. SEQUENCE

• Crée une matrice de nombres en séquence :

=SEQUENCE(5, 1, 1, 1)

• Crée une colonne de 5 nombres, commençant par 1, avec un incrément de 1.

6. Cas Pratiques

a. Analyse de Ventes

• Somme des ventes uniquement pour une catégorie spécifique :

=SOMME(SI(A1:A10="Légumes", B1:B10))

b. Moyenne Pondérée

• Moyenne des valeurs pondérées par une autre plage :

=SOMME(A1:A10 * B1:B10) / SOMME(B1:B10)

c. Recherche avec Plusieurs Critères

• Trouver une valeur correspondant à plusieurs conditions :

=INDEX(C1:C10, EQUIV(1, (A1:A10="Catégorie1") * (B1:B10="Produit1"), 0))

7. Résolution de Problèmes

Erreur #VALEUR!

• Cause : Les plages sélectionnées n’ont pas la même taille.
• Solution : Vérifiez que les plages utilisées dans les formules matricielles ont la même dimension.

Crochets Manquants (Anciennes Versions)

• Cause : La formule n’a pas été validée avec Ctrl + Maj + Entrée.
• Solution : Re-saisissez la formule et validez-la correctement.

8. Avantages des Formules Matricielles

1. Gain de temps : Une seule formule peut effectuer plusieurs calculs.
2. Automatisation : Réduit les erreurs manuelles en consolidant les calculs.
3. Flexibilité : Traite des plages dynamiques et complexes.

9. Bonnes Pratiques

1. Documentez vos formules :
   • Utilisez des commentaires pour expliquer les formules complexes.
2. Testez les résultats :
   • Validez chaque composant de la formule avant de l’appliquer à grande échelle.
3. Utilisez des noms de plage :
   • Nommez vos plages pour rendre les formules plus lisibles.

Cas Pratique : Analyse des Ventes avec Formules Matricielles dans Excel

Problématique :

Vous êtes responsable des ventes et devez analyser les performances de différentes catégories de produits. Vous souhaitez répondre aux questions suivantes en utilisant des formules matricielles dans Excel :

1. Quelle est la somme des ventes pour une catégorie donnée ?
2. Quelle est la moyenne des ventes pour une catégorie donnée ?
3. Quelle est la catégorie ayant réalisé la vente maximale ?
4. Quelle est la liste unique des catégories ?

1. Organisation des Données

Voici vos données, organisées comme suit :

2. Objectifs et Solutions

Objectif 1 : Somme des Ventes pour une Catégorie Donnée

Question :

Quelle est la somme des ventes pour la catégorie Légumes ?

Solution :

Utilisez la formule matricielle suivante :

=SOMME(SI(B2:B7="Légumes", C2:C7))

Explication :

• B2:B7="Légumes" : Crée une matrice contenant VRAI (1) si la catégorie est “Légumes”, sinon FAUX (0).
• C2:C7 : Contient les ventes associées.
• SI : Multiplie les valeurs des ventes par 1 (VRAI) ou 0 (FAUX).
• SOMME : Additionne les ventes filtrées.

Objectif 2 : Moyenne des Ventes pour une Catégorie Donnée

Question :

Quelle est la moyenne des ventes pour la catégorie Fruits ?

Solution :

Utilisez la formule matricielle suivante :

=SOMME(SI(B2:B7="Fruits", C2:C7)) / SOMME(SI(B2:B7="Fruits", 1))

Explication :

• Le numérateur SOMME(SI(B2:B7="Fruits", C2:C7)) calcule la somme des ventes pour “Fruits”.
• Le dénominateur SOMME(SI(B2:B7="Fruits", 1)) compte le nombre de produits dans la catégorie “Fruits”.
• La division retourne la moyenne des ventes.

Objectif 3 : Catégorie de la Vente Maximale

Question :

Quelle est la catégorie ayant généré la vente la plus élevée ?

Solution :

Utilisez la formule suivante :

=INDEX(B2:B7, EQUIV(MAX(C2:C7), C2:C7, 0))

Explication :

• MAX(C2:C7) : Retourne la valeur maximale des ventes.
• EQUIV : Trouve la position de cette valeur dans la colonne des ventes.
• INDEX(B2:B7, ...) : Retourne la catégorie correspondante.

Objectif 4 : Liste Unique des Catégories

Question :

Quelle est la liste unique des catégories ?

Solution (Excel 365 et 2021) :

Utilisez la fonction suivante :

=UNIQUE(B2:B7)

Solution (Anciennes Versions) :

1. Utilisez un filtre avancé :
   • Sélectionnez la colonne des catégories.
   • Allez dans Données > Avancé > Copier dans un autre emplacement.
   • Cochez Sans doublons et choisissez un emplacement de sortie.

3. Résultats dans Excel

Après avoir appliqué ces formules dans Excel, voici vos réponses :

4. Bonus : Dynamiser avec une Liste Déroulante

Objectif :

Permettre à l’utilisateur de choisir une catégorie dans une liste déroulante et afficher les résultats dynamiquement.

Étapes :

1. Créer une Liste Déroulante :
   • Sélectionnez une cellule (ex. D1).
   • Allez dans Données > Validation des données.
   • Choisissez Liste et entrez les catégories uniques.
2. Adapter la Formule pour la Somme : =SOMME(SI(B2:B7=D1, C2:C7))
3. Adapter la Formule pour la Moyenne : =SOMME(SI(B2:B7=D1, C2:C7)) / SOMME(SI(B2:B7=D1, 1))
4. Résultat : Lorsque l’utilisateur sélectionne une catégorie dans D1, les calculs s’ajustent automatiquement.

5. Résumé

Synthèse : Étapes pour Générer le Cas Pratique avec Formules Matricielles

1. Organisation des Données

• Créer une feuille nommée “Données” :
  • Ajouter les colonnes : Produit, Catégorie, et Ventes.
  • Remplir les données avec des exemples (ex. Pomme, Fruits, 100).

2. Ajouter une Feuille pour les Résultats

• Créer une feuille nommée “Résultats” :
  • Ajouter une table avec les colonnes :
    • Objectif : Description de la question (ex. “Somme des ventes pour Légumes”).
    • Résultat : Contient une formule Excel liée aux données.
    • Formule : Explication ou type de formule utilisée (ex. Formule Matricielle).

3. Insérer les Formules

• Somme conditionnelle : Somme des ventes pour une catégorie donnée (ex. Légumes).
• Moyenne conditionnelle : Moyenne des ventes pour une catégorie donnée (ex. Fruits).
• Valeur maximale conditionnelle : Catégorie associée à la vente maximale.
• Liste unique des catégories : Générer une liste sans doublons.

4. Appliquer des Styles

• Ajouter un style distinct pour les en-têtes des tableaux :
  • Texte en gras, couleur de fond bleue, texte blanc.
• Ajuster automatiquement la largeur des colonnes pour une meilleure lisibilité.

5. Tester les Formules

• Vérifier que les formules renvoient les résultats corrects en se basant sur les données fournies.

Autres articles

Calculer un écart-type dans Excel

L’écart-type est une mesure de la dispersion des données autour...

Read more

Guide : Validation des Données dans Excel

La validation des données dans Excel est une fonctionnalité puissante...

Read more

Guide : Formules Matricielles dans Excel pour...

Les formules matricielles dans Excel pour Mac permettent de traiter...

Read more