VBA : Utilisation d'un tableau (à trois dimensions ?)

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
Yvouille
Passionné d'Excel
Passionné d'Excel
Messages : 9'137
Appréciations reçues : 94
Inscrit le : 6 avril 2007
Version d'Excel : 2016

Message par Yvouille » 28 mars 2020, 08:01

Salut eriiic,

Merci beaucoup pour ton retour et ton intérêt à mon problème.

Celui-ci concerne la formation d’équipes à l’intérieur d'un club lors de rencontres hebdomadaires (12, 20, 40, ..... personnes jouent en même temps, 1, 2 ou plus de mêlées de la journée) et non pas l’organisation d’un tournoi.

En fonction du nombre de personnes présentes pour une mêlée, mon programme trouve déjà qui doit jouer en doublette ou en triplette, selon certains critères bien précis. Si tu mets un point d’arrêt à la ligne 162 de la macro ‘’Tirage’’, dans le module du même nom, tu arrives à l’étape montrée ci-dessous, soit la liste des joueurs qui doivent jouer en doublette ou en triplette (T ou D en colonne 11).
Capture.PNG
A cette étape, il se peut que le nombre de joueurs présents fasse qu’il n’y ait personne qui doive jouer en triplette (il n’y aurait donc que des D en colonne 11). Ou alors 3, 6, 9, …… joueurs doivent jouer en triplette - soit 1, 2, 3, …… triplettes – et les autres en doublettes. Dans certaines situations, il se peut qu’il n’y ait que des triplettes (si le nombre de terrains est limité, par exemple 48 joueurs sur 8 terrains).

Si le nombre de triplettes est un nombre impair, une triplette doit jouer avec une doublette, autrement les doublettes jouent avec les doublettes et les triplettes avec les triplettes.

Sur la base de cette liste de joueurs – déjà répartis en doublette ou en triplette – je cherche les meilleures combinaisons possibles, en tenant compte de l’historique des parties depuis le début de la saison, enregistrées sur la feuille ‘’Archives’’, afin que les joueurs se retrouvent le minimum de fois ensemble et le minimum de fois les uns contre les autres.

Le résultat souhaité est la répartition de ces joueurs sur les différents terrains (colonnes 1 à 7 de la feuille ‘’Equipes’’). Dans l’exemple en place, c’est le résultat obtenu avec le solde de ma macro. Tu vois que 1 triplettes joue contre une triplette sur le terrain 1, 1 triplette contre une doublette sur le terrain 2 puis plus que des doublettes contre des doublettes.
Capture bis.PNG
Capture bis.PNG (16.17 Kio) Vu 160 fois
Mais il doit être possible de trouver une meilleure combinaison que celles que trouve mon code actuel ; une meilleure combinaison qui fasse que ces joueurs n’ont si possible encore jamais joué ensemble ou contre jusque-là. S’il devait y avoir une pondération entre ces deux critères, le fait d’avoir déjà joué avec un autre joueur est plus ‘’grave’’ que le fait d’avoir déjà joué contre.

Amicalement.
Essai Tirage - Démo Forum.xlsm
(92.33 Kio) Téléchargé 4 fois
Yvouille

Valais de Coeur
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'722
Appréciations reçues : 446
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 28 mars 2020, 11:07

Bonjour Yvouille,

en fait j'avais fini ma phase de maturation et de réflexion, et démarré qq chose.
Je vais continuer dans cette voie et essayant d'intégrer tes éléments (mais il faut du temps car il faut être méticuleux, attentif, et que ça ressemble à une fusée à plusieurs étages demandant chacun du boulot).
Mais à ma façon, car chaque test ajouté coûte en temps machine.
Pour moi il y a des idées qui peuvent paraître logiques et excellentes pour améliorer mais qui au final s'avèrent moins bonnes en résultat car on explore 3 fois moins de branches dans le même temps.
Seulement c'est plus au feeling et avec l'expérience que je fais mes choix

Par exemple
le fait d’avoir déjà joué avec un autre joueur est plus ‘’grave’’ que le fait d’avoir déjà joué contre
Je compte traiter ces 2 données séparément car en fait elles sont, pour moi, indépendantes.
Phase 1 : on constitue les équipes doublette/triplettes et on les note en fonction des nouvelles paires 'avec' crées.
Plus il y en a, mieux c'est. Sans regarder individuellement, ce qui nous intéresse c'est l'écart type : resserrer l'écart entre ceux qui en ont plus et ceux qui en ont moins. Avec l'avantage d'une fonction excel 'rapide'
Et compter sur l'aléatoire pour que cet écart se réduise au fil du temps. Tant pis pour celui qui attend 6-8 tours avant de connaitre un nouveau partenaire. Son tour viendra puisque plus son écart est important, plus il sera retenu (à condition d'avoir la chance d'avoir été tiré, c'est là où les paramètres X et Z jouent mais qui ne peuvent déterminés que de manière empirique pour moi...)

