ALEATOIRE() : Une fonction de tirage aléatoire qui ne s’actualise pas sur Excel (non volatile)
Dans ce tutoriel, nous allons découvrir les fonctions qui permettent d’effectuer un tirage aléatoire sur Excel. En plus des traditionnelles fonctions ALEA() et ALEA.ENTRE.BORNES(), nous allons voir comment créer et utiliser la fonction ALEATOIRE() qui va permettre de retrouver les fonctionnalités des deux précédentes méthodes en permettant en plus de ne pas actualiser les résultats obtenus lors des recalculs.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Les fonctions ALEA() et ALEA.ENTRE.BORNES()
Nous avons déjà eu l’occasion déborder les deux fonctions permettant d’effectuer un tirage aléatoire sur Excel au cours de tutoriels précédents (ici, ici et encore là).
La première d’entre elles, la fonction ALEA() permet d’obtenir un nombre décimal supérieur ou égal à zéro et strictement inférieur à 1.
Le nombre ainsi retourné par Excel est généré à chaque fois qu'un calcul est effectué, même si ce calcul ne concerne pas la cellule.
Il suffit alors d’ajouter un calcul sur une autre cellule, d’ouvrir à nouveau le fichier ou encore de lancer un nouveau recalcul de la feuille pour voir le contenu de la cellule modifié (en appuyant par exemple sur la touche [f9].
La fonction ALEA() ne nécessite aucun paramètre et s’utilise simplement en la saisissant dans la barre des formules.
Par contre, l'absence de paramètre exigé par la fonction ne permet pas d'avoir la main sur le résultat retourné.
Pour définir les bornes de départ et de fin, il convient alors de retraiter le résultat obtenu de la manière suivante :
=ALEA()*([max]-[min])+[min]
Dans cette formule :
- [min] est la valeur minimale que l'on souhaite obtenir
- [max] est la valeur maximale que l'on souhaite obtenir
Pour éviter d’avoir à saisir cette formule assez complexe sur les versions les plus récentes d'Excel, nous pouvons utiliser la fonction ALEA.ENTRE.BORNES().
Contrairement à la fonction ALEA() basique, celle-ci attend deux arguments min, et max qui permettent de définir respectivement la valeur minimale attendue, et la valeur maximale.
En dehors du fait de permettre de définir des bornes plancher et plafond, l’autre grande différence entre les deux fonctions c’est que ALEA.ENTRE.BORNES() permet d’obtenir des nombres entiers.
=ALEA.ENTRE.BORNES(100;200)
2. Créer notre propre fonction de tirage ALEATOIRE
Voyons maintenant comment créer notre propre fonction personnalisée pour effectuer un tirage aléatoire.
Bien entendu, pour créer cette fonction, nous allons utiliser VBA :
- Pour commencer, nous lancer VBE en appuyant sur les touches [Alt]+[F11],
- Ensuite, nous créons un nouveau module : Menu Insertion > Module :
Et c’est dans ce module que nous allons insérer la fonction, en saisissant le mot-clé Function, suivi du nom de la fonction, par exemple ALEATOIRE() :
Pour tirer un nombre aléatoire en VBA, simplement appeler la méthode Rnd :
Function ALEATOIRE()
ALEATOIRE = Rnd
End Function
Ensuite, nous pouvons appeler cette fonction directement depuis la feuille de calcul, comme nous le ferions pour n’importe quelle autre fonction d’Excel :
Il suffit de taper le symbole égal pour spécifier à Excel que nous allons entrer une formule, puis appeler le nom de la fonction que nous avons défini.
En revanche, il ne s’agit pas d’un réel nombre aléatoire, pour cela, nous allons simplement devoir ajouter la méthode Randomize, juste avant le premier appel à Rnd :
Function ALEATOIRE()
Randomize
ALEATOIRE = Rnd
End Function
Maintenant, pour améliorer cette fonction, nous allons vouloir laisser à l’utilisateur la possibilité de renseigner des bornes en tant qu’argument de la fonction.
Ces arguments sont optionnels :
- S’ils sont omis, la fonction va fonctionner comme la fonction ALEA() : tirer un nombre aléatoire compris entre 0 et 1,
- S’ils sont renseignés, la fonction va fonctionner comme la fonction ALEA.ENTRE.BORNES() : tirer un nombre aléatoire compris entre les deux bornes
Pour cela, venons insérer les arguments de la fonction en utilisant le mot-clé Optional :
Function ALEATOIRE(Optional min As Variant, Optional max As Variant)
Ici, ces deux variables devront être des nombres entiers, mais nous allons les typer en tant que Variant, ce qui nous permettra de pouvoir utiliser la fonction IsMissing() qui permet de contrôler si un argument est renseigné ou non :
- Si l’argument est saisi, la fonction IsMissing() retourne True,
- Si l’argument est omis, la fonction IsMissing() retourne False
Nous pouvons donc effectuer un test pour déterminer ce que nous souhaitons réaliser :
If IsMissing(max) Then
' idem ALEA()
ALEATOIRE = Rnd
Else
' idem ALEA.ENTRE.BORNE
End If
Ne reste alors plus qu’à utiliser la formule que nous avons vue dans la première partie pour déterminer la valeur d’un nombre entier compris entre les deux bornes :
' idem ALEA.ENTRE.BORNE
ALEATOIRE = (Rnd * (max - min)) + min
Comme nous l’avons vu un peu plus tôt, les arguments sont typés en tant que Variant, ce qui fait que l’utilisateur a toute liberté pour saisir des valeurs à virgule.
Nous pouvons alors utiliser la fonction Int() pour nous permettre de transformer les arguments saisis par l’utilisateur en tant que valeur entière :
min = Int(min)
max = Int(max)
Nous allons également utiliser la fonction Int() pour retourner un résultat entier
ALEATOIRE = Int((Rnd * (max - min)) + min)
3. Rendre une fonction volatile
Le résultat que nous retrouvons ici correspond effectivement au résultat attendu, c’est-à-dire obtenir un nombre aléatoire, lequel restera identique, même après un recalcul de la feuille.
Si nous souhaitons rendre celui-ci volatile, nous pouvons utiliser la méthode Volatile de l’objet Application, en utilisant la valeur True :
Application.Volatile True
Cette ligne est à insérer au tout début de la fonction.
Il suffit maintenant d’appuyer sur la touche [F9] pour relancer un calcul de la feuille et modifier la valeur de la fonction :
