Inhalte
Pivot-Tabellen ist eines der leistungsstärksten Tools in Excel. Sie ermöglichen es Ihnen, große Datenmengen mit wenigen Mausklicks unterschiedlich zu analysieren und zusammenzufassen. In diesem Artikel lernen wir Pivot-Tabellen kennen, verstehen, was sie sind, und lernen, wie man sie erstellt und anpasst.
Dieser Artikel wurde mit Excel 2010 geschrieben. Das Konzept von PivotTables hat sich im Laufe der Jahre nicht wesentlich geändert, aber die Art und Weise, wie Sie sie erstellen, ist in jeder neuen Version von Excel etwas anders. Wenn Sie eine Version von Excel haben, die nicht 2010 ist, dann seien Sie darauf vorbereitet, dass die Screenshots in diesem Artikel von dem abweichen, was Sie auf Ihrem Bildschirm sehen.
Ein wenig Geschichte
In den frühen Tagen der Tabellenkalkulationssoftware der Regelball Lotus 1-2-3. Seine Dominanz war so vollständig, dass Microsofts Bemühungen, eine eigene Software (Excel) als Alternative zu Lotus zu entwickeln, wie Zeitverschwendung erschienen. Jetzt schnell vorwärts ins Jahr 2010! Excel dominiert Tabellenkalkulationen mehr als Lotus-Code es jemals in seiner Geschichte getan hat, und die Zahl der Menschen, die Lotus immer noch verwenden, geht gegen Null. Wie konnte das passieren? Was war der Grund für diese dramatische Wendung der Ereignisse?
Analysten identifizieren zwei Hauptfaktoren:
- Zuerst entschied Lotus, dass diese neumodische GUI-Plattform namens Windows nur eine vorübergehende Modeerscheinung war, die nicht lange anhalten würde. Sie weigerten sich, eine Windows-Version von Lotus 1-2-3 zu bauen (allerdings nur für ein paar Jahre) und sagten voraus, dass die DOS-Version ihrer Software alles sein würde, was die Verbraucher jemals brauchen würden. Microsoft hat Excel natürlich speziell für Windows entwickelt.
- Zweitens führte Microsoft in Excel ein Tool namens PivotTables ein, das in Lotus 1-2-3 nicht verfügbar war. PivotTables, exklusiv für Excel, erwiesen sich als so überwältigend nützlich, dass die Leute dazu neigten, bei der neuen Excel-Software-Suite zu bleiben, anstatt mit Lotus 1-2-3 fortzufahren, das sie nicht hatte.
PivotTables spielten zusammen mit der Unterschätzung des Erfolgs von Windows im Allgemeinen den Todesmarsch für Lotus 1-2-3 und leiteten den Erfolg von Microsoft Excel ein.
Was sind Pivot-Tabellen?
Was ist also der beste Weg, um zu charakterisieren, was PivotTables sind?
Einfach ausgedrückt sind Pivot-Tabellen Zusammenfassungen einiger Daten, die erstellt wurden, um die Analyse dieser Daten zu erleichtern. Im Gegensatz zu manuell erstellten Summen sind Excel-PivotTables interaktiv. Einmal erstellt, können Sie sie leicht ändern, wenn sie nicht das gewünschte Bild liefern. Mit nur wenigen Mausklicks können Summen umgedreht werden, sodass Spaltenüberschriften zu Zeilenüberschriften werden und umgekehrt. Mit Pivot-Tabellen können Sie viele Dinge tun. Anstatt zu versuchen, alle Funktionen von Pivot-Tabellen in Worten zu beschreiben, ist es einfacher, sie in der Praxis zu demonstrieren …
Die Daten, die Sie mit PivotTables analysieren, dürfen nicht zufällig sein. Es sollten Rohdaten sein, wie eine Art Liste. Beispielsweise könnte es sich um eine Liste der Verkäufe handeln, die das Unternehmen in den letzten sechs Monaten getätigt hat.
Betrachten Sie die in der folgenden Abbildung gezeigten Daten:
Beachten Sie, dass dies keine Rohdaten sind, da sie bereits zusammengefasst wurden. In Zelle B3 sehen wir 30000 $, was wahrscheinlich das Gesamtergebnis ist, das James Cook im Januar erzielt hat. Wo sind denn die Originaldaten? Woher kommt die Zahl von 30000 Dollar? Wo ist die ursprüngliche Verkaufsliste, aus der diese monatliche Gesamtsumme abgeleitet wurde? Es ist klar, dass jemand großartige Arbeit geleistet hat, um alle Verkaufsdaten der letzten sechs Monate zu organisieren und zu sortieren und sie in die Gesamttabelle umzuwandeln, die wir sehen. Was denken Sie, wie lange es gedauert hat? Stunde? Zehn Uhr?
Tatsache ist, dass die obige Tabelle keine Pivot-Tabelle ist. Es wurde aus an anderer Stelle gespeicherten Rohdaten handgefertigt und die Verarbeitung dauerte mindestens ein paar Stunden. Eine solche Übersichtstabelle lässt sich mit Hilfe von Pivot-Tabellen in wenigen Sekunden erstellen. Lassen Sie uns herausfinden, wie …
Wenn wir auf die ursprüngliche Verkaufsliste zurückgehen, würde sie in etwa so aussehen:
Sie werden überrascht sein, dass wir aus dieser Liste von Trades mit Hilfe von Pivot-Tabellen und in nur wenigen Sekunden einen monatlichen Umsatzbericht in Excel erstellen können, den wir oben analysiert haben. Ja, das und mehr können wir!
Wie erstelle ich eine Pivot-Tabelle?
Stellen Sie zunächst sicher, dass Sie einige Quelldaten in einer Excel-Tabelle haben. Die Liste der Finanztransaktionen ist die typischste, die auftritt. Dabei kann es sich um eine Liste mit allem Möglichen handeln: Kontaktdaten von Mitarbeitern, eine CD-Sammlung oder die Kraftstoffverbrauchsdaten Ihres Unternehmens.
Also starten wir Excel … und laden so eine Liste …
Nachdem wir diese Liste in Excel geöffnet haben, können wir mit der Erstellung einer Pivot-Tabelle beginnen.
Wählen Sie eine beliebige Zelle aus dieser Liste aus:
Dann auf der Registerkarte Einfügen (Einfügen) Auswahlbefehl PivotTabelle (Pivot-Tabelle):
Ein Dialogfeld wird angezeigt PivotTable erstellen (Erstellen einer Pivot-Tabelle) mit zwei Fragen an Sie:
- Welche Daten zum Erstellen einer neuen Pivot-Tabelle verwenden?
- Wohin mit der Pivot-Tabelle?
Da wir im vorherigen Schritt bereits eine der Listenzellen ausgewählt haben, wird automatisch die gesamte Liste ausgewählt, um eine Pivot-Tabelle zu erstellen. Beachten Sie, dass wir einen anderen Bereich, eine andere Tabelle und sogar eine externe Datenquelle wie eine Access- oder MS-SQL-Datenbanktabelle auswählen können. Außerdem müssen wir auswählen, wo die neue Pivot-Tabelle platziert werden soll: auf einem neuen Blatt oder auf einem der vorhandenen. In diesem Beispiel wählen wir die Option – Neues Arbeitsblatt (zu einem neuen Blatt):
Excel erstellt ein neues Blatt und platziert eine leere Pivot-Tabelle darauf:
Sobald wir auf eine beliebige Zelle in der Pivot-Tabelle klicken, erscheint ein weiteres Dialogfeld: PivotTable-Feldliste (Pivot-Tabellenfelder).
Die Liste der Felder oben im Dialogfeld ist eine Liste aller Titel aus der ursprünglichen Liste. Mit den vier leeren Bereichen am unteren Bildschirmrand können Sie der PivotTable mitteilen, wie Sie die Daten zusammenfassen möchten. Solange diese Bereiche leer sind, steht auch nichts in der Tabelle. Alles, was wir tun müssen, ist, die Überschriften aus dem oberen Bereich in die leeren Bereiche darunter zu ziehen. Gleichzeitig wird gemäß unserer Anleitung automatisch eine Pivot-Tabelle generiert. Wenn wir einen Fehler machen, können wir die Überschriften aus dem unteren Bereich entfernen oder andere ziehen, um sie zu ersetzen.
Gebiet Werte (Bedeutungen) ist wahrscheinlich die wichtigste der vier. Welche Überschrift in diesem Bereich platziert wird, bestimmt, welche Daten zusammengefasst werden (Summe, Durchschnitt, Maximum, Minimum usw.). Dies sind fast immer Zahlenwerte. Ein ausgezeichneter Kandidat für einen Platz in diesem Bereich sind die Daten unter der Überschrift Summe (Kosten) unserer Originaltabelle. Ziehen Sie diesen Titel in den Bereich Werte (Werte):
Bitte beachten Sie, dass der Titel Summe ist jetzt mit einem Häkchen markiert und im Bereich Werte (Werte) ein Eintrag ist erschienen Summe des Betrags (Betragsfeld Betrag), was darauf hinweist, dass die Spalte Summe zusammengefaßt.
Wenn wir uns die Pivot-Tabelle selbst ansehen, sehen wir die Summe aller Werte aus der Spalte Summe Originaltabelle.
Unsere erste Pivot-Tabelle ist also erstellt! Praktisch, aber nicht besonders beeindruckend. Wir möchten wahrscheinlich mehr Informationen über unsere Daten erhalten, als wir derzeit haben.
Wenden wir uns den Originaldaten zu und versuchen, eine oder mehrere Spalten zu identifizieren, die zum Aufteilen dieser Summe verwendet werden können. Wir können unsere Pivot-Tabelle beispielsweise so gestalten, dass die Gesamtsumme der Verkäufe für jeden Verkäufer einzeln berechnet wird. Diese. Zeilen mit dem Namen jedes Verkäufers im Unternehmen und seinem Gesamtumsatz werden zu unserer Pivot-Tabelle hinzugefügt. Um dieses Ergebnis zu erzielen, ziehen Sie einfach den Titel Verkäufer (Vertriebsmitarbeiter) in die Region Zeilenbeschriftungen (Streicher):
Es wird interessanter! Unser PivotTable nimmt Gestalt an…
Sehen Sie die Vorteile? Mit ein paar Klicks haben wir eine Tabelle erstellt, deren manuelle Erstellung sehr lange gedauert hätte.
Was können wir sonst noch tun? Nun, in gewissem Sinne ist unsere Pivot-Tabelle fertig. Wir haben eine nützliche Zusammenfassung der Originaldaten erstellt. Wichtige Information bereits erhalten! Im Rest dieses Artikels sehen wir uns einige Möglichkeiten zum Erstellen komplexerer PivotTables an und erfahren, wie Sie sie anpassen können.
PivotTable-Setup
Zunächst können wir eine zweidimensionale Pivot-Tabelle erstellen. Lassen Sie uns dies anhand der Spaltenüberschrift tun Bezahlverfahren (Zahlungsmethode). Ziehen Sie einfach den Titel Bezahlverfahren zum Gebiet Spaltenbeschriftungen (Säulen):
Wir erhalten das Ergebnis:
Sieht sehr cool aus!
Lassen Sie uns nun eine dreidimensionale Tabelle erstellen. Wie würde eine solche Tabelle aussehen? Mal schauen…
Kopfzeile ziehen Paket (Komplex) in die Gegend Berichtsfilter (Filter):
Merken Sie sich, wo er ist …
Dies gibt uns die Möglichkeit, den Bericht nach „Welche Ferienanlage wurde bezahlt“ zu filtern. Beispielsweise können wir eine Aufschlüsselung nach Verkäufern und Zahlungsmethoden für alle Komplexe anzeigen oder mit ein paar Mausklicks die Ansicht der Pivot-Tabelle ändern und dieselbe Aufschlüsselung nur für diejenigen anzeigen, die den Komplex bestellt haben Sonnenhungrige.
Also, wenn Sie das richtig verstehen, dann kann unsere Pivot-Tabelle als dreidimensional bezeichnet werden. Fahren wir mit der Einrichtung fort…
Wenn sich plötzlich herausstellt, dass in der Pivot-Tabelle nur die Zahlung per Scheck und Kreditkarte (also bargeldlose Zahlung) angezeigt werden soll, dann können wir die Anzeige des Titels abschalten Bargeld (Kasse). Dazu neben Spaltenbeschriftungen Klicken Sie auf den Abwärtspfeil und deaktivieren Sie das Kontrollkästchen im Dropdown-Menü Bargeld:
Mal sehen, wie unsere Pivot-Tabelle jetzt aussieht. Wie Sie sehen können, die Spalte Bargeld von ihr verschwunden.
PivotTables in Excel formatieren
PivotTables sind offensichtlich ein sehr mächtiges Werkzeug, aber bisher sehen die Ergebnisse ein wenig schlicht und langweilig aus. Zum Beispiel sehen die Zahlen, die wir addieren, nicht wie Dollarbeträge aus – es sind nur Zahlen. Lassen Sie uns das beheben.
Es ist verlockend, in einer solchen Situation das zu tun, was Sie gewohnt sind, und einfach die gesamte Tabelle (oder das gesamte Blatt) auszuwählen und die Standardschaltflächen für die Zahlenformatierung in der Symbolleiste zu verwenden, um das gewünschte Format festzulegen. Das Problem bei diesem Ansatz ist, dass, wenn Sie jemals die Struktur der Pivot-Tabelle in der Zukunft ändern (was mit einer Wahrscheinlichkeit von 99% der Fall ist), die Formatierung verloren geht. Was wir brauchen, ist eine Möglichkeit, es (fast) dauerhaft zu machen.
Zuerst suchen wir den Eintrag Summe des Betrags in Werte (Werte) und klicken Sie darauf. Wählen Sie im angezeigten Menü das Element aus Wertefeldeinstellungen (Wertfeldoptionen):
Ein Dialogfeld wird angezeigt Wertefeldeinstellungen (Wertfeldoptionen).
Presse Number Format (Zahlenformat) öffnet sich ein Dialogfenster. Format Cells (Zellformat):
Von der Liste Kategorie (Zahlenformate) auswählen Buchhaltung (Finanzen) und setzen Sie die Anzahl der Dezimalstellen auf Null. Drücken Sie nun ein paar Mal OKum zu unserer Pivot-Tabelle zurückzukehren.
Wie Sie sehen können, sind die Zahlen als Dollarbeträge formatiert.
Wenn wir schon bei der Formatierung sind, richten wir das Format für die gesamte PivotTable ein. Dazu gibt es mehrere Möglichkeiten. Wir verwenden die einfachere …
Klicken Sie auf die PivotTable-Tools: Design (Arbeiten mit PivotTables: Konstruktor):
Erweitern Sie als Nächstes das Menü, indem Sie auf den Pfeil in der unteren rechten Ecke des Abschnitts klicken PivotTable-Stile (PivotTable-Stile), um die umfangreiche Sammlung von Inline-Stilen anzuzeigen:
Wählen Sie einen geeigneten Stil und sehen Sie sich das Ergebnis in Ihrer Pivot-Tabelle an:
Andere PivotTable-Einstellungen in Excel
Manchmal müssen Sie Daten nach Datum filtern. Zum Beispiel gibt es in unserer Liste der Trades viele, viele Daten. Excel bietet ein Tool zum Gruppieren von Daten nach Tag, Monat, Jahr usw. Mal sehen, wie es gemacht wird.
Entfernen Sie zuerst den Eintrag. Bezahlverfahren aus der Region Spaltenbeschriftungen (Säulen). Ziehen Sie ihn dazu zurück in die Titelliste und verschieben Sie an seiner Stelle den Titel Datum gebucht (Datum der Buchung):
Wie Sie sehen können, hat dies unsere Pivot-Tabelle vorübergehend unbrauchbar gemacht. Excel hat für jedes Datum, an dem ein Handel getätigt wurde, eine separate Spalte erstellt. Als Ergebnis haben wir einen sehr breiten Tisch bekommen!
Um dies zu beheben, klicken Sie mit der rechten Maustaste auf ein beliebiges Datum und wählen Sie aus dem Kontextmenü (Gruppe):
Das Gruppierungsdialogfeld wird angezeigt. Wir wählen Monate (Monate) und klicken Sie auf OK:
Voila! Diese Tabelle ist viel nützlicher:
Diese Tabelle ist übrigens fast identisch mit der am Anfang des Artikels gezeigten, wo die Verkaufssummen manuell zusammengestellt wurden.
Es gibt noch einen weiteren sehr wichtigen Punkt, den Sie wissen müssen! Sie können nicht nur eine, sondern mehrere Ebenen von Zeilen- (oder Spalten-) Überschriften erstellen:
… und so wird es aussehen …
Das gleiche kann mit Spaltenüberschriften (oder sogar Filtern) gemacht werden.
Lassen Sie uns zur ursprünglichen Form der Tabelle zurückkehren und sehen, wie man Durchschnittswerte anstelle von Summen anzeigt.
Um loszulegen, klicken Sie auf Summe des Betrags und aus dem erscheinenden Menü auswählen Wertefeldeinstellungen (Wertfeldoptionen):
Die Liste Wertefeld zusammenfassen mit (Operation) im Dialogfeld Wertefeldeinstellungen (Wertfeldoptionen) auswählen Durchschnittlich (Durchschnitt):
Zur gleichen Zeit, während wir hier sind, lasst uns ändern Benutzerdefinierter Name (Benutzerdefinierter Name) mit Durchschnitt des Betrags (Betragsfeld Betrag) zu etwas Kürzerem. Geben Sie in dieses Feld so etwas wie ein Durchschn.:
Öffentlichkeitsarbeit/Presse OK und sehen was passiert. Beachten Sie, dass sich alle Werte von Gesamtsummen zu Durchschnittswerten geändert haben und sich die Tabellenüberschrift (in der oberen linken Zelle) zu geändert hat Durchschn.:
Wenn Sie möchten, können Sie sofort den Betrag, den Durchschnitt und die Anzahl (Verkäufe) in einer Pivot-Tabelle platzieren.
Hier ist eine Schritt-für-Schritt-Anleitung dazu, beginnend mit einer leeren Pivot-Tabelle:
- Kopfzeile ziehen Verkäufer (Vertriebsmitarbeiter) in die Region Spaltenbeschriftungen (Säulen).
- Ziehen Sie den Titel dreimal Summe (Kosten) zu Bereich Werte (Werte).
- Für das erste Feld Summe ändern Sie den Titel in Gesamt (Betrag) und das Zahlenformat in diesem Feld ist Buchhaltung (Finanzen). Die Anzahl der Nachkommastellen ist Null.
- Zweites Feld Summe Name Durchschnitte, stellen Sie die Operation dafür ein Durchschnittlich (Durchschnitt) und das Zahlenformat in diesem Feld ändern sich ebenfalls zu Buchhaltung (Finanzen) mit null Dezimalstellen.
- Für das dritte Feld Summe setze einen titel Zu Zählen und eine Operation für ihn – Zu Zählen (Menge)
- Im Spaltenbeschriftungen (Spalten)-Feld automatisch erstellt Σ-Werte (Σ Werte) – ziehen Sie es in den Bereich Zeilenbeschriftungen (Linien)
Hier ist, was wir am Ende haben werden:
Gesamtbetrag, Durchschnittswert und Anzahl der Verkäufe – alles in einer Pivot-Tabelle!
Zusammenfassung
Pivot-Tabellen in Microsoft Excel enthalten viele Funktionen und Einstellungen. In einem so kleinen Artikel sind sie nicht einmal annähernd, um sie alle abzudecken. Es bräuchte ein kleines Buch oder eine große Website, um alle Möglichkeiten von Pivot-Tabellen vollständig zu beschreiben. Mutige und neugierige Leser können ihre Erkundung von Pivot-Tabellen fortsetzen. Klicken Sie dazu einfach mit der rechten Maustaste auf fast jedes Element der Pivot-Tabelle und sehen Sie, welche Funktionen und Einstellungen sich öffnen. Auf der Multifunktionsleiste finden Sie zwei Registerkarten: PivotTable-Tools: Optionen (Analyse) und Design (Konstrukteur). Scheuen Sie sich nicht, einen Fehler zu machen, Sie können die PivotTable jederzeit löschen und neu beginnen. Sie haben eine Gelegenheit, die langjährige Benutzer von DOS und Lotus 1-2-3 nie hatten.