Le principe 'génétique' :
On part d'une position de départ qu'on note.
On génère X enfants (au feeling entre 5 et 30, en sachant que si on met 300, 299 auront été calculés pour rien puisque branches abandonnées) en permutant Y noms (réglable aussi, mais si élevé on tombe dans l'aléatoire pur et on perd l'intérêt du 'génétique') . On note et on garde la meilleure qui nous sert de nouveau départ.
On recommence tant qu'on améliore. Au bout de Z tirages (réglable) sans amélioration on laisse tomber.

Phase 2 : idem mais en permutant les équipes des matches. L'écart type des 'contre' le plus faible sera retenu.
Ceci dit, ce sera 2 fonctions de notation séparées que tu pourras faire à ton goût.
En gardant toujours en tête qu'un simple ajout de tests peut coûter très cher en performance. Si en ajoutant 3 lignes de codes tu explores 2 fois moins de positions, tu augmentes tes chances de passer à coté. Il faut que l'enjeu en vaille la chandelle.

Dis-moi si cette façon de voir les choses te parait prometteuse ou non.
Je n'ai pas l'expérience que tu as acquise là-dessus et il est possible que tu vois un écueil rédhibitoire.
Ou bien si tu préfères t'y tester seul, possible aussi :-)
eric
1 membre du forum aime ce message.
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Avatar du membre
Yvouille
Passionné d'Excel
Passionné d'Excel
Messages : 9'137
Appréciations reçues : 94
Inscrit le : 6 avril 2007
Version d'Excel : 2016

Message par Yvouille » 28 mars 2020, 11:39

Salut Eric,

J’ai compris que tu voulais également choisir les personnes jouant en doublette ou en triplette, mais il y aurait alors d’autres facteurs à tenir en compte, tels que le nombre de fois ayant déjà joué en triplette durant la saison, le pourcentage que ça représente par rapport au nombre total de participations et le fait qu’une personne nouvelle ait quand même une petite chance de ne pas jouer en triplette la première fois.

Il est tenu compte de tout ceci dans la première partie de ma macro et je pense qu’il serait mieux que tu partes sur cette liste de joueurs définis en doublette ou en triplette afin de chercher les meilleures combinaisons possibles par la suite. Mais si cela n’est pas possible ou complique les choses, on s’en accommodera.

Pour la suite, je n’ai pas absolument tout compris de tes explications, mais si le résultat final est bon, le chemin pour y arriver n’est pas si important.

Je ne sais pas ce que tu entends par ''temps machine'', mais nous effectuons nos tirages actuels à l'aide d'un programme qui prend 1 à 2 minutes par mêlée - sur un ordi de 16 GB - et ce n'est absolument pas un problème. On est des joueurs de pétanque, pas des stressés de l'informatique :)

Chaleureusement.
Yvouille

Valais de Coeur
h
h2so4
Passionné d'Excel
Passionné d'Excel
Messages : 9'461
Appréciations reçues : 428
Inscrit le : 16 juin 2013
Version d'Excel : 365 UK Windows 10

Message par h2so4 » 2 avril 2020, 15:52

Bonjour Yvouille,

afin de déterminer le meilleur algorithme, peux-tu m'indiquer le nombre de membres et le nombre maximum de membres présents ?

je suppose que pour l'organisation des rencontres, il faut faire jouer les triplettes contre les triplettes en priorité et s'il reste une seule triplette, la faire jouer contre une doublette. Est-ce correct ?

merci
1 membre du forum aime ce message.
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 9'722
Appréciations reçues : 446
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 2 avril 2020, 18:51

Bonjour,

pour ma part je n'ai pas encore pu dégager plusieurs heures pour m'y pencher plus... :roll:
@h2so4
oui, c'est ce que j'ai constaté.
Une seule triplette joue avec une doublette
eric
1 membre du forum aime ce message.
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Avatar du membre
Yvouille
Passionné d'Excel
Passionné d'Excel
Messages : 9'137
Appréciations reçues : 94
Inscrit le : 6 avril 2007
Version d'Excel : 2016

