Vorbereiten von Quelldaten für Pivot-Tabelle

Die Daten im richtigen Format zu haben, ist ein entscheidender Schritt bei der Erstellung einer robusten und fehlerfreien Pivot-Tabelle. Wenn Sie es nicht richtig machen, können Sie am Ende viele Probleme mit Ihrer Pivot-Tabelle haben.

Was ist ein gutes Design für die Quelldaten für die Pivot-Tabelle?

Sehen wir uns ein Beispiel für gute Quelldaten für eine Pivot-Tabelle an.

Folgendes macht es zu einem guten Quelldatendesign:

• Die erste Zeile enthält Überschriften, die die Daten in den Spalten beschreiben.
• Jede Spalte repräsentiert eine eindeutige Datenkategorie. Spalte C enthält beispielsweise nur Produktdaten und Spalte D und nur Monatsdaten.
• Jede Zeile ist ein Datensatz, der eine Instanz der Transaktion oder des Verkaufs darstellen würde.
• Die Datenheader sind eindeutig und werden nirgendwo im Datensatz wiederholt. Wenn Sie beispielsweise Verkaufszahlen für vier Quartale in einem Jahr haben, sollten Sie diese NICHT alle als Verkäufe benennen. Geben Sie diesen Spaltenüberschriften stattdessen eindeutige Namen wie Verkauf Q1, Verkauf Q2 usw.
  • Wenn Sie keine eindeutigen Titel haben, können Sie trotzdem eine Pivot-Tabelle erstellen und Excel würde diese automatisch durch Hinzufügen eines Suffixes (z. B. Vertrieb, Vertrieb2, Vertrieb3) eindeutig machen. Dies wäre jedoch eine schreckliche Möglichkeit, eine Pivot-Tabelle vorzubereiten und zu verwenden.

Häufige Fallstricke, die Sie bei der Aufbereitung der Quelldaten vermeiden sollten

• Die Quelldaten sollten keine leeren Spalten enthalten. Dieser ist leicht zu erkennen. Wenn in den Quelldaten eine leere Spalte vorhanden ist, können Sie keine Pivot-Tabelle erstellen. Es wird ein Fehler wie unten gezeigt angezeigt.
• Die Quelldaten sollten keine leeren Zellen/Zeilen enthalten. Obwohl Sie trotz leerer Zellen oder Zeilen erfolgreich eine Pivot-Tabelle erstellen können, gibt es viele Nebenwirkungen, die Sie später am Tag beißen können.
  • Angenommen, Sie haben eine leere Zelle in der Verkaufsspalte. Wenn Sie mit diesen Daten eine Pivot-Tabelle erstellen und das Verkaufsfeld in den Spaltenbereich einfügen, wird Ihnen die ANZAHL und nicht die SUMME angezeigt. Das liegt daran, dass Excel die gesamte Spalte als Textdaten interpretiert (nur wegen einer einzelnen leeren Zelle).
• Wenden Sie das relevante Format auf die Zellen in den Quelldaten an. Wenn Sie beispielsweise über Datumsangaben verfügen (die als Seriennummern im Backend in Excel gespeichert werden), wenden Sie eines der zulässigen Datumsformate an. Dies würde Ihnen helfen, die Pivot-Tabelle zu erstellen und das Datum als eines der Kriterien zum Zusammenfassen, Gruppieren und Sortieren der Daten zu verwenden.
  • Wenn Sie ein paar Sekunden Zeit haben, versuchen Sie dies. Formatieren Sie die Datumsangaben in Ihrer Pivot-Tabelle als Zahlen, und erstellen Sie dann eine Pivot-Tabelle mit diesen Daten. Wählen Sie nun in der Pivot-Tabelle das Datumsfeld aus und sehen Sie, was passiert. Es wird automatisch in den Wertebereich eingefügt. Das liegt daran, dass Ihre Pivot-Tabelle nicht weiß, dass dies Daten sind. Es interpretiert diese als Zahlen.
