Comment faire des lots de valeur égale?
Bonjour,
je souhaite faire 2 lots de valeur identique d'après un nombre de x cellules contenant des valeurs différentes.
Le but est d'obtenir 2 lots avec delta valeur = 0
ou si impossible
obtenir 2 lots identiques en valeur cumulée en mettant de côté une seule cellule valeur.
J'ai essayer avec une cellule cible (delta valeur) avec des conditions
mais le résultat ne fait que 1 lot.
Merci de votre aide et de votre indulgence.
Bonsoir,
où sont les conditions ?
La colonne A représente un seul lot ? Si oui, à quoi sert la B ?
Il faudrait remplir une 2è colonne.
Bonsoir Doux rêveur,
Merci de ta réponse.
Pour les conditions : partager les valeurs des cellules A2 à A12
pour que dans l'idéal E2 et E3 soit de valeur équivalente "50" dans mon tableau.
La cellule B2 sert à Xls à écrire à quel lot (1 ou 2) sera affectée la cellule A2, ainsi de suite jusqu'à B12.
J'espère mes explications plus claires.
Bonjour à tous
Les paramètres de votre solveur étaient bons. Ce sont les formules de votre feuille qui "clochaient".
- effacer la plage B2:B12
- en E2, mettre la formule :
=SOMME.SI(B2:B12;1;A2:A12)(la somme des valeurs de la colonne A quand B vaut 1) - en E3, mettre la formule :
=SOMME.SI(B2:B12;2;A2:A12)(la somme des valeurs de la colonne A quand B vaut 2) - en G2, mettre la formule :
=ABS(E2-E3)(la différence entre les deux précédentes sommes en valeur absolue)
Lancer le solveur et l'arrêter au bout d'un certain temps ou bien si la valeur cible n'est plus modifiée (voir les messages du solveur dans la barre d'état d'Excel).
Re,
Concernant le second cas (on élimine une des valeurs) et on cherche à diviser les valeurs restantes en deux parties de même total.
On simule un lot n° 3 qui sera le lot des cellules "sautées". Ce lot n° 3 ne comportera qu'une valeur (car on ne saute qu'une seule valeur).
Les lots peuvent donc prendre la valeur 1 ou 2 ou 3. Et on ajoute la condition en E7 : =NB.SI(B2:B12;3) et on ajoute la contrainte $E$7= 1 qui traduit le fait que le lot n° 3 ne comporte qu'une valeur (celle qu'on saute).
nota : si la contrainte $E$7 = 0 (au lieu de $E$7 = 1) alors on se retrouve dans le cas de la version v1 où il n'y a aucune cellule éliminée. La version v10 inclut donc aussi la version v1 par modification de la contrainte sur $E$7.
Bonjour mafraise,
Merci pour tes réponses.
A l'heure de tes réflections je dormai. Je vais refaire mon tableau avec tes formules (v1 & v10) en adaptant le Nb des cellules.
Je te tiendrai au courant des résultats obtenus.
Bonsoir mafraise,
J'ai testé mon tableau modifié avec la version V1 & V10.
-avec V1 : résultats identiques pour les 2 lots avec des valeurs identiques correspondant à (somme des valeurs/2)
Résultat OK pour moi.
Mais, car il y a un mais:
V1 me donne un delta de: 1,42109E-14 . Cela veut dire quoi ?
-avec V10 : résultat identique pour les 2 lots avec des valeurs identiques correspondant à (somme des valeurs - valeur exclue /2) , me donne un delta de : 0 ,
et me donne 1 dans la cellule valeur exclue que je retrouve bien en n°3 dans la colonne des lots.
Pourquoi un résultat différent entre les 2 versions? laquelle est la plus fiable ?
Merci d'avoir consacré du temps pour me sortir de cette galère. Cordialement.
Re,
V1 me donne un delta de: 1,42109E-14 . Cela veut dire quoi ?
Suivant la série de calculs effectués (et on ne maitrise pas la suite des opérations selon les modèles utilisés et comment Excel fait ses opérations arithmétiques) , le delta peut ne pas être égal à zéro tout en étant malgré tout très petit.
1,42109E-14 est égal à 0,0000000000000142109. C'est un nombre très petit qui est le résultat de très nombreux calculs effectués en interne par Excel. Cet enchainement de calcul peut aboutir à un nombre qui est une valeur approchée du résultat réel. C'est un phénomène bien connu.
Comment interpréter cette valeur "epsilonesque" ?
Les valeurs sources sont à deux chiffres maximum après la virgule. Les sommes de chaque lot sont donc aussi à deux chiffres maximum après la virgule. Et la valeur absolue de la différence des deux sommes est donc aussi une valeur à deux chiffres maximum après la virgule.
Or le solveur chargé de trouver le minimum de la différence trouve un delta = 0,0000000000000142109 avec delta qui, par raisonnement, doit être une valeur à deux chiffres max. après la virgule. Cela signifie que le delta réel ne peut être que nul (il est impossible par opérations arithmétiques formelles de trouver un delta avec plus de deux chiffres après la virgule). La valeur trouvée par Excel n'est qu'un "effet de bord" qui découle de la manière dont Excel effectue les opérations arithmétiques en interne.
On pourrait se prémunir de cette situation en utilisant les formules suivantes pour :
E2 : =ARRONDI(SOMME.SI(B2:B12;1;A2:A12);2)
E3 : =ARRONDI(SOMME.SI(B2:B12;2;A2:A12);2)
et pour G2 =ARRONDI(ABS(E2-E3);2)
Vous noterez que le solver met alors plus de temps à trouver une solution.
Je n'ai pas pu reproduire ce phénomène sur mon PC (et je n'ai pas de MAC). Il se peut même que le simple fait d'être sur MAC ou PC aboutisse à des résultats différents (mais de manière epsilonesque). Je ne sais pas comment Excel fait ces calculs arithmétiques en interne sur chacun des deux systèmes en liaison avec les processeurs via le système d'exploitation.
L'important c'est de savoir expliquer ces résultats et malgré tout de trouver une solution. Et vous avez constaté par vous-même que les deux sommes sont identiques.
Je ne sais pas décider si la méthode v1 est meilleure que la méthode v10. Ce sont grosso modo les mêmes. C'est la mise en œuvre par Excel qui aboutit au biais que vous avez constaté. Dans votre cas c'est la v1 qui présente ce biais. Rien ne dit que pour d'autres valeurs, ce n'est pas la v10 qui présenterait ce biais. L'important est de savoir que cela peut se produire et de savoir l'interpréter.
Bonjour Mafraise,
Merci pour tes explications claires car j'ai appris qqchose.
Mon tableau testé avec V1 & V10 aurait dû me donner le même résultat sachant que la colonne valeur est la même . pourquoi une différence?
Cordialement.
bonjour komet51, salut mafraise, Doux Rêveur,
une autre méthode
re,
Mon tableau testé avec V1 & V10 aurait dû me donner le même résultat sachant que la colonne valeur est la même . pourquoi une différence?
Comme dit précédemment, vous ne maitrisez pas comment Excel calcule en interne et comment la méthode utilisée par le solveur fait pour obtenir le résultat.
Il ne faut pas s'imaginer que l'informatique est quelque chose de merveilleux qui n'est pas soumis à la physique. Par exemple, symboliquement on peut manipuler la notion de 1/3 (un tiers). Notre petit cerveau comprend bien cette notion. Maintenant écrivez ce nombre sur une feuille avec un crayon.
Vous ne le pourrez pas. Car 1/3 s'écrit 0,3333333... de manière infinie or votre page n'est pas infinie (même si vous écrivez tout tout petit).
Une fois que vous avez écrit ce nombre (qui n'est pas strictement égal à 1/3), multipliez ce nombre par 3. On devrait trouver 1 (1/3 * 3 = 1).
Vous trouvez en fait 0,999999999... et non pas 1.
On est donc rattrapé par la réalité physique. Mathématiquement parlant, 1/3 * 3 vaut 1, mais physiquement avec un crayon et un papier vous ne pouvez pas trouver ce résultat. Et pourtant 1/3 * 3 vaut bien 1.
On peut démontrez mathématiquement que 0,99999999... à l'infini vaut 1.
En informatique, il peut se passer des choses analogues. Vous stockez un nombre (1/3) qui a une infinité de chiffres dans des mémoires ou registres qui eux sont finis. A un moment donné, cet effet de bord peut se manifester.
Bonsoir Mafraise,
Non, je ne mets pas en cause la façon de calcul d'excel.
Les version V1 et V10 étant semblables pourquoi les résultats diffèrent.
-V1 : 2 lots de valeur égale et delta de 0.
-V10 : 2 lots de valeur égale et delta de 0 mais avec une valeur exclue du calcul.
Cdlt.
Bonsoir BsAlv,
Merci de ton intervention. Joli tableau mais peux tu m'expliquer comment cela fonctionne.
Utilises tu le solveur ? car je ne vois rien en l'activant .Est ce que les formules sont celles lues au clic sur cellule ? comment puis je tester ta feuille xls avec mes données ?
Cdlt
Re,
Non, je ne mets pas en cause la façon de calcul d'excel.
Les version V1 et V10 étant semblables pourquoi les résultats diffèrent.
-V1 : 2 lots de valeur égale et delta de 0.
-V10 : 2 lots de valeur égale et delta de 0 mais avec une valeur exclue du calcul.
Je n'ai jamais prétendu que vous mettiez en cause la façon de calculer d'Excel. Quand j'écris "vous ne maitrisez pas" le "vous" ne désigne pas komet51 mais tout un chacun. J'aurais pu écrire "on ne maitrise pas" ou "personne ne maitrise".
Donc en réalité on a deux méthodes analogues (v1 et v10) mais pas identiques à la virgule près sinon il n'y aurait qu'une seule version
Ces deux méthodes ont un enchainement d'opérations certainement différent (sans doute proche mais pas identique). On n'a pas de contrôle sur cet enchainement d'opérations du solveur ni sur la manière (en interne) d'Excel de faire les opérations arithmétiques.
Ceci aboutit à deux résultats identiques en ce qui concerne la composition de chaque lot mais la limite calculée de chaque occurrence du solver (v1 et v10) est différente (de par la suite des opérations de chaque méthode de solver et de la manière dont Excel fait ces opérations arithmétiques). Comme je l'ai déjà écrit, les deux méthodes aboutissent au même résultat souhaité soit deux lots de même somme. La limite n'est pas le résultat souhaité, c'est juste la manière dont le solveur doit se comporter. Ensuite il faut interpréter ce que cela signifie. Et ça, je l'ai déjà décrit plus haut.
nota : indépendamment de tout ça, remettre en cause ce que tout le monde, y compris soi-même, tient pour acquis est toujours une excellente chose.
la plage C6:E16 sont des variables binaires et la somme de chaque ligne en colonne F est 1, donc il y a un "1"unique dans chaque ligne
C1 est 0,donc aucun écart entre lot1 et lot2
E3 <=1, donc max une pièce en lot3.
Maximaliser la valeur de lot1 (et donc aussi lot2) en C4 et D4
On utilise la méthode "Simplex", la meilleur méthode à condition qu'elle donne une solution, les autres donneront souvent une solution suboptimale, pas toujours la meilleur solution ou la même solution chaque fois qu'on lance solveur. (simplex non plus en cas de binaires ou integers)
Bonjour Bart,
Je suis nul en excel et j
'ai essayé ton tableau avec mes valeurs et le résultat est nul. Il n'y a bien que des conditions sur la cellule C4.
Voir images écran.Sinon puis je t'envoyer ma feuille Xls.
re,
je ne sais pas faire quelque chose avec un image, donc vous pouvez m'envoyer votre feuille.
Voila la feuille xls
la feuille, elle est vide ?
Rebonjour Bart,
je n'ai pas compris votre réponse"la feuille est vide.Revoilà la feuille.