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

[locsta29]

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]

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

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]

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]

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]

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 :

=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

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

 

[locsta29]

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]

Bonjour Locsta29

Salut Dranreb

Egalement avec fonction personnalisée

 

[Dranreb]

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

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]

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]

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]

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]

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

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

 

[locsta29]

Bien plus simple effectivement
Merci

 

[pierrejean]

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