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

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonjour le forum,

j'ai fait les 2 tableaux avec les exemples fait en manuels
merci de votre aide
Jacky

Juste pour dire que les 2 tableaux ne sont que des exemples, j'ai mis au hasard des personnes du même club dans chaque séries
le principe est celui ci, si il y a 2 personnes du même club alors un ira dans la série 1 et l'autre dans la série 2 mais s'ils sont 5 , 3 iront dans une série et 2 dans l'autre mais il n'y a pas d'importance que ce soit les 3 premiers dans la série 1 et les 2 suivant dans la série 2
j'espère avoir été un peu plus clair
merci
Jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonjour à tous

Une possibilité en pièce jointe (A vérifier bien sûr).

J'ai rajouté une colonne intermédiaire E en feuille 1, donnant le nombre d'inscrits de chaque club.

En feuille 2, la formule importante est en colonne C. Elle est matricielle, donc à valider avec les 3 touches Ctrl, maj tempo et enter. Elle permet de tirer au hasard des noms pris dans la colonne C de la feuille 1, ce qui donne la série 1. La série 2 comprend tous les autres noms, donnés eux aussi grace à une formule matricielle. Les autres colonnes utilisent toutes des INDEX(....;EQUIV(....;0)). J'espère que tu comprendras assez pour remplir les autres colonnes.

J'ai utilisé des noms définis avec des DECALER, mais si tu as toujours exactement le même nombre de lignes, on peut faire plus simple.
Par exemple, j'ai fait pour la plage noms, avec

DECALER(Feuil1!$C$7;;;EQUIV("zz";Feuil1!$C:$C;1)-6)

mais on aurait aussi pu faire avec feuil1!C$7:C$29 dans ce fichier.

Si ces divers noms te posent problème, je peux te renvoyer un fichier avec des plages écrites en dur.

@ plus

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonjour à tous

La "même" mais sans colonne intermédiaire.

@ plus

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonjour à tous, Cisco,

je viens d'essayer de mettre tes formules et les noms que tu as données mais j'ai des #REF donc je vais chercher pourquoi et j'ai une question , que veut le "zz" dans la formule DECALER(Feuil1!$C$7;;;EQUIV("zz";Feuil1!$C:$C;1)-6)
merci
jacky

 

[jacky49]

Re : Tri aléatoire avec conditions

Re le forum, Cisco,

oui j'ai toujours le même nombre de ligne qui va jusqu'à la ligne 105
mais j'ai toujours un problème pour recopier sur mon fichier d'origine
j'ai joint une capture d'image pour voir ce que j'ai
merci d'avance
jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonsoir

En pièce jointe, je met le fichier avec les plages définies en "dur" (version 5), ou directement dans les formules (version 6). C'est un peu plus compréhensible ainsi, mais cela ne s'adapte plus automatiquement à la longueur réelle de ton fichier. Si celui-ci va jusqu'à la ligne 105, il faut remplacer le 29 des noms "club" et "noms", et ceux dans les formules, par un 105, et dans "nomsserie1", le 18 par un 56 (50 noms dans la série 1 il me semble, et 49 dans la série 2).

