[Tutoriel] Calculer automatiquement des écart-types à l’aide d’une feuille de calcul

Je pense que pour vous comme pour moi, rédiger des comptes-rendus de bilan n’est pas une partie de plaisir. Je pensais qu’avec les années j’arriverais à les faire de plus en plus rapidement, mais ce n’est pas vraiment le cas.

J’ai quand même 3 astuces principales pour gagner un peu de temps :

  • utiliser des modèles de comptes-rendus,
  • utiliser un logiciel de dictée vocale (tutoriel à venir sous peu),
  • utiliser des feuilles de calculs pour analyser les résultats des épreuves,

En ce qui concerne ces dernières, je sais qu’elles circulent régulièrement sur les réseaux, vous pouvez trouver par exemple certains tableaux sur Pontt. Mais l’une de mes lectrices m’a demandé aujourd’hui comment elle pouvait rentrer les formules dans Excel pour en créer elle-même.

Utiliser Libre Office pour calculer automatiquement les écart-types à partir des notes brutes

Voici donc un petit tutoriel créé avec l’outil Classeur de Libre Office (fonctionne exactement de la même manière avec Microsoft Excel, sauf mention contraire de ma part)

Tout d’abord il faut créer un tableau « classique » avec les colonnes suivantes : nom de l’épreuve, note brute du patient, écart-type du patient, moyenne de la population, écart-type de la population.

Ensuite vous allez reporter les données de votre tableau papier dans votre feuille de calcul (bien vous relire pour vérifier qu’il n’y a pas d’erreur de frappe).

Il s’agit maintenant de créer la formule qui va calculer l’écart-type du patient en fonction de sa note brute, de la moyenne et de l’écart-type de la population. Pour cela :

  • Placez-vous dans la première case de votre colonne « écart-type du patient » (ici C2).
  • Tapez le signe égal, ouvrez une parenthèse puis cliquez sur la première case de votre colonne « note brute du patient » (ici B2).
  • Tapez le signe – et cliquez sur la première case de la colonne « moyenne de la population » (ici D2).
  • Fermez la parenthèse, tapez le signe /, puis cliquez sur la première case de la colonne « écart-type de la population » (ici E2).
  • Vous devez maintenant obtenir une formule du type        =(B2-D2)/E2
  • Validez en appuyant sur Entrée

Vous pouvez maintenant vérifier que votre formule fonctionne bien en testant différentes valeurs dans la première case de votre colonne « note brute » (ici B2) et voir les effets sur votre écart-type nouvellement calculé.

Pour les lignes suivantes le travail va être beaucoup plus simple, il ne vous reste plus qu’à vous placer dans le coin inférieur droit de la case dans laquelle vous aviez créé votre formule jusqu’à ce que votre curseur se transforme en une croix. Cliquez à cet endroit-là, et faites glisser votre curseur sur les cases en dessous en maintenant le bouton de la souris appuyé. Et voilà, la formule a été recopiée vers le bas, et le nom des cellules s’est automatiquement ajusté.

Si on vérifie, dans la case C3, la formule est donc =(B3-D3)/E3 et dans la case C4 =(B4-D4)/E4

Si vous souhaitez enlever des décimales dans l’affichage de vos résultats, cliquez tout simplement sur la petite icône surlignée en jaune sur l’image suivante.

Changer la couleur du fond d’une case en fonction du résultat

Pour aller plus loin, il est possible de formater un peu les résultats pour que les épreuves très chutées soient mises en valeur.

Dans Libre Office

Sélectionnez les cases concernées et cliquez sur « Format » > « Formatage conditionnel ».

Complétez les cases tel qu’indiqué dans la copie d’écran ci-dessous (en changeant les valeurs si vous souhaitez en utiliser d’autres).

Pour changer le remplissage de la cellule il va falloir créer un Nouveau Style, et dans le menu qui apparait choisir une couleur d’arrière-plan.

Validez et désormais la couleur de votre case changera automatiquement, en jaune pour les résultats compris entre -1,5 et -2 ET, et en rouge pour ceux inférieurs à -2 ET.

Dans Microsoft Excel

