XL 2019 Match somme et Ecart Type

darkfuryo

XLDnaute Nouveau
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.
captxl.jpg


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 !
 

Pièces jointes

  • 5 Nb à trouverv2.xlsx
    10.2 KB · Affichages: 21
Dernière édition:

sylvanu

XLDnaute Barbatruc
Supporter XLD
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 )
 

Pièces jointes

  • 5 Nb à trouverv2 (2).xlsm
    16.8 KB · Affichages: 5

darkfuryo

XLDnaute Nouveau
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 :

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.
doublons.jpg


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

Pièces jointes

  • doublons.jpg
    doublons.jpg
    220.3 KB · Affichages: 22
Dernière édition:

sylvanu

XLDnaute Barbatruc
Supporter XLD
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.;)
 

Pièces jointes

  • 5 Nb à trouverv2 (6).xlsm
    31.8 KB · Affichages: 6

darkfuryo

XLDnaute Nouveau
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) :
doublons02.jpg
 

Pièces jointes

  • doublons02.jpg
    doublons02.jpg
    213.2 KB · Affichages: 10

Discussions similaires

Réponses
2
Affichages
311

Statistiques des forums

Discussions
311 730
Messages
2 081 991
Membres
101 856
dernier inscrit
Marina40