On peut vous aider ?
Cherchez des réponses ou parcourez les rubriques de notre documentation
Synthèse de données d’un multi-onglet
Généralités
Pour les motifs et les multi-onglets, il n’est pas possible de construire, en dehors du motif ou du multi-onglet, une formule pointant directement sur une cellule du motif ou du multi-onglet.
Dans le cas d’un motif, des fonctions d’accès à des tableaux (INDEX, EQUIV, RECHERCHEV, SOMME, SOMME.SI etc…) peuvent être utilisées.
Les fonctions GTMS.xxxx n’ont généralement pas d’équivalent dans Excel pour l’accès à des plages situés « transversalement » sur des onglets (les références 3D existent dans Excel mais elles ne peuvent être utilisées que par un petit nombre de fonctions).
Cet article donne des exemples d’application pour les deux principales techniques d’extraction de données hors multi-onglet :
- utilisation du GTMS.fonction_agreg(expression) qui permet de sommer les valeurs d’une même adresse de cellule sur les onglets du multi-onglet
- utilisation du GTMOGETSHEETNAME avec un INDIRECT permettant de désigner directement l’onglet et l’adresse de la cellule dont on veut récupérer la valeur.
A noter que ces deux fonctions ne peuvent être utilisées que dans des cellules (pas dans des mises en forme conditionnelle, verrous, …).
Table des matières des onglets
Un exemple simple à réaliser est une table des matières listant les onglets avec des hyperliens pointant vers chacun d’entre eux.
Le principe, exploité dans la plupart des exemples de cet article, est d’utiliser un motif listant les items du multi-onglet et de construire grâce aux fonctions GT des hyperliens vers chacun des onglets.
Un exemple est inclus dans le fichier joint à ce tutoriel.
Agrégations sur les onglets : GTMS.
Les fonctions GTMS.xxxx permettent d’agréger des données dans les onglets d’un multi-onglet.
Fonctions GTMS. autorisées :
=GTMS.SOMME(expression) =GTMS.NB(expression) =GTMS.NBVAL(expression) =GTMS.MAX(expression) =GTMS.MIN(expression) =GTMS.MOYENNE(expression) =GTMS.CONCATENER(expression)
Utilisation
L’expression utilisée dans le GTMS doit contenir au moins un range vers un multi-onglet et doit être de l’un des types suivants :
- GTMS.xxx(Range), comme dans
=GTMS.SOMME(OngletMO!$B$3) - GTMS.xxx(fonction(formule)) comme dans
=GTMS.SOMME(SOMME(OngletMO!$A$1 ; OngletMO!$A$1:$B$3 ; HorsOngletMO!C12))
La formule GTMS.xxx() sera adaptée lorsque l’utilisateur Answer ajoute des onglets dans le multi-onglets.
Les plages pointées par expression ne peuvent faire référence à plusieurs groupes de multi-onglets.
Les fonctions GTMS ne sont autorisées que dans les formules inscrites dans les cellules (pas dans les formules de mise en forme conditionnelle, ni les verrous, ni les contraintes de validation, etc…).
Toutes les références à d’autres onglets que l’onglet courant doivent être inscrites en absolu ($A$1).
Mécanisme et exemples
La formule du type
=GTMS.SOMME( OngletMO!$B$3 )
sera remplacée dynamiquement par une expression du type
=SOMME(OngletMO_1!$B$3;OngletMO_2!$B$3;OngletMO_3!$B$3)
La formule du type
=GTMS.SOMME( SI( OngletMO!$B$3>0 ; 0 ; 1) )
sera remplacée dynamiquement par une expression du type
=SOMME(SI(OngletMO_1!$B$3>0;0;1) ; SI(OngletMO_2!$B$3>0;0;1) ; SI(OngletMO_3!$B$3>0;0;1) )
La formule du type
=GTMS.SOMME(SOMME.SI.ENS( OngletMO!$B$3:$B$12 ; OngletMO!$A$3:$A$12 ; HorsOngletMO!C15 ))
sera remplacée dynamiquement par une expression du type
=SOMME( SOMME.SI.ENS(OngletMO_1!$B$3:$B$12;OngletMO_1!$A$3:$A$12;HorsOngletMO!C15) ;
SOMME.SI.ENS(OngletMO_2!$B$3:$B$12;OngletMO_2!$A$3:$A$12;HorsOngletMO!C15);
SOMME.SI.ENS(OngletMO_3!$B$3:$B$12;OngletMO_3!$A$3:$A$12;HorsOngletMO!C15))
Ce type de formule permet par exemple de faire des agrégations sur les motifs se trouvant dans des multi-onglets.
La formule du type =GTMS.SOMME(OngletMO!$B$3 + OngletMO!$A$3) n’est pas autorisée.
Un exemple est inclus dans le fichier joint à ce tutoriel.
Limitations
Si un export Excel du questionnaire est réalisé, Excel ne supportera pas un trop grand nombre d’onglets utilisés dans les formules GTMS.
Les limitations suivantes sont en cause :
– pas plus de 255 arguments dans les formules Excel : les GTMS génèreront une erreur dans l’export Excel si plus de 255 onglets.
– les formules ne peuvent dépasser 8000 caractères dans Excel : la formule générée par la fonction GTMS provoquera une erreur dans l’export Excel si elle dépasse 8000 caractères (en comptant la taille des noms d’onglets utilisés pour référencer les range, etc…)
Enfin, à l’ouverture dans Answer (et à l’ajout/suppression d’onglets) si le multi-onglet est dynamique, si un grand nombre de formules GTMS est utilisé sur un groupe de multi-onglet avec un grand nombre d’onglets, les performances d’Answer pourront être très fortement pénalisées.
Par exemple, utiliser plus de 9000 formules GTMS pointant vers un onglet de plus de 30 onglets pénalisera très fortement les performances à l’ouverture dans Answer.
Un compromis devra être trouvé entre l’utilisation de fonctions d’agrégation globale comme les GTMS et l’utilisation de fonctions INDIRECT associées aux GTMOGETSHEETNAME ou GTMOAXISVAL.
Extraction de valeurs isolées
Extraction de valeurs isolées avec GTMS.SOMME ou GTMS.CONCATENER
Consiste à sommer (respectivement concaténer) une valeur nulle (resp. « ») sur tous les onglets sauf sur l’onglet dont on veut récupérer la valeur
En considérant un cas où
- la cellule OngletMO!A2 du multi-onglet contient l’item de multionglet
- la cellule OngletMO!B3 du multi-onglet contient la valeur à agréger
- la cellule HORS_MO!A3 contient l’item de multi-onglet pour lequel on veut extraire les valeurs
On pourra utiliser
- pour une valeur numérique ou une date, la formule
=GTMS.SOMME( SI(OngletMO!$B$3 = HORS_MO!A3 ; OngletMO!$B$3 ; 0)) - pour une chaîne, la formule
=GTMS.CONCATENER( SI(OngletMO!$B$3 = HORS_MO!A3 ; OngletMO!$B$3 ; « »))
L’avantage de cette solution est de ne pas utiliser de fonctions INDIRECT qui peuvent avoir un impact négatif important sur les performances.
Extraction de valeurs isolées avec INDIRECT et GTMOGETSHEETNAME
La fonction INDIRECT utilise, comme dans Excel, le premier argument, une expression chaîne, pour renvoyer la référence correspondant à cette chaîne (dans la syntaxe A1 d’Excel).
exemple =INDIRECT(« A »& »2 ») renvoie le contenu de la cellule A2.
la fonction GTMOGETSHEETNAME, spécifique à Answer, renvoie le nom de l’onglet correspondant à un item de multi-onglet pour un axe de multi-onglet.
En combinant ces deux fonctions, une formule peut accéder à une cellule d’un onglet du multi-onglet.
exemple: =INDIRECT(GTMOGETSHEETNAME(« Item de multi-onglet »)& »!A1″). Pour rappel, il est préférable d’isoler la fonction GTMOGETSHEETNAME(« Item de multi-onglet ») dans une cellule sans aucune autre fonction dans la formule.
La fonction INDIRECT est une fonction volatile : elle est recalculée systématiquement pour toute modification de valeur dans une cellule quelconque du classeur ou tout ajout de ligne/colonne).
La fonction INDIRECT (dans Excel comme dans un questionnaire GT) ne devrait donc être utilisée que lorsque aucun problème de performance (lié à des volumétries de données ou des complexités de calcul) puisse advenir dans le questionnaire.
A noter que l’utilisation de INDIRECT si réalisée sur une référence texte brut (« en dur ») peut poser des problèmes de maintenance du classeur de conception du questionnaire : l’ajout de lignes ou de colonnes avant la cellule adressée ne sera pas pris en compte par la référence de texte brut.
Un exemple est inclus dans le fichier joint à ce tutoriel.
Extraire la liste des items d’un multi-onglet dynamique
Cette technique est exposée dans l’onglet ‘Synthèse GTMS 1’ du classeur GTTut-SynthGTMS-Qst.xlsx.zip (colonnes E et suivantes).
Elle consiste à :
– dans le multi-onglet, récupérer l’index (ou numéro d’item) de l’item de multi-onglet courant en utilisant la fonction GTMOITEMNUM(). Dans l’exemple en F2
– hors du multi-onglet, construire une liste d’index (en s’arrêtant à un nombre maximum) 1, 2, 3
– hors du multi-onglet, concaténer l’ensemble des items de multi-onglet (dans l’exemple, en C2 du multi-onglet) en utilisant la fonction GTMS.CONCATENER, mais en ne renvoyant l’item à concaténer que lorsque la cellule contenant GTMOITEMNUM() dans l’onglet du multi-onglet est égale à l’index courant (hors du multi-onglet)
Le nombre maximum d’items devra être prévu à l’avance (10 items maximum dans l’exemple).
Synthèse de motifs dans des multi-onglets
Les fonctions GTMS.xxx permettent très simplement de réaliser ce type d’opérations. L’expression contenue dans GTMS.xxx(expression) peut pointer directement vers la plage complète d’un motif (+ une ligne comme les opérations habituelles sur les motifs)
