Comment utiliser la fonction getpivotdata dans Microsoft Excel

La plupart des gens savent que vous pouvez référencer une ou plusieurs cellules, tables et en-têtes de colonne, ou des gammes nommées dans des formules Excel. Cependant, moins savez que vous pouvez référencer des points de données spécifiques dans les phares en utilisant la fonction getpivotdata. Voici comment.

La syntaxe getpivotdata

Chaque fois que vous utilisez la fonction getpivotdata, vous devez saisir au moins deux arguments. Que vous saisisse plus de deux dépend de ce que vous voulez récupérer. Voici la syntaxe:

=GETPIVOTDATA(a,b,c¹,c²...)

où

• un (requis) est le nom du champ Ticottable contenant les données que vous souhaitez récupérer,
• b (requis) est une référence à une seule cellule, plusieurs cellules ou une plage nommée dans le pivottable, et
• C¹ et c² (facultatif) sont le premier de jusqu'à 126 paires de noms de champ (x¹) et les noms d'articles (x²) Définir les données que vous souhaitez récupérer.

Argument un Doit toujours être enveloppé dans des guillemets doubles. La même chose peut être dite pour les noms de champ (x¹) et les noms d'articles (x²), sauf si ce sont des dates, des nombres ou des références cellulaires.

Utilisation de getpivotdata pour récupérer les grands totaux

Étant donné que la fonction GetPivotData extrait des données basées sur les noms de champ et les noms d'éléments, il est préférable de gérer les modifications structurelles de références à cellules cartères que directes. C'est pourquoi c'est un excellent moyen de récupérer les grands totaux pour, disons, un rapport.

Dans les exemples utilisés dans cette section, les lignes de la pointe des pays sont des pays, les colonnes sont des produits et les valeurs sont des ventes.

Pour tirer de grands totaux d'un poivable, vous devez d'abord vous assurer qu'ils sont affichés. Pour ce faire, sélectionnez n'importe quelle cellule dans le Ticottable et dans l'onglet Design du ruban, développez le menu déroulant « Grand totaux ». Ensuite, cliquez sur « ON pour les lignes et les colonnes ».

Maintenant, le Tivottable montre de grands totaux pour chaque ligne et colonne, ainsi qu'un grand total de tout ce qui est combiné dans la cellule en bas à droite.

Récupérer le grand total de toutes les données dans un

L'exemple le plus élémentaire d'une formule Getpivotdata dans Microsoft Excel est lorsque la fonction est utilisée pour récupérer un grand total de toutes les données dans un acicotable. En fait, la seule chose que vous devez taper est le signe égal (=)!

Pour extraire un grand total d'un Ticottable dans une autre cellule de votre classeur, sélectionnez la cellule où vous voulez que le grand total récupéré soit affiché et tapez le signe égal:

=

Ensuite, cliquez sur la cellule dans le Ticottable qui affiche le grand total, et remarquez que la formule est insérée automatiquement:

=GETPIVOTDATA("Sales",Totals!$A$3)

Dans cet exemple, « Ventes » le champ contenant les données à récupérer, et Totaux! $ A 3 $ est une référence absolue à la cellule supérieure gauche dans la feuille de calcul des totaux.

Lorsque vous appuyez sur Entrée, le grand total est extrait avec succès.

La fonction GetPivotData récupère uniquement les valeurs, et non la mise en forme, vous devez donc appliquer manuellement les cellules, le texte et le nombre.

Récupérer le total d'une colonne ou d'une ligne de pointe

Vous pouvez suivre le même principe pour récupérer le grand total d'une colonne ou d'une ligne à partir d'un accéléré dans Excel – tapez simplement le signe égal, cliquez sur la cellule contenant le grand total en bas d'une colonne donnée ou la fin d'une ligne donnée et appuyez sur Entrée.

Ici, le total des ventes du produit nommé amarilla (Dans la colonne B du Ticottable dans la feuille nommée Ventes) est récupéré:

=GETPIVOTDATA("Sales",Totals!$A$3,"Product","Amarilla")

où « Ventes » est la valeur que vous souhaitez récupérer, Totaux! $ A 3 $ fait référence à une cellule dans le poivable pertinent, et le « Produit », « Amarilla » Le jumelage dit à Excel de trouver le champ nommé Produit puis localiser l'article nommé amarilla Dans ce domaine.

Cependant, pour le moment, la formule n'est pas dynamique. En d'autres termes, si vous vouliez récupérer le grand total pour un autre produit, vous devrez modifier la formule GetpivotData existante ou en générer une nouvelle.

Au lieu de cela, plutôt que du codage dur du nom du produit dans l'argument final, vous pouvez référencer une cellule contenant le nom du produit:

