Classement à plusieurs conditions

[lpz34]

Bonjours à tous, il pas mal de temps de je me connecte sur le site et vos conseils m'ont pas mal aidé.
Aujourd'hui impossible de résoudre le problème suivant.
S.V.P.

1°) - Je voudrais lancer le classement de la commission (feuille "Base_COM_General) en triant la base :

11°) - dans 1er temps sur les colonnes :
- la colonne B ou C : Résultat admissibilité, [ordre croissant colonne C] ;
- la colonne D : famille "ORTIF puis ORADM - Deux classements distincts ;
- la colonne E ou F : Option concours, [ordre croissant colonne E] ;
- la colonne G ou H : Ordre des candidats ayant composé ou pas [ordre croissant colonne H] ;
12°) - dans 2ème temps classer le les candidats admissibles ORTIF puis ORADM :
- en premier la colonne I : pour les candidats ORTIF ;
- puis la colonne J : pour les candidats ORADM ;
- les colonnes K à U représente le formulaire général à partir duquel un onglet par option est créé (exemples donnés dans le fichier joint).


Nb : Les effecifs sont variables par Famille (une famille peut être supprimée ou ajoutée) et par option (de 0 à x candidats).

2°) - Les conditions pour classer les candidats sont spécifiques à chaque famille (ORTIF ou ORADM) :

21°) - Le classement doit se faire pour les candidats ORTIF sur le TOTAL POINTS GENERAL [ordre décroissant colonne S] et pour les candidats ORADM sur la MOYENNE GENERALE [ordre décroissant colonne T] ;
22°)- A égalité de total points général ou de moyenne générale, le classement se fait sur le total des points oral obtenus aux épreuves orales [ordre décroissant colonne P] ;
23°) - A égalité de (total général et total oral) ou (moyenne générale et total oral), le classement se fait sur le total des points admissibilité [ordre décroissant colonne R] ;
24°) - A égalité de (total général et total oral et points admissibilité) ou (moyenne générale et total oral et points admissibilité), le classement se fait sur le numéro d'anonymat [ordre croissant colonne X].

3°) - Je voudrais parailleurs créer un onglet pour chaque Famille option (colonneF) dans un deuxième temps après délibération du jury (exemples des options MAI et BFADM).


PS : Le fichier est composé de quatre onglets :
- Accueil : Présentation et rappels des points cités ;
- Base_COM_General : classement général ;
- COM_Admission_MAI et COM_Admission_BFADM : onglets 3 et 4 correspondent aux formulaires édités pour la commission.

*** Dans le fichier joint : Un candidat est référencé par son N° Identifiant (colonne A) .
Il peut être inscrit dans une ou plusieurs option(s) (colonnes E et F) et de ce fait il appartient à la ou aux famille(s) de(s) option(s). Il reçoit alors un N° d'identifiant différent par option si nécessaire (Voir onglet "" du fichier joint).


Grand bMerci pour votre aide, Pierre

 

[hellnar]

Re : Classement à plusieurs conditions

Bonsoir,

Je viens de regarder le fichier proposé.
Il me semble que l'une des solutions opérationnelles serait d'utiliser des tableaux croisés.
Avez-vous essayé ?
Si oui, pourquoi avoir abandonné ?
Sinon ... on peut tenter le coup...

en attente

Hellnar

 

[hellnar]

Re : Classement à plusieurs conditions

Bonjour,

Toujours pas de réponse, alors on ajoute quelques questions :

1) Les formules sont trop compliquées et trop longues, donc difficiles à gérer.
on peut les simplifier pour bien voir ce qui se passe.
En plus elle est négative (ceux qui ne sont pas ....) au lieu d'être positive (ceux qui sont admis).
Enfin en nommant les colonnes (ou en utilisant les noms qui ont été définis) on peut passer de la formule :

=SI(LC4="ORADM";"";SI(OU(C2="NON ADMISSIBLE";C2="ERREUR.INSCRIPTION";C2="RADIE A L'ADMISSIBILITE";C2="DESISTE A L'ADMISSIBILITE";C2="RADIE INAP MED DEF");"";SOMMEPROD((L3C2:L429C2=LC2)*(L3C6:L429C6=LC6)*(L3C19:L429C19>LC19))+1))
à celle-ci :

