XL 2019 Formule de globalisation

[serras]

Bonjour, je me trouve face à des données à traiter pour lesquelles trouver une formule tourne au casse tête pour moi.

Je vous remercie de votre aide. En PJ un fichier de données.

A partir de 3 épreuves distinctes, j’ai besoin de faire un classement commun. Pour cela 3 étapes

ETAPE 1 : En colonne K des feuilles ECO INT et SCI, calcul automatique du classement dans la filière (lequel est fait sur la base de la moyenne en colonne J, avec départage des égalités en fonction de la note 3 et si la note 3 est à égalité alors classement à égalité c'est à dire qu'il peut y avoir plusieurs candidats avec le meme rang de classement)

ETAPE 2 : Calcul d’un coefficient global (sur la feuille GLOBAL, colonne F) des 3 épreuves sur la base de la règle suivante : indice_classement x (nbre_total_admis – 1) / (nbre_admis_par filière– 1 )

Sachant que :

• indice_classement = classement filière – 1 (en colonne L dans les feuilles)
• nbre_total_admis = total admis des filières ECO INT SCI
• nbre_admis_par filière = total admis dans la filière du candidat concerné

ETAPE 3 : Calcul d’un classement global (sur la feuille GLOBAL, colonne G) sachant que :

• le plus près de 0 est le mieux classé
• les exaequo sont départagés par la moyenne des notes 1 et 3 (en colonne M des feuilles ECO INT SCI) et en cas de nouvelle égalité départage en fonction de l’âge, au bénéfice du plus âgé

Attention : le nombre de candidats admis varie a chaque session, il faudrait donc que les formules s’adaptent au fait que le nombre d'admis par filière (et donc au total) est variable. Dans l'exemple en PJ il y a 18 + 5 + 7 candidats mais dans une prochaine session cela pourra être 25+14+9)

Merci de votre aide

L Serras

 

[sylvanu]

Bonsoir Serras,
En PJ un essai pour le point 1. Une colonne K (masquée ) est utilisée pour pondérer les ex aequos.

Col K :
=SI(NB.SI($J$2:$J$19;J2)=1;J2;J2+I2/1000)
Col L :
=RANG(K2;$K$2:$K$19)

Pour les points 2 et 3, rien compris. Vous devriez mettre quelques attendus pour rendre l'explication moins obscure.

 

[serras]

Bonjour, merci pour ce retour rapide.

L'étape 1 permet de donner un premier élément pour aboutir au classement global toute filière confondues, à partir du classement à l'intérieur de chaque filière.

L'étape 2 consiste à utiliser ce premier résultat pour l'intégrer dans une formule permettant de construire le classement global en étape 3 _ mode de calcul = indice_classement x (nbre_total_admis – 1) / (nbre_admis_par filière– 1 )

• Si on prend l'exemple du candidat TODESCO (filière ECO), son coefficient global est de 1,611 car le mode de calcul donne : 1 (son classement dans filière ECO -1) x 29 (dans l'exemple en PJ il y 30 admis au total (18 en eco +5 en int +7 en sci)) / 18 (car dans TODESCO est en filière ECO qui comporte 18 admis)
• Si on prend l'exemple du candidat BERNAUD (filière INT), son coefficient global est de 17,4 car le mode de calcul donne : 3 (son classement dans filière INT -1) x 29 (dans l'exemple en PJ il y 30 admis au total (18 en eco +5 en int +7 en sci)) / 5 (car dans BERNAUD est en filière INT qui comporte 5 admis)

A noter : avec la formule les premiers des 3 filières sont toujours à égalité avec un coef global à 0

L'étape 3 consiste à classer les candidats en prenant en compte leur coefficient global calculé à l'étape 2

J'espère avoir été un peu plus clair. Mais je me rends compte de la complexité du dispositif. Merci encore pour le temps que vous consacrerez à m'aider.

 

[sylvanu]

Bonjour,

