Formule de calcul pour tirage au sort
Bonjour à tous, Forum,
En tant que bénévole, j'aide une association d'handicapés à compter les points lors de leurs concours annuel de belote coichée. Il faisait tout à la main avant et j'ai donc créé une feuille excel pour leur simplifier la vie et surtout réduire le temps du comptage de points...
Pour tout ce qui est calculs, je n'ai pas trop de problème mais je rencontre une difficulté pour automatiser le tirage au sort des parties. Je m'explique :
Le concours se déroule en 3 parties (de 12 mènes). Pour le tirage de la 1ère partie, c'est facile... On fait le 1er inscrit contre le dernier, le 2ème contre l'avant dernier et ainsi de suite... (j'ai oublié de préciser qu'il y a toujours un nombre pair d'équipes...).
Là où cela se corse, c'est pour le tirage de la 2ème et 3ème partie. En effet, nous faisons rencontrer les vainqueurs entre eux que nous savons identifier dans la feuille "Résultats". Mais ensuite, je n'arrive pas à trouver la formule qui va bien à mettre dans la feuille "Tirage" pour que tout se fasse automatiquement.
Bonne journée à tous.
Fabrice,
Bonjour fabrice,
Il doit y avoir quelques âmes charitables sur le forum
Pour le 2è tirage, voici une solution avec 4 formules à mettre sur la feuille "Tirage" et à valider par CTRL+MAJ+ENTRÉE :
En O3 :
=SI(LIGNE(1:1)<=6;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="OUI")*Résultats!$BC$10:$BC$33;12-2*LIGNE(1:1)+2);"")En R3 :
=SI(LIGNE(1:1)<=6;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="OUI")*Résultats!$BC$10:$BC$33;12-2*LIGNE(1:1)+1);"")En U3 :
=SI(LIGNE(1:1)<=6;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="NON")*Résultats!$BC$10:$BC$33;12-2*LIGNE(1:1)+2);"")En X3 :
=SI(LIGNE(1:1)<=6;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="NON")*Résultats!$BC$10:$BC$33;12-2*LIGNE(1:1)+1);"")A noter que cette formule ne marche que pour 24 participants ; elle peut peut-être généralisée. De plus, elle ne prend pas en compte le cas où il y aurait ni perdant, ni gagnant (c'est possible ? Je ne sais pas jouer à la belote, qui plus est coinchée
Pour le 3è tirage, c'est où qu'on doit mettre les formules ?
Re- le forum,
Merci (encore) de ton aide vba-new...
Super pour les formules qui fonctionnent parfaitement. Le seul petit "hic", c'est qu'au dernier concours il y avait 92 équipes... Il faudrait donc que je puisse adapter pour au moins 128 équipes (la belote coinchée est très prisée dans le Beaujolais... Surtout chez nos anciens). C'est pourquoi j'ai apporté mon aide à cette assoc. A la main, il ne s'en sortait plus.
D'autre part, il y a forcément un gagnant et un perdant (l'égalité n'est pas possible)...
Concernant le 3e tirage, je récupérais les tirages précédents (colonnes AJ, AK, AL) et je faisais le tirage manuellement (colonne AO) en prenant garde que les équipes ne se rencontrent pas deux fois... Car c'est là la difficulté supplémentaire... Les équipes ne doivent jamais se rencontrer deux fois.
J'espère que tu me trouveras une formule magique...
Merci encore.
Fabrice,
Re,
J'avais un peu prévu le coupvba-new a écrit :A noter que cette formule ne marche que pour 24 participants ; elle peut peut-être généralisée
Voici une solution plus générale :
En O3 :
=SI(LIGNE(1:1)<=MAX(participants)/4;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="OUI")*Résultats!$BC$10:$BC$33;MAX(participants)/2-2*LIGNE(1:1)+2);"")En R3 :
=SI(LIGNE(1:1)<=MAX(participants)/4;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="OUI")*Résultats!$BC$10:$BC$33;MAX(participants)/2-2*LIGNE(1:1)+1);"")En U3 :
=SI(LIGNE(1:1)<=MAX(participants)/4;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="NON")*Résultats!$BC$10:$BC$33;MAX(participants)/2-2*LIGNE(1:1)+2);"")En X3 :
=SI(LIGNE(1:1)<=MAX(participants)/4;GRANDE.VALEUR((Résultats!$BE$10:$BE$33="NON")*Résultats!$BC$10:$BC$33;MAX(participants)/2-2*LIGNE(1:1)+1);"")Sachant que "participants" est la plage contenant les participants (feuille "Inscriptions"). Il faut que la saisie des participants soit rigoureuse, c'est-à-dire qu'il faut inscrire les équipes dans l'ordre croissant des numéros. Si une équipe n'existe pas, ne pas mettre de numéro.
Pour le 3è tirage, ça m'a l'air un peu compliqué
Re-,
Mais il fallait que le nombre de doublettes soit un nombre divisible par 4. Ce qui semble être le cas puisque 24/4=6 ; 92/4=23 ; 128/4=32
Aïe ! Ca pose un problème car ce ne sera pas forcément un multiple de 4. Les gens viennent et s'inscrivent au fil de l'eau. Nous faisons en sorte que le nombre d'équipe soit un nombre "pair" en complétant avec une doublette des organisateurs s'il y a lieu (si 75 équipes inscrites ---> les organisateurs forment une dernière équipe pour faire 76 par exemple...).
Il faut que la saisie des participants soit rigoureuse, c'est-à-dire qu'il faut inscrire les équipes dans l'ordre croissant des numéros.
Pour cela, pas de souci puisque c'est moi qui saisit les équipes au fil de l'eau et que le numéro est attribué automatiquement (formule ligne(-1) + 1).
Sinon, tes formules pour le 2e tirage semblent fonctionner parfaitement et je vais tenter de les comprendre...
Merci encore à toi.
Amicalement.
Fabrice,
En fait le problème ne vient pas tout à fait de ma formule mais du jeu en lui-même ou bien il y a une règle que je ne connais pas (?).Fabrice69 a écrit :Mais il fallait que le nombre de doublettes soit un nombre divisible par 4. Ce qui semble être le cas puisque 24/4=6 ; 92/4=23 ; 128/4=32
Aïe ! Ca pose un problème car ce ne sera pas forcément un multiple de 4. Les gens viennent et s'inscrivent au fil de l'eau. Nous faisons en sorte que le nombre d'équipe soit un nombre "pair" en complétant avec une doublette des organisateurs s'il y a lieu (si 75 équipes inscrites ---> les organisateurs forment une dernière équipe pour faire 76 par exemple...).
Imaginons que nous n'ayons pas un nombre multiple de 4. Exemple : 18 équipes. Tu m'as dit qu'il ne peut y avoir que des perdants et des gagnants. Donc à la fin de la première partie, on aura 9 équipes gagnantes et 9 perdantes.
Ensuite, tu m'as dit qu'à la 2è partie, les gagnants se rencontrent entre eux et les perdants entre eux. Mais comme on a un nombre impair de gagnants et perdants, il restera 1 équipe de gagnant qui ne rencontrera personne, idem pour les perdants. Voici un exemple des équipes gagnantes qui se rencontrent : 1 contre 2, 3 contre 4, 5 contre 6, 7 contre 8, 9 contre ???
C'est bon en fait ! Je n'avais pas vu qu'il y avait des formules dans la feuille InscriptionsFabrice69 a écrit :Il faut que la saisie des participants soit rigoureuse, c'est-à-dire qu'il faut inscrire les équipes dans l'ordre croissant des numéros.
Pour cela, pas de souci puisque c'est moi qui saisit les équipes au fil de l'eau et que le numéro est attribué automatiquement (formule ligne(-1) + 1).
Pour le 3è tirage, essaie en mettant cette formule en AM3 de la feuille "Tirage" :
=SI(RECHERCHEV(CNUM(GAUCHE(AK3;TROUVE("-";AK3)-2));Résultats!$BC$10:$BH$33;6;FAUX)="oui";GAUCHE(AK3;TROUVE("-";AK3)-2);DROITE(AK3;NBCAR(AK3)-TROUVE("-";AK3)-1))Cette formule est à tirer vers la droite (jusqu'à la colonne "AN") et vers le bas. Elle ne prend pas en compte (pour l'instant ?) le cas où les équipes se sont déjà rencontrées mais je voulais juste savoir si c'est bien comme ça que fonctionne le 3è tirage. Dis-moi si c'est pas ça.
-- 23 Fév 2010 19:05 --
Une bonne façon d'essayer de les comprendre c'est d'utiliser l'outil d'Audit de formules. Tu cliques sur la cellule contenant la formule puis tu vas dans Outils / Audit de formules / Évaluation de formule. Très utile ce machin là !Fabrice69 a écrit :Sinon, tes formules pour le 2e tirage semblent fonctionner parfaitement et je vais tenter de les comprendre...
Bonjour à tous, Forum, vba-new,
il restera 1 équipe de gagnant qui ne rencontrera personne, idem pour les perdants.
Tu as tout à fait raison vba-new et c'est une des raisons pour laquelle je n'arrive pas à automatiser totalement les tirages... En fait, si je garde ton exemple, je faisais donc gagnants contre gagnants et perdants contre perdants et je faisais se rencontrer les deux équipes qui restaient en vérifiant bien qu'elles n'avaient pas déjà joué l'une contre l'autre au 1er tour.
J'ai copié la formule pour le 3e tirage et il me semble qu'elle ne tient pas compte du nombre de parties gagnées... En effet, il faut, en priorité, que les équipes qui ont gagné 2 parties se rencontrent entre elles. Puis, on poursuit le tirage sans tenir compte du nombre de parties gagnées mais en prenant garde que les équipes ne se soient pas rencontrées au 1er tour.
En schématisant :
1er tour : tirage par ordre d'inscription (1er inscrit contre le dernier inscrit, 2e contre l'avant dernier et ainsi de suite).
2e tour : gagnants contre gagnants et perdants contre perdants et s'il reste 2 équipes on les fait se rencontrer en vérifiant que ce n'était pas déjà le cas au 1er tour.
3e tour : les gagnants de 2 parties entre eux puis, un tirage aléatoire en vérifiant que les équipes ne se soient pas rencontrées dans les 2 précédents tours.
Voilà, j'espère que je suis assez clair car pas facile à expliquer. Cela fait plusoeurs années que je donne un coup de main à cette association et que j'améliore au fur et à mesure mon projet... Mais j'avoue que cela fait très longtemps que je cherche une solution à ce
Je te remercie encore, vba-new de l'intérêt que tu portes à mon problème.
Amicalement.
Fabrice,
Bonjour fabrice, forum,
Pas facile, pas facile !
Je ne trouve pas de solution par formule pour le 3è tirage. Je suis en train de voir pour une solution en vba. Ça ne te dérange pas ?
Par contre, une question :
Donc, après avoir fait se rencontrer tous les gagnants de 2 parties, si une équipe qui n'a gagné aucune fois rencontre une équipe qui a gagné une seule fois, ce n'est pas grave ?Fabrice69 a écrit :3e tour : les gagnants de 2 parties entre eux puis, un tirage aléatoire en vérifiant que les équipes ne se soient pas rencontrées dans les 2 précédents tours.
Bonjour vba-new, Forum,
NON ! Cela ne me dérange pas du tout. Même si je ne maitrise pas parfaitement le VBA, je sais au moins le faire fonctionner...Je ne trouve pas de solution par formule pour le 3è tirage. Je suis en train de voir pour une solution en vba. Ça ne te dérange pas ?
Non, ce n'est pas grave du tout du moment qu'elles ne se sont pas rencontrées lors des tours précédents.Donc, après avoir fait se rencontrer tous les gagnants de 2 parties, si une équipe qui n'a gagné aucune fois rencontre une équipe qui a gagné une seule fois, ce n'est pas grave ?
Si même TOI, tu me dis que ce n'est pas facile
Merci encore à toi.
Amicalement.
Fabrice,
Salut fabrice,
Pfiouu ! On peut dire que j'ai galéré pour te pondre une macro qui fasse un tirage aléatoire !
Je joins le fichier sur lequel j'ai fait les tests. Il te suffit d'exécuter la macro "Lancer tirage".
Si tu rencontres des erreurs, dis-moi lesquelles et à quelle ligne du code.
Fais des tests fabrice. J'ai testé avec 24 équipes et ça a l'air de marcher. Pas sûr que ça marche pour un nombre d'équipe non divisible par 4.
Il est également très important que tu ne changes rien à la disposition des colonnes. Car c'est une macro qui est totalement adaptée à la structure actuelle de ton classeur.
Amicalement
Bonjour vba-new, Forum,
Quel boulot ! Un grand merci à toi vba-new. Je n'imaginais pas tout ça pour un tirage au sort...
J'ai testé avec 66 doublettes et la 1ere fonction matricielle (colonne O de l'onglet Tirage) ne fonctionne pas. Du coup, je n'ai pas pu tester la macro...
C'est en rajoutant des noms que l'erreur est apparue.
Je suis allé également remettre les formules en colonne BE et BH de l'onglet Résultats où tu les avais remplacées, pour des tests je pense, par la valeur "oui".
Je te renvoie le fichier avec les 66 doublettes d'inscrites et le résultat de la 1ere partie si tu veux tester de ton côté.
Merci encore pour ton aide précieuse.
Amicalement.
Fabrice,
Bonjour fabrice, forum,
C'est bon c'est réglé, avec un champ dynamique.Fabrice69 a écrit :J'ai testé avec 66 doublettes et la 1ere fonction matricielle (colonne O de l'onglet Tirage) ne fonctionne pas. Du coup, je n'ai pas pu tester la macro...
Ton fichier en retour.
Pour que la macro marche normalement, j'ai dû mettre des résultats bidons pour la 2è partie. Tu n'oublieras pas de remettre tes formules.
Dans la feuille Tirage, colonne AP, j'ai mis une formule pour tester si les équipes se sont déjà rencontrées. Si tout se passe bien, il devrait y avoir "Les équipes xx - xx se sont rencontrées 0 fois."
En colonne AR:AS, c'est pour voir si toutes les équipes sont bien présentes lors du 3è tirage. Si elles apparaissent plus d'une fois, c'est pas bon. Ça veut dire que la même équipe est en train de jouer contre plus d'un adversaire à la fois (je pense que c'est pas possible, si ?)...ou alors elle joue contre elle-même...ou les 2
Bon ça devrait pas arriver souvent si la macro est bien construite
Je te laisse faire des tests. Dis-moi si tu rencontres un problème.
PS : Apparemment la macro marche même pour un nombre d'équipe non divisible par 4. C'est presque tout à fait fortuit
Amicalement
Bonjour à tous, Forum, vba-new,
Désolé de t'embêter encore mais pour le 2e tirage, il me manque 2 équipes sur les 66 inscrites. En effet, je ne retrouve nulle part les équipes 63 et 66. J'ai beau chercher, je n'arrive pas à voir ce qui cloche dans les formules matricielles...
On y est presque mais je ne suis pas allé plus loin pour être sûr de tester la macro avec toutes les équipes...
Merci encore à toi.
Amicalement.
Fabrice,
Re,
Y'a pas de mal ! Faut bien qu'on (essaie) de finir ce qu'on a commencé non ?Fabrice69 a écrit :Désolé de t'embêter encore...
Voilà le problème d'un nombre d'équipes non multiple de 4 ! Il reste une équipe gagnante et une équipe perdante. J'ai changé les formules matricielles (colonnes O et R) pour que les 2 équipes restantes jouent entre elles. Bien que l'équipe 63 a perdu, je la mets quand même dans la colonne R. Ça me simplifie les choses.Fabrice69 a écrit :...mais pour le 2e tirage, il me manque 2 équipes sur les 66 inscrites. En effet, je ne retrouve nulle part les équipes 63 et 66. J'ai beau chercher, je n'arrive pas à voir ce qui cloche dans les formules matricielles...
Voici ton fichier en retour. Toujours la même macro tirageAlea à lancer.
Remarque : Si le nombre d'équipe est impair, n'oublie pas d'en rajouter une.
Bonsoir à tous, Forum,
vba-new, la macro s'exécute bien mais ne tient visiblement pas compte du nombre de parties gagnées. En effet, en priorité, il faut que les gagnants des 2 premières parties se rencontrent... Et si le nombre de gagnants de 2 parties est un nombre impair, alors on fait jouer la doublette qui reste contre une doublette qui n'a gagné qu'une partie (en veillant à ce qu'elles ne se soient pas rencontré lors des tirages précédents...).
Ne t'inquiète pas, c'est ce que je fais...Remarque : Si le nombre d'équipe est impair, n'oublie pas d'en rajouter une
Pas facile ce tirage au sort !!! Sincèrement, un énorme merci à toi pour ton aide.
Amicalement.
Fabrice,
Salut fabrice, forum,
??! C'est que j'ai rien compris alors ?! Qu'est-ce qui te fait dire ça ? Où est le problème ? Peux-tu illustrer stp ?Fabrice69 a écrit :vba-new, la macro s'exécute bien mais ne tient visiblement pas compte du nombre de parties gagnées.
Si tu me dis ça c'est que les formules dans les colonnes O et R sont fausses ?! Ou plutôt ne font pas ce que tu veux ?Fabrice69 a écrit :En effet, en priorité, il faut que les gagnants des 2 premières parties se rencontrent...
Bon faut qu'on tire tout ça au clair. Dans le fichier joint plus haut, il faut que tu me dises pourquoi la macro ne tient visiblement pas compte du nombre de parties gagnées (macro qui travaille à partir des colonnes O et R). Quel est le tirage attendu ?
PS : Mes interventions seront un peu plus disparates, ayant repris le chemin de l'école
Amicalement
Bonjour à tous, Forum, vba-new,
Alors bon courage à toi et donne priorité à tes études...ayant repris le chemin de l'école
Les formules matricielles pour le 2e tirage (colonnes O, R, U, X) ont l'air de très bien fonctionner.
Pour tester, j'ai donc saisi un résultat pour la 2e partie et je me retrouve avec 17 équipes ayant gagné 2 parties chacunes (colonne AC, AD).
La macro doit, en priorité, faire se rencontrer ces 17 équipes entre elles (16 en fait pour avoir un nombre pair, et celle qui reste jouant contre une équipe qui n'a remporté qu'une partie (colonne AE, AF)).
En lançant la macro, je constate que l'équipe 8 (qui a gagné 2 parties) rencontre l'équipe 5 (qui n'a gagné qu'une partie) ou encore que l'équipe 20 (qui a gagné 2 parties) rencontre l'équipe 16 (qui n'a gagné qu'une partie)...
Je ne m'inquiète pas du tout car je sais bien qu'au final on (tu) trouvera la solution... Le seul truc, le prochain concours est pour vendredi 5 mars prochain... Si je n'ai pas de solution d'ici là, je ferai le 3e tirage à la main en faisant attention que les équipes ne se soient jamais rencontrées.Donc ne t'inquiète pas si mon temps de réponse est un peu long. J'espère que ça ne vous impactera pas trop, ton association et toi
Merci à toi pour ton aide mais, encore une fois, il ne faut pas que mes carences en excel prennent le pas sur tes études...
Amicalement.
Fabrice,
Bonjour à tous, Forum,
J'ai re-testé, hier soir, avec seulement 6 équipes et je me suis aperçu que la première formule matricielle ne fonctionnait pas. Elle ne fait pas jouer les gagnants de la 1ère partie entre-eux...
Le concours débute à 14 heures cet après-midi et je vais donc faire avec mes anciens fichiers. Mais, par contre, pour le prochain (au mois d'octobre), je souhaiterais vraiment simplifier cette histoire de tirage au sort.
Mon cher vba-new, cela nous laisse un peu plus de temps pour tenter de trouver la meilleure solution.
Peut-être que les bases ne sont pas bonnes non plus et je suis prêt à tout modifier s'il le faut pour arriver à un bon résultat.
Bonne journée à tous.
Amicalement.
Fabrice,
Salut fabrice, forum,
Désolé de n'avoir pu te répondre avant mais j'ai un peu difficilement accès à internet pendant la semaine
Bon bah c'est râpé pour le concours d'aujourd'hui (j'espère qu'il s'est bien passé !
Est-ce que par hasard tu te baserais sur la colonne I de la feuille Tirage pour dire ça ? Parce que moi j'ai fait les formules à partir de la feuille Résultats comme tu l'as stipulé lors de ton tout premier message :Fabrice69 a écrit :J'ai re-testé, hier soir, avec seulement 6 équipes et je me suis aperçu que la première formule matricielle ne fonctionnait pas. Elle ne fait pas jouer les gagnants de la 1ère partie entre-eux...
Fabrice69 a écrit :Là où cela se corse, c'est pour le tirage de la 2ème et 3ème partie. En effet, nous faisons rencontrer les vainqueurs entre eux que nous savons identifier dans la feuille "Résultats".
Ah effectivement, y'a le temps d'ici là ! Mais qu'entends-tu par simplifier fabrice ? Car lorsque les formules et macros seront en place et au point, tu n'auras plus rien à faire !Fabrice69 a écrit :Mais, par contre, pour le prochain (au mois d'octobre), je souhaiterais vraiment simplifier cette histoire de tirage au sort.
Mon cher vba-new, cela nous laisse un peu plus de temps pour tenter de trouver la meilleure solution.
Oula fabrice ! Tu me surestimes un petit peu tropFabrice69 a écrit :Peut-être que les bases ne sont pas bonnes non plus et je suis prêt à tout modifier s'il le faut pour arriver à un bon résultat.
Concernant ton problème, j'attends ta réponse et on corrige la formule.
Bonjour à tous, vba-new, forum,
Le concours s'est déroulé hier après-midi sans trop de contrariété même si je trouve les gens de moins en moins patients et de plus en plus agressifs. Mais rien concernant la gestion des résultats que j'ai menée de mains de maitre.
C'est vraiment le tirage au sort qui me pose le plus de problèmes car je suis obligé de naviguer dans la feuille pour vérifier que les équipes ne se sont pas déjà rencontrées...
Bref, tu as raison, je ne vais pas repartir de zéro et j'espère bien que nous allons trouver la solution à ces 2 tirages au sort (car, en fait, ce ne sont que les 2e et 3e tirage qui posent souci...). Et c'est bien cela que j'appelle "simplifier" car, effectivement, après, je n'aurai plus grand chose à faire...
Je suis au boulot ce matin et je ne dispose pas du fichier. Mais je regarde ça dans le week-end (ces histoires de colonnes dans la bonne feuille) et te tiens au courant.
Merci encore à toi pour ton aide.
Amicalement.
Fabrice,