Calcul automatique dans un tableau

[Mic]

Bonjour,

J'ai besoin d'un petit coup de main SVP!!!

J'ai un tableau avec sommes des colonnes et des rangées connues.

Je voudrai que ce tableau se remplisse automatiquement en fonction de ces sommes.

J'ai essayé avec le solveur mais je dois avec un manque de connaissance car ça ne fonctionne pas!

Si une bonne âme pouvait me dépanner en me fournissant la soluce sous la forme d'un petit fichier xls ce serait sympa...

Ci-joint le petit tableau de travail avec explications.

Une petite question aussi : Le solveur peut-il être lancé automatiquement, sans passer par lui, du moment qu'une cellule est modifiée?


Merci beaucoup!

Mic

 

[Mic]

En tout cas on avance vers la soluce, et merci encore à toi LaurentTBT d'y croire jusqu'au bout...

Peut-être un jour on y arrivera, je te tiens au courant!

@+

Mic

 

[LaurentTBT]

Oui, on n'en est plus très loin. Une bonne nuit nous portera conseil, et comme demain j'ai 6h de train de prévues et que j'ai pu récupérer un portable, j'espère bien en venir à bout.

Au fait, à quoi cela te sert-il si ce n'est pas indiscret?

 

[Mytå]

Re: Calcul automatique dans un tableau(avec le solveur)

Re a vous tous

Me revoila en Macro avec le solveur

Tous semble bien fonctionner

A toi de finaliser Mic manque qu'a forcer le OK
pour le message du solveur (Résulat du solver)

A suivre

 

[Mic]

C'est pour gérer du personnel, l'envoyer régulièrement quelque part!

En fait, les totaux de rangées correspondent au nombre de personnes à envoyer, et à chaque rangée correspond une date.

Le total des colonnes représente le total de personnes à envoyer sur une période données (5 ou 6 dates) pour une entreprise. Il ya en fait autant de colonnes que d'entreprises. C'est pour celà que l'optimisation des rangées ne sert à rien.

Le fait d'optimiser la colonne permet de faire partir les personnes sans pénaliser l'entreprise, d'où l'importance d'avoir le plus petit écart possible.


Cette recherche de solution du "carré magique" pourra je suis sur servir à d'autres sujets complètement différents, et je trouve cette recherche de soluce super intéressante.

Voilà le pourquoi de la chose Laurent! Je te souhaite un bon voyage, qui devrait être bon si tu as le portable!

@+

Mic

 

[Mytå]

Re Mic et le Forum

On a travailler pour rien alors (JOKE)

Faudrait peut-etre voir une autre methode mais pas se soir j'ai pas le
temps je suis sur le Chat la.

Moi qui avait trouver avec le solveur un fichier presque parfait ,
mais bon pas grave peut-etre qu'il servira a d'autre personne

Amicalement Mytå

 

[Mic]

Non Myta, on ne travaille pas pour rien, bien au contraire!

Ta soluce est super, quasi parfaite, on atteint presque ce qu'il faudrait! On n'est pas loin du tout.

En fait pour arriver au top du top il faudrait optimiser les colonnes afin que pour chaque case d'une même colone, l'écart soit de 1 maximum!

C'est à dire que pour un total de 17, on ait que des 3 ou des 4 dans les cases. Le summun serait de les trier dans l'ordre 3;4;3;4;3!!!!
( j'ai mis ce que l'on devrait avoir de plus optimisé (à la main) dans le fichier joint).

Ta soluce par le solveur, c'est presque ça d'ailleurs!

Au fait Myta, tu connais un moyen pour que la macro puisse empêcher la fenêtre du solveur d'apparaître, ou on ne peux pas le faire?


Merci encore de ton coup de main, c'est super sympa et tout ce que l'on a fait et ce que l'on fait toujours avec toi et Laurent sert vraiment à quelquechose, sois en certain!


@+

Mic

 

[zon]

Bonsoir à tous,



Mic pour le message d'erreur je crois qu'on a trouvé une soluce je crois sur le chat avec myta:....

Il reviendra plus tard pour te donner une autre soluce...



A+++

 

[Mytå]

re le forum

Bon encore une contraite a valider se soir je vois pas mais apres une nuit de sommeil pt

Deja 3/4 de fait Mic

A+ Myta

 

[Mytå]

Re Mic et le forum

Version 2 avec le probleme de validation du OK du solveur

Solution fourni par Zon.

Me reste a travailler pour ta derniere demande soit :

En fait pour arriver au top du top il faudrait optimiser les colonnes afin que pour chaque case d'une même colone, l'écart soit de 1 maximum!

P.S. Je travaille la dessus aujourd'hui j'essaye de trouver de quoi

 

[LaurentTBT]

Bonsoir Mic, Mytå, Zon, C@thy, et tous les amis d'XLD.

Je n'ai pas vu le temps passer dans le train, et voici la version n°4, que je vous consil d'ouvrir pour comprendre ma prose qui va suivre.

