Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

Nemo31

XLDnaute Nouveau
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 ….
 

vgendron

XLDnaute Barbatruc
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

XLDnaute Nouveau
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

XLDnaute Nouveau
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

XLDnaute Impliqué
Supporter XLD
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

XLDnaute Nouveau
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.
 

mapomme

XLDnaute Barbatruc
Supporter XLD
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
 

Pièces jointes

  • Nemo31- Foie gras- v1.xlsm
    35.5 KB · Affichages: 13

Nemo31

XLDnaute Nouveau
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

XLDnaute Barbatruc
Supporter XLD
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...
 
Dernière édition:

Nemo31

XLDnaute Nouveau
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.
 

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…