Soit Pierre, Paul et Jacques les valeurs des cellules A1:A3.
Voici la formule qui permet de tirer au sort un candidat :
=INDEX(A1:A3;ENT(ALEA()*3)+1)
En figeant la plage de cellules qui contient la liste des prénoms, nous obtenons la formule qui permet de générer une liste aléatoire avec répétitions de ces 3 candidats :
=INDEX(A$1:A$3;ENT(ALEA()*3)+1)
puis recopier la cellule.
Voici un exemple en image sur une population un peu plus importante, dans lequel il s’agit de tirer au sort 3 personnes parmi 10.
Dans Excel, au moyen des fonctions INDEX et ALEA.ENTRE.BORNES, tirer au sort 3 personnes parmi 12.
En revanche, cela ne résout pas le problème des doublons ; en effet, quelques recalculs au moyen de F9 plus tard, on obtient ceci…
Dans Excel, au moyen des fonctions INDEX et ALEA.ENTRE.BORNES, tirer au sort 3 personnes parmi 12. Mais des doublons peuvent apparaître.
Mais votre formule n’est pas bonne.
La bonne formule est:
=INDEX(A$1:A$3;ENT(ALEA()*3))
Je peux me tromper, mais après réflexion, je maintiens la formule telle quelle (avec le « +1 »), car il ne faut pas oublier que la fonction ALEA génère un nombre réel aléatoire SUPÉRIEUR OU ÉGAL À 0 ET INFÉRIEUR à 1.
Voyons ce que donnerait l’utilisation de la formule
=INDEX(A$1:A$3;ENT(ALEA()*3))
que tu proposes.
Premier problème : Imaginons que notre ALEA() génère 0, car il est possible qu’elle le fasse ; dans ce cas, l’expression ENT(ALEA()*3) (donc sans le « +1 ») nous renvoie un 0, ce qui ne convient pas comme second argument à la fonction INDEX (cet argument doit dans notre cas être 1, ou 2, ou 3).
Deuxième problème : L’expression ENT(ALEA()*3) ne peut JAMAIS renvoyer la valeur 3 (ce qui fait que la cellule A3 ne serait jamais choisie), car ALEA() peut « au maximum » générer 0,9999999…, et par conséquent ENT(ALEA()*3) ne renverra « au mieux » que 2.
Conclusion : L’expression ENT(ALEA()*3) génère les valeurs 0 ou 1 ou 2 – c’est pourquoi il faut ajouter le « +1 » dans la formule.
Alors, convaincu ?
=INDEX(A$1:A$3;ALEA.ENTRE.BORNES(1;3))
Trop simple ?
😉
En fait, je me suis trompé.
Par contre si les cellules A1 à A3 contiennent des formules mathématiques (du genre 30*ALEA()), 5*ALEA() et =ALEA()*(100-50)+50), et que je veux qu’à chaque tirage la fonction ALEA() soit réinitialisée, je dois faire comment?
(Pour résumer je veux faire plusieurs tirages successifs dans un ensemble fini d’intervalles).
Merci pour ta réponse.
Marc.
Si tu souhaites « forcer » le recalcul des formules pour obtenir de nouvelles valeurs aléatoires comme si tu faisais un nouveau tirage, alors il faut faire F9, et toutes les formules de ta feuille seront recalculées.
Si au contraire (on en a aussi souvent besoin), tu souhaites à un moment donné cesser de générer des valeurs aléatoires et conserver les dernières valeurs générées, alors, la démarche à suivre est celle-ci :
– sélectionner les cellules contenant des formules avec ALEA ou ALEA.ENTRE.BORNES
– faire « copier »
– en maintenant la même sélection, faire « collage spécial », puis choisir « valeurs » (dans les versions récentes d’Excel, cela correspond au petit bouton « 123 » dans la partie « Coller » du menu contextuel).
Les formules seront alors remplacées par les dernières valeurs qu’elles avaient générées. Attention, tes formules n’existeront plus.
Je souhaiterais réagir à cet article et en profiter pour poser ma question.
J’ai 40 personnes au total, et chaque semaine 6 noms doivent sortir aléatoirement. Comment faire en sorte qu’une personne déjà tirée ne soit pas de suite tirée au sort?
Merci d’avance
c’est une vraie bonne question, à laquelle pour l’instant je n’ai pas de réponse, pas d’un point de vue « formule de calcul » en tout cas. Je ne suis pour tout dire pas sûr qu’il soit possible d’y répondre de manière satisfaisante uniquement par une formule de calcul, sans recourir au VBA. Mais il suffit souvent de penser que quelque chose est impossible, pour s’apercevoir que quelqu’un d’autre l’a fait ;-). Donc, la question reste pour le moment ouverte.
Si tu trouves la réponse sur un autre site, ou encore mieux par toi-même, fais-nous savoir !
J’ai un premier soucis, c’est après avoir rentré cette formule :
=INDEX(A$1:A$18;RANDBETWEEN(1;18))
J’ai un message d’erreur qui me dit qu’il y a u, problème avec la formule et qu’il ne la reconnait pas comme telle.
Mon deuxième soucis c’est le même que Goulard Michael mais je peux faire avec en supprimant de ma liste les noms déjà tirés.
Cordialement
ta formule paraît juste, à condition d’utiliser un ordinateur avec Excel en anglais. En revanche, si ta version d’Excel est française, ta formule doit être :
=INDEX(A$1:A$18;ALEA.ENTRE.BORNES(1;18))
ce qui est rigoureusement la même chose, mais dans la langue de Molière.
Excel n’utilise pas pour ses fonctions les mêmes appellations dans toutes les langues, mais de nombreux sites donnent les équivalences.
Je viens de mettre à jour l’article, avec des images qui prouvent que ta logique est bonne.