Addition au plus proche d'un plafond donné selon certains critères
Bonjour,
Je cherche à faire en sorte d'additionner des cellules pour m'approcher d'un montant donné.
Mais j'aimerais qu'en plus le calcul tienne compte d'une proportion donnée entre ces cellules (réparties en 3 types).
Par exemple, la somme totale doit sélectionner des valeurs de ces 3 types selon une proportion définie en amont : au maximum 20% du type 1, 20% du type 2, et 60% du type 3...
Débutant pour l'occasion sur Calc/Excel, je n'arrive pas à comprendre comment intégrer celà au Solver. Ce dernier ne prenant pas en compte mes contraintes (cellules du type 1 <= cellule du pourcentage]). C'est assez abstrait pour moi pour le moment. :/
Merci de votre aide.
(Re)-Bonjour !
Je vous poste 2 fichiers réalisés sous Excel, mais j'imagine que c'est transposable à Calc.
Qu'est ce qu'on cherche --> affecter des unités jusqu'à s'approcher au maximum du total de points à affecter
Sur quoi on joue pour y parvenir --> l'effectif par type d'unité (colonne A)
Objectif :
- V1 : minimiser l'écart entre total de point actuel et total de point cible (cellule I3)
- V2 : idem V1 + minimiser l'écart avec la répartition souhaitée par type d'unité (cellule I4)
Les contraintes :
- Le contenu de la plage variable est un nombre entier (et pas forcément binaire ! L'effectif d'une unité peut dépasser 1)
- Ces nombres sont positifs (pas d'effectif <0, ça n'a pas de sens)
- [facultatif] Inutile de tester des effectifs trop élevés (voir colonne F), l'optimisation sera donc plus rapide
- V1 : ne pas dépasser les % max par type d'unité (répartition en colonne J)
Différence V1 - V2 :
Le problème de la V1 est que si le total % de répartition par type d'unité est de 100%, le Solveur ne peut pas systématiquement trouver de solution satisfaisante pour affecter la totalité des points. En effet, une unité de cavalerie représente par exemple 15 points, soit plus de 20% des 50 points restants à affecter après optimisation. Le Solveur n'affecte donc pas ces 50 points à cause de la contrainte de répartition.
La V2 consiste à apporter de la souplesse au niveau de la répartition, en cherchant à s'en approcher au maximum sans contraindre cette répartition exacte. La pondération en cellule L5 sert à donner plus ou moins de poids à la répartition dans l'optimisation globale. Avec une forte pondération, le Solveur privilégiera un respect exact de la répartition souhaitée, mais pas forcément l'affectation de tous les points possibles.
Merci beaucoup Pedro pour cette réponse détaillée, et la mise en forme des documents qui aide à la compréhension.
Je ne sais pas si c'est le cas sous Excel, mais le solveur de Calc ne conserve pas les paramètres précédemment entrés.
J'essaie de reproduire les contraintes énoncées, mais ne vois pas comment retranscrire les deux dernières (celle de répartition et la facultative).
Voici quand même les paramètres que j'entre :
Cellule cible : I3
Optimisation : Minimum
Par modification de cellules : A2:A18
Conditions de limitation : A2:A18 Nombre entier // A2:A18 >=0
Autre question, si chaque unité était unique. Il suffirait alors que je remplace la contrainte de nombre entier par binaire. C’est bien cela ?
Je ne sais pas si c'est le cas sous Excel, mais le solveur de Calc ne conserve pas les paramètres précédemment entrés.
Excel les conserve, ils sont donc visibles dans les classeurs transmis, si ouverts avec Excel.
J'essaie de reproduire les contraintes énoncées, mais ne vois pas comment retranscrire les deux dernières (celle de répartition et la facultative).
Je te conseille d'utiliser la V2 pour la répartition. Dans ce cas, la gestion de la répartition se fait au niveau de la valeur cible. Plus la répartition réelle s'éloigne de celle souhaitée, plus la valeur cible augmente (sachant que l'objectif du Solveur est de minimiser cette valeur). En effet, j'ai inclus dans la valeur cible, la somme des écarts absolus entre répartition réelle et demandée, multiplié par la pondération (qui te permet d'accorder plus ou moins d'importance à ce critère).
Pour la gestion des max, il s'agit de la colonne F. La contrainte est donc écrite de la manière suivante : $A$2:$A$18<=$F$2:$F$18.
Autre question, si chaque unité était unique. Il suffirait alors que je remplace la contrainte de nombre entier par binaire. C’est bien cela ?
Si chaque unité est unique, tu n'as pas besoin de mettre une contrainte binaire, il te suffit de changer la valeur max correspondante en colonne F sur 1. Dans le cas présent de ma V2, si tu mets 5% en J11, cela correspond à un max de 1 pour ton unité "général d'Erlon" (voir cellule F2).
Si ça peut aider, voilà le scénario saisi dans le Solveur Excel pour le fichier V2 :
C'est limpide !
Je vois toutefois que je dois encore assimiler la logique derrière Excel/Calc.
Merci encore Pédro pour votre explication et le temps accordé à mon problème.
C'est limpide !
Je vois toutefois que je dois encore assimiler la logique derrière Excel/Calc.
Merci encore Pédro pour votre explication et le temps accordé à mon problème.
Avec plaisir, je n'ai pas souvent l'occasion de m'exercer sur des problèmes en lien avec le Solveur !
Merci pour le retour, la validation, et bonne continuation !