Générer des nombres aléatoires uniques sans VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Générer des nombres aléatoires uniques par calculs Excel

Dans cette formation Excel, nous proposons de montrer comment il est possible de générer plusieurs nombres aléatoires uniques et bornés et ce, sans code VBA. Les applications sont nombreuses. Il pourrait par exemple s'agir d'attribuer des ordres de passage aléatoires à des candidats, pour changer de l'éternel classement alphabétique. C'est aussi une façon dérivée de trier aléatoirement des tableaux. Nous le verrons en application directe dans la prochaine formation.



Dans l'application finalisée illustrée par la capture ci-dessus, des numéros uniques sont attribués aléatoirement à chacun des salariés. L'enjeu sans code VBA, consiste à produire ces nombres au hasard et sans doublons.

Sources et présentation de la problématique
Nous avons besoin de données à manipuler pour réaliser ces travaux. C'est pourquoi nous proposons de récupérer une source existante.

• Télécharger le classeur nombres-aleatoires-uniques.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles. Nous exploiterons la feuille Répartition_postes dans une prochaine formation. Elle permettra une mise en application dérivée de cette génération aléatoire, pour répartir automatiquement les salariés d'une entreprise, sur les différentes lignes de production.

La feuille Concaténer est celle qui nous intéresse ici. Elle porte ce nom particulier car elle était destinée à l'origine, à rassembler l'information du prénom et du nom de chaque salarié dans une même cellule. Elle énumère les trente employés d'une entreprise entre la cellule B5 et la cellule D34.

Deux boutons de macros sont présents, entre les colonnes H et J. Le bouton Trier concerne une prochaine formation. Elle consistera à appliquer ces acquis pour produire des tris aléatoires sur des tableaux Excel.

Le bouton Générer est celui qui doit déclencher le recalcul pour fixer l'attribution des numéros uniques à chacun des employés. Il s'agira de créer une macro automatique et non pas de développer un code VBA. Ces nombres aléatoires devront être inscrits dans la colonne Ordre de passage du tableau des employés.

Des colonnes sont prévues pour des calculs intermédiaires entre H et J, à partir de H5. En colonne H, il s'agira de produire une grande quantité de valeurs aléatoires pour que statistiquement, chaque nombre compris entre 1 et 30, pour les 30 salariés, soit produit. La colonne I doit extraire ces nombres dissociés de leurs formules pour en réaliser une sorte de photo à l'instant t. La colonne J doit servir à repérer chacun de ces nombres uniques par des numéros incrémentés. C'est une astuce que nous avons notamment exploitée dans la formation Excel pour extraire des données sur des critères recoupés. Il ne restera plus qu'à les exploiter pour restituer les valeurs qu'ils pointent, dans la colonne Ordre de passage.

Générer des nombres aléatoires par calculs Excel
La fonction Excel permettant de générer des nombres aléatoires se nomme Alea.Entre.Bornes. Sa syntaxe est la suivante :

=Alea.Entre.Bornes(valeur_inférieure ; valeur_supérieure)

Il faut donc lui passer les bornes inférieures et supérieures en paramètres. Comme son nom l'indique, elle retourne un nombre aléatoire entier, compris entre ces bornes, dans la cellule où elle est écrite. Comme nous devons produire une valeur unique pour chacun des 30 salariés, nous devons générer un entier compris entre 1 et 30. Mais rien n'empêche cette fonction de proposer une valeur qui a déjà été générée, bien au contraire. C'est pourquoi nous proposons de répliquer ce calcul en colonne H, sur plusieurs centaines de lignes. Statistiquement, chacun des 30 entiers sera proposé au moins une fois. Il faudra ensuite être en mesure d'extraire seulement les valeurs uniques, dans l'ordre où elles apparaissent.

• Sélectionner la première cellule pour la génération aléatoire, soit la cellule H5,
• Taper le symbole = pour débuter le calcul,
• Saisir la fonction de génération aléatoire suivie d'une parenthèse, soit : ALEA.ENTRE.BORNES(,
• Saisir le chiffre 1 pour définir la borne inférieure,
• Taper un point-virgule (;) pour passer dans l'argument de la borne supérieure,
• Saisir le nombre 30 pour définir la limite supérieure,
• Fermer la parenthèse de la fonction Alea.Entre.Bornes,
• Valider le calcul par le raccourci CTRL + Entrée pour conserver la cellule active,
• Tirer la poignée de la cellule jusqu'en ligne 400,
• Puis remonter en haut de la feuille grâce au raccourci CTRL + Home (Flèche Nord Ouest), par exemple,

La formule que nous avons bâtie est la suivante :

=ALEA.ENTRE.BORNES(1; 30)

