Problème formule excel Résolu/Fermé

Question

Bonjour, J'ai créé un tableau dans lequel des notes doivent apparaitre en fonction d'un certain barème qui diffère selon l'Age et le sexe de l'individu (par exemple une femme de 21 ans n'aura pas la même note qu'une femme de 40 ans ayant eu les mêmes résultats sportifs et ce ne sera pas la même note pour un homme de 21 ans.) Le problème c'est que les résultats obtenus ne sont pas les bons, comme si la formule ne prenait pas en considération une partie des données. Pour l'exemple, sur le documents excel, j'ai mis des âges différents avec des sexes différents. J'ai rentrais des résultats qui devraient correspondre à la note de19 pour chacun des individus, mais les individus de sexe masculin et féminin ayant plus de 38 ans n'affichent pas la note escomptée. Ils indiquent M 39ans => 17; M 43ans => 15 et M 47ans => 13... Idem pour les Femmes... Ce qui est surprenant c'est que cela fonctionne correctement pour les individus de sexe masculin ou féminin de moins de 38 ans... Voici la formule rentrée : =SI(ET(C6="M";E6<=21);SI(J6="";"";SI(J6>=45;20;SI(J6>=42;19;SI(J6>=39;18;SI(J6>=36;17;SI(J6>=33;16;SI(J6>=30;15;SI(J6>=28;14;SI(J6>=27;13;SI(J6>=25;12;SI(J6>=24;11;SI(J6>=22;10;SI(J6>=21;9;SI(J6>=19;8;SI(J6>=18;7;SI(J6>=16;6;SI(J6>=15;5;SI(J6>=13;4;SI(J6>=12;3;SI(J6>=10;2;SI(J6>=9;1;SI(J6<=7;0;""))))))))))))))))))))));SI(ET(C6="M";38>E6>21);SI(J6="";"";SI(J6>=50;20;SI(J6>=47;19;SI(J6>=44;18;SI(J6>=41;17;SI(J6>=38;16;SI(J6>=35;15;SI(J6>=32;14;SI(J6>=29;13;SI(J6>=28;12;SI(F6>=26;11;SI(J6>=25;10;SI(J6>=23;9;SI(J6>=22;8;SI(J6>=20;7;SI(J6>=19;6;SI(J6>=17;5;SI(J6>=16;4;SI(J6>=14;3;SI(J6>=13;2;SI(J6>=11;1;SI(J6<=10;0;""))))))))))))))))))))));SI(ET(C6="M";41>=E6>38);SI(J6="";"";SI(J6>=45;20;SI(J6>=42;19;SI(J6>=39;18;SI(J6>=36;17;SI(J6>=33;16;SI(J6>=30;15;SI(J6>=28;14;SI(J6>=27;13;SI(J6>=25;12;SI(J6>=24;6;SI(J6>=22;10;SI(J6>=21;9;SI(J6>=19;8;SI(J6>=18;7;SI(J6>=16;6;SI(J6>=15;5;SI(J6>=13;4;SI(J6>=12;3;SI(J6>=10;2;SI(J6>=9;1;SI(J6<=7;0;""))))))))))))))))))))));SI(ET(C6="M";45>=E6>42);SI(J6="";"";SI(J6>=39;20;SI(J6>=36;19;SI(J6>=33;18;SI(J6>=30;17;SI(J6>=28;16;SI(J6>=27;15;SI(J6>=25;14;SI(J6>=24;13;SI(J6>=22;12;SI(J6>=21;11;SI(J6>=19;10;SI(J6>=18;9;SI(J6>=16;8;SI(J6>=15;7;SI(J6>=13;6;SI(J6>=12;5;SI(J6>=10;4;SI(J6>=9;3;SI(J6>=7;2;SI(J6>=6;1;SI(J6<=7;0;""))))))))))))))))))))));SI(ET(C6="M";49>=E6>=46);SI(J6="";"";SI(J6>=33;20;SI(J6>=30;19;SI(J6>=28;18;SI(J6>=27;17;SI(J6>=25;16;SI(J6>=24;15;SI(J6>=22;14;SI(J6>=21;13;SI(J6>=19;12;SI(J6>=18;11;SI(J6>=16;10;SI(J6>=15;9;SI(J6>=13;8;SI(J6>=12;7;SI(J6>=10;6;SI(J6>=9;5;SI(J6>=7;3;SI(J6>=6;2;SI(J6<=7;0;""))))))))))))))))))));SI(ET(C6="F";E6<=21);SI(J6="";"";SI(J6>=35;20;SI(J6>=32;19;SI(J6>=29;18;SI(J6>=26;17;SI(J6>=23;16;SI(J6>=21;15;SI(J6>=20;14;SI(J6>=19;13;SI(J6>=18;12;SI(J6>=17;11;SI(J6>=16;10;SI(J6>=15;9;SI(J6>=14;8;SI(J6>=13;7;SI(J6>=12;6;SI(J6>=11;5;SI(J6>=10;4;SI(J6>=9;3;SI(J6>=8;2;SI(J6>=7;1;SI(J6<=6;0;""))))))))))))))))))))));SI(ET(C6="F";38>E6>21);SI(J6="";"";SI(J6>=40;20;SI(J6>=37;19;SI(J6>=34;18;SI(J6>=31;17;SI(J6>=28;16;SI(J6>=25;15;SI(J6>=22;14;SI(J6>=21;13;SI(J6>=19;12;SI(J6>=18;11;SI(J6>=17;10;SI(J6>=16;9;SI(J6>=15;8;SI(J6>=14;7;SI(J6>=13;6;SI(J6>=12;5;SI(J6>=11;4;SI(J6>=10;3;SI(J6>=9;2;SI(J6>=8;1;SI(J6<=7;0;""))))))))))))))))))))));SI(ET(C6="F";41>=E6>38);SI(J6="";"";SI(J6>=35;20;SI(J6>=32;19;SI(J6>=29;18;SI(J6>=26;17;SI(J6>=23;16;SI(J6>=21;15;SI(J6>=20;14;SI(J6>=19;13;SI(J6>=18;12;SI(J6>=17;11;SI(J6>=16;10;SI(J6>=15;9;SI(J6>=14;8;SI(J6>=13;7;SI(J6>=12;6;SI(J6>=11;5;SI(J6>=10;4;SI(J6>=9;3;SI(J6>=8;2;SI(J6>=7;1;SI(J6<=6;0;""))))))))))))))))))))));SI(ET(C6="F";45>=E6>42);SI(J6="";"";SI(J6>=29;20;SI(J6>=26;19;SI(J6>=23;18;SI(J6>=21;17;SI(J6>=20;16;SI(J6>=19;15;SI(J6>=18;14;SI(J6>=17;13;SI(J6>=16;12;SI(J6>=15;11;SI(J6>=14;10;SI(J6>=13;9;SI(J6>=12;8;SI(J6>=11;7;SI(J6>=10;6;SI(J6>=9;5;SI(J6>=8;4;SI(J6>=7;3;SI(J6>=6;2;SI(J6>=5;1;SI(J6<=4;0;""))))))))))))))))))))));SI(ET(C6="F";49>=E6>=46);SI(J6="";"";SI(J6>=23;20;SI(J6>=21;19;SI(J6>=20;18;SI(J6>=19;17;SI(J6>=18;16;SI(J6>=17;15;SI(J6>=16;14;SI(J6>=15;13;SI(J6>=14;12;SI(J6>=13;11;SI(J6>=12;10;SI(J6>=11;9;SI(J6>=10;8;SI(J6>=9;7;SI(J6>=8;6;SI(J6>=7;5;SI(J6>=6;4;SI(J6>=5;3;SI(J6>=4;2;SI(J6>=3;1;SI(J6<=2;0;"")))))))))))))))))))))))))))))))) J'espère que vous parviendrez à trouver d'où provient mon erreur...

