Tutoriel Excel : comment générer des nombres aléatoires

Ce tutoriel vous montre comment utiliser les fonctions ALEA et ALEA.ENTRE.BORNES pour générer des nombres aléatoires, des dates, des mots de passe et d'autres chaînes de texte aléatoirement dans Excel.

Il peut y avoir des cas où vous devez générer des nombres aléatoires dans Excel. Par exemple, pour sélectionner des gagnants au hasard dans une liste ou pour obtenir une liste aléatoire de nombres pour l'analyse des données ou pour créer des groupes aléatoires d'élèves en classe.

Quelle que soit la raison, Excel vous propose deux fonctions différentes qui vous donneront des nombres aléatoires : ALEA et ALEA.ENTRE.BORNES.

Table des matières

Fonction Excel ALEA - générer des nombres réels aléatoires

Exemple 1: spécifier la valeur limite supérieure de la plage

Exemple 2 : générer des nombres aléatoires entre deux nombres

Exemple 3 : génération d'entiers aléatoires dans Excel

Fonction Excel ALEA.ENTRE.BORNES - générer des entiers aléatoires dans une plage spécifiée

Comment générer des nombres aléatoires dans Excel pour deux cellules et plus ?

Générer des nombres décimaux avec la fonction ALEA.ENTRE.BORNES

Générer des nombres aléatoires avec des décimales spécifiées

Générer des alphabets aléatoires dans Excel

Générer des dates aléatoires dans Excel

Générer des nombres aléatoires uniques à l'aide de la fonction ALEA et RANG dans Excel

Comment empêcher ALEA et ALEA.ENTRE.BORNES de recalculer ?

Fonction Excel ALEA - générer des nombres réels aléatoires

Pour générer un nombre aléatoire dans Excel entre 0 et 1 (excluant), nous avons la fonction ALEA().

ALEA() est une fonction volatile, ce qui signifie qu'un nouveau nombre aléatoire est généré chaque fois que la feuille de calcul est mise à jour. Et cela se produit chaque fois que vous effectuez une action sur une feuille de calcul ; par exemple mettre à jour une formule (pas nécessairement la formule ALEA, mais n'importe quelle autre formule sur une feuille), modifier une cellule ou entrer de nouvelles données.

La fonction ALEA est disponible dans toutes les versions d'Excel 2019, 2016, 2013, 2010, 2007, 2003, XP et 2000.

Étant donné que la fonction Excel ALEA n'a pas d'arguments, il nous suffit de taper la formule « =ALEA() » dans la cellule et d'appuyer sur « Entrée ». La valeur change à chaque fois qu'une modification est apportée à la feuille.

Exemple 1: spécifier la valeur limite supérieure de la plage

Comme vous le savez maintenant, la formule ALEA dans Excel est utilisée pour générer des nombres aléatoires entre 0 et 1 uniquement, mais vous pouvez aussi l’utiliser pour renvoyer des nombres aléatoires entre 0 et 100 en utilisant la formule qui suit :

Ex. =100 * ALEA()

Dans cette formule, la fonction ALEA génère un nombre aléatoire entre 0 et 1 qui sera ensuite multiplié par 100 pour obtenir les nombres entre 0 et 100. Ainsi on obtient des nombres aléatoires comme le montre la capture ci-dessous :

Remarque : la valeur de la limite supérieure n'est jamais incluse dans la séquence aléatoire renvoyée. Par exemple, si vous souhaitez obtenir des nombres aléatoires entre 0 et 10, y compris 10, la bonne formule est : =11 * ALEA().

Exemple 2 : générer des nombres aléatoires entre deux nombres

Si vous souhaitez générer des nombres aléatoires entre deux nombres, vous pouvez utiliser la formule ALEA suivante :

=a + (b-a) * ALEA(), ici « a » et « b » peuvent être des nombres quelconques. Cette formule génère un nombre aléatoire entre deux entiers a et b.

Par exemple, pour obtenir un nombre aléatoire entre 5 et 10, utilisez la formule : =5 + (10 - 5) * ALEA().

Remarque : cette formule ne renverra jamais un nombre égal au plus grand nombre de la plage spécifiée (valeur b).