Comme nous l'avons dit plus haut, nous générons une quantité exagérée de valeurs aléatoires. L'objectif est de nous prémunir des redondances afin d'assurer la production, au moins une fois, de chacun des 30 nombres.

D'ailleurs, comme l'illustre le résultat de la capture ci-dessous, le nombre 7 est déjà répété 4 fois sur les 10 premières lignes. C'est une question de hasard. Et selon ce principe, les nombres produits n'apparaissent jamais dans un ordre logique mais toujours changeant. C'est ce phénomène que nous devons exploiter pour attribuer des numéros uniques et variants à chaque salarié.



Comme ces résultats sont le fruit d'une fonction Excel, ils sont dynamiques. Le recalcul se produit dès qu'un événement est intercepté sur le classeur.

• Sélectionner une cellule vide, par exemple I5,
• Enfoncer la touche F2 du clavier pour activer sa saisie,
• Puis, valider avec la touche Entrée,

Nous avons simulé la validation d'une saisie dans une cellule Excel. Il s'agit d'un événement qui a déclenché le recalcul de toutes les formules. Vous constatez en effet que tous les nombres aléatoires ont changé.

Ce mode de fonctionnement est gênant pour l'objectif à atteindre. Lorsque nous cliquerons sur le bouton Générer, nous souhaitons que les numéros soient attribués jusqu'à nouvel ordre. Or, la moindre action, comme nous venons de le voir, redistribuera les cartes. C'est pourquoi nous devons travailler sur une copie de ces numéros, déliée des formules.

Remarque : La touche F2 est un raccourci standard qui permet d'activer la saisie d'une zone de texte ou d'une cellule. L'autre méthode consiste à double cliquer directement sur la cellule. C'est un moyen intéressant pour visualiser et retravailler les formules notamment.

Macro Excel pour copier les données
Nous devons simuler les manipulations nécessaires pendant l'enregistrement d'une macro. Ces manipulations consistent à copier les nombres générés depuis la colonne H et à les coller, en valeurs dissociées des formules, en colonne I. Nous obtiendrons ainsi une vue figée à l'instant t des valeurs générées aléatoirement. Nous associerons ensuite cette macro au bouton Générer.

Pour déclencher l'enregistrement d'une macro, le ruban Développeur doit être disponible dans votre environnement. La formation pour débuter la programmation en VBA Excel rappelle, entre autres, comment l'afficher.

• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code, à gauche dans le ruban, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, la nommer Generer,

Les espaces sont interdits dans les noms de macros. Il est aussi préférable d'éviter l'emploi des accents.

• Cliquer sur le bouton Ok pour démarrer l'enregistrement des actions à simuler,

En phase d'enregistrement, il faut se concentrer sur les actions strictement nécessaires. Le cas échéant, à l'exécution, la macro reproduira toutes les manipulations, y compris celles qui n'étaient pas souhaitées.

• Cliquer sur le premier nombre aléatoire, soit la cellule H5,
• Faire défiler la feuille vers le bas jusqu'au dernier nombre de la liste,
• Puis, avec la touche MAJ enfoncée (Shift en anglais), cliquer sur le dernier nombre aléatoire,

Nous incluons ainsi toutes les cellules concernées dans la sélection.

• Réaliser le raccourci clavier CTRL + C ou cliquer sur le bouton Copier du ruban Accueil,
• Sélectionner la première cellule de la plage de destination, soit la cellule I5,
• Dans la section Presse-papiers sur la gauche du ruban Accueil, cliquer sur la flèche du bouton Coller,
• Dans la rubrique Coller des valeurs dans la liste, cliquer sur le bouton Mise en forme des valeurs et de la source, comme l'illustre la capture ci-dessous,
• Puis, sélectionner la cellule A1 de la feuille pour recentrer l'affichage,
• Enfin, dans le ruban Développeur, cliquer sur le bouton Arrêter l'enregistrement,

Comme vous l'avez remarqué, au moment de coller les données, les nombres aléatoires ont tous été regénérés. Nous avons donc dupliqué les anciennes valeurs. Chaque action est interprétée comme un évènement justifiant le recalcul. Il était donc primordial de figer cette vue.

La macro existe désormais mais elle n'est pas matérialisée. Ce n'est pas parce que le bouton porte le même nom, qu'ils sont reliés entre eux.

• Cliquer avec le bouton droit de la souris sur le bouton Générer,
• Dans le menu contextuel qui apparaît, choisir Affecter une macro,
• Dans la boîte de dialogue qui suit, sélectionner la macro Generer dans la liste,
• Puis, cliquer sur le bouton Ok pour confirmer l'association,

Désormais, le bouton et la macro sont liés. Un clic sur le bouton déclenche les actions que la macro a enregistrées.

• Cliquer sur une cellule vide de la feuille afin de désactiver la sélection du bouton,
• Puis, cliquer sur le bouton Générer,