Message par Yvouille » 2 avril 2020, 20:18

Bonjour à tous deux,

Merci beaucoup pour vos réponses.

L’été passé, 53 joueurs différents ont participé chacun entre 1 et 52 mêlées durant la saison. Donc le nombre de membres doit être de 60 environ(certains joueurs ne participant qu’une fois chaque deux ans et d’autres à toutes les mêlées organisées). Par soirée, on est entre 20 et 50 environ selon le temps qu’il fait. Il faudrait prévoir un peu de marge si un autre club souhaitait utiliser ce travail, bien que notre club soit déjà bien grand. Est-ce possible de partir sur une base de 100 joueurs au maximum en même temps ?

http://www.cpnf.ch/images/documents/arc ... l_2019.pdf

L’idée est effectivement de faire jouer les triplettes et les doublettes entre elles et de faire jouer une triplette contre une doublette si ce n’est pas possible autrement (donc si le nombre de triplettes est impairs, mais alors le nombre de doublettes est obligatoirement également impair).

Pour rappel, mon programme trouve déjà qui doit jouer en triplette et qui doit jouer en doublette selon les personnes présentes et selon d'autres critères précis (voir mon image du 28 mars) et le problème est de ''mélanger'' ces personnes afin qu'elles ne se soient pas encore rencontrées trop souvent, ni qu'elles aient déjà joué trop souvent ensemble (ce deuxième critère étant moins important que le premier).

Cordialement.
Yvouille

Valais de Coeur
h
h2so4
Passionné d'Excel
Passionné d'Excel
Messages : 9'461
Appréciations reçues : 428
Inscrit le : 16 juin 2013
Version d'Excel : 365 UK Windows 10

Message par h2so4 » 6 avril 2020, 16:11

Bonjour Yvouille,

une proposition d'établissement d'une grille des rencontres en repartant de ta liste de joueurs répartis en joueur de triplette et joueur de doublette.

- optimisation des triplettes (par groupe de 12 joueurs), on met ensemble les joueurs de triplettes ayant le moins souvent joué ensemble
- optimisation des doublettes (par groupe de 12 joueurs), on met ensemble les joueurs de doublettes ayant le moins souvent joué ensemble
- optimisation de la grille des rencontres(par groupe de 12 équipes), on fait jouer l'une contre l'autre les équipes dont les joueurs d'une équipe ont le moins joué contre les joueurs de l'autre équipe.

L'optimisation par groupe de 12 risque de ne pas donner systématiquement la meilleure grille, mais donne une grille avec un score acceptable dans un temps très raisonnable.

Pour mes tests j'ai mis une ligne de ton code en commentaire.

'Call Enregistrer_la_Partie 'je n'ai pas regardé ce que faisait ce code.

j'ai mis la partie de mon code dans le module1. Le code est touffu en raison des optimisations pour la performance.

à tester ...
Modifié en dernier par h2so4 le 7 avril 2020, 23:59, modifié 1 fois.
1 membre du forum aime ce message.
Avatar du membre
Yvouille
Passionné d'Excel
Passionné d'Excel
Messages : 9'137
Appréciations reçues : 94
Inscrit le : 6 avril 2007
Version d'Excel : 2016

Message par Yvouille » 6 avril 2020, 22:26

Salut H2so4,

Merci infiniment pour ta réponse :) J’ai l’impression que tu as fourni un travail monstre auquel je n’ai, bien entendu, presque rien compris !

Je me suis dépêché de le tester et te fais part de mes remarques.

Le fichier a été un tout petit peu modifié afin de permettre quelques contrôles. Sur la feuille ‘’Equipes’’, j’ai placé des formules dans la plage B2:G21 afin de séparer les équipes sélectionnées par ta macro sur la feuille ‘’Rencontres’’. Ceci me permet de voir en un seul coup d’œil si tous les joueurs sont pris et s’ils ne sont pas pris deux fois (dans les colonnes J et K).

Je constate alors quelques soucis :

Tous les joueurs pris ou non
Selon le nombre de joueurs, ils sont normalement pris tous à chaque fois (de 8 à 16, de 18 à 22 et 26 joueurs), ou alors ils sont parfois tous pris, parfois non (17, 23, 27 joueurs) ou ils ne sont jamais pris tous (24, 25, 28 et plus de joueurs). Selon mes constatations, lorsqu’il manque des joueurs, il en manque 12, 13, 16 ou plus.

