Hassen Sie die GETPIVOTDATA-Funktion von Excel? Warum erscheint es? Wie können Sie das verhindern? Gibt es eine gute Verwendung für GETPIVOTDATA?
Die meisten Menschen begegnen GETPIVOTDATA zum ersten Mal, wenn sie versuchen, eine Formel außerhalb einer Pivot-Tabelle zu erstellen, die Zahlen in der Pivot-Tabelle verwendet. Beispielsweise wird dieser Abweichungsprozentsatz nicht in die anderen Monate kopiert, da Excel GETPIVOTDATA-Funktionen einfügt.
Excel fügt GETPIVOTDATA jedes Mal ein, wenn Sie mit der Maus oder den Pfeiltasten auf eine Zelle in der Pivot-Tabelle zeigen, während Sie eine Formel außerhalb der Pivot-Tabelle erstellen.
Wenn die Funktion GETPIVOTDATA nicht angezeigt werden soll, geben Sie einfach eine Formel wie = D5 / C5-1 ein, ohne mit der Maus oder den Pfeiltasten auf Zellen zu zeigen. Diese Formel kopiert ohne Probleme.
Hier ist ein Datensatz, der eine Plannummer pro Monat und Geschäft enthält. Es gibt auch tatsächliche Verkäufe pro Monat und Geschäft für die Monate, die abgeschlossen sind. Ihr Ziel ist es, einen Bericht zu erstellen, der die Istwerte für die abgeschlossenen Monate anzeigt und für die zukünftigen Monate plant.
Erstellen Sie eine Pivot-Tabelle mit Store in ROWS. Geben Sie Monat und geben Sie die SPALTEN ein. Sie erhalten den unten gezeigten Bericht mit Januar-Ist, Januar-Plan und dem völlig unsinnigen Januar-Ist + Plan.
Wenn Sie eine Monatszelle auswählen und zu Feldeinstellungen wechseln, können Sie die Zwischensummen in Keine ändern.
Dies entfernt den nutzlosen Actual + Plan. Aber Sie müssen immer noch die Planspalten für Januar bis April loswerden. Es gibt keine gute Möglichkeit, dies innerhalb der Pivot-Tabelle zu tun.
So wird Ihr monatlicher Workflow:
- Fügen Sie dem Datensatz die Istwerte für den neuen Monat hinzu.
- Erstellen Sie eine neue Pivot-Tabelle von Grund auf neu.
- Kopieren Sie die Pivot-Tabelle und fügen Sie sie als Werte ein, damit sie keine Pivot-Tabelle mehr ist.
- Löschen Sie die nicht benötigten Spalten.
Es gibt einen besseren Weg. Die folgende sehr kleine Abbildung zeigt ein neues Excel-Arbeitsblatt, das der Arbeitsmappe hinzugefügt wurde. Dies ist alles nur Excel, keine Pivot-Tabellen. Das einzige bisschen Magie ist eine IF-Funktion in Zeile 4, die basierend auf dem Datum in Zelle P1 von Ist auf Plan umschaltet.
Die allererste Zelle, die ausgefüllt werden muss, ist der Januar Actuals for Baybrook. Klicken Sie in diese Zelle und geben Sie ein Gleichheitszeichen ein. Navigieren Sie mit der Maus zurück zur Pivot-Tabelle. Finden Sie die Zelle für Januar Actuals für Baybrook. Klicken Sie auf diese Zelle und drücken Sie die Eingabetaste. Wie üblich erstellt Excel eine dieser nervigen GETPIVOTDATA-Funktionen, die nicht kopiert werden können.
Aber heute wollen wir die Syntax von GETPIVOTDATA untersuchen.
Das erste Argument unten ist das numerische Feld "Sales". Das zweite Argument ist die Zelle, in der sich die Pivot-Tabelle befindet. Die verbleibenden Argumentpaare sind Feldname und Wert. Sehen Sie, was die automatisch generierte Formel bewirkt hat? Es hat "Baybrook" als Namen des Geschäfts fest codiert. Aus diesem Grund können Sie diese automatisch generierten GETPIVOTDATA-Formeln nicht kopieren. Sie codieren Namen tatsächlich in Formeln. Obwohl Sie diese Formeln nicht kopieren können, können Sie sie bearbeiten. In diesem Fall ist es besser, wenn Sie die Formel so bearbeiten, dass sie auf Zelle $ D6 zeigt.
Hier ist die Formel, nachdem Sie sie bearbeitet haben. Vorbei sind "Baybrook", "Jan" und "Actual". Stattdessen zeigen Sie auf $ D6, E $ 3, E $ 4.
Kopieren Sie diese Formel und wählen Sie dann in allen anderen numerischen Zellen Spezial einfügen, Formeln.
Hier ist Ihr jährlicher Workflow:
- Bauen Sie einen hässlichen Pivot-Tisch, den niemand jemals sehen wird.
- Richten Sie das Berichtsarbeitsblatt ein.
Jeden Monat müssen Sie:
- Fügen Sie neue Istwerte unter die Daten ein.
- Aktualisieren Sie den hässlichen Pivot-Tisch.
-
Ändern Sie die Zelle P1 im Berichtsblatt, um den neuen Monat wiederzugeben. Alle Zahlen werden aktualisiert.
Ändern Sie die Zelle P1
Sie müssen zugeben, dass Sie mit einem einfachen Bericht, der Zahlen aus einer Pivot-Tabelle zieht, das Beste aus beiden Welten erhalten. Sie können den Bericht so formatieren, dass Sie eine Pivot-Tabelle nicht formatieren können. Leere Reihen sind in Ordnung. Sie können Währungssymbole in der ersten und letzten Zeile haben, aber nicht dazwischen. Sie erhalten auch doppelte Unterstreichungen unter den Gesamtsummen.
Vielen Dank an @iTrainerMX für den Vorschlag dieser Funktion.
Schau Video
- GetPivotData tritt auf, wenn eine Formel in eine Pivot-Tabelle zeigt
- Während die ursprüngliche Formel korrekt ist, können Sie die Formel nicht kopieren
- Die meisten Leute hassen getpivotdata und wollen es verhindern
- Methode 1: Erstellen Sie eine Formel ohne Maus- oder Pfeiltasten
- Methode 2: Deaktivieren Sie GetPivotData dauerhaft über die Dropdown-Liste neben Optionen
- GetPivotData wird jedoch verwendet
- Ihr Manager möchte einen Bericht mit Istwerten für die letzten Monate und ein Budget für die Zukunft
- Im normalen Workflow müssen Sie eine Pivot-Tabelle erstellen, in Werte konvertieren und Spalten löschen
- Entfernen von Zwischensummen, um zu verhindern, dass Januar Actual + Plan mithilfe der Feldeinstellungen erstellt wird
- Erstellen Sie stattdessen eine Pivot-Tabelle mit "zu vielen" Daten
- Verwenden Sie ein gut formatiertes Berichtsarbeitsblatt
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Erstellen Sie aus der ersten Datenzelle im Arbeitsblatt eine Formel mit der Maus
- Lassen Sie GetPivotData zu
- Untersuchen Sie die Syntax von GetPivotData (zurückzugebendes Feld, Pivot-Position, Paare)
- Ändern Sie den fest codierten Wert so, dass er auf eine Zelle zeigt
- Durch dreimaliges Drücken von F4 wird nur die Spalte gesperrt
- Durch zweimaliges Drücken von F4 wird nur die Zeile gesperrt
- Spezielle Formeln einfügen
- Workflow im nächsten Monat: Daten hinzufügen, Pivot-Tabelle aktualisieren, Datum ändern
- Achten Sie besonders auf neue Geschäfte
Abschrift des Videos
Lernen Sie Excel aus dem Podcast, Folge 2013 - GetPivotData ist möglicherweise nicht ganz böse!
Ich werde das gesamte Buch podcasten. Klicken Sie auf das "i" in der oberen rechten Ecke, um es zu abonnieren.
Okay, in Folge 1998 habe ich kurz über dieses GetPivotData-Problem gesprochen. Wenn wir eine prozentuale Varianz berechnen und außerhalb der Pivot-Tabelle nach innen zeigen und ich die Maus oder die Pfeiltaste verwende, also 2019 / 2018-1. Diese Antwort, die wir hier erhalten, ist für Januar korrekt. Wenn wir jedoch doppelklicken, um sie nach unten zu kopieren, wird die Formel nicht kopiert, sondern die Januar-Antwort wird vollständig nach unten kopiert. Und wenn wir es uns ansehen, bekommen wir GetPivotData, ich habe GetPivotData nicht eingegeben, ich habe nur auf diese Zellen gezeigt, und dies begann in Excel 2002 ohne jegliche Warnung. Und an diesem Punkt habe ich gesagt, dass der Weg, dies zu vermeiden, darin besteht, die Formel C5 / B5-1 einzugeben, und Sie erhalten eine Formel, die Sie kopieren können. Oder wenn Sie GetPivotData einfach hassen, wenn es "völlig böse" ist, gehen Sie zur Registerkarte "Analysieren".t Öffnen Sie übrigens die Schaltfläche Optionen. Gehen Sie zurück zur Pivot-Tabelle, gehen Sie zur Registerkarte Analysieren, öffnen Sie die Dropdown-Liste neben Optionen, deaktivieren Sie dieses Kontrollkästchen, es handelt sich um eine globale Einstellung. Sobald Sie es ausschalten, wird es für immer ausgeschaltet sein, in Ordnung.
Die meisten Fragen lauten: "Wie schalte ich GetPivotData aus?" aber hin und wieder bekomme ich jemanden, der GetPivotData liebt. Und ich habe mit Rob Collie zu Mittag gegessen, als er noch bei Microsoft war, und er sagte: "Nun, unsere internen Kunden lieben GetPivotData." Ich sagte Was? Nein, jeder hasst GetPivotData! “ Rob sagt: "Sie haben Recht, außerhalb von Microsoft hassen sie GetPivotData." Ich spreche über die Buchhalter in Microsoft, und später traf ich einen, der jetzt für das Excel-Team arbeitet, Carlos, und Carlos war einer der Buchhalter, die diese Methode verwenden.
Okay, also hier ist was wir tun müssen. Wir haben unseren Bericht, einen Datensatz hier, in dem wir für jeden Monat den Plan für jedes Geschäft haben, und dann sammeln wir unten die tatsächlichen Werte. Okay, wir haben also Aktualisierungen für Januar bis Dezember, aber nur für einige Monate, die vergangenen Monate. Und unser Manager möchte, dass wir einen Bericht mit Geschäften auf der linken Seite erstellen, natürlich nur den Geschäften in Texas, um das Leben zu erschweren. Und dann haben wir Monate, und wenn wir eine tatsächliche für diesen Monat haben, zeigen wir die tatsächliche, also den tatsächlichen Januar, den tatsächlichen Februar, den tatsächlichen März, den tatsächlichen April. Aber dann für die Monate, in denen wir keine tatsächlichen Daten haben, wechseln wir um und zeigen das Budget an, also budgetieren Sie bis Dezember und dann insgesamt alles in Ordnung. Wenn Sie versuchen, diese Pivot-Tabelle zu erstellen, ja,es funktioniert nicht
Fügen Sie also PivotTable, New Worksheet ein, und legen Sie Store auf die linke Seite, die schöne Seite, legen Sie Monate über die Oberseite, setzen Sie Type über die Oberseite, setzen Sie Sales hierher, in Ordnung. Hier ist also, was wir bekommen, mit dem wir anfangen müssen zu arbeiten, also haben wir den tatsächlichen Januar-Plan, den Januar-Plan und dann den völlig nutzlosen Januar-Ist-Plus-Plan. Niemand wird dies jemals verwenden, aber ich kann diese grauen Spalten entfernen, das ist einfach genug, einige hier in dieser Zelle, gehen Sie zu Feldeinstellungen und ändern Sie die Zwischensummen in Keine. Aber es gibt absolut keine Möglichkeit für mich, den Januar-Plan zu entfernen, der nicht auch den April-Mai-Juni-Juli-Plan entfernt. Okay, es gibt keine Möglichkeit, dies loszuwerden. An diesem Punkt stecke ich jeden Monat fest, die gesamte Pivot-Tabelle auszuwählen, gehe zum Kopieren und dann zum Einfügen, Einfügen von Werten. Es ist kein Pivot-Tisch mehr,und dann beginne ich manuell die Spalten zu löschen, die nicht im Bericht erscheinen.
Okay, das ist die normale Methode, aber die Buchhalter von Microsoft haben im Januar einen zusätzlichen Schritt hinzugefügt. Es dauert 15 Minuten, und dieser Schritt ermöglicht es dieser Pivot-Tabelle, für immer zu leben, oder? Ich nenne dies die hässlichste Pivot-Tabelle der Welt, und die Buchhalter von Microsoft akzeptieren, dass dies die hässlichste Pivot-Tabelle der Welt ist, aber niemand außer ihnen wird diesen Bericht jemals sehen. Sie kommen hierher, um ein neues Blatt zu erstellen und den Bericht zu erstellen, den ihr Manager wünscht. Okay, hier sind die Läden auf der linken Seite. Ich habe sie sogar in Houston, Dallas und andere gruppiert. Es ist ein gut formatierter Bericht. Ich habe die Summen hervorgehoben. Sie werden sehen, dass sich in der ersten Zeile eine Währung befindet, wenn wir einige Zahlen eingeben, aber nicht in diesen nachfolgenden Zeilen, leeren Zeilen. Oh, leere Zeilen in der Pivot-Tabelle.Und ein kleines bisschen Logik hier oben, wo ich das Durchgangsdatum in Zelle P1 einfügen kann, und dann habe ich hier eine Formel, die analysiert, ob der Monat des Durchgangsdatums> diese Spalte ist, und dann das Wort Ist, ansonsten Setzen Sie das Wort Plan, in Ordnung. Alles, was ich tun muss, ist, das Datum zu ändern und dann das Wort "Tatsächlich" nach Plan umzudrehen, in Ordnung.
Nun, hier ist, was wir tun, wir werden uns erlauben, GetPivotData'd zu sein, richtig? Ich bin nicht sicher, ob das ein Verb ist, aber wir werden Microsoft erlauben, GetPivotData zu erhalten. Also beginne ich eine Formel mit einem = zu erstellen, greife nach der Maus und werde nach dem tatsächlichen Baybrook im Januar suchen! Also gehe ich zurück in die hässlichste Pivot-Tabelle der Welt, finde Baybrook, finde Januar, finde aktuelle und klicke auf Enter, und lass sie es mir antun, okay, los geht's, wir haben jetzt eine GetPivotData-Formel. Ich erinnere mich an den Tag, an dem ich das getan habe, als Rob mir erklärte, was sie taten, und ich ging zurück und versuchte es. Jetzt, plötzlich, mein ganzes Leben lang, habe ich GetPivotData losgeworden, ich habe GetPivotData nie wirklich angenommen. Was es also ist, ist der erste Gegenstand, nach dem wir suchen. 'In einem Feld namens "Sales" beginnt hier die Pivot-Tabelle. Es kann sich um eine beliebige Zelle in der Pivot-Tabelle handeln, die oben links verwendet wird.
Okay, dies ist ein Feldname "Store", und dann haben sie "Baybrook" fest codiert, dies ist ein Feldname "Month", sie haben "January" fest codiert, dies ist ein Feldname "Type" und sie ' ve fest codiert "Actual". Deshalb können Sie es nicht kopieren, weil sie die Werte fest codiert haben. Aber die Buchhalter von Microsoft, Carlos und seine Mitarbeiter erkennen: „Whoa, warte eine Sekunde, wir haben das Wort Baybrook hier, wir haben Januar hier, wir haben Actual hier. Wir müssen diese Formel nur ändern, um auf die tatsächlichen Zellen im Bericht zu verweisen, anstatt fest codiert zu werden. “ Okay, also nennen sie diese Parametrisierung die GetPivotData.
Entfernen Sie das Wort Baybrook, kommen Sie hierher und klicken Sie auf Zelle D6. Jetzt muss ich das auf die Spalte beschränken, okay, also drücke ich dreimal die Taste F4 und bekomme ein einzelnes $ vor dem D, okay. Für den Monat Januar entferne ich den fest codierten Januar, klicke auf die Zelle E3 und drücke zweimal F4, um sie auf die Zeile E $ 3 zu sperren. Geben Sie Actual ein, entfernen Sie das Wort Actual, klicken Sie zweimal auf E4, erneut auf F4, und ich erhalte eine Formel, die diese Daten jetzt zurückzieht. Ich werde das kopieren und dann Special einfügen, Formate wählen, alt = "" ESF, sehen, dass das F dort unterstrichen ist, ESF Enter, und jetzt, da ich es getan habe, werde ich es einfach mit F4 wiederholen, F4 ist eine Wiederholung und F4. Okay, jetzt haben wir einen gut aussehenden Bericht, er hat Leerzeichen, er hat eine Formatierung, er hat die einzelne Buchhaltung unter jedem Abschnitt unterstrichen.Ganz unten ist die doppelte Buchhaltung unterstrichen.
Richtig, Sie bekommen dieses Zeug nie in eine Pivot-Tabelle, das ist unmöglich, aber dieser Bericht wird aus der Pivot-Tabelle gesteuert. Also, was wir tun, wenn wir die Mai-Istdaten erhalten, hierher zurückkehren, sie einfügen, die hässlichste Pivot-Tabelle der Welt aktualisieren und dann hier im Bericht einfach das Durchgangsdatum von 30.04 auf 31.05. Ändern. Und das bewirkt, dass diese Formel vom Wort "Plan" auf "Tatsächlich" umschaltet, wodurch die tatsächlichen Daten aus dem Bericht anstelle des Plans abgerufen werden. Nun, hier ist die Sache, die - das ist großartig, oder? Ich kann sehen, wo ich das oft tun würde, wenn ich immer noch in der Buchhaltung arbeiten würde.
Wenn Sie einen neuen Speicher erstellen, müssen Sie wissen, dass Sie ihn manuell hinzufügen müssen. Richtig, die Daten werden in der Pivot-Tabelle angezeigt, aber Sie würden sie manuell hinzufügen. Jetzt ist dies eine Teilmenge aller Geschäfte. Wenn es alle Geschäfte melden würde, würde ich wahrscheinlich hier draußen außerhalb des Druckbereichs etwas haben, das die Gesamtsumme aus der Pivot-Tabelle gezogen hat. Und dann würde ich wissen, wenn diese Summe nicht mit der Gesamtsumme aus der Pivot-Tabelle übereinstimmt, dass etwas nicht stimmt, und hier unten eine IF-Funktion haben, die sagt: „Hey, es wurden neue Daten hinzugefügt, seien Sie sehr vorsichtig. ” Sie haben eine Art Mechanismus, um zu erkennen, dass neue Daten vorhanden sind. Aber ich verstehe, es ist eine coole Verwendung. Während GetPivotData uns die meiste Zeit nur verrückt macht, kann es tatsächlich eine Verwendung dafür geben. In Ordung,Das ist also Tipp Nr. 21 von 40 im Buch. Kaufen Sie das Buch jetzt, bestellen Sie online und klicken Sie auf das „i“ in der oberen rechten Ecke.
Lange, lange Zusammenfassung heute, in Ordnung: GetPivotData tritt auf, wenn eine Formel innerhalb einer Pivot-Tabelle zeigt, eine Formel außerhalb der Pivot-Tabelle innerhalb. Die ursprüngliche Formel ist zwar korrekt, wird jedoch nicht kopiert. Die meisten Leute hassen GetPivotData und wollen es verhindern. Sie können also eine Formel ohne die Maus oder die Pfeiltasten erstellen, einfach die Formel eingeben oder GetPivotData dauerhaft deaktivieren, ah, aber es gibt eine Verwendung, in Ordnung. Wir müssen also einen Bericht mit den tatsächlichen Daten für den letzten Monat und dem Budget für die Zukunft erstellen. Normaler Workflow, Pivot-Tabelle erstellen, in Werte konvertieren, Spalten löschen. Es gibt eine Möglichkeit, die Zwischensummen mithilfe der Feldeinstellungen zu entfernen und den tatsächlichen Plusplan für Januar zu entfernen. Stattdessen erstellen wir einfach die hässlichste Pivot-Tabelle der Welt mit zu vielen Daten.
Erstellen Sie ein gut formatiertes, einfach altes Arbeitsblatt mit möglicherweise ein wenig Logik, um das Wort "Ist" in "Plan" zu ändern. Geben Sie dann in der ersten Berichtszelle, der ersten Stelle, an der sich die Zahlen in diesem Bericht befinden, an = ein, zeigen Sie auf die Pivot-Tabelle und lassen Sie GetPivotData zu. Wir untersuchen die Syntax von GetPivotData, also das zurückzugebende Feld Sales, in dem sich die Pivot-Tabelle befindet, und dann Kriterienpaare, den Feldnamen und den Wert. Wir werden den fest codierten Wert entfernen und auf eine Zelle zeigen. Durch dreimaliges Drücken von F4 wird nur die Spalte gesperrt. Durch zweimaliges Drücken von F4 wird nur die Zeile gesperrt. Kopieren Sie diese Formel. Fügen Sie spezielle Formeln ein. Ich habe dort einen zusätzlichen Tipp eingefügt, dass F4 eine Wiederholung ist, also musste ich nur einmal zum Dialogfeld "Inhalte einfügen" gehen und dann für die nächsten Spezialformeln zum Einfügen nur F4 verwenden. Nächsten Monat die Daten hinzufügen,Aktualisieren Sie die Pivot-Tabelle und ändern Sie das Durchgangsdatum. Stellen Sie sicher, dass sie keine neuen Geschäfte gebaut haben, wissen Sie, haben Sie einen Mechanismus, entweder manuell oder eine Prüfformel, probieren Sie es aus. Dank iTrainerMX auf Twitter, der GetPivotData vorgeschlagen hat, auch Carlos und Rob von Microsoft, Rob jetzt von Power Pivot Pro. Carlos dafür, dass er das benutzt hat, und Rob dafür, dass er mir erzählt hat, dass Carlos es benutzt hat. Ich habe Carlos später getroffen und er hat bestätigt, dass er einer der Buchhalter war, die das die ganze Zeit bei Microsoft benutzt haben.und Rob, der mir erzählt hat, dass Carlos es benutzt hat, ich habe Carlos später getroffen, und er hat ja bestätigt, er war einer der Buchhalter, die das die ganze Zeit bei Microsoft benutzt haben, okay, los geht's.und Rob, der mir erzählt hat, dass Carlos es benutzt hat, ich habe Carlos später getroffen, und er hat ja bestätigt, er war einer der Buchhalter, die das die ganze Zeit bei Microsoft benutzt haben, okay, los geht's.
Hey, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2013.xlsx
