Tri aléatoire avec conditions

[jacky49]

Bonjour le forum,

je cherche à faire un tri aléatoire du tableau A6 à H105 en 2 séries avec comme conditions que 2 personnes du même club ne se trouve pas dans la même série sauf s'ils sont plus de personnes que de séries.
La dans le tableau, j'ai 23 personnes donc 1 séries de 12 personnes et une série de 11 personnes et si possible dans un autre onglet nommé séries que j'ai mis dans le fichier joint et j'ai fait les 2 tableaux avec les exemples fait en manuels
merci de votre aide
Jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Rebonsoir

Il faut essayer à partir du fichier 9 de mon précédent post.

@ plus

 

[jacky49]

Re : Tri aléatoire avec conditions

Re,

Donc, j'a fait comme tu as dit, a partir du fichier 9, j'ai recopier les formules une par une puis j'ai ajouté le signe = et remis les formules matricielles en colonnes C et L et je les ai recopier jusqu'à la ligne 56, cela fonctionne sauf que les tris ne sont plus égaux, j'ai un club qui a 5 personnes mais seulement 1 est en série 1 et les 4 autres en séries 2
merci
jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonjour

C'est un autre problème : Dans ton premier post, tu n'as pas demandé que les joueurs d'un même club soient à peu près répartis dans les deux séries, tu as demandé que si il y en a deux, ils soient dans les deux séries

Bonjour le forum,

je cherche à faire un tri aléatoire du tableau A6 à H105 en 2 séries avec comme conditions que 2 personnes du même club ne se trouve pas dans la même série sauf s'ils sont plus de personnes que de séries.
Jacky

J'avais bien pensé que cela serait mieux de répartir les membres d'un même club sur les deux séries (Par exemple, si il sont 10 du même club, 5 dans une série, 5 dans l'autre. Si ils sont 11 du même club, 5 dans une série, 6 dans l'autre...) mais comme cela nécessite une formule plus compliquée...

