XL 2016 J’ai un problème de foies gras de canards

[Nemo31]

Bonjour,

J’ai un problème de foies gras de canards.
Explications :
Tous les ans, juste avant l’hiver, nous faisons une réunion de famille pour cuisiner les confits et les foies gras.
Les gens viennent de loin (Paris, Bordeaux) pour faire une expédition dans le Gers.
Pour la répartition des confits, c’est facile car les canards sont tous pareils.
Mais les foies oscillent entre 400 g et 700 g.
Le prix étant de 35 € le kilo, il faut ventiler en fonction du poids (pour la facture) et du nombre demandé par chacun.
Jusqu’à maintenant cette ventilation était faite à la main par quelqu’un qui vient d’avoir un accident.

Je voudrais que Excel 2016 fasse le boulot mais je ne sais pas quelles fonctions sont les plus adaptées.
Données du problème :
30 foies à répartir sur 5 personnes qui ont demandé des nombres de foie différents (de 2 à 10) en se rapprochant le plus possible du poids moyen des 30 foies car il n’est pas question de refiler les plus petits à un et les plus gros à un autre.
Chaque foie est pesé et identifié par un numéro.
La facture est facile à établir quand la ventilation équitable est faite.
J’espère avoir été clair, et que mon problème intéressera de bonnes âmes qui voudront bien me donner des pistes.
Merci d’avance ….

 

[Hasco]

Bonjour,

Et moi j'ai un problème de foie de fichiers maigres non répartis entre n xldlNautes.

Je passe.

 

[vgendron]

Bonjour

LA première chose à faire par VOUS, c'est de mettre toutes vos données sur un tableau excel
par exemple: Colonne A: le numéro d'identification des 30 foies, colonne B, leur poid associé

un autre tablo: les noms des 5 personnes, le nombre demandé

ensuite, poster le fichier sur le forum et prier

 

[Nemo31]

Super !
J'aime bien "prier", j'ai la banane.
C'est déjà fait, mais cela fait appel à des liens entre feuilles qui ne sont pas intéressants pour tout le monde.
Je peux faire un classeur dédié (isolé).
Ca va venir...
merci pour la réponse

 

[Nemo31]

Voici le fichier.
J'espère que le téléchargement va fonctionner.

 

[Webperegrino]

Bonsoir Nemo31, Le Forum,
Une première approche...
Webperegrino

 

[Nemo31]

Merci pour le boulot (conséquent).
Je comprend comment fonctionne la fonction SOMMEPROD :
Elle sert à additionner les poids des foies (colonne B) attribués à chaque individu (colonne F).
Mais il me semble que cette attribution est manuelle.
En tous cas, pour une première approche c'est très intéressant.
Merci.

 

[Webperegrino]

Bonjour Nemo31, Le Forum,
"Mais il me semble que cette attribution est manuelle." : oui car j'avais respecté les formules qui étaient à l'origine placées dans la zone [K6:K11]...

Votre demande est en effet plus complexe que je le pensais : attribuer les morceaux demandés à chacun [H6:H11] pour placer les destinataires en résultat dans la zone [F7:F36] tout en ayant l'écart [L6:L11] le plus proche de 0.
Le problème à résoudre est très intéressant mais c'est trop dur à résoudre pour moi, désolé.
Webperegrino

 

[Nemo31]

Bonsoir,
Un grand merci pour votre aide.
Je sais que le problème est ardu, et je ne sais pas par quel bout l'attraper.
Il faut certainement faire un arbre de décision.
Mais dès les premières cases on se heurte à la faisabilité, ou non, par Excel.
Il faut certainement bien connaitre les possibilités (formules) d'Excel et peut-être le Solveur, l'analyse de scénario, ou la valeur cible et les tables de données.
Mais ce n'est pas mon cas.
C'est pour cela que je cherche une piste, un angle d'attaque qui soit prometteur.
Merci encore.

 

[TooFatBoy]

Bonjour,

C'est pour cela que je cherche une piste, un angle d'attaque qui soit prometteur.

Peut-être une piste ici : https://excel-downloads.com/threads/repartition-equitable-de-lots.20051835/#post-20385100

 

[Nemo31]

Bonjour,

Oui, oui, ça ressemble beaucoup à mon affaire.
Maintenant il faut que je me plonge dans le Solveur.
Un grand merci.

 

[mapomme]

Bonjour à tous et aux autres ,

Voici un essai via VBA

• Il faut remplir les cellules en orange (N° Foie, poids (en gramme), les noms des personnes, leurs nombre souhaité de foies, le prix au kilo).
• Il faut aussi saisir le nombre d'itérations (utilisé par la méthode choisie).

Ensuite cliquer sur le bouton Hop!

• Le résultat est affiché après la colonne en bleu (à partir de la colonne T)
• Ne pas toucher aux cellules entre la colonne H (en noir) et la colonne S (en bleu).

nota: je suis tout à fait enclin à être gratifié en nature

 

[Nemo31]

Ouah !
Je me prosterne devant vous Grand Maître !

J'ai rentré les données des années passées et force est de constater que les écarts de répartition sont beaucoup plus faibles que ce qu'on avait fait de façon intuitive.
Super génial !

