Solver avec condition date

Bonjour,

j'ai une macro VBA incluant un solver pour trouver dans une liste de poids, une valeur cible au préalable renseignée.

J'aimerais ajouter à cette macro la condition qui est de prendre les poids du plus ancien au plus recent

en piece jointe le fichier excel avec la macro

si vous avez besoin de plus d'infos n'hesitez pas

Yannis

Bonjour,

Pour répondre à cette problématique via le Solveur, il faut te poser la question du moyen de convertir cette condition en calcul numérique dont le résultat diminue lorsque la condition est remplie. Un essai qui ajoute le calcul suivant à la cellule objectif utilisée par le Solveur :

=MOYENNE.SI(N:N;1;F:F)-MIN(F:F)

Par contre, quitte à passer par VBA pour exécuter le Solveur, autant faire en sorte de dimensionner les plages du scénario aux lignes complétées dans le fichier. Car actuellement, le Solveur inclut des lignes vides, ce qui engendre un temps de calcul supplémentaire et inutile.

Comment j'ajoute la formule "=MOYENNE.SI(N:N;1;F:F)-MIN(F:F)" a la macro ? pour que ça fasse la manip ?

Oui la liste n'est pas fixe et je vais mettre une formule qui adapte le calcul a la plage saisie

toujours toi Pedro22 MERCI

Yannis111 a écrit :

Comment j'ajoute la formule "=MOYENNE.SI(N:N;1;F:F)-MIN(F:F)" a la macro ? pour que ça fasse la manip ?

Oui la liste n'est pas fixe et je vais mettre une formule qui adapte le calcul a la plage saisie

toujours toi Pedro22 MERCI

Dans la macro, il n'y a actuellement aucun calcul, car ceux ci ne changent pas. Ce calcul est à mettre directement sur la feuille, de sorte que la cellule P1 utilisée par le Solveur l'intègre d'une manière ou d'une autre. En dehors de la cellule P4, toutes les autres peuvent être résumées en un seul calcul placé en P1 (ou ailleurs sous réserve d'adapter la référence dans le Solveur).

=ABS(P4-SOMME.SI($N:$N;1;$D:$D))+MOYENNE.SI($N:$N;1;$F:$F)-MIN($F:$F)

Les plages indiquées dans les calculs peuvent être dimensionnées en mettant ton tableau source sous forme de tableau structuré, par contre ça ne règlera pas le défaut de la macro actuelle, qui indique une plage fixe au Solveur.

Bon je pense qu'il n'est pas possible de mettre en place la fonctionnalité dont j'ai besoin du moins avec le solver.

peut être en utilisant pas le solver : faire la somme de chaque poids pour arriver au poids cible en y ajoutant la contrainte de la date

Pour mettre a jour dans la macro du solver le nombre de ligne n y a t-il pas une possibilité ?

Bonjour,

Peux-tu expliquer ? La manip' ci dessus permet bien une prise en compte de la date dans l'optimisation réalisée par le Solveur.

Bonjour Pedro22

Eh bien j'ai essayé de mettre en place la formule afin que le solver prennent en compte la date dans son calcul mais en vain, j'ai que des erreurs .

Si tu as le temps je te propose d'essayer et de me retourner le fichier

Cordialement

La formule MOYENNE.SI renvoie une erreur si aucune ligne de la colonne N ne contient de 1. Cette erreur pose problème au Solveur, car il se base toujours sur une valeur numérique pour "s'orienter". Pour contourner le problème, on peut l'inclure dans une fonction SIERREUR ce qui donne en cellule P1 :

=ABS(P4-SOMME.SI($N:$N;1;$D:$D))+SIERREUR(MOYENNE.SI($N:$N;1;$F:$F)-MIN($F:$F);0)

Testé à l'instant sans problèmes.

J'ai aussi révisé le code de la macro pour lancer le Solveur :

Sub SOLVER()

Dim DerLig As Long

DerLig = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
SolverReset
SolverOk SetCell:="$P$1", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$2:$N$" & DerLig, Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$N$2:$N$" & DerLig, Relation:=5, FormulaText:="binaire"
SolverSolve

End Sub

Merci pedro22 pour ce retour

En effet j'ai mal du saisir la formule, je suis tres novice sur excel et je suis de voir les choses comme tu les vois

ça à vraiment l'air de bien fonctionner

j'aimerais juste que tu m'explique si tu le peut t'as formule qu'on rajouté et le système du solver avec cette methodes

Cordialement

Yannis

Le principe du Solveur est de tester plein de combinaisons qui répondent aux contraintes que tu lui impose. Le choix des meilleures combinaisons repose sur une fonction réponse, dont la valeur numérique évolue en fonction des combinaisons. Dans le cas présent, on lui indique que la valeur optimale recherchée consiste à minimiser le résultat de la formule.

Dans ton cas, la formule calcule d'une part l'écart, en valeur absolue (fonction ABS) entre la valeur cible (P4) et la somme des lignes retenues (SOMME.SI). D'autre part, on ajoute à cet écart la différence entre la date moyenne des lignes retenues (MOYENNE.SI) et la date minimale de ton jeu de données. Ainsi, le résultat de la fonction diminue lorsque les lignes retenues concernent les dates les plus anciennes.

Tu peux éventuellement affecter un coefficient à chacune des 2 composantes de la fonction réponse (proximité avec poids cible, et ancienneté des dates retenue) pour accentuer l'optimisation sur l'une ou l'autre.

Concernant la macro : j'ai ajouté la suppression du paramétrage précédent, et modifié la réécriture d'un nouveau scénario. Désormais, les plages indiquées dans le Solveur tiennent compte du nombre de lignes complétées dans ton fichier. La dernière ligne est déterminée ici sur la base de la colonne D.
Sous forme textuelle, cette instruction DerLig = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row signifie : "on affecte à la variable DerLig le numéro de la première ligne non vide en colonne D, en remontant les lignes depuis la dernière du classeur." Tu peux mimer une partie de cette action en sélectionnant une cellule à la fin de ton classeur, et en appuyant simultanément sur CTRL + FLECHE HAUT.