Astuces Expert

Extraire un échantillon aléatoire

Temps de lecture 3 minutes

Cet article va vous montrer comment sélectionner un échantillon de valeur très facilement.

Etape 1 : Taille de notre échantillon

Nous voulons extraire un échantillon de 1% 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(53769*1%)    => 537

Nous devons donc extraire 537 clients de notre base. Ça fait beaucoup mais vous allez voir comme c'est facile avec cette technique 😉

Etape 2 : Créer un nombre aléatoire entre les 2 extrêmités de notre liste

Nous allons chercher à créer une formule qui va nous retourner un nombre entre 2 et 53770.

  • 2 car il s'agit du numéro de ligne contenant le premier nom de nos clients,
  • 53770 car il s'agit de la dernière ligne de notre base client.
Premiere et derniere lignes de notre base de donnees

Pour retourner une valeur aléatoire entre ces 2 valeurs, nous allons utiliser la fonction ALEA.ENTRE.BORNES

=ALEA.ENTRE.BORNES(2;53770)

Et cette formule, nous allons la recopier 537 fois (résultat calculé dans le point précédent).

Recopie du nombre aleatoire autant de fois quattendu

Etape 3 : Collage-Spécial en Valeur

Une fois la recopie réalisée, il faut transformer les formules en nombres.

C'est indispensable car la fonction ALEA.ENTRE.BORNES va recalculer de nouvelles valeurs à chaque nouvelle action dans Excel.

Pour convertir le résultat de la formule en valeur, il faut passer par un collage spécial.

Nombre aleatoire converti en valeur

Etape 4 : Lier les valeurs à une référence

L'ordinateur a généré comme premier nombre aléatoire la valeur 41721.

Donc pour retourner le mail (colonne A) correspondant à cette ligne, la formule à écrire est tout simplement

=A41721

Reference vers lun des mails de notre base client

Etape 5 : Convertir en référence avec INDIRECT

Nous allons maintenant écrire la même référence mais dans une fonction INDIRECT, en écrivant la référence de la cellule entre guillemets.

Le résultat reste le même.

=INDIRECT("A41721")

Ecriture de la reference dans une fonction INDIRECT

C'est maintenant que tout se joue :

  1. Nous allons retirer de la chaîne de caractères le numéro de la ligne
  2. A la suite de la chaîne de caractères, nous allons ajouter le symbole & (comme cela a été expliqué dans cet article)
  3. Enfin, nous allons sélectionner la cellule E2 (la cellule qui contient le numéro de la ligne)

L'écriture devient alors :

=INDIRECT("A"&E2)

Extraction dynamique les emails

Il ne nous reste plus qu'à recopier cette formule pour les 536 autres valeurs et ainsi récupérer les emails de notre échantillon.

Vous pouvez de nouveau convertir en valeur mais ce n'est pas obligatoire.

Etape 6 : Retourner d'autres colonnes

Si vous voulez retourner le nom, ou le prénom de vos clients, il suffit de refaire exactement la même formule mais en changeant la lettre de la colonne, toujours entre guillemets.

Le nom est en colonne C donc la formule sera :

=INDIRECT("C"&E2)

Le prénom est en colonne B donc la formule sera

=INDIRECT("B"&E2)

Fonction qui regroupe le nom et le prenom

Vous trouverez des informations complémentaires sur la fonction ALEA.ENTRE.BORNES sur le site de Microsoft.

Articles liés

Comprendre les macros VBA

Frédéric LE GUEN

Analyser vos formules avec F9

Frédéric LE GUEN

Formule pour séparer un texte sur un délimiteur

Frédéric LE GUEN

2 commentaires

Clovis Rabut 23/01/2019 at 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

Répondre
Frédéric LE GUEN 24/01/2019 at 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/

Répondre

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.