=SI(ET(Famille="ORTIF";ordre=1);fm;"")

nb : fm est le nom de la Formule Matricielle.

Le problème est donc de traiter les cas d'égalité qui renvoient à un autre critère de classement.
Le problème n'est pas simple puisqu'il y a un grand nombre de critères.
d'où ma seconde question

2) Pourquoi pas de macros ?

ou

3) pourquoi ne pas séparer les bases entre ORDIF et ORDAM (ce qui permettrait d'utiliser des fonctions comme RANG()

Toujours en attente de réponse
cordialement

Hellnar

 

[hellnar]

Re : Classement à plusieurs conditions

Re-bonjour,

En regardant de plus près les lignes 91 à 93 on trouve deux fois le même candidat, même nom, code etc.. avec des notes différentes et une même moyenne. Est-ce un test ou une erreur ??
Hellnar

 

[hellnar]

Re : Classement à plusieurs conditions

Bon, et bien c'est pas la joie....
car en creusant un peu je me suis rendu compte qu'il y a plus d'erreurs dans les calculs qu'on ne pourrait l'imaginer.
Ainsi les cellules soi-disant en égalité ne le sont pas
Ainsi en écrivant =
=LC(-2)=L(1)C(-2)
pour les lignes 91-93 on obtient .... faux !!
Donc s'ils ne sont pas égaux MALGRE LE NOMBRE APPARENT, les classements n'ont pas à tenir compte du n° d'anonymat !
Cette erreur vient du fait qu'Excel n'a pas une très grande fiabilité pour les grands nombres, surtout lorsque certains sont multipliés par 10^7 et d'autres divisés par 100000 etc..
En utilisant la fonction ARRONDI(...;3) pour les calculs de moyenne ont obtient bien des valeurs identiques.... d'où une complication/clarification du problème puisque maintenant il y a 4 personnes parfaitement égales et non plus deux.
Le problème est ainsi mieux posé.

Dans ce cas, revoir les calculs et limiter expressément le nombre de chiffres derrière la virgule (2 ou plus mais en limitant)

Hellnar

 

[hellnar]

Re : Classement à plusieurs conditions

Bonjour,

Voilà ma réponse à la question concernant ORTIF.
Dans les cellules il faut
1 - compter les cellules qui ont une note supérieure
2 - ajouter 1
3 - faire le test s'il y a des exæquo
4 - Dans ce cas rechercher le nombre de ceux qui ont la même note mais un numéro d'anonymat inférieur et l'ajouter.
Bien sûr tout cela ne peut être mis dans une cellule SAUF si on nomme les fonctions matricielles.

J'ai donc nommé trois formules
FM (cf supra)
FMEGAL (pour compter les exaequo)
FManonymat pour ...
ce qui donne la formule simple suivante
=SI(ET(LC4="ORTIF";LC3 =1);FM+1+(FMEGAL>1)*FManonymat;"")

FM est la fonction déjà inscrite
FMEGAL est la même le > étant remplacé par =
FManonymat prend en compte la colonne d'anonymat ET d'égalité (on ne compte que ceux qui ont le même résultat..)
soit
=SOMMEPROD((Base_COM_General!L3C2:L428C2=Base_COM_General!LC2)*(Base_COM_General!L3C6:L428C6=Base_COM_General!LC6)*(Base_COM_General!L3C19:L428C19=Base_COM_General!LC19)*(Base_COM_General!L3C24:L428C24<Base_COM_General!LC24))

En nommant la formule toutes les informations viennent aussi (comme le nom de l'onglet) mais c'est lisible.
Cela semble bien fonctionner.
Je n'ai pas testé pour l'autre ORADM.
A vous de voir.

Bonne journée

Hellnar

 

[lpz34]

Re : Classement à plusieurs conditions

Bonjour,

je connais les tableaux croises, mais pour realiser une formulaire fixe par option avec des conditions, je ne vois pas
comment.

Merci, pour toi aide.

P.s / je ne suis pas tous les jours sur le p.c.

 

[lpz34]

Re : Classement à plusieurs conditions

RE BONJOUR,

1°) - En ce qui concerne la formule, celle que j'ai utilisée ne marche pas comme je le souhaite. C'est pour cette raison que je suis passé sur le forum. Mais si on peut la rationnaliser, pas de problème.

2°) - Je veux garder une base unique et donc pas séparer les populations "ORTIF et ORADM".

