XL 2019 Match somme et Ecart Type

[darkfuryo]

Bonjour à tous, je vous expose mon petit problème :
Problème : retrouver les 5 nombres de départ possibles

Données :
On vous donne la somme totale de ces 5 nombres : 169
On vous donne également l'écart moyen ou Variance 571,76, et donc l'écart-type qui est la racine carré de la variance soit 23,91150351.

J'ai crée une feuille Excel (en PJ) qui permet de jouer sur les 5 nombres de départ et qui détaille l'ensemble des opérations pour arriver à l'écart-type.
J'ai également inséré dans cette feuille la formule ECARTYPEP qui permet de trouver l'écart-type à partir des 5 nombres de départ en direct en guise de vérification.

En y passant 3h de mon temps, et en jouant sur les 5 nombres de départ, j'ai trouvé 2 sets de nombres qui correspondent exactement à la variance demandée : 7, 21, 29, 34, 78 et 8, 19, 19, 51, 72.
Seulement il doit exister une méthode plus efficiente qui donnerait toutes les combinaisons possible d'un coup.
J'ai cherché une fonction inverse de l'écart-type, je n'en ai pas trouvée.

J'ai trouvé sur votre forum une feuille de calcul "match somme" sur ce fil : https://www.excel-downloads.com/threads/combinaison-de-valeur-match-somme.70048/
Qui permet de retrouver toutes les combinaisons de nombres pour une somme donnée, puisque je sais que ma somme est 169 et que j'ai 5 nombres de départ, c'est pas mal, mais il faut également remplir la condition de variance/écart-type demandée.

N'ayant pas les connaissances avancées en Excel suffisantes, je viens vous demander un peu d'aide.
Merci !

 

[sylvanu]

Bonsoir Darkfuryo,

A mon très humble avis, vous avez deux équations ( Somme et Ecartype ) et 5 variables. Donc c'est insoluble.
J'ai tenté plein d'algos par itérations ou par dichotomie en pure perte.
Aussi je vous propose la bonne vielle méthode de bourrin en PJ, en balayant toutes les solutions.

On trouve 717 solutions en 17s ( sur mon PC )

Les limites :
1- Il faut fixer une tolérance sur l'écartype ( ici j'ai 0.0002%)
2- Les nombre N1 à N5 doivent être compris entre 1 et 100 ( pour limiter le temps de calcul )

 

[soan]

Bonsoir,

Sans l'écart moyen, la variance, et l'écart-type, et si on peut utiliser uniquement
les 4 opérations de base, ce serait comme le jeu télévisé "Le compte est bon".

soan

 

[sylvanu]

Re bonsoir, bonsoir Soan,
Le même sans les doublons.
146 combinaisons uniques ( en 17s )
( La V3 a été supprimée car fausse au niveau des doublons )

 

[darkfuryo]

Bonjour Sylvanu,
Tout d'abord un grand merci pour le temps que tu as pris pour réaliser ce fichier.

A force de chercher je suis également parti sur la méthode bourrin, à savoir trouver l'ensemble des combinaisons de 5 nombres (disons compris entre 5 et 82 afin de limiter les possibilités, car j'avais trouvé empiriquement 2 sets de nombres qui se trouvaient dans cet écart) qui peuvent donner la somme de 169 puis ensuite utiliser la fonction ECARTTYPEP de Excel avec une mise en forme conditionnelle de cellule en vert pour pointer les solutions qui correspondaient à la variance que je recherchais.

Seulement, il existe certes beaucoup de fichiers Excel disponibles qui font ce que je cherche, mais la plupart sont limités, soit dans leur construction, par exemple ils n'acceptent qu'une 15aine de nombres en entrée au lieu des 82 qui m'intéressent, soit ils finissent par bloquer Excel car le nombre de possibilités est immense et les algos ne sont pas prévu pour ça.

J'ai fini par trouver un fichier très bien qui fonctionne pour le besoin tel que je l'avais prévu, le fichier Accès Factures avec Somme Totale v4.3.4.xls proposé par Eriiiic par ici :

Microsoft 365 - Combinaison de montants égales à une valeur cible

Bonjour, Je débute ma carrière professionnelle et je dois faire face à l'outil excel (indispensable). Mon problème est le suivant: J'ai une base de donnée avec un certains nombres de montants (en valeur). Je sais que la somme de certains de ces montants est égale à une valeur cible donnée...

www.excel-downloads.com

Ce fichier est limité de base à 240 solutions, mais si comme me l'a proposé Eriiiic, on le convertit en .xlsm et qu'on modifie la variable nbMaxSol à plus, disons par exemple 2000 solutions, le fichier prendra son temps (autour de 2mn ici) mais il arrivera au bout du calcul.
J'étais donc en train de lancer fastidieusement ce fichier plusieurs fois, et je pense qu'il m'aurait fallu un certain temps avant de recouper la centaine de solutions que ton fichier permet de récupérer en quelques secondes.

Pour en revenir à ton fichier, il est extraordinairement rapide et efficient, de plus réalisé en quelques heures, je te félicite donc et te remercie une nouvelle fois.
D'autant plus que tu as également pensé à faire une version sans doublons !

Concernant cette dernière version, elle est superbe mais elle comporte encore 12 doublons (voir image en PJ), j'ai utilisé dans la colonne I la formule suivante : =SOMMEPROD((NB.SI(A2:E2;A2:E2)>1)*(A2:E2<>""))

Afin d'identifier rapidement les doublons et ensuite une mise en forme conditionnelle pour mieux les voir.

Ce n'est pas très grave, cela me permet déjà de trier très rapidement et beaucoup plus efficacement les résultats.

 

[sylvanu]

Bonjour,
Pouvez vous renvoyez votre jpg, il refuse de s'ouvrir.
Je n'arrive pas à trouver les doublons.

 

[sylvanu]

Oups, je crois avoir ciompris.
Pour moi un "doublon" est deux tirages de 5 nombres identiques.
Par ex 1,2,3,4,5 et 5,4,3,2,1
Je vois que votre Sommeprod met en exergue les combinaisons où deux nombres sont répétés.
Dois je les supprimer ? Je vais vous faire une version dans ce sens.

 

[sylvanu]

Une V6 avec un choix Oui/Non pour la suppression de "doublons horizontaux", c'est à dire la suppression des combinaisons où un même nombre apparait plusieurs fois.

retrouver les 5 nombres de départ possibles

Ne voulait pas dire qu'un même nombre ne pouvait pas apparaitre plusieurs fois.

 

[darkfuryo]

Bonjour Sylvanu,

Effectivement, vous avez raison, je me suis mal exprimé en posant le problème de départ.
Je n'avais pas anticipé une réponse aussi précise et rapide

Quant aux doublons effectivement, là aussi je me suis peut-être mal exprimé, cela peut être le tirage identique de 5 nombres, ou le fait qu'il y ait deux fois le même nombre qui se répète.

Ce dernier fichier fonctionne mais comporte encore je crois 2 doublons (au sens deux nombres identiques sur une même ligne) :

 

[sylvanu]

Un peu trop rapide la V6.
Quand on supprime des lignes il ne faut pas faire de 1 à N mais de N à 1, sinon il y a des ratés.
En espérant être ok ce coup ci.

 

[darkfuryo]

Impeccable !
146 combinaisons uniques, moins les 12 doublons horizontaux, on trouve bien 134 !
Cette fois-ci, le compte est bon !!!

Un énorme MERCI Sylvanu pour ta réactivité, tu as plié ça en 2 temps 3 mouvements, c'est vraiment parfait