Table des matières
Vous avez peut-être entendu parler de la loi de Pareto ou du principe 20/80. A la fin du XIXe siècle, le sociologue et économiste italien Vilfredo Pareto découvre que la répartition des richesses dans la société est inégale et soumise à une certaine dépendance : avec une augmentation de la richesse, le nombre de riches diminue de façon exponentielle avec un coefficient constant ( parmi les ménages italiens, 19 % des revenus appartenaient à 80 % des familles). Plus tard, cette idée a été développée dans son livre par Richard Koch, qui a proposé la formulation du « Principe 20/20 » universel (80 % des efforts donnent 20 % du résultat). En pratique, cette loi ne s'exprime généralement pas en de si beaux chiffres (lire « The Long Tail » de Chris Anderson), mais montre clairement la répartition inégale des ressources, des bénéfices, des coûts, etc.
Dans l'analyse commerciale, un diagramme de Pareto est souvent construit pour représenter cette inégalité. Il peut être utilisé pour montrer visuellement, par exemple, quels produits ou clients apportent le plus de profit. Il ressemble généralement à ceci :
Ses principales caractéristiques:
- Chaque colonne bleue de l'histogramme représente le bénéfice du produit en unités absolues et est tracée le long de l'axe de gauche.
- Le graphique orange représente le pourcentage cumulé des bénéfices (c'est-à-dire la part des bénéfices sur une base cumulée).
- Sur une bordure conditionnelle de 80 %, une ligne horizontale de seuil est généralement tracée pour plus de clarté. Tous les biens à gauche du point d'intersection de cette ligne avec le graphique du profit accumulé nous rapportent 80% de l'argent, tous les biens à droite - les 20% restants.
Voyons comment créer vous-même un diagramme de Pareto dans Microsoft Excel.
Option 1. Un diagramme de Pareto simple basé sur des données prêtes à l'emploi
Si les données source vous sont parvenues sous la forme d'un tableau similaire (c'est-à-dire déjà sous une forme finie) :
… puis nous procédons comme suit.
Trier le tableau par ordre décroissant de profit (onglet Données – Tri) et ajoutez une colonne avec la formule de calcul du pourcentage cumulé des bénéfices :
Cette formule divise le bénéfice total cumulé depuis le début de la liste jusqu'à l'élément actuel par le bénéfice total pour l'ensemble du tableau. Nous ajoutons également une colonne avec une constante de 80 % pour créer une ligne pointillée de seuil horizontale dans le futur graphique :
Nous sélectionnons toutes les données et construisons un histogramme régulier sur l'onglet Insérer – Histogramme (Insérer – Diagramme à colonnes). Ça devrait ressembler a quelque chose comme ca:
La série de pourcentages dans le graphique résultant doit être envoyée le long de l'axe secondaire (droit). Pour ce faire, vous devez sélectionner les lignes avec la souris, mais cela peut être difficile, car elles sont difficiles à voir dans le contexte de grandes colonnes de profit. Il est donc préférable d'utiliser la liste déroulante de l'onglet pour mettre en évidence Disposition or Format:
Faites ensuite un clic droit sur la ligne sélectionnée et sélectionnez la commande Formater la série de données et dans la fenêtre qui apparaît, sélectionnez l'option Sur l'axe secondaire (Secondary Axis). En conséquence, notre diagramme ressemblera à ceci:
Pour les séries Partage des bénéfices cumulés et Seuil, vous devez modifier le type de graphique de colonnes à lignes. Pour cela, cliquez sur chacune de ces lignes et sélectionnez la commande Modifier le type de graphique de série.
Il ne reste plus qu'à sélectionner la ligne horizontale Seuil et à la formater de sorte qu'elle ressemble à une ligne de coupure plutôt qu'à des données (c'est-à-dire, supprimer les marqueurs, rendre la ligne pointillée rouge, etc.). Tout cela peut être fait en cliquant avec le bouton droit sur la ligne et en sélectionnant la commande Formater la série de données. Maintenant, le diagramme prendra sa forme finale :
Selon elle, on peut conclure que 80% du profit est apporté par les 5 premiers biens, et tous les autres biens à droite de la pomme de terre ne représentent que 20% du profit.
Dans Excel 2013, vous pouvez le faire encore plus facilement - utilisez le nouveau type de graphique combiné intégré immédiatement lors du traçage :
Option 2 : tableau croisé dynamique et graphique de Pareto croisé dynamique
Que faire s'il n'y a pas de données prêtes à l'emploi pour la construction, mais seulement les informations brutes d'origine ? Supposons qu'au début nous ayons une table avec des données de ventes comme celle-ci :
Pour construire un diagramme de Pareto dessus et savoir quels produits se vendent le mieux, vous devrez d'abord analyser les données sources. La façon la plus simple de le faire est d'utiliser un tableau croisé dynamique. Sélectionnez n'importe quelle cellule dans le tableau source et utilisez la commande Insérer – Tableau croisé dynamique (Insérer – Tableau croisé dynamique). Dans la fenêtre intermédiaire qui apparaît, ne changez rien et cliquez sur OK, puis dans le panneau qui apparaît à droite, faites glisser les champs de données source du haut vers le bas de la mise en page du futur tableau croisé dynamique :
Le résultat devrait être un tableau récapitulatif avec le revenu total pour chaque produit :
Triez-le par ordre décroissant de revenus en définissant la cellule active sur la colonne Montant dans le champ Chiffre d'affaires et en utilisant le bouton de tri От Я до А (De Z à A) languette Données.
Nous devons maintenant ajouter une colonne calculée avec les revenus d'intérêts cumulés. Pour ce faire, faites à nouveau glisser le champ Revenu à la région Valeurs dans le volet de droite pour obtenir une colonne dupliquée dans le pivot. Ensuite, faites un clic droit sur la colonne clonée et choisissez la commande Calculs supplémentaires – % du total cumulé dans le champ (Afficher les données en tant que – % du total cumulé entrant). Dans la fenêtre qui apparaît, sélectionnez le champ Nom, sur lequel le pourcentage des revenus s'accumulera de haut en bas. Le résultat devrait ressembler à ce tableau :
Comme vous pouvez le voir, il s'agit presque d'un tableau prêt à l'emploi de la première partie de l'article. Il ne lui manque pour le plus grand bonheur qu'une colonne avec une valeur seuil de 80% pour construire une ligne de coupure dans un futur diagramme. Une telle colonne peut facilement être ajoutée à l'aide d'un champ calculé. Mettez en surbrillance n'importe quel nombre dans le résumé, puis cliquez sur l'onglet Accueil – Insertion – Champ calculé (Accueil – Insertion – Champ calculé). Dans la fenêtre qui s'ouvre, saisissez le nom du champ et sa formule (dans notre cas, une constante) :
Après avoir cliqué sur OK une troisième colonne sera ajoutée au tableau avec une valeur de 80% dans toutes les cellules, et elle prendra enfin la forme requise. Ensuite, vous pouvez utiliser la commande Graphique croisé dynamique (Graphique croisé dynamique) languette Paramètres (Options) or Analyse (Analyse) et configurez le graphique exactement de la même manière que la première option :
Mise en avant des produits phares
Pour mettre en évidence les facteurs les plus influents, c'est-à-dire les colonnes situées à gauche du point d'intersection de la courbe orange des intérêts cumulés avec la ligne de coupure horizontale de 80 % peuvent être mises en surbrillance. Pour ce faire, vous devrez ajouter une autre colonne au tableau avec la formule :
Cette formule renvoie 1 si le produit est à gauche du point d'intersection et 0 s'il est à droite. Ensuite, vous devez faire ce qui suit :
- Nous ajoutons une nouvelle colonne au graphique - la façon la plus simple de le faire est de copier simplement, c'est-à-dire de mettre en surbrillance la colonne rétro-éclairage, copiez-le (Ctrl + C), sélectionnez le diagramme et insérez (Ctrl + V).
- Sélectionnez la ligne ajoutée et basculez-la le long de l'axe secondaire, comme décrit ci-dessus.
- Type de graphique de série rétro-éclairage passer aux colonnes (histogramme).
- Nous supprimons le dégagement latéral dans les propriétés de la ligne (clic droit sur la ligne Illumination - Format de ligne - Espace latéral) afin que les colonnes fusionnent en un seul ensemble.
- Nous supprimons les bordures des colonnes et rendons le remplissage translucide.
En conséquence, nous obtenons une si belle mise en évidence des meilleurs produits :
PS
À partir d'Excel 2016, le diagramme de Pareto a été ajouté à l'ensemble standard de diagrammes Excel. Maintenant, pour le construire, sélectionnez simplement la plage et sur l'onglet insérer (Insérer) choisissez le type approprié :
Un clic – et le diagramme est prêt :
- Comment créer un rapport à l'aide d'un tableau croisé dynamique
- Configurer des calculs dans des tableaux croisés dynamiques
- Quoi de neuf dans les graphiques dans Excel 2013
- Article Wikipédia sur la loi de Pareto