3°) - En ce qui concerne l'utilisation de la macro, j'ai essayé, mais mon niveau s'arrête l'emploi de la macro automatique (enregistre les mouvement de la souris et arrête). Par ailleurs si la macro est nécessaire, je voudrais bien comprendre chaque ligne de code (action réalisé en mode mathématique : si, alors...).

4°) - En ce qui concerne la fonction RANG(), je la connais, mais vu les critères en cas dégalité, j'ai essayé la fonction SOMMEPRO() avec laquelle j'ai bloquée.


J'espère avoir répondu à tes questions, je regarderai les messages demain soir.
Cordialement, Pierre.

 

[lpz34]

Re : Classement à plusieurs conditions

REPONSE pour la ligne 91 à 93.


Tu peux modifier les données sur les candidats, le but est de montrer la méthode de classement (test)

Pierre

 

[hellnar]

Re : Classement à plusieurs conditions

Bonjour,

Merci pour ces explications. Il est souvent plus facile de résoudre un problème complexe avec une macro qui permet de multiples opérations.
Mais dans mon dernier envoi, il y a une solution qui fonctionne pour ORTIF et que l'on peut dupliquer pour ORADM (si cela parait compliqué je le fais).
Par contre pour le dernier tableau récapitulatif, il me semble que les tableaux croisés sont jouables. Pour y parvenir, il me faudrait une image de l'objet à réaliser, données en lignes et en colonnes.
Sinon, on peut le faire à la main (sélection de listes partielles, exportations vers une autre feuille etc..) puis enregistrer une macro.

Bon dimanche
Hellnar

 

[lpz34]

Re : Classement à plusieurs conditions

BONJOUR A TOI

En pièce jointe le nouveau fichier.

J'ai modifié les formules de calcul. J'ai utilisé la fonction ARRONDI à 3 pour le total point général et la moyenne.

En ce qui concerne les classements, il y toujours des problèmes.

N.B : Par ailleurs en passant par le menu (Outils, Option, onglet Général et en cochant la case "Style de référence L1C1", on passe à un affichage colonne"A, B,C...) et inversement.

1°) - En ce qui concerne les explications de ton message 84, tu as nommé des formules et des plages de cellules dans l'onglet "Base_COM_General".
11 - Peux-tu mes les (Famille et Ordre) préciser ?
12 - Comment as-tu fait ôuir nommé des formules de calculs ?

COLOR="blue"]2°) - [/COLOR][/B]Il un problème dans la colonne J (voir l'onglet accueil, à partir de la ligne 58):
-si pour la population des ORTIF, la fin de la formules =SI(ET(Famille="ORADM";ordre=1);SOMMEPROD(($B$3:$B$429=$B4)*($F$3:$F$429=$F4)*($S$3:$S$429>$S4))+1;"")
- pour la population des ORADM, la formule devrait se terminer =SI(ET(Famille="ORADM";ordre=1);SOMMEPROD(($B$3:$B$429=$B4)*($F$3:$F$429=$F4)*($T$3:$T$429>$T4))+1;"")

COLOR="blue"]3°) - [/COLOR][/B]Les problèmes je les ai mis au format rouge et blanc (colonnes l et J). Dans la colonne K j'ai saisi la bonne numérotation.

P.S : La numérotation (rang) est continue. Si dans une option de Famille à 15 candidats admissibles, la numérotation va de 001, 002, 003, 004, 005, 006, 007, 008, 009, 010, 011, 012, 013, 014, et 015.


Merci encore de te pencher sur mon cas, j'espère que tu as passé un bon dimanche.

A+, Pierre.

 

[lpz34]

Re : Classement à plusieurs conditions

Bonjour à toi

attend reponse urgent.

S.V.P

a+++

lpz34

 

[hellnar]

Re : Classement à plusieurs conditions