Exemple 3 : génération d'entiers aléatoires dans Excel

Si vous souhaitez générer les nombres aléatoires entre 5 et 10, par exemple, vous pouvez utiliser la formule vue dans l’exemple précèdent : =5 + (10-5) * ALEA(). Elle vous donnera les nombres aléatoires entre 5 et 10. Appliquez ensuite la fonction ENT( ) pour arrondir l'entier de sortie à l'entier positif le plus proche :

=ENT(5 + (10-5) * ALEA())         

Fonction Excel ALEA.ENTRE.BORNES - générer des entiers aléatoires dans une plage spécifiée

La fonction ALEA est juste géniale, mais que faire si vous ne cherchez pas un nombre aléatoire entre 0 et 1 ?

Vous pouvez certes multiplier les nombres aléatoires par une valeur définie, mais il existe un moyen plus simple : la fonction ALEA.ENTRE.BORNES.

La syntaxe de la fonction ALEA.ENTRE.BORNES est comme suit :

=ALEA.ENTRE.BORNES(min ; max)

  • L'argument « min » est le plus petit entier que la fonction retournera.
  • L'argument « max » est le plus grand entier que la fonction retournera.

L'exécution de cette fonction vous donnera des entiers aléatoires (sans décimales) entre ces deux nombres. N'oubliez pas que les deux nombres indiqués dans les arguments « min » et « max » peuvent aussi bien apparaître.

Par exemple, pour générer des entiers aléatoires entre 10 et 50 (10 et 50 compris), utilisez la formule ALEA.ENTRE.BORNES suivante :

=ALEA.ENTRE.BORNES(10; 50)

La fonction ALEA.ENTRE.BORNES dans Excel peut également créer des nombres positifs et négatifs. Par exemple, pour obtenir une liste d'entiers aléatoires de -10 à 10, entrez la formule suivante dans votre feuille de calcul :

=ALEA.ENTRE.BORNES(-10; 10)

La fonction ALEA.ENTRE.BORNES est disponible dans Excel 2019, Excel 2016, Excel 2013, Excel 2010 et Excel 2007. Dans les versions antérieures, vous pouvez utiliser la formule ALEA illustrée dans l'exemple 3 ci-dessus.

Plus loin dans ce tutoriel, vous trouverez quelques exemples de formules supplémentaires montrant comment utiliser la fonction ALEA.ENTRE.BORNES pour générer des valeurs aléatoires autres que des entiers.

Comment générer des nombres aléatoires dans Excel pour deux cellules et plus ?

Nous pouvons également générer des nombres aléatoires dans plusieurs cellules en une seule fois. L'utilisation de la poignée de recopie est un moyen assez rapide de le faire. Mais il existe un moyen plus rapide. Suivez les étapes ci-dessous :

  1. Tout d'abord, sélectionnez la plage de cellules dont vous avez besoin pour générer des nombres aléatoires dans Excel.
  2. Maintenant, ouvrez la formule ALEA.ENTRE.BORNES.
  3. Indiquez le nombre inférieur et le nombre supérieur.
  4. Fermez la parenthèse et n'appuyez pas simplement sur la touche ENTRÉE mais plutôt sur CTRL + ENTRÉE pour insérer des nombres aléatoires dans les cellules sélectionnées.

Générer des nombres décimaux avec la fonction ALEA.ENTRE.BORNES

La formule ALEA.ENTRE.BORNES ne peut insérer que des nombres entiers positifs et négatifs. Si l'un des nombres inférieurs et supérieurs est décimal, Excel convertit automatiquement le nombre en la valeur entière la plus proche.

Comme nous pouvons le voir dans l'image ci-dessus, le nombre minimum est 5,5 et le nombre maximum est 15,5. Ainsi, Excel convertit automatiquement le nombre inférieur 5,5 en 6 et 15,5 en 16. Nous avons donc obtenu les nombres entre 6 et 16.

Afin de générer des nombres décimaux avec la fonction ALEA.ENTRE.BORNES, vous devez multiplier la valeur par la fonction ALEA.