Pour ce qui est des précédentes définitions (version ter et 4) :
* DECALER(Feuil1!$C$7;;;EQUIV("zz";Feuil1!$C:$C;1)-6) crée une plage commençant en C7 ayant une hauteur définie par EQUIV("zz";Feuil1!$C:$C;1)-6. Avec cette dernière partie, Excel recherche le terme "zz" dans la colonne C, ne le trouve pas, et renvoie le n° de la dernière ligne non vide (29 dans l'exemple, 105 dans ton fichier réel). Donc DECALER(Feuil1!$C$7;;;EQUIV("zz";Feuil1!$C:$C;1)-6) = DECALER(Feuil1!$C$7;;;29-6) = DECALER(Feuil1!$C$7;;;23) = Feuil1!$C$7:$C$29 (On peut aussi faire avec d'autres termes, du style "yyy", "www", ce qui compte, c"est écrire un terme qui n'est pas dans la colonne C)

* Si la plage ne contient que des nombres, on peut faire avec 9^99 au lieu de "zz". Excel ne trouvant pas cette valeur, il renvoie là encore le numéro de la dernière ligne non vide.
Attention, cela ne fonctionne qu'avec un 1 à la fin du EQUIV(...;...;1).

 

[CISCO]

Re : Tri aléatoire avec conditions

Re

Et un fichier fonctionnant jusqu'à la ligne 105.

@ plus

P.S : Tu as des #REF avec le premier fichier car tu n'as pas remplacé les 29 dans les formules par des 105. Dans le fichier ter, les noms s'adaptent automatiquement à la longueur de ton fichier réel, pas les formules.

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonjour

Et une version uniquement avec, dans les formules, des plages nommées, définies avec des DECALER, s'adaptant automatiquement à la longueur réelle des listes en feuille 1 et en feuille 2 (= fichier 4 complété).

@ plus

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonsoir le forum, Cisco,

je voulais savoir s'il faut que je recopie tous les noms ou bien si en copiant la feuille séries dans mon fichier original cela suffit car j'ai une ligne vide et toujours des #Nombre comme tu peux voir dans la pièce jointe
merci
jacky

Re,

j'ai oublier de préciser que sur mon fichier original, la feuil 1 est alimenté en formule , c'est peut-être pour cela que cela ne fonctionne pas
merci d'avance
jacky

 

[jacky49]

Re : Tri aléatoire avec conditions

Re le forum ,Cisco ,

je joins le fichier Essai séries 8 car j'ai copier en valeur les noms de mon fichier original et du coup , j'ai les mêmes erreurs que dans mon fichier original
merci d'avance
jacky

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonsoir

J'essaye de te faire un fichier plus facile à transposer sur ton fichier réel.

@ plus

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonsoir

Dans le fichier 8 que tu viens de poster, je ne sais pourquoi, dans Feuil 1, les lignes en dessous de la ligne 29 ne sont pas réellement vides. Dans ces conditions, la fonction DECALER utilisée dans la définition des diverses plages renvoie des plages trop longues. Ainsi, en C19, SI(LIGNES($6:18)>ENT((LIGNES(noms)+1)/2);""; devrait renvoyer "" et ne le fait pas car la plage noms est trop longue, et on n'a pas LIGNES($6:18)>ENT((LIGNES(noms)+1)/2.

Si tu effaces toutes les lignes en dessous de cette ligne 29, en Feuil 1, tu verras que cela fonctionne très bien.

Ce fichier 8 ne peut fonctionner correctement que si dans la feuille 1, la liste de la colonne C donnant les noms ne comporte pas de cellule vraiment vide parmi les noms. Après la liste, il ni a pas de problème. Si il y en a dedans, les fonctions DECALER arrêteront les plages au dessus de la 1ère cellule vraiment vide.

@ plus

 

[CISCO]

Re : Tri aléatoire avec conditions

Bonsoir

En pièce jointe, un fichier fonctionnant jusqu'à la ligne 105 fait sans nom (alors que le fichier 8 n'était fait qu'avec des noms).

Recopie, une par une les formules de la ligne 7 de la feuille Series, sans le signe =. Colle les une par une au bon endroit dans ton fichier réel. Rajoute les signes =. Valide les formules en C7 et en L7 en matriciel (en appuyant sur les 3 touches Ctrl+maj tempo+enter). Tire (=copier-coller) ces formules vers le bas, aussi longtemps que désiré (c-à-d jusqu'en dessous de la ligne 56).

Tu peux aussi copier toute la plage A7:M7, et faire un collage spécial formules en ligne 7 de ta feuille Series de ton fichier réel.

@ plus

P.S : Je te laisse remplir les autres colonnes avec des INDEX(.....;EQUIV(.....)) adéquates.

 

[jacky49]

Re : Tri aléatoire avec conditions

Bonsoir,

oui mais je ne peux pas supprimer les formules que j'ai dans la feuille 1 car elle vont chercher les participants dans une feuille inscriptions , j'espère qu'il y a une autre solutions
merci d'avance
jacky