Le service Google Forms permet de créer des Google Forms en ligne et de récupérer les réponses sur une feuille de calcul Google Sheets. Lors de chaque réponse à un formulaire, Google ajoute automatiquement à la feuille de calcul la date et l’heure de validation du formulaire dans une colonne nommée horodateur. Nous détaillons dans ce tutoriel comment exploiter cette donnée pour effectuer des filtres par année à l’aide de tableaux croisés dynamiques. Nous préciserons dans de prochains tutoriels comment exploiter les données par mois, semaine, jour ou heure à l’aide de formules de calcul.
Information sur la création du formulaire
Pour toute information sur la démarche à suivre, n’hésitez pas à consulter nos tutoriels sur la création de formulaire avec la nouvelle interface de Google Forms.
Afin d’obtenir des données facilement exploitables sous forme de tableaux croisés dynamiques, il est conseillé de préférer des questions fermées (choix multiples) lors de la création du formulaire. Nous prenons dans notre exemple un fichier de suivi d’un accueil composé uniquement de 2 questions. Le formulaire sera complété en continu lors de chaque échange avec la personne en charge de l’accueil.
Une fois le formulaire en ligne, créez la feuille de calcul Googe Sheets qui récupérera chaque réponse sous forme de tableur. Remarquez la présence automatique de la colonne Horodateur, celle-ci ajoutera précisément la date et l’heure à chaque validation d’un formulaire. C’est cette donnée que nous allons exploiter.
Créer une seconde feuille pour exploiter les résultats
Nous allons commencer par créer une seconde feuille à notre Google Sheets en cliquant sur le + en bas à gauche. Effectuez un clic droit sur la nouvelle feuille puis renommer pour nommer la feuille BDD (base de données). Cette nouvelle feuille va nous permettre de récupérer l’ensemble des informations de la feuille Réponse au formulaire 1, en y ajoutant des colonnes spécifiques.
Afin de faciliter la création de formule de calcul, nous allons créer un identifiant unique pour chacune des lignes de notre tableau initial. Placez-vous en A1 puis indiquer le terme ID (pour identifiant) comme en-tête de colonne. Recopiez ensuite les en-têtes de colonne de la première feuille pour obtenir le résultat ci-dessous.
Pour créer l’identifiant, dans la cellule A2, indiquez 2 puis dans la cellule A3 =A2+1 afin d’incrémenter nos identifiants automatiquement.
Utilisez la poignée de recopie en effectuant un cliquer glisser vers le bas pour générer une centaine d’identifiants dans un premier temps. Vous pourrez ensuite ajuster en fonction du nombre de réponses à votre formulaires (ex : 1000 réponses = 1000 identifiants à créer)
A présent, nous allons récupérer les informations de la feuille Réponse au formulaire 1 dans nos 3 colonnes à l’aide de la fonction INDEX comme suivant :
Formule : B2 =index(‘Réponses au formulaire 1’!A$1:A;$A2;1)
Explication : B2 = Recherche dans la feuille Réponses au formulaire 1 le contenu de la colonne A correspondant au numéro de la ligne A2
Un aperçu du résultat s’affiche en bleu. Validez par Entrée puis utilisez la poignée de recopie pour copier la formule vers le bas.
Nous utilisons la même formule pour les colonnes C et D :
C2 =index(‘Réponses au formulaire 1’!B$1:B;$A2;1)
C3 =index(‘Réponses au formulaire 1’!C$1:C;$A2;1)
A noter que vous pouvez tout à fait recopier la formule vers la droite.
Voici à présent votre feuille de calcul pré-complétée. Le contenu d’un formulaire validé sera automatiquement recopié vers notre feuille BDD.
Exploiter les données par année à l’aide de formules de calcul
Nous ajoutons la colonne Année à notre feuille. Celle-ci va nous permettre de récupérer l’année de saisie du formulaire depuis la colonne Horodateur à l’aide d’une formule de calcul.
Voici la formule pour récupérer l’année :
Formule : E2 =YEAR(B2)
Explication : E2 = la formule récupère uniquement l’année depuis la date complète en B2.
Comme vous pouvez le voir, la formule étant basée sur l’horodateur, lorsqu’il n’y a pas d’informations, une année nommée 1899 s’affiche. Nous allons préciser que nous ne souhaitons pas avoir de contenu si la cellule horodateur est vide à l’aide d’une formule SI.
Formule : E2 =IF(B2= » »; » »;YEAR(B2))
Explication : E2 = Si B2 est vide alors on n’affiche rien, sinon la formule récupère uniquement l’année depuis la date complète en B2
Retrouvez le tableau complété à l’aide de ce lien : https://docs.google.com/spreadsheets/d/1sx8IvXSYX-Vk6-OSF7O0afmb0GZSiQIR8gG-PRBIU8M/edit?usp=sharing
Créer un tableau croisé dynamique pour exploiter la donnée Année.
A partir de la colonne Année générée depuis l’horodateur, nous allons créer un tableau croisé dynamique. Vous pouvez également consulter notre tutoriel sur la création de tableaux croisés dynamiques avec Google Drive pour obtenir plus d’informations sur la méthodologie.
Cliquez sur le menu Données puis sur Tableau croisé dynamique. Une nouvelle feuille de calcul nommée Tableau croisé dynamique 1 est ajoutée.
Cliquez sur Ajouter un champ à proximité de Lignes et sélectionnez la donnée Services concernées.
Puis effectuez la même chose pour ajouter la donnée Année pour les Colonnes.
Ajoutez enfin la donnée Services concernés pour la zone valeurs.
Par défaut, les valeurs sont présentées à l’aide la fonction SOMME (SUM). Pour faire ressortir le nombre d’intervention par service, nous allons synthétiser les données à l’aide de la fonction COUNTA.
Voici le résultat. Vos données sont à présent classées par Année. Vous pouvez bien entendu exploiter le tableau dynamique comme vous le souhaitez à l’aide des autres colonnes de la feuille de calcul. Ce tableau est automatiquement mis à jour lors de l’ajout de réponses au formulaire. Nous renommons la feuille Stat/Année pour faciliter la visibilité parmi l’ensemble des feuilles.
Dans des prochains tutoriels, nous reprendrons cet exemple pour exploiter nos données par mois, semaine, jour et amplitude horaire.