/ Arbeiten mit Pivot-Tabellen in Microsoft Excel

Arbeiten mit Pivot-Tabellen in Microsoft Excel

PivotTables sind eine der leistungsstärksten Funktionenvon Microsoft Excel. Mit ihnen können große Datenmengen mit wenigen Mausklicks analysiert und zusammengefasst werden. In diesem Artikel untersuchen wir PivotTables, verstehen, was sie sind, und lernen, wie sie erstellt und angepasst werden.

Hinweis: Dieser Artikel wurde mit Excel 2010 (Beta) verfasst. Das Konzept einer PivotTable hat sich im Laufe der Jahre kaum geändert, aber die Methode zum Erstellen einer PivotTable hat sich in nahezu jeder Excel-Iteration geändert. Wenn Sie eine Excel-Version verwenden, die nicht 2010 ist, erwarten Sie andere Bildschirme als in diesem Artikel.

Eine kleine Geschichte

In den Anfängen von Tabellenkalkulationsprogrammen war Lotus1-2-3 regierte den Rastplatz. Seine Dominanz war so vollständig, dass die Leute dachten, es wäre Zeitverschwendung für Microsoft, sich die Mühe zu machen, eine eigene Tabellenkalkulationssoftware (Excel) zu entwickeln, um mit Lotus mithalten zu können. Rückblick auf 2010 und Excel dominiert den Tabellenkalkulationsmarkt stärker als je zuvor, während die Anzahl der Benutzer, die noch Lotus 1-2-3 ausführen, gegen Null geht. Wie ist es passiert? Was verursachte eine solch dramatische Umkehrung des Vermögens?

Branchenanalysten führen zwei Faktoren an: Zunächst entschied Lotus, dass diese schicke neue GUI-Plattform mit dem Namen „Windows“ eine Modeerscheinung war, die niemals auffliegen würde. Sie lehnten es ab, eine Windows-Version von Lotus 1-2-3 zu erstellen (jedenfalls für ein paar Jahre), und sagten voraus, dass ihre DOS-Version der Software alles war, was irgendjemand jemals brauchen würde. Microsoft hat Excel natürlich exklusiv 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, exklusiv für Excel,wurde als so erstaunlich nützlich erachtet, dass die Leute bereit waren, ein ganz neues Softwarepaket (Excel) zu lernen, anstatt sich an ein Programm (1-2-3) zu halten, das es nicht hatte. Diese eine Funktion war zusammen mit der falschen Beurteilung des Erfolgs von Windows der Todesstoß für Lotus 1-2-3 und der Beginn des Erfolgs von Microsoft Excel.

Grundlegendes zu PivotTables

Was genau ist eine PivotTable?

Einfach ausgedrückt ist eine PivotTable eine Zusammenfassung einigerDaten, die erstellt wurden, um eine einfache Analyse dieser Daten zu ermöglichen. Im Gegensatz zu einer manuell erstellten Zusammenfassung sind Excel-PivotTables interaktiv. Sobald Sie eines erstellt haben, können Sie es problemlos ändern, wenn es nicht den genauen Einblick in Ihre Daten bietet, auf den Sie gehofft haben. Mit ein paar Klicks kann die Zusammenfassung gedreht werden, sodass die Spaltenüberschriften zu Zeilenüberschriften werden und umgekehrt. Es gibt noch viel mehr zu tun. Anstatt zu versuchen, alle Funktionen von PivotTables zu beschreiben, werden sie nur demonstriert.

Die Daten, die Sie mithilfe einer PivotTable analysieren, können nicht gerecht sein irgendein Daten - es muss sein roh zuvor unverarbeitete (nicht zusammengefasste) Daten - normalerweise eine Liste. Ein Beispiel hierfür ist die Liste der Verkaufsvorgänge in einem Unternehmen in den letzten sechs Monaten.

Untersuchen Sie die unten gezeigten Daten:

Beispiel für monatliche Verkaufsdaten