La fonction ALEA génère des nombres décimaux supérieurs à 0 et inférieurs à 1. La valeur donnée par la formule Excel ALEA.ENTRE.BORNES sera multipliée par la valeur décimale donnée par la fonction ALEA.

Générer des nombres aléatoires avec des décimales spécifiées

Bien que la fonction ALEA.ENTRE.BORNES dans Excel ait été conçue pour renvoyer des nombres entiers aléatoires, vous pouvez la forcer à renvoyer des nombres décimaux aléatoires avec autant de décimales que vous le souhaitez.

Par exemple, pour obtenir une liste de nombres avec une décimale, vous multipliez les valeurs inférieure et supérieure par 10, puis divisez la valeur renvoyée par 10 :

ALEA.ENTRE.BORNES (valeur inférieure * 10 ; valeur supérieure * 10) / 10

La formule ALEA.ENTRE.BORNES suivante renvoie des nombres décimaux aléatoires compris entre 1 et 50 et avec un seul chiffre après la virgule :

=ALEA.ENTRE.BORNES(1 * 10; 50 * 10) / 10

De manière similaire, pour générer des nombres aléatoires entre 1 et 50 avec 2 décimales, vous multipliez les arguments de la fonction ALEA.ENTRE.BORNES par 100, puis divisez le résultat par 100 :

=ALEA.ENTRE.BORNES(1 * 100; 50 * 100) / 100

Générer des alphabets aléatoires dans Excel

La fonction ALEA.ENTRE.BORNES est non seulement utilisée pour générer des nombres au hasard, mais également pour générer des alphabets aléatoires.

Nous devons alors inclure la fonction CAR dans Excel avec ALEA.ENTRE.BORNES.

=CAR(ALEA.ENTRE.BORNES(CODE("A"); CODE ("Z")))

Où A est le premier caractère et Z est le dernier caractère de la plage de lettres que vous souhaitez inclure (par ordre alphabétique).

Dans la formule ci-dessus :

  • CODE renvoie des codes ASCII numériques pour les lettres spécifiées.
  • ALEA.ENTRE.BORNES prend les nombres renvoyés par les fonctions CODE comme valeurs inférieure et supérieure de la plage.
  • CAR convertit les codes ASCII aléatoires renvoyés par ALEA.ENTRE.BORNES en des lettres correspondantes.

Si vous connaissez bien le tableau des codes de caractères ASCII, rien ne vous empêche de fournir les codes directement à la fonction ALEA.ENTRE.BORNES.

Par exemple, pour obtenir des lettres MAJUSCULE aléatoires entre A (code ASCII 65) et Z (code ASCII 90), vous écrivez :

=CAR(ALEA.ENTRE.BORNES(65; 90))

Pour générer des lettres minuscules de « a » (code ASCII 97) à « z » (code ASCII 122), vous utilisez la formule qui suit :

=CAR(ALEA.ENTRE.BORNES(97; 122))

Pour insérer un caractère spécial aléatoire, tel que ! " #  $ % & '( ) * + , - . /, utilisez la fonction ALEA.ENTRE.BORNES avec le paramètre inférieur défini sur 33 (code ASCII pour "!") et le paramètre supérieur défini sur 47 (code ASCII pour "/") :

=CAR(ALEA.ENTRE.BORNES(33; 47))

Générer des dates aléatoires dans Excel

Nous avons vu comment la formule ALEA.ENTRE.BORNES peut être utilisée pour générer des nombres aléatoires et des alphabets aléatoires. Nous allons maintenant voir comment nous pouvons générer des dates aléatoires.

  1. Ouvrez d'abord la fonction DATE.
  2. Le premier argument est « Année», tapez la formule ALEA.ENTRE.BORNES et utilisez les années 2018, 2020 comme valeurs « min » et « max » respectivement.
  3. L'argument suivant dans la fonction DATE est « Mois», ouvrez de nouveau la fonction ALEA.ENTRE.BORNES et fournissez des nombres de 1 et 12.
  4. Le dernier paramètre de la fonction DATE dans Excel est « Jour», tapez la fonction Excel ALEA.ENTRE.BORNES et utilisez les valeurs 1 et 31.
  5. Maintenant, appuyez sur Ctrl + Entrée. Vous obtiendrez des dates aléatoires dans une plage de cellules sélectionnée.

