Simulations de données aléatoires (via moyenne et écar type)

locsta29 · 8 Septembre 2016

Bonjour à tous,
Dans un premier temps je cherche a créer des simulations aléatoires de valeurs en fonction d'une moyenne et d'un écar type standard (valeurs données)
Ensuite je cherche a répéter plusieurs fois cette opération tout en gardant les données précédentes a fin de faire des moyennes.

J'ai joins un fichier excel en exemple afin de me faire mieux comprendre.

Cordialement
Marc

Dranreb · 8 Septembre 2016

Bonsoir.
Voyez si cette fonction perso peut vous être utile :

VB:

Function DistrQsN(ByVal Rnd0à1 As Double, ByVal Moyenne As Double, ByVal ÉcartType As Double) As Double
Rem. Distribution quasi normale à part que le nombre engendré ne fuira la moyenne de plus de 4 fois l'écart type
DistrQsN = (Rnd0à1 ^ 0.18148 - (1 - Rnd0à1) ^ 0.18148) * 4 * ÉcartType + Moyenne
End Function

locsta29 · 9 Septembre 2016

Bonsoir Dranreb, merci beaucoup de ta réponse, malheureusement je ne suis très alaise avec le VBA (je vais m'y mettre!!)
Idéalement je cherche a ce que le 3ème onglet (IDs simulés) soit généré a partir des données du 1er onglet sur X nombres de lignes (dates)

PS: j'ai mis des dates mais SimulationN°1 ou date pour moi n'a pas d'importance

Dranreb · 9 Septembre 2016

C'est une fonction perso à mettre dans un module standard, mais elle peut très bien être utilisée dans une formule de cellule

Dranreb · 9 Septembre 2016

locsta29 à dit:
Idéalement je cherche a ce que le 3ème onglet (IDs simulés) soit généré a partir des données du 1er onglet sur X nombres de lignes (dates)

Cette phrase n'a absolument aucun sens pour moi: dans le 1er onglet il y a une colonne A contenant ID1 à ID363, dans le 3ième 4 colonnes ID1 à ID4, je ne sais pas ce que vous voulez. Mais je vous l'ai déjà dit, si vous vous le savez vous pouvez faire des essais à l'aide de formules utilisant ma fonction perso, tel que en 'IDs simulés'!B2 :

Code:

=DistrQsN(ALEA();'Moyenne et Ecar Type IDs'!$B$2;'Moyenne et Ecar Type IDs'!$C$2)

Si les deux tableaux étaient dans le même sens avec des dates en colonnes çà permettrait une Macro simple qui ferait

VB:

With Feuil3.[B2:EU139]
.FormulaR1C1 = "=DistrQsN(RAND(),'Moyenne et Ecar Type IDs'!RC2,'Moyenne et Ecar Type IDs'!RC3)"
  .Value = .Value: End With

locsta29 · 9 Septembre 2016

C'est parfait merci
"dans le 3ième 4 colonnes ID1 à ID4, je ne sais pas ce que vous voulez"
Je n'ai mis que les 4 premiers pour faire plus simple mais évident il s'agit de faire la meme chose pour les 363 IDs

pierrejean · 9 Septembre 2016

Bonjour Locsta29

Salut Dranreb

Egalement avec fonction personnalisée

Dranreb · 9 Septembre 2016

Bonjour pierrejean
C'est une distribution uniforme, ça, non normale.
Un générateur en vraie distribution normale serait plutôt comme ça :

VB:

Function AleaNorm(ByVal Moy As Double, ByVal Ecart As Double) As Double
Dim Rnd1 As Double, Rnd2 As Double
Const Pi = 245850922 / 78256779
Rnd1 = Sqr(-2 * Log(Rnd)) * Ecart: Rnd2 = Rnd * Pi * 2
AleaNorm = Moy + Rnd1 * Cos(Rnd2)
End Function

Mais en pratique ma distribution quasi normale évite le risque, de plus en plus infime certes mais non nul, que la valeur engendrée s'évade à une distance énorme de la moyenne qui fausse tout dans un ensemble généralement limité.

locsta29 · 9 Septembre 2016

Je me rends compte que j'ai oublié un détail plutôt important, aucune des valeurs simulées ne doit pouvoir être inférieur à 0 et idéalement les valeurs devraient être arrondi à 0,25 toujours en respectant la moyenne et l'écar type standard.

Dranreb · 9 Septembre 2016

Dans ce cas mon réflexe est de me demander si ce n'est pas plutôt le logarithme de la valeur simulée qui doit suivre une distribution normale…

locsta29 · 9 Septembre 2016

Les moyennes et les ecar types des différents ID sont issus d'un historique de données auquel j'applique un barème multiplicateur (voir fichier joint)

Je souhaite extrapoler ses résultats (en faisant des simulations) en fonction de l'historique de données que j'ai déjà
J'ai utilisé les moyennes et ecar type des "Valeur Total" dans un souci de simplicité mais peut etre qu'il serait préférable d’utiliser les moyennes des "Nb de A, Nb de B etc..." (voir fichier)

Dranreb · 9 Septembre 2016

Hé SOMMEPROD est pas seulement fait pour être employé en dehors de sa véritable vocation !

Code:

=SOMMEPROD($B3:$J3;Paramètres!$B$5:$J$5)

locsta29 · 9 Septembre 2016

Bien plus simple effectivement
Merci

pierrejean · 9 Septembre 2016

Bon !
Je suis maintenant trop vieux pour l’étude des statistiques
Je sors sur la pointe des pieds.....

Simulations de données aléatoires (via moyenne et écar type)

locsta29

XLDnaute Nouveau

Pièces jointes

Dranreb

XLDnaute Barbatruc

locsta29

XLDnaute Nouveau

Pièces jointes

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

locsta29

XLDnaute Nouveau

pierrejean

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

locsta29

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

locsta29

XLDnaute Nouveau

Pièces jointes

Dranreb

XLDnaute Barbatruc

locsta29

XLDnaute Nouveau

pierrejean

XLDnaute Barbatruc

Discussions similaires