XL 2019 Formule de globalisation

serras

XLDnaute Junior
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
 

Pièces jointes

  • données - Copie.xlsx
    14.9 KB · Affichages: 7
Dernière édition:

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonsoir Serras,
En PJ un essai pour le point 1. Une colonne K (masquée ) est utilisée pour pondérer les ex aequos.
Code:
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.
 

Pièces jointes

  • données - Copie.xlsx
    15 KB · Affichages: 3

serras

XLDnaute Junior
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

XLDnaute Barbatruc
Supporter XLD
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

XLDnaute Barbatruc
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
 

Pièces jointes

  • PQ_Rang global selon filiere.xlsx
    34.3 KB · Affichages: 3

serras

XLDnaute Junior
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 ?
 

Discussions similaires

Réponses
4
Affichages
238

Statistiques des forums

Discussions
311 720
Messages
2 081 924
Membres
101 841
dernier inscrit
ferid87