=GETPIVOTDATA("Sales",Totals!$A$3,"Product",A1)

Les références cellulaires dans les formules n'ont pas besoin d'être enfermées dans des guillemets doubles.

Maintenant, lorsque vous tapez un nom de produit différent dans la cellule A1, le total des mises à jour en conséquence.

Allez plus loin et créez une liste déroulante des produits dans la cellule A1 à l'aide de l'outil de validation des données d'Excel. Cela vous évite d'avoir à taper les produits manuellement.

Suivez le même processus pour retourner le grand total à la fin d'une rangée dans le Tivottable. La formule suivante renvoie le total des ventes au Canada:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country","Canada")

où « Ventes » est la valeur à récupérer, Totaux! $ A 3 $ dit à Excel où se trouve le Tivottable, et le « Pays », « Canada » Le jumelage dit à Excel de trouver le champ nommé pays puis localiser l'article nommé Canada Dans ce domaine.

Ensuite, pour rendre la formule dynamique, remplacez «Canada» par une référence à une cellule contenant le nom du pays:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country",A1)

Utilisation de getpivotdata pour récupérer un point de données spécifique

En plus de récupérer les totaux grands, la fonction GetpivotData d'Excel peut également récupérer un point de données spécifique à l'intersection d'une colonne et d'une ligne spécifiées.

Supposons que vous souhaitiez voir les ventes totales du produit nommé Montana au Mexique.

Comme toujours avec la fonction getpivotdata, la meilleure approche consiste à laisser Excel générer la formule pour vous, puis vous pouvez faire des ajustements si nécessaire. Ainsi, après avoir tapé le signe égal dans la cellule où vous voulez que la valeur résultante soit située, cliquez sur la cellule pertinente dans le coton-cadrable. Dans ce cas, c'est la cellule D8, l'intersection de la colonne du Montana et de la rangée du Mexique.

En conséquence, Excel génère cette formule:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country","Mexico","Product","Montana")

Et lorsque vous appuyez sur Entrée, le total des ventes du Montana pour le Mexique est correctement retourné.

Maintenant, vous pouvez remplacer les valeurs codées durs dans la formule des références cellulaires. Plus précisément, plutôt que de référencer le « Mexique » pour le pays, vous pouvez référencer la cellule B2, et plutôt que de référencer « Montana » pour le produit, vous pouvez référencer la cellule A2:

=GETPIVOTDATA("Sales",Totals!$A$3,"Country",B2,"Product",A2) 

​​​​​​

En conséquence, lorsque vous modifiez le produit et le pays dans les cellules A2 et B2, respectivement, GetpivotData renvoie la valeur correspondante.

Utilisation de getpivotdata pour récupérer un chiffre basé sur une date

Une façon de voir des informations spécifiques à la date dans un Ticlotable consiste à insérer une trancheuse de chronologie. Cependant, où les tranchers de la chronologie vous permettent filtre Tivotable basé sur une certaine période de temps, la fonction getpivotdata vous permet extrait Informations provenant d'un pointage accidenté et prend moins de place.

La clé de l'utilisation de getpivotdata avec des dates est de structurer correctement le couvercle correctement. Dans les exemples utilisés dans cette section, les lignes de la broche sont des dates, les colonnes sont des produits et les valeurs sont des ventes. De plus, les grands totaux pour les lignes et les colonnes sont affichés.

Pour rendre les données plus claires, j'ai cliqué avec le bouton droit sur l'une des dates de la colonne A, cliqué sur « groupe » « sélectionné » mois « et » années « et cliqué » OK « .

En conséquence, les dates sont divisées en 2013 et 2014, chaque mois étant donné sa propre rangée.

Vous pouvez voir ce changement dans le volet Fields Tivottables, où les années (date) et les mois (date) sont affichées sous forme de champs de lignes séparés.

Récupérer un grand total d'une année spécifiée

Maintenant que le Ticlotable est correctement structuré par année et mois, vous pouvez utiliser la fonction GetpivotData pour extraire un total grand pour une année donnée.

Imaginez que vous souhaitez récupérer le total pour 2013. Pour ce faire, tapez le symbole égal et cliquez sur la cellule dans le Ticottable qui contient cette valeur. Tout de suite, Excel génère la formule pour vous:

=GETPIVOTDATA("Sales",Sales!$A$3,"Years (Date)",2013)

où « Ventes » le champ contenant les données à récupérer, et Ventes! $ A 3 $ est une référence absolue à la cellule supérieure gauche dans la feuille de travail de vente, « Années (date) » est le nom du champ, et 2013 est le nom de l'article dans ce champ.

Maintenant, rendez la formule dynamique. En d'autres termes, remplacez l'année du code dur par une référence cellulaire. Dans ce cas, l'année est dans la cellule A2:

=GETPIVOTDATA("Sales",Sales!$A$3,"Years (Date)",A2)

Lorsque vous appuyez sur Entrée, le total de 2013 est affiché dans la cellule B2.

Parce que la formule est dynamique, si vous changez l'année à 2014, le chiffre se met à jour en conséquence.

Récupérer un grand total à partir d'un mois spécifié d'une année spécifiée

En plus de récupérer un grand total annuel à partir d'un Tivottable à Excel, vous pouvez extraire un total pour un mois spécifique d'une année spécifiée.

Comme toujours, commencez par taper le symbole égal et sélectionnez la cellule pertinente par rapport au poivable. Ainsi, par exemple, pour retourner le grand total pour janvier 2014, vous devrez sélectionner la cellule H11.

Voici la formule que génére automatiquement:

=GETPIVOTDATA("Sales",Sales!$A$3,"Months (Date)",1,"Years (Date)",2014)

Remarquez comment le mois est indiqué comme un nombre (1 pour janvier). En conséquence, lorsque vous créez cette dynamique de formule, vous devez vous assurer que tout ce que vous saisissez entraîne un nombre entre 1 (janvier) et 12 décembre).

C'est là que la fonction du mois entre en jeu. Voici la formule dynamique:

=GETPIVOTDATA("Sales",Sales!$A$3,"Months (Date)",MONTH(A2&1),"Years (Date)",B2)

où

• « Mois (date) » est le premier nom de champ de ligne,

• Le nom de l'article dans ce premier champ est représenté par Mois (A2 et 1)qui prend le mois de trois lettres dans la cellule A2 et le transforme en valeur numérique entre 1 et 12,

• « Années (date) » est le nom de champ de deuxième ligne, et
• B2 dit à Excel que le nom de l'article dans ce deuxième champ est déterminé par la valeur de la cellule B2, qui, dans ce cas, est 2014.

Récupérer un point de données spécifique en fonction d'une date

Combinons tous les conseils expliqués jusqu'à présent dans ce guide pour retourner un total de ventes spécifique pour un produit donné au cours d'un mois donné d'une année donnée.

Tout d'abord, tapez le symbole égal et cliquez sur un point de données individuel dans le Ticotable pour générer la formule avec laquelle vous allez travailler.

Voici la formule résultante:

=GETPIVOTDATA("Sales",Sales!$A$3,"Product","Velo","Months (Date)",3,"Years (Date)",2014)

Maintenant, adaptez la formule pour la rendre dynamique. Changer « velo » à A2 (la cellule contenant le nom du produit), « 2014 » C2 (la cellule contenant l'année) et 3 à Mois (b2 et 1)qui convertit le mois dans la cellule B2 en un nombre:

=GETPIVOTDATA("Sales",Sales!$A$3,"Product",A2,"Months (Date)",MONTH(B2&1),"Years (Date)",C2)

Ensuite, appuyez sur Entrée pour voir le résultat.

Enfin, testez que la formule fonctionne comme prévu en modifiant les détails dans les cellules A2 en C2 et en référençant croisé le résultat avec le pivottable.

Points à noter lors de l'utilisation de getpivotdata

Avant d'aller de l'avant et d'utiliser cette fonction pratique dans vos classeurs Excel, prenez note de ces pointeurs finaux:

• Dactylographie = Et cliquer sur une cellule dans un Ticlotable ne déclenche qu'une formule getpivotdata si l'outil est activé. Pour vérifier cela, sélectionnez l'onglet Ticlotable, ouvrez l'onglet « Ticlotable Analyze » et dans le menu déroulant Options sous le nom du Ticottable, voyez si l'option Générer GetPivotData a une coche à côté. Si ce n'est pas le cas, cliquez dessus une fois.

• Si le deuxième argument d'une formule getpivotdata fait référence à une cellule qui n'est pas un pivottable, vous verrez un #Ref! erreur. Il en va de même pour tous les arguments qui ne correspondent pas à un nom de champ dans le Ticlotable.

• N'oubliez pas d'utiliser des devis doubles dans les arguments Getpivotdata, sauf s'ils contiennent des dates, des nombres ou des références cellulaires.

• Si vous utilisez une date complète comme argument GetpivotData, plutôt qu'un mois ou un an, vous devrez utiliser la fonction de date pour la transmettre du texte à un format de date reconnu.

---

Getpivotdata n'est qu'une des nombreuses fonctions de recherche dans Microsoft Excel, mais son caractère unique dans la façon dont il fonctionne avec les pivottables le distingue des autres. Si vous travaillez avec des données dans une plage régulière ou formatée sous forme de table Excel, vous pourriez envisager d'utiliser xLookup, d'indexer avec XMatch ou de filtrer à la place.