Choisir au hasard un membre par équipe avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Choisir au hasard un membre par équipe

Avec cette nouvelle astuce Excel, nous allons découvrir avec quelle simplicité il est possible de tirer un élément au sort dans un groupe. L'idée est par exemple de faire disputer des épreuves en choisissant au hasard les membres d'équipes qui doivent s'affronter. Il peut aussi s'agir d'un moyen d'alterner les postes de travail de façon totalement impartiale.



Dans l'exemple finalisé illustré par la capture, quatre équipes de cinq personnes chacune sont représentées ligne à ligne. A chaque fois que l'utilisateur clique sur le petit bouton situé entre les lignes de titres, les calculs sont regénérés. Ainsi, sur la droite de la feuille, une personne par équipe est tirée du chapeau. Et dans le même temps, des jeux dynamiques de couleurs viennent les surligner dans les cases.

Classeur source et présentation
Pour développer cette nouvelle astuce, nous proposons de débuter à partir d'un classeur offrant ces équipes.

• Télécharger le classeur choisir-membre-equipes-roulette.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Comme vous pouvez le voir sur l'unique feuille de ce classeur, les cinq équipes sont effectivement placées les unes sous les autres. Elles sont différenciées par des jeux de couleurs qui leur sont propres. Sur la droite et plus précisément en colonne H, des cases vides attendent les tirages au sort respectifs pour désigner aléatoirement un membre par équipe. De même, vous notez la présence d'un bouton en colonne G et sur la ligne 4. Il est déjà fonctionnel mais si vous cliquez dessus, rien ne se produit pour l'instant. Sa vocation est d'imposer le recalcul des formules. Mais à ce stade, les formules brillent par leur absence.

Si vous réalisez le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel et que vous affichez le code de l'élément Feuil1 (1auHasard), vous découvrez la procédure attachée au bouton :

Private Sub Generer_Click()
Application.Calculate
End Sub

C'est la méthode Calculate de l'objet Application qui ordonne le recalcul des formules de la feuille.

Tirer au sort par formule
Nous connaissons déjà la fonction Excel Choisir. D'ailleurs elle est aussi disponible avec Access. Elle porte bien son nom. Elle permet de choisir un élément dans un groupe :

=Choisir(Numéro; Elément1; Elément2; Elément3; Elément4; Elément5; ...)

En fonction d'un numéro passé en premier argument, elle ressort l'élément situé à la position correspondante. Et ces éléments sont énumérés dans les arguments qui suivent. Vous l'avez compris, dans notre application, ces éléments sont les membres des équipes. Mais pour que ce choix se fasse au hasard, ce numéro en premier paramètre doit constamment varier. C'est la raison pour laquelle, nous allons exploiter la fonction Alea.Entre.Bornes afin de générer aléatoirement un nombre compris entre 1 pour le premier membre et 5 pour le dernier.

• Sélectionner la case de la première personne à tirer au sort en cliquant sur la cellule H4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Choisir(,

Nous sommes donc placés dans le premier argument de la fonction Choisir. C'est ici précisément que nous devons définir le numéro qui va permettre de choisir aléatoirement l'un des membres de l'équipe.

• Inscrire la fonction de génération aléatoire suivie d'une parenthèse, soit : Alea.Entre.Bornes(,
• Taper le chiffre 1 pour désigner le premier membre de l'équipe,
• Taper un point-virgule (;) pour passer dans l'argument de la borne supérieure,
• Saisir le chiffre 5 pour désigner le dernier membre de l'équipe,
• Puis, fermer la parenthèse de la fonction Alea.Entre.Bornes,
• Taper alors un point-virgule (;) pour passer dans les arguments suivants de la fonction Choisir,

C'est ici que l'énumération des membres doit intervenir, tous reconnus par leurs cellules et chacun séparé d'un autre par un point-virgule. C'est en fonction du numéro généré par la fonction Alea.Entre.Bornes que l'un d'entre eux sera tiré au sort sur la position correspondante.

• Désigner le premier membre de la première équipe en cliquant sur la cellule B4,
• Taper un point-virgule et désigner le deuxième membre en cliquant sur la cellule C4,
• Poursuivre ainsi jusqu'au dernier membre en cellule F4,
• Dès lors, fermer la parenthèse de la fonction Choisir,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons active la cellule du résultat. Comme vous pouvez le voir, le premier membre est automatiquement tiré au sort. Dans le même temps, une couleur surgit pour le mettre en évidence dans son équipe. C'est une règle de mise en forme conditionnelle prédéfinie qui s'en charge. Elle se contente simplement de faire la correspondance entre les noms. Et bien entendu, si vous cliquez sur le bouton situé en colonne G, c'est un autre membre de l'équipe qui est choisi au hasard. Dans le même temps, la couleur de la mise en forme conditionnelle se déplace pour l'identifier dans son équipe.

• Copier la cellule H4 (CTRL + C),
• Puis, la coller (CTRL + V) en H7, H10 et H13,

Comme vous pouvez l'apprécier, pour chaque équipe, un membre est effectivement désigné aléatoirement. Et dans le même temps toujours, chaque personne ainsi tirée au sort est surlignée dans des jeux de couleurs personnalisés à l'intérieur de sa propre équipe. Bien entendu, chaque clic sur le bouton rebat les cartes pour définir de nouveaux membres.



La syntaxe de la formule que nous avons construite et répliquée est la suivante :

=CHOISIR(ALEA.ENTRE.BORNES(1;5); B4; C4; D4; E4; F4)

Une autre solution aurait permis d'aboutir au même résultat. Elle consiste à exploiter la fonction Adresse pour recomposer dynamiquement les coordonnées de la personne à choisir au hasard. Pour cela, en cellule H4, il convient de construire la syntaxe suivante avant de la répliquer sur les autres cellules :

=INDIRECT(ADRESSE(LIGNE(A4); ALEA.ENTRE.BORNES(2; 6))).

En premier argument de la fonction Adresse, nous définissons dynamiquement l'indice de ligne grâce à la fonction Excel Ligne. De cette manière nous pointons sur la ligne de l'équipe et l'indice suit la progression à la recopie. En deuxième argument, nous faisons varier aléatoirement l'indice de colonne entre le chiffre 2 pour la colonne B et le chiffre 6 pour la colonne F. Sur la ligne concernée, nous pointons donc aléatoirement sur l'un des membres de l'équipe. Enfin, la fonction Indirect qui englobe la syntaxe est indispensable pour interpréter ces coordonnées recomposées et pour livrer le contenu de la cellule de destination ainsi désignée.