Autres pseudo-alea

[maart]

Bonjour,

La fonction alea pourrait-elle être remplacée par une formule de calcul.
Connaissez-vous ce calcul, je souhaiterai y intégrer 2 variables qu'Excel testerait successivement dans une colonne, jusqu'à s'approcher des valeurs de référence dans une colonne du fichier (comparaison pseudo alea/réel)?
je ne connais pas la formule donc je ne peux fournir un fichier.
Merci et bon am

MAART

 

[sylvanu]

Bonjour Maart,
Vaste sujet, d'autant que "nombre aléatoire" sur ordinateur ne veut rien dire. il existe des centaines de lois qui ont toutes leur avantages et inconvénients.
Regardez ça, c'est intéressant :

https://www.apprendre-en-ligne.net/random/alea.pdf

 

[sylvanu]

Re,
Ca m'a titillé votre demande.

Un essai perso en PJ pour le fun avec :

Function AleaPerso(x)
    QueryPerformanceCounter InitTime
    QueryPerformanceCounter EndTime
    N = (EndTime ^ 2 / 1000) - Int(EndTime ^ 2 / 1000)
    AleaPerso = N
End Function

J'utilise la partie décimale du temps système pour en calculer un nombre aléatoire.
La distribution est correcte, on est de l'ordre de 0.5% d'erreur sur la moyenne, et à 0.3% sur le coef de Kurtosis, par rapport à une distrib uniforme mathématique.
Pas pire que la fonction Alea() d' XL ( feuille Alea ) qui donne des erreurs pire.

 

[soan]

Bonjour sylvanu,

pour Yeahou, c'est quel n° ?

@Yeahou : non, surtout, ne regarde pas le fichier pdf du post #2,
ou tu risques d'avoir une attaque cardiaque !

et cesse de griffer la gentille dame blonde !
(bonjour quand même ! )

soan

 

[Dranreb]

Bonsoir.
J'ai déjà utilisé ça que j'avais fait il y a longtemps en VBA car j'avais constaté des alignements gênant avec Rnd pour des images basées sur des valeurs de pixels aléatoires :

Function Aléat(Optional ByVal G As Double = -1) As Double
   Static X As Double
   If G >= 0 Then If G > 0 Then X = G Else X = Now / 2958466
   X = (X + 1.35198775424545) ^ 7: X = X - Int(X)
   Aléat = X
   End Function

Mais je n'ai jamais fait d'étude sérieuse pour savoir si ça respectait mieux que Rnd certains critères officiels. J'avais juste vu que c'était plus satisfaisant pour mes besoins.
Exemple de fonction à distribution normale qui l'utilise :

Function AléatN(ByVal ÉTyp As Double, ParamArray Moy() As Variant) As Variant
   Dim TRés() As Double, N As Long, Np As Long, AléN1 As Double: Static AléN2 As Double, DéjàDonné As Boolean
   If UBound(Moy) <= 0 Then
      GoSub Donner: AléatN = AléN1 * ÉTyp
      If UBound(Moy) = 0 Then AléatN = Moy(0) + AléatN
   Else
      ReDim TRés(1 To UBound(Moy) + 1) As Double
      Np = 0: N = 1: Do:
         GoSub Donner: TRés(N) = Moy(Np) + AléN1 * ÉTyp: If N = UBound(TRés) Then Exit Do
         Np = N: N = N + 1: Loop
      AléatN = TRés
      End If
   Exit Function
   Dim AléU1 As Double, AléU2 As Double, S As Double
Donner: If DéjàDonné Then
      AléN1 = AléN2: DéjàDonné = False
   Else:                 AléU1 = Aléat * 2 - 1
      Do: AléU2 = AléU1: AléU1 = Aléat * 2 - 1: S = AléU1 * AléU1 + AléU2 * AléU2: Loop Until S <= 1
      S = Sqr(-2 * Log(S) / S): AléN1 = AléU1 * S: AléN2 = AléU2 * S: DéjàDonné = True: End If
   Return
   End Function

