Il est presque impossible d’extraire les lignes de total et de grand total d’un rapport de tableau croisé dynamique à l’aide de la fonction GETPIVOTDATA dans Google Sheets. Mais il existe une formule alternative.
Bien sûr, pour extraire dynamiquement les valeurs agrégées d’un tableau croisé dynamique, y compris à partir d’une ligne de total, vous pouvez utiliser la fonction GETPIVOTDATA.
Mais avec cette fonction, vous ne pouvez pas extraire une ligne de total complète sans la mettre en imbrication.
Par exemple, voici la formule GETPIVOTDATA imbriquée ci-dessous dans la cellule E12 et le rapport de tableau croisé dynamique dans A1:D12.
={"Grand Total",GETPIVOTDATA("Sum of Total Sales",A1),GETPIVOTDATA("Average of Total Sales",A1)}
La formule extrait avec succès la ligne de grand total.
Mais qu’en est-il de l’inclusion des lignes de sous-total ? Je veux dire les lignes « Jeanne Total » et « Matt Total ».
La formule GETPIVOTDATA imbriquée deviendra très compliquée. Veuillez voir l’exemple ci-dessous.
={"Jeanne Total",GETPIVOTDATA("Sum of Total Sales",A1,"Name","Jeanne"),GETPIVOTDATA("Average of Total Sales",A1,"Name","Jeanne");{"Matt Total",GETPIVOTDATA("Sum of Total Sales",A1,"Name","Matt"),GETPIVOTDATA("Average of Total Sales",A1,"Name","Matt")};{"Grand Total",GETPIVOTDATA("Sum of Total Sales",A1),GETPIVOTDATA("Average of Total Sales",A1)}}
Vous pouvez ignorer cette formule. Vous ne pouvez pas l’appeler une formule dynamique car, pour chaque ligne de total, vous devez mettre en imbrication la formule.
Cela signifie que vous ne pouvez pas utiliser la fonction GETPIVOTDATA de manière dynamique pour extraire les totaux individuels et les lignes de grand total d’un tableau croisé dynamique.
Au lieu de cela, vous pouvez utiliser une combinaison FILTRE + RECHERCHE, qui est dynamique.
Lorsque la source change, les lignes de total peuvent monter ou descendre. Ma formule est prête à accueillir ces changements.
Prenons en compte un petit ensemble de données (rapport de ventes de deux employés).
Voici les paramètres du tableau croisé dynamique utilisés pour générer le rapport de tableau croisé dynamique ci-dessus.
En passant, pour accéder à l’éditeur de tableau croisé dynamique, allez dans le menu Insertion – Tableau croisé dynamique.
Formule pour extraire les lignes de total d’un rapport de tableau croisé dynamique dans Google Sheets
La combinaison FILTRE + RECHERCHE suivante permettra d’extraire les lignes de sous-total et de total du tableau croisé dynamique ci-dessus.
=filter(A1:D,search("Total",A1:A)>1)
C’est une formule très propre par rapport à celle de GETPIVOTDATA ci-dessus. Elle est également dynamique.
Résultat de la formule :
Pourquoi devrais-je l’appeler dynamique ?
Par exemple, supposons que vous ayez ajouté un autre employé à votre rapport de ventes.
La formule inclura également le total de cette personne dans la sortie extraite.
Comment utiliser la combinaison FILTRE + RECHERCHE dans un tableau croisé dynamique pour extraire des données
La fonction FILTRE est pratique pour filtrer un tableau avec des conditions. Vous pouvez également utiliser cette fonction dans un tableau croisé dynamique.
FILTRE(plage, condition1, [condition2, ...])
J’ai utilisé la formule RECHERCHE pour alimenter le critère/condition1 du FILTRE. C’est parce que je veux faire une correspondance partielle.
Je veux filtrer les lignes de la colonne A du tableau croisé dynamique qui contiennent la chaîne « Total ».
C’est pourquoi j’ai utilisé la fonction RECHERCHE dans le FILTRE.
La fonction RECHERCHE renverra un nombre numérique si la chaîne « Total » est disponible dans une ligne. Donc le critère sera :
search("Total",A1:A)>1
Certains d’entre vous peuvent se demander s’il est possible d’utiliser des caractères génériques dans le FILTRE. La réponse est NON. C’est pourquoi j’ai utilisé la fonction RECHERCHE.
Voilà tout ce qu’il faut savoir sur l’extraction dynamique des lignes de total à partir d’un tableau croisé dynamique dans Google Sheets.
J’espère que vous avez apprécié ce tutoriel. À la prochaine avec un autre tutoriel sur Google Sheets.
Articles similaires :
- Créez un rapport d’analyse d’âge à l’aide d’un tableau croisé dynamique dans Google Sheets.
- Rapport mensuel de tableau croisé dynamique dans Google Sheets en utilisant la colonne de date.
- Regrouper les dates dans un tableau croisé dynamique dans Google Sheets (mois, trimestre et année).
- Ajouter un champ calculé dans un tableau croisé dynamique dans Google Sheets.
- Zoomer dans un tableau croisé dynamique dans Google Sheets (champ de date).
- Comment trier les colonnes de total général du tableau croisé dynamique dans Google Sheets.
- Comment trier les colonnes du tableau croisé dynamique dans l’ordre personnalisé dans Google Sheets.
