Formule excel : Classement suivant 4 critères Fermé

Question

Bonjour,

Je viens vers vous car j'ai un soucis avec excel. Je ne suis pas du tout un pro d'excel donc je fonctionne de manière logique. Malheureusement, j'ai un problème pour cette formule.

J'ai un tableau de classement d'une poule de foot de 4 équipes. Je voudrais que dans la première case(coloneD), une formule me renvoit le classement de l'équipe par rapport aux 3 autres et ce selon plusieurs critères : les points en colone F, la différence de but en colone L, les buts marqués en colone J et des points disciplinaires en colone O.

Pour ce faire, j'ai fonctionné de manière logique en imbriquant des formules SI suivant chaque cas (pts de l'équipe 1 = pts de l'équipe 2 = pts de l'équipe 3 mais pas de l'équipe 4, puis différence de buts des équipes 1 et 2 égales mais pas celle de l'équipe 3, etc....)

Voila la formule à laquelle je suis arrivé :
=SI(ET(F6<>F4;F6<>F5;F6<>F7);5-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6<>F5;F6<>F7;L6=L4;J6=J4;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6<>F5;F6<>F7;L6=L4;J6>J4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6<>F5;F6<>F7;L6>L4);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6<>F4;F6<>F7;L6=L5;J6=J5;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6<>F4;F6<>F7;L6=L5;J6>J5);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6<>F4;F6<>F7;L6>L5);4-RANG(F6;F4:F7;1);SI(ET(F6=F7;F6<>F4;F6<>F5;L6=L7;J6=J7;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F7;F6<>F4;F6<>F5;L6=L7;J6>J7);4-RANG(F6;F4:F7;1);SI(ET(F6=F7;F6<>F4;F6<>F5;L6>L7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L4;L6<>L5;J6=J4;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L4;L6<>L5;J6>J4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L5;L6<>L4;J6=J5;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L5;L6<>L4;J6>J5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L4;L6=L5;J6=J4;J6<>J5;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L4;L6=L5;J6=J5;J6<>J4;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L4;L6=L5;J6=J4;J6=J5;MIN(O4;O5;O6)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;L6=L4;L6=L5;MAX(J4;J5;J6)=J6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6<>F7;MAX(L4;L5;L6)=L6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L4;L6<>L7;J6=J4;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L4;L6<>L7;J6>J4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L7;L6<>L4;J6=J7;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L7;L6<>L4;J6>J7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L4;L6=L7;J6=J4;J6<>J7;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L4;L6=L7;J6=J7;J6<>J4;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L4;L6=L7;J6=J4;J6=J7;MIN(O4;O6;O7)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;L6=L4;L6=L7;MAX(J4;J6;J7)=J6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F7;F6<>F5;MAX(L4;L6;L7)=L6);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L5;L6<>L7;J6=J5;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L5;L6<>L7;J6>J5);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L7;L6<>L5;J6=J7;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L7;L6<>L5;J6>J7);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L5;L6=L7;J6=J5;J6<>J7;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L5;L6=L7;J6=J7;J6<>J5;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L5;L6=L7;J6=J5;J6=J7;MIN(O5;O6;O7)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;L6=L5;L6=L7;MAX(J5;J6;J7)=J6);4-RANG(F6;F4:F7;1);SI(ET(F6=F5;F6=F7;F6<>F4;MAX(L5;L6;L7)=L6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6<>L5;L6<>L7;J6=J4;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6<>L5;L6<>L7;J6>J4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L5;L6<>L4;L6<>L7;J6=J5;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L5;L6<>L4;L6<>L7;J6>J5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L7;L6<>L4;L6<>L5;J6=J7;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L7;L6<>L4;J6>J7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6<>L7;J6=J4;J6<>J5;O6<O4;4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6<>L7;J6=J5;J6<>J4;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6<>L7;J6=J4;J6=J5;MIN(O4;O5;O6)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6<>L7;MAX(J4;J5;J6)=J6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L7;L6<>L5;J6=J4;J6<>J7;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L7;L6<>L5;J6=J7;J6<>J4;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L7;L6<>L5;J6=J4;J6=J7;MIN(O4;O6;O7)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L7;L6<>L5;MAX(J4;J6;J7)=J6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L5;L6=L7;L6<>L4;J6=J5;J6<>J7;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L5;L6=L7;L6<>L4;J6=J7;J6<>J5;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L5;L6=L7;L6<>L4;J6=J5;J6=J7;MIN(O5;O6;O7)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L5;L6=L7;L6<>L4;MAX(J5;J6;J7)=J6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;MAX(L4;L5;L6;L7)=L6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6=J4;J6<>J5;J6<>J7;O6<O4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6>J4);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6=J5;J6<>J4;J6<>J7;O6<O5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6>J5);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6=J7;J6<>J4;J6<>J5;O6<O7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6>J7);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6=J4;J6=J5;J6<>J7;MIN(O4;O5;O6)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6=J4;J6=J7;J6<>J5;MIN(O4;O6;O7)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6=J5;J6=J7;J6<>J4;MIN(O5;O6;O7)=O6);4-RANG(F6;F4:F7;1); SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;J6=J4;J6=J5;J6=J7;MIN(O4;O5;O6;O7)=O6);4-RANG(F6;F4:F7;1);SI(ET(F6=F4;F6=F5;F6=F7;L6=L4;L6=L5;L6=L7;MAX(J4;J5;J6;J7)=J6);4-RANG(F6;F4:F7;1);5-RANG(F6;F4:F7;1))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Je sais c'est horrible mais selon moi ca devrait fonctionné. Je pense juste que j'ai trop de formules imbriquées (la limite est à 64 d'après mes recherches et j'en ai 67).

