Comment Extraire un Échantillon Aléatoire ?

Comment Extraire un Échantillon Aléatoire ?
Dernière mise à jour le 22/05/2024
Temps de lecture : 3 minutes

Pour extraire un échantillon aléatoire avec Excel, quelques étapes suffisent

  1. 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

  2. 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.

Comment extraire un echantillon aleatoire

É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.

Formule pour trouver le nombre de lignes dans le tableau

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

Formule pour déterminer le nombre de lignes à extraire

É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 :

  1. Renvoyer un nombre aléatoire entre 2 bornes
  2. Retourner directement une liste de nombres aléatoires
formule pour creer la liste de nombre aleatoire

Si vous n'avez pas la fonction TABLEAU.ALEA sur votre version d'Excel, vous pouvez utiliser

  1. la fonction ALEA.ENTRE.BORNES =ALEA.ENTRE.BORNES(2;53770)
  2. 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.

Convertir la formule en nombre avec le collage special

Étape 4 : Construire les références avec les nombres aléatoires

  1. Partons du premier nombre aléatoire ; le 16
  2. Nous voulons récupérer le prénom qui est à la ligne 16 ; soit la cellule A16

=A16

  1. Il existe une formule dans Excel qui permet de "construire" vos références ; c'est la fonction INDIRECT
  2. Ecrivons exactement la même référence dans la fonction INDIRECT

=INDIRECT("A16")

  1. 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)

Constuire la reference avec la fonction INDIRECT
  1. Et recopiez la formule pour les autres cellules (c'est tout simple)
Liste des prenoms pris aleatoirement

É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.

Recuperer le nom des villes avec le nombre aleatoire

Articles liés

3 Comments

  1. Manseau
    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.

    Reply

  2. Clovis Rabut
    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

    Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

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

MVP_logo_2017

Comment Extraire un Échantillon Aléatoire ?

Reading time: 3 minutes
Dernière mise à jour le 22/05/2024

Pour extraire un échantillon aléatoire avec Excel, quelques étapes suffisent

  1. 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

  2. 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.

Comment extraire un echantillon aleatoire

É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.

Formule pour trouver le nombre de lignes dans le tableau

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

Formule pour déterminer le nombre de lignes à extraire

É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 :

  1. Renvoyer un nombre aléatoire entre 2 bornes
  2. Retourner directement une liste de nombres aléatoires
formule pour creer la liste de nombre aleatoire

Si vous n'avez pas la fonction TABLEAU.ALEA sur votre version d'Excel, vous pouvez utiliser

  1. la fonction ALEA.ENTRE.BORNES =ALEA.ENTRE.BORNES(2;53770)
  2. 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.

Convertir la formule en nombre avec le collage special

Étape 4 : Construire les références avec les nombres aléatoires

  1. Partons du premier nombre aléatoire ; le 16
  2. Nous voulons récupérer le prénom qui est à la ligne 16 ; soit la cellule A16

=A16

  1. Il existe une formule dans Excel qui permet de "construire" vos références ; c'est la fonction INDIRECT
  2. Ecrivons exactement la même référence dans la fonction INDIRECT

=INDIRECT("A16")

  1. 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)

Constuire la reference avec la fonction INDIRECT
  1. Et recopiez la formule pour les autres cellules (c'est tout simple)
Liste des prenoms pris aleatoirement

É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.

Recuperer le nom des villes avec le nombre aleatoire

Articles liés

3 Comments

  1. Manseau
    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.

    Reply

  2. Clovis Rabut
    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

    Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

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