Fonction ALEA()
Renvoie un nombre décimal entre 0 et 1 pouvant avoir jusqu'à 15 décimales (limite d'Excel).
Un nouveau nombre réel aléatoire est renvoyé chaque fois que la feuille de calcul est recalculée.
Syntaxe
= ALEA()Aucun argument.
Utilisation
Pour obtenir un nombre entier entre 0 et 99, il suffit de d'utiliser :
= ENT(ALEA()*100)Pour obtenir un nombre entier entre 10 et 60, on va d'utiliser :
= ENT( ALEA() * (Valeur Max - Valeur min + 1)) + Valeur Minsoit
= ENT(ALEA()*(60-10+1))+10Depuis de nombreuse versions, Excel met à notre disposition la fonction ALEA.ENTRE.BORNES pour obtenir ce résultat.
Fonction ALEA.ENTRE.BORNES()
Renvoie un nombre entier aléatoire compris entre 2 valeurs spécifiées (Versions >2013).
Un nouveau nombre entier aléatoire est renvoyé chaque fois que la feuille de calcul est recalculée.
Syntaxe
= ALEA.ENTRE.BORNES(Min;Max)- Min : Valeur minimale pouvant être générée.
- Max : Valeur maximale pouvant être générée.
Utilisation
Pour obtenir un nombre entier entre 10 et 60, on va d'utiliser :
=ALEA.ENTRE.BORNES(10;60)Fonction TABLEAU.ALEA()
Renvoie un tableau de nombres aléatoires (Versions > 2016).
Un nouveau tableau de nombres aléatoires est renvoyé chaque fois que la feuille de calcul est recalculée.
Fonction intéressante avec l'utilisation des tableau dynamique (Voir Formules de tableaux dynamiques, une nouvelle logique de conception).
Syntaxe
=TABLEAU.ALEA([Lignes] ; [Colonnes] ; [Min]; [Max] ; [Entier])- Lignes : (Facultatif) Nombre entier de lignes du tableau à générer (par défaut 1).
- Colonnes : (Facultatif) Nombre entier de colonne du tableau à générer (par défaut 1).
- Min : (Facultatif) Valeur minimale pouvant être générée.
- Max : (Facultatif) Valeur maximale pouvant être générée.
- Entier : (Facultatif : True/False)
- VRAI : Génération de nombres entiers.
- FAUX ou omis : Génération de nombres décimales (=avec virgule).
Utilisation
Génération d'un tableau de 2 lignes et 3 colonnes de nombres entiers entre 10 et 60.
=TABLEAU.ALEA(2;3;10;60;VRAI)Générer des nombres aléatoires uniques dispersés
Avec les fonctions précédentes (ALEA, ALEA.ENTRE.BORNES, TABLEAU.ALEA) , on peut obtenir plusieurs fois la même valeur. Cett fois, on veut obtenir 10 valeurs uniques entre 0 et 50.
(en colonne) = LET(n;10;PRENDRE(UNIQUE(TABLEAU.ALEA(4*n;1;0;50;VRAI));n))
(en ligne) = LET(n;10;PRENDRE(UNIQUE(TABLEAU.ALEA(1;4*n;0;50;VRAI));;n))Exemple de résultat : 11 ; 38 ; 18 ; 17 ; 0 ; 16 ; 6 ; 0 ; 6 ; 16.
Principe
- On va générer beaucoup plus de nombres aléatoires que nécessaire (ici 4 fois plus=>4*n), on retire les doublons (UNIQUE) en espérant qu'il en reste assez 😁. Puis on ne conserve que les 10 premiers (PRENDRE).
Générer une suite de nombres se suivant en ordre aléatoire
On veut obtenir les 5 chiffres de 1 à 5 selon un ordre aléatoire.
(en colonne) = LET(n;5;
s;SEQUENCE(n);
m;TABLEAU.ALEA(n);
TRIERPAR(s;m))
(en ligne) = LET(n;5;
s;SEQUENCE(;n);
m;TABLEAU.ALEA(;n);
TRIERPAR(s;m))Exemple de résultat : 2 ; 5 ; 1 ; 4 ; 3.
Principe
- On génère une matrice de valeur de 1 à 5 (s), on la trie (TRIERPAR) selon l'ordre d'une matrice de nombre aléatoire (m).
Exemples de mise en œuvre
Tirage aléatoire dans une liste
Dans une liste on veux faire une tirage au sort de 3 personnes. On utilisera par exemple une des formules de la partie "Générer des nombres aléatoires uniques dispersés"
=LET(P;Liste;N;3;i;PRENDRE(UNIQUE(TABLEAU.ALEA(4*N;1;0;NBVAL(P);VRAI));N);INDEX(P;i))Génération d'un mot de passe
Mots de passe de 10 caractères (exemple : "0hk1=@XC6z").
=CONCAT(CAR(TABLEAU.ALEA(1;10;48;125;VRAI)))Volatilité des résultats
Toutes ces fonctions sont des fonctions volatiles, c'est à dire qu'Excel va regénérer les valeurs renvoyées à chaque saisie / mise à jour dans le classeur !
Pour arrêter ce changement de valeur, la seule solution sera de détruire les formules en les remplaçant par leurs résultats via un Copier > Collage spécial > Valeurs.
Macros VBA
Sub RemplirAvecAleatoire()
'Nombre décimale entre 0 et 1
Dim rgCell As Range
Randomize
For Each rgCell In Selection
rgCell.Value = Rnd()
Next
End Sub
Sub AleatoireEntre2Bornes()
Const lgDebut As Double = 1 ' Borne de début
Const lgFin As Double = 2 ' Borne de fin
Const byMaxDec As Byte = 1 ' Nombre de maximale de décimale
' - Avec 1, on peut obtenir 5,2 mais aussi 5 (soit 5,0)
Dim rgCell As Range
Randomize
For Each rgCell In Selection
rgCell.Value = Round(lgDebut + (lgFin - lgDebut) * Rnd, byMaxDec)
Next
End Sub
Sub AleatoireEntre2BornesSansDoublons()
Const lgDebut As Double = 1 ' Borne de début
Const lgFin As Double = 2 ' Borne de fin
Const byMaxDec As Integer = 1 ' Nombre de maximale de décimales (Avec 1 on peut obtenir 5,2 ou 5 soit 5,0)
Dim lgNbCell As Long
Dim i As Long
Dim dicListe As Object
Dim rgCell As Range
Dim lgValeur As Double
' Vérifier que l'intervalle est suffisant
lgNbCell = Selection.Count
If (((lgFin - lgDebut) * 10 ^ byMaxDec) + 1) < lgNbCell Then
MsgBox "Intervalle insuffisant pour générer " & lgNbCell & " valeurs uniques."
Exit Sub
End If
' Dictionnaire pour éviter les doublons
Set dicListe = CreateObject("Scripting.Dictionary")
' Génération des valeurs uniques
Randomize
Do While dicListe.Count < lgNbCell
lgValeur = lgDebut + Round((lgFin - lgDebut) * Rnd, byMaxDec)
If Not dicListe.Exists(lgValeur) Then _
dicListe.Add lgValeur, lgValeur
Loop
' Remplir la sélection
i = 0
For Each rgCell In Selection
rgCell.Value = dicListe.Items()(i)
i = i + 1
Next
End SubMerci pour votre attention bienveillante.