Donc avez vous une solution pour simplifiée ma formule? Ou alors une modification? Ou même voyez vous une erreur dans cette formule?

Merci d'avance de vos réponses.

Tmac

le hollandais volant · Answer

salut, ouwarf....


Voici comment je fessait autrefois quand les limitations étaient à 6 formules imbriqués (formules "SI") : 

j'en mettait 5 et la dernière renvoyait à la case d'à coté ou j'en mettait 5 autres et ainsi de suite!

par exemple :

=si(cond1;vrai;si(cond2;vrai;...,...,si(cond5;vrai;si(cond6;vrai;=case_d'à_coté))...))

Tmac · Answer

Merci à toi.
C'est vrai que ca marche en la décomposant. J'y avais pas pensé. C'est pas optimal mais c'est déjà ca.

Toutefois, si quelqu'un a une solution pour simplifier carrément ma formule, je suis preneur. Parce que maintenant que ma formule est bonne pour une équipe, il faut que je la fasse pour les autres. Et en faisant glisser, ca marche pas. J'ai peur de devoir me taper les changements de case à la main... Quelqu'un a une solution pour ca aussi?

Merci d'avance

Tmac

Mike-31 · Answer

Salut,

Tu ne peux imbriquer que sept conditionnelles dans une formule, au dela il faut faure des cascades de six la septième renvoyant 0 et ainsi de suite.

Pour utiliser l'incrémentation et ne pas avoir à saisir une telle formule et surtout les erreur toujours possible, utilise les valeurs absolues et relatives, lorsque tu souhaites faire référence à la même cellule quelque soit le déplacement de la formule mets la référence entre $ $ comme ceci $C$1 ou $C1 pour un déplacement latéral ou C$1 pour un déplacement vers le bas ou rien si la valeur de la cellule doit changer 

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

eriiic · Answer

Bonjour,

déja tout ceux qui ont excel 2003 et moins ne pourrons pas t'aider en théorie (limites des 6 fonctions).
Et je serais curieux de voir comment réagit le convertisseur 2007-2003 que l'on a pour lire ces fichiers, et si tu dois le passer à qcq'un qui n'a pas excel2007 tu risques d'être coincé. A tester donc...
Le mieux est de déposer sur cijoint.fr un exemple (laisse tes formules qu'on sache ce que ça donne sur 2003) mais surtout explique la logique des tests et calcul si possible.
Je confirme : c'est horrible
J'ai peur on a peur pour toi aussi...

eric

Mike-31 · Answer

Re,

Avec ce lien un exemple d'imbrication de 7 SI (maximum possible) avec trois cascades ce qui offre une combinason de 21 SI mais on pourrait aller beaucoup plus loin. Toute fois il est préférable de rester au dessous de 7 imbrication par cascade

https://www.cjoint.com/?grxt0s26dQ


=SI(A1=1;"un";SI(A1=2;"deux";SI(A1=3;"trois";SI(A1=4;"quatre";SI(A1=5;"cinq";SI(A1=6;"six";SI(A1=7;"sept";)))))))&SI(A1=8;"huit";SI(A1=9;"neuf";SI(A1=10;"dix";SI(A1=11;"onze";SI(A1=12;"douze";SI(A1=13;"treize";SI(A1=14;"quatorze";)))))))&SI(A1=15;"quinze";SI(A1=16;"seize";SI(A1=17;"dix sept";SI(A1=18;"dix huit";SI(A1=19;"dix neuf";SI(A1=20;"vingt";SI(A1=21;"vingt et un";)))))))

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Tmac · Answer

Tout d'abord, merci à tous pour vos réponses.

Merci beaucoup our l'exemple des SI imbriqués avec "&" au milieu. J'ai utilisé cette méthode et ma formule matche à présent.
Voici un exemple : 

https://www.cjoint.com/?gsozSLnrKz

Je rapelle que le but est de classer 4 équipes selon plusieurs critères : On regarde les pts, puis en cas d'égalité, on compare les "diff", puis en cas d'égalité, on compare les "GF" et enfin en cas d'égalité, on compare les "DP". Si encore égalité, j'ai choisi de laisser vide, car en finissant ma dernière imbrication par autre chose que SI(...;....;SI(.....................;;"")) alors j'obtiens un double résultat (le cas a été vérifié dans un SI auparavant, puis le dernier SI n'est pas vérifié donc il met aussi ce dernier résultat). Si vous avez une solution pour ce petit soucis, sinon je peux totalement faire avec un vide.

Mon soucis reste le problème de reproduire la formule sans avoir a retappé tout (ou remplacé une à une les cases qui changent. Le problème est que je ne vois pas vraiment comment utilisé les $ sachant que la formule prend des cases au dessus et en dessous. La formule que j'ai écrite, pour l'équipe 3 prend en compte les cases de la ligne 9 en fonction de celles des lignes 7,8 et 10. Si je fais glisser vers le haut par exemple, il va prendre les cases de la ligne 8 en fonction de celles des lignes 6,7 et 9. Or je veux qu'il le fasse en fonction des cases des lignes 7, 9 et 10. C'est possible avec les "$"????

Merci d'avance.

Tmac

Mike-31 · Answer

Salut,

J'essaye de comprendre ton tableau joint, la plage B7 à B 10 représente tes 4 équipes !

Colonne D Pts sont les points obtenus et saisi manuellement !
ColonnesE V colonneF D colonneG L  sont renseigné par un autre classeur test

Mais GF GA sont renseigné comment !

Colonne J Diff la différence est calculé comment !
Et en colonnes K, L et M comment sont elles renseignées !

Pour ta formule lorsque tu dis on regarde les points c'est par rapport aux 4 équipes qui sont représenté par les 4 lignes de ton tableau, si deux équipes sont à égalité on compare les GF de ces deux équipes, puis les DP toujours de ces deux équipes etc ... c'est bien ça

Pour la dernière question, les $ si tu reprends mon post 3 je t'ai expliqué, maintenant à toi de faire un essai pour voir le comportement d'une formule toute simple=A1+B1 ainsi que =$A1+$B$1 etc et tu incrémentes la formule vers le bas

Une fois que tu auras compris ces références absolue et relative, écris ta formule, si tu l'écris correctement tu pourras l'incrémenter dans une colonne sans avoir à retoucher quoi que ce soit 

A+
Mike-31

Un problème sans solution est un problème mal posé  (Einstein)

Tmac · Answer

Alors, pour t'expliquer mon tableau. Il se trouve dans un onglet d'un classeur, je l'ai sorti et mis à part pour vous faire le lien.

La colonne B doit comporter le classement de chaque équipe par rapport aux autres, c'est la que doit se trouver ma formule.
La colonne C comportera le nom des équipes
Les colonnes D, E et F comporteront le nb de Victoire, Egalité(Draw) et Défaites (Loss).
Les colones G, H et I comporteront le nb de buts marqués (Goal For), de buts encaissés (Goal Against) et la différence (GF - GA).
Tout ceci sera renseigné par les autres onglets du classeur.

Enfin les colones J, K et L coporteront les Cartons Jaunes (Yellow Card), Cartons rouges (Red Cards) et Points disciplinaires (nb de YC + nb de RC*2)
Ceci sera rensigné à la main.

Ensuite, le tableau fonctionne comme tu l'as compris. Si les points sont tous différents, alors on donne directement le classement. SI il y a deux ou plus équipes à égalité de points, on compare les diff, puis ainsi de suite avec les GF et les DP.

Pour les incrémentations et les $, j'ai peur que vu la façon dont j'ai écris la formule, cela ne puisse pas marcher.
En effet, prenons un morceau de la dite formule (le premier SI) :

SI(ET(F6=F4;F6<>F5;F6<>F7;L6=L4;J6=J4;O6<O4);4-RANG(F6;F4:F7;1))

Même si je l'écris comme ceci :

SI(ET(F6=$F$4;F6<>$F$5;F6<>$F$7;L6=$L$4;J6=$J$4;O6<$O$4);4-RANG(F6;$F$4:$F$7;1))

Que je la tire vers le haut, cela va donner :

SI(ET(F5=F4;F5<>F5;F5<>F7;L5=L4;J5=J4;O6<O4);4-RANG(F5;F4:F7;1))

Au lieu de :

SI(ET(F5=F4;F5<>F6;F5<>F7;L5=L4;J5=J4;O6<O4);4-RANG(F5;F4:F7;1))

En gros en tirant vers le haut, je veux que tout les F5 deviennent des F6 et tous les F6 des F5, de même pour les L5, L6, J5, J6, O5, O6...

Je suis un peu perdu là...

Tmac