Règle de Pareto sur excel ??

[wainso]

Bonjour tout le monde
Aujourd'hui j'ai un problème assez complexe enfin pour moi
J'ai une base de données sur Excel composé de plusieurs colonnes dont ceux qui nous intéresse sont montant et références.
En fait je voudrais réaliser une macro qui me permette d'avoir le montant de la référence qui représente le seuil Pareto c.-à-d à partir de quel montant nous pouvons dire que 20 % des actes représente 80% du montant total des références.
Ça se rapproche à la notion de valeur cible sur excel mais je ne suis pas sûr
Manuellement je ferais de la sorte:
1 - trier la base de données suivant la colonne des montants de plus haut au plus bas
2 - ajouter une colonne juste à côté qui représente le pourcentage cumulé des valeurs triés
3 - chercher la référence dont le montant est égal à 80% dans la colonne ajoutée ou la valeur la plus proche de 80%

Merci pour l'aide fourni

 

[Staple1600]

Re : Règle de Pareto sur excel ??

Bonjour à tous

wainso

Merci pour l'aide fourni

Merci pour le fichier exemple fourni qui permettra de fournir l'aide...

 

[excfl]

Re : Règle de Pareto sur excel ??

Bonjour le forum,

Voir le fichier joint.

excfl

 

[wainso]

Re : Règle de Pareto sur excel ??

merci pour votre réactivité
avant de voir l'exemple fourni voici un exemple réduit d'un fichier que je traite
la base n'est pas trié au départ et les deux colonnes C et D ont été ajouté je cherche la valeur 17500 qui représente 80 % des valeurs

 

[excfl]

Re : Règle de Pareto sur excel ??

Re,

Avec la méthode du 20/80, ce sont 20% des produits vendus qui génèrent 80% du CA.
Ce sont ces 20% de produits qui sont particulièrement observés dans la gestion des stocks.

Dans l'exemple joint , seul le chiffre d'affaires est proposé : il devrait être décomposé en quantités et prix unitaires.

excfl

 

[wainso]

Re : Règle de Pareto sur excel ??

Tout d'abord merci beaucoup le fichier de excfl est génial
Je vous explique un peu mon souci. Je contrôle tout les actes de dépenses soumis par mon entreprise et qui sont référencié dans la colonne A. Je veux concentrer mon intervention sur uniquement les actes réprésentant les enjeux financiers les plus importants et en même temps un nombre réduit.
Donc au lieu de contrôler 100% des actes je souhaite analyser les 20% en nombre qui réprésenteront 80 % du montant et donc je dois déterminer un seuil à partir duquel tout acte devra être contrôler et renoncer à ceux qui sont inférieur à ce seuil

 

[excfl]

Re : Règle de Pareto sur excel ??

Re,

Tout d'abord merci beaucoup le fichier de excfl est génial
Je vous explique un peu mon souci. Je contrôle tout les actes de dépenses soumis par mon entreprise et qui sont référencié dans la colonne A. Je veux concentrer mon intervention sur uniquement les actes réprésentant les enjeux financiers les plus importants et en même temps un nombre réduit.
Donc au lieu de contrôler 100% des actes je souhaite analyser les 20% en nombre qui réprésenteront 80 % du montant et donc je dois déterminer un seuil à partir duquel tout acte devra être contrôler et renoncer à ceux qui sont inférieur à ce seuil

Les dépenses sont classées dans l'ordre décroissant.
Par contre : 80% des dépenses : 13 % des actes.
Pour 20% des actes : 85,90 % des dépenses.
excfl

 

[wainso]

Re : Règle de Pareto sur excel ??

Parfaitement excfl
Maintenant est il possible de générer par macro les deux valeurs c.-à-d. Le montant de l'acte en jaune et celui aussi que tu vient de me le faire remarquer c.-à-d. 11988 ( 20% des actes)
Merci

 

[wainso]

Re : Règle de Pareto sur excel ??

Dans le même fil des idées je tiens à préciser que la table finale sera diminué des valeurs abérentes et ce en appliquant un test statistique afin de vérifier parfaitement la règle 20/80

 

[excfl]

Re : Règle de Pareto sur excel ??

Re,

Dans le même fil des idées je tiens à préciser que la table finale sera diminué des valeurs abérentes et ce en appliquant un test statistique afin de vérifier parfaitement la règle 20/80

J'ai laissé 85 actes pour arriver à : 20/82
excfl

 

[wainso]

Re : Règle de Pareto sur excel ??

Je suis sous ipad maintenant donc je ne peux explorer le fichier pouvez vous m'expliquer ce que vous avez fait
Merci

 

[excfl]

Re : Règle de Pareto sur excel ??

Re,

J'ai supprimé les 35 dépenses les plus faibles.

% des actes : E2 : =((LIGNE()-1))/85

excfl

 

[wainso]

Re : Règle de Pareto sur excel ??

D'accord mais y a t il moyen d'automatiser ce travail et d'avoir une macro qui fait tout

 

[excfl]

Re : Règle de Pareto sur excel ??

D'accord mais y a t il moyen d'automatiser ce travail et d'avoir une macro qui fait tout

Avis aux amateurs de macros !

Le travail est déjà automatisé : les colonnes C à E contiennent des formules de calcul.
Quant à trouver 20/80 : cela ne poserait pas de problème si les 2 taux étaient toujours exacts et identiques : mais 20/80 est une fourchette estimée qui ne tombe pratiquement jamais pile.

excfl

 

[excfl]

Re : Règle de Pareto sur excel ??

Re,

Une colonne supplémentaire : F2 : =(20%-E2)+(80%-D2)

Calcul de l'écart minimum : supérieur à 0 : =MIN(SI((F:F)>0%;F:F))

Formule matricielle : à valider avec CTRL MAJ ENTREE

MFC pour terminer.

excfl