Je n'arrive toujours pas à optimiser dans tous les cas;
J'ai une solution (la jaune), où j'optimise en priorité les colonnes les plus élevées.
J'ai une solution, (la grise), où j'optimise en priorité les petites colonnes.
Le mieux par rapport à la version 3, c'est que je n'ai plus de cas aberrants avec des nombres négatifs.
Le problème, c'est que c'est parfois l'une, parfois l'autre la meilleure. (clique sur les 2 boutons exemple 1 et 2 pour voir, et j'ai un troisième exemple qui génère les valeurs initiales (bleues) au hasard). Il faut sans doute trouver un algorithme médian, mais je suis à court d'idée.

J'ai habillée ma feuille, pour donner des exemples, et pour essayer de comprendre.

J'ai eu aussi l'idée de lancer plusieurs fois ma troisième solution (aléatoire), et de mémoriser la fois où elle me donne le meilleur résultat. Tu peux le faire en cliquant sur le bouton "Boucle visible" pour voir comment cela fonctionne, et en mettant le nombre de calculs à faire dans l'itération (si tu ne rentre rien dans la boite de dialogue ouverte, une valeur sera saisie par défaut)

Ensuite, en faisant une boucle très longue (10000), on peux espérer avoir une solution proche de l'optimale (si on n'a vraiment pas de chance, on peut n'avoir que des solutions avec écarts importants). Dans ce cas, choisis le bouton "boucle invisible" pour gagner du temps (la macro shunte les affichages intermédiaires).

Bref, je me suis bien amusé, mais je n'ai pas la solution finale.
Je me pose une question sur mes calculs d'écarts moyens: ce nombre qu'il faut minimiser représente-t-il bien ce que l'on cherche? a priori, oui, puisque cela correspond pour l'exemple 1 (le tien)

Bon, je n'aurai malheureusement plus autant de temps ces jours-ci, alors j'espère que Mytå et Zon auront plus de réussite. Ceci-dit, je suis d'accord avec toi, cet exercice est assez intéressant, et même très prenant!

Good luck.

 

[LaurentTBT]

Bonjour à tous, Salut à tous ceux qui planchent sur ce carré diabolique

Cette fois, je crois que c'est bon.

Ceci-dit, ne nous emballons pas, j'ai déjà failli titrer un de mes précédents posts par "Euréka" pour finalement trouver des erreurs. Mais bons, c'est peut-être la bonne.

Je suis reparti sur une toute nouvelle piste, d'ailleurs peut-être plus simple dans le principe.

J'ai réparti la somme des colonnes proportionnellement aux sommes des lignes où du moins de ce qu'il en reste, en partant des plus élevées vers les moins élevées.
Les formules ne comportent que des multiplications, sommes et divisions, plus quelques subtilités pour l'affichage.
J'ai retouché la feuille "explications" en espérant être compréhensible.

Dans tous les cas que j'ai testés, j'obtiens toujours la meilleure optimisation.

J'ai remis une feuille avec cette nouvelle solution, ainsi que la solution aléatoire (pour faire des boucles, afin de voir si il n'y a pas par hasard mieux que ma solution proportionnelle), et j'ai rajouté la solution avec solveur (qui donne des résultats moins optimisés). J'ai aussi eu des cas ou le solveur tournait pendant longtemps (j'ai interrompu).

Ceci dit, si vous arrivez à optimiser votre solution avec solveur, cela permettra de vérifier si mes formules sont vraiment optimales, car je n'en ai pas la preuve.

En tout cas, cette fois, c'est bien ma dernière version, car je vais m'absenter pour la fin de la semaine (pas d'internet dispo là où je vais). Alors si vous pouvez jeter un coup d’œil d'ici demain...

@+ et je croise les doigts pour ce dernier jet.

NB: Mytå, comment fais-tu pour insérer les références du solveur dans ton projet VBA? Je n'ai pas réussi et j'ai du repartir de ton classeur.

 

[Mytå]

Salut de nouveau le forum.

LaurentTBT pour ajouter le solveur dans VBA

1. Tu dois avoir l'utilitaire solveur d'installer dans les macros complementaires.

2.Tu crées un macro avec l'enregistreur de macro du solveur ou tu passes directement sur une feuille.

3.Ensuite dans l'editeur VBA dans Outils > references > coche solveur.xls

Mytå

 

[LaurentTBT]

Merci, Mytå

Il me manquait l'étape 3!
Je n'ai jamais utilisé le solveur. Ce fil va être pour moi l'occasion de m'y mettre.

Où en est-tu avec l'optimisation du carré diabolique?

Comme je l'ai mis ci-dessus, je ne suis pas sûr du tout d'avoir la meilleure solution. Ceci-dit, on peut certes minimiser les écarts entre les nombre d'une même colonne, mais de là à n'avoir que des écarts de 1, j'ai de sérieux doutes sur la faisabilité de la chose.

Bon courage.

 

[Mytå]

Re LaurenTBT

j'y arrive mais pas de facon automatique je dois efface des cellules et relancer le solveur, cela reste a suivre

Mytå

 

[zon]

Bonjour à tous,



Bravo Laurent ton fichier ne peut pas être plus explicite..



Myta je ne me souviens plus trés bien ce dont on avait parlé sur le chat ...



A+++