La construction d'un diagramme de Pareto n'est pas très compliquée et repose sur 2 tâches distinctes
- Il faut tout d'abord présenter les valeurs numériques d'une façon très précise
- Ensuite, il faut construire un graphique constitué de 2 séries ; un histogramme et une courbe
Pourquoi faire un diagramme de Pareto ?
Un diagramme de Pareto vous permet de visualiser en un coup d’œil, les facteurs ou les causes qui agissent favorablement ou non sur un résultat.
Pour clarifier, nous allons partir du tableau suivant qui retrace les réponses à un questionnaire pour déterminer les facteurs qui contribuent à avoir de nouveaux clients.
En regardant les résultats, nous voyons que la réputation de l'établissement est la première des raisons. Mais est-ce que les autres facteurs jouent-ils également un rôle ?
Seul un diagramme de Pareto pourra nous répondre 😊😉
En effet, le cumul des facteurs qui totalisent 80% sont considérés comme significatif. Nous allons donc réaliser un graphique qui nous permettra de mettre en évidence les facteurs significatifs avec une couleur différente.
Notez que sur le graphique précédent, les couleurs des premiers histogrammes non pas été modifiées manuellement. Elles apparaissent automatiquement en fonction des valeurs lues.
Construction des formules
Etape 1 : Tri des données en ordre décroissant
Tout d'abord, il faut trier les données par ordre décroissant.
- Mettez votre cellule active dans l'une des cellules de votre Tableau
- Activez le menu Données>Trier
- Choisissez la colonne de tri Réponse
- Puis l'ordre de tri Du plus grand au plus petit
Les données sont maintenant présentées de la façon suivante
Etape 2 : Calcul de la somme
Ici, rien de compliquer. Il faut juste calculer la somme des réponses en utilisant la fonction Somme.
Dans notre classeur, la formule s'écrit :
=SOMME(B4:B10)
Etape 3 : Calcul des pourcentages
Nous allons nous servir de la somme calculée à l'étape précédente pour calculer le pourcentage de chaque réponse. Le but c'est de mesurer le "poids" que représente chaque réponse dans le total.
Pour le premier pourcentage, nous écrivons la formule :
=B4/$B$11
Etape 4 : Recopie de la formule
Maintenant, il faut recopier cette formule pour les autres cellules. Cette opération se fait très simplement avec l'une des techniques de recopie d'une cellule.
C'est pour cette raison que nous avions entouré la référence B11 (le calcul de la somme) par des $. Les dollars verrouillent la référence d'une cellule pour éviter que les références changent lors d'une recopie. Pour plus d'exemples sur les références verrouillées ou non, vous pouvez lire l'article suivant.
Nous pouvons également changer le format des nombres pour afficher le symbole pourcentage.
Etape 5 : Calcul du cumul des pourcentages
La dernière étape (et oui déjà 😍) consiste à effectuer la somme cumulative des pourcentages. Pour cela nous allons utiliser la technique utilisée dans cet article pour obtenir le résultat final.
Création du graphique de Pareto
Nous allons maintenant nous servir de ces données pour construire le diagramme de Pareto. Et pour le créer plus facilement, il est recommandé de sélectionner toutes vos données avant d'insérer le graphique.
Graphiques recommandés
Depuis Excel 2013, une fonctionnalité très intelligente permet de "deviner" le meilleur graphique adapté à vos données. Il s'agit du menu Insérer > Graphiques recommandés.
Comme un diagramme de Pareto est un graphique "standard", Excel n'a pas de mal à comprendre que vos données correspondent à ce type de graphique.
C'est pourquoi Excel vous présente en second choix dans la liste des graphiques recommandés, un graphique qui ressemble très exactement à un diagramme de Pareto. C'est-à-dire :
- Un histogramme pour les résultats collectés lors du sondage
- Une courbe qui montre le cumul des pourcentages
Certes tout n'est pas parfait avec ce graphique et quelques ajustement seront nécessaires. Mais, grâce à cette option, les étapes 1 et 3 expliqués ci-dessous seront déjà automatiquement réalisées 😍😉😎
Etape 1 : Création de l'histogramme
Si vous ne passer par l'assistant de graphique recommandé, voici toutes les étapes détaillées.
Commencer par activer le menu Insérer > Histogramme > Histogramme groupé
Le graphique se présente comme ceci
Etape 2 : Suppression de la série "Pourcentage"
Dans un diagramme de Pareto, la colonne du calcul des pourcentages n'est pas nécessaire. Nous allons donc la retirer de notre graphique
- Clic-droit dans le graphique
- Menu Sélectionner les données
- Décochez la série Pourcentage
Etape 3 : Changer la série Cumul en courbe
Maintenant, nous allons nous focaliser sur la série Cumul pour transformer l'histogramme en une courbe.
- Faîtes un clic-droit sur le graphique
- Activez le menu Modifier le type de graphique
- Dans le menu gauche, sélectionnez le menu Graphique combiné
- Pour la série Cumul vous
- Sélectionnez le type de graphique Courbe
- Cochez la case axe secondaire
Etape 4 : Ajout des étiquettes de données
Il y a plusieurs façon de rajouter les étiquettes à un graphique dans Excel mais le plus rapide c'est
- Activer le graphique
- Se déplacer vers la droite et cliquez sur le + pour afficher les options du graphique
- Cocher la case Étiquettes de données
Etape 5 : Changez la mise en forme
Il ne nous reste plus qu'à améliorer la mise en forme des barres et de la courbe pour finaliser notre diagramme.
Pour sélectionnez facilement un élément d'un graphique, le plus simple c'est
- Aller dans le menu Format
- Sur la gauche, activer le menu déroulant
- Sélectionner l'élément que vous voulez modifier
- Cliquer le sur le bouton Mise en forme de la sélection
Pour l'histogramme il faut changer les paramètres suivants
- Options des séries
- Largueur de l'intervalle : 0%
- Superposition des séries : 0%
- Remplissage
- Couleur : bleue (c'est le plus souvent la couleur utilisé pour un diagramme de Pareto)
- Bordure
- Trait plein
- Couleur : noire
- Épaisseur : 0,75pt
Pour la courbe
- Remplissage du trait
- Couleur : rouge (c'est le plus souvent la couleur utilisé pour un diagramme de Pareto)
- Largeur : 1,5pt
- Lissage coché (l'option tout en bas)
- Marqueur
- Couleur : Rouge
- Type : Losange
- Taille : 8
Changez le titre du graphique et vous obtenez maintenant le graphique suivant
Changez la couleur des barres représentant 80%
Le propre d'un diagramme de Pareto c'est d'afficher la part qui représente 80% des données. Ceci peut se faire manuellement mais lors du rafraîchissement toute la mise en forme du graphique sera à refaire.
Il est donc préférable de transformer le graphique pour que la couleur des barres changent automatiquement en fonction des données. Voici la méthode.
Etape 1 : Ajouter une colonne conditionnelle
Nous allons commencer par ajouter une colonne qui va afficher les données qui sont inférieures ou égales au 80% du cumul. Nous allons bien évidemment utiliser la fonction SI pour faire ce travail.
Mais attention, la formule n'est pas aussi simple qu'il n'y parait.
En effet, si nous écrivons la formule suivante nous n'inclurons jamais l'élément qui atteint 80% (ou 0,8).
=SI(D4<=0,8;B4;"")
Nous devons donc enrichir le test de façon à
- Déterminer quand la cellule de la colonne précédente est inférieure à 80%.
- Ou alors, si la valeur de la colonne précédente est supérieure à 80% il faut que la cellule précédente soit elle inférieure à 80%
=SI(OU(D4<=0,8;ET(D3<=0,8;D4>0,8));B4;"")
Etape 2 : Ajouter la nouvelle série dans le graphique
Pour rajouter une série dans n'importe quel graphique et pour n'importe quelle version d'Excel, il suffit de
- Copier la plage de données qui contient les valeurs à rajouter au graphique sans le titre de la série
- Sélectionner le graphique
- Coller (Ctrl + V) 😉
Etape 3 : Changer la série en histogramme
Dans l'étape 3 de la création du graphique, nous avions vu comment changer d'axe et de type de graphique facilement. Nous allons reprendre cette méthode pour
- Mettre la série sous forme d'histogramme
- Ne pas mettre la série en axe secondaire
Le graphique se présente maintenant de la façon suivante (l'écartement entre les histogrammes a été initialisé).
Etape 4 : Superposition des histogrammes
C'est maintenant tout l'astuce 💡. Nous allons superposer les histogrammes afin que la nouvelle série soit au-dessus de la précédente.
- Sélectionner la nouvelle série
- Dans les options de la série
- Superposition : 100%
- Largeur de l'intervalle : 0%
Etape 5 : Suppression des étiquettes
Cette étape n'est pas la plus compliquée mais elle ne doit pas être omise.
Il est important de supprimer les étiquettes de la nouvelle série. Elles n'apportent aucune information supplémentaire car elles sont rigoureusement identique à la colonne B.
- Sélectionnez la nouvelle série
- Positionnez vous sur le menu des options du graphique
- Décochez l'option Étiquettes de données
Etape 6 : Changez les couleurs pour terminer le diagramme
Je vous laisse révéler vos talents de graphiste pour changer les couleurs des histogrammes et de la courbe. Moi, je vous propose ce résultat.