Beachten Sie, dass dies ist nicht Rohdaten. In der Tat ist es bereits eine Zusammenfassung irgendeiner Art. In Zelle B3 sehen wir 30.000 US-Dollar, anscheinend die Summe der Verkäufe von James Cook im Januar. Wo sind also die Rohdaten? Wie sind wir auf 30.000 Dollar gekommen? Wo befindet sich die ursprüngliche Liste der Verkaufsvorgänge, aus denen diese Zahl generiert wurde? Es ist klar, dass sich irgendwo jemand die Mühe gemacht haben muss, alle Verkaufstransaktionen der letzten sechs Monate in der oben gezeigten Zusammenfassung zusammenzufassen. Wie lange hat das wohl gedauert? Eine Stunde? Zehn?

Höchstwahrscheinlich ja. Sie sehen, die obige Tabelle ist tatsächlich nicht eine PivotTable. Es wurde manuell aus Rohdaten erstellt, die an anderer Stelle gespeichert waren, und die Kompilierung dauerte in der Tat einige Stunden. Dies ist jedoch genau die Art von Zusammenfassung könnten mit PivotTables erstellt werden. In diesem Fall hätte dies nur wenige Sekunden gedauert. Finden wir heraus, wie ...

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

Rohdaten

Sie werden vielleicht überrascht sein, wenn Sie das mit derDank der PivotTable-Funktion von Excel können wir mit nur wenigen Mausklicks in wenigen Sekunden eine monatliche Verkaufszusammenfassung erstellen, die der obigen ähnelt. Das können wir - und noch viel mehr!

So erstellen Sie eine PivotTable

Stellen Sie zunächst sicher, dass Sie Rohdaten in a habenArbeitsblatt in Excel. Eine Liste von Finanztransaktionen ist typisch, kann aber auch eine Liste von fast allem sein: Kontaktdaten der Mitarbeiter, Ihre CD-Sammlung oder Kraftstoffverbrauchsangaben für den Fuhrpark Ihres Unternehmens.

Also starten wir Excel ... und laden eine solche Liste ...

Rohdaten

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:

Rohdaten - ausgewählt

Dann von der Einfügen Klicken Sie auf die Registerkarte PivotTable Symbol:

Einfügen / PivotTable

Das Erstellen Sie PivotTable wird angezeigt und stellt Ihnen zwei Fragen: Auf welchen Daten soll Ihre neue PivotTable basieren und wo sollen 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 Blatt, 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 vorhandene einer. In diesem Beispiel wählen wir a Neu einer:

Dialogfeld "PivotTable erstellen"

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

PivotTable-Bereich

Ein weiteres Feld wird ebenfalls angezeigt: PivotTable-Feldliste. Diese Feldliste wird angezeigt, wenn wir auf eine Zelle in der PivotTable (oben) klicken:

PivotTable-Felder

Die Liste der Felder im oberen Teil der Box istTatsächlich die Sammlung von Spaltenüberschriften aus dem ursprünglichen Rohdaten-Arbeitsblatt. In den vier leeren Feldern im unteren Bereich des Bildschirms können wir auswählen, wie unsere PivotTable die Rohdaten zusammenfassen soll. Da sich in diesen Feldern noch nichts befindet, 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 Kästchen abzulegen. Anschließend wird automatisch eine PivotTable erstellt, die unseren Anweisungen entspricht. Wenn wir etwas falsch machen, müssen wir nur die Felder dorthin ziehen, wo sie herkommen, 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, Finden 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 das Werte Box:

nur betraglich

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

Wenn wir die PivotTable selbst untersuchen, finden wir in der Tat die Summe aller "Amount" -Werte aus dem Rohdaten-Arbeitsblatt:

Betrag nur drehbar

Wir haben unseren ersten PivotTable erstellt! Handlich, aber nicht besonders beeindruckend. Es ist wahrscheinlich, dass wir ein wenig mehr Einblick in unsere Daten benötigen.

Anhand unserer Beispieldaten müssen wir uns identifiziereneine oder mehrere Spaltenüberschriften, die wir möglicherweise verwenden könnten, um diese Summe aufzuteilen. Zum Beispiel können wir entscheiden, dass wir eine Zusammenfassung unserer Daten sehen möchten, wo wir eine haben Zeilenüberschrift für jeden der verschiedenen Verkäufer in unserem Unternehmen und insgesamt für jeden. Dazu müssen wir lediglich das Feld "Verkäufer" in das Feld ziehen Zeilenbeschriftungen Box:

Verkäufer

Jetztendlich wird es interessant! Unser PivotTable nimmt Gestalt an…

rows1

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

Was können wir also noch tun? In gewisser Hinsicht ist unser PivotTable vollständig. Wir haben eine nützliche Zusammenfassung unserer Quelldaten erstellt. Das Wichtige ist schon gelernt! Im weiteren Verlauf des Artikels werden einige Möglichkeiten zum Erstellen komplexerer PivotTables und zum Anpassen dieser PivotTables untersucht.

Zunächst können wir eine erstellen zwei-Maßtabelle. Verwenden Sie dazu die Spaltenüberschrift "Zahlungsmethode". Ziehen Sie einfach die Überschrift "Zahlungsmethode" auf Spaltenbeschriftungen Box:

2dim1

Welches sieht so aus:

2dim1data

Angefangen zu bekommen sehr cool!

Machen wir es zu einem dreiTabelle. Wie könnte ein solcher Tisch aussehen? Okay, lass uns nachsehen…

Ziehen Sie die Spalte / Überschrift "Package" auf Berichtsfilter Box:

3dim1

Beachten Sie, wo es endet….

3dim1data

Auf diese Weise können wir unseren Bericht anhand des gekauften „Urlaubspakets“ filtern. Beispielsweise können wir die Aufschlüsselung des Verkäufers im Vergleich zur Zahlungsmethode für anzeigen alle Pakete, oder ändern Sie es mit ein paar Klicks, um die gleiche Aufteilung für das Paket „Sunseekers“ anzuzeigen:

3dim1datafiltered

Wenn Sie also richtig darüber nachdenken, ist unser PivotTable jetzt dreidimensional. Passen wir weiter an…

Wenn sich herausstellt, sagen wir, dass wir nur sehen wollen Scheck und Kreditkarte Transaktionen (d. h. keine Bargeldtransaktionen), können wir die Option "Bargeld" in den Spaltenüberschriften deaktivieren. Klicken Sie auf den Dropdown-Pfeil neben Spaltenbeschriftungen, und deaktivieren Sie "Cash":

kein Bargeld

Mal sehen, wie das aussieht ... Wie Sie sehen, ist "Cash" weg.

nocashdata

Formatierung

Dies ist natürlich ein sehr mächtiges System, aber soBisher sehen die Ergebnisse sehr klar und langweilig aus. Zunächst einmal sehen die Zahlen, die wir summieren, nicht wie Dollarbeträge aus - nur einfache alte Zahlen. Korrigieren wir das.

Eine Versuchung könnte sein, das zu tun, was wir gewohnt sindWählen Sie in diesem Fall einfach die gesamte Tabelle (oder das gesamte Arbeitsblatt) aus und schließen Sie die Formatierung mit den Standardschaltflächen für die Zahlenformatierung in der Symbolleiste ab. Das Problem bei diesem Ansatz ist, dass diese Zahlenformate verloren gehen, wenn Sie in Zukunft die Struktur der PivotTable ändern (was zu 99% wahrscheinlich ist). Wir brauchen einen Weg, der sie (halb-) dauerhaft macht.

Zuerst suchen wir den Eintrag "Summe des Betrags" im Werte Box, und klicken Sie darauf. Ein Menü wird angezeigt. Wir wählen aus Wertfeldeinstellungen… aus dem Menü:

dollar1

Das Wertfeldeinstellungen wird angezeigt.

dollar2

Drücke den Zahlenformat Knopf und der Standard Zellen formatieren erscheint:

dollar3

Von dem Kategorie Liste, wähle (sprich) Buchhaltungund lassen Sie die Anzahl der Dezimalstellen auf 0 fallen. Klicken Sie auf okay ein paar Mal, um zum PivotTable zurückzukehren ...

dollar4

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

Während wir uns mit der Formatierung befassen, formatieren wir die gesamte PivotTable. Hierfür gibt es verschiedene Möglichkeiten. Verwenden wir einen einfachen…

Drücke den PivotTable-Tools / Design Tab:

PivotTableDesign

Lassen Sie dann den Pfeil unten rechts auf der PivotTable-Stile Liste, um eine große Sammlung von eingebauten Stilen zu sehen:

