XL 2010 Probléme avec NB.SI.ENS en cas d'égalité sur 2 fichiers

[gservas]

Bonjour
Je fais encore appel à vos connaissances sur la fonction NB.SI.ENS
Sur le 1er cas => le fichier NB.SI.ENS avec 2 critéres
Je cherche a creer un classement suivant les 2 critéres région et nombre de points. Cependant en cas d'égalité du nombre de point et de région je n'arrive pas à avoir un classement logique par ordre décroissant. J'aimerai que en cas d'égalité le classement continue logiquement sans avoir de doublon sur le chiffre le plus à droite de la formule. L'ordre de concurrents qui sont à égalité n'a pas d'importance.
J'ai essayé cette formule mais sans succés
=CONCATENER(A4;" - ";B4;" - ";NB.SI.ENS($B$4:B4;B4;$C$4:C4;"<="&SI(C4=$C$4:C4;"100";C4))+SI(C4=$C$4:C4;NB.SI.ENS($B$4:B4;B4;$C$4:C4;C4);0))

Sur le 2nd cas => le fichier NB.SI.ENS avec 3 critéres et si disqualifié et égalité
Je cherche a creer un classement suivant les 3 critères et idem en cas d'égalité de temps malgré les 2 autres critères (catégorie et sexe). L'ordre de concurrents qui sont à égalité n'a pas d'importance. La formule fonctionne avec l'égalité de disqualification mais pas de temps.
J'ai essayé cette formule mais sans succés car en cas d'égalité de temps il y a doublon.
=CONCATENER(A2;" - ";B2;" - ";C2;" - ";NB.SI.ENS(B$2:B$21;B2;C$2:C$21;C2;D$2$21;"<="&SI(D2="DISQUALIFIE";"20";D2))+SI(D2="DISQUALIFIE";NB.SI.ENS(B$2:B2;B2;C$2:C2;C2;D$22;"DISQUALIFIE");0))

Merci pour votre aide
a+
GS

 

[R@chid]

Bonjour,
j'ai essayé de t'aider, mais vraiement je n'ai pas pu compredre comment tu as obtenu le résultat escompté.

 

[gservas]

Bonjour
Merci pour ton aide
Pour répondre à ta question je l'ai tapé à la main pour vous montrer.
L'idée sur le tableau avec 2 critères est d'avoir un classement décroissant alors que sur le tableau avec 3 critères un classement croissant.
Merci
GS

 

[R@chid]

Bonjour,
oui je sais que tu les as tapés à la main, mais je voudrais bien savoir quelle logique t"as suis pour avoir ses résultats, car j'ai cherché dans tous les sens et je n'ai pas pu m'en sortir, je vais t'aider oui, mais qu'est ce que je dois dire à Excel pour qu'il comprenne ce que je veux.

 

[gservas]

Bonjour
Merci pour ton aide
Le probléme dans les 2 cas est l'égalité parfaite dans le concatener sachant que c'est le chiffre du dernier critére qui doit changer en cas d'égalité.
Pour le tableau avec 2 critéres => le concatener comprend :
- le 1er chiffre est celui du competiteur (sans importance de le NB.SI.ENS) si besoin tu peux le retirer pour t'aider, celà sera même plus simple. Sinon remplace le par une lettre unique est identique à tout le monde.
- le 2nd chiffre est la région (1er critére dans le NB.SI.ENS)
- le 3éme chiffre (2nd critére dans le NB.SI.ENS)est celui du classement décroissant suivant le nombre de points.
Le problème est que dans ce 2nd critère avec le premier critère identique je peux avoir des égalités,c'est à dire que je peux avoir 2 concurrents de la même région avec le même nombre de points. C'est ici que je voudrais qu'en cas d'égalité de 2 concurrents ne pas avoir de doublons. L'ordre des concurrents à égalité n'a pas d'importance

Pour le tableau avec 3 critères => le concatener comprend :
- le 1er chiffre est celui de l'épreuve (sans importance de le NB.SI.ENS) si besoin tu peux le retirer pour t'aider, celà sera même plus simple.
- le 2nd éléments est celui de la catégorie (1er critére dans le NB.SI.ENS)
- le 3éme éléments (2nd critére dans le NB.SI.ENS)est celui du sexe
- le 4éme élements (3nd critére dans le NB.SI.ENS)est celui du temps en ordre croissant.
Le problème est que dans ce 3éme critère avec le premier et le second critère identique je peux avoir des temps identique ,c'est à dire que je peux avoir 2 concurrents de la même catégorie et du même sexe avec le même temps . C'est ici que je voudrais qu'en cas d'égalité de 2 concurrents ne pas avoir de doublons.L'ordre des concurrents à égalité n'a pas d'importance.
Tu verras aussi dans la formule mise en place que les disqualifiés se trouve à la fin du classement dans un ordre quelconque. A ne pas retirer.

J’espère avoir été compréhensible dans mes explications même si je sais que ce n'est pas simple à comprendre.
Merci encore pour l'aide apportée par ce forum
GS

 

[Dugenou]

Bonjour,
Sans avoir regardé ton fichier et sans aucun doute sur la capacité de R@chid à te proposer une solution, je me permets de te donner le truc qu'on utilise en général pour séparer les ex aequo : on ajoute au chiffre qui sert à classer (le temps, les points, le score...) le N° de ligne divisé par 10 000 ou plus : ça ne change pas le résultat affiché en nombre entier, mais ça sépare les ex aequo puisqu'ils auront le même résultat plus quelques millièmes.
Cordialement

 