Permettez-moi, Grand Maître, quelques irrévérencieuses questions :
1) A quoi servent les chiffres aléatoires (colonne K) ?

2) A chaque calcul (bouton HOP !) les modifications d'apparence des cellules (colonnes I à AC) sont initialisées.
Comment faire pour faire des modifs genre format de cellule (avec des unités, nb de décimales, etc) ?

3) La solution pour ne pas tripoter le code VBA (même si cela ne me ferait pas de mal de mettre les mains dans le cambouis) est sans doute de faire une importation des données (avec un lien) dans le classeur qui me sert pour faire tous les autres calculs concernant les confits (genre attributioon des bocaux).
Mais à chaque recalcul, les prochaines années, les cellules concernées seront mises à jour.
Comment faire pour les figer ?

4) Un copier-coller fige les données mais nécessite une recopie pour pouvoir faire de la mise en page.
Est-ce la solution ?

5) Je sais, j'abuse, mais sans rentrer dans les détails de la programmation, quel est le cheminement intellectuel qui permet d'aborder, et de résoudre ce problème ?

En tout cas, un très très gros, grand, MERCI

 

[mapomme]

Re,

1) A quoi servent les chiffres aléatoires (colonne K) ?

En fait, on procède en tâtonnant.

L'idée est :

• de répartir les 30 foies (en fait leur numéro) de manière aléatoire sur la colonne J
• de calculer l'écart du poids moyen des foies pour chaque personne par rapport au poids moyen de tous les foies (donc autant d'écarts que de personnes)
• de calculer l'écart-type de ces écarts

Ensuite :

• on compare l'écart-type actuel au précédent écart type
• si l'écart-type actuel est supérieur à l'écart-type précédent alors la répartition courante ne nous intéresse pas et on procède directement à une nouvelle répartition aléatoire des numéros de foie.
• si l'écart type courant est inférieur à l'écart type précédent alors la répartition courante est plus intéressante et on la sauvegarde en valeur sur les colonnes T à V et ensuite on procède à une nouvelle répartition aléatoire.
• on arrête la boucle quand on a procédé au nombre d'itérations saisi en cellule G7.

Quand tout ceci est fait :

• on recopie en valeur la colonne solution (n° des foies) sur la colonne J
• on rajoute les prix (colonne W)
• on construit le TCD (en Y1)
• on met en forme le TCD
• on met en forme le tableau T à W (couleur et suppression des noms en double)

Cette partie plus pénible et verbeuse en code VBA a été faite en enregistrant les macros puis en les modifiant un peu.

Comment faire un tirage aléatoire des numéros de foie ? :
Tout d'abord, on est passé en calcul manuel (sinon, à chaque recalcul de la feuille, la fonction ALEA() serait recalculée). On veut contrôler le moment de recalculer ces fonctions ALEA() en colonne K.

• on recalcule la feuille sur ordre
• la colonne k contient donc de nouvelles valeurs
• on trie les deux colonnes J:K selon les nouvelles valeurs de la colonne K
• une fois le tri fait, les numéros de foie de la colonne J ont tous changé de place (à cause du tri)
• nous avons donc une nouvelle répartition aléatoire des foies

Je ne me suis pas penché sur vos questions de mise en forme et de sauvegarde. A chaque jour suffit sa peine; ça viendra sans doute demain...

 

[Nemo31]

Super, merci pour vos explications
Donc à chaque nouvelle itération sauvegardée on se rapproche de l'écart-type nul.
Plus le nombre d'itération est élevé et plus on s'approche du but et ceci de manière aléatoire.
Original, mais efficace, il fallait y penser.
En fait, le VBA est indispensable sinon avec des formules dans les cellules cela ferait une usine à gaz ?

A propos du tableau TCD (en Y1) :
J'ai ajouté une feuille au classeur que vous m'avez envoyé afin de faire de la recopie de données et de faire de la mise en forme pour pouvoir faire du copier coller des valeurs calculées vers ma feuille récapitulative par année.
J'ai changé les noms sur la feuille d'origine (cases en jaune), le VBA l'accepte.
Il y a un truc que je ne comprend pas.
Quand on lance le calcul la première fois, le lien de recopie depuis le tableau croisé dynamique est :
=LIREDONNEESTABCROISDYNAMIQUE("Poids moyen";Foies!$Y$1;"Qui";"Gaëlle")
Si on relance le calcul, le lien se modifie tout seul et devient :
=LIREDONNEESTABCROISDYNAMIQUE("Poids moyen";Foies!$X$1;"Qui";"Gaëlle").
Le "Y" est changé en "X".
A chaque fois on décrémente l'alphabet (W, V, etc)
Evidemment, comme le tableau n'existe pas j'ai des #REF! partout dans mon tableau de recopie.
Je ne comprends pas. Mais surtout je ne sais pas commen contourner l'obstacle.

Merci beaucoup pour vos explications.
Cela m'ennuie d'utiliser le travail de quelqu'un sans, au moins, comprendre comment cela a été conçu.
Et puis, j'apprends, j'évolue.