Stile

Wählen Sie eine, die Ihnen gefällt, und sehen Sie sich das Ergebnis in Ihrer PivotTable an:

formatiert

Andere Optionen

Wir können auch mit Daten arbeiten. Normalerweise enthält eine Transaktionsliste viele, viele Daten, wie die, mit der wir begonnen haben. Excel bietet jedoch die Möglichkeit, Datenelemente nach Tag, Woche, Monat, Jahr usw. zu gruppieren.

Entfernen Sie zunächst die Spalte "Zahlungsmethode" aus der Spaltenbeschriftungen Feld (ziehen Sie es einfach zurück in die Feldliste) und ersetzen Sie es durch die Spalte "Datum gebucht":

date1

Wie Sie sehen, ist unsere PivotTable dadurch sofort unbrauchbar und wir erhalten eine Spalte für jedes Datum, an dem eine Transaktion stattgefunden hat - eine sehr breite Tabelle!

date2

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

date3

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

date4

Voila! EIN viel nützlichere Tabelle:

date5

(Diese Tabelle ist im Übrigen praktisch identisch mit der am Anfang dieses Artikels gezeigten - der ursprünglichen Verkaufszusammenfassung, die manuell erstellt wurde.)

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

2row

… Was so aussieht….

2rowdata

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

Lassen Sie uns noch einmal sehen, wie Sie zeichnen gemittelt Werte, anstatt summierte Werte.

Klicken Sie zunächst auf "Summe der Beträge" und wählen Sie Wertfeldeinstellungen… aus dem erscheinenden Kontextmenü:

dollar1

In dem Wertfeld zusammenfassen mit Liste in der Wertfeldeinstellungen auswählen Durchschnittlich:

average1

Während wir hier sind, ändern wir das Benutzerdefinierter Name, von "Average of Amount" bis zu etwas prägnanterem. Tippe so etwas wie "Avg" ein:

average2

Klicken okayund sehen, wie es aussieht. Beachten Sie, dass sich alle Werte von summierten Summen zu Durchschnittswerten ändern und der Tabellentitel (obere linke Zelle) zu „Durchschn.“ Geändert hat:

average3

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

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

  1. Ziehen Sie "Verkäufer" in das Spaltenbeschriftungen
  2. Ziehen Sie das Feld „Betrag“ nach unten in das Feld Werte Schachtel dreimal
  3. Ändern Sie für das erste Feld "Betrag" den benutzerdefinierten Namen in "Gesamt" und das Zahlenformat in Buchhaltung (0 Dezimalstellen)
  4. Ändern Sie für das zweite Feld „Betrag“ den benutzerdefinierten Namen in „Durchschnitt“ und die Funktion in Durchschnittlich und es ist das Zahlenformat von Buchhaltung (0 Dezimalstellen)
  5. Ändern Sie für das dritte Feld „Betrag“ den Namen in „Anzahl“ und die Funktion in Anzahl
  6. Ziehen Sie die automatisch erstellte
    Sigmavalues
    Feld von Spaltenbeschriftungen zu Zeilenbeschriftungen

Am Ende haben wir Folgendes:

Combo

Summe, Durchschnitt und Anzahl auf derselben PivotTable!

Fazit

Es gibt viele, viele weitere Funktionen und Optionenfür von Microsoft Excel erstellte PivotTables - viel zu viele, um sie in einem Artikel wie diesem aufzulisten. Um das Potenzial von PivotTables voll auszuschöpfen, ist ein kleines Buch (oder eine große Website) erforderlich. Mutige und / oder geeky Leser können PivotTables ganz einfach weiter erkunden: Klicken Sie einfach mit der rechten Maustaste auf fast alles und sehen Sie, welche Optionen für Sie verfügbar sind. Es gibt auch zwei Ribbon-Tabs: PivotTable-Tools / -Optionen und Design. Es spielt keine Rolle, ob Sie einen Fehler machen - es ist einfach, die 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, lesen Sie möglicherweise unseren Artikel zum Erstellen einer PivotTable in Excel 2007.

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

Laden Sie unser Übungs-Excel-Arbeitsbuch herunter