Formules de répartition

Bonjour à toutes et tous,

Je suis tout nouveau sur ce site. J’ai quelques notions d’Excel et arrive en général à me débrouiller avec les différents tutos mais aujourd’hui je suis coincé d’où cet appel à l’aide.

Je cherche une solution avec Excel pour calculer la meilleure répartition possible.
J'ai commencé un fichier (voir P.J.) qui me simplifie un peu le travail mais j'aimerais trouver les formules pour aller au bout de la répartition.

J’imagine que le tableau que j’ai réalisé est une usine à gaz donc mes excuses par avance car j’ai fait avec mes capacités 😉

Mon besoin :

Je dois répartir des fleurs sur des branches qui sont composées chacune de 2 tiges.

Mes données d'entrées sont :

• Le nombre de fleurs à répartir
• Le nombre de fleurs maximum par branche

Contraintes :

• Chaque branche ne peut être composée de tiges ayant le même nombre de fleurs. Cependant, je peux faire des branches à une seule tige pour repartir les fleurs restantes ou me permettre un meilleur remplissage des autres branches.
  Exemple : Pour répartir 151 fleurs avec un maximum de 23 fleurs par tige, la solution optimale est :
  • 4 tiges de 21 fleurs (= 2 branches)
  • 2 tiges de 22 fleurs (= 1 branche)
  • 1 tige de 23 fleurs (= 1 branche)

La solution idéale est de trouver la composition ayant le moins de branches possibles avec des tiges ayant un nombre de fleurs le plus proche possible du maximum.

Dans mon tableau, je suis parti sur un maximum de 23 fleurs à répartir par tige et sur un nombre de tiges maximum de 96 car cela correspond au standard maximum que j’ai à traiter. Le nombre de tiges maximum pourrait également être une donnée d'entrée mais, à ce stade, je ne l'ai pas prévue.

En ligne 7, j’ai calculé le nombre de fleurs restantes à répartir en fonction des différentes combinaisons possibles.

A partir de la ligne 38, j’ai créé un 2^ème tableau dans lequel j’ai noté quelques exemples de combinaisons que j’aimerais trouver à l’aide de formules afin de me faire gagner du temps.

On pourrait également imaginer renseigner le nombre de fleurs minimum souhaité par tige afin de faire apparaitre que les résultats ayant du sens car je n’ai pas trop d’intérêt à multiplier le nombre de tige.

J’espère que mes explications sont assez claires et que l’un d’entre vous pourra m’aider à trouver une solution.

Je suis preneur de toutes suggestions, y compris s’il faut modifier complètement le fichier car j’imagine que certaines formules doivent pouvoir éviter d’avoir recours à 2 tableaux.

En vous remerciant par avance de vos conseils et de vos réponses.

Excellement vôtre 😉

Lolo80

Bonjour,

C'est normal que vous ayez du mal à "trouver une formule". Je peux me tromper mais votre problème à première vue anodin est en fait assez complexe.

En effet, il s'agit de chercher une optimisation sur une répartition à priori quasi infinie des fleurs/branches…

Il va falloir définir des critères très précis si vous voulez qu'Excel trouve "la solution la plus intelligente". Selon moi (j'espère que je me trompe), cela ne peut etre résolu que de manière algorithmique, via VBA.

Autrement je pense qu'il est possible de créer un classeur, d'analyser beaucoup de combinaisons... mais on ne peut pas étudier l'infinité de cas possibles. Via VBA non plus d'ailleurs mais c'est censé etre plus rapide.

Dans les critères manquant je pense au nombre max de fleurs "perdues", a la taille réelle du stock à répartir, de combien peut varier le nombre de fleurs max par tige…

Dans l'état actuel des choses, si j'ai bien compris, il faudrait faire une feuille/cas et encore…

Bonjour Saboh12617,

Je vous remercie pour votre aide et votre réactivité.

C'est bien ce qui me semblait que mon problème anodin était complexe :D

J'avais imaginé qu'a partir du nombre de "fleurs restantes à repartir", on pouvait mettre en place une formule qui allait tester les différentes combinaisons jusqu'à trouver celle où il y avait moins de branches et/ou tiges puisqu'on définit le nombre de fleurs maximum par tige. Donc normalement, il n'y a qu'une combinaison possible par nombre de tiges pleines. Si mon nombre max est 23, l'algorithme pourrait commencer par tester 23, puis 22, puis 21 et ainsi remplir les tiges au fur et à mesure. Je me trompe ?

Concernant le nombre de fleurs "perdues", dans la pratique, j'arrive toujours à trouver une combinaison qui ne m'en fait pas perdre puisque je peux toujours faire une branche à une tige avec un nombre de fleurs différent des autres tiges. Je peux même combiner plusieurs répartitions comme dans mon exemple. Donc j'aurais tendance à vous répondre : 0 fleur perdue.

Pour la variation du nombre de fleurs maximum, normalement, il n'y en a pas besoin de le revoir à la hausse puisqu'on peut toujours trouver une solution avec moins de fleurs par branche. Cependant, on peut, comme je le proposais, définir un nombre de fleurs mini pour éviter le nombre de combinaisons.
Dans l'absolu, si j'arrive à calculer les 3 répartitions optimales (21, 18 et 16 tiges pleines dans mon exemple), j'en ai assez.

En espérant avoir répondu à vos questions, bonne soirée à vous 😉

bonjour Lolo80,Saboh12617,

une solution avec une fonction personnalisée, mais une solution avec ces nouvelles formules365 est faisable (mais il me manque l'experience, donc pour les "die-hards")

voir PJ cellule G1 = nombre de tiges, H1 = perte de fleurs, I1: Z1 = les tiges

PS. Pour les "die-hards", le résultat attendu sont ces cellules en orange de la ligne 3 , mais if faut encore repartir la 2eme cellule (7) vers les cellules I3:P3 en respectant le min et le max

Bonsoir BsAlv,

Je te remercie pour ton aide et pour la piste que tu proposes.

Je vois qu'en 2 lignes, tu arrives à trouver le meilleur résultat de tout mon tableau.

Je vais étudier ces fonctions car elles ont l'air intéressantes.

Bonne soirée ;)

re,

une nouvelle version (juste pour améliorer ma propre formule 365, avant quelqu'un autre la corrige)