• Schließen Sie keine Spaltensummen, Zeilensummen, Durchschnitte usw. als Teil der Quelldaten ein. Sobald Sie die Pivot-Tabelle haben, können Sie diese später problemlos abrufen.
• Erstellen Sie immer eine Excel-Tabelle und verwenden Sie diese dann als Quelle für eine Pivot-Tabelle. Dies ist eher eine gute Praxis und kein Fallstrick. Ihre Pivot-Tabelle würde auch mit Quelldaten funktionieren, die keine Excel-Tabelle sind. Der Vorteil von Excel Table besteht darin, dass die expandierenden Daten angepasst werden können. Wenn Sie dem Dataset weitere Zeilen hinzufügen, müssen Sie die Quelldaten nicht immer wieder anpassen. Sie können die Pivot-Tabelle einfach aktualisieren und sie berücksichtigt automatisch die neuen Zeilen, die den Quelldaten hinzugefügt wurden.

Beispiele für fehlerhafte Quelldatendesigns

Schauen wir uns einige schlechte Beispiele für Quelldatendesigns an.

Fehlerhaftes Quelldatendesign - Beispiel 1

Dies ist eine gängige Methode zur Pflege von Daten, da sie leicht zu verfolgen und zu verstehen ist. Bei dieser Datenanordnung gibt es zwei Probleme:

• Sie erhalten kein vollständiges Bild. Sie können beispielsweise sehen, dass der Umsatz für Mid West im ersten Quartal 2924300 beträgt. Aber handelt es sich um einen einzelnen Verkauf oder um eine Anzahl von Verkäufen? Wenn Sie jeden Datensatz in einer separaten Zeile zur Verfügung haben, können Sie eine bessere Analyse durchführen.
• Wenn Sie damit fortfahren und eine Pivot-Tabelle erstellen (was Sie können), erhalten Sie unterschiedliche Felder für verschiedene Quartale. Etwas wie unten gezeigt:

Fehlerhaftes Quelldatendesign - Beispiel 2

Diese Datendarstellung mag vom Management und dem Publikum von PowerPoint-Präsentationen gut angenommen werden, eignet sich jedoch nicht zum Erstellen einer Pivot-Tabelle.

Auch dies ist die Art von Zusammenfassung, die Sie ganz einfach mit einer Pivot-Tabelle erstellen können. Selbst wenn Sie Ihre Daten irgendwann einmal so aussehen lassen möchten, pflegen Sie die Quelldaten in einem Pivot-fähigen Format und erstellen Sie diese Ansicht mithilfe der Pivot-Tabelle.

Fehlerhaftes Quelldatendesign - Beispiel 3

Dies ist wiederum eine Ausgabe, die leicht mit einer Pivot-Tabelle erhalten werden kann. Sie kann jedoch nicht zum Erstellen einer Pivot-Tabelle verwendet werden.

Der Datensatz enthält leere Zellen und die Quartale werden als Spaltenüberschriften verteilt.

Außerdem wird die Region oben angegeben, obwohl sie in jedem Datensatz enthalten sein sollte.

[FALLSTUDIE] Konvertieren von schlecht formatierten Daten in Pivot-Tabellenfähige Quelldaten

Manchmal erhalten Sie möglicherweise ein Dataset, das nicht als Quelldaten für die Pivot-Tabelle verwendet werden kann. In einem solchen Fall haben Sie möglicherweise keine andere Wahl, als die Daten in ein Pivot-freundliches Datenformat zu konvertieren.

Hier ist ein Beispiel für schlechtes Datendesign:

Jetzt können Sie Excel-Funktionen oder Pivot-Abfrage verwenden, um diese Daten in ein Format zu konvertieren, das als Quelldaten für die Pivot-Tabelle verwendet werden kann.

Sehen wir uns an, wie diese beiden Methoden funktionieren.

Methode 1: Verwenden von Excel-Formeln

Sehen wir uns an, wie Sie Excel-Funktionen verwenden, um diese Daten in ein für Pivot-Tabellen geeignetes Format zu konvertieren.

• Erstellen Sie eine eindeutige Spaltenüberschrift für alle Kategorien im ursprünglichen Dataset. In diesem Beispiel wären es Region, Quartal und Umsatz.
• Verwenden Sie in der Zelle unter der Überschrift Region die folgende Formel: =INDEX($A$2:$A$5,ROUNDUP(ROWS($A$2:A2)/COUNTA($B$1:$E$1),0))
  • Ziehen Sie die Formel nach unten und es werden alle Regionen wiederholt.
