Comment construire un DIAGRAMME de PARETO sur EXCEL

Dans ce tutoriel, nous allons voir comment construire rapidement un Diagramme de Pareto, afin de représenter simplement les différentes causes qui impactent un phénomène étudié (analyse des pannes machines, typologie d’une clientèle, étude du chiffre d’affaires d’une entreprise,…).

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Si la vidéo vous plaît, vous pouvez vous abonner à la chaîne Youtube pour ne manquer aucune astuce.

1. Qu’est-ce que le diagramme de PARETO ?

Le diagramme de PARETO est un type de graphique qui permet de mettre en évidence l’importance des différentes causes qui impactent un phénomène.

En d’autres termes, ce document va permettre de définir des règles de priorités sur les actions à mener pour gérer le phénomène étudié, qui peut être par exemple :

• L’étude des pannes d’une machine,
• La typologie d’une clientèle,
• La composition du chiffre d’affaires d’une entreprise,…

Ce graphique repose en effet sur le principe du 20/80 qui énonce que dans de nombreux domaines, seulement 20% des efforts permettent d’engendrer 80% de résultats.

Il convient donc de consacrer une grande partie des ressources disponibles à gérer ces 20% pour obtenir un résultat maximal.

La construction du diagramme de PARETO se décompose en trois étapes :

• Organiser les données
• Traiter les données
• Construire le graphique

2. Organiser les données

Avant de pouvoir organiser des données, il convient évidemment de les recueillir, et pour cela les moyens sont nombreux :

• Relevés de pannes machines,
• Étude statistique,
• Décomposition des éléments de chiffres d’affaires,
• Sondages, …

Attention de ne pas tomber dans le piège courant d’une trop grande variété de données. En effet, au-delà d’une petite dizaine d’éléments étudiés, l’interprétation du document sera rendue très difficile. Il convient alors de regrouper les données par tranches ou par famille.

Pour illustrer ce tutoriel nous disposons de l’exemple suivant :

Nous retrouvons ici un relevé arrêts des machines dans une usine au cours de l’année 2018. Ces arrêts sont exprimés en minutes.

L’entreprise souhaite savoir comment diminuer significativement les temps d’immobilisations de ces machines.

La première chose à faire consiste à organiser les données de manière hiérarchique, en présentant tout d’abord les causes ayant un impact plus important.

Pour cela, nous sélectionnons l’ensemble des données correspondant aux durées d’immobilisation, puis nous effectuons un tri descendant (menu Données > Trier du plus grand au plus petit) :

Excel détecte alors que les données adjacentes à la sélection et propose de les inclure dans le tri, ce que nous souhaitons effectivement faire :

Nous pouvons donc choisir l’option Étendre la sélection, puis cliquer sur le bouton Tri.

À présent, étant donné que nous allons ajouter des colonnes à la suite de ce tableau, nous pouvons ajouter des titres aux colonnes (Causes et Arrêts (mn)) :

3. Traiter les données

Maintenant que nous disposons de nos données, agencées de manière hiérarchique, nous allons calculer les durées d’arrêts machines, cumulées.

Nous commençons par récupérer la durée de la première cellule (« Surchauffe du bloc moteur ») en récupérant simplement la valeur de la cellule correspondante :

 =B9 

Ensuite, pour calculer un cumul, nous reprenons le résultat de la cellule située juste au-dessus, auquel nous ajoutons la durée d’immobilisation de la ligne en cours :

 =C9+B10 

Et enfin, nous pouvons étendre ce calcul aux autres causes d’arrêts de machines, en tirant la poignée de recopie située en bas à droite de la cellule :

Ce qui nous permet de récupérer les durées d’immobilisations hiérarchiques ET cumulées :

Le dernier élément manquant pour construire notre diagramme est le pourcentage cumulé représenté par chacune des causes.

Pour rappel, pour calculer un pourcentage, il suffit de diviser un élément par le total (ce total étant représenté par la dernière ligne de la colonne précédente) :

 =C9/$C$14 

Comme vous pouvez le constater par la présence des symbole dollar (« $ »), la référence à la cellule C14 est dite absolue, afin de pouvoir la figer lorsque nous allons étendre la formule sur les lignes du dessous. Pour tout savoir sur les différents types de références, vous pouvez cliquer sur ce lien.

Et maintenant, comme nous l’avions fait juste avant, nous allons utiliser la poignée de recopie sur les cellules du dessous :

Évidemment, les données affichées ne sont pas très lisibles à ce moment-là. Nous allons donc leur appliquer un format de pourcentage (depuis le menu Accueil du ruban) :

Normalement, la dernière cellule de la colonne doit afficher 100% !

4. Construire le diagramme de PARETO

Maintenant que nos données sont prêtes (triées, et traitées), nous allons pouvoir attaquer la construction du graphique.

Et là, pas de panique Excel a justement un type de graphique tout prêt pour nous !

Pour commencer, nous sélectionnons toutes les données du tableau :

Ensuite, nous allons choisir d’insérer un graphique combiné. Il s’agit d’un type de graphique qui permet de présenter deux types de données, sur deux axes différents. Nous avons étudié ce type de graphique dans un précédent tutoriel que vous retrouverez en suivant ce lien.

Pour insérer ce graphique, rendez-vous dans le menu Insertion du ruban, puis dans le menu Combiné, nous choisissons le deuxième type de graphique : Colonne groupé – Ligne sur l’axe secondaire :

Le graphique inséré ne ressemble pas encore à un diagramme de PARETO.

La raison en est très simple : avant d’insérer le graphique, nous avons sélectionné l’ensemble des colonnes du tableau, y compris la colonne « Cumules (mn) », qui ne sert en fait qu’à permettre le calcul de la dernière colonne « Cumuls (%) ».

Il convient donc d’exclure cette colonne du graphique :

• Sélectionnez une des colonnes directement sur le graphique :

• Puis appuyez simplement sur la touche [Suppr] du clavier afin d’exclure les colonnes du graphique :

Et voilà ! nous disposons maintenant d’un graphique de PARETO.

En revanche, il est encore nécessaire d’apporter quelques améliorations à ce dernier pour le rendre plus lisible.

• Donner un titre au graphique :

• Appliquer un thème au graphique (menu Création de graphique, qui n’apparaît que lorsque le graphique est sélectionné) :

• Réduire l’intervalle entre les barres de l’histogramme (clic-droit sur l’une des barres > Mettre en forme une série de données > dans le menu Options des séries, réduire la largeur de l’intervalle à 10%) :

• Pour le moment, le graphique n’est pas très lisible, tant les libellés prennent de la place. Nous allons donc modifier la manière dont ceux-ci sont affichés (clic sur les libellés, puis dans le menu Formar de l’axe, nous choisissons une Orientation du texte > horizontale) :

Enfin, pour bien situé à quel endroit se trouvent les 80% des conséquences, nous simplement insérer une flèche :

Le point de départ de cette flèche se situe au niveau des 80% de l’axe des pourcentages :

Puis nous jouons avec la flèche d’arrivée et le rond jaune pour que la flèche attérisse sur la courbe des abscisses, avec un angle sur la courbe des pourcentages :

En enfin, nous pouvons personnaliser l’apparence de cette flèche :

Nous pouvons déduire de cette analyse que l’essentiel de l’attention doit être porté sur le problème de surchauffe du bloc moteur, ainsi que sur les problèmes de pannes électriques et sur la formation des utilisateurs.