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

 

[C@thy]

Bonjour Mic,

Là, comme ça, je vois pas, peut-être Super Monique ou MagikGénial Ti.

Tout ce que je sais c'est que le solveur ne peut pas t'aider, il faudrait une formule de calcul (par exemple des fonctions SOMME) or tu n'as que des données. Le plus dur à faire, c'est l'analyse, après on peut écrire une macro.

 

[Mic]

Pour C@thy Re: Calcul automatique dans un tableau

Merci C@thy d'avoir tenté de répondre à mon pb... et oui c'est dur, trés dur de trouver cette soluce mais je ne perd pas espoir!

Une personne aura peut-être un déclic "salvateur" génial qui sait!!!

Mic

 

[LaurentTBT]

Re: Pour C@thy Re: Calcul automatique dans un tableau

Bonsoir

Je n'ai pas de solution malheureusement, mais plutôt une remarque décourageante:
il y a plusieurs remplissages possibles pour ton tableau: celui que tu as trouvé manuellement, mais aussi ceux en intervertissant tes lignes dont la somme est la même (Echange par exemple la ligne 1 et la ligne 5 de ton tableau et tu as une autre solution possible). A mon avis, il va être difficile de remplir le tableau avec des formules.

Good luck quand même

 

[LaurentTBT]

Re: Pour C@thy Re: Calcul automatique dans un tableau

Re_bonsoir.

Je confirme, lorsqu'une solution existe, il y a plusieurs résultats possible, dont un qui marche à tous les coups (enfin je pense), mais en utilisant un max de 0. voir fichier joint

A noter que pour qu'il y ait une ou plusieurs solutions, il faut une condition simple: la somme des sommes verticales doit être égale à la somme des sommes horizontales (c'est mathématique: si on fait la somme de toutes les données intérieures au tableau, on obtient le même résultat en sommant d'abord en horizontal, ou d'abord en vertical).

Mais dans ton énoncé, il y a peut-être un détail qui excluerait ma solution avec plein de 0? J'espère que non. Sinon, ça va être coton...

J'espère que cela marche à tous les coups, car je suis incapable de reprendre mes formules. D'ailleurs, il ne faut pas me demander d'expliquer comment j'en suis arrivé là!

PS: Mic, n'oublie pas de zipper tes pièces jointes.

 

[Mytå]

Salut Mic, C@thy, LaurentTBT et le forum

J'ai un debut de solution avec le solveur qui semble fonctionner mais probleme avec les valeurs (la précison des decimales)

Peut-etre que quelqu'un trouvera une idée

Voir piece jointe

A suivre

 

[Mic]

Re: Pour C@thy Re: Calcul automatique dans un tableau

Merci encore à C@thy, Laurent et Myta pour vos pistes!

C'est dur, mais je trouverai...peut-être!

Pour Laurent, et oui le pire c'est qu'il faut minimiser les 0, du style une somme de 8 doit se décomposer en par exemple 3 valeurs : 2,2,3 et non 0,0,8 et non aussi 0,5,3!

Il faudrait arriver à optimiser ces valeurs... l'horreur quoi!

Pour Myta, merci beaucoup, c'est une m'a donné une super piste que je suis en train d'exploiter!

Merci à tous, et si quelqu'un trouve une soluce complète sous forme XLS, je suis preneur!

;-)

Mic

 

[LaurentTBT]

Bonsoir tout le monde.

J'ai trouvé un moyen par des formules, avec cependant le besoin de passer par un tableau intermédiaire.

Comme je l'ai déjà écrit, il existe plusieurs solutions qui répondent au critère des sommes. Pour choisir, j'ai laissé faire le hasard en utilisant la fonction ALEA. En appuyant sur F9, excel recalcul, notamment cette fonction ALEA, et on obtient d'autres solutions.

Les formules sont assez lourdes, aussi j'ai décomposé ma méthode sur une feuille d'explication.
J'ai adjoint un bouton et une petite macro pour masquer les calculs intermédiaires.

Je ne sais pas à quoi sert ce calcul, en tout cas, il m'aura permis de me frotter à des fonctions que je n'ai pas l'habitude d'utiliser (INDEX, RANG...), et d'utiliser une formule de l'excellente Monique pour traiter des problèmes de doublons.

Je suis sur une piste pour obtenir le même résultat sans passer par un tableau intermédiaire, mais je ne promet rien.

J'espère que ma méthode sera déjà une réponse satisfaisante.

Bonne soirée à tous

 

[LaurentTBT]

Re: Pour C@thy Re: Calcul automatique dans un tableau

Oups, Mic, je n'avais pas vu ton dernier post.

Bon, le hasard fera peut-être bien les choses, en limitant les 0. Peux-tu me donner exactement l'énoncé du sujet en ce qui concerne les solutions acceptables et celles qui ne le sont pas? Je pourrai peut-être adapter ma méthode.