... de retour ... mais pas pour longtemps.
Désolé de n'avoir pas répondu plus tôt mais très occupé par ailleurs..
Pour nommer des cellules utiliser la fonction nom (définir) ou carrément taper leur nom dans la fenetre en haut à gauche où se trouvent habituellement les adresses des cellules. C'est important de le faire.
Pour retrouver, corriger, utiliser Insertion, Nom etc...
Idem pour nommer une formule : l'écrire dans une cellules puis la copier et la nommer dans Insertion.. en la faisant précéder par le signe =
On peut ensuite l'utiliser (en faisant attention dans toute autre cellule ayant les mêmes dispositions. Cela permet de simplifier les formules dans une cellule.

Pour ce qui est du tableau croisé, il me semble que c'est compliqué dans ton cas, car il ne s'agit pas tellement d'un recensement (combien ont telle note) mais plutôt d'un classement. Donc je retire ce que j'ai dit auparavant.

Je vais essayer de me replonger dans ton nouveau fichier... si ce n'est pas trop tard

Hellnar

 

[hellnar]

Re : Classement à plusieurs conditions

Rebonsoir,

Il semble que le fichier que j'avais envoyé ne contenait pas toutes les formules créées auparavant. Ce ne devait donc pas être très compréhensible. Toutes mes excuses.

J'ai installé toutes les formules dans ce nouveau fichier y compris pour ORADM en essayant de respecter les bonnes colonnes.
J'ai ajouté une feuille pour que l'on puisse voir les formules qui ont été nommées (FM pour l'un et FMO pour l'autre etc..)
Les résultats sont différents entre mes calculs et tes calculs.... mais je pense avoir raison
Par exemple celui qui est classé 4 par toi et 10 par moi a une note qui manque puisqu'il est exempté... donc je pense que cela a joué dans ton classement. Si c'est le cas, il faut recalculer sa moyenne pour qu'il passe en 4. Pour les autres, il y a des erreurs dans tes classements car les numéros d'anonymat sont dans le sens que j'utilise... ou alors je ne comprend plus rien.

Voilà ce que je peux faire avant de repartir pour une semaine à l'étranger.

Cordialement

Hellnar

 

[lpz34]

Re : Classement à plusieurs conditions

Bonjour Hellnar,

De retour de Carnac dans le Morbihan, je me suis empressé de lire mes messages. En ce qui nous concerne, je recentrerais notre discussion sur l'onglet "Base_COM_General" du fichier " 1_Clt_Com_Admission_xxxxxx.xls sous Zip.
J'ai fais des vérifications sur la réactivité des classements colonne 19.

1°) - En ce qui concerne la population "ORTIF".
J'ai fais des vérifications sur les lignes 90 à 91 en modifiant les données des colonnes 13, 14, 15 et 18.
Le classement n'a pas bougé. Ce qui n'est normal. Alors j'ai saisi (en rouge fond jaune) dans la colonne 11 le bon classement. A priori il manque un critère d'égalité colonnes (19 (total points général) , puis 16 (total points oral), puis 18 (rappel point admissibilité) et en fin colonne 24 (Nmr anonymat (ordre croissant)).

2°) - En ce qui concerne la population "ORADM".
J'ai fais des vérifications sur les lignes 165 à 171 en modifiant les données des colonnes 13, 14, 15 et 18.
Le classement n'a pas bougé. Ce qui n'est normal. Alors j'ai saisi (en rouge fond jaune) dans la colonne 11 le bon classement.
La ligne 165 est bien classé, elle doit être 4ème et non 10ème. Car le problème doit être identique au que pour la population ORTIF.
A priori il manque un critère d'égalité colonnes (20 total points général, puis 16 total points oral, puis 18 rappel point admissibilité et en fin colonne 24 Nmr anonymat (ordre croissant).

3°) - Moyenne Générale.
La moyenne est égale au total points général divisé par 100 pour tous les candidats,
sauf pour les candidats ayant été "Exempt définitif de sport - Imputable au service" qui ont la moyenne égale au total points général divisé par 90.
Ce point répond au classement de la ligne 165.


A te lire, cordialement LPZ34