Vous devriez mettre quelques attendus pour rendre l'explication moins obscure.

Un attendu dans un fichier est toujours plus parlant qu'un long discours.

Tel que je l'ai compris, et surtout de faire en automatique une synthèse multipages, je ne sais pas faire en formules. Pour moi, le VBA rendrait le problème plus simple, si vous acceptez les macros VBA.

 

[Cousinhub]

Bonjour,
Hello sylvanu
@serras , à l'étape 2, tu parles du nombre d'admis..
Si moyenne >=10 ou le nombre de candidats par session?

 

[serras]

je ne suis pas spécialiste et je ne vois pas trop les conséquences des macros BVA. SI c'est le seul moyen pour faire fonctionner le dispositif, je veux bien tester

 

[serras]

Bonjour,
Hello sylvanu
@serras , à l'étape 2, tu parles du nombre d'admis..
Si moyenne >=10 ou le nombre de candidats par session?

Le nombre d'admis est seulement le total des personnes listées dans les 3 feuilles ECO INT SCI. EN clair tous ceux qui sont sur la feuille sont admis. Il s'agit "seulement" de classer ces admis

 

[Cousinhub]

Ok,
Regarde le fichier joint.
Dans ce fichier, chaque onglet "Filiere" contient un Tableau Structuré, qui ne prend en compte que les 9 premières colonnes, et qui tous commencent par "t_" (exemple, "t_sci").
Les calculs sont effectués par le biais de Power Query (en natif dans ta version) : les différentes moyennes, les calculs "Coeff", ainsi que les "Rangs" (en cas d'égalité, on passe de 1-1- à 3 pour le troisième si 2 premiers)
Dans la requête de récapitulation, j'ai mis toutes les colonnes (avec les calculs internes, donc pas de formules) afin que tu puisses contrôler.
Si ça te convient

 

[Cousinhub]

Re-,
J'ai oublié de préciser, pour la mise à jour, clic droit dans la requête, "Actualiser", ou dans le Ruban "Données", "Actualiser tout"

 

[serras]

Bonjour

Ok,
Regarde le fichier joint.
Dans ce fichier, chaque onglet "Filiere" contient un Tableau Structuré, qui ne prend en compte que les 9 premières colonnes, et qui tous commencent par "t_" (exemple, "t_sci").
Les calculs sont effectués par le biais de Power Query (en natif dans ta version) : les différentes moyennes, les calculs "Coeff", ainsi que les "Rangs" (en cas d'égalité, on passe de 1-1- à 3 pour le troisième si 2 premiers)
Dans la requête de récapitulation, j'ai mis toutes les colonnes (avec les calculs internes, donc pas de formules) afin que tu puisses contrôler.
Si ça te convient

merci beaucoup pour ce travail.
lorsque je tente une mise à jour (pour faire un test j'ai modifié la note " du 1er, dans la feuille ECO) j'ai un message d'erreur quand je tente d'actualiser tout : [EXPRESSION ERROR] L’importation table.AddrankColumn ne correspond à aucune exportation. Avez-vous oubliez une référence de module ?

 

[Cousinhub]

Re-,
Tu as bien la version 2019 d'Excel?
Si ta version est inférieure, alors c'est normal...

 

[serras]

A priori oui
MICROSOFT office professionel plus 2019 / EXCEL 2019 MSO version 1808

 

[Cousinhub]

Ok,
Je viens de télécharger mon fichier, et modifier une note, et pas de soucis...
C'est bien sur mon fichier que tu testes?
Ou sur le tien?

 

[Cousinhub]

Re-,
Je viens d'essayer sur une version 2019, et effectivement, j'ai le même souci
Je regarde

 

[Cousinhub]

Re-,
L'essai que j'avais effectué était sous Excel 2019 Pro version 1902...
Je viens de mettre à jour Office, et suis passé à la version 2304
Et miracle, ça fonctionne
Si tu peux mettre à jour ta version...