XL 2021 Automatisation de la randomisation des cellules d'une colonne

[MATLEA]

Bonjour à tous,

Dans le fichier matlea3 joint j'ai créé le tableau 1 avec des cellules C6 à C11 des patronymes. Mon but est de les reclasser de manière aléatoire. Manuellement (tableau 2) je rentre dans la cellule D17 =alea() ce qui me donne une valeur numérique. Dans le tableau 3 j'ai incrémenté sur les 6 cellules en descendant le point vert à partir du coin gauche de la cellule D17. Dans le tableau 4 j'ai randomisé en sélectionnant ensemble les cellules C et D 41 à 46 puis en cliquant sur données, trier puis colonne D dans trier par puis OK ce qui me donne le résultat visible dans le tableau 4. J'ai fait un tableau 5 en sélectionnant et copiant uniquement les cellules C50 à C57.
Ma question est de savoir s'il serait possible à partir d'un tableau 1 d'automatiser les différentes étapes pour arriver automatiquement aux résultats du tableau 4 et même 5.

Merci d'avance.

Matlea

 

[fanfan38]

Bonjour et bienvenu sur le forum
si j'ai tout compris ci joint ma solution

A+ François

 

[JHA]

Bonjour à tous,

A essayer par formule ou avec un peu de VBA

JHA

 

[Dranreb]

Bonjour.
Vous parlez d'automatisation. Cela implique une macro VBA.
Mais dans ce cas il est inutile d'utiliser la fonction ALEAT d'Excel dans des formules, la fonction Rnd de VBA faisant l'affaire pour peu qu'une instruction Randomize ait été exécutée soit de façon à déterminer délibérément du début une certaine série bien précise soit au contraire d'en assurer une originale. Le tri aussi est inutile, le Mélange de Fisher-Yates faisant l'affaire.
La fonction perso Hasard de ces classeurs peut reproduire un nom de rang aléatoire dépendant d'une graine et du numéro de ligne.

 

[MATLEA]

Merci DRANREB

Votre solution semble très efficace mais j'ai du mal à l'appliquer à ma problématique;
Dois-je saisir graine et dans quelle cellule? Dans votre tableau dans la cellule E3 il y a jeudi et une horloge mais E2et E3 semblent liées. Puis dans chacune des cellules du tableau 2 on trouve la formule suivante =@INDEX(C$5:C$10;@Hasard(LIGNE()-14;LIGNES(C$5:C$10);$E$3))
MAIS
dans D8 on lit une formule commençant par une flèche ="⟵ =INDEX(C$5:C$10;Hasard("&LIGNE()-14&";"&LIGNES(C$5:C$10)&";"&$E$3&"))"
c'est incrémenté dans D9, D10 etc
Mais ce qu'on lit dans le tableau au niveau de la ligne D9 ne correspond pas. Idem pour D10, D11, D12 etc
J'ai vraiment besoin de votre aide de façon pratique pour appliquer vos solutions à mes problématiques.

MERCI

 

[Dranreb]

C'est mieux de définir la graine pour stabiliser le tirage, mais il n'est pas obligatoire qu'elle soit dans une cellule. Sa valeur peut aussi être la référence d'un nom, qui ne se réfère donc alors pas à une cellule. La colonne D montre juste la formule appliquée après remplacement des arguments par leurs valeurs.
Je le rejoint parce qu'il y a aussi une autre façon d'écrire la formule.
Regardez aussi la feuille "Fonction Hasard" du ListeAléat.xlsm: c'est un peu un tuto qui calcule et montre comment rédiger la formule selon ce qu'on veut bien fournir à la fonction et ce qu'on en attend.

 

[MATLEA]

Bonjour à tous,

A essayer par formule ou avec un peu de VBA

JHA

Bonsoir JHA

Par formule ca marche avec mes fichiers mais est-il possible de rendre automatique l'étape de =alea() puis d'incrémentation sur la colonne.

Pour le second fichier avec un peu de VBA j'ai quelques problèmes;

