MacOS Générer ordre de passage aléatoire sans doublon avec intervalle donné
Bonjour,
J'aurais besoin de générer une liste de n° aléatoire, correspondant à un ordre de passage, compris entre 1 et un nombre à définir dans une cellule (car mon nombre d'étudiant varie d'une année à une autre).
J'ai vu ce sujet-là qui correspond pile poil à ce que je désire, mais voilà, je travaille sous macOS, donc Set Tirag = CreateObject("Scripting.Dictionary") ne fonctionne pas, et j'ai cette erreur :
Je ne sais pas comment contourner ce souci sous macOS.
J'ai Office 365, donc Excel est en sa dernière version accessible via l'abonnement O365.
J'aimerais aussi améliorer un peu la macro pour qu'elle me génère donc la liste d'ordre de passage en fonction de la liste d'étudiants que j'aurais collé dans le tableau.
Dans le fichier excel joint en fin de message, j'ai deux tableaux :
- celui de gauche pour lequel je colle ma liste d'étudiants avec leur groupe, trié par nom.
- celui de droite que j'aimerais être rempli automatiquement.
La macro devrait récupérer le nombre de ligne du tableau1, et générer la liste des ordres de passage dans la dernière colonne de ce même tableau.
J'aimerais que par la même occasion, elle copie les ordres de passages et les noms/groupes associés dans le tableau2 et qu'elle en fasse le tri par ordre de passage (bon là, je pourrais m'en sortir avec la fonction d'enregistrement de macro).
Le tout avec un bouton pour lancer la macro.
Voilà le fichier excel que j'aimerais utiliser :
Merci beaucoup d'avance pour votre aide.
Bonne journée à toutes et à tous.
Bonjour,
1. Si vous êtes sous MAC, pensez à mettre votre profil excel à jour dans votre compte utilisateur.
2. Le MAC ne prend pas en compte les ActiveX... en voici quelques-unes --> scripting dictionnary, cases à cocher type activeX, Rowssource, ...
Il est donc normal que vous ayez cette erreur.
3. Pourquoi ne pas faire automatique le report dans le tableau de droite à chaque entrée de l'ordre de passage du tableau de gauche. Cela vous évite le bouton. Possible cela ?
Comment déterminez-vous l'ordre de passage dans le tableau de gauche ?
Autre Idée : Pourquoi ne pas utiliser uniquement le tableau de gauche. Une fois les élèves encodés, il suffirait de faire un code qui attribue l'ordre de manière aléatoire dans la colonne "ordre de passage". Faire un tri éventuel par ordre croissant si vous le désirez. cela simplifie nettement à mon avis
Remarque :
- vous avez choisi les tableaux structurés et dans ce cas, vous ne devez jamais avoir de lignes sans données
- Même remarque que je viens d'écrire sur un autre fil. Ne mettez pas de la couleur blanche pour supprimer le quadrillage. cela peut alourdir le fichier inutilement. Vous disposez d'une fonctionnalité Quadrillage dans le menu Affichage qu'il suffit de décocher. C'est fou ce que je vois cette manière de faire dans les fichiers
Bonsoir,
Pour le dernier point, les cellules sur fond blanc, je ne savais pas qu'on pouvait virer le quadrillage ! Merci pour l'astuce. J'espère que ce réglage n'est pas partagé par tous les classeurs ouverts futurs ^^
Si vous êtes sous MAC, pensez à mettre votre profil excel à jour dans votre compte utilisateur.
Je ne peux pas changer en étant exhaustif, alors j'ai mis : MAC & Win10 MS365
J'ai mis dans ma signature plus de détails.
Je savais que c'était parce que j'étais sous macOS que l'activeX ne fonctionnait pas.
Ma question était de comment faire une solution universelle...
Je rempli moi-même dans le tableau de gauche les noms et groupes.
Ensuite, c'est la macro qui doit remplir le reste.
Là j'ai réussi à générer manuellement un ordre de passage en suivant une méthode trouvée sur un autre forum (je n'ai pas encore assez de message à mon actif pour poster un lien... :/ ) :
Sans macro sur 3 colonnes
Colonne A (de 1 à 200) = Alea()
Colonne B (de 1 à 200) les nombres de 0 à 199
Cellule C1 :
=RECHERCHEV(PETITE.VALEUR($A$1:$A$200;LIGNE());$A$1:$B$200;2;0)
et recopie incrémentée de C1 à C200
F9 pour nouveau tirage
J'ai adapté à mon faible nombre, ici 19 pour l'exemple :
=RECHERCHEV(PETITE.VALEUR($A$1:$A$19;LIGNE());$A$1:$B$19;2;0)Je cherche actuellement à l'automatiser via une macro pour qu'elle gère le nombre variable d'étudiants.
J'ai réussi à faire ceci avec la macro
- à compter combien j'ai de noms d'étudiants dans mon tableau 1 (celui de gauche) dans la feuille
Ordre de passageet à afficher un message me disant combien j'avais d'étudiants ;
Dans la feuille Generateur, grace à une boucle for, j'ai réussi à remplir :
- la première colonne d'un nombre de valeur aléatoire correspondant au nombre d'étudiant déterminé précédemment ;
- la deuxième colonne de nombre entier commençant à 1 et s'incrémentant de 1 jusqu'au nombre d'étudiant ;
Sauf que je n'arrive pas à faire remplir la colonne 3 avec la formule construite avec le nombre d'étudiant de ma liste :"=VLOOKUP(SMALL($A$1:$A$" & nb_etudiants & ";ROW());$A$1:$B$" & nb_etudiants & ";2;0)"
Que je tente via la construction par une variable séparée comme dans le code ci-dessous, ou bien via une construction au moment de remplir la cellule :
Range("C" & i).Formula = "=VLOOKUP(SMALL($A$1:$A$" & nb_etudiants & ";ROW());$A$1:$B$" & nb_etudiants & ";2;0)"
PS : j'ai utilisé le site fr.excelfunctions.eu pour connaître les correspondances entre les noms de fonctions FR et EN car dans le .FORMULA, il faut l'appellation EN.
Bon, après moult recherche, je suis tombé sur le traducteur de formule de excel-pratique
Bref, en regardant le résultat pour voir si je ne m'étais pas planté, j'ai vu une subtile différence entre ma formule et celle du traducteur : le caractère du séparateur !
Un ; en FR et une , en EN...
Purée, on ne pourrait pas avoir une uniformisation là-dessus... ??
Voilà la formule EN qui fonctionne :Range("C" & i).Formula = "=VLOOKUP(SMALL($A$1:$A$" & nb_etudiants & ",ROW()),$A$1:$B$" & nb_etudiants & ",2,0)"
J'ai donc actuellement pu générer mes ordres de passage aléatoires.
Ce soir j'en suis resté au remplissage du tableau1 :
La feuille Generateur, juste avant la recopie par la macro de la colonne C avec la dernière ligne :
La feuille Ordre de passage, Tableau1, après la copie :
Donc pour le moment, c'est fonctionnel ^^
Peut-être pas optimal, mais fonctionnel comme je le souhaite...
Demain, je m'occupe de faire la copie vers l'autre tableau.
Le code VBA actuel :
Option Explicit ' On force à déclarer les variables
Option Base 1 ' les tableaux commenceront à l'indice 1
Public Const SHEET_Ordre As String = "Ordre de passage" ' Nom de la feuille ou se placer
Public Const SHEET_Gen As String = "Generateur"
Public Const CELL_1er_etudiant As String = "A4" ' Cellule avec le nom du premier etudiant
Public Const NOM_Tableau_Ordre As String = "Tableau1_Tri_Noms"
Sub Generer_Ordre_de_Passage()
' Fonction pour generer les ordres de passage :
' La macro doit recuperer le nombre de ligne du tableau1
' puis generer la liste des ordres de passage dans la derniere colonne de ce meme tableau
' Par la meme occasion, elle copie les ordres de passages et les noms/groupes associes dans le tableau2
' et qu'elle en fait le tri par ordre de passage.
Dim nb_etudiants As Integer
Dim ws_ordre, ws_gen As Worksheet
Dim R As Range
Set ws_ordre = Worksheets(SHEET_Ordre)
ws_ordre.Activate
'Range(CELL_1er_etudiant).Select
' On sélection la cellule A4, soit le premier etuditant, pour ne pas avoir tout le tableau sélectionné
Set R = Range(CELL_1er_etudiant, Range(CELL_1er_etudiant).End(xlDown))
nb_etudiants = R.Count
'MsgBox "Il y a " & nb_etudiants & " étudiants à évaluer."
Set ws_gen = Worksheets(SHEET_Gen)
ws_gen.Activate
Range("A1:C" & nb_etudiants).Clear
Dim formule_ordre As String
'formule_ordre = "=VLOOKUP(SMALL($A$1:$A$" & nb_etudiants & ",ROW()),$A$1:$B$" & nb_etudiants & ",2,0)"
' Pour 19 etudiants on aura :
' "=VLOOKUP(SMALL($A$1:$A$19;ROW());$A$1:$B$19;2;0)"
'
' VF de la formule : =RECHERCHEV(PETITE.VALEUR($A$1:$A$19;LIGNE());$A$1:$B$19;2;0)
Dim i As Integer
For i = 1 To nb_etudiants
Range("A" & i).Formula = "=RAND()" ' En Fr la fonction serait "=ALEA()"
Range("B" & i).Value = i
Range("C" & i).Formula = "=VLOOKUP(SMALL($A$1:$A$" & nb_etudiants & ",ROW()),$A$1:$B$" & nb_etudiants & ",2,0)"
Next
Set R = Range("C1:C" & nb_etudiants)
Dim val_C_max As Integer
val_C_max = 4 + nb_etudiants - 1
Sheets(SHEET_Ordre).Range("C4:C" & val_C_max).Value = R.Value
Set ws_ordre = Worksheets(SHEET_Ordre)
ws_ordre.Activate
End SubLe fichier excel :
Je ne peux pas changer en étant exhaustif, alors j'ai mis : MAC & Win10 MS365
J'ai mis dans ma signature plus de détails.
Bah c'est bien comme vous avez fait
Si je comprends vous mettez l'ordre de passage dans le tableau 1 via un code ?
Si oui, je ne vois pas l'utilité du tableau de droite.
Vu votre avancement vous voulez quoi comme aide en fait au final ?
Je vous avais préparé un code en attendant votre retour sur mon post précédent et que j'ai adapté puisqu'au vu de votre post je pense comprendre que l'ordre de passage en colonne 3 doit être fait par code.
Ce que vous avez fait est un peu complexe. Vous avez juste besoin du code ci-dessous :
Sub test()
Dim a()'déclaration variable de type tableau
Dim i As Byte, q As Byte
With ActiveSheet.ListObjects("Tableau1_Tri_Noms") '
.ListColumns(3).DataBodyRange.ClearContents 'suppression des données en colonne 3
ReDim a(.ListRows.Count - 1) 'redimensionne le tableau Tri_noms sur base du nombre de lignes du tableau
For i = 0 To UBound(a) 'boucle depuis 0 jusque limite supérieure de a (correspond au nombre de lignes du tableau structuré - 1 car début par i = 0)
a(i) = .DataBodyRange(i + 1, 1) 'attribue le nom des élèves à "a" pour chaque variable i
Next i
For i = 1 To UBound(a) + 1 'boucle sur toutes les valeurs de "a"
q = Application.RandBetween(0, UBound(a) - i + 1) 'attribue un numéro d'ordre aléatoire à chaque valeur de "a"
lig = WorksheetFunction.Match(a(q), .ListColumns(1).DataBodyRange, 0) 'renvoie la ligne de l'élève a(q) trouvée en colonne 1 du tableau structure
.DataBodyRange(lig, 3) = i 'on met en colonne 3 du tableau structuré la valeur de i pour la ligne trouvée
a(q) = a(UBound(a) + 1 - i) 'on passe à l'eleve suivant enregistré dans le tableau
Next i
End With
End SubVous n'avez donc pas besoin de formule ni de feuille supplémentaire
Après on peut ajouter des lignes pour trier le tableau de gauche comme le désirez au final ou transférer vers le tableau de droite. Dites moi et je vous donne le code à ajouter
Attention comme je vous ai dit, ne mettez jamais de lignes sans données dans un tableau structuré.
Vous avez laissé des lignes vides dans votre dernier fichier
Bonjour
Si je comprends vous mettez l'ordre de passage dans le tableau 1 via un code ?
C'est ça
Si oui, je ne vois pas l'utilité du tableau de droite.
Le tableau de droit va me servir à remplir un autre fichier excel qui lui doit impérativement être classé par ordre de passage, c'est un tableau de publipostage que je dois envoyer à mon administration pour édition des convocations.
Vu votre avancement vous voulez quoi comme aide en fait au final ?
Et bien, je n'ai plus vraiment besoin d'aide maintenant que j'ai réussi à faire ce que je voulais.
Du coup je partageais mon expérience, et le code que j'ai généré.
Je vous avais préparé un code en attendant votre retour sur mon post précédent et que j'ai adapté puisqu'au vu de votre post je pense comprendre que l'ordre de passage en colonne 3 doit être fait par code.
Ce que vous avez fait est un peu complexe. Vous avez juste besoin du code ci-dessous :
Merci
En lançant votre macro, j'ai une erreur sur lig qui n'est pas définie.
Comment la définie-t-on ? (J'entends par là, dim lig as ... )
Pourriez-vous expliquer un peu ce que fait votre code et donc comment il génère des nombres aléatoires uniques ?
Vous n'avez donc pas besoin de formule ni de feuille supplémentaire
Après on peut ajouter des lignes pour trier le tableau de gauche comme le désirez au final ou transférer vers le tableau de droite. Dites moi et je vous donne le code à ajouter
C'est en effet plus pratique de ne pas avoir besoin de feuille supplémentaire. Cela dit, mon code pourrait être exécuté dans des colonnes assez loin et masquées, le tout dans la même feuille.
Et oui, l'objectif ensuite c'est de transférer le contenu du tableau1 (gauche), dans le tableau2 (droite), et de faire un tri de ce tableau 2 sur l'ordre de passage.
Attention comme je vous ai dit, ne mettez jamais de lignes sans données dans un tableau structuré.
Vous avez laissé des lignes vides dans votre dernier fichier
En quoi est-ce gênant d'avoir des lignes vides dans le tableau ?
Jusqu'à présent ça ne m'a jamais occasionné le moindre problème.
Attention, je ne dis pas que vous n'avez pas raison, mais j'aimerais comprendre ce que ça peut provoquer comme problèmes :)
En tout cas, merci pour votre aide
Re
En lançant votre macro, j'ai une erreur sur lig qui n'est pas définie.
Oui désolé, mettez ceci
Dim lig as byteEt oui, l'objectif ensuite c'est de transférer le contenu du tableau1 (gauche), dans le tableau2 (droite), et de faire un tri de ce tableau 2 sur l'ordre de passage.
Ok alors faites ceci
1. Dans le code que je vous ai donné, juste avant le END SUB, ajoutez cette ligne
call Ajout_tableau22. Ajoutez le code ci-dessous après le premier code
Sub Ajout_tableau2()
With ActiveSheet.ListObjects("Tableau2_Tri_OrdrePassage")
On Error Resume Next
.DataBodyRange.Delete
On Error GoTo 0
.ListRows.Add
ActiveSheet.ListObjects("Tableau1_Tri_Noms").DataBodyRange.Copy .DataBodyRange.Item(1, 1)
With .Sort
.SortFields.Add Key:=Range("Tableau2_Tri_OrdrePassage[Ordre de Passage]"), SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End With
End SubEn quoi est-ce gênant d'avoir des lignes vides dans le tableau ?
Cela n'apporte rien de plus et cela pourrait vous poser des soucis au cas où vous ajouteriez de nouvelles données par code par exemple. Idem si vous utilisez power query ou des TCD, les lignes vides feront parties des résultats.
Exemples :
- si vous cherchez la dernière ligne vide dans un tableau structuré, il vous prendra la dernière ligne de votre tableau qu'elle soit vide ou pas. Du coup par exemple dans votre cas il ira chercher la ligne 37 au lieu de 21 (Eleve 18).
- dans le cas du code proposé, la variable "a" reprendra toutes les lignes de votre tableau de gauche même si vide.
En gros vous devez toujours penser à base de données.
De toute façon si vous devez ajouter une ligne manuellement, il vous suffit de vous positionner juste en dessous de la dernière ligne du tableau structuré puis d'ajouter vos données. Vous verrez que le tableau structuré va directement prendre en charge vos nouvelles données. Exemple dans votre fichier, la dernière ligne du tableau est la 37, allez ajouter une donnée en ligne 38, vous comprendrez ce que je vous explique avant
Pourriez-vous expliquer un peu ce que fait votre code et donc comment il génère des nombres aléatoires uniques ?
Pas de souci je vais vous mettre cela dans le code du post précédent
Si terminé pensez à
Cordialement
Merci pour la correction, je n'ai plus cette erreur ^^
Mais comme j'ai mis ceci Option Base 1 ' les tableaux commenceront à l'indice 1 les indices des tableaux ne vont plus dans votre code.
Je viens de voir que vous aviez mis plein de commentaires dans le code ^^ Merci
ça vient de m'aider à finaliser les modifications pour que tout fonctionne sans erreur d'indice hors plage (j'ai une vieille habitude de faire commencer mes tableaux à l'indice 1 quand c'est possible...)
Voilà les modifications, qui semblent fonctionner sans générer d'erreurs, j'ai aussi modifié le nom de certaines variables pour que ça reflète mieux (et que ça me parle bien) ce qu'elles contiennent :
Option Explicit ' On force à déclarer les variables
Option Base 1 ' les tableaux commenceront à l'indice 1
Public Const SHEET_Ordre As String = "Ordre de passage" ' Nom de la feuille ou se placer
Public Const SHEET_Gen As String = "Generateur"
Public Const CELL_1er_etudiant As String = "A4" ' Cellule avec le nom du premier etudiant
Public Const NOM_Tableau1 As String = "Tableau1_Tri_Noms"
Public Const NOM_Tableau2 As String = "Tableau2_Tri_OrdrePassage"
Public Const NOM_Col_Tableau2_Tri_Final As String = "Ordre de Passage"
Sub Generer_Ordre_de_Passage()
Dim tab_noms_eleve() ' déclaration variable de type tableau
Dim i, alea, ligne_eleve As Byte ' Nombre entier de 0 à 255.
With ActiveSheet.ListObjects(NOM_Tableau1)
.ListColumns(3).DataBodyRange.ClearContents ' suppression des données en colonne 3
ReDim tab_noms_eleve(.ListRows.count) ' redimensionne le tableau Tri_noms sur base du nombre de lignes du tableau
For i = 1 To UBound(tab_noms_eleve) ' boucle depuis 1 jusque limite supérieure de a (correspond au nombre de lignes du tableau structuré car début par i = 1)
tab_noms_eleve(i) = .DataBodyRange(i, 1) ' attribue le nom des élèves à "a" pour chaque variable i
Next i
For i = 1 To UBound(tab_noms_eleve) 'boucle sur toutes les valeurs de "a"
alea = Application.RandBetween(1, UBound(tab_noms_eleve) - i + 1) ' attribue un numéro d'ordre aléatoire à chaque valeur de "a"
ligne_eleve = WorksheetFunction.Match(tab_noms_eleve(alea), .ListColumns(1).DataBodyRange, 0) ' renvoie la ligne de l'élève a(q) trouvée en colonne 1 du tableau structure
.DataBodyRange(ligne_eleve, 3) = i ' on met en colonne 3 du tableau structuré la valeur de i pour la ligne trouvée
tab_noms_eleve(alea) = tab_noms_eleve(UBound(tab_noms_eleve) + 1 - i) ' on passe à l'eleve suivant enregistré dans le tableau
Next i
End With
Call Ajout_tableau2
End Sub
Sub Ajout_tableau2()
With ActiveSheet.ListObjects(NOM_Tableau2)
On Error Resume Next
.DataBodyRange.Delete
On Error GoTo 0
.ListRows.Add
ActiveSheet.ListObjects(NOM_Tableau1).DataBodyRange.Copy .DataBodyRange.Item(1, 1)
With .Sort
.SortFields.Add Key:=Range(NOM_Tableau2 & "[" & NOM_Col_Tableau2_Tri_Final & "]"), SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End With
End SubPouvez-vous me dire si c'est OK ?
L'ajout de la fonction de recopie et de tri fonctionne parfaitement, et j'ai appris des choses :) sur la manipulation des tableaux construits.
Petite question : quel est l'intérêt de faire une procédure séparée pour la copie des données du tableau1 vers le tableau2 ?
Dans l'optique où je ne vais pas être amené à faire ceci séparément du tirage au sort de l'ordre de passage ? (à la rigueur, je peux l'utiliser avec mon autre fonction faite avant la vôtre ^^, mais je vais conserver la vôtre, qui me paraît finalement plus simple et plus adaptée ^^)
Cela n'apporte rien de plus et cela pourrait vous poser des soucis au cas où vous ajouteriez de nouvelles données par code par exemple. Idem si vous utilisez power query ou des TCD, les lignes vides feront parties des résultats.
Exemples :
- si vous cherchez la dernière ligne vide dans un tableau structuré, il vous prendra la dernière ligne de votre tableau qu'elle soit vide ou pas. Du coup par exemple dans votre cas il ira chercher la ligne 37 au lieu de 21 (Eleve 18).
- dans le cas du code proposé, la variable "a" reprendra toutes les lignes de votre tableau de gauche même si vide.
En gros vous devez toujours penser à base de données.
De toute façon si vous devez ajouter une ligne manuellement, il vous suffit de vous positionner juste en dessous de la dernière ligne du tableau structuré puis d'ajouter vos données. Vous verrez que le tableau structuré va directement prendre en charge vos nouvelles données. Exemple dans votre fichier, la dernière ligne du tableau est la 37, allez ajouter une donnée en ligne 38, vous comprendrez ce que je vous explique avant
Merci pour ces explications, je comprends mieux. Je tâcherais à l'avenir à ne pas laisser de ligne vide
PS : je ne suis pas programmeur, mais j'ai quand même des bases dans divers langages ^^
Mais il arrive que certain algorithme m'échappe, ou que certaines fonctions appelées ne me soient pas connues.
Bonjour
Bah, vous pourriez rester plus simple...
Comme vous me posez la question, voici mes réponses :
1. Les déclarations Public const ne servent que lorsque vous devez utilisez des variables dans des codes se trouvant dans plusieurs feuilles, modules, etc...
Je pense que je ne serais pas le seul à vous le dire. Dans votre cas il n'y a vraiment aucun intérêt à faire cela.
En lieu et place et pour suivre un peu l'idée que vous avez, j'aurais plutôt fait ceci
Dim NOM_Tableau1 As ListObject
Dim NOM_Tableau2 As ListObjectEnsuite on écrit ceci dans le code :
Set NOM_Tableau1 = ActiveSheet.ListObjects("Tableau1_Tri_Noms")'dans le code Generer_Ordre_de_Passage
Set NOM_Tableau2 = ActiveSheet.ListObjects("Tableau2_Tri_OrdrePassage")'dans le code Ajout_tableau22. Les 3 premières déclarations Public servent à quoi dans votre fichier ?.
3. La ligne Dim i, alea, ligne_eleve As Byte n'est pas correcte. Dans ce cas seule la variable ligne_eleve est de type Byte. Toutes les autres sont de type variant qui est le type de variable à éviter. Vous devez donc mettre Byte pour chacune des 3 variables.
De plus le nom "alea" est à remplacer. Là vous utilisez une fonction excel pour nommer une variable. A ne jamais faire !. Là remplacez "alea" par autre chose. J'avais mis "q" dans mon code. Prenez peut-être numalea par exemple.
Donc vous devez corriger comme je vous avez donné dans mon code -->
Dim i as byte, numalea as byte, ligne_eleve As Byte4. Pour le nom "NOM_Col_Tableau2_Tri_Final" : pourquoi définissez vous un nom alors qu'il se défini automatiquement par le nom "ordre de passage" que vous utilisez dans la cellule H3 du tableau structuré ?. Là je ne comprends pas et si je fais une lecture du code c'est moins simple à analyser car le nom choisi ne permet pas d'associer directement à la colonne "ordre de passage".
En fonction des points ci-dessus, voici votre code adapté :
Option Explicit ' On force à déclarer les variables
Option Base 1 ' les tableaux commenceront à l'indice 1
Dim NOM_Tableau1 As ListObject
Dim NOM_Tableau2 As ListObject
Sub Generer_Ordre_de_Passage()
Dim tab_noms_eleve() ' déclaration variable de type tableau
Dim i As Byte, numalea As Byte, ligne_eleve As Byte ' Nombre entier de 0 à 255.
Set NOM_Tableau1 = ActiveSheet.ListObjects("Tableau1_Tri_Noms")
With NOM_Tableau1
.ListColumns(3).DataBodyRange.ClearContents ' suppression des données en colonne 3
ReDim tab_noms_eleve(.ListRows.Count) ' redimensionne le tableau Tri_noms sur base du nombre de lignes du tableau
For i = 1 To UBound(tab_noms_eleve) ' boucle depuis 1 jusque limite supérieure de a (correspond au nombre de lignes du tableau structuré car début par i = 1)
tab_noms_eleve(i) = .DataBodyRange(i, 1) ' attribue le nom des élèves à "a" pour chaque variable i
Next i
For i = 1 To UBound(tab_noms_eleve) 'boucle sur toutes les valeurs de "a"
numalea = Application.RandBetween(1, UBound(tab_noms_eleve) - i + 1) ' attribue un numéro d'ordre aléatoire à chaque valeur de "a"
ligne_eleve = WorksheetFunction.Match(tab_noms_eleve(numalea), .ListColumns(1).DataBodyRange, 0) ' renvoie la ligne de l'élève a(q) trouvée en colonne 1 du tableau structure
.DataBodyRange(ligne_eleve, 3) = i ' on met en colonne 3 du tableau structuré la valeur de i pour la ligne trouvée
tab_noms_eleve(numalea) = tab_noms_eleve(UBound(tab_noms_eleve) + 1 - i) ' on passe à l'eleve suivant enregistré dans le tableau
Next i
End With
Call Ajout_tableau2
End Sub
Sub Ajout_tableau2()
Set NOM_Tableau2 = ActiveSheet.ListObjects("Tableau2_Tri_OrdrePassage")
With NOM_Tableau2
On Error Resume Next
.DataBodyRange.Delete
On Error GoTo 0
.ListRows.Add
NOM_Tableau1.DataBodyRange.Copy .DataBodyRange.Item(1, 1)
With .Sort
.SortFields.Add Key:=Range(NOM_Tableau2 & "[ordre de passage]"), SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End With
End SubMais comme j'ai mis ceci Option Base 1 ' les tableaux commenceront à l'indice 1 les indices des tableaux ne vont plus dans votre code.
Oui c'est aussi une manière de procéder.
Petite question : quel est l'intérêt de faire une procédure séparée pour la copie des données du tableau1 vers le tableau2 ?
Chacun fonctionne un peu à sa manière. Pour ma part je préconise toujours de faire des macros contenant le moins de lignes possibles plutôt que de faire des codes trop longs qui ne sont pas faciles à analyser en cas de problème ou de changement. On aurait pu mettre tout en un seul mais cela me paraissait plus clair de dissocier ce que l'on doit faire avec le tableau 1 (définir les ordre de passage) et le tableau 2 (ajoute et tri).
Voilà pour mes commentaires... j'ai été long là...
Cordialement
Bonjour @Dan
1. Les déclarations Public const ne servent que lorsque vous devez utilisez des variables dans des codes se trouvant dans plusieurs feuilles, modules, etc...
Je pense que je ne serais pas le seul à vous le dire. Dans votre cas il n'y a vraiment aucun intérêt à faire cela.
Sur un autre projet, il était plus simple pour moi d'avoir ces constantes en dehors des autres fonctions, d'une part lorsque je devais les modifier après un changement dans la feuille de calcul, mais surtout pour que mes différentes fonctions, et modules puissent avoir accès à ces valeurs. Une seule définition est plus simple que plusieurs à modifier ^^
J'ai gardé cette habitude, ici c'est vrai que ça n'est pas trop nécessaire, si ce n'est que centraliser les variables à modifier en cas de modification de la feuille de calcul est plus simple et plus pratique à avoir tout en haut du module.
En lieu et place et pour suivre un peu l'idée que vous avez, j'aurais plutôt fait ceci
Dim NOM_Tableau1 As ListObject Dim NOM_Tableau2 As ListObjectEnsuite on écrit ceci dans le code :
Set NOM_Tableau1 = ActiveSheet.ListObjects("Tableau1_Tri_Noms") 'dans le code Generer_Ordre_de_Passage Set NOM_Tableau2 = ActiveSheet.ListObjects("Tableau2_Tri_OrdrePassage") 'dans le code Ajout_tableau2
C'est effectivement une bonne solution de procéder ainsi. Surtout que mon module n'est pas très chargé en fonctions :)
Mais mon objectif de passer par la variable de type string pour le nom du tableau est de ne pas me soucier du nom du tableau dans la fonction, si jamais je le change par la suite, je n'ai qu'à modifier une seule fois ma variable constante.
En revanche, pourriez-vous m'expliquer en quoi votre déclaration en ListObject est plus avantageuse que ma version en String ? (j'aime bien comprendre
2. Les 3 premières déclarations Public servent à quoi dans votre fichier ?.
Ces déclarations sont pour une autre procédure, celle que j'avais faite avant que vous ne donniez la vôtre.
3. La ligne Dim i, alea, ligne_eleve As Byte n'est pas correcte. Dans ce cas seule la variable ligne_eleve est de type Byte. Toutes les autres sont de type variant qui est le type de variable à éviter. Vous devez donc mettre Byte pour chacune des 3 variables.
De plus le nom "alea" est à remplacer. Là vous utilisez une fonction excel pour nommer une variable. A ne jamais faire !. Là remplacez "alea" par autre chose. J'avais mis "q" dans mon code. Prenez peut-être numalea par exemple.
Donc vous devez corriger comme je vous avez donné dans mon code -->
Dim i as byte, numalea as byte, ligne_eleve As Byte
Oh ! Je pensais que ce genre de déclaration déclarait tous les éléments de la même manière, ici en Byte... Belle erreur en effet !
Le type variant n'est effectivement pas forcément souhaitable.
Je vais changer cela.
Et pour la variable alea, mince aussi, je n'avais pas fait attention, je vais aussi la modifier.
4. Pour le nom "NOM_Col_Tableau2_Tri_Final" : pourquoi définissez vous un nom alors qu'il se défini automatiquement par le nom "ordre de passage" que vous utilisez dans la cellule H3 du tableau structuré ?. Là je ne comprends pas et si je fais une lecture du code c'est moins simple à analyser car le nom choisi ne permet pas d'associer directement à la colonne "ordre de passage". enfin c'est un avis perso bien entendu.
Je n'ai pas bien compris ce que vous avez expliqué sur la définition automatique du nom de colonne...
Est-ce que le nom de la colonne est directement obtenue en appelant le n° de la colonne ?
Pourriez-vous réexpliquer s'il vous plait ?
Chacun fonctionne un peu à sa manière. Pour ma part je préconise toujours de faire des macros contenant le moins de lignes possibles plutôt que de faire des codes trop longs qui ne sont pas faciles à analyser en cas de problème ou de changement. On aurait pu mettre tout en un seul mais cela me paraissait plus clair de dissocier ce que l'on doit faire avec le tableau 1 (définir les ordre de passage) et le tableau 2 (ajoute et tri).
Je vous rejoins dans cette idée de faire des fonctions les plus petites possible.
je vous avoue que même si je pars dans cette optique quand je programme un code (script bash/powershell/..., VBA...) à la fin j'ai bien quelques fonctions, mais ma principale est souvent un gargantua de ligne de code
En général, je fais une nouvelle fonction appelée par ma principale que si je suis amené à faire appel à ce code plusieurs fois au cours de la fonction principale.
Si vous voyiez le code des procédures de mon fichier excel de récupération de données des voitures peugeot/citroen/ds... ou de mon script powershell MKVMerge
Voilà pour mes commentaires... j'ai été long là...
Merci bien en tout cas, la longueur ne me pose pas de problème quand ce sont des explications
J'ai moi-même tendance à faire de longs messages
Merci encore, et bonne journée
Petite question supplémentaire : est-il possible d'envisager des contraintes sur les ordres de passage en fonction de créneaux horaires défini, pour lesquels certains étudiants ne peuvent pas y être affecté ? Et en limitant le nombre d'étudiant par créneau ?
(en créant un autre tableau qui regroupe les dates & horaires de passage qui peuvent changer d'une année sur l'autre)
Je demande cela car je viens d'être confronté au souci... j'ai 3 créneaux cette année pour faire passer des CCF à ma classe, mais sur deux créneaux, j'ai des étudiants qui ne peuvent être sur l'un, mais le peuvent sur l'autre, et vice-versa.
PS : je peux créer un autre sujet s'il le faut
Sur un autre projet, il était plus simple pour moi d'avoir ces constantes en dehors des autres fonctions, d'une part lorsque je devais les modifier après un changement dans la feuille de calcul, mais surtout pour que mes différentes fonctions, et modules puissent avoir accès à ces valeurs. Une seule définition est plus simple que plusieurs à modifier
Oui j'avais compris l'idée et cela se tient en cas où vous devez utiliser la même variable à droite et à gauche dans un projet Ici aucun raison d'utiliser des constantes puisqu'avec la solution que je propose on déclare le nom une fois via l'instruction SET... Donc on est plus direct.
Chaque fois que vous utilisez un tableau structuré, excel vous créera automatiquement un nom dans le gestionnaire. Cette fonction n'existait pas avant puis qu'il fallait créer le nom soit même et en plus prévoir la variation de plage en cas d'ajout de données.
En revanche, pourriez-vous m'expliquer en quoi votre déclaration en ListObject est plus avantageuse que ma version en String ? (j'aime bien comprendre )
On déclare en Dim NOM_TableauX en Listobject car la ligne Set NOM_TableauX correspond à l'objet tableau structuré que vous avez défini dans le gestionnaire de nom. Si vous utilisez String vous aurez un plantage (avec X valant 1 ou 2 dans votre cas)
Je n'ai pas bien compris ce que vous avez expliqué sur la définition automatique du nom de colonne...
Est-ce que le nom de la colonne est directement obtenue en appelant le n° de la colonne ?
Oui lorsque vous créez un tableau structuré, Excel utilise par le nom que vous attribuez à la colonne
C'est pour cette raison que vous aviez cette instruction dans le code de tri de mon premier post --> Range("Tableau2_Tri_OrdrePassage[Ordre de Passage]"
Où "Tableau2_Tri_ordrepassage" est le nom que vous avez changé dans le gestionnaire de noms et "Ordre de Passage" est le nom que vous avez mis en colonne 3 du tableau. A noter vous pouvez aussi remplacer utiliser cette ligne dans le tri en lieu et place de celle mentionnant "ordre de Passage".
.SortFields.Add Key:=NOM_Tableau2.ListColumns(3).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscendingDu coup plus besoin de mentionner le titre de colonne. On utilise simplement le numéro de colonne. .. finalement j'aurais dû vous proposer cela...Là peut-être que vous pourriez remplacer le 3 par numcol mais à voir si cette colonne est mentionnée dans plusieurs macros
J'y ajouterai de bien faire attention avec les codes VBA dans excel car on n'arrive trop souvent à "usine à gaz". Donc restez simple le plus possible.
Petite question supplémentaire : est-il possible d'envisager des contraintes sur les ordres de passage en fonction de créneaux horaires défini, pour lesquels certains étudiants ne peuvent pas y être affecté ? Et en limitant le nombre d'étudiant par créneau ?
(en créant un autre tableau qui regroupe les dates & horaires de passage qui peuvent changer d'une année sur l'autre)
Bah cela doit être possible. Il faudrait voir comment vous procédez. Peut-être penser à mettre tout dans le tableau de gauche ?
A vous de voir pour un nouveau sujet mais vous pouvez continuer ici vu que je suis le seul à vous avoir répondu
Bonsoir
Merci beaucoup pour ces explications et encore une fois pour votre aide
En ce qui concerne ma dernière demande, sur les contraintes horaires en fonction des groupes, j'ai ajouté deux autres tableaux dans ma feuille :
j'ai aussi ajouté une validation de données sur les groupes avec comme source le tableau de la liste des groupes.
L'idée est de forcer le tirage au sort des ordres de passage en fonction des contraintes de session jour+heure, du nombre d'étudiants maximal par session (il peut être moindre, avec mini 2) et du groupe affecté : si pas de groupe, pas de contrainte de groupe, on peut mettre n'importe qui.
Si le tirage est incomplet parce que les contraintes sont trop grandes :
- soit on affiche un message disant d'ajouter des sessions pour tel ou tel groupe (celui pour lequel il reste des étudiants à caser) ;
- soit on bourre un peu plus une session exceptionnellement : on l'affiche en rouge.
Je ne sais pas ce qui serait le mieux... peut-être une combinaison des deux :
on affiche un message, et on laisse le choix d'ajouter une ou plusieurs sessions, ou bien d'augmenter le nombre max d'étudiants d'une session, en indiquant quel nombre serait top pour la résolution du problème de tirage.
Je ne sais pas si c'est faisable avec une ou plusieurs macros, mais si c'était faisable, ce serait top ;)
Je n'ai malheureusement pas l'algorithme qui me vient pour essayer de coder ça.
Merci d'avance pour toute aide nouvellement apportée
Bonne soirée
Bonjour,
L'idée est de forcer le tirage au sort des ordres de passage en fonction des contraintes de session jour+heure, du nombre d'étudiants maximal par session (il peut être moindre, avec mini 2) et du groupe affecté : si pas de groupe, pas de contrainte de groupe, on peut mettre n'importe qui.
1. Dans le tableau 3, je vois deux dates en FED A et deux en FED C.
Dans votre tableau de gauche, colonne Date de passage, quelles seraient les dates à mentionner en fonction du tableau 3.
Exemple dans votre fichier posté :
C12 - Ordre de passage N° 1 - FED C
C23 - Ordre de passage N° 2 - FED C
C17 - Ordre de passage N° 3 - FED C
2. Lorsque vous écrivez, "pas de contrainte de groupe", doit-on comprendre que vous n'avez pas de groupe mentionné dans le tableau de gauche colonne 3
3. Dans le tableau 3, la colonne Nb évalués, correspondrait au nombre d'élèves possibles dans le groupe concerné
En gros, si je regarde pour FED C, on aurait uniquement 3 étudiants pour chaque vendredi ?. Soit 6 étudiants au total ?
4. Autre question, est-ce normal que le nombre évalués soit à 18 dans le tableau3 alors que votre tableau de gauche mentionne 21 étudiants
Désolé de mes questions mais Je ne suis pas sûr de bien comprendre tout là
Bonjour,
Merci de votre réponse
edit : soucis avec la tabulation et entrée... ça a validé l'envoi du message avant de l'avoir écrit
1. Dans le tableau 3, je vois deux dates en FED A et deux en FED C.
Dans votre tableau de gauche, colonne Date de passage, quelles seraient les dates à mentionner en fonction du tableau 3.
Exemple dans votre fichier posté :
C12 - Ordre de passage N° 1 - FED C
C23 - Ordre de passage N° 2 - FED C
C17 - Ordre de passage N° 3 - FED C
1/
Dans le tableau3_Contraintes, renommé en dans la version ci-jointe du fichier excel, il faut comprendre :
- S'il y a un groupe sélectionné, alors seuls les étudiants de ce groupe peuvent prétendre à cette session ;
- S'il n'y a aucun groupe sélectionné, alors n'importe quel étudiant de n'importe quel groupe peut prétendre à la session.
Il ne peut pas y avoir deux groupes sélectionnés via la validation par liste... ou bien je ne sais pas faire.
Et là je me dis que je vais renommer la première colonne de ce tableau...
Ainsi, pour l'étudiant n°1, il peut être dans une de ces sessions-là :
- Lundi 30/01 - 9h-11h
- Jeudi 02/02 - 11h-13h
- Lundi 06/02 - 9h-11h
- Jeudi 09/02 - 11h-13h
Et pour l'étudiant n°10 :
- Lundi 30/01 - 9h-11h
- Vendredi 03/02 - 13h30-15h30
- Lundi 06/02 - 9h-11h
- Vendredi 10/02 - 13h30-15h30
En gros, je me cale sur leurs heures dans l'emploi du temps, et comme ma matière est scientifique, j'ai des séances dédoublées (TP) avec un des groupes.
Et donc, dans la 4ème colonne du tableau1, il faudrait que soit recopié la session déterminée par le tirage au sort.
2. Lorsque vous écrivez, "pas de contrainte de groupe", doit-on comprendre que vous n'avez pas de groupe mentionné dans le tableau de gauche colonne 3
2/
Comme dit dans la réponse au point 1/, pas de contrainte = classe complète, donc les deux groupes sont dedans, mais je ne peux pas avoir de selection par validation de données via une liste pour deux choix possibles.
Après, il existe peut-être une autre solution dont je suis preneur ;)
3. Dans le tableau 3, la colonne Nb évalués, correspondrait au nombre d'élèves possibles dans le groupe concerné
En gros, si je regarde pour FED C, on aurait uniquement 3 étudiants pour chaque vendredi ?. Soit 6 étudiants au total ?
Oui, c'est ça. Les FED-C seraient évalués les vendredis (donc 6 étudiants), mais aussi les lundis avec max 6 aussi, mais là ça dépend de s'il y a des FED-C en même temps sur les lundis, sachant que le nombre d'évalués sur un lundi reste de 3 (FED-A et FED-C réunis).
4. Autre question, est-ce normal que le nombre évalués soit à 18 dans le tableau3 alors que votre tableau de gauche mentionne 21 étudiants
4/
My bad !
En essayant une génération avec davantage d'étudiants, j'ai omis de revoir à la hausse soit le nombre d'étudiants par session, soit le nombre de session.
Dans la version ci-jointe, j'ai remis les valeurs que j'ai dans la réalité : 18 étudiants (j'en ai un qui ne sera pas évalué, gardant sa note de l'an dernier).
Désolé de mes questions mais Je ne suis pas sûr de bien comprendre tout là
Il est préférable de demander plus de détails, et de nouvelles explications, car dans ma tête c'est super limpide, mais pas forcément une fois que j'ai transcrit par écrit mes idées
En tout cas, merci beaucoup pour votre aide
Re,
Point 1
Dans le tableau3_Contraintes, renommé en dans la version ci-jointe du fichier excel, il faut comprendre :
- S'il y a un groupe sélectionné, alors seuls les étudiants de ce groupe peuvent prétendre à cette session ;
- S'il n'y a aucun groupe sélectionné, alors n'importe quel étudiant de n'importe quel groupe peut prétendre à la session.
Ok cela concerne donc bien la colonne 2 du tableau de gauche. Dans ce tableau il y a toujours un groupe FED. Mais il se pourrait qu'il n'y ait pas de FED mentionné. Auquel cas, il faudrait prendre les jours pour lequel il n'y a pas de FED mentionné en tableau3.
Il ne peut pas y avoir deux groupes sélectionnés via la validation par liste... ou bien je ne sais pas faire.
La liste de validation ne permet pas l'ajout de donnée qui ne pas reprise dans votre tablea "liste des groupes". Soit vous faites un choix dans la liste ou vous laissez vide. Donc ce point est ok
Point 2 :
mais je ne peux pas avoir de selection par validation de données via une liste pour deux choix possibles.
Non c'est bon cela. Il vaut mieux une donnée que deux. Sinon ce sera trop complexe
Point 3:
Voyez le tableau ci-dessous. Est-ce qu'il y a erreur ou pas
Comme vous verrez, je trie sur l'ordre de passage et il y a au max 3 étudiants par jour
Point 3:
Voyez le tableau ci-dessous. Est-ce qu'il y a erreur ou pas
<capture>
Comme vous verrez, je trie sur l'ordre de passage et il y a au max 3 étudiants par jour
Ce remplissage me parait cohérent avec ce qui est attendu ^^
re
Dans la vue que j'ai postée j'ai trié par ordre de passage avant d'attribuer les jours.
Est-ce que l'ordre des jours que je vous ai montré peut être identique à l'ordre de passage dans votre fichier ?
Donc par exemple : ordre 12, 2 et 17 serait le 30/01, les 3 suivants serait le 02/02, etc... tout cela en respectant le FED bien entendu
Dans la vue que j'ai postée j'ai trié par ordre de passage avant d'attribuer les jours.
Est-ce que l'ordre des jours que je vous ai montré peut être identique à l'ordre de passage dans votre fichier ?
Donc par exemple : ordre 12, 2 et 17 serait le 30/01, les 3 suivants serait le 02/02, etc... tout cela en respectant le FED bien entendu
Si j'ai bien compris votre question, oui tout à fait ^^
L'ordre de passage peut coïncider avec les sessions :)
Mais votre exemple à la fin me fait penser que je n'ai pas trop compris
Bonjour
désolé du retard mais cela m'a prit plus de temps pour trouver une solution.
Voici le code à ajouter pour les dates de passage.
Sub Generer_dates()
Dim TSTriNoms As ListObject, TSContraintes As ListObject
Dim i As Byte, nb As Byte
Dim c As Range
Dim prem As String
With Worksheets("Ordre de passage")
Set TSTriNoms = .ListObjects("Tableau1_Tri_Noms")
Set TSContraintes = .ListObjects("Tableau3_Contraintes")
If WorksheetFunction.Sum(TSContraintes.ListColumns(2).DataBodyRange) <> TSTriNoms.ListRows.Count Then
MsgBox "le nombre d'étudiants est différent du nombre de sessions évaluées"
Exit Sub
End If
TSTriNoms.ListColumns(4).DataBodyRange.ClearContents 'effacer colonne 4 du tableau Tri noms
'ajout des dates si groupes de contraintes
For i = 1 To TSContraintes.ListRows.Count
With TSTriNoms.ListColumns(2).Range
Set c = .Find(TSContraintes.DataBodyRange(i, 3), LookIn:=xlValues)
If Not c Is Nothing Then
prem = c.Address
Do
If c.Offset(0, 2).Value = vbNullString Then
c.Offset(0, 2).Value = TSContraintes.DataBodyRange(i, 1)
nb = nb + 1
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And nb <> TSContraintes.DataBodyRange(i, 2)
End If
End With
'ajout des dates si pas de Groupes de contraintes
If TSContraintes.DataBodyRange(i, 3) = vbNullString Then
For Each c In TSTriNoms.ListColumns(4).DataBodyRange
If c = vbNullString And nb <> TSContraintes.DataBodyRange(i, 2) Then
TSTriNoms.DataBodyRange(c.Row - 3, 4) = TSContraintes.DataBodyRange(i, 1)
nb = nb + 1
End If
Next c
End If
nb = 0
Next i
End With
End SubPour l'exécuter à la suite de l'autre, allez dans le code Generer_Ordre_de_Passage, et juste après la ligne Call Ajout_tableau2 mettez ceci
Call Generer_datesFaites un test et dites moi
Crdlt
Bonsoir,
Merci beaucoup :)
J'ai tenté de comprendre le code que vous avez fourni...
Mais, sans vrai succès... Il y a aussi des variables dont je ne saisis pas la fonctionnalité... (c'est souvent pour ça que mes variables ont un nom à rallonge pour que ça me parle quand je regarde le code
Alors, si je lance la procédure sans point d'arrêt, mon excel plante, enfin il ne réagit plus du tout... là ça fait 5 minutes XD
Mais si je lance en pas-à-pas, ça semble fonctionner, au moins pour remplir les 3 premières dates de passage. J'ai ensuite fait pas-à-pas-sortant, et pouf, excel ne répond plus...
Une idée de pourquoi ça plante ainsi ? (là ça fait bien 10min que ça tourne avec excel non réactif...)
Et avec ce qui a été complété, je me dis que la macro devrait procéder autrement...
car le lundi 30/01 est la première session, il devrait donc y avoir les premiers étudiants à passer, donc le 1, 2, 3 etc...
Mon idée, mal traduite en écrit, était que la génération des ordres de passages coincide avec les dates de passages, tout en contraignant aux groupes disponibles pour la session.