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.

 

[Grand Chaman Excel]

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

Bonsoir (encore le soir ici) Peppone13,

Voici ma proposition (avec des colonnes intermédiaires). Note que la formule E est différente que en B, C, D. (les couleurs c'est pour ça)

 

[ROGER2327]

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

Bonjour à tous

Solution avec une fonction personnalisée.​

ROGER2327
#5114

Mercredi 25 Pédale 138 (Saint Peligraf Poligrafovitch, chien, SQ)
29 Ventôse An CCXIX
2011-W11-6T02:51:01Z

 

[hoerwind]

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

Bonjour Peppone13, salut Grand Chaman Excel et Roger,

Peppone, j'espère que tu vas mieux que cette nuit.
Comme consolation, dis-toi que tes proches doivent être heureux, tu as moins de mordant !

En pièce jointe deux solutions par formules pour le tableau supérieur.

La première :
La fonction ALEA.ENTRE.BORNES() est volatile
Les résultats se modifient à chaque validation d'une cellule dans le fichier
Pour tester : touche clavier F9

La deuxième :
Activer le calcul itératif sous les options Excel
Si A1 n'est pas vide (un espace suffit) les résultats sont figés
Si A1 est vide les résultats se modifient

Quant au deuxième petit tableau, c'est assez simple.
B12 : =ALEA.ENTRE.BORNES(6;14)
B13 : =B15-B12
Le résultat en B12 peut également être figé par la même méthode que celle employée sous le premier tableau.

Bon weekend.

 

[david84]

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

Bonjour à tous,
En reprenant l'idée de base d'Hoerwind, ci-joint proposition.
A+

 

[ROGER2327]

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

Re-Bonjour à tous

Correction d'une faute grossière dans le code de la fonction.

Function tata(x, n%, mi%, ma%)
Dim i, sDat(), s%, tmp1%, tmp2%
'-----------------------------------------------------
  Application.Volatile 'Supprimer cette ligne si on ne
'veut pas de recalcul automatique.
'-----------------------------------------------------
  ReDim sDat(0, 0 To n - 1)
  If x >= mi * n And ma * n >= x And n Then
    Randomize
    For i = n - 1 To 1 Step -1
      tmp1 = WorksheetFunction.Min(ma, x - s - mi * i)
      tmp2 = WorksheetFunction.Max(mi, x - s - ma * i)
      sDat(0, i) = tmp2 + Int((tmp1 - tmp2 + 1) * Rnd)
      s = s + sDat(0, i)
    Next
    sDat(0, 0) = x - s
  Else
    For i = 0 To n - 1
      sDat(0, i) = "-"
    Next
  End If
  tata = sDat
End Function

​

ROGER2327
#5115

Mercredi 25 Pédale 138 (Saint Peligraf Poligrafovitch, chien, SQ)
29 Ventôse An CCXIX
2011-W11-6T10:59:50Z

 

[Peppone13]

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

Bonjour le Forum, Roger2327, Grand Chaman Excel, hoerwind et David84,
Dans le cadre de mon insomnie (voilà ce que c'est que d'avoir mangé trop de bonbons !!!! après, on souffre des dents !!!!), j'ai eu l'illumination au moment même où j'éteignais l'ordi cette nuit !!!!
Mais il m'a fallu attendre que ma femme parte au marché ce matin (tout du moins, c'est ce que m'a dit son amant !!!!) pour pouvoir me re-connecter et vous rejoindre (et accessoirement la tromper une nouvelle fois avec EXCEL !!!!)....
J'avais donc trouvé une solution (c.f. mon fichier joint) qui ressemble à celle d'hoerwind et un peu à celle de Grand Chaman d'Excel.
Un grand merci également à Roger2327 et David84 pour leurs propositions que je vais regarder avec attention car elles proposent de nouvelles approches. Je regarderai laquelle est la plus simple à intégrer à mon classeur, car finalement, de nouvelles idées d'évolution de mon fichier me viennent au fur et à mesure !!! (j'espère ne pas être en train de construire une usine à gaz trop ambitieuse !!!)
Quoiqu'il en soit, je pense venir vous faire un petit (gros ?) coucou très prochainement et très réguliérement et mettre à contribution vos neurones et vos capacité !!!
bonne continuation de WE à tous.....
bien EXCELLEMENT,
Peppone31.

 

[Peppone13]

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

Bonsoir.... le Forum (et ses noctambules !!!),

j'ai donc continué à travailler sur mon fichier et me suis trouvé confronté au petit problème soulevé par hoerwind concernant la "volatilité" de la fonction ALEA. Je ne savais pas ce que celà voulait dire...... mais maintenant..... je sais !!!
Pour la contrer, j'ai essayé de suivre la préconisation du calcul itératif (alors là, si c'est pas du chinois !!!), mais sans grand succès.
Pour les courageux, j'ai joint mon classeur avec l'avancée de mes "travaux". Dans l'onglet, j'ai essayé de bien préciser ce qui me posait problème....
Merci par avance pour l'intérêt que vous porterez à mes requêtes et votre aide,
bien EXCELLEMENT,
bonne fin de nuit,
Peppone31