Générer des nombres aléatoires uniques à l'aide de la fonction ALEA et RANG dans Excel

Il peut arriver que la fonction ALEA renvoie des valeurs en double. Donc par prudence, je vous recommande de vérifier les valeurs en double lorsque vous utilisez cette fonction.

La formule générique de la fonction EQUATION.RANG est :

=RANG.EQ(nombre; réf; [ordre])

Les paramètres de la fonction « EQUATION.RANG » sont :

  • nombre - un nombre pour lequel nous voulons trouver le rang dans le tableau.
  • réf - le tableau où nous voulons classer le nombre.
  • [ordre] – utilisé pour le classement dans l'ordre croissant ou décroissant (0 ou valeur omise).

Par exemple, si vous souhaitez générer 10 nombres aléatoires dans Excel (sans répétitions), suivez les étapes ci-dessous :

  1. Sélectionnez les cellules dans lesquelles vous souhaitez obtenir les nombres aléatoires.
  2. Dans la cellule active, entrez =ALEA().
  3. Maintenez la touche « Ctrl » enfoncée et appuyez sur Entrée.
  4. Sélectionnez toutes les cellules (où vous avez le résultat de la fonction ALEA) et convertissez-les en valeurs.
  5. Dans la colonne adjacente, utilisez la formule suivante : =EQUATION.RANG(A1; $A$2:$A$12).

Vous pouvez maintenant utiliser les valeurs de la colonne B comme nombres aléatoires !

Comment empêcher ALEA et ALEA.ENTRE.BORNES de recalculer ?

Comme la fonction ALEA recalcule si une modification est apportée à la feuille, nous devons copier puis coller les formules en tant que valeurs si nous ne voulons pas que les valeurs soient modifiées à chaque fois. Pour cela, nous devons coller les valeurs de la fonction ALEA() à l’aide de l’option « Collage spécial » afin que les cellules n’affichent que le résultat de la formule et ne soient pas recalculées.

Pour ce faire, suivez les étapes ci-dessous :

  1. Tout d’abord, sélectionnez les valeurs retournées par la fonction ALEA.
  2. Appuyez sur « Ctrl + C» pour copier les valeurs (ou faites un clic droit dessus et sélectionnez « Copier »).
  3. Sans modifier la sélection, appuyez sur Alt + Ctrl + Vpour ouvrir la boîte de dialogue « Collage spécial ».
  4. Choisissez « Valeurs» dans les options et cliquez sur « OK ».

Ainsi, vous pouvez voir que la valeur dans la barre de formules est la valeur elle-même et non la fonction ALEA().

Il existe une autre façon d'obtenir la valeur recherchée, mais uniquement pour une cellule. Si vous souhaitez obtenir directement la valeur, et non pas la fonction, les étapes sont les suivantes :

  • Tout d'abord, tapez =ALEA() dans la barre de formules, puis appuyez sur F9 pour remplacer la formule par sa valeur et appuyez enfin sur «Entrée»

Conclusion :

  • La génération de nombres aléatoires dans Excel est très facile avec la fonction ALEA. Ainsi, ALEA.ENTRE.BORNES vous offre encore plus d'options.
  • La fonction ALEA() dans Excel calcule une nouvelle valeur chaque fois que la feuille de calcul est modifiée. Pour surmonter ce problème, il vous suffit de convertir le résultat généré en valeurs.
  • Pour éviter le changement automatique des valeurs retournées par la fonction ALEA(), il vous suffit d'accéder à la barre de formules Excel et d'appuyer sur F9 pour convertir la formule ALEA en son résultat.
  • Si vous souhaitez générer un ensemble de nombres aléatoires dans plusieurs cellules, sélectionnez les cellules et entrez ALEA(), puis appuyez sur Ctrl + Entrée.
  • Si vous souhaitez calculer les nombres aléatoires entre deux nombres, vous pouvez utiliser la fonction ALEA.ENTRE.BORNES ou la formule ALEA suivante : =a + (b-a) * ALEA()

Télécharger le fichier Excel de ce tutoriel

Article publié le 01 Août 2020par Hanane Mouqqadim