Trier un tableau sur VBA
Salut les gars,
Sidéré, je suis! 7-8 sec. sur ma machine! Les bras m'en sont tombés!
Steelson , j'ai l'impression de te voir jouer dans la cour des grands depuis mon bac à sable!
Bémol
Cela dit, pour ces histoires de doublons et autres trucs...
- dans ma moulinette-apéro, je renumérote d'office les demandes : 2-4-6 deviennent 1-2-3, 2-2 deviennent 1-2 et 2 devient 1 : indispensable pour utiliser Dico intelligemment!
En relisant l'historique de l'aventure, je constate et comprends enfin que Steelson utilise cet accès direct entre n° choix et n° de ligne que j'ai commencé à développer.
- une double numérotation pour une même zone sera traitée de la même manière. Le système précédent ne fera pas de cadeau et éliminera d'office tout choix inférieur même situé dans la même zone.
Á toi Denis, de confirmer le bien-fondé de la procédure ou alors nous décrire maintenant les critères à prendre en compte pour démêler ces situations.
Steelson, dans l'état actuel de mes connaissances en Dico (= 0), ça me paraît très difficile de faire mieux que toi!
Je ne sais pas ce qui est le plus malin : continuer dans mon coin ou étudier Dico et ton code et chercher à l'optimiser pour le bien commun...
En tout cas, pas avant ce soir et si la soirée se présente comme hier, ça ne va pas le faire non plus.
Allez, haut les coeurs!
Encore bravo, Steelson!
A+
Bémol
: n'attendait-on pas +- 63.000 individus placés ? Sauf erreur, je n'en vois qu'une trentaine de mille... ??
C'est normal vu que le fichier n'est pas 100% fidèle à l'original, mais pour l'instant avec le fichier original et avec les doublons on trouve environ 65000 ce qui est plutôt encourageant.
Alors il y a une différence par rapport au fichier réel, c'est que le numéro d'individu est composé de chiffres et de lettres, peut-être que c'est ça qui met à mal ton programme ? J'ai testé en ajoutant un "a" à chaque individu et effectivement cela met des doublons. Désolé de pas te l'avoir dit, je ne pensais pas que cela serait un problème
Je pense justement que c'est là le soucis ... sauf preuve du contraire car j'apprends aussi la manipulation des dico et des array en "marchant" et donc pour le moment j'ai une version numérique et une version alphanumérique.
Pourquoi ? Je me suis aperçu que "dico", plus exactement Scripting.Dictionary est très sensible au contenu de la clé :
dico(range("A1"))ne sera pas la même chose quedico(range("A1").value(je me suis déjà fait avoir par le passé en omettant le value comme on le fait souvent !).
dico(12345)ne sera pas la même chose quedico("12345")
Dans ton jeu d'essai, j'ai construit les "dico" comme ceci :
dico_individu(1)="1149,1|1144,2|"choix 1 = zone 1149 et choix 2 = zone 1144 pour l'individu 1
on retrouve ici le candidat 1 dans les zones correspondantes (1,1 veut dire candidat 1 choix 1, et 1,2 veut dire candidat 1 choix 2)
dico_zone(1149)="63942,1|51719,3|51820,2|19191,1|25196,1|38188,5|2471,1|46968,4|12311,4|9295,2|47890,4|52893,4|63890,5|19579,2|1492,3|51010,2|12852,2|63996,2|488,1|51799,3|64036,4|38239,3|14667,2|62499,5|63889,2|53712,3|63532,2|52525,2|64061,1|165,5|15705,5|1,1|48816,1|64066,2|2286,3|64133,4|13872,3|64332,1|39570,6|22112,4|37737,5|52746,2|63504,1|40980,4|62040,4|44,1|435,4|63497,4|45,6|63663,2|534,4|"
dico_zone(1144)="62040,2|84,3|52217,1|44366,4|26150,3|51719,4|52746,1|64133,2|64066,1|13872,2|37737,1|47222,2|38239,2|19579,1|43772,1|31700,2|63984,1|1,2|63912,1|33384,4|66023,3|64831,4|"mais quand j'extrait de ces zones le candidat 1 qui est éligible, il apparaît comme texte et non plus comme nombre et dico ne lui retrouve plus ses choix, donc je ne peux pas supprimer son choix 2 qui subsiste et devient un doublon.
Bref, dis moi si les zones sont toutes numériques ? et les individus tous alphanumériques avec l'aménagement en suffixe a comme proposé ? je te renverrai la macro un poil modifiée.
Là aussi j'apprends en marchant, et pour le moment je suis allé au plus vite dans le dimensionnement du tableau résultat, je vais corriger cela et dimensionner exactement à la réponse de la macro.Je constate un autre problème sur le fichier sur lequel tu t'es basé : dans les dernières lignes de l'onglet "résultat", il y a des numéros d'individu qui n'existent pas : ils dépassent 66998 alors que c'est le maximum possible.
Que nenni.Steelson , j'ai l'impression de te voir jouer dans la cour des grands depuis mon bac à sable!
Bravo! Vive Dico! : Steelson, dans l'état actuel de mes connaissances en Dico (= 0), ça me paraît très difficile de faire mieux que toi!
Je ne sais pas ce qui est le plus malin : continuer dans mon coin ou étudier Dico et ton code et chercher à l'optimiser pour le bien commun...
Surtout continue car j'insiste tu m'as déjà beaucoup apporté grâce à ton analyse ... je pense qu'au tout début j'avais un biais qui a conduit à intervertir des affectations.
Après, c'est en effet l'utilisation de dico, mais aussi des array en VBA, je me suis rendu compte hier soir, enfin cette nuit, que ce qui était chronophage était de jouer en direct avec les cellules d'une feuille ce que j'ai toujours eu tendance à faire jusque maintenant.
J'ajoute que ta présentation en ascenseur m'a fait opter pour ceci où tu retrouves exactement le contenu de la colonne dans l'ordre des points.
dico_zone(1144)="62040,2|84,3|52217,1|44366,4|26150,3|51719,4|52746,1|64133,2|64066,1|13872,2|37737,1|47222,2|38239,2|19579,1|43772,1|31700,2|63984,1|1,2|63912,1|33384,4|66023,3|64831,4|"Il est alors très facile de supprimer les choix en faisant un replace.
Bon, va falloir que je retourner à ma plomberie, ma menuiserie, ma peinture, mon jardinage, mon béton, mon parquet, etc.
@Steelson : Tous les individus sont composés de chiffres et de lettres, tous les zones sont uniquement des chiffres.
Après si cela te prend beaucoup de temps, ne t'embête pas, je peux transformer les individus uniquement en chiffres, même si cela me fait faire une manip' un peu longue (la conversion en nombre des 151828 lignes prend plusieurs heures...)
Je suis en train de la tester là, je vous tiens au courant !
Ecran ordi HS...
Probablement out of game jusque demain en soirée !
Comme toi, Steelson, retour à tous ces travaux passionnants. Au moins, madame sera contente !
A+
Salut Denis,
plusieurs heures pour convertir tes données ??
C'est un ZX Spectrum, ton ordi ?
A+
Il faudra une légère adaptation ... je vais réfléchir avant d'agir sinon je vais droit dans le mur.Après si cela te prend beaucoup de temps, ne t'embête pas, je peux transformer les individus uniquement en chiffres, même si cela me fait faire une manip' un peu longue (la conversion en nombre des 151828 lignes prend plusieurs heures...)
Je suis en train de la tester là, je vous tiens au courant !
@curulis57 : je suis d'accord avec toi, c'est très étonnant mais j'ai lancé la conversion vers 10 h et ce n'est toujours pas terminé !
Il faudra une légère adaptation ... je vais réfléchir avant d'agir sinon je vais droit dans le mur.Après si cela te prend beaucoup de temps, ne t'embête pas, je peux transformer les individus uniquement en chiffres, même si cela me fait faire une manip' un peu longue (la conversion en nombre des 151828 lignes prend plusieurs heures...)
Je suis en train de la tester là, je vous tiens au courant !
Donc voici comme promis :
https://www.cjoint.com/c/IJEqDCxNmsw
je testerai une solution plus adaptative en fonction du fait que la valeur est numérique ou pas !
C'est génial merci Steelson ! Là j'ai quelques incohérences mais je suis en train de les voir une par une, pour l'instant elles viennent uniquement de problèmes dans le fichier original, que je peux corriger manuellement.
Je te tiens au courant
@Steelson : Encore une fois bravo ! Le programme fonctionne pour 99,98% des individus !!!
Le seul problème, c'est lorsque l'on rencontre les cas suivant :
Il y a 1 seule place à Marseille, 1 seul place à Paris, il n'y a que 2 candidats :
Paul demande Marseille en choix 1, il a 1000 points
Paul demande Paris en choix 2, il a 1500 points
Marcel demande Paris en choix 1, il a 1000 points
Marcel demande Marseille en choix 2, il a 1500 points
Le programme comprend que :
- Marcel ne peut pas avoir Paris, puisque Paul a plus de points que lui pour Paris, on lui a attribue donc le choix n°2, Marseille.
- Paul ne peut pas avoir Marseille, puisque Marcel a plus de points que lui pour Marseille, on lui attribue donc le choix n°2, Paris.
Ici, les 2 individus sont lésés puisqu'ils ont tous les 2 leur choix n°2 alors qu'ils auraient dû avoir tous les 2 leur choix n°1 !
Remarque : le cas peut bien sûr se rencontrer avec davantage de candidats, ce n'est qu'un exemple basique.
Il y a 1 seule place à Marseille, 1 seul place à Paris, il n'y a que 2 candidats :
Paul demande Marseille en choix 1, il a 1000 points
Paul demande Paris en choix 2, il a 1500 points
Marcel demande Paris en choix 1, il a 1000 points
Marcel demande Marseille en choix 2, il a 1500 points
Cela ne m'étonne pas trop, je pressentais qu'il y avait des "bouclages " possibles ! On peut y réfléchir quand même.
Initialement, je traitais d'abord tous les choix 1 de façon "ferme", mais je m'étais rendu compte que si Paul demandait Paris avec 2000 points, il ne l'avait pas, c'était Marcel !! C'est un des cas d'écart avec Curulis qui m'avait alerté et donc j'ai plutôt fait une grosse préparation des données avant d'affecter.
Ce cas se produit justement quand des points sont "identiques", chose que j'ai vérifié, mais pour lequel je laisse excel se débrouiller en classant toutes les données par points !
Au passage, j'au revu la macro pour ne pas dépendre de la qualité numérique / alphanumérique des données ... en passant systématiquement la clé des dico en alphanum. Exemple :
For i = 1 To UBound(zone)
cle = IIf(IsNumeric(zone(i, 1)), CStr(zone(i, 1)), zone(i, 1))
nb(cle) = zone(i, 2)
NextLa macro devient alors ...
Option Base 1
Sub Affecter()
Dim zone() As Variant
Dim data() As Variant
Dim result() As Variant
Dim cle As Variant
[debut] = Now
zone = [Tzones].Value
Set nb = CreateObject("Scripting.Dictionary") ' effectif par zone
For i = 1 To UBound(zone)
cle = IIf(IsNumeric(zone(i, 1)), CStr(zone(i, 1)), zone(i, 1))
nb(cle) = zone(i, 2)
Next
Sheets("Data").ListObjects(1).Sort.Apply
data = [Tdata].Value
Set z = CreateObject("Scripting.Dictionary") ' zone contient individu et choix dans l'ordre des points
Set c = CreateObject("Scripting.Dictionary") ' choix par individu
For i = 1 To UBound(data)
' candidats pour une zone donnée
cle = IIf(IsNumeric(data(i, 3)), CStr(data(i, 3)), data(i, 3))
z(cle) = z(cle) & data(i, 1) & "," & data(i, 4) & "|"
' choix des candidats
cle = IIf(IsNumeric(data(i, 1)), CStr(data(i, 1)), data(i, 1))
c(cle) = c(cle) & data(i, 3) & "," & data(i, 4) & "|"
Next
tour = 0
Do
tour = tour + 1
drapeau = False
For Each lieu In z.keys
tz = Split(z(lieu), "|")
For i = 0 To Application.Min(UBound(tz), nb(lieu)) - 1
individu = (Split(tz(i), ",")(0))
choix = Split(tz(i), ",")(1)
tc = Split(c(individu), "|")
For ii = 0 To UBound(tc) - 1
autrelieu = Split((tc(ii)), ",")(0)
autrechoix = Split((tc(ii)), ",")(1)
If autrechoix > choix Then
drapeau = True
c(individu) = Replace(c(individu), autrelieu & "," & autrechoix & "|", "")
z(autrelieu) = Replace(z(autrelieu), individu & "," & autrechoix & "|", "")
End If
Next
Next
Next
Loop While drapeau
Debug.Print "nb de tours : " & tour
Sheets("Resultat").Select
ReDim result(1 To 2, 1 To UBound(data)) ' inversé pour pouvoir ensuite redimensionner en preserve
With Sheets("Resultat").ListObjects(1)
If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
End With
ligne = 0
For Each lieu In z.keys
tz = Split(z(lieu), "|")
For i = 0 To Application.Min(UBound(tz), nb(lieu)) - 1
ligne = ligne + 1
individu = (Split(tz(i), ",")(0))
result(1, ligne) = individu
result(2, ligne) = lieu
Next
Next
ReDim Preserve result(1 To 2, 1 To ligne)
Debug.Print "nb de lignes : " & ligne
Range("A2").Resize(ligne, 2) = Application.Transpose(result)
With Sheets("Resultat").ListObjects(1)
.Sort.Apply
End With
[fin] = Now
MsgBox "fin de calcul !"
End SubJ'ai regardé le temps ...
3 secondes pour préparation des données (mise en place des "dico")
6 secondes pour la boucle
immédiat pour la mise en forme des résultats.
Le problème est en effet un conflit de règle entre privilégier le choix 1 quand c'est possible mais aussi respecter ceci ...
Si on évite vraiment les doublons dans les points est-ce que l'on se sort ?Exemple :
Ici, le fonctionnaire A sera prioritaire pour Paris par rapport au fonctionnaire B puisque son nombre de points est plus élevé.
- un fonctionnaire A demande Paris sur son choix n°6 et a 100 points
- un fonctionnaire B demande Paris sur son choix n°1 et a 50 points
Est-ce qu'on ne pourrait pas d'abord affecter tous les individus qui ont le plus grand nombre de points avec le choix n°1 et après appliquer le programme tel qu'il est actuellement ? Le problème ne se constate que sur des choix n°1 donc cela pourrait régler le gros du problème.
Par contre je viens de me rendre compte que si actuellement il n'y a pas de doublons pour la dernière place disponible d'une zone dans mon fichier original, vu que mon but est de constater les effets d'une modification des critères, il n'est pas impossible que cela provoque des doublons qui n'existaient pas avant, donc finalement c'est possible que, de façon marginale, il y en ait
Il n'est pas nécessaire de classer ces doublons, vu que le phénomène sera marginal, mais il serait bien de les prendre en compte pour que cela ne fasse pas planter l'application (si jamais ce risque pouvait exister)...
Est-ce qu'on ne pourrait pas d'abord affecter tous les individus qui ont le plus grand nombre de points avec le choix n°1 et après appliquer le programme tel qu'il est actuellement ? Le problème ne se constate que sur des choix n°1 donc cela pourrait régler le gros du problème.
Ach, j'étais parti comme cela, il faudrait que je retrouve le cas auquel cela amenait ! je vais fouiller ...
Par contre je viens de me rendre compte que si actuellement il n'y a pas de doublons pour la dernière place disponible d'une zone dans mon fichier original, vu que mon but est de constater les effets d'une modification des critères, il n'est pas impossible que cela provoque des doublons qui n'existaient pas avant, donc finalement c'est possible que, de façon marginale, il y en ait
Il n'est pas nécessaire de classer ces doublons, vu que le phénomène sera marginal, mais il serait bien de les prendre en compte pour que cela ne fasse pas planter l'application (si jamais ce risque pouvait exister)...
En réalité, les doublons j'y crois de moins en moins eu égard à l'algorithme.
Celui exécute la chose suivante : pour un individu, dès qu'il est éligible d'un lieu en tenant compte de ses points les plus élevés (cela peut-être le choix 3 par exemple), alors les choix "inférieurs" sont effacés ipso facto (en l’occurrence ses choix 4, 5 et 6) et si ensuite on lui reconnait une éligibilité au choix 2, alors le choix 3 est effacé. Le choix 3 resterait en lice si et seulement il y avait d'autres candidats en nombre suffisant pour remplir ce choix avant lui.
Donc en fin de traitement, un individu "éligible" n'a plus qu'un seul choix !
Est-ce qu'on ne pourrait pas d'abord affecter tous les individus qui ont le plus grand nombre de points avec le choix n°1 et après appliquer le programme
Denis, tu peux le faire sans changer le programme !
Il suffit de modifier l'ordre de tri du tableau des choix par individus.
Au lieu de la trier par points, trie le par choix croissant et point décroissants ensuite.
La macro ne fait que trier selon ces ordres de tri prédéfinis par l'utilisateur. C'est d'ailleurs un point sensible car si le tri a été modifié les réponses ne seront pas les mêmes !
Sans regarder dans le détail, avec les données que tu avais fournies, en changeant l'ordre (choix puis points)
80% des individus reçoivent le même affectation.
Il y a 1% de plus d'individus ayant trouvé chaussure à leur pied
Et donc 1 individu sur 5 reçoit une autre affectation.
Je n'ai pas modifié la macro, juste le tri des données.
Si tu le dis, je te fais confiance
Il va falloir que je fasse beaucoup de modifs sur mon fichier original (rien à voir avec le programme, mais l'application du programme m'a permis de me rendre compte qu'il fallait que je le fasse).
Je te tiens au courant une fois avoir fait les modifs et essayé ce que ça donne avec le nouveau tri.
ça sera pas avant lundi, alors je te souhaite un bon weekend
@Steelson : En fait, je viens de me rendre compte que je m'étais mal exprimé, je n'ai pas dit que je voulais changer les règles de classement et classer en fonction des n° de choix, puis du nombre de points en ordre décroissant. J'ai testé et cela me donne sans surprise un résultat bien différent de celui attendu.
Ce que je demandais, c'est s'il était possible de régler les cas problématiques cités plus haut en adoptant une règle globale qui permettent de faire en sorte que tous les individus qui sont en haut du classement par points sur leur choix n°1 soient affectés d'office (ceci n'étant pas valable pour les choix n°2 et plus, à cause du principe des chaises musicales).
Mais si tu me dis que c'est un peu trop pénible à faire, ce n'est pas grave, tu en as déjà fait ÉNORMÉMENT et comme je l'ai déjà dit, ces cas problématiques sont rares (0,02%).