Trier aléatoirement les tableaux Excel sans code VBA

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


Sujets que vous pourriez aussi aimer :


Trier les tableaux Excel aléatoirement

Dans cette formation Excel, nous proposons de montrer comment il est possible de trier aléatoirement les tableaux, sans l'intervention du code VBA. Pour ce faire, nous choisissons d'exploiter les travaux de la formation précédente, dans laquelle nous avions réussi à générer des nombres aléatoires uniques, sur une plage de valeurs définie. Nous avions ainsi pu attribuer un numéro unique à chacun des salariés ou candidats recensés dans un petit tableau de données. Ce sont des formules d'extraction qui avaient permis d'importer chacun de ces nombres selon un calcul intermédiaire de repérage. Le plus dur est fait. Il suffit de trier sur ces valeurs aléatoires. Mais elles doivent être détachées des formules car tout calcul est dynamique dans Excel et donc, toute action redistribuerait ces valeurs.



La capture ci-dessus illustre le résultat de l'application finalisée. Des ordres de passage aléatoires sont calculés dans l'avant dernière colonne. Ces données sont restituées dans la dernière colonne, détachées de leurs formules. Un tri croissant est alors enclenché au clic sur un bouton.

Sources et présentation de la problématique
Pour concentrer l'étude sur les manipulations essentielles, nous proposons de récupérer les travaux précédents.

• Télécharger le classeur trier-aleatoirement-tableaux.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. Seule la première nous intéresse ici. Il s'agit de la feuille nommée Concaténer.

• Cliquer sur le bouton Générer situé en haut de la feuille entre les colonnes H et I,

Un traitement s'enclenche comme vous le constatez. Il s'agit des formules que nous avons bâties lors de la formation précédente. Un clic sur le bouton Générer déclenche un événement qui impose le recalcul de toutes les formules.

Une grande quantité de nombres aléatoires, compris entre 1 et 30, est produite en colonne H, selon la formule suivante :

=ALEA.ENTRE.BORNES(1; 30)

La macro associée au bouton Générer les restitue dans la colonne I, dissociés de leurs formules, par collage spécial. Ces copies ne sont plus modifiées à chaque recalcul. Seul le bouton Générer les actualise. Un calcul de repérage, selon un numéro incrémenté, est effectué en colonne J, selon la formule suivante :

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

Il consiste à identifier chaque nombre aléatoire qui n'a pas encore été proposé. Il permet donc d'identifier toutes les valeurs aléatoires uniques à exploiter dans le tableau source des salariés.

Et précisément en colonne E, ces numéros sont extraits, dans l'ordre où ils ont été générés, grâce aux fonctions de recherche, selon la formule suivante :

=SIERREUR(INDEX(H:J; EQUIV(LIGNE(H1); J:J;0);2); '')

Cette recherche est réalisée sur ces numéros incrémentés de repérage. Lorsqu'ils sont trouvés, c'est le nombre aléatoire correspondant qui est récupéré.

Copier les valeurs de formules par Macro
Le tri doit être réalisé sur ordre et plus précisément, au clic sur le bouton Trier. Il est positionné à droite du bouton Générer sur la feuille. C'est donc une macro associée à ce bouton qui doit réaliser les actions. Nous pourrions très bien ordonner ce tri sur la colonne Ordre de passage. Il fonctionnerait certes. Mais comme un calcul d'extraction des numéros existe, le salarié remonté en tout début de tableau, se verrait affecté d'un numéro réajusté par le calcul. Et ce numéro ne correspondrait plus à sa position.

Nous proposons donc, comme nous l'avons fait pour les nombres aléatoires, de réaliser une copie spéciale de ces valeurs extraites, en colonne F. Ces numéros ne seront pas réajustés avec le tri. Ils resteront cohérents. Il s'agit donc d'enregistrer une macro qui en profitera pour organiser le tri croissant dans la foulée. Nous avons besoin du ruban Développeur. 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 du ruban, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, la nommer Trier,

• Cliquer sur le bouton Ok pour démarrer l'enregistrement,
• Sélectionner tous les nombres aléatoires, soit la plage de cellules E5:E34,
• La copier à l'aide du raccourci CTRL + C par exemple,
• Sélectionner la première cellule de la plage de destination, soit F5,
• Cliquer sur l'onglet Accueil en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche du ruban, dans la section Presse-papiers, cliquer sur la flèche du bouton Coller,
• Dans la liste, cliquer sur le bouton Mise en forme des valeurs et de la source,

Nous réalisons ainsi une copie détachée des nombres aléatoires, sans leurs formules.

• Sélectionner l'une des cellules du tableau, par exemple C5,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Trier et filtrer, cliquer sur le bouton Trier,
• Dans la boîte de dialogue qui suit, définir le tri sur la colonne Ordre pour tri,

Par défaut, les données seront organisées du plus petit nombre au plus grand.

• Valider ce réglage en cliquant sur le bouton Ok de la boîte de dialogue,

Vous remarquez que les salariés se sont effectivement classés en ordre croissant, selon le numéro aléatoire qui leur avait été attribué.

• 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 Arrêter l'enregistrement,

La macro existe désormais, mais seulement à l'état de code VBA à ce stade. Elle permet de réaliser un tri croissant du tableau sur les ordres de passage restitués. Nous devons la lier au bouton Trier pour la matérialiser.

• Cliquer avec le bouton droit de la souris sur le bouton Trier,
• Dans le menu contextuel, choisir Affecter une macro,

• Dans la liste de la boîte de dialogue qui suit, sélectionner la macro Trier,
• Puis, cliquer sur Ok pour établir la liaison,
• Sélectionner une cellule de la feuille pour désactiver la sélection du bouton,
• Puis, cliquer sur le bouton Trier,

Comme les salariés ont été réorganisés par le tri croissant précédent, le calcul dans la colonne Ordre de passage a réattribué un nouveau nombre aléatoire à chacun. Comme la macro Trier réalise un tri croissant sur ces valeurs redistribuées et restituées dans la colonne F, le tri change. Ainsi, à chaque clic, un ordre de passage aléatoire est proposé à chaque employé.

Mais, comme vous l'avez sans doute remarqué, les nombres aléatoires en colonne H n'ont pas été regénérés. Dans la procédure, il conviendrait d'abord de cliquer sur le bouton Générer et ensuite sur le bouton Trier. Ainsi, les cartes seraient totalement rebattues.

Voilà donc comment réaliser un tri aléatoire sur des tableaux Excel, sans l'intervention du code Visual Basic. L'essentiel du travail réside dans les formules que nous avions bâties lors de la formation précédente. Elles avaient permis d'extraire la suite des valeurs aléatoires uniques. Il ne restait plus qu'à les utiliser pour définir l'ordre de tri sur ces nombres.