Caclul au prorata avec gestion des arrondis

[jl.delor]

Bonjour le Forum,

Je cherche une formule qui permette de gérer les écarts d'arrondis dans un calcul de prorata ; de sorte que le total réparti corresponde à la base initiale (avec affectation de l'écart au prorata des valeurs).

J'ai trouvé une méthode d'affectation de l'écart à la valeur la plus élevée en utilisant la formule RANG.
Mais cette méthode nécessite de faire 2 calculs et affecte l'ensemble de l'écart à une seule valeur.
Est-il possible d'utiliser une formule ?

Je vous joins un fichier exemple.
Merci à tous de votre avis

 

[Victor21]

Re : Caclul au prorata avec gestion des arrondis

Bonjour.

Pourquoi arrondir pour ensuite répartir les arrondis ?
Ne suffirait-il pas de ne pas arrondir ?

 

[KenDev]

Re : Caclul au prorata avec gestion des arrondis

Bonsoir jl.delor, Victor,

Ci-joint un fichier déjà transmis lors d'une demande similaire (nom des colonnes adapté et explications revues).
La colonne Objectif Commande initiale devient inutile mais un certains nombre d'autres ont été ajoutées...

Garder ce fichier tel quel pour des tests (Les 2 seules cases à éventuellement modifier sont les 2 valeurs paramètres en B22:B23 et appuyer sur le bouton)

Pour des cas réels ponctuels, enregistrer une copie sans la macro, retirer le bouton et effacer les cases inutiles (paramètres, case arret macro...)

Les données d'entrées sont alors à inscrire en B1 (base calcul) et en colonne B (obj.cmd annuelles par magasin) au lieu des formules aléatoires actuelles.

Vérifier que la logique de décision est celle attendue (ici les quantités sont calculées au minimum puis ajustées selon les + gros restes, puis si nécessaire tirées au sort) . Sinon, avec un peut de chance, les modifications à faire seront mineures.


@Victor : Le cas exemple transmis par jl.delor est trivial. Ça peut se compliquer en cas d'égalités dans les données de départ et/ou dans les restes, ou encore en cas d'entrées démesurées, biscornues... (voir fichier, lancer la sub telle quelle pour 1000 essais et observer les colonnes I:J)

Cordialement

KD

Edit : la macro ne sert qu'à simuler la touche F9 en série. Pour voir des données 'difficiles', il faut donc faire F9 et attendre d'avoir des données en I:J

 

[mapomme]

Re : Caclul au prorata avec gestion des arrondis

Bonjour jl.delor, Victor, KenDev et le forum,

Tout d’abord merci à KenDev pour avoir avec sagacité vu et décrit le pb. que j’avais niaisement ignoré.

Le tableau de KenDev m’a fait reconsidérer l’idée de jl.delor basé sur un classement des plus grands restes en utilisant la fonction d'Excel : =RANG()

Le résultat est le fichier joint avec :
En feuille « Obj » un essai basé sur les plus grands restes (sur le principe d’équité)
En feuille « ObjVol » un essai basé sur les plus gros volumes (sur le principe qu’un vendeur ayant un gros volume de ventes aura moins de difficulté à en faire une de plus que celui qui en fait peu – j’ai déjà vu !)

Ce second cas a posé un problème : en rajoutant une quantité epsilon à un volume élevé (>1), je n’obtenais plus une précision suffisante lors de l’ajout de la «quantité epsilon» et par conséquent j’obtenais des ex-æquo malgré l’ajout du «epsilon».

Je n’ai pas trop fouillé pour trouver la vraie raison : représentation interne d’Excel, mon paramétrage d’Excel ou une grosse cagade de ma part (sans doute la cause la plus probable!)

NB: KenDev => j'ai honteusement pompé ton travail!

 

[jl.delor]

Re : Caclul au prorata avec gestion des arrondis

Merci à tous les experts.
Je retiens l'idée d'une répartition du restant à l'unité sur les plus gros volumes

 

[jl.delor]

Re : Caclul au prorata avec gestion des arrondis

A partir de vos fichiers, j'obtiens le résultat suivant nglet ObjQte (2).
Je neutralise le problème du rang en ajoutant LIGNE()/10^10 (et fais le choix de prioriser des résultats identiques selon leur position dans le tableau).