P.S. : et bravo aux Toulousains pour leur match avec l'équipe de France ce soir contre le Pays de Galles ;o)))

 

[hoerwind]

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

Bonjour,

On dit d'une fonction qu'elle est "volatile" lorsqu'elle se met à jour à chaque calcul du fichier.
J'en connais quatre : ALEA(), ALEA.ENTRE.BORNES(), AUJOURDHUI() et MAINTENANT().
Il y en a peut-être d'autres.

Je ne comprends pas pourquoi il faut définir quatre valeurs (Zones 1 à 4) pour en calculer la MEDIANE(), alors qu'une seule formule peut renvoyer ce résultat.
Exemple : la médiane de quatre valeurs aléatoires pour un total de 20 peut varier de 1 à 6 et sa valeur est renvoyée par la formule :
=ALEA.ENTRE.BORNES(2;12)/2
Comme les valeurs de la colonne F varient, il peut en être tenu compte sous la formule :
G6 : =ALEA.ENTRE.BORNES(2;ENT((F6-1)/3)*2)/2
formule à copier vers le bas.
Si tu désires plus d'explications quant au calcul de la médiane de quatre valeurs aléatoires dont le total est prédéfini, n'hésites pas.

Pour établir un calcul itératif (=Itérations sous XL2003), c'est à dire une formule qui fait appel à son propre résultat (aussi appelé référence circulaire), il faut faire usage des formules du deuxième tableau de ma pièce jointe précédente (message #4) ou à celle de david84 (message #5) qui est plus complexe mais peut être copiée vers le bas et vers la droite, mais qui doit être encore adaptée à un calcul itératif.
Ce n'est pas le cas sous ta dernière pièce jointe.
Fais aussi une recherche sous les anciens messages avec "date figée" comme mot clef, tu trouveras plusieurs exemples de calcul iératif.

Je puis te faire un exemple sur ton dernier fichier, mais tu devrais d'abord préciser si tu désires rendre le calcul itératif inopérant pour tout le tableau en bloc, ou bien ligne par ligne.

Pour les autres questions, on verra après avoir résolu celles-ci.
C'est petit à petit que l'oiseau fait son nid.

 

[Peppone13]

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

Bonjour le Forum, Bonjour hoerwind,

petite connection rapide pour prendre connaissance des éventuels réponses sur ce fil de discussion.... et ravi de t'y retrouver

J'aurai plus de temps pour bien lire ta réponse demain (au bureau, on est toujours plus disponible qu'à la maison, non ???!!!), et y répondre de la façon la plus précise que possible.
Merci pour ton explication de ce qu'est une fonction volatile. Donc, rien à voir avec le journal satyrique sortant le mercredi !!!!

Egalement un grand merci pour le temps passé sur ma demande d'aide,
te souhaitant ainsi qu'au Forum une excellente fin de week-end,
à demain,
Peppone31.

 

[Peppone13]

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

Bonjour Le Forum, hoerwind,

j'ai donc regardé plus précisement ton message et ai recherché "date figée" comme tu le préconisais.
Pour répondre à ta suggestion concernant mes "Zones de 1 à 4", c'est parce qu'il me semblait que ces éléments me seraient utiles par la suite dans l'utilisation de mes fichiers (notament pour établir les feuilles de matches, c.f. la partie droite de mes tableaux dans l'onglet "feuille de matches). Peut-être qu'effectivement je me suis posé des problèmes tout seul !!!
J'ai essayé de comprendre ta formule sur le "calcul de la médiane de 4 valeurs aléatoires dont le total est prédéfini), mais ça me passe un peu loin !!!! Je ne vais pas t'embêter avec ça et vais rechercher un peu avec google !!!!

J'ai donc "bloqué" mon fichier avec le calcul itératif comme tu le préconisais (il m'a fallu pas mal tatonné d'ailleurs, mais je crois que "ça marche").
Par contre, ça me pose un autre petit problème que j'explique dans le fichier joint (au niveau de la mise à jour de certaines valeurs). J'ai essayé de formuler précisement une petite (grosse ?) amélioration que je souhaitais mettre en place dans mes tableaux. Je pense que ça passe par l'imbrication des formules "INDEX" et "DECALER" ???. Mais avec l'itération qui se rajoute à tout ça....
([mode_sanglots ON]) Boûhhhh !!!! ([mode_sanglots ON])

Au plaisir de te(vous) lire,

bonne semaine, bien EXCELLEMENT,
Peppone31

 

[hoerwind]

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

Bonjour,