Avec la formule actuelle, tu peux très bien avoir, pour 10 membres du même club, 1 dans une série, et 9 dans l'autre (et même les 10 dans la série 2 puisque cette dernière prend tous les noms non sélectionnés en série 1, donc le critère que tu as défini dans ton premier post n'est pas toujours vérifié (Pour éliminer ce cas, il suffit de refaire un ou deux tirages, mais cela veut dire qu'il faut contrôler la répartition des membres, club par club, au besoin à l'aide d'une formule)).

Je vais essayer de trouver une formule répartissant mieux, automatiquement, les membres d'un même club. Pas évident. Il faut modifier la partie NB.SI(Feuil1!D$7: D$105;Feuil1!D$7: D$105)>2, et pour le moment, je ne vois pas trop comment !

@ plus

 

[CISCO]

Re : Tri aléatoire avec conditions

Rebonjour

Une possibilité en pièce jointe. Ce n'était pas si compliqué que cela. J'ai juste remplacé

(NB.SI(Feuil1!D$7: D$105;Feuil1!D$7: D$105)>2)

par

(NB.SI(Feuil1!D$7: D$105;Feuil1!D$7: D$105)/2>NB.SI(D$6: D6;Feuil1!D$7: D$105))

en C7.

NB.SI(Feuil1!D$7: D$105;Feuil1!D$7: D$105) compte le nombre de membres, club par club, dans la liste en feuil1.
On divise par deux pour trouver la moitié, histoire de se fixer une limite.

NB.SI(D$6: D6;Feuil1!D$7: D$105) compte le nombre de membres, club par club, déjà sélectionnés en série 1.

Si

(NB.SI(Feuil1!D$7: D$105;Feuil1!D$7: D$105)/2>NB.SI(D$6: D6;Feuil1!D$7: D$105))

renvoie VRAI, c'est qu'on a moins de la moitié des membres du club en série 1, et qu'on peut continuer à sélectionner des membres de ce club. Si cette partie de la formule renvoie FAUX, c'est qu'on ne peut plus sélectionner des membres de ce club.

Recopie juste la formule en C7, sans le =, colle là dans ton fichier réel. Rajoute le =. Valide la formule avec Ctrl+maj tempo+enter. Puis tire cette formule vers le bas.

Pour vérifier la validité de cette formule, rempli la colonne Feuil1!D juste avec 3 ou 4 clubs, et contrôle la répartition.

Cette formule ne garantie pas une répartition équilibrée des membres d'un même club dans les deux séries. Le hasard peut toujours faire que les membres d'un club ne soient pas sélectionnés dans la série 1 et donc se retrouvent presque tous dans la série 2. Autrement dit, la formule empêche qu'il y ait plus de la moitié+1 des membres d'un club qui se retrouve dans la série 1. Elle n'empêche pas qu'il y en ait moins de la moitié +1.

Pour obtenir un équilibrage parfait, il faudrait que la formule en colonne C tienne aussi compte des résultats déjà obtenus en colonne L, dans les lignes au dessus, et réciproquement. A voir.

@ plus

P.S : J'ai rajouté un espace devant certains D, pour ne pas avoir de .

 

[CISCO]

Re : Tri aléatoire avec conditions

Rebonjour à tous

En pièce jointe, j'ai rajouté à droite un petit tableau contrôlant la répartition. En T7, il y a une formule matricielle permettant de lister automatiquement les clubs de la feuille 1. Les colonnes série 1 et série 2 doivent avoir des nombres proches l'un de l'autre 5 (ce n'est pas le cas dans l'exemple car je n'ai pas tiré les formules jusqu'à la ligne 56 dans les tableaux série 1 et série 2, à gauche). Si ce n'est pas le cas, il faut refaire un tirage.

Pour ce qui est d'avoir une formule "parfaite", donnant une répartition parfaite à chaque fois, en C, tenant compte des résultats déjà affichés en L, cela me semble trop compliqué... Avec une macro, c'est faisable, mais je ne sais pas le faire.

@ plus

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonjour Le forum, Cisco,

j'essaie ce soir tes fichier et je te redis mais c'est vrai que si c'est mieux en macro, j'espère que quelqu'un va se pencher sur mon problème
merci d'avance
jacky

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonsoir le forum, Cisco,

je te joins le fichier essai séries 11, j'ai changé les noms de club et il est vrai que même si je n'ai que 2 club du même nom, il les mets tous les deux en série 2 et sur le fichier que je te joins, j'ai un # NA sur la ligne 7 dans le tableau série 1 et dans mon fichier original, c'est une ligne vide pourquoi ??? je ne sais pas
merci
jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonsoir

Le fichier 11 ne donne les bons résultats que si il y a des noms jusqu'à la ligne 105. Si ce n'est pas le cas, comme dans le dernier exemple que tu as mis sur le forum, cela pose problème.

En pièce jointe, j'ai complété ton dernier fichier jusqu'à la ligne 105, avec seulement 2 clubs, le A et le B. Tu verras, cela fonctionne et la répartition est équilibrée, comme le montre le tableau à droite.

Si la liste de noms ne va pas toujours jusqu'à la ligne 105, il faut mieux faire avec des noms définis avec des DECALER, comme je te le proposais dans mes premiers fichiers (mais il faut que les cellules en dessous des derniers noms soient vraiment vides). Mais dans ce cas, il faut transposer les formules et les définitions des noms (Cf. le gestionnaire de noms) des fichiers que je te propose sur ton fichier réel, ou le contraire copier les formules de ton fichier réel sur mes propositions.

Conclusion : As-tu toujours des noms jusqu'à la ligne 105, ou non ?

@ plus

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonjour le forum, Cisco,

Non, je n'ai pas toujours des noms jusqu'à la ligne 105 car cela varie suivant le nombre d'inscrits mais par contre, j'ai des formules dans toutes les colonnes jusqu'à la ligne 105 car comme je te l'ai dit , il s'alimente suivant un fichier inscriptions
j'espère que cela ne posera pas de problème
merci d'avance de ton aide
jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonjour

J'ai repris ton fichier 8 pour avoir les mêmes conditions que toi (des cellules qui paraissent vides, mais qui ne le sont pas réellement). J'ai évalué en Séries!A2 la hauteur des plages (variable, puisque le nombre de noms change d'un cas à l'autre), non plus avec des EQUIV("zz";...;1) qui donnent la position de la dernière ligne non vide, mais ne fait pas la différence entre du texte et une formule donnant une cellule vide, mais avec un

=SOMME((NBCAR(Feuil1!C$7:C$105)>1)*1)

en matriciel (ESTTEXTE ou NBVAL ne conviennent pas car ils ne font pas la différence entre une cellule contenant vraiment quelque chose, du texte ou un nombre, et celles contenant des formules, apparemment vides). Donc en Séries!A2 on a le nombre d'inscrits.

Les noms sont maintenant définis avec des DECALER(....;;;Séries!$A$2) au lieu des DECALER(....;;;EQUIV("zz";...;1))

Apparemment, ça fonctionne, quelle que soit la longueur de la liste de noms, tant qu'elle ne va plus loin que la ligne 105.

Les noms sont tirés au hasard pour créer la série 1. Comme écrit précédemment, cela n'est pas parfait car le hasard peut faire que peu de noms d'un même club soit mis dans cette série, et donc beaucoup dans la série 2. Il faut donc, par précaution, vérifier la répartition obtenue dans le tableau de droite (les nombres des deux dernières colonnes doivent être très proche l'un de l'autre). Les quelques cas que j'ai traité semblent donner presque toujours des répartitions équilibrées.

Pour faire le transfert :
* Soit tu copies ta plage Feuil1!A7:H105 de ton fichier et tu la colles sur Feuil1!A7:H105 du fichier ci-joint
* Soit tu copies les formules, y compris celle en Séries!A2, et tous les noms de la pièce ci-jointe dans ton fichier réel.

@ plus

P.S : J'ai essayé de mettre des formules donnant systématiquement des répartitions équilibrées des membres d'un même club, en C7 et L7, en mettant du ALEA dans chacunes, mais pour le moment, cela ne convient pas.

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonsoir le forum, Cisco,

je viens donc de copier le tout dans mon fichier original et cela fonctionne très bien, c'est vrai qu'il faut refaire le tirage pour arriver au meilleur équilibrage des séries mais cela fonctionne pour le moment
merci beaucoup de tout ce que tu as fait, bien sur si on peut l'améliorer , je suis preneur
jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonjour

Si ça fonctionne, c'est le principal.

J'essayerai de trouver une solution donnant une répartition équilibrée à chaque fois dès que j'aurai un peu plus de temps. Reviens faire un tour sur le forum de temps en temps pour voir où j'en suis, ou si quelqu'un d'autre a une autre proposition intéressante.

@ plus

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonjour le forum, Cisco,

oui pas de problème, je viendrais voir régulièrement
merci beaucoup
jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonsoir

J'ai cherché une méthode permettant d'obtenir systématiquement une répartition mieux équilibrée. Je ne suis pas certain d'y être arrivé... Cf. une méthode possible en pièce jointe.

Le principe : Sur une ligne donnée, la formule dans la colonne C tire au hasard un nom pour créer la série 1. Ce membre est du club X. Si il y a des membres de ce club X non sélectionnés dans les lignes précédentes, la formule de la colonne L, sur la même ligne, tirera systématiquement un de ces membres pour compléter la série 2. Et ainsi de suite. Cela se voit bien sur le fichier car on a très souvent à gauche et droite, en C et en L, le même club sur une ligne.

Donc, il y a à peu près le même nombre de membres de chaque club dans la série 1 et dans la série 2.

A toi de tester davantage en vérifiant à chaque fois la répartition dans le tableau de droite.

@ plus

P.S1 : J'ai aussi mis une mise en forme conditionnelle pour mettre en évidence les noms qui seraient tirés dans les 2 séries (ce qui normalement ne devraient pas arriver). Si des cellules apparaissent en rouge, c'est qu'il y a un problème et que les formules ne sont pas bonnes !

P.S2 : Si il y a des homonymes (même nom et même prénom), il faut absolument les différencier, par exemple en rajoutant un nombre après le prénom, FEST jean1, FEST jean2 par exemple.

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonsoir le forum, Cisco,

je viens de faire plusieurs essais, et c'est très concluant vu que si un club est mal partagé, on refait le tirage et pour le moment en 3 essais cela fonctionne
merci beaucoup