On peut vous aider ?
Cherchez des réponses ou parcourez les rubriques de notre documentation
Lecture des structures d’un classeur Excel
Présentation
Cet article tente de fournir au développeur GT les grandes lignes d’élaboration d’un classeur de conception de questionnaire GT à partir d’un classeur Excel brut soumis par le concepteur fonctionnel (personne ou organisme émettant la demande de construction d’un questionnaire GT).
Le classeur Excel brut devrait respecter les préconisations émises dans la fiche projet. Les textes des cellules, hauteurs de lignes, largeurs de colonnes, mises en forme, mises en forme conditionnelles et enfin les formules devraient être opérationnels dans Excel.
Il arrive fréquemment que, pour des processus de collecte déjà existants utilisant des classeurs Excel, le passage à un questionnaire GT normalise certains fonctionnements du classeur ou des flux de données : le classeur pourra être modifié par rapport au classeur initial et ce, dans une optique d’amélioration, de clarification ou de simplification de la saisie, des données et/ou des flux.
La démarche suivante consiste à parcourir les informations affichées dans le classeur Excel brut pour en dégager les structures sous-jacentes et préparer l’élaboration des tables correspondantes de la base de données client.
Cependant, avant même de parcourir les données du classeur, il est nécessaire de préciser les éléments interrogées dans les questionnaires envoyés : les entités.
Enfin, la lecture de cet article suppose d’avoir assimilé les notions de compartiments ainsi que les mécanismes de lancement et d’intégration.
Détermination des entités d’interrogation du questionnaire
Les entités d’interrogation sont les sujets d’interrogation du questionnaire.
Les entités permettent de distinguer globalement les questionnaires envoyés les uns des autres.
ATTENTION : généralement, les entités ne sont pas les correspondants ou même une donnée liée uniquement au correspondant.
Les questionnaires sont envoyés à des correspondants par mail. Cependant, on demande dans de nombreux cas au correspondant, non pas de fournir des informations sur lui-même, mais à propos d’une entité (un service, une filiale, une agence, etc…). Le correspondant fournit ces informations « au nom » de cette entité.
Lorsque l’on pose au correspondant des questions personnelles, en ce cas, l’entité et le correspondant sont confondus, mais cela n’est généralement pas le cas et il y a très souvent une entité sous-jacente permettant de distinguer les sujets d’interrogation.
Les entités sont représentées par les items/modalités d’un ou plusieurs axes : les axes de distribution.
La détermination correcte des entités pourra être effectuée en se posant cette question : Que se passe-t-il si mon correspondant change ?
Ou, plus précisément, si les données sont historisées, quelle est la bonne information à stocker pour minimiser l’impact d’un changement de correspondant sur l’historique des données ? Si seule l’adresse mail du correspondant est stockée dans l’historique, le changement de correspondant aura des impacts majeurs. Si l’identifiant de l’entité est stocké dans l’historique, le changement de correspondant dans la seule relation d’affectation pourra être effectué de manière transparente pour l’historique.
Le choix des entités détermine le travail de suivi de la campagne : les gestionnaires de suivi visualisent la dernière réponse par entité, valident une réponse par entité et relancent les entités.
L’intégration des données sera également facilitée par la détermination correcte des entités (par défaut, l’intégration concerne la dernière réponse par entité ou la réponse validée par entité).
Les réponses correctrices des correspondants (réponses successives apportant des corrections aux réponses précédentes) seront ainsi d’autant plus facilement gérées que les axes de distribution auront été correctement définis. Cette remarque vaut d’autant plus dans le cas des axes de validation supplémentaires.
Axes de validation supplémentaire
Dans certains cas, ce n’est pas l’expéditeur qui détermine précisément l’entité. Le correspondant peut répondre pour plusieurs entités parmi une liste ou le correspondant est totalement libre de fournir une information concernant tel ou tel sujet.
Les « sujets de réponse » (issues des axes de validation supplémentaire) sont différenciés les uns des autres avec le ou les axes de validation supplémentaire : ce sont des axes dont les valeurs sont généralement saisies directement ou indirectement (via des formules) par le correspondant et qui permettent de différencier les sujets de réponses les uns des autres.
Dans tous les cas, un ou des axes de distribution sont cependant nécessaires : un axe de distribution extrême pourrait ne contenir qu’une seule et même valeur pour tous les correspondants.
Bien qu’Il soit toujours préférable de parvenir à identifier un ou plusieurs axes permettant de différencier les réponses les unes des autres, ne serait-ce que pour autoriser les réponses correctrices de la part du correspondant, dans certains cas, il n’est cependant pas possible de trouver un « identifiant » pour distinguer les réponses les unes des autres. L’information de la date de transmission (par exemple, un contrôle contenant la date et heure courante) peut alors être utilisée en dernier ressort, cette information ne permettra cependant pas des réponses correctrices de la part du correspondant (chaque réponse du correspondant sera la seule et unique réponse pour une nouvelle entité).
Parcours du classeur Excel
L’opération consiste à balayer le classeur Excel à plusieurs reprises pour en dégager les éléments variables et les rattacher à des structures de données : les compartiments du questionnaire et les tables de la base client.
Pour un développeur GT expérimenté, les balayages seront effectués conjointement.
D’un point de vue pratique, il peut être utile de construire deux tableaux : un pour dessiner/esquisser le modèle de données et l’autre pour inscrire les modifications à mettre en œuvre dans le questionnaire ainsi que les questions complémentaires à poser au concepteur fonctionnel (lors du parcours du classeur Excel se posent généralement des questions sur le fonctionnement du questionnaire et la signification des données présentées ou leur liaison).
Pour certaines informations figurant dans le classeur et dont les composants sont évidents, ces derniers peuvent être créés dans le classeur Excel dès les premiers parcours.
Parcours 1 : Détermination des éléments variables du questionnaire
Dans la vie d’un questionnaire, ce dernier ne voit ses données modifiées qu’à trois moments :
– lors de la conception
– lors de la personnalisation (pré-remplissage du questionnaire sur le serveur avant l’envoi)
– lors de la saisie par le correspondant
Ces trois étapes doivent être constamment présentes à l’esprit lors de l’élaboration du questionnaire.
Il est généralement déconseillé d’initialiser des données dans le questionnaire lors de la conception (hors le cas spécifique de la transposition) : il est plus souple de définir les valeurs initiales lors de la personnalisation.
Ce premier parcours du classeur consiste à repérer les composants/informations affichées et saisies, ainsi que toutes les informations variables du questionnaire suivant les modalités suivantes :
- variables d’une entité à l’autre
- variables d’un correspondant à l’autre
- variables d’une session d’interrogation/campagne à l’autre
- communs à des questionnaires différents et que l’on veut pouvoir réutiliser d’un questionnaire à l’autre
Parmi les éléments potentiellement communs à des questionnaires différents figurent les entités et les correspondants si ceux-ci servent de base à l’envoi de plusieurs questionnaires.
Les éléments suivants ne devraient pas être omis :
– éléments variables directs : libellés, nombres affichés, pièces jointes
– dépendances : contenu de liste déroulante, nombre intervenant dans le calcul d’une formule, listes de référence utilisées pour afficher certains éléments à choisir, listes ou nombres utilisés dans le cadre de contraintes de validation/vérification de cohérence des données saisies
Parcours 2 : Esquisse du modèle de données
Cette étape consiste essentiellement à déterminer les axes-clés de chaque niveau d’information du questionnaire.
Une esquisse du modèle logique de données sera généralement élaborée conjointement à la détermination des clés pour les données : on tentera de rattacher chacune des informations affichées ou saisies dans le questionnaire à l’une des clés déterminées dans cette étape.
Les premiers champs clé pour le questionnaire sont les axes de distribution définissant les entités.
Les axes de validation supplémentaire s’ils existent sont évidemment à inclure pour les données intégrées. Les axes de distribution resteront champs-clés pour les informations à diffuser.
Les données variables identifiées par l’étape précédente doivent maintenant être rattachées aux différentes clés du questionnaire.
Pour chaque donnée affichée doit se poser la question de sa signification afin de pouvoir la rattacher à une clé déjà identifiée du questionnaire ou envisager l’élaboration d’une nouvelle clé.
Lorsqu’une dimension implicite ou explicite est ajoutée dans le questionnaire (démultiplication d’un onglet, liste de lignes ou tableau croisé), de nouvelles clés doivent être identifiées.
Les autres informations variables sont alors rattachées à l’une des clés identifiées.
Ces clés peuvent ou non inclure les clés du questionnaire : par exemple, dans le cas d’une diffusion par filiale, une liste de prix qui serait la même pour toutes les filiales ne comprendrait pas comme clé les axes de distribution (l’axe filiale dans notre cas qui est la clé du questionnaire).
Néanmoins, si les axes-clés du questionnaire ne sont pas forcément inclues, les clés des compartiments supérieurs devraient l’être : si une information se trouve dans un onglet démultiplié, sa clé devrait être rattaché à l’axe de multi-onglet (même chose pour le motif). L’information peut cependant être déposée à la racine du questionnaire et rappelée (par une formule) dans le multi-onglet.
Le modèle de données se dessine donc lors de ce parcours d’élaboration des clés.
Les compartiments GT sous la forme de multi-onglets, motifs et transposition sont également esquissés.
Parcours 3 : choix des structures GT (multi-onglet, motif, transposition)
Généralement, les compartiments seront imposés par la forme du questionnaire et l’agencement des informations demandées. Dans le questionnaire vont se détacher des zones homogènes (onglets identiques, listes et tableaux croisés à associer aux multi-onglets, listes et transpositions).
Cependant, dans certains cas, il peut être intéressant de passer d’un type de compartiment à un autre. Par exemple, d’un multi-onglet à un motif ou à une transposition si l’on souhaite que les correspondants puissent ajouter des modalités, ou pour d’autres raisons, (concision du classeur, vérification de contraintes globales, etc…). Dans d’autres cas, le fait de passer à un multi-onglet peut aérer le questionnaire pour en faciliter la saisie.
Enfin, lorsque des onglets sont quasiment identiques, il peut être intéressant, afin d’utiliser un multi-onglet, de rendre les onglets initiaux complètement identiques quitte à faire quelques concessions sur la mise en forme ou le fonctionnement.
Le tableau suivant essaie de mettre en évidence les caractéristiques comparées de chacun des conteneurs/transformations.
| Multi-onglet | Motif | Transposition | |
|---|---|---|---|
| Description sommaire | Onglets | Lignes | Tableau croisé Croisement de dimensions en lignes en colonnes, voire en onglets |
| Nombre de dimensions | 1 mais concaténation possible | 1 Les clés sont utilisables et/ou des contraintes peuvent être définies | Plusieurs |
| Eléments démultipliés dans le qstx | 1 ou plusieurs onglets contigus Démultiplication suivant une seule direction pour le multi-onglet | Plage rectangulaire de cellules contiguës Démultiplication suivant une seule direction pour le motif | Cellules contiguës ou non dans des onglets différents ou non. Cellules définies à la conception du qstx |
| Modalités des dimensions définies lors de | la personnalisation mais si utilisation d’items anonymes => attention à la vérification des contraintes de clefs | la personnalisation ou la saisie.attention au cas sans modalités (motif vide) | la conception mais si utilisation d’items anonymes => attention à la vérification des contraintes de clefs |
| Ajout/suppression d’items | Non | Oui La possibilité d’ajouter ou de supprimer des lignes dans un motif est une option globale au motif. | Non |
| Comportements/dates relatives | Aucune gestion directe | Aucune gestion directe | Anonymisation des dates |
| Comportements différenciés directement suivant les items (mises en forme, contraintes, formules) | Non Les mises en forme conditionnelles, gtverrou (anc. verrouGT), contraintes globales de validation et formules sont possibles mais doivent gérer la bascule d'items | Non Les mises en forme conditionnelles, gtverrou (anc. verrouGT), contraintes globales de validation et formules sont possibles mais doivent gérer la bascule d'items | Oui Les mises en forme conditionnelles, gtverrou (anc. verrouGT), contraintes globales de validation sont possibles |
| Nb minimum de modalités | 1 | 1 au moins une ligne existe et sera renvoyée, même si non remplie | 1 |
| Nb max de modalités définies lors de la | personnalisation | Personnalisation ou saisie | conception |
| Clé sous-jacente | Axe de multi-onglet | Non définie, des contraintes peuvent être placées dans le questionnaire pour vérifier les clés | Axes transposés hors axe valeur i.e. tous les axes mentionnés au moins une fois en-dehors des axes valeurs |
| Pointage à l’intérieur (item particulier de la démultiplication) | Oui possible via pointage de chaque élément sur données chiffrées ou non chiffrées mais avec contraintes de performance (cf Synthèse de données d'un multi-onglet) | Oui, indirectement via des index/equiv ou des recherchev [3] | Oui, directement sur la cellule, recherches indirectes également possibles |
| Agrégations possibles | Somme mais autres possibles via pointage de chaque élément sur données chiffrées ou non chiffrées mais avec contraintes de performance | Toutes formules d’agrégation Excel incluant éventuellement decaler/indirect sauf formules matricielles | Toutes formules d’agrégation Excel incluant éventuellement decaler/indirect sauf formules matricielles |
| Agrégations conditionnelles | Oui, possible via pointage de chaque élément sur données chiffrées ou non chiffrées mais avec contraintes de performance | Oui via les fonctions somme.si, nb.si, somme.si.ens | Directement ou via les fonctions somme.si, nb.si, somme.si.ens |
| Matrice de saisie Excel | Non | OuiPrévoir un grand nombre de lignes pour simplifier la manipulation du classeur Excel par les correspondants => ceci implique une gestion des lignes vides dans le motif | Oui |
| Intégration préférée | Update sur champs-clés ou pivot | Pivot si le correspondant peut ajouter/supprimer des lignes ou modifier des clés Update sur champs-clés sinon | Update sur champs clés si axes hors valeur en nombre restreint Pivot sur axes de niveau supérieur + axes |
| Lancement de campagne | Clé de l’axe de démultiplication | Attention à l’association Motif+Transpo : la clé peut-être absorbée par la transpo | Les modalités autres que celles définies à la conception sont ignorées [4] |
| Vérification des clés pour l’intégration | Cas général : OK Les clés ne sont pas modifiables par le correspondant Si anonymisation manuelle d’items, uniquement dans certains cas et difficile | A construire systématiquement pour assurer la cohérence des données si le correspondant peut ajouter ou supprimer des lignes ou modifier des clés existantes | Cas général : OK Les clés ne sont pas modifiables par le correspondant) Si anonymisation manuelle d’items, vérification par formules Excel |
| Vérification de contraintes | Onglet par onglet, vérif des contraintes sur l’ensemble possible mais uniquement sur données chiffrées | Vérification sur le range de base, les vérifications globales sont possibles en utilisant des fonctions d’agrégation | Toutes les vérifications avec des formules Excel sont possibles |
| Import Excel-Answer | Import Excel en "point à point" en mettant à jour l'onglet correspondant | Import Excel global au motif basé sur le numéro de ligne dans Excel et le qstx | Import Excel basé sur les noms Excel correspondant aux composants |
| pour composants valides (en-têtes alphanumériques) cf Import Answer Excel |
Lancement de la campagne
Parmi les points importants lors du lancement figurent le choix des axes de distribution et l’articulation entre tables associés à des compartiments différents.
Comme il a été décrit dans ce document, chaque compartiment a une clé explicite (pour le multi-onglet ou la transposition) ou implicite (numéro de ligne dans le cas du motif). Les tables associées aux différents compartiments devraient avoir la même clé.
Les articles suivants décrivent les options de l’action de lancement et la mécanique d’assemblage des données :
Intégration des données
Les articles suivants décrivent les options de l’action d’intégration et les mécanismes de l’intégration de données :
Intégrer les résultats de formules
Les résultats de formules sont extraits du questionnaire en utilisant la fonction gtcontrole qui permet de créer un composant contrôle (composant non éditable par le correspondant) et de l’initialiser par une formule (deuxième argument de la fonction gtcontrole).
L’intégration des résultats de formules est nécessaire dans les cas où cette information ne fait pas redondance avec d’autres données du questionnaire.
Par exemple, le code correspondant à un libellé d’item choisi dans une liste déroulante devrait être produit dans le questionnaire (en utilisant
Dans la plupart des cas, intégrer une formule comme, par exemple, une somme de nombres alors que chacun des nombres est intégré par ailleurs est plus problématique. En effet, au-delà de la question de la redondance d’informations, cela signifie que les données ne pourront pas être facilement corrigées directement dans les tables.
Cependant, dans les cas où les formules exprimées dans Excel demanderaient un travail non négligeable de phrases SQL dans la base de données, il peut malgré tout être intéressant d’intégrer ces formules pour faciliter le travail de restitution (notamment). Des cas typiques se présentent pour des formules dont les équivalents seraient des opérations non identiques entre indicateurs ou entre lignes de la base ou des calculs incrémentiels (le SQL est ensembliste).
Si l’intégration de certaines formules est effectuée, il faudra garder à l’esprit que les données ainsi intégrées garderont une certaine dépendance vis-à-vis du questionnaire.
Intégrations répétées
Un point à prendre en considération lors de la conception du projet est la possibilité laissée ou non au gestionnaire à exécuter l’action d’intégration de manière répétée : on ne peut généralement pas l’interdire et il faudra donc en évaluer les conséquences.
Les intégrations répétées successives pour une même campagne/session d’interrogation demandent à finement ajuster les paramètres d’intégration.
Il est déterminant de savoir si, lorsqu’il y a historisation, les données intégrées modifient uniquement les données pour une seule date ou un ensemble de dates : dans le cas d’une seule date, on pourra toujours, en dernier ressort, créer une procédure stockée pour effacer les données « courantes » de l’historique. Si plusieurs dates sont concernées par l’action d’intégration, il faudra utiliser des pivots ou marquer des enregistrements comme étant à supprimer pour les supprimer ultérieurement avec une procédure stockée.
Un cas d’intérêt se présente lorsque l’on autorise une réponse avec une liste vide pour un motif (en fait une seule ligne existe dans la réponse, mais cette ligne est vierge). Comme l’intégration ne gère pour l’instant pas les suppressions d’enregistrements existants, il faudra traiter ce cas en marquant des éléments à supprimer ou en adjoignant les données de ce motif aux données d’un autre motif qui lui ne peut être vide (dans ce dernier cas, l’import par pivot sera suffisant).