Les nombres aléatoires sont de nouveau générés, du fait de l'événement occasionné. Dans le même temps, la macro a restitué les anciennes valeurs en colonne I.

Repérer les nombres aléatoires uniques
En prévision des calculs d'extraction, nous devons marquer chaque nombre aléatoire non encore proposé, d'un numéro incrémenté. Ce repérage doit se faire en colonne J, à partir de la cellule J5. Ces valeurs incrémentées serviront ensuite de repères d'extraction, pour enfin attribuer des ordres de passage uniques, dans le tableau source.

Nous l'avons dit à plusieurs reprises, tous les calculs sont dynamiques dans Excel. Un clic sur le bouton Générer extraira les nouvelles valeurs aléatoires. De fait, les calculs de repérage liés se mettront à jour. Et par voie de conséquence, les extractions s'adapteront pour distribuer de nouveaux numéros.

Il s'agit de tester si la valeur en cours a déjà été proposée, dans une plage de cellules qui doit progresser en même temps que le calcul est répliqué. En effet, si nous réalisions directement ce décompte sur l'intégralité de la plage, chaque valeur serait déjà générée de nombreuses fois. Ce décompte doit être effectué grâce à la fonction Excel de dénombrement Nb.Si. Sa syntaxe est la suivante :

=Nb.Si(Plage_de_cellules ; Critère_à_compter)

Le critère n'est autre que le nombre aléatoire à compter. Si cette fonction retourne un résultat supérieur à zéro, cela signifie que la valeur a déjà été proposée et ne doit plus être considérée. Nous devons donc réaliser ce test dans une fonction Excel conditionnelle Si. Sa syntaxe est la suivante :

=Si(Critère_à_vérifier ; Action_Alors ; Action_Sinon)

Le critère à vérifier consiste à savoir si le décompte du nombre aléatoire est positif. Alors (Action_Alors), nous ne devons pas repérer la valeur. Sinon (Action_Sinon), nous devons repérer la valeur par un numéro incrémenté.

