Dans ce cas d’usage sur Google Sheets nous allons voir comment réaliser un questionnaire avec des résultats automatiques. Avec Google Sheets de créer un questionnaire personnalisé pour obtenir directement des résultats que nous pouvons afficher sous forme de pourcentage ou encore de graphique par exemple.
Niveau : Avancé
ℹ️ Information : Si vous n’êtes pas familier avec Google Sheets je vous recommande de regarder les deux articles et vidéos « Les indispensables Google Sheets » ou pour maîtriser pleinement la solution suivre les formations en ligne.
Questionnaire d’audit, d’auto-évaluation, etc…
Cette solution peut notamment être très intéressante à utiliser dans le cadre d’une auto-évalution ou encore d’un Audit. Par exemple, vous pouvez avoir plusieurs points de contrôle à vérifier avec un client et après que celui-ci ai répondu à toutes vos questions, vous avez automatiquement le résultat. Ce qui vous permet de savoir tout de suite à quel niveau agir.
ℹ️ Information : il est également possible de créer un questionnaire de ce type de la solution Google Forms. Cependant, avec Google Sheets nous pouvons aller plus loin et être plus précis dans notre résultat. Par exemple définir un nombre de points différents en fonction des réponses proposées.
Démonstration vidéo
Questionnaire « Windtopik »
Pour illustrer ce cas d’usage, je vous propose un questionnaire sur les solutions et outils développés sur le site. En plus de découvrir comment réaliser un questionnaire avec des réponses/résultats automatiques, vous pouvez également vérifier vos connaissances sur ces sujets. 😉
🎯 Objectifs
Avant d’aller plus loin sur le cas d’usage, voici un récapitulatif des objectifs que permet d’atteindre ce questionnaire.
- Tester les connaissances sur différentes sujets de Windtopik.
- Afficher un score spécifique en fonction de la réponse à une question.
- Afficher automatiquement les résultats sur différentes formes (graphiques, pourcentage, …).
- Obtenir un rapport d’une situation à un instant « T ».
Évidemment cette liste n’est pas exhaustive. Elle peut être bien plus longue lorsqu’elle est appliquée à des sujets plus spécifiques par exemple.
📝 Description
Dans ce cas, le questionnaire est composé de 5 feuilles comme le montre la copie d’écran ci-dessous :
Feuilles type « Quiz » (Google Workspace, Raspberry-PI, Evernote, Asana)
Quiz avec des questions de type « case à cocher » avec un calcul automatique du score en fonction de la réponse.
Feuille « Résultat »
Tableau de bord qui affiche de façon détaillée les scores obtenus aux différents Quiz ainsi qu’une note « globale ».
⚙️ Fonctionnement
Voyons maintenant comme fonctionne concrètement ce questionnaire et les fonctionnalités qui ont été utilisées pour le créer.
Feuille de type « Quiz »
Voyons pour commencer le fonctionnement de la feuille de type « Quiz ». Dans l’exemple, nous avons 4 feuilles qui sont créées plus ou moins de la même façon en fonction des questions et des réponses attendues.
Je vais développer ici la création de la feuille « G Suite ». En ce qui concerne la mise en forme rien de très compliqué ici. Pour obtenir ce « design », j’ai utilisé les fonctionnalités suivantes :
- Insertion d’image (logo windtopik),
- Police « conforta »,
- Fusion de cellules (titre de la rubrique, questions),
- Case à cocher,
- Désactivation du quadrillage.
Le plus dur dans cette feuille est évidemment l’utilisation des fonctions. Elles sont notamment utilisées pour …
- Calculer le résultat d’une question en fonction de la réponse,
- Afficher un message d’erreur si deux cases (ou plus) sont cochées pour une même question – Dans le cas où il n’y a qu’une réponse possible.
- Cas particulier – Plusieurs réponses possibles.
- Réaliser le total des points de la rubrique.
ℹ️ Information : Notez bien avant d’aller plus loin que la case à cocher affiche « VRAI » lorsque la case est cochée et « FAUX » lorsqu’elle n’est pas cochée.
1 – Résultat de la question en fonction de la réponse :
Pour calculer le résultat en fonction des cases à cocher, j’ai utilisé uniquement la fonction SI, mais avec des imbrications.
Par exemple, dans le cas de la question 2 de la rubrique G Suite, nous avons trois possibilités. Donc, nous allons devoir imbriquer une fonction SI dans une fonction SI autant de fois que nous avons de cas possible. Soit en fois 3.
Le résultat dans ce cas doit être « 1 » si l’on coche la case « Tableur » et « 0 » pour les autres cases (Traitement de texte, Présentation).
Ce qui nous donne la formule suivante dans la cellule « F12 » :
=SI(D14=VRAI;"0";SI(D15=VRAI;"1";SI(D16=VRAI;"0";"")))Littéralement parlant, nous pouvons décrire la formule comme ceci :
SI la cellule D14 est égale à « VRAI » alors j’affiche « 0 » Sinon >
SI la cellule D15 est égale à « VRAI » alors j’affiche « 1 » Sinon >
SI a cellule D14 est égale à « VRAI » alors j’affiche « 0 » Sinon je n’affiche rien.
Suite à cela, nous avons bien le résultat désiré (0 ou 1) en fonction de la case cochée. Cependant, si par erreur je coche deux cases, le résultat risque de ne plus être correct. Dans ce cas, nous allons simplement afficher un message d’erreur comme nous allons le voir ci-dessous. 😉
2 – Afficher un message d’erreur si deux cases sont cochées dans une même question :
Voyons ce cas de figure avec la question 3 de la rubrique « G Suite ». Dans ce cas, nous avons de réponse possible « Vrai » ou « Faux ». La bonne réponse étant « Vrai » et rapporte 2 points.
Cependant, si je coche les deux cases, je vais afficher un message d’erreur qui stipule qu’il ne faut sélectionner qu’une seule case. Pour cela, nous allons devoir utiliser les fonctions « SI » et « ET ».
Ce qui nous donne la formule suivante dans la cellule « E30 » :
=SI(ET(D28=VRAI;D29=VRAI);"Erreur de saisie ! Sélectionnez une seule case.";"")Littéralement parlant, nous pouvons décrire la formule comme ceci :
SI D28 est égal à « VRAI » ET que D29 est égal à « VRAI » alors j’affiche « Erreur de saisie ! Sélectionnez une seule case » Sinon je n’affiche rien.
L’affichage du message d’erreur se complique lorsqu’il y a plus de deux choix. Dans ce cas, il est notamment possible d’utiliser la fonction « OU » pour ajouter tous les cas de figure possibles (exemple ci-dessous avec l’affichage du message d’erreur pour la question 2 de « Google Workspace »).
3 – Cas particulier « Plusieurs réponses possibles » :
Pour illustrer ce cas, voyons la question 3 de la rubrique « Raspberry-PI ». Ici, pour avoir la bonne réponse à 3 points il faut cocher la case « 1 », « 2 » et « 4 ». Tous les autres cas sont égaux à 0 point.
Pour réaliser cela, nous allons utiliser à nouveau la fonction « SI » et « ET ».
Ce qui nous donne la formule suivante dans la cellule « F16 » :
=SI(ET(D18=VRAI;D19=VRAI;D20=VRAI;D21=VRAI);"0";SI(ET(D18=VRAI;D19=VRAI;D21=VRAI);"3";"0"))Littéralement parlant, nous pouvons décrire la formule comme ceci :
SI D18, D19, D20 ET D21 sont égaux à « VRAI » alors j’affiche « 0 » Sinon >
SI D18, D19 ET D21 sont égaux à « VRAI » alors j’affiche « 3 » Sinon dans les autres cas j’affiche « 0 ».
Pas besoin de message d’erreur, car il y a plusieurs réponses possibles.
4 – Réaliser le total des points de la rubrique :
Enfin, probablement le plus simple ici, faire le calcul de tous les scores.
Dans ce cas, j’ai simplement réalisé le total en cumulant les cellules avec le point des différentes questions.
Feuille « Résultats »
La feuille « résultats » est une sorte de tableau de bord qui permet de visualiser le score des différentes rubriques et un score total sur le questionnaire.
Voici les fonctionnalités utilisées dans cette feuille :
Score : Récupération des données en spécifiant la feuille puis la cellule.
Niveau : Utilisation de la fonction « Sparkline » pour afficher une barre dans une cellule.
Pourcentage : Affichage du taux de réussite en %.
Graphique : Affichage d’un graphique en radar pour visualiser rapidement les points forts / points faibles.
Obtenir le fichier « Modèle »
Suite à l’explication détaillée, voici le lien d’accès au fichier « Questionnaire Windtopik ». Cliquez simplement sur le bouton « Créer une copie » pour obtenir gratuitement le fichier modèle.
Vous pouvez alors le personnaliser et l’adapter à votre(vos) besoin(s). 😉
Pour conclure…
Voilà pour ce cas d’usage avec Google Sheets. Au-delà du questionnaire en lui même, c’est un exercice très intéressant à réaliser pour apprendre à maîtriser différentes fonctionnalités de la solution de tableur de Google.
J’espère que ce cas serez utile, que cela soit pour un usage particulier, our encore pour améliorer vos compétences sur Sheets. 😉
Portez-vous bien et à bientôt !