Détestez-vous la fonction GETPIVOTDATA d'Excel? Pourquoi cela apparaît-il? Comment pouvez-vous l'empêcher? Y a-t-il une bonne utilisation de GETPIVOTDATA?
La plupart des gens rencontrent d'abord GETPIVOTDATA lorsqu'ils essaient de créer une formule en dehors d'un tableau croisé dynamique qui utilise des nombres dans le tableau croisé dynamique. Par exemple, ce pourcentage de variance ne sera pas copié vers les autres mois en raison d'Excel insérant les fonctions GETPIVOTDATA.
Excel insère GETPIVOTDATA chaque fois que vous utilisez la souris ou les touches fléchées pour pointer vers une cellule à l'intérieur du tableau croisé dynamique lors de la création d'une formule en dehors du tableau croisé dynamique.
En passant, si vous ne voulez pas que la fonction GETPIVOTDATA apparaisse, tapez simplement une formule telle que = D5 / C5-1 sans utiliser la souris ou les touches fléchées pour pointer vers les cellules. Cette formule se copie sans aucun problème.
Voici un ensemble de données contenant un numéro de forfait par mois et par magasin. Il y a aussi des ventes réelles par mois et par magasin pour les mois qui sont terminés. Votre objectif est de créer un rapport qui montre les chiffres réels pour les mois terminés et le plan pour les mois à venir.
Créez un tableau croisé dynamique avec Store in ROWS. Mettez le mois et tapez les COLONNES. Vous obtenez le rapport ci-dessous, avec le plan réel de janvier, le plan de janvier et le plan réel + de janvier complètement absurde.
Si vous sélectionnez une cellule de mois et accédez aux paramètres de champ, vous pouvez modifier les sous-totaux sur Aucun.
Cela supprime le plan Réel + inutile. Mais vous devez toujours vous débarrasser des colonnes de plan pour janvier à avril. Il n'y a pas de bon moyen de le faire à l'intérieur du tableau croisé dynamique.
Ainsi, votre flux de travail mensuel devient:
- Ajoutez les chiffres réels du nouveau mois à l'ensemble de données.
- Créez un nouveau tableau croisé dynamique à partir de zéro.
- Copiez le tableau croisé dynamique et collez-le en tant que valeurs afin qu'il ne soit plus un tableau croisé dynamique.
- Supprimez les colonnes dont vous n'avez pas besoin.
Il y a une meilleure façon de procéder. La très petite figure suivante montre une nouvelle feuille de calcul Excel ajoutée au classeur. Tout cela est juste Excel, pas de tableaux croisés dynamiques. Le seul peu de magie est une fonction IF de la ligne 4 qui bascule de Réel à Plan en fonction de la date de la cellule P1.
La toute première cellule à remplir est janvier, Rapports statistiques pour Baybrook. Cliquez dans cette cellule et tapez un signe égal. À l'aide de la souris, revenez au tableau croisé dynamique. Recherchez la cellule Rapports statistiques de janvier pour Baybrook. Cliquez sur cette cellule et appuyez sur Entrée. Comme d'habitude, Excel crée l'une de ces fonctions GETPIVOTDATA ennuyeuses qui ne peuvent pas être copiées.
Mais aujourd'hui, étudions la syntaxe de GETPIVOTDATA.
Le premier argument ci-dessous est le champ numérique "Ventes". Le deuxième argument est la cellule dans laquelle réside le tableau croisé dynamique. Les paires d'arguments restantes sont le nom et la valeur du champ. Voyez-vous ce que la formule générée automatiquement a fait? Il a codé en dur "Baybrook" comme nom du magasin. C'est pourquoi vous ne pouvez pas copier ces formules GETPIVOTDATA générées automatiquement. Ils codent en fait des noms en dur dans des formules. Même si vous ne pouvez pas copier ces formules, vous pouvez les modifier. Dans ce cas, il serait préférable que vous modifiiez la formule pour qu'elle pointe vers la cellule $ D6.
Voici la formule après l'avoir modifiée. Finis "Baybrook", "Jan" et "Actual". Au lieu de cela, vous pointez vers $ D6, E $ 3, E $ 4.
Copiez cette formule, puis choisissez Collage spécial, formules dans toutes les autres cellules numériques.
Voici maintenant votre flux de travail annuel:
- Construisez un tableau croisé dynamique que personne ne verra jamais.
- Configurez la feuille de calcul du rapport.
Chaque mois, vous devez:
- Collez les nouveaux chiffres réels sous les données.
- Actualisez le vilain tableau croisé dynamique.
-
Modifiez la cellule P1 de la feuille de rapport pour refléter le nouveau mois. Tous les numéros sont mis à jour.
Changer la cellule P1
Vous devez admettre que l'utilisation d'un rapport simple qui extrait des chiffres d'un tableau croisé dynamique vous donne le meilleur des deux mondes. Vous êtes libre de mettre en forme le rapport de manière à ne pas mettre en forme un tableau croisé dynamique. Les lignes vides sont bien. Vous pouvez avoir des symboles monétaires sur la première et la dernière ligne, mais pas entre les deux. Vous obtenez également des soulignements doubles sous les totaux généraux.
Merci à @iTrainerMX d'avoir suggéré cette fonctionnalité.
Regarder la vidéo
- GetPivotData se produit lorsqu'une formule pointe à l'intérieur d'un tableau croisé dynamique
- Bien que la formule initiale soit correcte, vous ne pouvez pas copier la formule
- La plupart des gens détestent obtenir des données croisées dynamiques et veulent l'empêcher
- Méthode 1: créer une formule sans la souris ni les touches fléchées
- Méthode 2: désactivez GetPivotData de manière permanente à l'aide du menu déroulant à côté des options
- Mais il y a une utilisation pour GetPivotData
- Votre responsable veut un rapport avec les chiffres réels des mois passés et le budget pour l'avenir
- Le flux de travail normal vous obligerait à créer un tableau croisé dynamique, à convertir en valeurs, à supprimer des colonnes
- Suppression des sous-totaux pour empêcher le plan réel + de janvier à l'aide des paramètres de champ
- Créez plutôt un tableau croisé dynamique avec "trop" de données
- Utilisez une feuille de calcul de rapport bien formatée
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- À partir de la première cellule de données de la feuille de calcul, créez une formule avec la souris
- Autoriser GetPivotData à se produire
- Examiner la syntaxe de GetPivotData (champ à renvoyer, emplacement du pivot, paires)
- Changer la valeur codée en dur pour pointer vers une cellule
- Appuyer trois fois sur F4 ne verrouille que la colonne
- Appuyer deux fois sur F4 ne verrouille que la ligne
- Coller des formules spéciales
- Workflow le mois prochain: ajoutez des données, actualisez le tableau croisé dynamique, changez de date
- Ultra prudent pour faire attention aux nouveaux magasins
Transcription de la vidéo
Apprenez Excel à partir d'un podcast, épisode 2013 - GetPivotData n'est peut-être pas entièrement mauvais!
Je vais diffuser tout ce livre en podcast, cliquez sur ce «i» dans le coin supérieur droit pour vous abonner.
Très bien, dans l'épisode 1998, j'ai brièvement parlé de ce problème GetPivotData. Si nous calculons un% de variance et que nous sommes en dehors du tableau croisé dynamique pointant à l'intérieur, et que j'utilise la souris ou la touche fléchée, donc 2019 / 2018-1. Cette réponse que nous allons obtenir ici est correcte pour janvier, mais lorsque nous double-cliquons pour copier cela vers le bas, la formule ne copie pas, nous obtenons la réponse de janvier tout en bas. Et quand nous y regardons, nous obtenons GetPivotData, je n'ai pas tapé GetPivotData, je viens de pointer vers ces cellules, et cela a commencé à se produire dans Excel 2002 sans aucun avertissement. Et à ce moment-là, j'ai dit que le moyen d'éviter cela est de taper la formule C5 / B5-1, et vous obtiendrez une formule que vous pourrez copier. Ou si vous détestez simplement GetPivotData, si c'est "complètement diabolique", allez dans l'onglet Analyser, net ouvrez le bouton Options au passage. Revenez au tableau croisé dynamique, allez dans l'onglet Analyser, ouvrez la liste déroulante à côté d'Options, décochez cette case, c'est un paramètre global. Une fois que vous l'éteignez, il l'éte pour toujours, d'accord.
La plupart du temps, les questions que je reçois sont "Comment désactiver GetPivotData?" mais de temps en temps, je trouverai quelqu'un qui aime GetPivotData. Et je déjeunais avec Rob Collie quand il était encore chez Microsoft, et il a dit: «Eh bien, nos clients internes adorent GetPivotData.» J'ai dit quoi? Non, tout le monde déteste GetPivotData! » Rob dit: "Vous avez raison, en dehors de Microsoft, absolument, ils détestent GetPivotData." Je parle des comptables à l'intérieur de Microsoft, et plus tard, j'en ai rencontré un qui travaille maintenant pour l'équipe Excel, Carlos, et Carlos était l'un des comptables qui utilisent cette méthode.
Très bien, alors voici ce que nous devons faire. Nous avons notre rapport, un ensemble de données ici qui, pour chaque mois, nous avons le plan pour chaque magasin, puis en bas, nous accumulons les chiffres réels. Très bien, nous avons donc des chiffres réels pour janvier à décembre, mais nous n'avons que des chiffres réels pour quelques mois, les mois qui se sont écoulés. Et ce que notre responsable veut que nous fassions, c'est rédiger un rapport avec des magasins sur le côté gauche, seuls les magasins du Texas, bien sûr, pour rendre la vie plus difficile. Et puis, nous avons des mois, et si nous avons un réel pour ce mois, nous montrons le réel, donc janvier réel, février réel, mars réel, avril réel. Mais ensuite, pour les mois où nous n'avons pas de chiffres réels, nous basculons et montrons le budget, donc budgétisez jusqu'en décembre, puis un total totalisant tout, d'accord. Eh bien, lorsque vous essayez de créer ce tableau croisé dynamique, ouais,ça ne marche pas.
Alors insérez le tableau croisé dynamique, Nouvelle feuille de calcul, vous placez Store sur le côté gauche, c'est beau, mettez Mois en haut, mettez Type en haut, mettez Ventes ici, d'accord. Donc, voici ce que nous obtenons avec lequel nous devons commencer à travailler, donc nous avons le plan réel de janvier, le plan de janvier, puis le plan de janvier plus complètement inutile. Personne ne l'utilisera jamais, mais je peux me débarrasser de ces colonnes grises, c'est assez facile, certains ici dans cette cellule, allez dans Paramètres de champ et modifiez les sous-totaux sur Aucun. Mais il n'y a absolument aucun moyen pour moi de supprimer le plan de janvier qui ne supprimera pas également le plan d'avril mai juin juillet, d'accord, il n'y a aucun moyen de s'en débarrasser. Donc, à ce stade, chaque mois, je suis bloqué en sélectionnant tout le tableau croisé dynamique, en allant à Copier, puis à Coller, Coller les valeurs. Ce n'est plus un tableau croisé dynamique,puis je commence à supprimer manuellement les colonnes qui n'apparaissent pas dans le rapport.
D'accord, c'est la méthode normale, mais les comptables de Microsoft ont ajouté une étape supplémentaire en janvier, cela prend 15 minutes, et cette étape permet à ce tableau croisé dynamique de vivre éternellement, non? J'appelle cela le tableau croisé dynamique le plus laid du monde, et les comptables de Microsoft admettent qu'il s'agit du tableau croisé dynamique le plus laid du monde, mais personne ne verra jamais ce rapport à part eux. Ce qu'ils font, c'est qu'ils viennent ici sur une nouvelle feuille et rédigent le rapport que leur responsable souhaite. D'accord, voici les magasins sur le côté gauche, je les ai même regroupés en Houston, Dallas et autres, c'est un rapport bien formaté. J'ai mis en évidence les totaux, vous verrez que lorsque nous entrons des chiffres, il y a de la devise sur la première ligne, mais pas ces lignes suivantes, des lignes vides. Ooh, lignes vides dans le tableau croisé dynamique.Et un tout petit peu de logique ici, où je peux mettre la date de fin dans la cellule P1, puis j'ai une formule ici qui analyse que SI le mois de la date de fin est> cette colonne, puis mettre le mot Réel, sinon mettez le mot Plan, d'accord. Donc, tout ce que j'ai à faire est de changer cela jusqu'à la date, puis le mot Réel retourne au plan, d'accord.
Maintenant, voici ce que nous faisons, nous allons nous permettre d'être GetPivotData'd, non? Je ne suis pas sûr que ce soit un verbe, mais nous allons autoriser Microsoft à GetPivotData. Alors je commence à construire une formule avec un =, je prends la souris, et je vais chercher le Baybrook de janvier! Je retourne donc dans le tableau croisé dynamique le plus laid du monde, je trouve Baybrook, je trouve janvier, je trouve réel, et je clique sur Entrée, et je les laisse me le faire, d'accord, nous y voilà, nous avons maintenant une formule GetPivotData. Je me souviens du jour où j'ai fait ça, c'était comme, vous savez, après que Rob m'ait expliqué ce qu'ils faisaient, et je suis retourné et j'ai essayé. Maintenant, tout d'un coup, toute ma vie, je me suis débarrassé de GetPivotData, je n'ai jamais vraiment adopté GetPivotData. Donc ce que c'est, c'est le premier élément, c'est ce que nous recherchons, là 'sa champ appelé Ventes, c'est là que le tableau croisé dynamique commence, et il peut s'agir de n'importe quelle cellule du tableau croisé dynamique, ils utilisent la main en haut à gauche.
D'accord, il s'agit d'un nom de champ "Store", puis ils ont codé en dur "Baybrook", il s'agit d'un nom de champ "Mois", ils ont codé en dur "Janvier", il s'agit d'un nom de champ "Type", et ils ' ve "Réel" codé en dur. C'est pourquoi vous ne pouvez pas le copier, car ils ont codé les valeurs en dur. Mais les comptables de Microsoft, Carlos et ses collègues réalisent: «Whoa, attendez une seconde, nous avons le mot Baybrook ici, nous avons janvier ici, nous avons Actual ici. Nous devons simplement modifier cette formule pour qu'elle pointe vers les cellules réelles du rapport au lieu d'être codées en dur. » Très bien, ils appellent donc cela le paramétrage de GetPivotData.
Supprimez le mot Baybrook, venez ici et cliquez sur la cellule D6. Maintenant, je dois verrouiller cela dans la colonne, d'accord, alors j'appuie 3 fois sur la touche F4, je reçois un seul $ avant le D, d'accord. Pour le mois de janvier, je supprime le janvier codé en dur, je clique sur la cellule E3, j'appuie deux fois sur F4 pour le verrouiller sur la ligne, E $ 3. Tapez Réel, supprimez le mot Réel, cliquez sur E4, encore une fois sur F4 deux fois, d'accord, et j'obtiens une formule qui récupère maintenant ces données. Je vais copier ça, puis Coller spécial, choisissez Formats, alt = "" ESF, voyez le F est souligné ici, ESF Enter, et puis maintenant que, j'ai fait ça, je vais juste répéter avec F4, F4 est une refonte et F4. Très bien, nous avons maintenant un beau rapport, il a des blancs, il a un formatage, il a un soulignement comptable unique sous chaque section,tout en bas, il a le double soulignement comptable.
Bon, vous n'obtenez jamais ce truc dans un tableau croisé dynamique, c'est impossible, mais ce rapport est tiré du tableau croisé dynamique. Alors, ce que nous faisons lorsque nous obtenons les chiffres réels de mai, revenez ici, collez-les, allez actualiser le tableau croisé dynamique le plus laid du monde, puis ici sur le rapport, changez simplement la date de fin du 30/04 au 31/05. Et ce que cela fait, c'est que cette formule passe du mot Plan à Réel, qui extrait les données réelles du rapport, au lieu du plan, d'accord. Maintenant, voici la chose qui - c'est génial, non? Je vois souvent où je ferais cela si je travaillais encore, vous savez, dans la comptabilité.
La chose à laquelle vous devez faire très attention est que s'ils créent un nouveau magasin, vous devez savoir l'ajouter manuellement, à droite, les données vont apparaître dans le tableau croisé dynamique, mais vous l'ajouteriez manuellement. Maintenant, celui-ci est un sous-ensemble de tous les magasins, s'il déclarait tous les magasins, j'aurais probablement ici, en dehors de la plage d'impression, quelque chose qui extrait le total général du tableau croisé dynamique. Et puis je saurais, si ce total ne correspond pas au total général du tableau croisé dynamique, que quelque chose ne va pas, et j'aurais une fonction IF en bas ici. " Ils ont une sorte de mécanisme pour détecter que de nouvelles données sont là. Mais je comprends, c'est une bonne utilisation. Ainsi, alors que la plupart du temps, GetPivotData nous rend fous, il peut en fait être utile. Bien,c'est donc le conseil n ° 21 sur 40 dans le livre, achetez le livre maintenant, commandez en ligne, cliquez sur ce «i» dans le coin supérieur droit.
Récapitulatif long, long aujourd'hui, d'accord: GetPivotData se produit lorsqu'une formule pointe à l'intérieur d'un tableau croisé dynamique, une formule à l'extérieur du tableau croisé dynamique pointe à l'intérieur. Bien que la formule initiale soit correcte, elle ne sera pas copiée. La plupart des gens détestent GetPivotData et veulent l'empêcher. Vous pouvez donc créer une formule sans la souris ou les touches fléchées, tapez simplement la formule ou désactivez GetPivotData de façon permanente, ah, mais il y a une utilisation, d'accord. Nous devons donc créer un rapport avec des chiffres réels pour le mois dernier, un budget pour l'avenir. Flux de travail normal, créer un tableau croisé dynamique, convertir en valeurs, supprimer des colonnes. Il existe un moyen de supprimer les sous-totaux en utilisant les paramètres de champ, en supprimant ce plan plus réel de janvier. Au lieu de cela, nous allons simplement créer le tableau croisé dynamique le plus laid du monde avec trop de données.
Créez une ancienne feuille de calcul de rapport joliment formatée, avec peut-être un peu de logique pour changer le mot Réel en Plan. Et puis à partir de la première cellule de rapport, le premier endroit où les nombres vont être dans ce rapport, tapez un =, pointez sur le tableau croisé dynamique et autorisez GetPivotData à se produire. Nous examinons la syntaxe de GetPivotData, c'est donc le champ à renvoyer, Sales, où réside la table Pivot, puis les paires de critères, le nom du champ et la valeur. Nous allons supprimer la valeur codée en dur et pointer sur une cellule, appuyer 3 fois sur F4 verrouille uniquement la colonne, appuyer sur F4 2 fois pour verrouiller uniquement la ligne, copier cette formule, Coller les formules spéciales. J'ai ajouté une astuce supplémentaire pour dire que F4 est une refonte, donc je n'ai eu qu'à accéder à la boîte de dialogue Collage spécial une fois, puis pour les prochaines formules spéciales de collage, j'ai juste utilisé F4. Le mois prochain, ajoutez les données,actualisez le tableau croisé dynamique, modifiez la date de fin. Assurez-vous qu'ils n'ont pas construit de nouveaux magasins, vous savez, ont une sorte de mécanisme, manuel ou une formule de contrôle, vérifiez-le. Merci à iTrainerMX sur Twitter, qui a suggéré GetPivotData, ainsi qu'à Carlos et Rob de Microsoft, Rob maintenant de Power Pivot Pro. Carlos pour avoir utilisé ceci, et Rob pour m'avoir dit que Carlos l'utilisait, j'ai rencontré Carlos plus tard, et il a confirmé que oui, il était l'un des comptables qui l'utilisaient tout le temps chez Microsoft, d'accord, c'est parti.et Rob pour m'avoir dit que Carlos l'utilisait, j'ai rencontré Carlos plus tard, et il a confirmé que oui, il était l'un des comptables qui l'utilisait tout le temps chez Microsoft, d'accord, voilà.et Rob pour m'avoir dit que Carlos l'utilisait, j'ai rencontré Carlos plus tard, et il a confirmé que oui, il était l'un des comptables qui l'utilisait tout le temps chez Microsoft, d'accord, voilà.
Eh bien, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2013.xlsx
