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

Drekki

XLDnaute Nouveau
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
 

Pièces jointes

  • RecapNormes.xls
    14 KB · Affichages: 170

JNP

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

Bonjour Drekki et bienvenue :),
Je ne suis pas formuliste, donc il y a certainement plus simple :p.
Je te proposerais en E17
Code:
=SOMMEPROD((CNUM(GAUCHE(G13:G14;2))<=B17)*(CNUM(DROITE(G13:G14;2))>=B17)*(H12:I12=C17)*(H13:I14))
Bonne journée :cool:
 

Drekki

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

Je te remercie beaucoup, c'est effectivement beaucoup moins à rallonge comme formule.
Sans vouloir abuser, pourrais tu m'expliciter un peu les fonctions que tu emploies afin que je puisse adapter cette formule à d'autres cas de figure ?

Merci encore pour la rapidité et la pertinence de ta réponse.
 

JNP

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

Re :),
C'est surtout qu'elle ne variera pas (en dehors des zones à adapter) en fonction de l'augmentation de ton tableau :p.
GAUCHE(G13:G14;2) récupère l'âge inférieur en prennant les 2 caractères de gauche (20 à 59) et DROITE l'âge supérieur. Ne pas dépasser 99 ans et saisir 09 pour 9 ans :D...
CNUM transforme ces 2 caractères en nombres.
Ensuite SOMMEPROD fait la multiplication de ta matrice en sachant qu'un test vérifié vaut 1 et faux vaut 0, soit "Si supérieur ou égal à min" * "Si inférieur ou égal à max" * "Si SC = 1 ou 0" * "Matrice des résultats".
J'espère que c'est clair :eek:...
Bon courage :cool:
 

eriiic

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

Bonjour tout le monde,

Une autre proposition qui te permet de te passer du tableau intermédiaire.
Dans 'Age' ne mettre que la borne inférieure, cad 20 et non '20 à 59'

=(D17-(DECALER($B$2;EQUIV(B17;$B$2:$B$5;1)-2+C17;2)))/(DECALER($B$2;EQUIV(B17;$B$2:$B$5;1)+C17-2;3))

EQUIV(B17;$B$2:$B$5;1) te donne la ligne de $B$2:$B$5 où se trouve la valeur de B17, donc celle de tes données
(faire -1 dessus pour ramener l'offset de décalage à 0, et encore -1 car ton SC va de 1 à 2 et non de 0 à 1, d'où le -2)
DECALER($B$2;...;2) pour utiliser la moyenne
DECALER($B$2;...;3) pour utiliser l'écart type

eric
 

Drekki

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

Bonjour,

Je vous remercie beaucoup tous deux, j'ai appris des choses et j'aime ça !

J'ai adopté la solution d'eriiiic, car effectivement l'économie du tableau intermédiaire m'arrange beaucoup. Surtout que j'obtiens ainsi un document plus clair, avec une feuille contenant les formules et les données à remplir, et une autre servant de référence de normes. (Ce qui est très important pour moi, car il s'agit à terme de convaincre des collègues que l'informatique est un outil efficace qui simplifie certaines choses).

J'essaie donc de développer le truc avec d'autres tests et d'autres normes, et j'aimerais m'assurer que je n'ai pas compris de travers.
- Tu n'as pas mis le -2 à la même place selon sa première ou deuxième occurrence, mais en fait, peu importe n'est ce pas ?
- Ce -2 restera constant dans toutes les adaptations de cette formule à d'autres normes puisque je n'aurais jamais de valeur 0 donc je dois systématiquement décaler ?
- Que signifie le [type] dans la formule EQUIV ?

Je me lance maintenant dans l'inclusion d'un troisième (sexe) et d'un quatrième critère (version du test), et c'est un peu coton. J'ai déjà arrangé le tableau de données pour garder la logique du décalage, je vais voir comment goupiller tout ça.

Si j'arrive à mes fins, je vous montrerai, chers professeurs !

Bonne journée :)