m@rina · Answer

Bonjour,

Plutôt que de  recopier ta formule indigeste qu'on n'a pas vraiment envie de tester, tu devrais nous dire quels sont tes critères précis et peut-être nous donner ton fichier pour qu'on comprenne mieux. On pourra sans doute te donner une autre solution.

m@rina

PapyLuc51 · Answer

Bonjour

Vu l'ensemble de cette formule il semblerait qu'il y ait une erreur dans cette partie (voir les caractères gras) deux fois le nombre 6 en réponse ; à mon avis c'est 11 qu'il faut mettre

SI(ET(C6="M";41>=E6>38);SI(J6="";"";SI(J6>=45;20;SI(J6>=42;19;SI(J6>=39;18;SI(J6>=36;17;SI(J6>=33;16;SI(J6>=30;15;SI(J6>=28;14;SI(J6>=27;13;SI(J6>=25;12;SI(J6>=24;6;SI(J6>=22;10;SI(J6>=21;9;SI(J6>=19;8;SI(J6>=18;7;SI(J6>=16;6;SI(J6>=15;5;SI(J6>=13;4;SI(J6>=12;3;SI(J6>=10;2;SI(J6>=9;1;SI(J6<=7;0;""))))))))))))))))))))))

Cordialement

yo2607 · Answer

https://www.cjoint.com/c/KAwoY43damf

Si je comprends bien, pour vous, afin de simplifier le tout, il faudrait faire un tableau des barèmes sur un feuillet et y faire référence dans ce tableau là, correcte?

yo2607 · Answer

Woooh! rapide!
Je ne saisis pas très bien où tu veux en venir en faisant cela mais je vais mettre les barèmes sous format de tableau excel, si cela peut t'aider....

Abominafreutroce · Answer

Bonjour,

J'ai d'abord fait du ménage : enlevé les mises en forme conditionnelles, formats qui piquent les yeux, liste déroulantes qui ne fonctionnent pas pour y voir clair :"ouf !"
J'ai ajouté au fichier les barèmes et je les ai transformés pour qu'Excel puisse les lire.
Et ensuite fait la formule pour le résultat des pompes. Le principe sera le même pour le reste.

https://www.cjoint.com/c/KAxh7k3wkww

Bonne journée

Abominafreutroce · Answer

Bonjour,

Pour les bornes il faut modifier le tableau.
Lorsque je lis le chiffre 19 en face de 47 pompes pour les hommes de 22 à 36 ans, je n'imagine pas qu'en fait c'est 18... :-) Alors que je si je fais 50 pompes c'est bien 20 !

Pour changer l'ordre des résultats il faut changer l'ordre du tableau et légèrement la formule.
Voici le résultat sans changer les bornes dans le tableau 

https://www.cjoint.com/c/KAxjbBkYHEw

C'est mieux ?

yo2607 · Answer

Petite question, comme tu peux le voir,  dans le tableau de natation, il y a des résultats qui ne sont pas des chiffres, mais du texte. Je ne pourrais pas utiliser la meme formule du coup, si?

Abominafreutroce · Answer

Bonjour,

Il est difficille d'expliquer un INDEX/EQUIV en quelques lignes... pour le point précis du 1 ou -1, ca correspond à l'ordre de recherche. Soit je cherche plus petit ou plus grand que le chiffre donné. Mais le tableau doit être trié différement. Va voir dans l'aide d'EQUIV, c'est expliqué.

Quant à"matrice" c'est le tableau complet dans lequel tu fais ta recherche. Je les ai nommés "PompesFemmes" et "PompesHommes" pour plus de clarté.

Non, ne peut pas utiliser le texte direct pour les apnées. Il faut modifier le tableau. Mais je ne vois nulle par des données pour l'apnée dans ton tableau final.

Je t'envoie le tableau dans la matinée

Bonne journée,

Abominafreutroce · Answer

Voilà le fichier :

https://www.cjoint.com/c/KAzjeSz7mhw

Il y a peut être une incohérence dans le tableau des pompes : 37 ans au lieu de 38 ans partout ailleurs

Pour nommer une plage on la sélectionne et on écrit son nom en zone nom : 
et on les retrouve dans formule / gestionnaire de nom
Dans ton cas avec quantité de noms qui ne servent à rien surement du à des copier coller intempestifs :-) !

