Arbeiten mit Pivot-Tabellen in Microsoft Excel

PivotTables sind eine der leistungsfähigsten Funktionen von Microsoft Excel. Sie ermöglichen die Analyse und Zusammenfassung großer Datenmengen mit nur wenigen Mausklicks. In diesem Artikel untersuchen wir PivotTables, verstehen deren Eigenschaften und erfahren, wie sie erstellt und angepasst werden.

Hinweis: Dieser Artikel wurde mit Excel 2010 (Beta) geschrieben. Das Konzept einer PivotTable hat sich im Laufe der Jahre wenig geändert, aber die Methode der Erstellung eines PivotTables hat sich in fast jeder Excel-Iteration geändert. Wenn Sie eine Version von Excel verwenden, die nicht 2010 ist, erwarten Sie andere Bildschirme als in diesem Artikel.

Eine kleine Geschichte

In den Anfängen von Tabellenkalkulationsprogrammen beherrschte Lotus 1-2-3 den Schlafplatz. Seine Dominanz war so umfassend, dass die Leute glaubten, es sei Zeitverschwendung für Microsoft, sich die Entwicklung ihrer eigenen Spreadsheet-Software (Excel) zu nehmen, um mit Lotus konkurrieren zu können. Flash-Forward auf 2010 und die Dominanz von Excel im Spreadsheet-Markt ist größer als bei Lotus, während die Anzahl der Benutzer, die noch Lotus 1-2-3 verwenden, gegen null geht. Wie ist es passiert? Was hat zu einer derart dramatischen Umkehrung des Vermögens geführt??

Branchenanalysten lassen sich auf zwei Faktoren zurückführen: Erstens entschied Lotus, dass diese schicke neue GUI-Plattform mit dem Namen „Windows“ eine Modeerscheinung war, die niemals abnehmen würde. Sie lehnten es ab, eine Windows-Version von Lotus 1-2-3 zu erstellen (zumindest für einige Jahre), da sie voraussagten, dass ihre DOS-Version alles war, was jeder jemals brauchen würde. Microsoft hat Excel natürlich ausschließlich für Windows entwickelt. Zweitens hat Microsoft eine Funktion für Excel entwickelt, die Lotus in 1-2-3 nicht bereitgestellt hat, nämlich PivotTables. Die PivotTables-Funktion, die exklusiv für Excel verfügbar ist, wurde als so erstaunlich nützlich erachtet, dass die Leute bereit waren, ein ganz neues Softwarepaket (Excel) zu lernen, anstatt mit einem Programm (1-2-3) zu bleiben, das es nicht hatte. Zusammen mit der falschen Einschätzung des Erfolgs von Windows war dies die Todesstrafe für Lotus 1-2-3 und der Beginn des Erfolgs von Microsoft Excel.

PivotTables verstehen

Was genau ist ein PivotTable??

Vereinfacht gesagt, ist ein PivotTable eine Zusammenfassung einiger Daten, die erstellt wurde, um eine einfache Analyse dieser Daten zu ermöglichen. Im Gegensatz zu einer manuell erstellten Zusammenfassung sind Excel-PivotTables jedoch interaktiv. Sobald Sie eine erstellt haben, können Sie sie leicht ändern, wenn sie nicht genau die Einblicke in Ihre Daten bietet, auf die Sie sich geeinigt haben. Mit ein paar Klicks kann die Zusammenfassung "geschwenkt" werden - so gedreht, dass die Spaltenüberschriften zu Zeilenüberschriften werden und umgekehrt. Es gibt noch viel mehr Möglichkeiten. Anstatt zu versuchen, alle Funktionen von PivotTables zu beschreiben, werden wir sie einfach demonstrieren.

Die Daten, die Sie mit einem PivotTable analysieren, können nicht einfach sein irgendein Daten - es muss sein roh Daten, die zuvor nicht verarbeitet wurden (nicht zusammengefasst) - normalerweise eine Liste irgendeiner Art. Ein Beispiel hierfür ist die Liste der Verkaufstransaktionen in einem Unternehmen für die letzten sechs Monate.