Mais je l'utilise peu, parce qu'à long terme on peut finir par obtenir très exceptionnellement des valeurs fortement éloignées de la moyenne demandée.
Pour des simulation je préfère utiliser ma distribution quasi normale :

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
Function DistrQsNMnMx(ByVal Rnd0à1 As Double, ByVal Mini As Double, ByVal Maxi As Double) As Double
   DistrQsNMnMx = DistrQsN(Rnd0à1, (Mini + Maxi) / 2, (Maxi - Mini) / 8)
   End Function

 

[maart]

Bonjour à tous,

Merci Sylvanu pour ton lient très intéressant. Je n'ai pas tout compris dans ton fichier ni comment adapter à mon fichier...tout comme l'explication de Dranreb...
Je joins un petit exemple de 20 lignes que j'ai commencé: A= réel, F = résultat "alea" à 2 variables à et b, puis comparaison (G) et somme (H1).

Si par exemple ma formule d'alea est a * b/100 alors la macro "combine" successivement chaque variable pour correspondre le plus exactement (à + ou-1) du réel. Ce sont les numéros chance de la FDJ que j'ai limités de 3 à 10.
Si ça vous inspire et possible.
Bonne journée

 

[Dranreb]

Bonjour.
Pourquoi au fond demandiez vous à pouvoir remplacer dans vos formules la fonction ALEA d'Excel ou sa dérivée ALEA.ENTRE.BORNES par autre chose ?
Non, je dis ça parce que si vous n'avez pas compris nos explications c'est peut être parce qu'elle ne répondent pas à ce que vous aviez cru demander !
Essayez de mieux préciser comment vos cellules oranges doivent dépendre du reste, ou plus exactement statistiquement tendre vers quoi, et selon quelle distribution.
Excel ne pratique que la génération de nombre aléatoire en distribution uniforme, mais il est bien sûr possible d'en déduire des nombres distribués autrement, notamment en distribution normale, c'est à dire selon un écart type autour d'une moyenne.
Dans un module standard :

Option Explicit
Function DistrN(ByVal Rnd1 As Double, ByVal Rnd2 As Double, ByVal ÉTyp As Double, ParamArray Moy() As Variant) As Variant
   Const Pi×2 = 2 * (245850922 / 78256779)
   Rnd1 = Sqr(-2 * Log(Rnd1)) * ÉTyp: Rnd2 = Rnd2 * Pi×2
   If UBound(Moy) = 1 Then
      DistrN = Array(Moy(0) + Rnd1 * Cos(Rnd2), Moy(1) + Rnd1 * Sin(Rnd2))
   Else: DistrN = Moy(0) + Rnd1 * Cos(Rnd2): End If
   End Function

Exemple d'utilisation de cette fonction dans votre classeur, en F2, à propager sur 20 lignes :

=ARRONDI(DistrN(ALEA();ALEA();2,25;$A2);0)

Ce qui pourrait d'ailleurs s'écrire sans faire appel à une fonction perso :

=ARRONDI($A2+RACINE(-2*LN(ALEA()))*2,25*COS(ALEA()*PI()*2);0)

2,25 étant, dans les 2 cas, l'écart type arbitraire que j'ai choisi.

 

[maart]

Merci pour cette formule et quelle formule !!
je vais essayer de faire une boucle de macro pour tester plusieurs écart-types (entre 1 et 3 avec 1 décimale par ex) pour approcher au mieux de mes valeurs réelles. Comment peut-on enlever les résultats négatifs?

Peut -on fixer des bornes ici comme avec alea?
Merci

Maart

 

[Dranreb]

On ne peut pas fixer de bornes avec une distribution normale, mais la génération d'un nombre s'écartant de la moyenne désirée de plus de 4 fois l'écart type est extrêmement rare.
Ma fonction perso DistrQsN du poste #5 l'élimine complètement sans guère altérer la distribution en courbe de Gauss, si vous préférez, et la DistrQsNMnMx permet de spécifier des bornes. S'il ne doit y avoir de nombre négatif, il vaut mieux s'intéresser à une autre distribution telle que la loi Log-normale, la Gamma ou celle de Weibull.

 

[maart]

Bonjour, c'est trop compliqué pour moi.

A bientôt

 

[Dranreb]

Bonjour.
J'ai quelque chose sur la loi Weibull.
(Typiquement c'est le moment de la panne d'un appareil à partir de sa mise en service, donc après une durée toujours positive)