Pour extraire un échantillon aléatoire avec Excel, quelques étapes suffisent
- Générer un nombre aléatoire pour sélectionner les lignes à extraire
Plusieurs fonctions aléatoires d'Excel peuvent être utilisées, comme ALEA.ENTRE.BORNES ou TABLEAU.ALEA
- Retourner les données correspondantes aux numéros de lignes aléatoires
En utilisant la fonction INDIRECT, il est très facile d'associer les numéros aléatoires à de vraies lignes dans Excel
Présentation de notre base clients
Nous allons partir d'un classeur contenant la liste de nos clients. Pour les besoins de l'exemple, nous avons créé une base de 20 noms seulement. Mais la technique expliquée ici va s'appliquer à n'importe quel classeur Excel, quelques soit le nombre de lignes.
Étape 1 : Calculer le nombre de lignes à extraire
L'avantage de mettre les lignes de notre base dans un Tableau, c'est de connaître le ligne très facilement.
=LIGNES(Table1[Prénom])
Ensuite, nous voulons extraire un échantillon de 25% de la totalité de nos clients. Pour éviter toute erreur d'arrondi nous allons utiliser la fonction ENT pour renvoyer le résultat sous forme d'entier.
=ENT(LIGNES(Table1[Prénom])*25%) => 5
Nous allons donc extraire 5 nombres aléatoires
Étape 2 : Construire l'échantillon avec les nombres aléatoires
Nous avons tous les éléments nécessaires pour construire notre liste de nombres aléatoires
- Le nombre de départ ; 2
- Le nombre de lignes au total ; 20
- Et enfin le nombre à extraire ; 5
Et maintenant nous allons utiliser la fonction TABLEAU.ALEA qui a le double intérêt de :
- Renvoyer un nombre aléatoire entre 2 bornes
- Retourner directement une liste de nombres aléatoires
=TABLEAU.ALEA(5;;2;20;VRAI)
Si vous n'avez pas la fonction TABLEAU.ALEA sur votre version d'Excel, vous pouvez utiliser
- la fonction ALEA.ENTRE.BORNES =ALEA.ENTRE.BORNES(2;53770)
- puis la recopier autant de fois que nécessaire ; soit 5 fois ici.
Astuce : Vous pouvez également construire une liste de nombres aléatoires SANS DOUBLON avec la technique vue dans l'article sur le tirage du loto.
Étape 3 : Collage-Spécial en Valeur
Le problème de travailler avec les fonctions qui retournent un nombre aléatoire, c'est que les nombres sont recalculés en permanence. Pour éviter cela, il faut convertir le résultat de la formule en valeur, il faut passer par un collage spécial.
Étape 4 : Construire les références avec les nombres aléatoires
- Partons du premier nombre aléatoire ; le 16
- Nous voulons récupérer le prénom qui est à la ligne 16 ; soit la cellule A16
=A16
- Il existe une formule dans Excel qui permet de "construire" vos références ; c'est la fonction INDIRECT
- Commençons par intégrer la référence dans la fonction INDIRECT
=INDIRECT("A16")
- Puis décomposons la référence ; la colonne entre guillemet et le numéro c'est la valeur du nombre aléatoire
=INDIRECT("A"&F2)
- Et recopiez la formule pour les autres cellules (c'est tout simple)
Étape 5 : Retourner d'autres colonnes
Maintenant, pour retourner la colonne de la ville, toujours en se basant sur le nombre aléatoire, il suffit de changer la lettre de la colonne.
=INDIRECT("D"&F2)
Manseau
Clovis Rabut
Frédéric LE GUEN
11/03/2022 @ 16:43
Excellent, je viens d'apprendre encore grâce à des gens comme vous qui divulguez avec intelligence votre grand savoir.
Merci beaucoup.
23/01/2019 @ 11:31
Bonjour,
Comment éviter les doublons avec la fonction ALEA.ENTRE.BORNE ?
Car avec la technique expliquée au point 2, il peut y avoir une redondance des nom qui sortent.
Et dans le cas ou je souhaite par exemple sélectionner une liste aléatoire de clients à qui envoyer un mail promotionnel, il serait fâcheux que certains d'entre eux reçoivent deux fois le même mail.
Bien à vous
24/01/2019 @ 09:56
Bonjour,
Oui effectivement, la fonction ALEA.ENTRE.BORNE ne se prémunie pas des doublons. Il y a cet article qui explique comment le faire avec les fonctions Excel https://www.excel-exercice.com/rechercher-doublons-formule/ ou alors avec Power Query si vous avez Excel 2016 ou 365 https://www.excel-exercice.com/identifier-les-doublons-avec-power-query/