XL 2019 Formule de globalisation

serras · 25 Mai 2023

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 · 25 Mai 2023

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.

serras · 26 Mai 2023

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 · 26 Mai 2023

Bonjour,

sylvanu à dit:
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 · 26 Mai 2023

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

serras · 26 Mai 2023

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 · 26 Mai 2023

Cousinhub à dit:
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 · 26 Mai 2023

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 · 26 Mai 2023

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 · 26 Mai 2023

Bonjour

Cousinhub à dit:
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 · 26 Mai 2023

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

serras · 26 Mai 2023

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

Cousinhub · 26 Mai 2023

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 · 26 Mai 2023

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

Cousinhub · 26 Mai 2023

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...

XL 2019 Formule de globalisation

serras

XLDnaute Occasionnel

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

serras

XLDnaute Occasionnel

sylvanu

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

serras

XLDnaute Occasionnel

serras

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Pièces jointes

Cousinhub

XLDnaute Barbatruc

serras

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

serras

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Discussions similaires