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.

Excel formation - diagramme de PARETO - 01

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.

Excel formation - diagramme de PARETO - 02

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, …

Excel formation - diagramme de PARETO - 03

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.

Excel formation - diagramme de PARETO - 04

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

Excel formation - diagramme de PARETO - 05

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 formation - diagramme de PARETO - 06

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 :

Excel formation - diagramme de PARETO - 07

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

Excel formation - diagramme de PARETO - 08

À 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)) :

Excel formation - diagramme de PARETO - 09

 

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 

 

Excel formation - diagramme de PARETO - 10

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 

 

Excel formation - diagramme de PARETO - 11

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 :

Excel formation - diagramme de PARETO - 12

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

Excel formation - diagramme de PARETO - 13

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 

 

Excel formation - diagramme de PARETO - 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 :

Excel formation - diagramme de PARETO - 15

É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) :

Excel formation - diagramme de PARETO - 16

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 :

Excel formation - diagramme de PARETO - 17

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 :

Excel formation - diagramme de PARETO - 18

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 :

Excel formation - diagramme de PARETO - 19

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

Excel formation - diagramme de PARETO - 20

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 :

Excel formation - diagramme de PARETO - 21

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

Excel formation - diagramme de PARETO - 22

  • 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%) :

Excel formation - diagramme de PARETO - 23

  • 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) :

Excel formation - diagramme de PARETO - 24

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

Excel formation - diagramme de PARETO - 25

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

Excel formation - diagramme de PARETO - 26

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 :

Excel formation - diagramme de PARETO - 27

Excel formation - diagramme de PARETO - 28

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

 

Excel formation - diagramme de PARETO - 29

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.



Articles qui pourraient vous intéresser

Tout savoir sur les procédure SUB en VBA [#06 FORMATION EXCEL VBA COMPLETE]

Focus sur les variables en VBA [#05 FORMATION EXCEL VBA COMPLETE]

Comment évaluer une formule sur Excel : le déroulement pas à pas

Les tableaux croisés dynamiques, c’est facile !

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.