En tout cas, dis-moi ce que tu penses de mon fichier.

@+

 

[Mic]

Re: Pour C@thy Re: Calcul automatique dans un tableau

Merci Laurentde ton aide!

Le but est un peu similaire au "carré magique", connaissant la somme de toutes les colonnes et rangées, on doit remplir le tableau en y mettant des nombres entiers et positifs.

En plus!, il faudrait qu'on arrive à optimiser chaque colonne et chaque rangée en mettant l'écart en tre ces chiffres le moins élevé.

Je m'explique:

si le total d'une colonne fait 8 et que nous avons 5 cases de cette colonne à remplir, il faut que celles-ci soit du style (au mieux) : 2;1;2;1;2 et non pas 0;5;2;1;0. lC'est là que ça se complique vraiment!

Le solveur pour de petites sommes (exemple :1) sur 5 cases me sort des chiffres décimaux et pire des nombres négatifs! Si je travaille en arrondis, toutes les cases sont à 0!

Tu vois le truc!

Je te remercie pour ton fichier, je le vois dès demain et je te tiens au courant... Je ne peux le faire aujourd'hui.

@+ et merci encore

Mic

 

[LaurentTBT]

Re: Pour C@thy Re: Calcul automatique dans un tableau

Bonjour Mic, bonjour tout le monde, et ...
TRES BONNE FETE A TOUTES LES MAMANS.[b/]

Mic, tu me donnes du fil à retordre, mais je ne suis pas du style à vite laisser tomber. Alors voilà une nouvelle version, avec une feuille supplémentaire où je cherche à minimiser les écarts. J'ai rajouté aussi un calcul de ces écarts. Tu verras que je ne suis pas à la solution optimale, puisqu'avec l'exemple que tu as calculé manuellement, tu fais mieux que moi.

Cependant, j'ai une petite idée derrière la tête. En effet, ma méthode consiste à optimiser en partant des plus petites sommes. Et forcément, les grosses sommes non optimisées entraînent des gros écarts. Je pense maintenant à chercher à optimiser d'abord les grandes sommes, mais cela nécessitera de grosses modifs. Alors peut-être à plus tard si j'y parvient.

NB: c'est dommage, j'aimais bien ma solution qui laissais une petite place au hasard...

 

[Mic]

Re: Pour C@thy Re: Calcul automatique dans un tableau

Merci Thiebaut,

Pas mal ta soluce! Dans l'énoncé de mon problème j'ai oublié de te dire ou je me suis mal exprimé car il faudrait optimiser uniquement les cases des colonnes et non pas des rangées.

Exemple : soit un total rangée de 7 : on peut avoir sur 5 cases de la rangée 1;2;0;0;4.
En revanche pour un total colonne de 8 il faut que l'écart soit minimum entre chaque cases de la colonne.

Voilà donc les dernières infos!!!

Merci encore!

Mic

 

[LaurentTBT]

Re: On va y arriver: Calcul automatique dans un tableau

Voilà qui simplifie un peu.

La version 1.2 que voici donne le même résultat que ton calcul manuel: Euréka!

Sauf que: remplace la somme de la 2ème colonne (4) par 350... et voilà une mauvaise surprise.

Oulala, je commence à avoir mal à la tête avec ton carré diabolique!

Enfin, j'entrevois peut-être une idée...Il faut que je rajoute quelques conditions supplémentaires.

Alors gardons courage, on finira bien par en percer le mystère.

Je replanche encore un petit peu dessus. A tout de suite.

 

[LaurentTBT]

Cette fois, je crois que j'y suis.

Je retrouve toujours le même tableau que toi, et si tu mets des nombres très élevés pour les sommes, il me trouve aussi une solution sans nombres négatifs cette fois.

NB: j'ai rajouté un petit plus. A partir de tes données initiales, mets 30 en H7, tu auras une petite surprise.

En tout cas, compte tenu de mon mal de crâne actuel, le premier qui trouve un cas où ça ne marche pas... Non, je rigole. Mais la prochaine fois, j'y réfléchirai à deux fois avant de me lancer dans un tel casse-tête.

Besoin d'aller m'aérer un peu l'esprit maintenant, alors bonne journée à tous et à bientôt.

 

[LaurentTBT]

Le premier qui trouve un cas où ça ne marche pas... et bien c'est moi!

En effet, avec 10 au lieu de 6 en case H6 pour la solution particulière, et bien j'ai encore des nombres négatifs.

Bon, je laisse tomber pour l'instant, car je commence à sérieusement m'embrouiller les neurones. Je reprendrai plus tard si personne ne nous aide d'ici là. (c'est clairement un appel au secours).

@+ et désolé pour la fausse joie.

Laurent