La matrice MAGIQUE qui révèle tous les problèmes de votre entreprise dans Excel (en 5 minutes)
Dans ce tutoriel, je vais vous montrer comment transformer un simple tableau d’incidents en une carte visuelle dans Excel, qui se met à jour automatiquement dès que nous ajoutons une nouvelle ligne.
Nous allons partir de zéro, comme si nous découvrions Excel ensemble, et pas à pas nous allons construire un outil que nous pourrons utiliser dans n’importe quel magasin, service ou équipe pour suivre les problèmes et les prioriser rapidement.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons un relevé des incidents d’un magasin (problèmes de caisse, ruptures de stock, plaintes clients, etc.) :
Dans ce tableau, les colonnes fréquence et gravité sont des nombres entre 1 et 5, où 1 signifie très faible et 5 très élevé.
Par exemple, pour la Fréquence, une valeur de 1 signifie que l’incident se produit de manière très exceptionnelle, tandis que 5 signifie que l’incident est quasi quotidien.
Pour commencer, nous allons transformer ce bloc en « tableau structuré », c’est-à-dire un tableau évolué qu’Excel reconnaît officiellement comme une base de données, avec son propre nom et des en-têtes clairement identifiés et surtout avec une série de fonctionnalités qui vont nous simplifier la vie.
Un autre gros avantage, c’est que le tableau structuré s’étend automatiquement. Si nous ajoutons un nouvel incident dans notre base, il sera automatiquement intégré au tableau, et toutes les formules qui font référence au tableau structuré le prendront en compte sans aucune modification.
Pour effectuer cette transformation, nous sélectionnons l’une des cellules de la plage, puis nous nous rendons dans l’onglet « Accueil » et nous cliquons sur « Mettre sous forme de tableau ».
Nous aurions également pu utiliser le raccourci clavier [Ctrl]+[L].
Dans la fenêtre qui s’ouvre, nous vérifions que la plage est correcte, et nous cochons la case « Mon tableau comporte des en-têtes », et enfin nous validons en cliquant sur « OK ».
Nous voyons maintenant qu’Excel applique un style au tableau, avec des flèches de filtre dans les en-têtes.
Nous nous rendons dans l’onglet « Création de tableau » (ou « Tableau » selon la version) et nous donnons un nom explicite au tableau dans la zone « Nom du tableau », par exemple « Incidents ».
Ce nom va nous permettre d’écrire des formules beaucoup plus simples, du type « Incidents[Gravité] » au lieu de « $E$2:$E$11 », ce qui est plus lisible et moins source d’erreurs, surtout lorsque nous débutons.
2. La matrice 5x5 de la carte des incidents
Maintenant que notre base de données est prête, nous allons utiliser la matrice 5x5 qui va accueillir nos incidents.
L’idée est très simple : les lignes représentent la Fréquence, les colonnes représentent la Gravité, et chaque cellule affichera la liste des incidents correspondants.
3. Remplir automatiquement la matrice avec TEXTE.CONCAT, puces et sauts de ligne
Nous allons maintenant écrire la formule qui va faire toute la magie : afficher, dans chaque cellule de la matrice, la liste des incidents dont la Fréquence et la Gravité correspondent aux valeurs de la ligne et de la colonne.
Nous allons aussi ajouter des puces et des sauts de ligne pour rendre le résultat lisible.
Pour gagner du temps, nous allons utiliser mon outil « IA Excelformation.fr », mais évidemment nous pourrions également le faire à la main.
Nous sélectionnons la cellule J8, et nous nous rendons dans le menu « IA Excelformation.fr » afin de cliquer sur le bouton > « Créer une formule » :
Puis, dans la boîte qui s’affiche, nous pouvons décrire notre besoin en français, par exemple « Liste les titres des incidents du tableau Incidents dont la Fréquence correspond à la cellule I8 et la Gravité à la cellule J7, séparés par des retours à la ligne avec des puces. En cas d’erreur, n’affiche rien. ».
Ensuite, nous validons en appuyant sur le bouton [OK].
L’IA va alors nous proposer une formule prête à l’emploi que nous pouvons adapter si nécessaire.
Il est possible que la formule retournée diffère légèrement de celle que je viens d’obtenir, ce n’est pas important tant que le résultat affiché est correct.
N’hésitez pas à recommencer l’opération si vous n’êtes pas satisfait du résultat.
Maintenant, revenons sur cette formule ci pour que vous puissiez l’utiliser même si vous n’avez pas IA Excelformation.fr.
Celle-ci utilise les nouvelles fonctions dynamiques d'Excel 365, et surtout elle évite les longues formules SI qui peuvent être lourdes à calculer.
Décortiquons-la étape par étape pour bien comprendre chaque partie.
=SIERREUR(JOINDRE.TEXTE(CAR(10);VRAI;"• &FILTRE($B$7:$B$16;($D$7:$D$16=$I8)*($E$7:$E$16=J$7));"")
3.1. La structure globale : SIERREUR
La formule est enveloppée dans SIERREUR, qui fonctionne comme un filet de sécurité.
SIERREUR teste une formule, et si elle génère une erreur, elle affiche un texte personnalisé à la place.
Ici, si jamais aucune ligne ne correspond à nos critères, FILTRE renverra une erreur, et SIERREUR affichera alors une cellule vide ("").
Sans SIERREUR, nous aurions une erreur visible dans la cellule, ce qui casserait l'aspect propre de notre matrice.
3.2. JOINDRE.TEXTE : assembler les résultats
Ensuite, nous retrouvons la fonction JOINDRE.TEXTE, laquelle est une sorte d’équivalent moderne et puissant de TEXTE.CONCAT.
Elle prend plusieurs textes (ou une plage) et les assemble en une seule chaîne avec un séparateur.
Les trois premiers arguments sont :
- CAR(10) : c’est le caractère de retour à la ligne (comme un [Entrée] dans la cellule)
- VRAI : permet d’ignorer les cellules vides
- " • "&FILTRE(...) : est le texte à assembler, que nous allons maintenant détailler
3.3. FILTRE : la vraie magie
FILTRE est la fonction star ici.
Elle agit comme un filtre automatique qui ne garde que les lignes répondant à nos critères.
FILTRE(plage_à_filtrer ; condition1*condition2)
Dans notre cas :
FILTRE($B$7:$B$16 ; ($D$7:$D$16=$I8)*($E$7:$E$16=J$7))
Explication des plages :
- $B$7:$B$16 : ce sont les titres des incidents (colonne B)
- $D$7:$D$16 : les fréquences (colonne D)
- $E$7:$E$16 : les gravités (colonne E)
- $I8 : la fréquence de la ligne courante
- J$7 : la gravité de la colonne courante
Puis, les conditions :
- ($D$7:$D$16=$I8) teste chaque fréquence du tableau = fréquence de la ligne
- ($E$7:$E$16=J$7) teste chaque gravité du tableau = gravité de la colonne
Le signe « * » (multiplié) entre les deux correspond à l’opérateur logique « ET » qui permet de s’assurer que les deux conditions sont obligatoirement vraies.
Devant FILTRE, nous avons "• "& qui ajoute une puce au début de chaque titre filtré.
3.4. Le déroulement complet
Pour résumer dans cette formule, la fonction FILTRE examine chaque ligne du tableau afin de ne conserver que les titres avec Fréquence=$I8 ET Gravité=J$7, puis ajoute la puce devant chaque titre sélectionné.
La fonction JOINDRE.TEXTE assemble le tout avec CAR(10) entre chaque.
Et enfin SIERREUR affiche "" si aucune ligne ne correspond aux critères de filtre.
3.5. Amélioration de la formule
Avant d’aller plus loin, nous allons opérer une petite amélioration sur la formule, afin de profiter des avantage du tableau structuré que nous avons généré un au début de la vidéo.
Nous nous plaçons donc dans la barre des formules, nous sélectionnons la première plage des cellules du tableau que nous identifions grâce au code couleur (ici la plage B7 à B16 en bleu) :
Puis, nous la remplaçons par son équivalent littéral, en cliquant au-dessus de l’en-tête de la colonne « titre » dans le tableau (lorsque le curseur prend la forme d’une flèche pointant vers le bas :
$B$7:$B$16 devient alors « Incidents[Titre] ».
Puis nous répétons l’opération pour les deux autres colonnes :
=SIERREUR(JOINDRE.TEXTE(CAR(10);VRAI;"• "&FILTRE(Incidents[Titre];(Incidents[Fréquence]=$I8)*(Incidents[Gravité]=J$7)));"")
Notre formule est maintenant totalement dynamique.
Ensuite, nous allons recopier cette formule dans toute la matrice en sélectionnant toutes les cellules (J8 jusqu’à N12) et en validant la formule en appuyant sur [Ctrl]+[Entrée].
Grâce aux signes « $ » dans la formule, Excel va adapter automatiquement les références :
Nous obtenons ainsi une matrice où chaque cellule affiche, sous forme de liste à puces, les incidents qui correspondent à la combinaison Fréquence / Gravité.
Si nous modifions la Fréquence d’un incident dans le tableau source, nous verrons immédiatement cet incident « se
À partir de là, nous pouvons utiliser notre carte des incidents pour animer des réunions, visualiser les problèmes prioritaires et décider des actions à mener, sans avoir besoin de parcourir ligne par ligne notre tableau source.
Et dès que nous ajoutons un incident en bas du tableau « Incidents », la matrice se met à jour automatiquement, sans aucune intervention supplémentaire.
