Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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.


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

sylvanu

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

Pièces jointes

  • 5 Nb à trouverv2 (4).xlsm
    28.3 KB · Affichages: 7
Dernière édition:

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.


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
    220.3 KB · Affichages: 23
Dernière édition:

sylvanu

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

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) :
 

Pièces jointes

  • doublons02.jpg
    213.2 KB · Affichages: 11

darkfuryo

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

Discussions similaires

  • Question
Microsoft 365 Tableau
Réponses
24
Affichages
894
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…