GetPivotData dans Excel - Conseils Excel

Vous venez de créer un tableau croisé dynamique dans Excel. Vous cliquez en dehors du tableau croisé dynamique. Vous créez une formule Excel. Vous copiez cette formule dans toutes les lignes du tableau croisé dynamique. Le calcul rapporte la mauvaise réponse pour tous sauf la première ligne du tableau croisé dynamique. Vous venez d'être piqué par la "fonctionnalité" Générer GetPivotData. Jetons un coup d'œil à ce que c'est et comment l'empêcher.

Évitez simplement le problème GetPivotData - La méthode rapide

Le moyen le plus rapide d'empêcher Excel de générer GetPivotData consiste à taper votre formule sans utiliser la souris ou les touches fléchées. Si vous tapez simplement =E5/D5, Excel créera une formule relative "normale" qui peut être copiée sur toutes les lignes adjacentes au tableau croisé dynamique.

Empêcher le problème GetPivotData - La méthode permanente

Il existe un paramètre masqué pour empêcher Excel de générer GetPivotData. Il est plus caché dans Excel 2003 que dans Excel 2007.

Dans Excel 2007, procédez comme suit:

• Créer un tableau croisé dynamique dans Excel 2007
• Assurez-vous que la cellule active est à l'intérieur du tableau croisé dynamique
• Regardez sur le côté gauche de l'onglet Options des outils de tableau croisé dynamique du ruban. Il y a un bouton Options. Ne cliquez pas sur le bouton Options! Sur le côté droit du bouton Options se trouve une flèche déroulante. Cliquez sur la flèche de la liste déroulante. Décochez Générer GetPivotData.
• Vous pouvez désormais saisir des formules à l'aide de la souris, des touches fléchées ou de la saisie.

Dans Excel 2003 et versions antérieures, procédez comme suit:

• Choisissez les outils, personnalisez
• Il existe trois onglets dans la boîte de dialogue Personnaliser. Choisissez l'onglet Commandes au centre.
• Dans la zone de liste de gauche, choisissez le 7e élément, Données
• Dans la zone de liste de droite, faites défiler presque tout le chemin vers le bas. La huitième icône à partir de la fin de la liste est Générer GetPivotData. Faites glisser cette icône de la zone de liste et déposez-la au milieu de n'importe quelle barre d'outils existante.
• Cliquez sur le bouton Fermer pour fermer la boîte de dialogue.
• Cliquez sur l'icône que vous venez d'ajouter à la barre d'outils Excel. Cela désactivera la fonction. Vous pouvez désormais entrer des formules à l'aide de la souris sans générer de fonctions GetPivotData.

Voici un conseil bonus: j'avais l'habitude de mépriser cette fonctionnalité et je voulais simplement qu'elle soit désactivée tout le temps. J'ai ajouté l'icône à ma barre d'outils, désactivé l'icône, puis supprimée de la barre d'outils. Maintenant… je me suis un peu détendu. Je peux voir que l'icône a parfois de bonnes utilisations. J'ai donc créé un tableau croisé dynamique dans Excel 2003 et je me suis assuré que le pointeur de cellule était dans le tableau croisé dynamique. J'ai ensuite utilisé la boîte de dialogue de personnalisation pour faire glisser l'icône Générer GetPivotData vers ma barre d'outils de tableau croisé dynamique. Maintenant, lorsque je suis dans un tableau croisé dynamique, je peux choisir d'activer ou de désactiver la fonctionnalité.

Pourquoi Microsoft a-t-il fait cela? Y a-t-il une bonne utilisation de GetPivotData?

Que doit faire GetPivotData? De toute évidence, Microsoft aime la fonction car ils l'ont imposée à des millions de personnes sans méfiance qui utilisent des tableaux croisés dynamiques.

GetPivotData renverra toutes les cellules visibles d'un tableau croisé dynamique. Plutôt que de pointer vers une adresse de cellule, cependant, vous décrivez la valeur comme une intersection de différentes valeurs de champ.

À mesure que le tableau croisé dynamique change, GetPivotData continuera à renvoyer les mêmes données logiques à partir du tableau croisé dynamique, à condition que les données soient toujours visibles dans le tableau croisé dynamique. Cela peut être important si vous changez de mois en mois dans le champ de page d'un tableau croisé dynamique et que vous souhaitez toujours renvoyer les ventes pour un client particulier. Au fur et à mesure que la liste des clients change chaque mois, la position du client changera. En utilisant =GETPIVOTDATA, vous pouvez vous assurer de renvoyer la valeur appropriée à partir du tableau croisé dynamique.

=GETPIVOTDATAcommence toujours par deux arguments particuliers. Il a ensuite éventuellement des paires d'arguments supplémentaires.

Voici les deux arguments requis:

1. Le nom d'un champ de données. Cela peut être "Somme des revenus" ou simplement "Revenus".
2. Toute cellule située «à l'intérieur» du tableau croisé dynamique. Saviez-vous que votre tableau croisé dynamique a un nom? Vous ne le saviez probablement pas car vous ne pouvez pas découvrir le nom dans l'interface Excel. Vous devrez vous rendre sur VBA pour connaître le nom du tableau croisé dynamique. Donc, il était plus facile pour Microsoft de vous permettre d'identifier le tableau croisé dynamique en pointant vers n'importe quelle cellule du tableau croisé dynamique. Bien que vous puissiez choisir n'importe quelle cellule, il est plus sûr de choisir la cellule du coin supérieur gauche. Il est possible que votre tableau croisé dynamique soit réduit pour une combinaison particulière de champs de page. Dans ce cas, l'utilisation de la cellule du coin supérieur gauche vous assurera de continuer à pointer à l'intérieur du tableau croisé dynamique.

Ensuite, vous continuez la fonction avec des paires d'arguments supplémentaires. Chaque paire comprend un nom de champ et une valeur.

GetPivotData ne peut renvoyer que les valeurs visibles dans le tableau croisé dynamique

Après avoir vu quelques fonctions GetPivotData fonctionner, vous pourriez penser qu'elles sont plus puissantes qu'elles ne le sont réellement. En fait, la fonction ne fonctionnera que si la valeur particulière est visible dans le tableau croisé dynamique. Considérez l'image ci-dessous.

• Dans la cellule A2, le GETPIVOTDATA renvoie les ventes de janvier 2004 d'ABC dans la région centrale. Ceci saisit le 80003 de la cellule G19.
• Cependant, la formule dans A6 échoue. Il demande des ventes d'ABC dans toutes les régions. Le tableau croisé dynamique montre ABC total pour le centre, ABC total pour l'Est, ABC total pour l'Ouest, mais il n'affiche jamais l'ABC total pour toutes les régions, donc le résultat est un #REF! Erreur.
• Si vous faisiez pivoter le champ de région vers la zone de page, la formule dans A6 commencerait à fonctionner, mais la formule dans A2 et A4 commencerait à renvoyer #REF !. Si vous choisissez Central dans la liste déroulante Région, les quatre formules fonctionneront.
• La désactivation des totaux généraux dans la boîte de dialogue Options de tableau croisé dynamique entraînera de nombreuses fonctions GetPivotData à commencer à renvoyer #REF! les erreurs.