Sélectionnez les cases concernées et cliquez sur « Mise en forme conditionnelle » > « Nouvelle règle ».

Complétez les cases tel qu’indiqué dans la copie d’écran ci-dessous (en changeant les valeurs si vous souhaitez en utiliser d’autres).

Validez et désormais un petit feu tricolore s’affichera automatiquement dans votre case, en vert si le résultat est supérieur à -1,5 ET, en jaune s’il est compris entre -1,5 et -2 ET, et en rouge s’il est inférieur à -2 ET.

Pour enregistrer vos tableaux d’étalonnage pensez à utiliser les modèles de documents (extension .ots sous Libre Office, .xlt ou .xltx avec Microsoft Excel).

Voilà, j’espère que ce tutoriel vous aura aidé à créer de belles feuilles de calcul et vous permettra de gagner du temps dans l’analyse des résultats de vos patients.

Vous avez aimé cet article ? Partagez-le avec vos amis sur Twitter ou Facebook, et inscrivez-vous à la Newsletter pour recevoir tous les nouveaux billets d’Ortho & Co directement dans votre boîte mail.

27 réflexions sur « [Tutoriel] Calculer automatiquement des écart-types à l’aide d’une feuille de calcul »

  1. merci pour ce tuto; je vais l’imprimer pour m’entrainer.
    auras-tu le temps un jour de nous en faire un sur la conversion d’un PDF? 🙂
    merci d’avance!

  2. Mais, mais… C’est GÉNIAL !!! Merci 100 fois, cela fait des mois que je me dis qu’il faut que je crée ces fichus tableaux excell pour que mes compte-rendus soient un peu moins galères à écrire, amis je ne trouve jamais le temps (ni le courage…) de m’y mettre. Grâce à ce billet, je découvre un logiciel libre et une manière de faire qui me convient tout à fait ! Merci beaucoup !

  3. Ping : Statistiques | Pearltrees

  4. Ping : Cabinet | Pearltrees

  5. Merci beaucoup, ce tutoriel m’a permis de me mettre à excel en un clin d’oeil. Je vais enfin pouvoir me créer une base de données pour effectuer mes comparaisons test-test, patient-patient etc.

  6. Bonjour,

    Merci pour ce tutoriel qui m’a permis de connaître la technique pour mettre des cellules en couleur selon la valeur ! très utile ! 🙂

    Je peux rajouter deux petits conseils :
    –> pour ne pas voir apparaître l’écart type quand le score n’est pas marqué, il faut inscrire une formule un tout petit peu plus complète :
    Au lieu d’avoir =(B4-D4)/E4
    Inscrivez : =SI(B4="";"";(B4-D4)/E4)
    ça fonctionne sur Excel (pas sur que la fonction SI fonctionne sur libre office, elle ne fonctionne pas sur drive par exmple…)

    –> Autre chose, si vous avez une case avec le temps et non un score (temps de lecture d’un texte par exemple), avoir un temps élevé sera donc moins bon. Il faut juste penser à mettre un signe moins devant la formule [tel que =-(B4-D4)/E4], sinon avec un temps de 600 secondes en lecture on peut se retrouver avec du +55 écart-type ! 😀 (c’est utile pour des fichiers de la BALE ou de l’ECLA 16+ par exemple)

    Je viens de m’en faire un pour l’ECLA 16+ et je vais en faire un autre pour la BELO mais ce ne sont pas des écart-types mais des centiles… Avez-vous un conseil pour m’aider à le réaliser? Je rame un peu… 🙁

    • Mince, je vois que les guillemets américains sont passés en guillemets français dans la formule SI ! 🙁 Je ne sais pas si ça change grand chose, mais sur excel ils mettent automatiquement les guillemet américains… 😉
      Attention : il n’y a pas d’espace entre les guillemets ! (ils ont été mis automatiquement avec le format français, mais ne sont pas à mettre dans la formule)

      • Bonjour Maxime,
        J’ai corrigé le petit bug d’affichage des guillemets dans votre commentaire, pas de panique.
        Merci beaucoup pour les petits conseils supplémentaires, je suis certaine que les autres lecteurs apprécieront (sans compter que je ne connaissais pas non plus l’astuce pour ne pas voir apparaitre l’écart-type quand le score n’est pas marqué).
        Concernant les centiles il faut faire des choses un peu plus complexes que ce dont je parlais dans ce tutoriel :
        – créer les tableaux de valeurs brutes dans des feuilles de calculs séparées (une feuille par épreuve), avec une colonne « score brut », et ensuite une colonne « centile » par étalonnage ex pour la feuille LECTURE :
        Score Brut | CP |CE1
        28 | 15 | 5
        – dans la feuille qui sera utilisée pour les résultats (une par année d’étalonnage), vous pouvez créer une colonne « score du patient » et une colonne « percentile patient ». Exemple pour la feuille CP à compléter comme ceci :
        Score Brut | Percentile patient
        (à compléter) | =RECHERCHEV(case du score brut;LECTURE!zone du tableau sur la feuille dite;numéro de la colonne correspondante au CP)

        Exemple de formule complète : =RECHERCHEV(B4;LECTURE!A3:E28;2)
        Vous pouvez même rajouter votre condition préférée : =SI(B4="";"";RECHERCHEV(B4;LECTURE!A3:E28;2))

        Enfin il est possible de rajouter un petit graphique qui permettra de visualiser le résultat (avec un graphique en barres horizontales par exemple).

        Voilà, j’espère que ce n’est pas trop obscur, n’hésitez pas à revenir vers moi si besoin !

        • SUPER ! merci beaucoup pour votre réactivité ! je me creuse la tête depuis deux jours à chercher comment faire ! 🙂

          Je vais tester ça tout de suite !!!
          merci merci merci !

          • ça fonctioooooooooooooooooooooooonne !!!!!!!!!!!!!!!!

            MERCI !!!

            (j’ai un peu ramé quand même ! mais bon maintenant que j’ai compris il ne reste plus qu’à remplir mes tableaux !!) 😀

  7. Bonjour,

    je serais intéressée pour avoir des conseils car je n’arrive pas à faire un tableau avec des centiles!! 🙁 même en suivant vos instructions, j’ai loupé quelque chose!)

    • Bonjour Xela,
      C’est qu’en fait les centiles et les écarts-types ne sont pas du tout les mêmes outils statistiques. Avec les instructions de ce billet il est possible de calculer les écarts-types, mais pas de montrer la répartition en centiles.
      Pour les centiles il faudrait intégrer dans une feuille différente les tableaux de données, et utiliser des formules qui diraient en substance « si le score pour cette épreuve est compris entre x et y alors cela correspond à c10, mais si le score est compris entre y et z alors cela correspond à c25, etc… »
      Si jamais je trouve le temps prochainement j’en ferai un billet à part entière avec un exemple concret.
      Bon courage !

        • Bonjour! Alors voilà j’ai réussi à trouver les formules pour calculer les centiles et réussi à faire une mise en forme conditionnelle de la cellule et créer un graphique à partir de cela.
          Mais pour pousser un peu j’aimerais faire une mise en forme conditionnelle du graphique . Je sais que c’est possible mais est-ce que cela peut l’^étre en fonction de celle du tableau? et se faire directement?
          ex: si centile patho dans le tableau est rouge que la représentation sur le graphique soit aussi en rouge mais qu’elle se soit faite toute seule?

          Je ne sais pas si je suis très claire !

          Merci pour votre aide 🙂

  8. Bonsoir,

    J’ai bien compris votre tutoriel pour les écarts-types. En revanche, malgré vos explications dans les commentaires pour les percentiles, rien n’y fait je n’y arrive absolument pas -_-
    J’aimerai faire une feuille excel pour les résultats en percentile pour le test de chronodictées (année 6e) avec un code couleur.
    Merci beaucoup!

    • Bonsoir Suzon,
      J’arrive un peu après la bataille mais peut-être que ce tutoriel vous aidera ? Si ce n’est pas le cas je me note de faire un nouveau tutoriel pour la mise en couleur des percentiles.
      Bonne continuation,
      Lydie

Répondre à Veyret ClaireAnnuler la réponse.