Pour moi la colonne AA contient un temps et l'apnée c'est autre chose, c'est comme ça que j'ai construit le fichier... Pour l'apnée il faudrait rajouter la profondeur et si c'est terminé ou pas ?

Bonne journée

Abominafreutroce · Answer

OK
L'apnée ne dépend pas de l'age non plus ce qui simplifie considérablement les choses.
J'ai ajouté les colonnes ad hoc pour rentrer les données et fait la formule :

https://www.cjoint.com/c/KAAhXIKnnTw

Bonne journée

yo2607 · Answer

Merci c'est Top! Cela va vraiment me simplifier la tache, merci beaucoup!

yo2607 · Answer

Allez dernier petit détail, pour que ce soit parfait haha

J'ai 0 qui s'affiche quand je n'ai pas de résultat dans les pompes, alors que ce n'est pas le cas pour les autres formules, comment cela se fait?

Au temps pour moi j'ai trouver, il fallait que je supprime la ligne 0 dans le tableau des pompes car excel considérait la case vide comme une valeur 0!

Du coup je te remercie de nouveau pour le temps que tu as accordé à mon problème!!

Abominafreutroce · Answer

Ce fut un plaisir ! :-)

yo2607 · Answer

Bonjour,

Désolé de casser le game, mais j'ai encore un problème sur le document. Dans le cas de mon document, lorsque je rentre 2.16 ( en aisance aquatique) comme résultat, je dois avoir 14 et pourtant cela m'indique 15.... J'ai changé l'ordre du tableau de barème mais cela n'a rien changé.... (c'était pire...) Le meme problème se retrouve au niveau du 2400m si je mets 10.05 je n'ai pas le bon résultat...
Pourrais tu y jeter un dernier coup d'oeil s'il te plait?

