I. Introduction▲
Le diagramme de Pareto est un outil statistique qui permet d'identifier l'importance
relative de chaque catégorie dans une liste d'enregistrements, en comparant
leur fréquence d'apparition.
Un diagramme de Pareto est mis en évidence lorsque 20 % des catégories produisent 80 % du nombre
total d'effets.
Cette méthode permet donc de déterminer rapidement quelles sont les priorités d'actions. Si on
considère que 20 % des causes représentent 80% des occurrences, agir sur ces 20 % aide à
solutionner un problème avec un maximum d'efficacité.
En préparant leurs ambitions, les managers expérimentés savent que seuls quelques éléments
majeurs sont décisifs. Le reste sera traité par la même occasion en tant que parties de ces éléments.
Joseph Moser Juran - 1964
Un diagramme de Pareto est généralement présenté sous forme d'histogramme en colonne. Les
catégories sont affichées en abscisses, par ordre décroissant de fréquence. Le nombre d'apparition
est indiqué sur l'axe des ordonnées. Une courbe en ligne contenant les valeurs cumulées complète
le diagramme.
Le diagramme de Pareto est également appelé :
* Méthode "ABC".
* Règle des 80/20.
L'exemple présenté dans cet article a été mis en oeuvre dans Excel 2007.
Le principe reste identique sous les versions antérieures.
Il existe bien entendu d'autres solutions Excel pour visualiser un diagramme de Pareto.
Cette démo reposait sur plusieurs attendues :
* Ne pas utiliser de VBA.
* Automatiser au maximum le classeur pour que l'utilisateur
ait simplement à renseigner les données collectées.
* Détailler les formules pour expliquer le principe de calcul.
* Afficher les valeurs dans un graphique pour obtenir un résultat
visuel.
* Pouvoir réutiliser directement le classeur démo pour
d'autres analyses.
Le classeur démo montre également comment :
* Extraire des données sans doublons puis les trier, par formule.
* Créer des plages nommées dynamiques dans Excel 2007.
* Utiliser ces noms dans des graphiques Excel 2007.
II. La collecte des données▲
La collecte des données est une action essentielle car toute l'analyse va être basée sur la
validité des informations recueillies.
Il convient donc de vérifier :
* La justesse des périodes de mesures.
* La véracité des enregistrements.
* Les unités de mesure identiques pour l'ensemble de la source de données.
* La cohérence des catégories.
* Le regroupement des causes mineures en une seule catégorie "Divers".
L'exemple proposé dans la feuille 'Collecte' (voir le classeur en téléchargement à la fin de cette
page) liste les causes d'immobilisation d'une machine de production. Un nombre
d'heures d'arrêt, mesuré sur une année, est associé à chaque catégorie. L'objectif final est
d'identifier les causes principales d'arrêt et de savoir sur quelles catégories
agir en priorité pour augmenter le taux de rendement de l'installation.
Si vous souhaitez réutiliser le classeur pour votre projet, supprimez les données existantes
dans la feuille 'Collecte', à partir de la deuxième ligne, puis insérez vos valeurs.
La colonne A contient les catégories (les causes).
Insérez les éléments à la suite dans la feuille, sans ligne vide.
Ne vous préoccupez pas des calculs ou des tris préalables. L'application va s'en charger
automatiquement comme vous le découvrirez dans la suite du tutoriel.
Pensez juste à regrouper les catégories mineures en une seule catégorie "Divers" afin de ne
pas polluer l'analyse par des données non représentatives.
La colonne B contient le nombre d'occurrences pour chaque catégorie.
Indiquez les valeurs en prenant soins de conserver une seule unité (Par exemple, ne mélangez
pas des kilos avec des tonnes).
Attention à ne pas utiliser plusieurs libellés pour une même cause.
Excel dispose d'une fonctionnalité dans le menu contextuel des cellules afin de choisir
rapidement parmi les élément existants dans les cellules contigües d'une colonne :
Clic droit dans la première cellule vide de la colonne / Liste déroulante de choix.
Nota :
Si vous disposez d'une liste brute de catégories sans comptage d'occurrences,
placez simplement les groupes dans la colonne A et indiquez 1 dans chaque cellule
de la colonne B.
III. Les calculs▲
Toutes les opérations sont réalisées dans la feuille 'Calculs'.
Ce chapitre décrit en détail le processus qui va permettre d'obtenir le résultat définitif.
III-A. Lister les catégories sans doublons▲
La première phase consiste à extraire la liste des catégories sans doublons,
de la feuille 'Collecte'.
La formule ci-dessous est saisie dans la cellule A2 (colonne 'IndexUniques') puis étirée
vers le bas :
=SI(Collecte!A2="";"";SI(NB.SI(Collecte!A$2:A2;Collecte!A2)>1;"";MAX($A$1:$A1)+1))
Ainsi, vous repérez et indexez la position de chaque catégorie de manière unique.
Important :
Cette formule doit être recopiée dans autant de lignes qu'il y a de données dans la
feuille 'Collecte' afin de pouvoir prendre en compte toutes les catégories.
Dans la cellule B2 (colonne 'Liste catégories'), est saisie la formule :
=SI(LIGNES($A$2:$A2)>MAX(IndexUniques);"";DECALER(Collecte!$A$2;EQUIV(LIGNES($A$2:$A2);IndexUniques;0)-1;0))
Vous obtenez la liste des catégories sans doublons.
Remarquez dans la formule, l'utilisation d'une plage nommée 'IndexUniques' qui
représente la colonne A dans la feuille 'Calculs'.
Procédez de la manière suivante pour créer le nom :
Onglet "Formules" dans le ruban.
Bouton "Gestionnaire de noms" dans le groupe "Noms définis".
Bouton "Nouveau".
"IndexUniques" est saisi dans le champ "Nom". C'est la référence qui sera utilisée dans la cellule B2.
La formule =DECALER(Calculs!$A$2;;;NBVAL(Collecte!$A:$A)-1;1) est indiquée dans le
champ "Fait référence à:".
Le bouton OK permet de valider le nom.
La fonction DECALER peut être résumée ainsi dans notre exemple :
DECALER(Cellule de base;;;Nombre de lignes;Nombre de colonnes)
Le nombre de lignes de la plage nommée est dépendant de la
partie NBVAL(Collecte!$A:$A)-1.
Elle compte le nombre de données
dans la colonne A de la feuille 'Collecte'.
La formule DECALER permet donc la mise en oeuvre d'une plage nommée dynamique, c'est à
dire qui sera redéfinie automatiquement en fonction du nombre de données dans la feuille
'Collecte'.
Les autres fonctions du classeur utilisent également des plages nommées dynamiques.
Les mises à jour sont donc automatisées et l'utilisateur n'a pas besoin
de modifier toutes les formules quand le nombre et le contenu des données changent.
Nota
C'est volontairement que la plage nommée 'IndexUniques' est basée sur le nombre de lignes
de la feuille 'Collecte'. Ce choix limite le risque d'oublier des catégories dans la source de
données.
Le tableau suivant récapitule les noms et les références utilisés dans le classeur démo :
| Nom | Référence | Formule |
|---|---|---|
| Categories | Feuille Collecte / colonne A | =DECALER(Collecte!$A$2;;;NBVAL(Collecte!$A:$A)-1;1) |
| Occurences | Feuille Collecte / colonne B | =DECALER(Collecte!$B$2;;;NBVAL(Collecte!$B:$B)-1;1) |
| IndexUniques | Feuille Calculs / colonne A | =DECALER(Calculs!$A$2;;;NBVAL(Collecte!$A:$A)-1;1) |
| NbOccurences | Feuille Calculs / colonne C | =DECALER(Calculs!$C$2;;;SOMMEPROD((Calculs!$C$1:$C$100<>"")*1)-1;1) |
| RangUnique | Feuille Calculs / colonne D | =DECALER(Calculs!$D$2;;;SOMMEPROD((Calculs!$D$1:$D$100<>"")*1)-1;1) |
| OrdreOccurences | Feuille Calculs / colonne F | =DECALER(Calculs!$F$2;;;SOMMEPROD((Calculs!$C$1:$C$100<>"")*1)-1;1) |
| CategoriesTriees | Feuille Calculs / colonne G | =DECALER(Calculs!$G$2;;;SOMMEPROD((Calculs!$G$1:$G$100<>"")*1)-1;1) |
| Part | Feuille Calculs / colonne H | =DECALER(Calculs!$H$2;;;SOMMEPROD((Calculs!$C$1:$C$100<>"")*1)-1;1) |
| CumulOccurences | Feuille Calculs / colonne I | =DECALER(Calculs!$I$2;;;SOMMEPROD((Calculs!$C$1:$C$100<>"")*1)-1;1) |
| Pourcentage | Feuille Calculs / colonne J | =DECALER(Calculs!$J$2;;;SOMMEPROD((Calculs!$C$1:$C$100<>"")*1)-1;1) |
| Limite_A | Feuille Calculs / colonne K | =DECALER(Calculs!$K$2;;;SOMMEPROD((Calculs!$D$1:$D$100<>"")*1)-1;1) |
| Limite_B | Feuille Calculs / colonne L | =DECALER(Calculs!$L$2;;;SOMMEPROD((Calculs!$D$1:$D$100<>"")*1)-1;1) |
III-B. Compter le nombre d'occurrences par catégorie▲
La colonne C (champ 'Nb occurences') de la feuille 'Calculs' va comptabiliser le total de valeurs
pour chaque groupe.
=SI(B2="";"";SOMME.SI(Categories;B2;Occurences))
Le nom 'Categories' représente la colonne A dans la feuille 'Collecte' :
=DECALER(Collecte!$A$2;;;NBVAL(Collecte!$A:$A)-1;1)
Le nom 'Occurences' représente la colonne B dans la feuille 'Collecte' :
=DECALER(Collecte!$B$2;;;NBVAL(Collecte!$B:$B)-1;1)
La formule additionne toutes les valeurs par catégorie.
III-C. Déterminer le rang de chaque catégorie▲
Un diagramme de Pareto représente généralement un histogramme de données décroissantes et
une courbe des valeurs cumulées. Pour obtenir le même résultat visuel dans un graphique
Excel, nous allons devoir classer chaque total par ordre décroissant.
Tout d'abord il faut identifier le rang de chaque total : 1 pour le plus grand total, 2
pour le deuxième total, ...etc ...
La formule suivante est insérée dans la colonne D (champ 'RangUnique').
=SI(B2="";"";RANG(C2;NbOccurences)+NB.SI($C$2:C2;C2)-1)
Le nom 'NbOccurences' représente la colonne C dans la feuille 'Calculs'.
+NB.SI($C$2:C2;C2)-1 permet de différencier la numérotation
des ex-aequo. Cela sera utile pour lister les catégories
triées, comme vous pourrez le voir dans le chapitre suivant.
III-D. Trier les catégories par totaux décroissants▲
La phase suivante consiste à trier les valeurs et les catégories associées.
La colonne F (champ 'OrdreOccurences') trie les totaux par ordre décroissant.
=SI(C2="";"";GRANDE.VALEUR(NbOccurences;LIGNE()-1))
La colonne G (champ 'CatégoriesTriées') trie les catégories par ordre décroissant.
Cette plage de cellules servira à créer l'axe des abscisses du graphique.
=SI(C2="";"";INDEX(DECALER(RangUnique;;-2;SOMMEPROD((Calculs!$C$1:$C$100<>"")*1)-1;3);EQUIV(LIGNE()-1;RangUnique;0);1))
III-E. Calculer la part de chaque catégorie▲
Les données de la colonne H (champ 'Part') vont servir de valeurs d'ordonnées pour
l'histogramme du graphique.
Il s'agit donc ici de redéfinir les valeurs de la colonne F (champ 'OrdreOccurences')
sous forme de pourcentage par rapport au total de cette colonne.
=SI(C2="";"";F2/SOMME(OrdreOccurences))
III-F. Cumuler les occurrences▲
Un diagramme de Pareto est également basé sur la représentation d'une courbe cumulée
de 0 à 100%.
La colonne I (champ 'OrdreOccurences') va additionner les valeurs triées dans la colonne F.
=SI(F2="";"";SOMME($F$2:F2))
La colonne J (champ 'Pourcentage') va convertir les valeurs de la colonne I en pourcentage
par rapport au total de cette colonne.
Cette plage de cellules servira d'ordonnées pour la courbe de cumul dans le graphique.
=SI(I2="";"";I2/SOMME(NbOccurences))
III-G. Visualiser les limites A, B et C dans le graphique▲
L'efficacité d'un graphique Excel reposant en partie sur son attrait visuel, il
reste à définir des droites à 80 et 95 %. Vous pourrez ainsi visualiser rapidement les
limites qui définissent l'analyse de Pareto :
La plage A : Catégories contenant de 0 à 80% des effets.
La plage B : Catégories contenant de 80 à 95 % des effets.
La plage C : les 5 % d'effets restants.
Le calcul effectué dans les colonnes K et L permettra d'afficher ces droites limites
dans le graphique.
=SI(C2="";"";0,8)=SI(C2="";"";0,95)
IV. Le résultat▲
IV-A. Le diagramme de Pareto▲
Désormais, tous les paramètres sont en place pour afficher le diagramme de Pareto
dans un graphique Excel. L'objectif consiste à obtenir la présentation finale suivante :
IV-B. L'ajout des séries dans le graphique▲
Ajoutez une nouvelle feuille de calcul.
Sélectionnez l'onglet "Insertion" dans le ruban.
Cliquez sur le bouton "Colonne" dans le groupe "Graphiques".
Sélectionnez "Histogramme 2D" / groupé.
Un nouvel objet graphique vierge est inséré dans la feuille.
Sélectionnez cet objet graphique.
Faites un clic droit.
Cliquez sur l'option "Sélectionner des données" dans le menu contextuel.
Cliquez sur le bouton "Ajouter".
Nous allons créer l'histogramme décroissant (données de la colonne H) :
Indiquez un nom pour la série.
Dans le champ "Valeurs de la série", saisissez la plage nommée correspondant à la
colonne H :
='pareto_XL2007.xls'!Part
Cliquez sur le bouton OK pour valider.
Cliquez sur le bouton "Modifier" pour spécifier l'abscisse (la liste des catégories).
Saisissez la plage nommée correspondant à la colonne G :
=pareto_XL2007.xls!CategoriesTriees
Cliquez sur le bouton OK dans toutes les boîtes de dialogue pour valider.
Reproduisez la même opération pour ajouter les autres séries.
La courbe de cumul :
=pareto_XL2007.xls!Pourcentage
La droite de limite des 80 % :
=pareto_XL2007.xls!Limite_A
La droite de limite des 95 % :
=pareto_XL2007.xls!Limite_B
Toutes les séries sont insérées dans le graphique mais on est encore loin du compte
pour ce qui est du résultat visuel :
Le chapitre suivant montre comment mettre en forme le graphique et finaliser
le projet.
IV-C. La mise en forme du graphique▲
IV-C-1. Redéfinir les limites de l'axe des ordonnées▲
Par défaut, les valeurs d'ordonnées sont indiquées de 0 à 120 %.
Pour afficher les valeurs de 0 à 100 %, sélectionnez l'axe des ordonnées.
Clic droit.
Sélectionnez "Mise en forme de l'axe" dans le menu contextuel.
Cliquez sur le menu "Options de l'axe".
Sélectionnez l'option "Fixe" pour la valeur "Maximum".
Indiquez 1 (ce qui représente 100 %) dans le champ.
Cliquez sur le bouton "Fermer".
IV-C-2. Modifier les types de séries d'histogramme en courbe▲
Les séries Cumul, Limite_A et Limite_B doivent être transformées en courbes dans le
diagramme de Pareto.
Procédez de la même manière pour les 3 séries :
Sélectionnez la série.
Clic droit.
Sélectionnez "Modifier le type de graphique Série de données" dans le menu contextuel.
Choisissez le type de graphique "Courbe".
Cliquez sur le bouton OK pour valider.
IV-C-3. Afficher toutes les catégories sur l'axe des abscisses▲
Pour visualiser la liste complète des catégories sur l'axe des abscisses :
Sélectionnez l'axe des abscisses.
Clic droit.
Sélectionnez "Mise en forme de l'axe" dans le menu contextuel.
Choisissez le menu "Options de l'axe".
Cliquez sur l'option "Spécifier l'unité de l'intervalle".
Indiquez la valeur 1.
Ensuite, Cliquez sur le menu "Alignement".
Choisissez "Faire pivoter tout le texte de 270°" dans le menu déroulant "Orientation
du texte".
Cliquez sur le bouton "Fermer".
Nota :
Lorsque vous faites un clic droit sur l'axe, apparait également la mini barre d'outils
Excel 2007.
Utilisez ce menu contextuel pour personnaliser le format du texte.
IV-C-4. Supprimer les espaces entre chaque donnée en abscisses▲
Sélectionnez la série en histogramme ('Catégories').
Clic droit.
Sélectionnez l'option "Mettre en forme une série de données".
Menu "Options des séries".
Ramenez le curseur "Largeur de l'intervalle" à 0%.
Pendant que la fenêtre de personnalisation de la série est ouverte, vous
pouvez mettre en forme :
* La couleur de fond
* Les bordures
* Les types de traits et les marqueurs
Cliquez sur le bouton "Fermer" pour terminer.
La mise en forme de votre graphique est désormais terminée et vous pouvez visualiser
le diagramme de Pareto.
On voit clairement qu'il faut agir en priorité sur les temps de panne mécanique et sur la
durée des intercampagnes pour diminuer significativement l'immobilisation de la
machine.
V. Conclusion▲
J'espère que les informations fournies dans cet article vous aideront à finaliser
vos projets.
Adaptez et modifiez librement le classeur démo en fonction de vos besoins.
Les formules sont ici volontairement détaillées sur plusieurs colonnes mais vous pouvez
regrouper les fonctions, masquer ou réarranger les colonnes de calcul intermédiaire comme bon
vous semble.
VI. Liens▲
Le diagramme de Pareto, sur WikiPedia.
Utiliser les fonctions Excel
Optimisez vos recherches avec les fonctions Index et Equiv
La gestion des doublons dans Excel
VII. Remerciements▲
Je remercie toute l'équipe Office de DVP
et particulièrement
Philippe Jochmans,
Jean Ballat ,
pour la relecture et la correction du tutoriel.
VIII. Téléchargement▲
Téléchargez le classeur démo
(Testé sous Excel2002 et Excel2007).