• Cliquer sur la première cellule du calcul de repérage, soit J5,
• Taper le symbole = pour commencer l'écriture de la formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Saisir la fonction de dénombrement suivie d'une parenthèse, soit Nb.Si(,
• Saisir les références de la cellule I4, soit I4,
• Taper le symbole deux points (:) suivi de la même référence, ce qui donne I4:I4,
• Sélectionner seulement la première des deux références et enfoncer la touche F4 du clavier, ce qui donne : $I$4:I4,

Nous figeons ainsi la borne supérieure de la plage de cellules, en atteste la présence des dollars encadrant ses références. Ainsi, lorsque nous répliquerons le calcul, la plage du critère à dénombrer, grandira au fur et à mesure des lignes passées en revue.

• Cliquer à la toute fin de la formule en cours pour replacer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument du critère à dénombrer,
• Sélectionner la première valeur aléatoire, soit la cellule I5,
• Fermer la parenthèse de la fonction Nb.Si,
• Taper le symbole supérieur suivi du chiffre 0 pour la condition à vérifier, soit >0,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir deux guillemets pour indiquer de conserver la cellule vide, soit '',

En effet, si la fonction de dénombrement retourne un résultat positif, cela signifie que le nombre aléatoire a déjà été repéré. Dans ce cas, les deux guillemets permettent d'insérer un texte vide, pour une cellule vierge.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir la fonction pour extraire le plus grand nombre suivie d'une parenthèse, soit Max(,
• Saisir les références de la plage de cellules suivante : J4:J4,
• Comme précédemment, figer seulement la première des deux, ce qui donne : $J$4:J4,
• Fermer la parenthèse de la fonction Max,
• Ajouter une unité, soit : +1,
• Fermer la parenthèse de la fonction Si,
• Valider le calcul par le raccourci CTRL + Entrée pour conserver la cellule active.

Dans la branche Sinon de la fonction Si, la fonction Max permet de repérer le nombre le plus grand déjà incrémenté. Comme nous la faisons agir sur une plage de qui progresse en même temps que le calcul, du fait de son incrémentation (+1), elle augmente l'indice de repérage à chaque fois qu'un nouveau nombre unique est repéré. Le premier nombre aléatoire est d'ailleurs tout de suite marqué par le chiffre 1. Comme il s'agit du premier, il n'a nécessairement pas eu l'occasion d'être déjà proposé.

• Double cliquer sur la poignée de la cellule J5 pour répliquer la formule sur tout le tableau,

Comme vous le constatez, les repères incrémentés se positionnement parfaitement en regard de chaque nombre aléatoire qui n'a pas déjà été généré. S'il a déjà été suggéré, la cellule reste vide, selon l'indication donnée dans la branche Alors de la fonction Si.

• Cliquer sur le bouton Générer pour réaliser une simulation,

Tous les nombres aléatoires sont recalculés en colonne H. Toutes leurs anciennes valeurs sont répliquées en colonne I. Tous les repérages incrémentés sont recalculés en colonne J. Donc, lorsque nous produirons l'extraction dans la colonne E du tableau source, tous les ordres de passage seront redéfinis, et seulement au clic sur le bouton. Notre calcul de repérage est effectivement bâti sur la colonne I. Cette dernière ne réagit qu'au clic sur le bouton grâce à la macro n'ayant prélevé que les valeurs.

La formule que nous avons bâtie est la suivante :

=SI(NB.SI($I$4:I4;I5)>0;'';MAX($J$4:J4)+1)

Extraire tous les nombres aléatoires uniques
Nous devons désormais exploiter les fonctions Index et Equiv. Il s'agit d'effectuer la recherche de chaque numéro incrémenté en colonne J pour rapatrier le nombre aléatoire correspondant, issu de la colonne I. Leurs syntaxes sont les suivantes :

=Index(Tableau_de_recherche ; Indice_de_ligne ; Indice_de_colonne)
=Equiv(Valeur_cherchée ; Colonne_de_recherche ; 0)

La fonction Index extrait une information de base de données (Tableau_de_recherche) située au croisement d'une ligne et d'une colonne. Le tableau de recherche est représenté par les colonnes I et J. Le numéro de colonne est connu. Par rapport à la sélection, le nombre aléatoire à extraire se trouve en première colonne (Indice 1). L'indice de ligne en revanche, dépend de l'emplacement de chaque numéro incrémenté à trouver. C'est là qu'intervient la fonction Equiv. Elle retourne dynamiquement l'indice de ligne d'une valeur cherchée. Et pour qu'elle puisse chercher un numéro qui s'incrémente en même temps que le calcul est répliqué, nous devons exploiter la fonction Excel Ligne. Cette dernière retourne l'indice de ligne d'une cellule passée en paramètre, par exemple 1 pour A1. En déplaçant le calcul vers le bas, elle retournera 2 pour A2 et ainsi de suite. Cette recherche doit être faite dans la colonne J. Le dernier argument de la fonction Equiv est fixé à 0 pour réaliser une recherche selon une correspondance exacte. Les résultats doivent être importés dans la colonne E du tableau des salariés.

• Sélectionner la première cellule du calcul, soit E5,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction d'erreur suivie d'une parenthèse, soit SiErreur(,

En effet, les fonctions d'extraction, lorsqu'elles ne trouvent pas les éléments cherchés, retournent un message d'erreur. Nous souhaitons gérer ces potentielles exceptions.

• Saisir la fonction d'extraction suivie d'une parenthèse, soit Index(,
• Saisir les références de la matrice de recherche, soit I:J,
• Taper un point-virgule (;) pour passer dans l'argument du numéro de ligne,
• Saisir la fonction retournant l'indice de ligne d'une recherche, suivie d'une parenthèse, soit Equiv(,
• Saisir la fonction renvoyant l'indice de ligne d'une cellule, suivie d'une parenthèse, soit Ligne(,
• Désigner une cellule de la première ligne de la feuille, par exemple A1,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne J toute entière, soit J:J,
• Taper un point-virgule (;) suividu chiffre 0 pour demander une correspondance exacte, soit ;0,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne pour la fonction Index,
• Saisir le chiffre 1 pour désigner la première des deux,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du calcul pour le répliquer sur la hauteur du tableau,

Comme vous le remarquez, lesnombres aléatoires uniques sont parfaitement extraits et attribués à chacun des employés. Il n'y a aucune redondance grâce au calcul intermédiaire ayant permis de repérer chaque nouvelle valeur par un numéro incrémenté. Et comme l'extraction que nous venons de réaliser effectue sa recherche sur ces numéros, les valeurs affectées sont uniques et apparaissent dans un ordre aléatoire.

La formule que nous avons construite est la suivante :

=SIERREUR(INDEX(I:J; EQUIV(LIGNE(A1); J:J;0); 1); '')



Dans la formation suivante, nous pourrons exploiter ces résultats d'extraction pour réaliser des tris aléatoires sur des tableaux Excel. Le plus dur est fait. Il ne reste plus qu'à organiser les données sur la colonne Ordre de passage qui varie à chaque demande.

• Cliquer une dernière fois sur le bouton Générer,

Comme les anciennes valeurs aléatoires sont produites, les calculs de repérage se réactualisent. De fait, l'extraction importe les nouveaux nombres aléatoires pointés, dans l'ordre où ils apparaissent. Chaque salarié se voit affecté d'un nouveau numéro unique.