[Dugenou]

Maintenant j'ai ouvert ton fichier pour 2 critères et je suis comme R@chid : y'a un truc que je ne comprends pas : les 3 premières lignes : compétiteurs 1, 2 et 3, région 7, points 5 5 et 6
comment peut on les classer 1er tous les 3 ?
et le suivant de la région 7 avec 6 points que tu affiches 2 eme il devrait être 3eme ou 4 eme après les 3 premiers si on sépare les ex aequo.
En PJ une proposition avec le calcul détaillé. J'ai considéré qu'il fallait classer les compétiteurs au sein de leur région. Si ce n'est pas ça, merci de préciser.
Cordialement

 

[Dugenou]

et dans la même veine le fichier à 3 critères

 

[R@chid]

Bonsoir @ tous,

Bonjour,
Sans avoir regardé ton fichier et sans aucun doute sur la capacité de R@chid à te proposer une solution, je me permets de te donner le truc qu'on utilise en général pour séparer les ex aequo : on ajoute au chiffre qui sert à classer (le temps, les points, le score...) le N° de ligne divisé par 10 000 ou plus : ça ne change pas le résultat affiché en nombre entier, mais ça sépare les ex aequo puisqu'ils auront le même résultat plus quelques millièmes.
Cordialement

Merci Dugenou, j'allais m'arracher les cheveux juste pour comprendre.

Cordialement

 

[gservas]

Bonsoir
Merci pour votre retour c'est presque parfait.
C'est exactement ca il sur le premier fichier de classé les concurrents suivant leur région.
Sur le 2nd fichier, c'est ca il faut classer les concurrents suivant leur catégorie et leur région.
L'idéal serait que les 2 cases créés a savoir rang + ligne/100000000 et rang région soit intégré dans la formule que j'avais mise en place si c'est possible bien entendu.
Merci pour ton aide
GS

 

[Dugenou]

Bonsoir,
je crois que ce n'est pas possible (en tout cas je ne sais pas le faire)
voir pj avec une colonne masquée : c'est ce que peux faire de mieux
Cordialement

 

[R@chid]

Bonjour @ tous,

Salut Dugenou,
Bravo d'avoir trouvé la solution même si le questionneur continue de donner des résultats escomptés incorrectes.

Une seule formule et sans colonne intermédiaire pour renvoyer les résultats trouvés par notre ami Dugenou, en G2 :

=A2&" - "&B2&" - "&C2&" - "&SIERREUR(EQUIV(D2+LIGNE()/9^9;PETITE.VALEUR(SI((A$2:A$21=A2)*(B$2:B$21=B2)*(C$2:C$21=C2)*ESTNUM(D$2:D$21);D$2:D$21+LIGNE(D$2:D$21)/9^9);LIGNE(INDIRECT("1:"&NB.SI.ENS(A$2:A$21;A2;B$2:B$21;B2;C$2:C$21;C2))));0);NB.SI.ENS(A$2:A2;A2;B$2:B2;B2;C$2:C2;C2))

Formule matricielle
@ valider par Ctrl+Maj+Entrée
@ tirer vers le bas


J'espère que mon message numéro 10 000 sur le forum soit utile.



Cordialement

 

[Dugenou]

Bonjour @R@chid,
Bravo pour ton 10 000 eme et bravo pour avoir trouvé une formule unique : je me penche dessus aujourd'hui : grâce à toi je vais encore progresser.

Cordialement

 

[Dugenou]

Bon,
C'est vraiment fort ! Je comprends les formules mais j'ai encore du mal à assimiler l'ensemble.
Au sujet de ton 10 000 : il était une coutume il y a quelques années que pour célébrer le passage à Barbatruc le contributeur produise un fichier un peu original et le partage. On a eu comme ça quelques perles de Monique, jmps, hervé ou robert
On les retrouve au salon en regardant les posts qui commencent par un chiffres genre 6000 ou 10 000.

Cordialement

 

[R@chid]

Bonjour @ tous,
Salut Dugenou,
Merci, mais c'est moi qui ai plutôt progressé grâce à toi et aux amis sur le forum.

Au sujet de ton 10 000 : il était une coutume il y a quelques années que pour célébrer le passage à Barbatruc le contributeur produise un fichier un peu original et le partage. On a eu comme ça quelques perles de Monique, jmps, hervé ou robert
On les retrouve au salon en regardant les posts qui commencent par un chiffres genre 6000 ou 10 000.

Oui j'ai vu quelques discussions de ce type comme tu l'as dit il y a quelques années, peut-être que l'on n'a rien à ajouter d'où l'on a cessé de le faire .

La formule que j'ai donnée est une matricielle si jamais le tableau n'est pas trié, le cas échéant, et comme le tableau sur l'exemple fourni est trié, une simple formule classique fera l'affaire :

=A2&" - "&B2&" - "&C2&" - "&NB.SI.ENS(A$2:A2;A2;B$2:B2;B2;C$2:C2;C2)

Sous Excel pour Microsoft 365, quelque soit le tri :

=JOINDRE.TEXTE(" - ";;A2:C2;SIERREUR(EQUIV(D2+LIGNE()/9^9;TRIER(FILTRE(D$2:D$21+LIGNE(D$2:D$21)/9^9;(A$2:A$21=A2)*(B$2:B$21=B2)*(C$2:C$21=C2)));0);NB.SI.ENS(A$2:A2;A2;B$2:B2;B2;C$2:C2;C2)))

On n'a pas besoin de valider par Ctrl+Maj+Entrée les formules matricielles.


Cordialement