Wenn Sie dieses Tutorial lesen, besteht eine große Chance, dass Sie von der Excel-Pivot-Tabelle gehört (oder sogar verwendet) haben. Es ist eine der leistungsstärksten Funktionen in Excel (kein Scherz).
Das Beste an der Verwendung einer Pivot-Tabelle ist, dass Sie mit einem sehr grundlegenden Verständnis davon immer noch ziemlich tolle Dinge damit machen können, auch wenn Sie nichts in Excel wissen.
Lass uns anfangen.
Hier klicken um die Beispieldaten herunterzuladen und mitzumachen.
Was ist eine Pivot-Tabelle und warum sollten Sie sich darum kümmern?
Eine Pivot-Tabelle ist ein Tool in Microsoft Excel, mit dem Sie riesige Datensätze schnell (mit wenigen Klicks) zusammenfassen können.
Auch wenn Sie absolut neu in der Welt von Excel sind, können Sie ganz einfach eine Pivot-Tabelle verwenden. Es ist so einfach wie das Ziehen und Ablegen von Zeilen-/Spaltenüberschriften, um Berichte zu erstellen.
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Dies sind Verkaufsdaten, die aus ~1000 Zeilen bestehen.
Es enthält die Verkaufsdaten nach Region, Einzelhändlertyp und Kunde.
Nun möchte Ihr Chef vielleicht ein paar Dinge aus diesen Daten wissen:
- Wie hoch war der Gesamtumsatz in der Region Süd im Jahr 2016?
- Was sind die fünf größten Einzelhändler nach Umsatz?
- Wie war die Leistung von The Home Depot im Vergleich zu anderen Einzelhändlern im Süden?
Sie können diese Fragen mit Hilfe von Excel-Funktionen beantworten, aber was ist, wenn Ihr Chef plötzlich eine Liste mit fünf weiteren Fragen hat.
Sie müssen zu den Daten zurückkehren und bei jeder Änderung neue Formeln erstellen.
Hier sind Excel-Pivot-Tabellen wirklich praktisch.
Innerhalb von Sekunden beantwortet eine Pivot-Tabelle all diese Fragen (wie Sie weiter unten erfahren werden).
Der eigentliche Vorteil besteht jedoch darin, dass es Ihrem wählerischen, datengesteuerten Chef entgegenkommt, indem seine Fragen sofort beantwortet werden.
Es ist so einfach, Sie können sich auch ein paar Minuten Zeit nehmen und Ihrem Chef zeigen, wie es selbst geht.
Hoffentlich haben Sie jetzt eine Vorstellung davon, warum Pivot-Tabellen so großartig sind. Lassen Sie uns mit dem Datensatz (siehe oben) eine Pivot-Tabelle erstellen.
Einfügen einer Pivot-Tabelle in Excel
Hier sind die Schritte zum Erstellen einer Pivot-Tabelle mit den oben gezeigten Daten:
- Klicken Sie auf eine beliebige Stelle im Datensatz.
- Gehen Sie zu Einfügen -> Tabellen -> Pivot-Tabelle.
- Im Dialogfeld Pivot-Tabelle erstellen funktionieren die Standardoptionen in den meisten Fällen einwandfrei. Hier sind ein paar Dinge, die Sie überprüfen können:
- Tabelle/Sortiment: Es wird standardmäßig basierend auf Ihrem Datensatz ausgefüllt. Wenn Ihre Daten keine leeren Zeilen/Spalten enthalten, ermittelt Excel automatisch den richtigen Bereich. Sie können dies bei Bedarf manuell ändern.
- Wenn Sie die Pivot-Tabelle an einem bestimmten Ort erstellen möchten, geben Sie unter der Option „Wählen Sie, wo der PivotTable-Bericht platziert werden soll“ den Ort an. Andernfalls wird mit der Pivot-Tabelle ein neues Arbeitsblatt erstellt.
- OK klicken.
Sobald Sie auf OK klicken, wird ein neues Arbeitsblatt mit der Pivot-Tabelle erstellt.
Während die Pivot-Tabelle erstellt wurde, sehen Sie darin keine Daten. Alles, was Sie sehen würden, ist der Name der Pivot-Tabelle und eine einzeilige Anweisung auf der linken Seite und Pivot-Tabellenfelder auf der rechten Seite.
Bevor wir nun mit der Analyse von Daten mit dieser Pivot-Tabelle beginnen, wollen wir verstehen, was die Muttern und Schrauben sind, die eine Excel-Pivot-Tabelle ausmachen.
Die Schrauben und Muttern einer Excel-Pivot-Tabelle
Um eine Pivot-Tabelle effizient zu verwenden, ist es wichtig, die Komponenten zu kennen, die eine Pivot-Tabelle erstellen.
In diesem Abschnitt erfahren Sie Folgendes:
- Pivot-Cache
- Wertebereich
- Zeilenbereich
- Spaltenbereich
- Filterbereich
Pivot-Cache
Sobald Sie mit den Daten eine Pivot-Tabelle erstellen, passiert etwas im Backend. Excel erstellt eine Momentaufnahme der Daten und speichert sie in seinem Speicher. Dieser Snapshot wird als Pivot-Cache bezeichnet.
Wenn Sie verschiedene Ansichten mit einer Pivot-Tabelle erstellen, kehrt Excel nicht zur Datenquelle zurück, sondern verwendet den Pivot-Cache, um die Daten schnell zu analysieren und Ihnen die Zusammenfassung/Ergebnisse anzuzeigen.
Der Grund, warum ein Pivot-Cache generiert wird, besteht darin, die Funktionsweise der Pivot-Tabelle zu optimieren. Selbst wenn Sie Tausende von Datenzeilen haben, fasst eine Pivot-Tabelle die Daten superschnell zusammen. Sie können Elemente per Drag & Drop in die Felder Zeilen/Spalten/Werte/Filter ziehen und die Ergebnisse werden sofort aktualisiert.
Hinweis: Ein Nachteil des Pivot-Cache besteht darin, dass er die Größe Ihrer Arbeitsmappe erhöht. Da es sich um ein Replikat der Quelldaten handelt, wird beim Erstellen einer Pivot-Tabelle eine Kopie dieser Daten im Pivot-Cache gespeichert.
Weiterlesen: Was ist Pivot-Cache und wie man ihn am besten verwendet.
Wertebereich
Der Wertebereich enthält die Berechnungen/Werte.
Basierend auf dem zu Beginn des Tutorials gezeigten Datensatz können Sie, wenn Sie schnell den Gesamtumsatz nach Region in jedem Monat berechnen möchten, eine Pivot-Tabelle wie unten gezeigt erhalten (wir werden später im Tutorial sehen, wie Sie diese erstellen). .
Der orange hervorgehobene Bereich ist der Wertebereich.
In diesem Beispiel enthält es den Gesamtumsatz in jedem Monat für die vier Regionen.
Zeilenbereich
Die Überschriften links neben dem Wertebereich bilden den Zeilenbereich.
Im folgenden Beispiel enthält der Bereich Zeilen die Regionen (rot hervorgehoben):
Spaltenbereich
Die Überschriften oben im Wertebereich machen den Spaltenbereich aus.
Im folgenden Beispiel enthält der Bereich Spalten die Monate (rot hervorgehoben):
Filterbereich
Der Bereich "Filter" ist ein optionaler Filter, mit dem Sie den Datensatz weiter aufschlüsseln können.
Wenn Sie beispielsweise nur die Verkäufe für Multiline-Händler sehen möchten, können Sie diese Option aus der Dropdown-Liste auswählen (in der Abbildung unten hervorgehoben), und die Pivot-Tabelle würde nur mit den Daten für Multiline-Händler aktualisiert.
Analysieren von Daten mithilfe der Pivot-Tabelle
Versuchen wir nun, die Fragen mithilfe der von uns erstellten Pivot-Tabelle zu beantworten.
Hier klicken um die Beispieldaten herunterzuladen und mitzumachen.
Um Daten mit einer Pivot-Tabelle zu analysieren, müssen Sie entscheiden, wie die Datenzusammenfassung im Endergebnis aussehen soll. Beispielsweise möchten Sie möglicherweise alle Regionen links und den Gesamtumsatz rechts daneben. Sobald Sie diese Klarheit im Hinterkopf haben, können Sie die relevanten Felder einfach per Drag & Drop in die Pivot-Tabelle ziehen.
Im Abschnitt Pivot-Tab-Felder haben Sie die Felder und die Bereiche (wie unten hervorgehoben):
Die Felder werden basierend auf den Back-End-Daten erstellt, die für die Pivot-Tabelle verwendet werden. Im Abschnitt Bereiche platzieren Sie die Felder, und je nachdem, wohin ein Feld geht, werden Ihre Daten in der Pivot-Tabelle aktualisiert.
Es ist ein einfacher Drag-and-Drop-Mechanismus, bei dem Sie einfach ein Feld ziehen und in einen der vier Bereiche legen können. Sobald Sie dies tun, wird es in der Pivot-Tabelle im Arbeitsblatt angezeigt.
Versuchen wir nun, die Fragen zu beantworten, die Ihr Vorgesetzter mit dieser Pivot-Tabelle hatte.
F1: Wie hoch war der Gesamtumsatz in der Region Süd?
Ziehen Sie das Feld Region in den Bereich Zeilen und das Feld Umsatz in den Bereich Werte. Es würde die Pivot-Tabelle im Arbeitsblatt automatisch aktualisieren.
Beachten Sie, dass, sobald Sie das Feld Umsatz im Bereich Werte ablegen, es zur Summe des Umsatzes wird. Standardmäßig summiert Excel alle Werte für eine bestimmte Region und zeigt die Summe an. Wenn Sie möchten, können Sie dies in Anzahl, Durchschnitt oder andere Statistikmetriken ändern. In diesem Fall ist die Summe das, was wir brauchten.
Die Antwort auf diese Frage wäre 21225800.
Q2 Welches sind die fünf größten Einzelhändler nach Umsatz?
Ziehen Sie das Feld Kunde in den Zeilenbereich und das Feld Umsatz in den Wertebereich. Falls sich im Bereichsabschnitt andere Felder befinden und Sie diese entfernen möchten, wählen Sie sie einfach aus und ziehen Sie sie heraus.
Sie erhalten eine Pivot-Tabelle wie unten gezeigt:
Beachten Sie, dass die Artikel (in diesem Fall die Kunden) standardmäßig in alphabetischer Reihenfolge sortiert sind.
Um die Top-5-Händler zu erhalten, können Sie diese Liste einfach sortieren und die Top-5-Kundennamen verwenden. Um dies zu tun:
- Klicken Sie mit der rechten Maustaste auf eine beliebige Zelle im Bereich Werte.
- Gehen Sie zu Sortieren -> Sortieren vom Größten zum Kleinsten.
Dadurch erhalten Sie eine sortierte Liste basierend auf dem Gesamtumsatz.
F3: Wie war die Leistung von The Home Depot im Vergleich zu anderen Einzelhändlern im Süden?
Sie können für diese Frage viele Analysen durchführen, aber hier versuchen wir einfach, die Verkäufe zu vergleichen.
Ziehen Sie das Regionsfeld in den Zeilenbereich. Ziehen Sie nun das Feld Kunde in den Bereich Zeilen unter das Feld Region. Wenn Sie dies tun, würde Excel verstehen, dass Sie Ihre Daten zuerst nach Region und dann nach Kunden innerhalb der Regionen kategorisieren möchten. Sie haben etwas wie unten gezeigt:
Ziehen Sie nun das Feld Umsatz in den Bereich Werte und Sie haben die Verkäufe für jeden Kunden (sowie die Gesamtregion).
Sie können die Händler anhand der Verkaufszahlen sortieren, indem Sie die folgenden Schritte ausführen:
- Klicken Sie mit der rechten Maustaste auf eine Zelle, die den Verkaufswert für einen beliebigen Einzelhändler enthält.
- Gehen Sie zu Sortieren -> Sortieren vom Größten zum Kleinsten.
Dies würde sofort alle Einzelhändler nach dem Verkaufswert sortieren.
Jetzt können Sie schnell die Region Süd durchsuchen und feststellen, dass The Home Depot 3004600 Verkäufe betrug und damit besser abgeschnitten hat als vier Einzelhändler in der Region Süd.
Jetzt gibt es mehr als eine Möglichkeit, die Katze zu häuten. Sie können auch die Region in den Filterbereich legen und dann nur die Südregion auswählen.
Hier klicken um die Beispieldaten herunterzuladen.
Ich hoffe, dieses Tutorial gibt Ihnen einen grundlegenden Überblick über Excel-Pivot-Tabellen und hilft Ihnen beim Einstieg.
Hier sind einige weitere Pivot-Tabellen-Tutorials, die Ihnen gefallen könnten:
- Vorbereiten von Quelldaten für Pivot-Tabelle.
- So wenden Sie die bedingte Formatierung in einer Pivot-Tabelle in Excel an.
- So gruppieren Sie Datumsangaben in Pivot-Tabellen in Excel.
- So gruppieren Sie Zahlen in der Pivot-Tabelle in Excel.
- So filtern Sie Daten in einer Pivot-Tabelle in Excel.
- Verwenden von Slicern in der Excel-Pivot-Tabelle.
- So ersetzen Sie leere Zellen durch Nullen in Excel-Pivot-Tabellen.
- So fügen Sie eine berechnete Excel-Pivot-Tabelle hinzu und verwenden sie.
- So aktualisieren Sie die Pivot-Tabelle in Excel.