Überprüfen Sie die unten gezeigten Daten:

Beachten Sie, dass dies ist nicht Rohdaten. In der Tat ist es bereits eine Zusammenfassung. In Zelle B3 sehen wir 30.000 Dollar, was anscheinend der Gesamtumsatz von James Cook für den Monat Januar ist. Wo sind also die Rohdaten? Wie sind wir zu 30.000 Dollar gekommen? Wo ist die ursprüngliche Liste der Verkaufstransaktionen, aus denen diese Zahl generiert wurde? Es ist klar, dass sich irgendjemand irgendjemand die Mühe gemacht hat, alle Verkaufstransaktionen der letzten sechs Monate in der oben gezeigten Zusammenfassung zusammenzufassen. Wie lange dauern Sie wohl? Eine Stunde? Zehn?

Höchstwahrscheinlich ja. Sie sehen, die obige Tabelle ist eigentlich nicht eine PivotTable. Es wurde manuell aus Rohdaten erstellt, die an anderer Stelle gespeichert wurden, und das Kompilieren dauerte tatsächlich einige Stunden. Es ist jedoch genau die Art von Zusammenfassung könnte mit PivotTables erstellt werden, in diesem Fall hätte es nur wenige Sekunden gedauert. Lass uns herausfinden, wie…

Wenn wir die ursprüngliche Liste der Verkaufstransaktionen aufspüren würden, könnte dies ungefähr so aussehen:

Sie werden überrascht sein zu erfahren, dass wir mit der PivotTable-Funktion von Excel in wenigen Sekunden mit nur wenigen Mausklicks eine monatliche Umsatzzusammenfassung erstellen können, die der obigen oben ähnlich ist. Wir können das - und noch viel mehr!

So erstellen Sie eine PivotTable

Stellen Sie zunächst sicher, dass Sie einige Rohdaten in einem Arbeitsblatt in Excel haben. Eine Liste von Finanztransaktionen ist typisch, aber es kann eine Liste von fast allem sein: Angaben zu den Mitarbeitern, Ihre CD-Sammlung oder Angaben zum Kraftstoffverbrauch für die Fahrzeugflotte Ihres Unternehmens.

Also starten wir Excel… und laden eine solche Liste…

Sobald die Liste in Excel geöffnet ist, können Sie mit der Erstellung der PivotTable beginnen.

Klicken Sie auf eine einzelne Zelle in der Liste:

Dann aus dem Einfügen Klicken Sie auf die Registerkarte PivotTable Symbol:

Das Erstellen Sie PivotTable In einem Feld werden zwei Fragen gestellt: Auf welchen Daten sollte Ihr neues PivotTable basieren und wo sollten sie erstellt werden? Da wir bereits auf eine Zelle in der Liste geklickt haben (im obigen Schritt), ist die gesamte Liste, die diese Zelle umgibt, bereits für uns ausgewählt ($ A $ 1: $ G $ 88 auf der Zahlungen in diesem Beispiel). Beachten Sie, dass wir eine Liste in einem anderen Bereich eines anderen Arbeitsblatts oder sogar einer externen Datenquelle auswählen können, z. B. einer Access-Datenbanktabelle oder sogar einer MS-SQL Server-Datenbanktabelle. Wir müssen auch auswählen, ob unsere neue PivotTable auf einem erstellt werden soll Neu Arbeitsblatt oder auf einem bestehender ein. In diesem Beispiel wählen wir a Neu ein:

Das neue Arbeitsblatt wird für uns erstellt, und in diesem Arbeitsblatt wird eine leere PivotTable erstellt:

Eine weitere Box erscheint: Die PivotTable-Feldliste. Diese Feldliste wird angezeigt, wenn wir auf eine Zelle in der PivotTable (oben) klicken:

Bei der Liste der Felder im oberen Teil des Felds handelt es sich eigentlich um die Auflistung der Spaltenüberschriften aus dem ursprünglichen Rohdaten-Arbeitsblatt. Mit den vier leeren Feldern im unteren Teil des Bildschirms können wir auswählen, wie unser PivotTable die Rohdaten zusammenfassen soll. Bisher ist nichts in diesen Feldern, daher ist die PivotTable leer. Alles, was wir tun müssen, ist, Felder aus der obigen Liste nach unten zu ziehen und sie in die unteren Felder zu legen. Dann wird automatisch eine PivotTable erstellt, die unseren Anweisungen entspricht. Wenn wir es falsch verstehen, müssen wir nur die Felder zu ihrem Ursprungsort ziehen und / oder ziehen Neu Felder nach unten, um sie zu ersetzen.

Das Werte Box ist wohl die wichtigste der vier. Das Feld, das in dieses Feld gezogen wird, stellt die Daten dar, die auf irgendeine Weise zusammengefasst werden müssen (durch Summieren, Mitteln, Ermitteln des Maximums, Minimums usw.). Es ist fast immer so numerisch Daten. Ein perfekter Kandidat für dieses Feld in unseren Beispieldaten ist das Feld / die Spalte "Betrag". Ziehen wir das Feld in die Werte Box:

Beachten Sie, dass (a) das Feld "Betrag" in der Liste der Felder jetzt markiert ist und "Summe des Betrags" hinzugefügt wurde Werte Das Feld zeigt an, dass die Betragsspalte summiert wurde.

Wenn wir die PivotTable selbst untersuchen, ermitteln wir tatsächlich die Summe aller "Amount" -Werte aus dem Rohdaten-Arbeitsblatt:

Wir haben unsere erste PivotTable erstellt! Praktisch, aber nicht besonders beeindruckend. Wahrscheinlich benötigen wir etwas mehr Einblick in unsere Daten.

In Bezug auf unsere Beispieldaten müssen wir eine oder mehrere Spaltenüberschriften identifizieren, mit denen wir diese Summe möglicherweise aufteilen könnten. Zum Beispiel können wir entscheiden, dass wir eine Zusammenfassung unserer Daten wünschen, wenn wir eine haben Zeilenüberschrift für jeden der verschiedenen Verkäufer in unserem Unternehmen und eine Summe für jeden. Um dies zu erreichen, müssen Sie lediglich das Feld „Verkäufer“ in das Feld ziehen Zeilenbeschriftungen Box:

Jetzt, Endlich werden die Dinge interessant! Unser PivotTable nimmt Gestalt an… .

Mit ein paar Klicks haben wir eine Tabelle erstellt, deren manuelle Bearbeitung viel Zeit in Anspruch genommen hätte.

Was können wir sonst noch tun? In gewissem Sinne ist unsere PivotTable vollständig. Wir haben eine nützliche Zusammenfassung unserer Quelldaten erstellt. Das Wichtige ist bereits gelernt! Für den Rest des Artikels werden wir einige Möglichkeiten untersuchen, wie komplexere PivotTables erstellt werden können, und Möglichkeiten, wie diese PivotTables angepasst werden können.

Zuerst können wir eine erstellen zwei-Maßtabelle. Lassen Sie uns dies tun, indem Sie "Zahlungsmethode" als Spaltenüberschrift verwenden. Ziehen Sie einfach die Überschrift "Zahlungsmethode" auf die Spaltenbeschriftungen Box:

Was sieht so aus:

Beginnen zu bekommen sehr cool!

Lass es uns zu einem machen drei-Maßtabelle. Wie könnte so ein Tisch aussehen? Okay, lass uns nachsehen…

Ziehen Sie die Spalte "Paket" auf die Berichtsfilter Box:

Beachten Sie, wo es endet… .

Dies ermöglicht uns, unseren Bericht nach dem "Urlaubspaket" zu filtern. Wir können beispielsweise die Aufteilung der Verkäufer nach Zahlungsmethoden für sehen alles Pakete, oder ändern Sie sie mit ein paar Klicks, um die gleiche Aufteilung für das „Sunseekers“ -Paket anzuzeigen:

Wenn Sie also richtig darüber nachdenken, ist unser PivotTable jetzt dreidimensional. Lass uns weiter anpassen…

Wenn es sich herausstellt, sagen wir, dass wir nur sehen wollen Scheck und Kreditkarte Transaktionen (d. h. keine Bargeldtransaktionen), dann können wir den Eintrag "Cash" aus den Spaltenüberschriften abwählen. Klicken Sie auf den Dropdown-Pfeil neben Spaltenbeschriftungen, und deaktivieren Sie "Cash":

Mal sehen, wie das aussieht… Wie Sie sehen, ist „Cash“ weg.

Formatierung

Dies ist offensichtlich ein sehr leistungsfähiges System, aber die Ergebnisse sehen bisher sehr einfach und langweilig aus. Zum einen sehen die Zahlen, die wir summieren, nicht wie Dollarbeträge aus, sondern einfach alte Zahlen. Lasst uns das korrigieren.

Eine Versuchung könnte darin bestehen, das zu tun, was wir unter solchen Umständen gewohnt sind. Wählen Sie einfach die gesamte Tabelle (oder das gesamte Arbeitsblatt) aus und verwenden Sie die Standard-Zahlenformatierungsschaltflächen in der Symbolleiste, um die Formatierung abzuschließen. Das Problem bei diesem Ansatz besteht darin, dass diese Zahlenformate verloren gehen, wenn Sie in der Zukunft die Struktur der PivotTable ändern (was zu 99% wahrscheinlich ist). Wir brauchen einen Weg, um sie (halb) dauerhaft zu machen.

Zuerst finden wir den Eintrag "Summenbetrag" im Werte und klicken Sie darauf. Ein Menü erscheint. Wir wählen aus Wertfeldeinstellungen… aus dem Menü:

Das Wertfeldeinstellungen Feld erscheint.

Drücke den Zahlenformat Taste und der Standard Feld "Zellen formatieren" erscheint:

Von dem Kategorie Liste auswählen (sagen) Buchhaltung, und lassen Sie die Anzahl der Dezimalstellen auf 0. Klicken Sie auf OK ein paar mal, um wieder zum PivotTable zu gelangen…

Wie Sie sehen, wurden die Zahlen korrekt als Dollarbeträge formatiert.

Wenn wir uns mit Formatierung beschäftigen, formatieren wir die gesamte PivotTable. Dafür gibt es mehrere Möglichkeiten. Lass uns ein einfaches verwenden ...

Drücke den PivotTable-Tools / Design Tab:

Lassen Sie dann den Pfeil in der rechten unteren Ecke des Fensters herunter PivotTable-Stile Liste, um eine große Sammlung integrierter Stile zu sehen:

Wählen Sie eine beliebige aus und sehen Sie sich das Ergebnis in Ihrer PivotTable an:

Andere Optionen

Wir können auch mit Terminen arbeiten. In der Regel befinden sich in einer Transaktionsliste viele, viele Daten, wie beispielsweise die, mit der wir begonnen haben. Excel bietet jedoch die Möglichkeit, Datenelemente nach Tag, Woche, Monat, Jahr usw. zu gruppieren. Sehen wir uns an, wie das gemacht wird.

Zuerst entfernen wir die Spalte "Zahlungsmethode" aus der Spaltenbeschriftungen Feld (ziehen Sie es einfach zurück in die Feldliste) und ersetzen Sie es durch die Spalte „Book Booked“:

Wie Sie sehen, macht dies unsere PivotTable sofort unbrauchbar und gibt uns für jedes Datum, an dem eine Transaktion stattfand, eine Spalte - eine sehr breite Tabelle!

Um dies zu beheben, klicken Sie mit der rechten Maustaste auf ein beliebiges Datum und wählen Sie aus Gruppe… aus dem Kontextmenü:

Das Gruppierungsfeld wird angezeigt. Wir wählen aus Monate und klicken Sie auf OK:

