Extractions aléatoires 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 :


Extractions aléatoires

Cette petite astuce Excel propose deux solutions pour extraire des données aléatoirement.



Dans l'exemple illustré par la capture, sur un tableau source, nous produisons deux extractions dans un autre tableau. Et toutes deux sont bien aléatoires. Cependant, la première restitue des doublons tandis que la seconde, moyennant quelques trous, offre une extraction aléatoire de valeurs uniques.

Classeur source
Pour la présentation de cette nouvelle astuce, nous proposons de récupérer ce classeur.

• Télécharger le fichier extraire-aleatoirement-sans-doublons.xlsx en cliquant sur ce lien,
• Double cliquer sur le classeur réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons donc un tableau énumérant des fruits. Sur la droite, une table à deux rangées attend les extractions. Pour terminer les présentations, si vous déployez la zone Nom, en haut à gauche de la feuille Excel, vous constatez que la plage des fruits à extraire est nommée Fruits. Nous exploiterons ce nom pour simplifier la construction des formules.

Extraction aléatoire avec doublons
Dès lors qu'il s'agit de réaliser une extraction de données, la fonction Index se présente comme la plus adaptée. Mais pour que son choix se porte sur des lignes aléatoires, nous devons faire varier ses indices avec la fonction Alea.Entre.Bornes.

• Cliquer sur la cellule E4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la plage d'extraction par son nom, soit : Fruits,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,

C'est lui que nous devons faire varier aléatoirement.

• Taper la fonction de génération aléatoire suivie d'une parenthèse, soit : ALEA.ENTRE.BORNES(,
• Taper le chiffre 1 pour désigner la première ligne du tableau d'extraction,
• Taper un point-virgule (;) pour passer dans l'argument de la borne supérieure,
• Taper le nombre 10 pour désigner la dernière ligne du tableau,

Il s'agit bien sûr d'indices de ligne relatifs à l'analyse portée par la fonction Index.

• Fermer la parenthèse de la fonction Alea.Entre.Bornes,
• Puis, fermer la parenthèse de la fonction Index,
• Dès lors, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette technique permet de conserver active la cellule du résultat pour l'exploiter dans l'enchaînement. Et c'est ce que nous allons faire.

• Tirer la poignée de la cellule jusqu'en E13,

Nous obtenons bien une extraction aléatoire des fruits, certes avec des redondances. En effet, la fonction Alea.Entre.Bornes ne s'interdit pas de générer un nombre qu'elle a déjà proposé. La syntaxe du calcul que nous avons construit est la suivante : =INDEX(Fruits; ALEA.ENTRE.BORNES(1; 10)). Il n'est en effet pas nécessaire de préciser l'indice de colonne en troisième paramètre puisque la recherche est initialisée en premier paramètre sur une seule rangée.

Extraction aléatoire sans doublons
Pour réaliser une extraction aléatoire des valeurs sans générer de répétitions, nous devons exploiter une autre fonction. Il s'agit de la fonction Alea. Si vous tapez la syntaxe suivante dans une cellule : =ALEA(), et que vous la validez, vous obtenez un nombre compris entre 0 et 1 avec une extrême précision comme par exemple cette valeur : 0,013051384.

Astuce : Pour éviter de générer de nouveaux nombres à chaque changement dans les cellules de la feuille, vous pouvez taper la formule directement dans la barre de formule et enfoncer la touche F9 du clavier. Vous obtiendrez un nombre aléatoire statique. Cette astuce est valable pour la fonction Alea mais aussi pour la fonction Alea.Entre.Bornes.

Dans cette extraction sans doublons, le principe consiste à importer un fruit sous condition. Si le nombre aléatoire ne dépasse pas une certaine valeur, le fruit est ignoré. Nous devons donc exploiter la fonction conditionnelle Si.

• Sélectionner la cellule F4 et taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Inscrire la fonction aléatoire suivie de deux parenthèses, soit : Alea(),

En effet, elle ne requiert aucun argument.

• Taper le symbole inférieur (<) pour annoncer le critère à honorer,
• Puis, saisir la valeur : 0,6,

C'est une condition arbitraire. Nous décidons que l'extraction du fruit sur la ligne en cours doit avoir lieu dans la mesure où la génération aléatoire a conduit à une valeur inférieure à ce seuil.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Sélectionner le fruit de la ligne en cours en cliquant sur sa cellule C4,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Inscrire deux guillemets ("") pour garder la cellule vide lorsque le nombre est trop grand,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Double cliquer sur la poignée du résultat pour propager le calcul sur la hauteur du tableau,

Forcément, avec ce procédé linéaire, aucun doublon ne peut être généré. Chaque cellule est passée en revue tour à tour et son extraction n'est pas garantie. Mais des trous sont générés. Souvenez-vous, une technique matricielle nous avait appris à éliminer les trous dans les listes.

La syntaxe de la formule que nous avons construite est la suivante : =SI(ALEA()<0,6;C4;"").

Enfin, si vous souhaitez repérer dynamiquement les doublons, il convient de bâtir une règle de mise en forme conditionnelle. Pour cela, la première rangée de l'extraction doit préalablement être sélectionnée, soit la plage de cellules E4:E13. Dès lors, la syntaxe à bâtir est la suivante :

=NB.SI($E$4:$E$13;E4)>1

Il convient de lui associer une couleur comme le orange par exemple. Dès lors, toutes les redondances surgissent explicitement.



Le principe de cette règle consiste à dénombrer la présence du fruit en cours d'analyse dans la plage complète grâce à la fonction Nb.Si.