Joueurs pris plusieurs fois
Il arrive que des paires soient prises plusieurs fois et qu’elles jouent contre elles-mêmes ou contre d’autres équipes. Voici un exemple :
Capture plusieurs paires croisées ou non.PNG
Capture plusieurs paires croisées ou non.PNG (12.77 Kio) Vu 99 fois
En effectuant plusieurs fois le tirage avec les mêmes joueurs, ce problème arrive parfois, parfois non.

Les deux problèmes ci-dessus sont parfois séparés, parfois accumulés.


Autres remarques :

Nombre de 12 joueurs par groupe
Lors du précédent travail pour lequel tu m’avais grandement aidé, nous avions aussi limité les groupes à un maximum de 12 personnes, raison pour laquelle j’avais imaginé partir cette fois-ci sur un groupe unique. Mais tu sembles dire que ce n’est pas réaliste. A voir donc si cette nouvelle macro permettra quand même de meilleurs résultats que celle utilisé actuellement (dans un autre fichier que celui présenté ici).

Durée de la macro
Sur un ordinateur moyen à bien, ta macro dure 1 à 5 secondes en général, 15 secondes pour 25 et 31 joueurs, 30 secondes pour 43 joueurs et 1 minutes pour 50 joueurs. Si vraiment on pouvait gagner en qualité des tirages, ce serait acceptable que cela dure jusqu’au double de temps, mais quand même pas beaucoup de plus.

Nombre de triplettes
Le présent fichier n’est prévu que pour l’extérieur, c’est-à-dire qu’il ne peut jamais y avoir plus de 3 triplettes. Mais je devrais le modifier encore afin qu’il soit utilisable à l’intérieur et il pourrait y avoir plus de triplette, voire que des triplettes (je l’avais expliqué dans mes premiers messages). Est-ce important pour toi de pouvoir tester de telles situations, en d’autres mots devrais-je transformer ce fichier et ma macro afin de pouvoir simuler plus de triplettes ?
h2so4 a écrit :
6 avril 2020, 16:11
Pour mes tests j'ai mis une ligne de ton code en commentaire.
'Call Enregistrer_la_Partie 'je n'ai pas regardé ce que faisait ce code.
Ce code actualise la feuille ‘’Archives’’ afin que la dernière mêlée tirée soit prise en compte pour les tirages futurs. Mais comme ce n’est pas important pour l’instant, on peut très bien se contenter d’effectuer nos essais sur la base des seules données déjà enregistrées sur cette feuille ‘’Archives’’, ceci d’autant plus que j’ai supprimé des colonnes de la feuille ‘’Equipes’’ et que je devrais revoir cette macro ‘’Enregistrer la partie’’.

Encore une fois un tout grand merci pour ton aide.

Sincèrement.
essai tirage yvouillev6_BIS.xlsm
(107.57 Kio) Téléchargé 1 fois
Yvouille

Valais de Coeur
h
h2so4
Passionné d'Excel
Passionné d'Excel
Messages : 9'461
Appréciations reçues : 428
Inscrit le : 16 juin 2013
Version d'Excel : 365 UK Windows 10

Message par h2so4 » 7 avril 2020, 00:00

bonsoir,

je me doutais bien que des bugs allaient sortir.

voici une nouvelle version.

L'alternative aux groupes de 12 c'est un algorithme génétique. Je m'y attaque dès que j'ai l'occasion. ceci dit je pense que la solution proposée (quand il n'y a pas de bugs) semble sortir des scores intéressants. tu peux voir le score de la grille sélectionnée en D1 de la feuille rencontres. j'ai fait les vérifications pour quelques grilles et ce score semble correct.

ce score est la somme des rencontres jouées par chaque joueur avec les autres joueurs de son équipe + la somme des rencontres jouées par chacun des joueurs d'une équipe contre chacun des joueurs de l'équipe adverse.
essai tirage yvouillev7.xlsm
(110.72 Kio) Téléchargé 4 fois
1 membre du forum aime ce message.
h
h2so4
Passionné d'Excel
Passionné d'Excel
Messages : 9'461
Appréciations reçues : 428
Inscrit le : 16 juin 2013
Version d'Excel : 365 UK Windows 10

Message par h2so4 » 7 avril 2020, 15:25

Bonjour Yvouille,

voici une solution avec un algorithme génétique (semble plus efficace que mes tentatives précédentes, contrairement à ce que mon intuition me laissait penser).
Modifié en dernier par h2so4 le 7 avril 2020, 23:58, modifié 1 fois.
1 membre du forum aime ce message.
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message