Calcul d'écart à la moyenne dépendant de critères

[Drekki]

Bonjour,

Dans le cadre de mon travail, je dois évaluer une déviance par rapport à la moyenne des scores de mes patients à certains tests.
Pour chaque test, j'ai des normes me donnant la moyenne et l'écart type en fonction de l'âge et du niveau socio culturel provenant d'étude de groupe témoin.
Plutôt que de reprendre mes tableaux de normes à chaque fois que je rédige un compte rendu, je souhaiterais me constituer un fichier excel où je pourrais entrer l'âge, le nsc et le score de mon patient et recevoir le nombre d'écart-type de différence par rapport à la moyenne (c'est notre critère d'examen).

J'ai trouvé une formule fonctionnelle, mais un peu lourde, sachant que sur mon exemple, j'ai utilisé des normes simples qui ne comportent que deux valeurs pour l'age et le nsc. Mais certains tests ont des normes plus fines comprenant 10 tranches d'age possible ou plusieurs valeurs de nsc, ce qui, avec ma formule, me donnerait des lignes interminables de si, avec les erreurs de frappe risquées. (Je n'ai pas encore réfléchi aux normes compernant un troisième critère...)

Le fichier joint indique le tableau de norme, puis mon essai pour mettre en forme un formulaire pertinent. J'ai créé un tableau effectuant le calcul pour un score donné, puis une formule qui va chercher la donnée correspondant à certains critères.

Existe-t-il un moyen léger de déterminer si une valeur est comprises entre deux chiffres ?
Est-il possible de piocher plus directement dans un tableau de données ?
Quelles grossières lourdeurs contenues dans ma formule peuvent être évitées ?

Je vous remercie,
Bonne journée

 

[eriiic]

Re : Calcul d'écart à la moyenne dépendant de critères

Re,

excuse-moi, j'avais oublié de mettre le fichier...

Donc en ligne 37 j'ai essayé de reproduire ton exemple de $E$24 où tu trouves -0.984, et moi : -0.189
En pas à pas je vois que j'utilise les données de Normes ligne 9, colonnes 7-8.
En ligne 38 idem, je trouve -1.588 (ligne 9, colonnes 9-10) et toi en E25 tu as -0.750
Si tu pouvais m'expliquer cette différence.
Et si tu trouves ma réponse correcte faire 2-3 autres contrôles,

En lignes 39 à 42 avec BEM144 apparemment mon calcul est correct.

on a comme paramètre sexe 2 (homme)
Heu non, 2=femme

Dans la fonction je contrôle qu'on envoie pas de paramètres inutiles. En testant sur BEM144 j'ai donc eu en réponse 'Sexe interdit'...
Finalement je trouve ça bof comme réponse
J'enlève les contrôles et je calcule sans tenir compte des paramètres inutiles ?

"impossible de traiter votre question. Soit Excel ne trouve pas de fonction équivalente, soit l'aide n'est pas installée"
C'est normal, je n'ai pas mis d'aide, par contre tu dois voir qu'il attend dans l'ordre les paramètres score, table, sexe , age , nsc , libellé
D'ailleurs si tu as un autre nom pour Libellé, j'étais en panne d'idée. Ca doit représenter qu'on attend P R V Animaux Fruits Meubles

eric

 

[Drekki]

Re : Calcul d'écart à la moyenne dépendant de critères

Ah flûte, il faut que homme = 2 pour le calcul de la moyenne du test RLRI16 parce qu'elle est calculé à partir d'une équation qui utilise le 2 en tant que valeur multiplicatrice pour pondérer une moyenne générale). Donc ce serait plus simple de décréter pour tout les tests homme = 2

Bon par contre j'ai un gros souci, je suis rentrée chez moi et là open office sous entend que je rêve si je veux les macros.

Néanmoins tu as tout à fait raison, pour la ligne 37, en calculant à l'ancienne (avec la paperasse et la calculette), j'obtiens également -0,19, et -1,588 en ligne 38, donc je me suis planté dans un décalage quelque part.

Pour les autres lignes je n'ai pas tes résultats vu que open office boude la macro, mais j'ai
En ligne 38 : -1,59
En ligne 39 : 2,97
En ligne 40 : 2,11
En ligne 41 : -2,71
En ligne 42 : -2,16

Pour remplacer le terme libellé, on peut tout à fait prendre le terme version, vu que ce sont les différentes versions du test.

Quant au sexe interdit, elle m'a bien fait rire celle-là ! Effectivement, mieux vaut calculer sans les paramètres inutiles, sinon ça risque de rendre la promotion d'excel auprès de mes collègues plus délicate

Je vais creuser cette histoire de macros sous open office.

Et par contre je retourne au boulot lundi prochain seulement (mi-temps) donc je ne peux pas regarder à nouveau pourquoi l'excel installé à l'hosto me dit qu'il n'y a pas d'arguments.

 

