Les tableaux GETPIVOTDATA sont une fonctionnalité puissante de Google Sheets qui permet d’extraire plusieurs valeurs agrégées et de créer un tableau ou une plage de données. Cette technique est précieuse car ces données peuvent être utilisées pour générer plusieurs graphiques à partir d’une seule table pivotante.
Bien sûr, vous pouvez utiliser les fonctions FILTER ou QUERY pour obtenir la valeur souhaitée à partir d’une table pivotante. Cependant, ces méthodes présentent deux inconvénients :
-
Déplacement de la table pivotante : Lorsque vous déplacez la table pivotante, il se peut que vous deviez modifier les plages utilisées dans la formule.
-
Modification des données source : Lorsque vous modifiez les données source, la table pivotante peut s’agrandir ou se réduire. Ainsi, les colonnes ou les lignes utilisées pour le filtrage doivent être ajustées. De plus, ces fonctions nécessitent une expertise dans la création de filtres basés sur des critères.
La fonction GETPIVOTDATA est conçue pour renvoyer une seule valeur agrégée, et l’utilisation de la fonction ARRAYFORMULA avec celle-ci ne serait pas utile. Pour obtenir plusieurs valeurs avec GETPIVOTDATA, il faut utiliser l’une des fonctions d’aide LAMBDA avec elle.
Dans cet article, nous explorerons plusieurs exemples d’utilisation de la fonction d’aide MAP Lambda avec GETPIVOTDATA dans Google Sheets pour renvoyer un tableau ou une plage de données.
Données fictives pour tester les formules de tableau GETPIVOTDATA
Les données d’exemple se composent de trois colonnes : Joueur, Jeu et Score. Cet ensemble de données est adapté pour tester plusieurs regroupements par ligne et par colonne.
Il y a deux joueurs (John et Emily) et deux jeux (Formula Frenzy et Data Daze). Les deux premières colonnes du tableau contiennent ces données. Chaque joueur a participé deux fois à chaque jeu, et vous pouvez trouver leurs scores dans la troisième colonne.
Nous allons créer une table pivotante à partir de cet ensemble de données pour tester les formules de tableau GETPIVOTDATA dans Google Sheets. Pour accéder aux données d’exemple et à tous les exemples de formules, copiez la feuille d’exemple en cliquant sur le bouton ci-dessous.
Table pivotante avec plusieurs regroupements par ligne et formules de tableau GETPIVOTDATA
Pour tester la fonction GETPIVOTDATA avec plusieurs valeurs, la première étape consiste à créer un rapport de table pivotante. Suivez les étapes ci-dessous pour créer une table pivotante à partir des données fournies avec un regroupement par ligne.
Création d’une table pivotante
- Supposons que la table se trouve dans la plage de cellules A1:C9 de la « Feuille1 ».
- Déplacez-vous dans la cellule A11.
- Cliquez sur Insertion > Tableau croisé dynamique.
- Dans le champ « Plage de données », saisissez la plage A1:C9.
- Sélectionnez « Feuille existante » car nous insérons la table pivotante dans la feuille existante qui contient les données source.
- Saisissez A11 dans le champ prévu à cet effet.
- Cliquez sur « Créer ».
Une fois ces étapes terminées, Sheets insérera la mise en page de la table pivotante et ouvrira le volet de l’éditeur de table pivotante dans la barre latérale.
- Faites glisser et déposez les champs « Joueur » puis « Jeu » sous « Lignes ».
- Faites glisser et déposez le champ « Score » sous « Valeurs » deux fois. Définissez « Résumer par » sur SOMME et MOYENNE.
Avec ces étapes, la table pivotante est maintenant créée. Nous allons maintenant explorer la formule de tableau GETPIVOTDATA pour extraire plusieurs valeurs de ce rapport de table pivotante.
Utilisation de GETPIVOTDATA avec MAP et LAMBDA dans Google Sheets
Récupérer plusieurs points de données à partir d’une table pivotante :
La syntaxe de GETPIVOTDATA est la suivante :
GETPIVOTDATA(nom_valeur, n'importe_quelle_cellule_table_pivot, [colonne_origine, ...], [élément_table_pivot, ...])
Points clés :
- La fonction GETPIVOTDATA extrait des données spécifiques d’une table pivotante.
- Elle ne peut pas renvoyer directement plusieurs valeurs.
- MAP et LAMBDA étendent ses capacités pour récupérer plusieurs résultats.
Exemple de scénario #1 (Regroupement par ligne)
(Référez-vous à la « Feuille1 » dans la feuille d’exemple ci-dessus)
Récupérer les scores moyens d’Emily et de John dans le jeu « Formula Frenzy ».
Pour cela, commencez par créer un tableau comme illustré ci-dessous dans la plage E1:G3 :
| Joueur | Jeu | MOYENNE du Score |
|---|---|---|
| Emily | Formula Frenzy | |
| John | Formula Frenzy |
Notez que la fonction GETPIVOTDATA ne prend pas en charge la spécification de plusieurs éléments pivot directement, comme illustré ci-dessous :
=GETPIVOTDATA(G1, A11, E1, E2:E3, F1, F2:F3) // formule incorrecte !
Étant donné que la spécification directe d’un tableau n’est pas prise en charge dans GETPIVOTDATA, nous utilisons la fonction MAP pour itérer sur chaque valeur du tableau, ce qui permet de récupérer plusieurs résultats.
Pour obtenir le score moyen pour les deux joueurs, saisissez la formule de tableau suivante dans la cellule G2, qui se répandra jusqu’à G3 :
=MAP(E2:E3, F2:F3, LAMBDA(a, b, GETPIVOTDATA(G1, A11, E1, a, F1, b)))
Explication de la formule :
-
Fonction MAP :
MAP(tableau1, [tableau2, ...], LAMBDA([nom, ...], expression_formule))La fonction MAP applique une fonction LAMBDA spécifiée à chaque paire correspondante d’éléments de deux tableaux et renvoie un tableau de résultats. Dans ce cas :
- tableau1 : E2:E3 (noms des joueurs – Emily et John)
- tableau2 : F2:F3 (noms des jeux – Formula Frenzy)
-
Fonction LAMBDA :
=LAMBDA(a, b, GETPIVOTDATA(G1, A11, E1, a, F1, b))La fonction LAMBDA définit une fonction anonyme qui prend deux arguments (a et b) et renvoie le résultat de l’expression qui l’entoure, qui est une fonction GETPIVOTDATA dans ce cas.
-
Arguments de GETPIVOTDATA :
- nom_valeur : G1 (« MOYENNE du Score »)
- n’importe_quelle_cellule_table_pivot : A11 (référence à une cellule à l’intérieur de la table pivotante)
- colonne_origine : E1 (« Joueur »)
- élément_table_pivot : a (chaque joueur du tableau résultat)
- colonne_origine : F1 (« Jeu »)
- élément_table_pivot : b (chaque jeu du tableau résultat)
Exemple de scénario #2 (Regroupement par ligne)
(Référez-vous à la « Feuille2 » dans la feuille d’exemple ci-dessus)
Pour obtenir à la fois « MOYENNE du Score » et « SUM du Score » dans la formule de tableau, suivez ces étapes :
- Saisissez « SUM du Score » dans la cellule H1.
- Utilisez cette formule MAP imbriquée dans la cellule G2 :
=MAP(G1:H1, LAMBDA(x, MAP(E2:E3, F2:F3, LAMBDA(a, b, GETPIVOTDATA(x, A11, "Joueur", a, "Jeu", b)))))
Explication :
-
MAP externe : Itère sur chaque valeur du tableau valeur (G1:H1), contenant « MOYENNE du Score » et « SUM du Score ».
-
Applique la MAP interne pour chaque valeur_nom.
-
MAP interne : Itère sur chaque combinaison d’éléments pivot, où les noms des joueurs proviennent de E2:E3 et les noms des jeux de F2:F3.
-
Récupère la valeur spécifiée (MOYENNE ou SOMME) en utilisant GETPIVOTDATA pour chaque combinaison joueur-jeu.
Formule (dans la cellule G2) :
=MAP(G1:H1, LAMBDA(x, MAP(E2:E3, F2:F3, LAMBDA(a, b, GETPIVOTDATA(x, A11, "Joueur", a, "Jeu", b)))))
Table pivotante avec regroupements par ligne et par colonne et formules de tableau GETPIVOTDATA
Dans ce cas, la mise en page de la table pivotante a été modifiée. Le champ « Joueur » est regroupé et le champ « Jeu » est pivoté.
Exemple de scénario #1 (Regroupement par ligne et par colonne)
(Référez-vous à la « Feuille3 » dans la feuille d’exemple ci-dessus)
Récupérez les scores moyens et les scores totaux pour chaque combinaison joueur-jeu.
Pivot Table Structure :
- Faites glisser le champ « Jeu » sous « Colonnes » et le champ « Joueur » sous « Lignes » dans l’éditeur de table pivotante.
- Placez le champ « Score » dans « Valeurs » et résumez avec la fonction MOYENNE.
Création de la table de données :
- Créez une table dans la plage E1:H3 comme suit :
| Data Daze | Formula Frenzy | |
|---|---|---|
| Emily | ||
| John |
Utilisez la formule GETPIVOTDATA suivante dans la cellule F2 pour récupérer plusieurs valeurs agrégées de la table pivotante :
=MAP(F1:G1, LAMBDA(c, MAP(E2:E3, LAMBDA(r, GETPIVOTDATA(A11, A11, "Joueur", r, "Jeu", c)))))
Explication de la formule :
- MAP externe : Itère sur les colonnes F1:G1 (noms des jeux).
- MAP interne : Itère sur les lignes E2:E3 (noms des joueurs).
- GETPIVOTDATA : Récupère les données de la table pivotante.
- x (nom_valeur) : Valeur actuelle de la colonne externe de la MAP (MOYENNE ou SOMME).
- A11 (n’importe quelle_cellule_table_pivot) : Référence une cellule à l’intérieur de la table pivotante (probablement en haut à gauche).
- « Joueur » (colonne_origine) : Champ à partir duquel récupérer les données.
- a (élément_table_pivot) : Valeur de ligne actuelle de la MAP interne (nom du joueur).
- « Jeu » (colonne_origine) : Champ à filtrer.
- c (élément_table_pivot) : Valeur de colonne actuelle de la MAP externe (nom du jeu).
Exemple de scénario #2 (Regroupement par ligne et par colonne)
(Référez-vous à la « Feuille4 » dans la feuille d’exemple ci-dessus)
Cette fois, il y a un changement supplémentaire dans la table pivotante. Dans l’exemple ci-dessus, le champ « Joueur » est regroupé et le champ « Jeu » est pivoté avec l’agrégation définie sur MOYENNE.
Maintenant, pour ce scénario, nous utiliserons la même table pivotante mais avec une agrégation supplémentaire, qui est la SOMME.
En résumé, ajoutez « Joueur » sous « Lignes », « Jeu » sous « Colonnes » et placez « Score » deux fois sous « Valeurs ».
Ensuite, créez le tableau suivant dans la cellule E1:H3 pour utiliser dans la formule de tableau GETPIVOTDATA.
| MOYENNE du Score | SOMME du Score | ||
|---|---|---|---|
| Emily | Formula Frenzy | ||
| John | Data Daze |
Formule (dans la cellule G2) :
=MAP(G1:H1, LAMBDA(x, MAP(E2:E3, F2:F3, LAMBDA(a, b, GETPIVOTDATA(x, A11, "Joueur", a, "Jeu", b)))))
Explication de la formule :
- MAP externe : Itère sur les colonnes G1:H1 (représentant MOYENNE et SOMME).
- MAP interne : Itère sur les lignes E2:E3 (noms des joueurs) et les colonnes F2:F3 (noms des jeux).
- GETPIVOTDATA : Récupère les données de la table pivotante.
- x (nom_valeur) : Valeur actuelle de la colonne externe de la MAP (MOYENNE ou SOMME).
- A11 (n’importe quelle_cellule_table_pivot) : Référence une cellule à l’intérieur de la table pivotante (probablement en haut à gauche).
- « Joueur » (colonne_origine) : Champ à partir duquel récupérer les données.
- a (élément_table_pivot) : Valeur de ligne actuelle de la MAP interne (nom du joueur).
- « Jeu » (colonne_origine) : Champ à filtrer.
- b (élément_table_pivot) : Valeur de colonne actuelle de la MAP interne (nom du jeu).
Utilisation de GETPIVOTDATA Array et de graphiques dans Google Sheets
L’utilisation de la fonction GETPIVOTDATA pour renvoyer un tableau de valeurs dans Google Sheets présente des avantages spécifiques.
Lors de la création de graphiques à partir d’une table pivotante, vous pouvez désirer exclure les lignes et les colonnes TOTAL/GRAND TOTAL, une étape que vous préféreriez éviter.
Dans de tels cas, la combinaison de GETPIVOTDATA et MAP peut être utilisée pour extraire les valeurs requises de la table pivotante et faciliter la création de graphiques.
Par exemple, référez-vous à l’exemple de scénario #1 dans « Table pivotante avec regroupements par ligne et par colonne et formules de tableau GETPIVOTDATA ». Vous pouvez utiliser le tableau obtenu pour créer un graphique en colonnes ou un graphique en barres.
Conseils supplémentaires
Lors de la génération du tableau résultant, vous pouvez utiliser la fonction UNIQUE dans les données source pour obtenir dynamiquement les valeurs uniques de la colonne_originale.
Pour les noms des joueurs dans les exemples donnés, utilisez la formule suivante :
=UNIQUE(A2:A9)
De même, pour obtenir les noms uniques des jeux, utilisez la formule :
=UNIQUE(B2:B9)
Cette approche ajoute de la souplesse à la formule de tableau GETPIVOTDATA.
Explorez des fonctions supplémentaires comme VSTACK, HSTACK, TOCOL et TOROW, car elles peuvent améliorer la flexibilité lors de l’utilisation du tableau résultant.
Ressources :
