Un QCM avec Calc

jeudi 21 mars 2019, par ID

À l’origine, un projet d’e-learning qui date quasiment de Mathusalem et qui n’a pas vraiment abouti. Mais il restait toujours dans un coin du disque dur et de ma cervelle cette possibilité de faire des QCM avec un tableur. Le classeur devait pouvoir calculer le nombre de bonnes réponses, n’afficher les solutions qu’aux questions ayant eu des réponses et délivrer un score avec un petit message spécifique en fonction du nombre des bonnes réponses. Et, évidemment, il devait être facile à faire évoluer.

Picto tableurPour concevoir ce genre de document, il n’est pas nécessaire du tout de connaître un langage de macro. En fait, pour tout dire, il s’inscrit dans mon optique qui est d’utiliser au maximum les fonctionnalités et, dans le cadre d’un tableur, les fonctions, des logiciels avant de commencer à penser « macro ».

 Qu’attend-on d’un QCM ?

Cette réflexion préalable guide nos pas tout au long de la construction du modèle.

Ici, l’idée c’était :

  • d’avoir des réponses que l’on pouvait compter ;
  • un calcul automatique des bonnes réponses sur le nombre total de questions posées ;
  • la solution qui apparaît uniquement après qu’une réponse ait été donnée ;
  • d’avoir un document facile à faire vivre parce qu’on ne va pas se cantonner à un seul QCM et qu’on ne va pas réinventer la roue à chaque fois.

 Quelles fonctionnalités et fonctions du tableur utiliser ?

Rien de très compliqué sauf la rédaction de la formule avec le message lié au score, non qu’elle soit vraiment complexe, mais, comme toujours, l’ordre des éléments et la qualité de la syntaxe sont essentiels.

Pour les fonctionnalités, on reconnaîtra ma « marque de fabrique » [1], je reconnais, en effet, les utiliser très souvent :

  • fonctions et fonctionnalités basées sur des Plages et expressions nommées, ce qui permet une meilleure évolutivité du classeur et facilite la mise en place des diverses formules en les documentant ;
  • mises en forme conditionnelles pour faire ressortir les bonnes et les mauvaises réponses et surtout faire afficher ou non les solutions en fonction de la présence d’une case renseignée ;
  • des styles, de toute façon impératifs avec les mises en forme conditionnelles dans Calc ;
  • la protection des feuilles alliée au déverrouillage des cellules où l’utilisateur doit intervenir.

Pour les fonctions :

  • une petite fonction MAX qui permet savoir automatiquement le nombre total de questions, pratique pour faire vivre le classeur, plus besoin de changer les formules en fonction de leur nombre ;
  • un NB.SI pour déterminer le résultat, à savoir le nombre de réponses exactes, ce qui donne le score automatiquement ;
  • des collages avec liaisons pour afficher la réponse donnée avec la solution et comparer (et vérifier aussi) ;
  • la fonction SI soit toute seule quand il s’agit de dire si la réponse est bonne ou pas, soit imbriquée pour le message délivré dans la feuille de score et qui s’adapte selon un barème modifiable sans qu’il soit nécessaire de modifier la formule. La complexité de cette formule réside dans le nombre de SI imbriqués et leur ordre. Ce dernier est primordial pour avoir les résultats attendus. Il faut tester, tester et encore tester.

Et c’est tout ! Mais ça suffit pour faire un QCM tout à fait utilisable et efficace.

 La structure

Une première feuille, le questionnaire dont les réponses s’appuient sur une liste figurant dans une feuille spécifique où elles sont toutes réunies.

La feuille questionnaire
L’utilisateur choisit la réponse dans une liste.

Une deuxième feuille avec les solutions qui va, d’une part afficher la bonne réponse, en allant chercher sa lettre (ce pourrait être des nombres) dans une liste figurant elle-même dans une liste avec un texte complémentaire (visée pédagogique) qui est, lui, saisi dans une cellule à côté de l’indicatif de la solution, et, d’autre part, afficher la réponse de l’utilisateur, collage avec liaison, en délivrant un message qui varie selon la qualité de la réponse, fonction SI. Comme indiqué plus haut, la solution n’apparaît que s’il y a une réponse à la question, plus précisément, si la case de réponse est renseignée. On appréciera tout l’intérêt des mises en forme conditionnelles qui permettent de faire cela.

Les solutions
Pour plus d’efficacité pédagogique, il y a un rappel de la réponse donnée par l’utilisateur.

Une troisième avec le score. Il indique le nombre total de bonnes réponses à partir d’un calcul effectué sur la feuille qui abrite toutes les listes et affiche un message en fonction du score et d’un barème qui figure sur la feuille des listes. L’intérêt d’avoir une feuille à part pour le score est qu’on peut rajouter d’autres informations. Idéalement, le message lié au score devrait pouvoir afficher des liens, mais, ce n’est pas possible. La fonction SI ne les garde pas. Il aurait fallu encore complexifier la formule et trouver le moyen de rajouter une fonction LIEN.HYPERTEXTE, j’imagine, je n’ai même pas essayé. D’un autre côté, est-ce bien nécessaire dans ce cadre précis ?

Le score
En fonction du nombre de bonnes réponses sur le nombre total de question, le message qui s’affiche est différent.

La quatrième feuille est celle qui sert de base à tous les calculs, ou presque, et peut être masquée par la suite.

Une feuille « pivot »
La feuille qui sert à faire tout le travail, tous les éléments font l’objet de plages pour plus de facilité de modification du contenu du classeur.

 Quelques astuces et une limite

Pour faciliter le travail, quelques suggestions :

  • donner des noms parlants, cohérents et pas trop longs à vos plages, éviter les abréviations absconses ;
  • nommer à peu près tout ce qui sert de source aux calculs, ainsi, en n’utilisant que des noms dans les formules, vous les documentez comme je l’ai indiqué, mais, surtout, il est facile de modifier le contenu du classeur sans avoir à retoucher les formules ;
  • pour les fonctions un peu compliquées, surtout longues, l’Assistant fonction de Calc est d’une grande aide puisqu’il vous permet de construire les formules en les imbriquant les unes dans les autres, n’hésitez pas également à recourir à un éditeur de texte pour les écrire, visuellement c’est plus facile.

La limite, c’est que le classeur ainsi conçu ne peut accepter qu’une réponse par question, pas de possibilité d’avoir plusieurs réponses. Mais, je suis sûre qu’en réfléchissant un peu, cela ne devrait pas être beaucoup plus compliqué.

 Et si on veut le modèle tout fait ?

On peut aller télécharger un modèle de classeur construit sur ces bases sur le site numericoach.net avec un QCM tout fait « en vrai » pour voir comment ça fonctionne. Il est protégé, mais sans mot de passe.

Les explications figurent dans les commentaires de la feuille Listes et dans les commentaires des Propriétés du fichier.


Pour compléter : pour récupérer le fichier modèle de QCM


[1Voir, par exemple, ce tutoriel sur la conception d’un planning annuel.