[eriiic]

Re : Calcul d'écart à la moyenne dépendant de critères

Re,

J'ai inversé, femme=1 et homme=2 (aussi ça m'étonnait que les femmes soient supérieures aux hommes mais bon... ;-))
Pas sûr que ça t'aide pour le critère multiplicatif, il faudra peut-être prévoir un traitement particulier.

Règles pour la construction des tables (à conserver):
- TOUTES les règles sont importantes
- le nom du test doit figurer en colonne A
- une table se termine par le mot 'fin' en colonne A
- si le sexe est un critère : on doit trouver Homme d'inscrit sous le nom du test en colonne A
- Si nsc est un critère : nsc doit être inscrit en colonne B sous la ligne du nom du test, ou sous la ligne où est inscrit Homme si le sexe est un critère
(tel que tu as construit tes tableaux actuellement)
- Si sexe et nsc sont détectés en critère je contrôle que leur valeur est présente (donc nsc=0 n'est pas utilisable)
- si le nom de version en paramètre de la fonction est vide, je prend les valeurs dans les 2 colonnes à partir de B (ou de C si nsc est un critère)
- si des tables sont ajoutées compléter la liste en L4:L13, si des versions sont présentes compléter les listes des versions (à partir de M3) en n'oubliant pas de les nommer.

J'ai l'impression que c'est correct pour les 4 types de tables présents, je te laisse tester.

Macro excel sur open office : tu peux oublier...
Tente en installant le viewer excel mais vu qu'il n'autorise pas les modification pas sûr que les macros tournent...

A ton boulot le niveau de sécurité doit bien être sur 'moyen' puisque tu as vu au moins une fois la réponse de la fonction.
Je t'ai signé ce projet, si tu acceptes de faire confiance à ma signature la confirmation ne te sera plus demandée.
Par contre ça m'oblige à re-signer à chaque modif et je risque d'oublier. Rappelle-le moi si j'oublie.

eric

PS : en PJ la capture de ce que tu dois voir avec 'insertion / fonction / personnalisée' avec les paramètres attendus en clair.

 

[Drekki]

Re : Calcul d'écart à la moyenne dépendant de critères

Désolée pour le retard de réponse, j'ai eu beaucoup de patients. L'avantage c'est que ça m'a fait plein de données brutes pour tester le schmilblick, et tout fonctionne à la perfection. J'ai même réussi à ajouter des tests, en suivant scrupuleusement tes instructions et en rebouchant un peu mes lacunes de base (genre redéfinir un nom).

Pour le critère multiplicatif, j'ai triché honteusement... Comme la moyenne était calculé grâce à une formule utilisant certains critères comme coefficient, j'ai calculé la moyenne pour chacun de ces critères et j'ai obtenu un tableau comme les autres.
(au lieu de mettre "12 - 0.80*age + 0.20*NSC - 0.52*genre", avec femme=1, homme=2, j'ai fait un tableau femme toujours à -0.52 et un tableau homme toujours à - 1.04).

Par contre pour les normes basées sur les percentiles, je sèche. Je ne vois pas comment modifier la présentation des données pour les faire correspondre.
Un score donné se trouve dans un intervalle de réponse. Des paramètres entrent parfois en jeu, mais ils ne sont pas nombreux. J'ai un de mal à expliquer plus clairement qu'avec un exemple, je le joins donc en feuille 1 de RecapNorme4 (pas pu le mettre sur le même fichier qui devient trop lourd). J'y ai mis deux exemples, un où à un score donné, selon la version du test, correspond un percentile. Le deuxième comporte deux tableaux, un pour chaque version du test, l'âge entrant en compte. J'ai mis en vert la correspondance que je recherche avec les critères que j'ai entré. J'ai également fait un petit tableau, tout au début, de résultat attendu. J'avais posé le problème ici aussi (des fois que je m'y sois mieux exprimée)http://www.excel-downloads.com/forum/148146-rechercher-une-colonne-en-fonction-dune-ligne-et-dune-valeur.html.

 

[eriiic]

Re : Calcul d'écart à la moyenne dépendant de critères

Bonjour,

Pour le test RLRI16 si le score est de 16 pour RT1 faut-il ramener 75 ou 99 ?
Et le score ne peut jamais être >16 ?

eric

 

[Drekki]

Re : Calcul d'écart à la moyenne dépendant de critères

Le score est sur 16, donc 16 est le meilleur score que l'on peut obtenir. En général, on prend la meilleure tranche possible, donc pour 16, le centile 99. En fait, on considère généralement que la performance est limite au centile 10 et qu'il y a du souci à se faire au centile 5. Donc c'est vrai que 75 ou 99, dans les deux cas on est content (parce que oui, ça fait plaisir quand les patients vont bien).