Somme fixe de 3 cases, pour une répartition aléatoire

[djyo]

Bonjour,

Je suis pas expert en Excell mais je me débrouille assez. Je code quelques Macro depuis un peu, ayant de bonnes notions en programmation mais sur ce coup ci je cale avec Excell.

L'idée c'est que j'ai 3 cases Adjacentes sur une ligne, et ceci multiplié sur une centaine de ligne.
Dans chaque case il peut y avoir la valeur 0 ou 1. Et la somme des 3 cases doit être égale à un nombre donné NB (0,1, 2 ou 3 c'est variable, mais disons 1 pour l'exemple).
La répartition des 1 et des 0 doit être donc aléatoires.

Mon souci principal c'est que je sais pas bien comment faire pour que les cases s'organisent entre elles pour se répartir aléatoirement les 1 et 0. Il faudrait en plus que tout ça se recharge en appuyant sur F9.

Merci d'avance pour vos réponses

 

[tototiti2008]

Re : Somme de Case Fixe pour Cases aléatoires

Bonjour djyo,

Bienvenue sur XLD,

Peux-tu nous faire une bout de fichier exemple avec les résultats attendus ?

 

[djyo]

Re : Somme de Case Fixe pour Cases aléatoires

Voilà c'est fait. Je l'ai ajouté au sujet.

 

[tototiti2008]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Re,

Peut-être :

Dans un Module standard, colle la fonction là

Function SommeRand(Nb As Long, Total As Long) As Byte()
Dim Tot As Long, Res() As Byte
    Application.Volatile
    Randomize
    ReDim Res(1 To Nb)
    Tot = 0
    For i = 1 To Nb
        If Tot >= Total Then
            Res(i) = 0
        ElseIf Nb - i < Total - Tot Then
            Res(i) = 1
        Else
            Res(i) = Int(Rnd * 2)
        End If
        Tot = Tot + Res(i)
    Next i
    SommeRand = Res
End Function

Dans ta feuille de calcul, sélectionne de C5 à E5

mets la formule

=sommerand(3;2)

(Le 3 pour le nombre de valeurs à renvoyer, le 2 pour la valeur à atteindre au total)

et valide en matriciel (Ctrl+Maj+Entrée)

 

[djyo]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Waaaaouuuwww
C'est juste génial. Il me reste plus qu'à adapter le macro à ce que je veux faire mais là je devrait plus avoir de problème.

Merci encore énormément!

J'ai juste une petite dernière question:
Une fois que j'ai fait subir des opération au tableau, j'obtiens un résultat final dans une case qui varie (en actualisant avec F9) mais qui doit normalement suivre approximativement une loi normale
Y-a-t-il un moyen pour qu'Excel fasse pour moi ce qui serait un peu fastidieux quand même: appuyer sur F9 une centaine de fois, écrire cette valeur dans un tableau pour pouvoir faire un graphique (aucun soucis pour le graphique).

 

[djyo]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Euh je viens de me rendre compte qu'en fait ton code ne rendait pas équiprobable les possibilités que le 1 soit dans une case ou l'autre.
Plus on s'éloigne de la première case et plus la probabilité se rétrécit (tend vers 1 ou 0).
Donc voilà si quelqu'un a une autre idée...

 

[djyo]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Bon j'ai finalement trouvé, mais le code est un peu épaissi avec 3 Boucles For et un Loop. Si quelqu'un a une idée pour améliorer etc...

' Fonction attribuant un nombre donné de 1 (NbPos) sur un Nb total de cases, leur position étant l'aléatoire. Le reste devient 0
Function AleaPos(NbTot, NbPos)
Application.Volatile
Randomize

Dim Poss(), PosConnue(), Final() As Byte

' Création du tableau de variable poss (position possible dans le tableau) allant jusqu'au NbTot, met des 0 dans le Final
    ReDim Poss(1 To NbTot)
    ReDim Final(1 To NbTot)
    For i = 1 To NbTot
        Poss(i) = i
        Final(i) = 0
    Next i

' Création du tableau de variable aléatoire allant jusqu'au NbPos
    ReDim PosConnue(1 To NbPos)
    For i = 1 To NbPos
        Do
            PosConnue(i) = Int(Rnd * NbTot) + 1
        ' Vérifie que la position n'a pas déjà été attribuée à un autre 1
        Loop Until Poss(PosConnue(i)) > 0
        ' Supprime la possibilité qu'un autre 1 puisse arriver à la même place
        Poss(PosConnue(i)) = 0
    Next i

'On a plus qu'à mettre les 1 dans le tableau
    For i = 1 To NbPos
            Final(PosConnue(i)) = 1
    Next i
    
AleaPos = Final()


End Function

 

[tototiti2008]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Bonjour djyo,

Pour que je meure moins bête, peux-tu me dire comment tu as déterminé que les possibilités n'étaient pas équiprobables ?

Merci

Edit : Non pas que je doute du fait que ce soit le cas, mais c'est pour savoir

 

[tototiti2008]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Re,

Une version qui a l'air mieux du côté de l'équiprobabilité

Function SommeRand(Nb As Long, Total As Long) As Byte()
Dim Tot As Long, Res() As Byte, Num As Long, i As Long
    Application.Volatile
    Randomize
    ReDim Res(1 To Nb)
    For i = 1 To Nb
        Res(i) = 0
    Next i
    Tot = 0
    Do
        Num = Int(Rnd * Nb) + 1
        If Res(Num) <> 1 Then
            Res(Num) = 1
            Tot = Tot + 1
        End If
    Loop Until Tot = Total
    SommeRand = Res
End Function

 

[djyo]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

En fait j'ai appliqué la formule sur 60 lignes et pour 5 cases et à chaque fois j'avais les fréquences des 1 par colonnes. (somme divisé par nb lignes)
Par exemple si je spécifiais 1 une fois à chaque ligne. Pour la première case la probabilité était de 1/2, mais pour la seconde ça diminuait à 1/4, puis 1/8 etc (cf le fichier joint).

Et puis je me suis rendu compte que dans le code on déterminait si le 1 était aléatoire en fonction du total des cases qui le précédait. Donc plus on s'éloignait de la première case, plus la probabilité de rendre 0 était grande. D'ailleurs la probabilité que la dernière case soit remplie était de (1/2)puissance 5 (logique)

Et du coup j'ai fait un autre algorithme sur la position des 1 aléatoires mais c'est un peu plus lourd.

 

[djyo]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Ah right, la dernière marche
(j'ai posté avant d'avoir vu ton message)

 

[tototiti2008]

Re : Somme fixe de 3 cases, pour une répartition aléatoire

Re,

OK, merci de ta réponse