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.
Übrigens, wenn Sie nicht möchten, dass die GETPIVOTDATA-Funktion angezeigt wird, geben Sie einfach eine Formel ein, z. B. =D5/C5-1ohne die Maus oder die Pfeiltasten zu verwenden, um 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 In Zeilen speichern. Geben Sie den Monat ein 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 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 komprimierte 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 January Actual für 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 Tatsächlich 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.
Die folgende Abbildung zeigt die Formel, nachdem Sie sie bearbeitet haben. Vorbei sind "Baybrook", "Jan" und "Actual". Stattdessen zeigen Sie auf $ D6, E $ 3 und E $ 4.
Kopieren Sie diese Formel und wählen Sie dann in allen anderen numerischen Zellen Spezial einfügen, Formeln.
Hier ist Ihr monatlicher 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.
Sie müssen zugeben, dass Sie mit einem 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.