• Verwenden Sie in der Zelle unter der Überschrift Quartal die folgende Formel: =INDEX($B$1:$E$1,ROUNDUP(MOD(ROWS($A$2:A2),COUNTA($B$1:$E$1)+0.1) ,0))
  • Ziehen Sie die Formel nach unten und es werden alle Quartale wiederholt.
• Verwenden Sie in der Kopfzeile unter Sales die folgende Formel: =INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0 ))
  • Ziehen Sie es nach unten, um alle Werte zu erhalten. Diese Formel verwendet die Regions- und Quartalsdaten als Nachschlagewerte und gibt den Verkaufswert aus dem ursprünglichen Dataset zurück.

Jetzt können Sie diese resultierenden Daten als Quelldaten für die Pivot-Tabelle verwenden.

Klicken Sie hier, um die Beispieldatei herunterzuladen.

Methode 2: Verwenden von Power Query

Power Query verfügt über eine Funktion, mit der diese Art von Daten problemlos in ein Pivot-fähiges Datenformat konvertiert werden kann.

Wenn Sie Excel 2016 verwenden, sind die Power Query-Funktionen auf der Registerkarte Daten in der Gruppe Abrufen und Transformieren verfügbar. Wenn Sie Excel 2013 oder frühere Versionen verwenden, können Sie es als Add-In verwenden.

Hier ist eine ausgezeichnete Anleitung zur Installation von Power Query von Jon von Excel Campus.

Auch hier, wenn man bedenkt, dass die Daten wie unten gezeigt formatiert sind:

Hier sind die Schritte zum Konvertieren der Quelldaten in das Pivot-Table-fähige Format:

• Konvertieren Sie die Daten in eine Excel-Tabelle. Wählen Sie den Datensatz aus und gehen Sie zu Einfügen -> Tabellen -> Tabelle.
• Stellen Sie im Dialogfeld Tabelle einfügen sicher, dass der richtige Bereich ausgewählt ist, und klicken Sie auf OK. Dadurch werden die Tabellendaten in eine Excel-Tabelle umgewandelt.
• Gehen Sie in Excel 2016 zu Daten -> Abrufen und Transformieren -> Aus Tabelle.
  • Wenn Sie das Power Query-Add-In in einer früheren Version verwenden, wechseln Sie zu Power Query -> Externe Daten -> Aus Tabelle.
• Wählen Sie im Abfrageeditor die Spalten aus, die Sie entpivotieren möchten. In diesem Fall sind dies die für die vier Quartale. Um alle Spalten auszuwählen, halten Sie die Umschalttaste gedrückt und wählen Sie dann die erste Spalte und dann die letzte Spalte aus.
• Gehen Sie im Abfrage-Editor zu Transformieren -> Beliebige Spalte -> Unpivot-Spalten. Dadurch werden die Daten der Spalte in ein Pivot-Table-freundliches Format konvertiert.
• Power Query weist den Spalten generische Namen zu. Ändern Sie diese Namen in die gewünschten. Ändern Sie in diesem Fall Attribut in Quartal und Wert in Umsatz.
• Gehen Sie im Abfrage-Editor zu Datei -> Schließen und laden. Dadurch wird das Dialogfeld Power Query-Editor geschlossen und ein separates Arbeitsblatt erstellt, das die Daten mit nicht pivotierten Spalten enthält.

Nachdem Sie nun wissen, wie Sie die Quelldaten für Pivot-Tabellen vorbereiten, sind Sie bereit für Excel in der Welt der Pivot-Tabellen.

Hier sind einige andere Pivot-Tabellen-Tutorials, die Sie möglicherweise nützlich finden:

• So aktualisieren Sie die Pivot-Tabelle in Excel.
• Verwenden von Slicern in der Excel-Pivot-Tabelle - Ein Handbuch für Anfänger.
• So gruppieren Sie Datumsangaben in Pivot-Tabellen in Excel.
• So gruppieren Sie Zahlen in der Pivot-Tabelle in Excel.
• Pivot-Cache in Excel - Was ist das und wie wird es am besten verwendet?
• So filtern Sie Daten in einer Pivot-Tabelle in Excel.
• So fügen Sie ein berechnetes Feld für eine Excel-Pivot-Tabelle hinzu und verwenden es.
• So wenden Sie die bedingte Formatierung in einer Pivot-Tabelle in Excel an.
• So ersetzen Sie leere Zellen durch Nullen in Excel-Pivot-Tabellen.