Générer des nombres aléatoires dont la somme est pré-déterminée....

[Peppone13]

Bonsoir le Forum, ou plutôt bonne nuit !!!!

J'ai regardé toutes les discussions tournant autour des nombres aléatoires, mais sans trouver ce que je recherche.
Je pense que les fonctions ALEA() ou ALEA ENTRE BORNES() devraient convenir, mais je ne trouve pas la petite astuce finale.
Comme vous le verrez dans le fichier joint, je souhaite générer 4 nombres entiers aléatoires compris entre 1 et 19, il peut y avoir des doublons. Jusque là, pas trop de problème.
Là où (pour moi) ça se complique, c'est que le total de ces 4 nombres est connu à l'avance (par exemple 20 ou 25 dans mon petit fichier).

Si quelqu'un à l'étincelle qui me manque !!!
Un grand merci par avance, et EXCELLENT week-end,
Peppone.

 

[hoerwind]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Re,

Vois la proposition sous la pièce jointe, avec explications.
Si quelque chose ne te semble pas clair, n'hésites pas.


.

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Bonjour le Forum, le Fil, hoerwind,
le printemps est bien installé sur Toulouse, et je tenais à vous en faire profiter ;o))

Concernant le problème survenu avec le fichier, je ne comprends pas ce qu'il se passe. Lorsque j'essaye de l'ouvrir de mon côté, j'ai une fenêtre "Fichier en cours d'utilisation" qui s'affiche avec un message m'indiquant que le fichier est vérouillé pour modification par "un autre utilisateur".....
Je remets donc en pièce-jointe le fichier, surtout que j'ai pu corriger comme promis
- les 4 cellules (B36, 52, 68 et 84).
- J'ai également modifié les formules en 'BDD Joueurs'!B:E.

Par contre, je ne comprends pas à quoi tu fais référence lorsque tu parles de fichier avec un calcul sur ordre.... Késako ???

Indiques-moi où j'ai écrit des "formules qui ne renvoient pas de résultat" . C'est probablement le cas, et il s'agit d'une erreur (encore le fameux coup du copier/coller ? une étourderie ? une formule effacée par inadvertance, un coup des russes ????).

Voici donc les premiers éléments, et restant à ta disposition si besoin, car tout n'est certainement pas très clair dans mes besoins exprimés !!
à te lire,
bien EXCELLEMENT,
Peppone31.

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Re hoerwind,
décidément, je cumule :
en me connectant, je n'avais vu que ton premier message, et non pas celui avec la pièce jointe !!!!
Je l'ouvre tout de suite et reviens vers toi !!!!
Tchô ;o)))

 

[hoerwind]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Re,

Sous les options Excel, même boite de dialogue que pour le calcul itératif, à gauche de celui-ci, manuel était coché (sous Excel2003 cela s'appelle calcul sur ordre !), alors que normalement cela doit être automatique.

Tu n'a pas écrit "formules qui ne renvoient pas de résultat", je disais simplement que c'est très gênant pour un néophyte de voir qu'une formule ne renvoie pas de résultat (sauf avec la touche clavier F9) et ne sait pas comment y remédier.

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

hoerwind,
après quelques minutes passées sur le fichier et tes améliorations, je reviens sur le fil pour te dire que tu as réussi à faire exactement ce que je souhaitais.
Histoire d'essayer de progresser un peu, je vais regarder plus en détail tes formules et la façon dont tu les as organisées pour résoudre ma problématique.
Je vais également insérer toutes les formules dans les tableaux et faire quelques essais en soufflerie.

Ensuite, comme j'ai plus d'idées d'amèlioration que de connaissance en EXCEL, je pense que je vais revenir très très bientôt sur le forum ;o)))

@tout',
Peppone31.

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

re,
nos messages se sont croisés !!!
C'est effectivement moi hier (ou avant-hier ?) qui avait coché "manuel" lors de mes différents essais. J'avais l'impression que j'avais ainsi trouvé la solution pour figer les formules !!!. Du coup, je ne comprenais pas pourquoi tu me parlais de cellule A1 à effacer ou remplir pour figer ou lancer les calculs ! Je ne l'ai compris que tout à l'heure, lorsque j'ai découvert une partie de tes explications que tu avais annoté sur ma feuille et à côté desquelles j'étais passé !!!
Je crois que l'on s'est pas mal courru après et force quiproquo !!!
encore merci,
et à plus ;o))))

 

