Monsieur Excel
Pour tout savoir faire sur Excel !

30 décembre 2017

Une nouvelle énigme (b)

Comme nous l’avons vu à la fin de l’article précédent, l’analyse de toutes les permutations possibles de 1 à 10 nous emmènerait trop loin, avec 3.628.800 permutations possibles. Mais nous pouvons facilement réduire le problème avec un peu de bon sens. Voici deux façons différentes d’y parvenir…

Réduction par analyse globale

Le nombre moyen par sommet est de 5,5 soit de 16,5 par groupe de trois lettres. Pour obtenir un total de 20 pour chacun des 5 trinômes importants, il faudra donc taper dans les nombres élevés. Et ce en priorité pour A et B, qui interviennent chacun 3 fois dans un trinôme de 20. Puis pour C et/ou D qui interviennent 2 fois dans un trinôme de 20 et une fois dans un trinôme de 17.

Réduction par analyse détaillée

Prenons le cas des sommets A et B. Comme chacun doit mener à 3 trinômes de total 20 avec à chaque fois 2 partenaires nouveaux, on peut éliminer pour eux les valeurs de 1 à 6. Si par exemple A était égal à 5, on pourrait avoir 5-9-6 ou 5-8-7 mais on n’aurait pas de troisième possibilité.

Solution possible

En tenant compte de deux remarques précédentes, on voit que l’on pourrait déjà bien simplifier le problème en mettant 10 en A et 9 en B. Il ne resterait alors plus que 7! combinaisons possibles, soit 5.040 cas à analyser. Si l’on trouve une solution, ce sera forcément la bonne. Sinon, il suffira de relâcher cette contrainte…

L’écran suivant montre comment j’ai résolu le problème. On tire les valeurs dans le bloc D3 :D12 à partir des aléas en colonne C. Les cellules à fond bleu sont celles définies par notre choix ci-dessus. La cellule « Ecart » en E20 calcule le nombre d’écarts par rapport aux objectifs.

On découvre en fait qu’il y a deux solutions possibles, que j’ai reproduites en I3:K12.

Remarque 1 – Nous comprenons pourquoi la question posée était d’obtenir le produit C*J*D car celui-ci reste le même avec ces deux solutions.

Sur 10 résolutions, il m’a fallu en moyenne 2.334 itérations ce qui correspond comme par hasard à 5.040/2 puisqu’il y a deux solutions.

Remarque 2 – On pourrait peut-être trouver d’autres solutions en mettant d’autres valeurs de départ en A et B. Mais cela va compliquer les formules en D5:D12…

rédigé par Hervé Thiriez | 4 commentaire(s)

4 Commentaire(s):

  • Bonjour,

    Mes voeux en ce jour de l'an ou votre énigme m'a permis de remettre de l'ordre dans mes neurones ;-)
    Ce coup ci, le solveur arrive assez facilement au bout de l'énigme proposé.
    En posant comme contrainte "tous différents" ; "entier" aux cellules contenant A à J ; que les sommes = 20 et cellule objectif : CID = 17 on trouve de nombreuses solutions.
    Comme vous en aviez donné 2 avec C*J*D = 160 , je vous en propose deux avec C*J*D = 100.

    Merci encore pour votre blog

    A 7 9
    B 9 7
    C 5 10
    D 10 5
    E 8 1
    F 4 4
    G 3 6
    H 6 3
    I 1 8
    J 2 2

    By Blogger Unknown, sur 8:34 PM  

  • Meilleurs vœux

    Si on regarde bien le schéma, on s’aperçoit que une fois fixé A,B,C,D toutes les autres valeurs peuvent être trouvé en résolvant de simples équations (E=20-A-C, ...).
    Et si comme vous l'avez dit : "nous avons besoin de A>=7" (donc idem pour B vu que le schéma est symétrique)

    on pourrait tester 4*3*10*9 combinaisons = 1080
    Il va de soit que C et D on besoin de grandes valeurs, donc on pourrait aussi trouvé le minimum pour C.

    et éventuellement réduire encore le nombre de combinaisons à tester.
    avec A=7, 8 possibilités pour B,C,D
    avec A=8, 8 possibilités pour B,C,D
    avec A=9, 8 possibilités pour B,C,D
    avec A=10, 16 possibilités pour B,C,D

    soit 40 tests à effectuer si nos conditions de départ sont bonnes.

    By Blogger Benji, sur 5:13 PM  

  • Je n'avais pas utilisé le solveur car je me méfie comme de la peste du solveur d'Excel quand les variables sont entières et/iu quand le problème n'est pas linéaire.

    En effet, dans ces deux situations, j'ai eu de multiples exemples ou le solveur affirmait avoir trouvé la solution optimale alors que ce n'était pas le cas.

    Le solveur d'Excel n'est fiable que pour des modèles linéaires avec des variables toutes continues. Si ce n'est pas le cas, vous risquez d'obtenir de faux résultats...

    By Blogger Hervé Thiriez, sur 10:00 AM  

  • Bonjour M. Thiriez,

    Ayant également déjà eu de nombreuses mauvaises surprises avec le Solveur, je fais mienne votre mise en garde.
    Mais pour une fois qu'il se montrait efficace, je me devais aussi de lui rendre justice :-)

    By Blogger Unknown, sur 10:07 AM  

Enregistrer un commentaire

<< Accueil