https://www.cjoint.com/c/KABl0STs7Qf

Merci

tontong · Answer

Bonjour,
Pour les âges rangés par ordre croissant Equiv(;;;1)va bien chercher la position de la valeur inférieure, donc pas de problème. Par contre Equiv() ne trouve pas la valeur supérieure pour les temps.
2solutions:
Utiliser une matricielle ( J.Boisgontier) feuille1
Faire une échelle de 0.05 en 0,05 et remplacer le temps réel  par la valeur arrondie à 0,05 au dessus.
On cherche alors la valeur exacte avec Equiv(;;;0). Feuille 2
https://www.cjoint.com/c/KABtxSGJuWK

Il faudrait mettre les temps dans un format d'heure valide: hh: mm: ss.

Abominafreutroce · Answer

Bonsoir,

Je pense que le problème vient des tableaux et pas de la formule : 

Pour la première valeur incorrecte (M / 35 ans / 12.46) La formule va chercher dans la matrice la colonne d'age 22 et la ligne 12.45 qui sont les premières valeurs inférieures ou égales à ce que tu demandes, ce qui correspond à 4. C'est exactement ce que l'on demande à Excel.

Un peu plus bas ( M / 25 ans / 11,57) considéré correct va chercher selon le même principe la colonne d'age 22 et la ligne 11.55, toujours les valeurs immédiatement inférieures et là ça ne te pose pas de problème.

Pour l'aisance aquatique incorrecte (M / 27 ans / 2.15) il donne 15. Je vois que dans le tableau il y a justement la ligne 2.15 et en face...15 . Donc Excel trouve... 15 !!!