[hoerwind]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Re,

Histoire d'essayer de progresser un peu, je vais regarder plus en détail tes formules

C'est le plus beau remerciement que tu puisses m'adresser, merci aussi, car j'ai le sentiment de ne pas avoir perdu mon temps en essayant de partager le petit peu de savoir que d'autres m'ont enseigné.

Je te fais encore la petite explication de la Médiane !

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Histoire d'essayer de progresser un peu, je vais regarder plus en détail tes formules[/COLOR]

Avec plaisir, car je pense que je me suis parfois compliqué la vie pour rien !!!

Ce qui est fabuleux avec EXCEL, c'est que j'ai l'impression qu'il y a certainement à chaque fois plusieurs façons d'arriver au résultat !!!

Je te fais encore la petite explication de la Médiane !

.... j'avais tellement abusé que je n'osais plus te le demander !!!!!

 

[hoerwind]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Bonsoir,

La médiane d’une série impaire de valeurs numériques est égale à la valeur centrale de cette série triée
Exemple :
Données : 8, 3, 6, 12, 1
Série triée : 1, 3, 6, 8, 12
Médiane : 6
La médiane d’une série paire de valeurs numériques est égale à la moyenne des deux valeurs centrales de cette série triée.
Exemple :
Données : 8, 3, 6, 12, 1, 7
Série triée : 1, 3, 6, 7, 8, 12
Médiane : =(6+7)/2 = 6,5

Dans le cas qui nous occupe, il s’agit d’une série de quatre (=pair) valeurs numériques, 0 exclu mais avec possibilité de doublons, et dont la somme est égale à 20.
Constats :
- la plus grande valeur ne peut être supérieure à 17 (par ex. : 1, 1, 1, 17)
- la quatrième valeur doit être égale ou supérieure à la troisième (par ex. :1, 1, 9, 9)
- la plus grande moyenne des deux valeurs centrales est obtenue si ces deux valeurs sont égales (par ex. :1, 6, 6, 7 ou 2, 6, 6, 6)
En conséquence : ALEA.ENTRE.BORNES(1;6) renverra tous les résultats possibles dans le présent cas de figure.

Sous un précédent message les bornes avaient été multipliées par 2, et le résultat divisé par 2, car il me semblait que la formule ne renvoyait que des nombres entiers.
Après test, c’est faux, donc 1 et 6 comme bornes est parfait.

Mais le total de 20 est variable, il faut donc faire varier la borne supérieure : =ENT((total-1)/3)
-1 parce qu'au moins une des valeurs sera égale ou supérieure à 1
/3 parce qu’il y a quatre valeurs dont une peut être égale à 1, la deuxième égale à la troisième et la troisième égale à la quatrième,
Pour un total de 22 la borne supérieure sera 7 (par ex. : 1, 7, 7, 7), ce que cette formule renvoie

Il y a encore moyen, dans la formule, de faire varier le nombre de valeurs, ainsi que de tenir compte du nombre de ces valeurs (pair ou impair), mais là c’est hors problème posé.

J’espère que c’est clair, ... et qu’il te reste encore quelques aspirines.

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Bonsoir le Forum, le Fil,
bonsoir hoerwind,

