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.
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
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
- Ecrivons exactement la même référence dans la fonction INDIRECT
=INDIRECT("A16")
- Puis décomposons la référence
- La colonne entre guillemet
- Le numéro (c'est le résultat du nombre aléatoire), en dehors des guillemets
=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, comme la colonne D ici.
