XL 2019 Combinaison possibles sans redondance

[srabich42]

Bonjour à tous.

Merci à ce forum d'exister et à tous les contributeurs.
Je n'ai pas trouvé la section où me présenter donc je le fais ici . Je suis prof de Physqiue-Chimie au Lycée de Die dans la Drôme. La réforme du bac entraine des choix d'orientation à la carte pour les élèves, nous nous retrouvons donc en cette fin d'année avec pleins de combinaisons d'enseignement de spécialité différentes dans lesquels il est difficile de se retrouver.

J'ai une liste d'élèves qui doivent choisir 3 spécialités parmi X spécialités (8 cette année à Die mais ça peut être plus). Chaque spécialité ne peut être prise qu'une fois.
J'obtiens un tableau avec sur chaque ligne le nom de l'élève, la spécialité 1, la spécialité 2, la spécialité 3.

En faisant supprimer les doublons par colonne, j'obtiens les spé uniques par colonne et je regroupe les 3 dans une seule pour avoir la liste des spé choisies (Tableau en haut à gauche de l'onglet combinaisons).

J'ai ensuite recodé chaque spécialité avec un nom plus court (Humanité, Littérature et Philosophie devient tout simplement _PHILO_) (Dans le même tableau

Puis grâce à un site internet, j'ai listé toutes les combinaisons possibles de choix sans prendre en compte l'ordre (tirage de 3 parmi 8 sans remise), ce qui fait quand même 56 combinaisons !
Ces 56 combinaisons sont listées toujours dans l'onglet "combinaisons" dans le tableau de droite (jai rajouté un numéro de combinaison et j'ai séparé chaque spécialité de chaque combinaison dans d'autres colonnes à droite.

Sur l'onglet Base qui contient les 86 élèves de 2nde et leurs trois choix de spécialité j'ai :
- rajouté 3 colonnes à droites contenant les spécialités choisies avec le code plus court
- puis concaténé ces trois cellules pour avoir la combinaison sous forme d'une chaine de caractère)

enfin et ç'est là que ça ne marche plus, j'ai écris une formule (M2 de l'onglet Base) qui doit vérifier dans la cellule L2 la présence des codes spé1, sp2 et spé3 de la combinaison 1. Si les 3 sont présents, la valeur vraie doit être le numéro de combinaison correspondant.

Si je ne fais que la recherche de la première combinaison, ça marche et m'affiche bien Combinaison 1 (pour tous les élèves qui correspondent), mais dès que je veux tester la 2ème combinaison, il y a erreur (pour le 2ème élève par exemple)

Dans l'idée, pour chaque combinaison élève (onglet base), je dois chercher parmi les 56 combinaisons de spécialités possibles (onglet Combinaison), dès que la correspondance est trouver, cela doit m'afficher le numéro de combinaison.

Est ce que vous pouvez m'aider à corriger cette formule svp. ça m'aiderai bcp.

Bonus : certains élèves ont choisi une spécialité qui ne se fait pas dans notre établissement. Dans ce cas là, aucune correspondance ne sera trouvée car aucune combinaison ne contient la spé "EXT DIE", il faudrai dans ce cas là que ça affiche la case E60 de l'ongle combinaison, càd EXT DIE

Merci d'avance pour votre aide.
Srabich42

 

[sylvanu]

Bonjour srabich42,
Un essai en PJ avec l'ensemble des combinaisons :

=SIERREUR(INDEX(Combinaisons!E:E;
SIERREUR(EQUIV(I2&","&J2&","&K2;Combinaisons!F:F;0);
SIERREUR(EQUIV(J2&","&K2&","&I2;Combinaisons!F:F;0);
SIERREUR(EQUIV(K2&","&I2&","&J2;Combinaisons!F:F;0);
SIERREUR(EQUIV(I2&","&K2&","&J2;Combinaisons!F:F;0);
SIERREUR(EQUIV(K2&","&J2&","&I2;Combinaisons!F:F;0);
SIERREUR(EQUIV(J2&","&I2&","&K2;Combinaisons!F:F;0);""))))))
);"Ext Die")

 

[srabich42]

Bonjour Sylvanu,

et un très grand merci, car ça marche parfaitement.
Par contre comment ?????? Je n'arrive pas à comprendre la démarche ni la formule !

De ce que j'ai compris, pour chaque triplet de spécialité, il peut y avoir 6 ordres différents.
Donc la formule permet de rechercher une à une les 6 ordres différents dans ma liste de toutes les combinaisons possible, et doit forcément en trouver une.

Si j'ai bien compris dans la formule, la recherche se fait dans toute la colonne F de l'onglet "combinaison" et permet de retourner le numéro de combinaison correspondant de la colonne E. Donc si à l'avenir, il y avait plus de spécialités et donc plus de combinaisons, il suffirait de les rajouter en dessous et ça devrait marcher ?

Il est clair que cette formule est sobre mais efficace. Est ce que ce que j'avais commencé à faire aurai pu marcher avec la fonction si.condition ? et si oui, qu'est ce qui n'allait pas chez moi ?

Enfin, je cherche ensuite dans un nouvel onglet "Elève_combinaison" à avoir la liste des élèves ayant choisi la même combinaison afin de constituer les groupes. En mettant chaque combinaison en colonne, j'avais pensé faire une condition Si sur chaque ligne et tester tous les élèves du type
si ( cellule combinaison elève = $cellule combinaison colonne ; Nom de l'élève; "" ) mais je vais me retrouver avec des trous dans ma liste quand il ny a pas de correspondance.

Existe-t-il une fonction qui permet de tester chaque combinaison élève avec le numéro de la combinaison de la colonne et les ajouter en dessous à la suit dynamiquement ?

Merci encore pour votre aide et votre rapidité
Srabich42

 

[sylvanu]

Bonjour Srabich,
1- C'est le Sierreur qui fait la différence, avec seulement des Si vous aurez des #N/A.
2- On peut encore rajouter des spécialités, mais la formule va se complexifier, pour N spécialités il y a 2^N-2 combinaisons, donc 6 avec 3 mais 14 avec 4, et 30 avec 5.
3- Pas tot compris pour la fin.
En PJ un essai avec les noms en lignes et les combinaisons en colonnes. En utilisant les filtres on peut filtrer sur chaque combinaison pour avoir la liste des élèves.