Quand tu lui donnes un chiffre il cherche la valeur immédiatement inférieure ou égale. Il faut à mon avis revoir les bornes du tableau (j'en parlais plus haut).

Bonne soirée

Abominafreutroce · Answer

D'accord :-)

Tu retournes les tableaux d'AA et de 2400m (en triant) et tu mets -1 aux Equiv des colonnes. Ca devrait être mieux.

Je ne vais pas le faire tout de suite, c'est pas l'heure !

Néanmoins, je persiste : dans sa logique mathématicienne implacable si tu mets 15 en face de 2.16 Excel trouvera 15... C'est qu'il est têtu l'animal.

Equiv fonctionne toujours avec inferieur / supérieur ou égal

Si pour 2.16 je dois obtenir 14, je vais basiquement, implacablement, (sans aucune originalité ni créativité je te l'accorde) écrire... 14

yo2607 · Answer

Bonjour, 
d'accord, merci, je vais essayer et je te dis si c'est bon! :)

yo2607 · Answer

Bon ben, y a un truc que je ne dois pas faire correctement... J'ai inversé les tableaux comme tu m'as dit de faire et j'ai mis -1 dans les equiv des colonnes, pourtant cela ne fonctionne pas, bien au contraire, maintenant, je n'ai plus aucun résultat qui s'affiche haha Je n'ai modifié la formule que dans le 2400M hein j'ai pas insisté sur l'AA! 

https://www.cjoint.com/c/KACngvTaZWf

Si au passage tu arrives à m'expliquer ce que je n'ai pas compris, par ce que ça me désespère un peu de ne pas pouvoir la modifier seule cette formule... :(((((

tontong · Answer

Bonjour,
À partir de cette trame vous pourrez établir un fichier complet.
Je vous laisse le soin de compléter les barèmes pour les féminines.
Les valeurs et formules en jaune dans ces barèmes ne sont pas indispensables; elles ont servi à l'élaboration et la vérification.
Si vous réécrivez ces feuilles commencez bien par incrémenter les temps en secondes pour les transformer en hh:mm:ss par formule dans la colonne voisine.
 Dans le fichier précédent k6 et k7  montraient la différence de résultat en changeant la formule.
https://www.cjoint.com/c/KACqdRj6ZjK

yo2607 · Answer

Bonjour,

voici ce que j'ai fait comme formule : 

=SIERREUR(SI(C65="M";INDEX(_2400M;EQUIV(PLAFOND(F65*86400;5);'2400m'!A3:A93;1);EQUIV(E65;'2400m'!C2:H2;1));INDEX('2400m'!J1:Q93;EQUIV(PLAFOND(F65*86400;5);'2400m'!J3:J93;1);EQUIV(E65;'2400m'!L2:Q2;1)));"")

Le problème c'est que ça ne fonctionne pas haha 
Voici le document en question , j'ai mis la formule en ligne 65. 

https://www.cjoint.com/c/KADiByqsvbf

Cordialement,

tontong · Answer

Bonjour,
Pour faire du "cousu main" il va me falloir un peu de temps, ce que je n'ai guère aujourd'hui. Je reviens dès que possible.

tontong · Answer

Bonjour,
À vérifier
https://www.cjoint.com/c/KADpyvuhLJK
Il faudrait ajouter des Sierreur():
=SIERREUR(laformule;" ")

Abominafreutroce · Answer

Bonjour,

Quand je dis -1 sur les colonnes , c'est sur les colonnes de recherche du temps, pas les âges (ce truc là me retourne la tête aussi...)

J'ai modifié les formules, les tests d'erreurs, les tableaux et changé la liste déroulante H/F qui ne marchait plus

https://www.cjoint.com/c/KAElslTycRw

Bonne journée

yo2607 · Answer

Merci à vous deux!!!

Problème formule excel

25 réponses

Discussions similaires

Newsletters