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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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

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 :

Excel formation - fonction aleanonvolatile - 01

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() :

Excel formation - fonction aleanonvolatile - 02

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 :

Excel formation - fonction aleanonvolatile - 03

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)

Excel formation - fonction aleanonvolatile - 04

 

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 :

Excel formation - fonction aleanonvolatile - 05

 

 

 

 



Articles qui pourraient vous intéresser

Comment exporter une feuille dans un classeur (ouvert ou fermé) sur Excel ?
Sortez du lot : comment créer un CV moderne et impactant avec Excel
Comment ajouter une apostrophe en début de cellule sur Excel ?
Comment cacher des données dans des cellules Excel ?
Comment certifier gratuitement une macro VBA sur Excel ?
Comment remplacer un texte dans une cellule avec la fonction REMPLACER d’Excel ?
Les arguments de valeurs et de références (ByVal et ByRef) de VBA sur Excel
Comment créer un menu dynamique sur Excel ?
Comment traduire un texte dans toutes les langues en utilisant une fonction Excel
Comment utiliser les fonctions de base d’Excel (Addition, soustraction, multiplication, division)
Comment trouver et contrôler toutes les formules d'une feuille de calcul Excel
Comment calculer des sous-totaux dans un tableau Excel : la fonction SOUS.TOTAL

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.