Comment faire tirage aléatoire multiples avec Excel sans macro ni prise de tête
Dans ce tutoriel, je vais vous montrer comment créer un tirage au sort automatique dans Excel pour choisir un gagnant dans une liste de participants, avec une méthode simple, propre, et surtout réutilisable dans n’importe quel fichier.
Nous allons partir d’une liste classique de participants, puis nous allons demander à Excel de tirer un numéro au hasard, d’afficher automatiquement le prénom correspondant, et enfin de tirer plusieurs gagnants sans modifier l’ordre du tableau.
L’objectif est vraiment de construire un petit outil concret, que nous pouvons utiliser pour un concours, une tombola, un choix aléatoire d’élève, de client, de collaborateur ou même de tâche à traiter.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons utiliser le tableau suivant dans lequel nous retrouvons une liste de participants inscrits à un jeu concours.
Dans ce tableau, la colonne « N » contient un identifiant unique pour chaque participant.
C’est ce numéro qui va nous permettre de faire le tirage. Excel va choisir un nombre au hasard, puis nous allons utiliser ce nombre pour retrouver automatiquement le prénom placé sur la même ligne.
2. Tirer un numéro au hasard
Nous allons maintenant créer une petite zone de tirage à droite du tableau.
En cellule D6, nous saisissons « Gagnant : », et en E6, nous allons écrire notre première formule :
=ALEA.ENTRE.BORNES(1;35)
Cette formule demande à Excel de choisir un nombre entier entre 1 et 35.
Le premier argument, ici 1, correspond au plus petit numéro possible. Le deuxième argument, ici 35, correspond au plus grand numéro possible.
Comme notre tableau contient 35 participants, Excel peut donc tirer n’importe quel participant de la liste.
Mais cette formule a une petite limite.
Si nous ajoutons un 36e participant, il faudra penser à remplacer 35 par 36.
Ce n’est pas dramatique, mais cela représente une risque d’exclusion de nouveau participants en cas d’oubli.
Nous pouvons donc faire mieux en utilisant une formule dynamique.
Mais avant cela, nous allons transformer notre base en un tableau dynamique, ce qui va permettre d’obtenir une base plus solide.
Nous cliquons dans une cellule du tableau, puis nous utilisons le raccourci :
[Ctrl]+[L]
Excel ouvre alors une fenêtre pour créer un tableau. Nous vérifions que la plage est correcte, puis nous cochons l’option « Mon tableau comporte des en-têtes », étant donné que nous avons un titre en haut des colonnes.
Ensuite, nous validons.
Notre plage devient alors un tableau structuré. C’est beaucoup plus pratique, car si nous ajoutons un participant en bas de liste, Excel pourra automatiquement agrandir le tableau.
Nous pouvons ensuite cliquer dans le tableau, aller dans l’onglet Création de tableau ou Tableau, puis renommer le tableau dans la zone prévue.
Nous allons l’appeler : « Participants »
Grâce à ce nom, nous pourrons écrire des formules plus lisibles, par exemple : « Participants[Prénom] » au lieu d’utiliser une plage du type : « $B$2:$B$16 C’est plus clair, plus propre, et surtout plus facile à réutiliser.
Maintenant, revenons dans la cellule E6 pour modifier la formule en :
=ALEA.ENTRE.BORNES(1;NBVAL(Participants[Prénom]))
Ici, la fonction NBVAL compte automatiquement le nombre de prénoms présents dans la colonne « Prénom » du tableau.
Si nous ajoutons une nouvelle personne, Excel adaptera automatiquement la limite maximale du tirage.
La fonction ALEA.ENTRE.BORNES se recalcule régulièrement.
Pour relancer volontairement le tirage, nous pouvons appuyer sur [F9], ainsi Excel génère un nouveau numéro.
Petite astuce importante : tant que la formule reste active, le tirage peut changer.
Si nous voulons conserver le résultat final, nous copions la cellule H8 avec [Ctrl]+[C], puis nous faisons un collage spécial en valeurs avec [Ctrl]+[Alt]+[V], puis « Valeurs ».
3. Afficher automatiquement le prénom du gagnant
Pour l’instant, Excel nous donne seulement un numéro.
Ce n’est pas très pratique, nous voulons qu’Excel fasse tout seul le lien entre le numéro tiré et le prénom du participant.
Pour faire cela, nous pouvons utiliser plusieurs fonctions d’extraction ou de recherche, comme RECHERCHEV, RECHERCHEX, ou encore le duo INDEX + EQUIV.
Si vous suivez la chaîne depuis quelque temps, vous devez savoir que ma préférence va souvent vers INDEX + EQUIV.
Parce que cette méthode est plus souple que RECHERCHEV, notamment parce qu’avec RECHERCHEV, la colonne recherchée doit obligatoirement se trouver à gauche de la colonne que nous voulons récupérer.
Et même si RECHERCHEX est plus moderne et très agréable à utiliser, elle n’est pas disponible dans toutes les versions d’Excel.
INDEX + EQUIV reste donc une solution très fiable, très compatible, et encore très utile à connaître.
Nous allons donc modifier la formule que nous avons saisie en E6 :
=INDEX(Participants[Prénom];EQUIV(ALEA.ENTRE.BORNES(1;NBVAL(Participants[Prénom]));Participants[N ];0))
Où :
- Nous reprenons notre formule =ALEA.ENTRE.BORNES(1;NBVAL(Participants[Prénom])) pour obtenir un nombre entier au hasard.
- Nous encapsulons cette formule en tant qu’argument de la fonction EQUIV, laquelle cherche une valeur dans une plage, puis renvoie sa position.
- Et le dernier argument de cette fonction EQUIV, 0, signifie que nous voulons une correspondance exacte. C’est important, car nous ne voulons pas qu’Excel trouve un numéro proche. Nous voulons qu’il retrouve exactement le numéro tiré.
- Enfin, nous utilisons INDEX(Participants[Prénom];position) : La fonction INDEX permet de récupérer une valeur située à une certaine position dans une plage, la position donnée par EQUIV.
Résultat : Excel affiche automatiquement le prénom du gagnant.
Si nous le souhaitons, nous pouvons simplifier encore davantage cette formule, car les valeurs de la colonne « N » sont des nombres simples, qui commencent à 1 et qui suivent l’ordre exact du tableau : 1, 2, 3, 4, 5, et ainsi de suite.
Dans cette situation, nous n’avons même pas besoin de chercher la position avec EQUIV, puisque le numéro tiré correspond déjà directement à la position dans la colonne des prénoms.
Nous pouvons donc écrire une formule plus courte :
=INDEX(Participants[Prénom];ALEA.ENTRE.BORNES(1;NBVAL(Participants[Prénom])))
Cette méthode est parfaite lorsque la liste est propre, sans trou, sans numéro manquant, et lorsque l’ordre des numéros correspond exactement à l’ordre des lignes.
En revanche, si votre colonne « N » contient des numéros personnalisés, par exemple des numéros de dossier, des identifiants clients, ou une numérotation qui ne commence pas à 1, il vaut mieux conserver la version avec INDEX + EQUIV.
Pour rendre le résultat plus agréable à lire, nous pouvons aussi afficher une phrase complète au lieu d’afficher uniquement le prénom.
Par exemple, dans la cellule E6, nous pouvons écrire :
="Le gagnant est : "&INDEX(Participants[Prénom];ALEA.ENTRE.BORNES(1;NBVAL(Participants[Prénom])))
Le symbole & permet de coller plusieurs morceaux de texte ensemble.
C’est beaucoup plus propre si nous présentons le tirage à l’écran.
Petite astuce importante : comme nous utilisons une fonction aléatoire, le gagnant peut changer à chaque recalcul du classeur.
Pour relancer le tirage, nous pouvons appuyer sur [F9].
Mais une fois que le bon gagnant est affiché, nous devons penser à figer le résultat si nous voulons le conserver.
Pour cela, nous sélectionnons la cellule qui contient le gagnant, nous copions avec [Ctrl]+[C], puis nous faisons un collage spécial en valeurs.
Le raccourci le plus pratique est [Ctrl]+[Alt]+[V], puis nous choisissons « Valeurs » et nous validons avec « OK ».
De cette manière, la formule est remplacée par le prénom réellement tiré, et le résultat ne changera plus au prochain recalcul.
4. Tirer plusieurs gagnants sans trier la base
Maintenant, nous allons améliorer le fichier pour tirer plusieurs gagnants.
La méthode que je vais vous montrer va fonctionner, sans devoir modifier l’ordre de la base.
Cela sera utile si notre liste est classée par numéro d’inscription ou par ordre d’arrivée, ce n’est pas idéal.
Nous allons donc garder la base intacte.
Pour cela, nous allons ajouter une colonne à droite du tableau, que nous appelons « Nombre aléatoire ».
Étant donné que nous avons transformé notre base en tableau structuré, la colonne va s’ajouter automatiquement.
Il ne restera plus qu’à ajouter la formule suivante dans la cellule C7 :
=ALEA()
Celle-ci va se recopier automatiquement sur toute la colonne.
La fonction ALEA génère un nombre décimal entre 0 et 1.
Chaque participant reçoit donc un score aléatoire et unique.
Nous allons encore une fois modifier la formule pour aller chercher non plus un nombre aléatoire, mais directement la valeur la plus petite de cette nouvelle colonne.
Pour cela, nous allons pouvoir utiliser la fonction Excel PETITE.VALEUR.
=PETITE.VALEUR(Participants[Score aléatoire];1)
Cette formule récupère la plus petite valeur aléatoire lorsque le rang vaut 1, la deuxième plus petite lorsque le rang vaut 2, et ainsi de suite.
="Le gagnant N 1 est : "&INDEX(Participants[Prénom];EQUIV(PETITE.VALEUR(Participants[Score aléatoire];1);Participants[Score aléatoire];0))
Nous recopions la formule vers le bas pour obtenir trois gagnants, et il ne reste plus qu’à modifier le texte affiché et le rang de la valeur à extraire :
="Le gagnant N 2 est : "&INDEX(Participants[Prénom];EQUIV(PETITE.VALEUR(Participants[Score aléatoire];2);Participants[Score aléatoire];0))
="Le gagnant N 3 est : "&INDEX(Participants[Prénom];EQUIV(PETITE.VALEUR(Participants[Score aléatoire];3);Participants[Score aléatoire];0))
À chaque pression sur [F9], Excel recalcule les scores aléatoires, puis désigne automatiquement trois nouveaux gagnants.
L’avantage est que nous ne trions pas le tableau. La base reste propre, dans son ordre d’origine, et la zone de résultat se met à jour toute seule.
Si nous voulons figer les gagnants, nous sélectionnons les cellules contenant les résultats, nous copions avec [Ctrl]+[C], puis nous collons en valeurs.
