Regarder la vidéo - Comment créer un graphique de Pareto dans Excel
Le diagramme de Pareto est basé sur le principe de Pareto (également connu sous le nom de règle des 80/20), qui est un concept bien connu dans la gestion de projet.
Selon ce principe, environ 80% des problèmes peuvent être attribués à environ 20% des problèmes (ou environ 80% de vos résultats pourraient être le résultat direct d'environ 20% de vos efforts, et ainsi de suite…).
La valeur du pourcentage de 80/20 peut varier, mais l'idée est que de tous les problèmes/efforts, il y en a quelques-uns qui ont un impact maximum.
Il s'agit d'un concept largement utilisé dans la gestion de projet pour prioriser le travail.
Création d'un graphique de Pareto dans Excel
Dans ce tutoriel, je vais vous montrer comment faire un :
- Graphique de Pareto simple (statique) dans Excel.
- Graphique de Pareto dynamique (interactif) dans Excel.
Créer un graphique de Pareto dans Excel est très simple.
Toute la supercherie est cachée dans la façon dont vous organisez les données dans le backend.
Prenons l'exemple d'un hôtel pour lequel les données des réclamations pourraient ressembler à quelque chose comme indiqué ci-dessous :
REMARQUE : pour créer un graphique de Pareto dans Excel, vous devez disposer les données par ordre décroissant.
Création d'un graphique de Pareto simple (statique) dans Excel
Voici les étapes pour créer un graphique de Pareto dans Excel :
- Configurez vos données comme indiqué ci-dessous.
- Calculez le % cumulé dans la colonne C. Utilisez la formule suivante : =SUM($B$2:B2)/SUM($B$2:$B$1)
- Sélectionnez l'ensemble de données complet (A1:C10), accédez à Insertion -> Graphiques -> Colonne 2-D -> Colonne groupée. Cela insère un graphique à colonnes avec 2 séries de données (nombre de plaintes et pourcentage cumulé).
- Cliquez avec le bouton droit sur l'une des barres et sélectionnez Modifier le type de graphique de série.
- Dans la boîte de dialogue Modifier le type de graphique, sélectionnez Combo dans le volet de gauche.
- Effectuez les modifications suivantes :
- Nombre de plaintes : colonne groupée.
- % cumulé : ligne (cochez également la case Axe secondaire).
[Si vous utilisez Excel 2010 ou 2007, ce sera un processus en deux étapes traiter. Tout d'abord, remplacez le type de graphique par un graphique en courbes. Cliquez ensuite avec le bouton droit sur le graphique en courbes et sélectionnez Formater la série de données et sélectionnez Axe secondaire dans les options de série]
- Votre graphique de Pareto dans Excel est prêt. Ajustez les valeurs de l'axe vertical et le titre du graphique.
Comment interpréter ce graphique de Pareto dans Excel
Ce diagramme de Pareto met en évidence les enjeux majeurs sur lesquels l'hôtel doit se concentrer pour trier le maximum de réclamations. Par exemple, cibler les 3 premiers problèmes traiterait automatiquement environ 80 % des plaintes.
Par exemple, cibler les 3 premiers problèmes traiterait automatiquement environ 80 % des plaintes.
Création d'un graphique de Pareto dynamique (interactif) dans Excel
Maintenant que nous avons un graphique de Pareto statique/simple dans Excel, allons un peu plus loin et rendons-le un peu interactif.
Quelque chose comme indiqué ci-dessous :
Dans ce cas, un utilisateur peut spécifier le % de plaintes à traiter (à l'aide de la barre de défilement Excel), et le graphique mettra automatiquement en évidence les problèmes à examiner.
L'idée ici est d'avoir 2 bars différents.
Le rouge est mis en surbrillance lorsque la valeur en pourcentage cumulée est proche de la valeur cible.
Voici les étapes pour créer ce graphique de Pareto interactif dans Excel :
- Dans la cellule B14, j'ai la valeur cible qui est liée à la barre de défilement (dont la valeur varie de 0 à 100).
- Dans la cellule B12, j'ai utilisé la formule =B14/100. Comme vous ne pouvez pas spécifier de valeur en pourcentage pour une barre de défilement, nous divisons simplement la valeur de la barre de défilement (en B14) par 100 pour obtenir la valeur en pourcentage.
- Dans la cellule B13, entrez la combinaison suivante de fonctions INDEX, MATCH et IFERROR :
=SIERREUR(INDICE($C$2:$C$10,SIERREUR(MATCH($B$12,$C$2:$C$10,1),0)+1),1)
Cette formule renvoie la valeur cumulée qui couvrirait la valeur cible. Par exemple, si la valeur cible est de 70 %, elle renverra 77 %, indiquant que vous devriez essayer de résoudre les trois premiers problèmes.
- Dans la cellule D2, entrez la formule suivante (et faites glisser ou copiez pour toutes les cellules - D2:D10) :
=SI($B$13>=C2,B2,NA()) - Dans la cellule E2, entrez la formule suivante (et faites glisser ou copiez pour toutes les cellules - E2:E10) :
=SI($B$13<> - Sélectionnez les données dans les colonnes A, C, D et E (appuyez sur Ctrl et sélectionnez à l'aide de la souris).
- Allez dans Insertion -> Graphiques -> Colonne 2D -> Colonne en cluster. Cela insérera un histogramme avec 3 séries de données (pourcentage cumulé, les barres à mettre en évidence pour atteindre la cible et toutes les autres barres restantes)
- Cliquez avec le bouton droit sur l'une des barres et sélectionnez Modifier le type de graphique de série.
- Dans la boîte de dialogue Modifier le type de graphique, sélectionnez Combo dans le volet de gauche et effectuez les modifications suivantes :
- % cumulé : ligne (cochez également la case Axe secondaire).
- Barres en surbrillance : colonne groupée.
- Barres restantes : colonne groupée.
- Cliquez avec le bouton droit sur l'une des barres en surbrillance et changez la couleur en rouge.
C'est ça!
Vous avez créé un graphique de Pareto interactif dans Excel.
Désormais, lorsque vous modifiez la cible à l'aide de la barre de défilement, le graphique de Pareto se met à jour en conséquence.
Utilisez-vous le graphique de Pareto dans Excel ?
J'aimerais avoir votre avis sur cette technique et comment vous l'avez utilisée. Laissez vos empreintes dans la section commentaires
- Analyser les plaintes des restaurants à l'aide du graphique de Pareto.
- Création d'un diagramme de Gantt dans Excel.
- Création d'un graphique Milestone dans Excel.
- Création d'un histogramme dans Excel.
- Modèle de calculatrice de feuille de temps Excel.
- Modèle de suivi des congés des employés.
- Calcul de la moyenne pondérée dans Excel.
- Création d'une courbe en cloche dans Excel.
- Graphiques Excel avancés
- Comment ajouter un axe secondaire dans les graphiques Excel
