Microsoft 365 Nombre aléatoire avec loi de Benford

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #2

Bonjour Jcavoinet,
Peut être une piste :

Add-in pour Loi de Benford

Le 20 février dernier, je vous ai présenté la Loi de Benford (Article Loi de Benford). Aujourd’hui, je vous propose de partager avec vous un Add-in que j’ai créé afin de définir rapidement si une liste répond à la Loi de Benford. Voici comment fonctionne la macro et le résultat qu’elle fournit...

cooking-excel.com

Data analyse : comment l’étonnante loi de Benford permet de traquer la fraude en entreprise ? – Sociétés Modernes

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #3

Re,
Just for the fun ... en PJ un essai avec la fonction perso LoiBenford(x)
La simulation montre une assez bonne corrélation entre VBA et loi.

VB:

Public tablo(1000), Flag As Boolean
Function LoiBenford(x)
    If Flag = False Then ListeBenford       ' Construction table
    LoiBenford = tablo(1 + Int(999 * Rnd))  ' Renvoi valeur
End Function
Sub ListeBenford()
' Nb   %     Qté pour 1000
'1   30,1%   301
'2   17,6%   176
'3   12,5%   125
'4   9,7%    97
'5   7,9%    79
'6   6,7%    67
'7   5,8%    58
'8   5,1%    51
'9   4,6%    46
Dim N As Integer, Limite()
Limite = Array(1, 301, 477, 602, 699, 778, 845, 903, 954, 1000)
N = 0
For i = 1 To 1000
    tablo(i) = N
    N = N + 1
    If N = 10 Then N = 0
Next i
For i = 0 To 8
    For j = Limite(i) To Limite(i + 1)
        tablo(j) = (10 * (i + 1)) + tablo(j)
    Next j
Next i
Flag = True
End Sub

 

Pièces jointes

• Benford.xlsm

  29.3 KB · Affichages: 45

Vote positif 0 Vote négatif

J

jcavoinet

XLDnaute Nouveau

• 19 Novembre 2020

• #4

Bonjour Sylvanu, merci beaucoup pour votre réponse. Il me semble que cela peut générer une vérification des suites de nombres, mais pas créer la suite en elle même. Il faudrait pour cela renverser le code de ce module pour générer des listes Benford. Je peux me tromper, je n'ai pas les connaissances dont vous faites preuve sur ce sujet. Ma motivation est bien ici de générer aléatoirement un nombre avec l'ADN Benford.

 

Vote positif 0 Vote négatif

J

jcavoinet

XLDnaute Nouveau

• 19 Novembre 2020

• #5

Vous avez envoyé votre second post avant ma première réponse, je viens de télécharger le fichier et je regarde tout de suite, merci beaucoup de votre implication et de votre réactivité.

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #6

Si vous regardez mon fichier, la colonne A Contient des nombres de 1 à 99 qui respectent la loi de Benford.

Le reste n'est que fioriture, juste pour vérifier que cette série de nombre respecte bien cette loi.
Le minimum dans ce genre de cas est de vérifier que le résultat est bien le résultat attendu.

Donc si vous voulez un nombre qui suit cette loi, dans une cellule vous faites =LoiBenford(0), comme c'est fait dans la colonne A.

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #7

Oups ! La V1 est un petit peu bugguée, je reviens avec une version plus robuste.

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #8

Voilà la V2 ... sans nombre >99.

 

Pièces jointes

• Benford2.xlsm

  30.2 KB · Affichages: 18

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #9

Comme vous avez pu le constater un tirage dynamique peut être très long à l'usage.
La V3 est plus rapide, elle est basée sur une suite de 10000 nombres qui respectent la loi.
Pour faire un tirage il suffit de rentrer la formule : =INDEX(A:A;1+ENT(9999*ALEA()))

 

Pièces jointes

• Benford3.xlsm

  86 KB · Affichages: 20

Vote positif 0 Vote négatif

J

jcavoinet

XLDnaute Nouveau

• 19 Novembre 2020

• #10

Merci encore beaucoup, je commence à comprendre la logique de votre méthode, que je trouve brillante.

 

Vote positif 0 Vote négatif

J

jcavoinet

XLDnaute Nouveau

• 19 Novembre 2020

• #11

J'essaie de m'approprier cette méthode, mais je n'ai définitivement pas le niveau pour reproduire sans copier ces formules. Je n'ai pas de formation, juste beaucoup de questions et j'aime chercher les réponses. Merci encore beaucoup pour le temps que vous passez à me répondre, je vais passer du temps à étudier vos tableaux pour comprendre. A peine je me penche sur le sujet de cette loi de Benford que ma femme me demande déjà de lui sortir les nombres pour le loto avec ces nombres .... je ne suis pas sorti du pétrin.

 

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 19 Novembre 2020

• #12

Bonsoir
Cette formule a l'air de donner un nombre aléatoire compris entre 1 et 99 avec la dite distribution.

Code:

=ENT(10^(ALEA()*2))

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #13

Bonsoir à tous,

Bonsoir @Dranreb

J'aime bien ta formule. Les nombres tirés suivent la loi de Benford. Tout comme avec ma méthode qui utilise une fonction personnalisé. Mais quelle simplicité er rapidité que l'usage de ta formule.
Il a une petite différence au niveau des tirages de chaque nombre entre 1 et 99. La formule semble privilégier les chiffres de 1 à 9. De mon côté, on ne voit pas cette prédominance des chiffres. En tout cas, les deux méthodes répondent à la question.
Le questionneur n'a émis aucun souhait sur la répartition des nombres au sein du tirage.
Le fichier comporte ta formule (onglet Dranreb) et la méthode de mapomme (assez proche de celle de sylvanu, c'est pourquoi je ne l'avais pas publiée mais la formule de @Dranreb a aiguisé ma curiosité ).

 

Pièces jointes

• jcavoinet- benford- v1.xlsm

  269.9 KB · Affichages: 62

Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc

• 19 Novembre 2020

• #14

Si j'ai bien raisonné les chiffres de 1 à 9 ne doivent pas être plus fréquents que les chiffres de dizaines des autres.

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 19 Novembre 2020

• #15

Re @Dranreb,

Dranreb à dit:

Si j'ai bien raisonné les chiffres de 1 à 9 ne doivent pas être plus fréquents que les chiffres de dizaines des autres.

Cliquez pour agrandir...

C'est bien ce qu'il me semble aussi.
Dans mon fichier, j'ai appliqué ta formule sur 10 000 cellules puis fais les stats. Me suis je trompé ? Tu peux jeter un coup d'oeil ?
Mon erreur doit être aussi grosse que moi, mais je ne la débusque pas !

 

Vote positif 0 Vote négatif