oui, expliqué comme celà, c'est clair et parait tellement évident ! Je ne m'étais pas posé autant de question en élaborant mon petit fichier !!!!
Un grand merci à toi et au forum pour votre aide, et à très bientôt, car méfiez-vous...... je continue mon fichier !!!
bonne fin de nuit,
bien EXCELLEMENT,
Peppone31.

 

[job75]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Bonjour le fil, le forum,

J'entre bien tardivement dans cette discussion.

Il me semble que cette solution VBA - très simple - n'a pas encore été fournie :

Private Sub CommandButton1_Click() 'Bouton Tirage
Dim cel As Range, plage As Range
Application.ScreenUpdating = False
1 [B12:B13].FormulaLocal = "=ALEA.ENTRE.BORNES(6;14)"
If [B15] <> [B12] + [B13] Then GoTo 1 Else [B12:B13] = [B12:B13].Value
For Each cel In [F4:F9]
  Set plage = cel.Offset(, -4).Resize(, 4)
2 plage.FormulaLocal = "=ALEA.ENTRE.BORNES(1;19)"
  If cel <> Application.Sum(plage) Then GoTo 2 Else plage = plage.Value
Next
End Sub

Fichier joint.

A+

 

[job75]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Re,

Un peu pour le fun, une solution sans doublon pour chaque total :

 

[job75]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Re,

Si les totaux sont déterminés par des formules avec SOMME, versions bis :

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Bonjour le Forum, bonjour Job75....
.... et bonjour hoerwind !!!!

merci pour ta solution.
je vais bien entendu regarder celà de plus près, même si je ne connais (pratiquement) rien en macros.... je pense même tenter de l'intégrer dans une autre version de mon fichier....
De toutes façons, j'ai pas mal d'amélioration à apporter à mon "projet" et pense qu'il me faudra faire appel très prochainement à vos compétances à toi, à celles d'hoerwind et celles du Forum ;o)))
Encore merci,
bonne fin de journée
&@+,
Peppone31.

 

[Peppone13]

Re : Générer des nombres aléatoires dont la somme est pré-déterminée....

Bonjour le Forum, bonjour Job75....
.... et bonjour hoerwind !!!!

Coucou..... me revoilou !!!!.... et toujours avec mon petit fichier que j'ai enfin un peu de temps pour le reprendre ..... et le compléter petit à petit !!!

Tout d'abord, mille excuses Job75, car je n'ai pas eu le courage de refaire mes tableaux en intégrant ta séduisante proposition....
Par contre, j'aurais bien besoin de tes lumières, de celles d'hoerwind et du Fil :

dans la feuille "BDD joueurs" du fichier joint, je souhaite ajouter la notion suivante, à savoir l'indisponibilités de certains joueurs, et je pense qu'il me faut passer par une "petite" macro.....
A chaque fois que j'active la micro, il faudrait que se produisent 2 choses.

1) les suspensions :

Je souhaite tout d'abord qu'à chaque match (ou à chaque fois que j'active la macro) les suspensions des joueurs soient remises à jouer. Pour cela, il suffit juste de retrancher "1" aux chiffres inscrits en colonne N6:N77 et N87:N101 (attention, impossible de nombre négatif bien sûr). Ensuite, il faudra attribuer de façon aléatoire 2 matches de suspension à 2 joueurs, et 1 match de suspension à 4 joueurs. Je ne sais pas si c'est très clair ?

2) les blessures :

de la même façon, à chaque match de championnat (ou à chaque fois que j'active la macro), je voudrais répartir 10 matches de blessures, à répartir de façon aléatoire en colonne O6:O77 et O87:O101. Là, par contre, les 10 matches seront répartis aléatoirement, à raison de 3 matches maximum sur un même joueur....
Là, pareil..... je ne sais pas si c'est très clair.

Merci par avance pour vous creuser les méninges sur mon petit fichier, et je reste à votre disposition si besoin pour tout question..... car je pense qu'il y en aura !!!!

bonne fin d'après-midi,
bien EXCEL-LEMENT,
Peppone31