Dans la macro voir la copie ci-dessous), à quoi correspondent les valeurs a6:a11 (pour 6 et 11 ce sont les numéros de ligne de la 1ère et dernière cellule mais pourquoi a et en dessous pourquoi b dans Range("b6:b11"). Pour mes fichiers dois-je faire une macro similaire en remplaçant seulement 6 et11 par mes numéros de ligne première et dernière?

Dans chaque cellule du tableau 2 où doivent apparaitre les noms dans un nouvel ordre on trouve des formules du type =@INDEX($D$6:$D$11;RANG($B6;$B$6:$B$11;1)). De nouveau 6 et 11 sont OK pour moi et D doit correspondre à la colonne dans laquelle se trouve le tableau 1. Mais pourquoi B apres RANG. J'ai essayé différentes combinaisons dans mes autres fichiers mais sans résultat probable.
Comment donc combiner une macro et les formules dans les cellules pour que puisse se faire automatiquement la randomisation des cellules dans le tableau 2.
Merci pour les conseils.
Matlea

MACRO


Application.ScreenUpdating = False

Range("a6:a11").Select

Selection.Copy

Range("b6:b11").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Application.ScreenUpdating = True

Range("d13").Select

End Sub

 

[MATLEA]

Bonsoir JHA

Par formule ca marche avec mes fichiers mais est-il possible de rendre automatique l'étape de =alea() puis d'incrémentation sur la colonne.

Pour le second fichier avec un peu de VBA j'ai quelques problèmes;

Dans la macro voir la copie ci-dessous), à quoi correspondent les valeurs a6:a11 (pour 6 et 11 ce sont les numéros de ligne de la 1ère et dernière cellule mais pourquoi a et en dessous pourquoi b dans Range("b6:b11"). Pour mes fichiers dois-je faire une macro similaire en remplaçant seulement 6 et11 par mes numéros de ligne première et dernière?

Dans chaque cellule du tableau 2 où doivent apparaitre les noms dans un nouvel ordre on trouve des formules du type =@INDEX($D$6:$D$11;RANG($B6;$B$6:$B$11;1)). De nouveau 6 et 11 sont OK pour moi et D doit correspondre à la colonne dans laquelle se trouve le tableau 1. Mais pourquoi B apres RANG. J'ai essayé différentes combinaisons dans mes autres fichiers mais sans résultat probable.
Comment donc combiner une macro et les formules dans les cellules pour que puisse se faire automatiquement la randomisation des cellules dans le tableau 2.
Merci pour les conseils.
Matlea

MACRO


Application.ScreenUpdating = False

Range("a6:a11").Select

Selection.Copy

Range("b6:b11").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Application.ScreenUpdating = True

Range("d13").Select

End Sub

Bonjour JHA

J'ai une suggestion et deux questions.
Avec le fichier formule je crois avoir trouvé comment automatisé les étapes. Après avoir constitué le tableau 1 en laissant les cellules vides (sans les patronymes) Je prépare le tableau 2 en commençant par la colonne alea() contenant les 6 cellules qui ont donc ces valeurs de type 0,0123 etc Puis je prépare la colonne du tableau 2 qui accueillera les patronymes après randomisation en entrant dans chaque cellule la formule de type =@INDEX($B$3:$B$50;RANG($Z3;$Z$3:$Z$50;1)).
Ce n'est qu'à cette étape que je finalise le process en introduisant les patronymes dans le tableau 1 qui à chaque saisie va générer sa copie dans le tableau 2 à une position aléatoire.
Ceci marche très bien pour un nombre de cellules du tableau 2 alea() )contenant les valeurs de type 0,0123) égal au nombre de cellules du tableau 1. Dans l'exemple 6.
Mon objectif est de préparer une matrice pour être utilisée sans modification avec des tableaux 1 contenant un nombre variable de lignes. Si j'ai un nombre de lignes dans le tableau 1 inférieur à celui du tableau 2 la randomisation introduit au hasard des "0" sur certaines lignes ce que je voudrais éviter.
Voir le fichier joint Matlea4jha.
Est-il aussi possible d'empêcher les valeurs alea d'évoluer, j'ai essayé de les copier mais elles changent aussi dans la nouvelle colonne.
Merci

 

[Dranreb]

Bonjour.
Pas de commentaire sur ma réponse et mon dernier classeur joint ?

 

[MATLEA]

Bonjour JHA

J'ai une suggestion et deux questions.
Avec le fichier formule je crois avoir trouvé comment automatisé les étapes. Après avoir constitué le tableau 1 en laissant les cellules vides (sans les patronymes) Je prépare le tableau 2 en commençant par la colonne alea() contenant les 6 cellules qui ont donc ces valeurs de type 0,0123 etc Puis je prépare la colonne du tableau 2 qui accueillera les patronymes après randomisation en entrant dans chaque cellule la formule de type =@INDEX($B$3:$B$50;RANG($Z3;$Z$3:$Z$50;1)).
Ce n'est qu'à cette étape que je finalise le process en introduisant les patronymes dans le tableau 1 qui à chaque saisie va générer sa copie dans le tableau 2 à une position aléatoire.
Ceci marche très bien pour un nombre de cellules du tableau 2 alea() )contenant les valeurs de type 0,0123) égal au nombre de cellules du tableau 1. Dans l'exemple 6.
Mon objectif est de préparer une matrice pour être utilisée sans modification avec des tableaux 1 contenant un nombre variable de lignes. Si j'ai un nombre de lignes dans le tableau 1 inférieur à celui du tableau 2 la randomisation introduit au hasard des "0" sur certaines lignes ce que je voudrais éviter.
Voir le fichier joint Matlea4jha.
Est-il aussi possible d'empêcher les valeurs alea d'évoluer, j'ai essayé de les copier mais elles changent aussi dans la nouvelle colonne.
Merci

J'y travaille mais je ne suis pas un expert

 

[MATLEA]

Bonjour.
Pas de commentaire sur ma réponse et mon dernier classeur joint ?

Dranreb,
Je ne suis pas un expert et j'ai du mal. Même quand je fais un copier coller de tout le tableau dans un nouveau fichier excel les cellules ne se remplissent pas comme elles devraient. Désolé.

Bonjour.
Pas de commentaire sur ma réponse et mon dernier classeur joint ?

 

[JHA]

Bonjour à tous,

Je ne comprends pas tes commentaires, peux-tu joindre un fichier avec les explications?

JHA

 

[MATLEA]

Bonjour à tous,

Je ne comprends pas tes commentaires, peux-tu joindre un fichier avec les explications?

JHA

OK je le fais ce soir

 

[Dranreb]

quand je fais un copier coller de tout le tableau dans un nouveau fichier excel les cellules ne se remplissent pas comme elles devraient.

Il faut bien évidemment glisser/déplacer le module MFctHasard vers le projet VBA de votre classeur pour que la fonction perso soit définie.

 

[MATLEA]

OK je le fais ce soir

OK je le fais ce soir

voir d'abord matlea VBA JHA