Voila! EIN viel nützlichere Tabelle:

(Übrigens ist diese Tabelle praktisch identisch mit der Tabelle am Anfang dieses Artikels - der ursprünglich erstellten Verkaufszusammenfassung, die manuell erstellt wurde.)

Eine weitere coole Sache, die Sie beachten sollten, ist, dass Sie mehr als einen Satz von Zeilenüberschriften (oder Spaltenüberschriften) haben können:

… Was so aussieht… .

Ähnliches können Sie mit Spaltenüberschriften (oder sogar Berichtsfiltern) tun..

Um die Dinge wieder einfach zu halten, schauen wir uns an, wie sie plotten gemittelt Werte, anstatt summierte Werte.

Klicken Sie zuerst auf „Summe der Beträge“ und wählen Sie Wertfeldeinstellungen… aus dem erscheinenden Kontextmenü:

In dem Wertfeld zusammenfassen nach Liste in der Wertfeldeinstellungen Wählen Sie Durchschnittlich:

Wenn wir hier sind, ändern wir das Benutzerdefinierter Name, von "Average of Amount" zu etwas etwas prägnanter. Geben Sie etwas wie "Avg" ein:

Klicken OK, und sehen, wie es aussieht. Beachten Sie, dass sich alle Werte von summierten Summen zu Durchschnittswerten ändern und der Tabellentitel (obere linke Zelle) in "Durchschnitt" geändert wurde:

Wenn wir möchten, können wir sogar Summen, Durchschnittswerte und Zählungen (Zählungen = wie viele Verkäufe gab es) auf derselben PivotTable haben!

Hier sind die Schritte, um so etwas in Position zu bringen (beginnend mit einer leeren PivotTable):

Ziehen Sie "Verkäufer" in die Spaltenbeschriftungen
Ziehen Sie das Feld "Amount" in das Feld Werte dreimal box
Ändern Sie für das erste Feld "Betrag" den benutzerdefinierten Namen in "Gesamt" und das Zahlenformat in Buchhaltung (0 Dezimalstellen)
Ändern Sie für das zweite Feld "Betrag" seinen benutzerdefinierten Namen in "Durchschnitt" und seine Funktion in Durchschnittlich und es ist Zahlenformat bis Buchhaltung (0 Dezimalstellen)
Ändern Sie für das dritte "Amount" -Feld seinen Namen in "Count" und seine Funktion in Anzahl
Ziehen Sie das automatisch erstellte Feld von Spaltenbeschriftungen zu Zeilenbeschriftungen

So kommen wir zum Schluss:

Summe, Durchschnitt und Anzahl auf derselben PivotTable!

Fazit

Es gibt viele, viele weitere Features und Optionen für PivotTables, die mit Microsoft Excel erstellt wurden - viel zu viele, um sie in einem solchen Artikel aufzulisten. Um das Potenzial von PivotTables vollständig abdecken zu können, ist ein kleines Buch (oder eine große Website) erforderlich. Tapfere und / oder geeky Leser können PivotTables problemlos weiter erkunden: Klicken Sie einfach mit der rechten Maustaste auf alles und sehen Sie, welche Optionen für Sie verfügbar sind. Es gibt auch die zwei Ribbon-Registerkarten: PivotTable-Tools / -Optionen und Design. Es macht nichts, wenn Sie einen Fehler machen - es ist einfach, das PivotTable zu löschen und erneut zu starten - eine Möglichkeit, die alte DOS-Benutzer von Lotus 1-2-3 nie hatten.

Wenn Sie in Office 2007 arbeiten, möchten Sie vielleicht unseren Artikel zum Erstellen einer PivotTable in Excel 2007 lesen.

Wir haben eine Excel-Arbeitsmappe beigelegt, die Sie herunterladen können, um Ihre PivotTable-Fähigkeiten zu üben. Es sollte mit allen Versionen von Excel ab 97 funktionieren.

Laden Sie unsere Übungs-Excel-Arbeitsmappe herunter