Je constate que tu n'as pas modifié les formules sous 'BDD Joueurs'!B:E
Le calcul itératif ne fonctionne donc pas.
De plus, tu n'a pas répondu à ma question :
tu devrais d'abord préciser si tu désires rendre le calcul itératif inopérant pour tout le tableau en bloc, ou bien ligne par ligne
J'attends ta réponse pour corriger le fichier en conséquence.

En Equipe!B52 il faut faire référence à A52 et non à A53
Idem pour B36
Erreur de jeunesse ou faute d'inattention ?

Le transfert automatique d'un nouveau nom de la feuille 'BDD Joueurs'!A:A vers Equipe!A:A peut se faire par formule matricielle (difficile à maitriser quand on n'y est pas habitué) ou par formule (normale) mais par le biais d'une colonne intermédiaire (facile à comprendre).
Que choisis-tu ?

Si je comprends ton souhait, ce n'est pas :
je voudrais que "hoerwind" apparaisse automatiquement dans la première cellule non vide de la colonne A52:A61 dans l'onglet "Equipe"
mais bien :
je voudrais que "hoerwind" apparaisse automatiquement en Equipe!A:A dans le petit tableau correspond à l'équipe renseignée en 'BDD Joueurs'!H:H, en remplacement du premier nom de moins de trois caractères y figurant.
D'après ce que tu écris, un deuxième nom devrait venir écraser un premier nom déjà transféré.

Pour ce qui est du calcul de la médiane, si tu le désires je te mets une petite explication, le temps de me rappeler ce que j'ai appris à l'école maternelle !

A te lire.

 

[Peppone13]

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

Re Le Forum, hoerwind,

et tout d'abord, merci pour tout le temps que tu consacres à mes problèmes !!!

Cette fin d'après-midi, je n'ai pas trop le temps, mais demain matin, je me pencherai plus concrètement sur le dossier.

J'en profiterai donc :
- pour modifier les formules ('BDD Joueurs'!B:E) et comprendre un feu mieux le fonctionnement du calcul itératif. Je pensais que c'était le cas (car les cellules ne se remettaient à jour qu'à l'utilisation de touche "F9"), mais visiblement non !!!!.
-modifier les formules en cellules B36, B52, mais également en B68 et B84 ! Et que celui qui n'a jamais fauté en faisant des copiers-coller me jette la 1° pierre !!!

Je peux prendre tout de même 2 minutes pour répondre à tes autres questions (c'est la moindre des choses) :
- il s'agit d'une faute de jeunesse (de moins en moins !!!) ET d'inattention (de plus en plus !!!)....
- je souhaite effectivement rendre le calcul itératif inopérant pour tout le tableau (en fait, il y en a 2 sur cette page). Je n'avais pas compris ce que tu entendais par cette question.
- pas de problème pour le transfert automatique d'un nouveau nom de joueur par formule. Dis-moi où et quelle colonne rajouter (car, comme tu t'en doutes, je ne suis pas habitué aux formules matricielles... n'ayons pas d'ambition trop démesurée !!!)
- tu as parfaitement résumé mon souhait suivant concernant les nouveaux noms des joueurs et leur distribution en venant écraser les noms de moins de 3 caractères. ça doit être une méga formule, ça, non ????!!!....

Je suis admiratif de voir que tu conserves depuis l'école maternelle tes cours sur la médiane. D'ailleurs, je suis un peu honteux de t'avouer qu'à la maternelle, seuls les dessins et les goûters me motivaient !!!

Encore merci, et normalement à demain matin,
Peppone31.

 

[Peppone13]

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

.... j'ai juste oublié de préciser que le petit topo (on va perler de "pense-intelligent" !!!) m'interresse, si tu remets la main dessus ;o)))

 

[hoerwind]

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

Bonjour,

J'ai un problème avec ton dernier fichier joint, que je ne parviens pas à résoudre :

Sous XL2003, en modifiant une formule je reçois le message : "Excel a cessé de fonctionner ... "
J'ai bien vu que tu as des plages nommées (menu Insertion - Noms - Définir) qui pointent vers un autre fichier non joint, mais je ne trouve pas où il est fait référence à ces plages nommées.
En désactivant les macros, le problème subsiste.
Je ne trouve pas de fonctions propres à XL2007 qui pourraient être à l'origine du problème.
Si tu as une idée, elle serait la bienvenue, car je suis plus habitué à XL2003 qu'à XL2007, donc plus rapide pour te proposer des solutions.

Sous Excel2007, pas de problème

En toute amitié : ce n'est pas très gentil de joindre un fichier avec un calcul sur ordre.
Heureusement que je suis habitué à ce genre de plaisanterie, car écrire des formules qui ne renvoient pas de résultat pourrait se faire arracher les cheveux à plus d'un !

En attendant, je continue sur XL2007