Edit : J'ai réussi à inclure le critère sexe par un si, en revanche pour la version du test, je n'arrive pas à formuler mon idée. Les différentes versions n'étant que d'autres colonnes, je me disais que je pourrais inclure un décalage supplémentaire, mais pour ce faire, il me faut donner un équivalent nombre à un texte (toujours dans le souci d'une utilisation simple au final), c'est-à-dire que si la version utilisée est r, il n'y ait qu'à taper r qui pourrait renvoyer à 2 dans une autre cellule(appelons la , et dont je pourrais me servir dans la formule final avec un +tellecellule. Mais pour l'instant je bute, parce qu'avec des si, ça ressemble à rien
 

Pièces jointes

  • RecapNormes2.xls
    23.5 KB · Affichages: 104
  • RecapNormes2.xls
    23.5 KB · Affichages: 105
  • RecapNormes2.xls
    23.5 KB · Affichages: 110
Dernière édition:

Drekki

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

Toujours en train de me battre avec mes tites normes, et surtout de rendre le résultat glamour pour les collègues, j'ai voulu qu'une cellule contenant une formule qui manque de référence garde un vide pour affichage.
(je n'ai malheureusement pas trouvé d'autres solutions que d'avoir des lignes non renseignées pour les versions alternatives des tests, vu que je n'arrive pas à ajouer un critère, textuel de surcroît).
J'ai donc mis en E23 un si(D23="";E23="";et là ma grosse formule), et Excel l'a mal pris.
D'une part il me note 0 dans ma cellule, et je ne vois pas trop d'où il vient, d'autre part, il me demande de choisir un nombre d'itérration car la formule contient une référence circulaire.
Donc là, je suis plutôt embarrassée.
Auriez vous des suggestions ? J'aimerais soit trouver une autre technique pour ne pas avoir à laisser des lignes vides, soit une technique pour que les vides ne comptent pas pour zéro.
 

Pièces jointes

  • RecapNormes2.xls
    25 KB · Affichages: 136
  • RecapNormes2.xls
    25 KB · Affichages: 136
  • RecapNormes2.xls
    25 KB · Affichages: 120

eriiic

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

Bonjour,

Un peu de mal à te suivre... Surpris ? ;-)

- Tu n'as pas mis le -2 à la même place selon sa première ou deuxième occurrence, mais en fait, peu importe n'est ce pas ?
heuu oui, x-2=2-x donc pas de pb

- Ce -2 restera constant dans toutes les adaptations de cette formule à d'autres normes puisque je n'aurais jamais de valeur 0 donc je dois systématiquement décaler ?
Je ré-explique mais je pense que tu as compris.
Tant que ref dans DECALER(ref;colonne;ligne) sera sur la même ligne que ta 1ère donnée, et tant que SC démarrera à 1, oui il faudra corriger ligne avec un offset de -2

- Que signifie le [type] dans la formule EQUIV ?
La meilleure explication te sera donnée si tu positionnes ton curseur sur un de ses paramètre et que tu cliques sur EQUIV dans le popup qui apparait.

J'ai donc mis en E23 un si(D23="";E23="";et là ma grosse formule), et Excel l'a mal pris.
Oui, il ne faut pas de référence circulaire (une cellule fait référence à elle même). Met :
si(D23="";"";...

eric
 

Drekki

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

Je te remercie, je suis rassurée d'avoir compris jusque là.
Je te prie de m'excuser pour mon manque de clarté, il est parfois difficile de juger ce qui est pur présupposé dans son propre raisonnement.
Je voulais dire que pour normer les résultats d'un patient à l'épreuve des fluences (le troisième tableau de la feuille "score"), il faut prendre en compte l'âge, le nsc, comme dans les tableaux des épreuves précédentes pour lesquels tu m'as aidé, mais également le sexe, ce que j'ai résolu en ajoutant un "si" à la formule, et aussi la version du test (souvent, pour observer l'évolution d'un patient, on lui fait passer le même test à une autre session, mais on prend une version alternative pour qu'il n'y ait pas d'effet d'apprentissage). Pour ce quatrième critère, je n'ai pas trouvé d'autre solution que de proposer à l'examinateur (ceux qui par la suite se serviront de ce fichier pour normer les résultats de leurs patients et qui veulent l'interface la plus simple possible) toutes les versions existantes et qu'il renseigne son score sur la bonne ligne.
Autrement, il m'aurait fallu imbriquer une horde de si, et je m'y serais perdue.

Ou alors faire un tableau intermédiaire, ce qui est dommage.
=SI(C22="p";E22;SI(C22="r";E23;SI(C22="v";E24;"")))

Et là c'est en mettant les formules, mais ça ne marche pas de toutes façons.
Code:
=SI(C22="p";SI($C$3=1;(D22-(DECALER(Normes!$A$34;EQUIV($C$2;Normes!$A$34:Normes!$A$39;1)-2+$D$19;2)))/(DECALER(Normes!$A$34;EQUIV($C$2;Normes!$A$34:Normes!$A$39;1)+$D$19-2;3));(D22-(DECALER(Normes!$A$42;EQUIV($C$2;Normes!$A$42:Normes!$A$47;1)-2+$D$19;2)))/(DECALER(Normes!$A$42;EQUIV($C$2;Normes!$A$42:Normes!$A$47;1)+$D$19-2;3)));SI(C22="r";SI($C$3=1;(D23-(DECALER(Normes!$A$34;EQUIV($C$2;Normes!$A$34:Normes!$A$39;1)-2+$D$19;4)))/(DECALER(Normes!$A$34;EQUIV($C$2;Normes!$A$34:Normes!$A$39;1)+$D$19-2;3));(D23-(DECALER(Normes!$A$42;EQUIV($C$2;Normes!$A$42:Normes!$A$47;1)-2+$D$19;2)))/(DECALER(Normes!$A$42;EQUIV($C$2;Normes!$A$42:Normes!$A$47;1)+$D$19-2;5)));SI(C22="v";SI($C$3=1;(D24-(DECALER(Normes!$A$34;EQUIV($C$2;Normes!$A$34:Normes!$A$39;1)-2+$D$19;6)))/(DECALER(Normes!$A$34;EQUIV($C$2;Normes!$A$34:Normes!$A$39;1)+$D$19-2;3));(D24-(DECALER(Normes!$A$42;EQUIV($C$2;Normes!$A$42:Normes!$A$47;1)-2+$D$19;2)))/(DECALER(Normes!$A$42;EQUIV($C$2;Normes!$A$42:Normes!$A$47;1)+$D$19-2;7)));"")))
Je ne suis pas sûre que mon explication soit bien plus claire, je l'espère en tout cas. Et puis ma solution de facilité, certes un peu moins ergonomique, reste tout à fait acceptable en terme de simplicité d'utilisation.

Bonne journée !
 

Pièces jointes

  • RecapNormes2.xls
    26.5 KB · Affichages: 109
  • RecapNormes2.xls
    26.5 KB · Affichages: 115
  • RecapNormes2.xls
    26.5 KB · Affichages: 115

eriiic

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

Bonjour,

Je crois que tu as atteint les limites de lisibilité pour les formules et que ça sera de plus en plus dur (voir risqué) de modifier ou faire évoluer...

Je te propose de changer ton fusil d'épaule et de partir sur une fonction personnalisée.
Ex de syntaxe :
=deviance(table, sexe, age, nsc, libellé)
J'en ai démarré une, pour l'instant elle ne retourne que la cellule où se trouve le paramètre m à utiliser en fonction des paramètres d'entrée (ça pourrait être des listes déroulantes pour certains) que tu lui as fourni.
Il faudra ajouter des paramètres d'entrée et que tu me rappelles la formule de calcul pour la compléter (ça ne sera pas le plus difficile, déjà il faut récupérer les bons paramètres dans les différentes tables).

Je ne l'ai testée que sur 2 tables (regarde en B37:G37), il faudrait que tu confirmes si tu es d'accord sur l'idée avant de développer le reste.
Il y aura certaines règles à respecter pour la construction de tes tables. Pour celles qui ne marchent pas on verra s'il faut modifier leur présentation ou le programme.

J'espère que tu vois où je veux en venir... ;-)
Dis moi ce que tu en penses

eric
 

Pièces jointes

  • RecapNormes2-3.zip
    23.8 KB · Affichages: 70
  • RecapNormes2-3.zip
    23.8 KB · Affichages: 69
  • RecapNormes2-3.zip
    23.8 KB · Affichages: 70
Dernière édition:

Drekki

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

Ah bah tout de suite c'est la classe, ça augmente nettement l'ergonomie.
Tu es allé plus loin que ce que j'imaginais possible !

La formule pour calculer l'écart à la moyenne d'un score est (score-moyenne)/écart-type. Cela nous donne z, le nombre d'écart type de différence par rapport à la moyenne.
(D'ailleurs faudra que je magouille un peu pour les tests dont les performances se notent en secondes, parce que là contrairement aux autres tests, plus le chiffre est petit plus on est bon).

Sinon je ne sais pas si la liste déroulante des tests est utile, vu qu'il serait plus lisible d'avoir un tableau pour entrer le score par test plutôt que de le choisir à chaque fois. C'est vrai que parfois on fait tel test et pas un autre, et que dans l'idéal faudrait que je mette tout les tests possibles, mais bon, y en a quand même qui sont systématiquement utilisés.
De plus, je bûche (ou bute https://www.excel-downloads.com/thr...en-fonction-dune-ligne-et-dune-valeur.148146/) sur d'autre type type de norme. Donc il faudra peut-être mélanger des tableaux qui n'ont pas la même solution.

Je me sens un peu frustrée de ne pas percevoir la face cachée de tes manips, parce que j'aime bien mettre les mains dans le cambouis.

Je te remercie énormément de toute l'aide que tu m'apportes.
 

eriiic

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

Bonjour,

J'ai ajouté le paramètre score à la fonction, seulement je ne retourne pas le même résultat que toi.
Si tu pouvais contrôler avec les paramètres de la ligne 37 ce qu'il faut calculer... Moi je calcule (score-moyenne)/écart-type, cad: =(15-16.07)/5.66 => -0.189

je ne sais pas si la liste déroulante des tests est utile
Tant que tu es sûr que la saisie est strictement égale à ce qui est inscrit dans la feuille norme ça marchera

De plus, je bûche (ou bute ) sur d'autre type type de norme. Donc il faudra peut-être mélanger des tableaux qui n'ont pas la même solution.
Il faudrait prévoir d'inscrire en feuille Norme à droite du nom de la norme le type de tableau (selon sa construction).
Par exemple 1 pour ceux de type fluences, BEM144; 2 pour DO80; etc

Au passage si tu fais 'insertion / fonction... / personnalisée' les noms en clair des paramètres attendus s'affichent

Par contre évite de laisser passer une semaine entre 2 compléments, c'est un peu dur de devoir réassimiler ton tableau à chaque fois...

eric
 

Drekki

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

Bonjour,

Je te prie de m'excuser pour les délais, grosse semaine qui a fait que je n'ai pas pu m'y pencher rapidement.

Pour un homme de 50 ans ayant un score de 15 et passant la version "animaux", je me trouve avec (15-33,64)/6,59= -2,82

Pour le nom des tests, je me disais qu'il était possible de les fixer une fois pour toutes, et ne pas donner le choix. Un peu comme ma présentation du début, où un tableau dans lequel renseigner les scores égale un test. Si on inscrit le score dans le tableau de la BEM, on se retrouve avec l'écart à la moyenne pour la BEM.

Par contre j'ai un message disant que "impossible de traiter votre question. Soit Excel ne trouve pas de fonction équivalente, soit l'aide n'est pas installée". Et si je clique quand même sur la fonction personnalisée 'recapNormes2-3.xls'!Module1.deviance, j'ai le même message plus Cette fonction ne possède pas d'argument.
Cela a peut-être un lien avec le niveau de sécurité et la macro dont il me parle lorsque j'ouvre le fichier. Il désactive les macros en l'absence d'un certificat de l'auteur.
 
Dernière édition:

eriiic

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

Pour un homme de 50 ans...
Si tu pouvais rester sur les 2 exemples de la feuille et me dire si c'est bon ou mauvais, et si mauvais pourquoi (quelles cellules et quelles valeurs de Normes il faut utiliser) ça serait bien
eric
 

Drekki

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

Dans le tableau vert que tu as fait au milieu, on a comme paramètre sexe 2 (homme), age 50, nsc 1 et libellé Animaux. Ce qui fait une moyenne de 33,64 (!Normes I36) et un écart type de 6,59 (!Normes J36) pour ces paramètres là.
Par contre en G37, c'est marqué #NOM?

Pour le second exemple, pour 50 ans avec un nsc à 3 et un libellé RIE v-v, on trouve une moyenne de 9,42 (!Normes G11) et un ecart type de 1,42 (!Normes H11).
Donc pour le second exemple ça marche tout à fait puisque la formule renvoie "m en Normes!G11", ce qui est tout à fait exact.
 

Discussions similaires

Statistiques des forums

Discussions
314 450
Messages
2 109 724
Membres
110 552
dernier inscrit
jasson