So erstellen Sie eine Pivot-Tabelle in Excel. Wie man mit Daten arbeitet

MS Excel Pivot Table ist ein leistungsstarkes Datenanalysetool. Mit diesem Tool können Sie mit nur wenigen Mausklicks Informationen aus einer großen Datenmenge in jedem Kontext extrahieren. In diesem Artikel werde ich versuchen, in einfacher und verständlicher Sprache Schritt für Schritt zu erklären, was und wie zu tun ist, um Ihnen dieses äußerst praktische MS Excel-Tool zur Verfügung zu stellen.

Anforderungen an Quelldaten.

Die Grundlage jeder Pivot-Tabelle ist also ein korrekt aufgebautes Datenarray – die „richtige Tabelle“. In der folgenden Abbildung sehen Sie ein Beispiel für ein korrekt geformtes Datenarray:

Schauen wir uns an, was eigentlich die „Richtigkeit“ dieser Tabelle ist. Die Richtigkeit ist:

• Jede Spalte enthält nur denselben Datentyp, die Spalte A Nur Datumsangaben in der Spalte IN nur Dokumente, in der Spalte MIT nur Kunden, Geld in Geld, Lieferanten in Lieferanten, Produktkategorien in Kategorien und so weiter ... In einer solchen Tabelle können wir sehr einfach verwenden Filter;
• Spalten mit demselben Datentyp werden nicht wiederholt.
• Die Tabelle enthält keine Gesamtzeilen, sondern nur „saubere Daten“.
• In den Textdaten der Tabelle gibt es keine leeren Zellen; jede Zeile enthält den Namen des Kunden, des Produkts, des Lieferanten, des Managers usw.

In einem Array spielt es übrigens überhaupt keine Rolle, in welcher Reihenfolge die Spalten stehen und nach welchem ​​Feld die Daten sortiert sind. Dies hat keinerlei Auswirkungen auf die Konstruktion des Pivot Tables.

Und hier ist ein Beispiel für eine „falsche Tabelle“, aus der die Pivot-Tabelle nicht erstellt wird, selbst wenn Sie sie knacken, und selbst wenn sie erstellt wird, ist es völlig unmöglich, mit den Daten zu arbeiten ... Unsere „ „Die beliebtesten“ Buchhaltungssysteme „erfreuen“ uns meist mit solchen Tabellen, die uns diese in Form von Berichten liefern, die für eine weitere Analyse völlig ungeeignet sind:

Hier ist ein weiteres Beispiel für „Ungültiges Array“:

Hier gibt es eine ganze Reihe von „Unregelmäßigkeiten“:

• Erstens haben die Spalten „Lieferant“ und „Kategorie“ leere Zellen, sodass wir den Filter nicht verwenden können;
• Zweitens gibt es in fast jeder Spalte die Zeilen „Gesamt...“, die für die Erstellung der Pivot-Tabelle völlig unnötig sind und außerdem nur im Weg stehen.
• Drittens wird auch die Spalte „Gesamtsumme“ nicht benötigt;
• Viertens befindet sich die gleiche Art von Daten, nämlich „Geld“, in bis zu drei Spalten: „Jan“, „Feb“ und „März“, was die Erstellung der Pivot-Tabelle erheblich erschweren wird, was bedeutet, dass wir dies tun müssen mach was damit...

Dies ist jedoch alles für ein separates Gespräch. Wenn Sie erfahren möchten, wie Sie solche „krummen Tabellen“ schnell in „richtige Arrays“ umwandeln, lesen Sie die Artikel: „Wie erstellt man schnell eine Pivot-Tabelle aus einem 1C- oder SAP-Bericht?“ und „Wie konvertiert man schnell eine Tabelle in ein Array für Pivot-Tabellen?“

Eigentlich geht es bei der Erstellung der Pivot-Tabelle um Folgendes:

Nehmen Sie das „richtige Array“, platzieren Sie den Cursor in einer beliebigen Zelle des Arrays, wählen Sie im Hauptmenü die Registerkarte „Einfügen“ und klicken Sie in der linken Ecke im Abschnitt „Tabellen“ auf die Schaltfläche „Pivot-Tabelle“:

Klicken Sie im sich öffnenden Dialogfeld „Pivot-Tabelle erstellen“ auf „OK“:

MS Excel erstellt ein neues Blatt, auf dem die Stelle angegeben wird, an der die Pivot-Tabelle eingefügt wird, und auf der rechten Seite wird ein Fenster zum Einrichten von Pivot-Tabellenfeldern angezeigt, in dem Sie alle Namen der Spalten von sehen Ihr Array:

Beginnen wir mit der Einrichtung der Pivot-Tabelle. Welchen Schnitt wollen wir? Produktkategorien nach Manager – bitte. Bewegen Sie die Maus über das Feld „Kategorie“, klicken Sie mit der linken Maustaste darauf und ziehen Sie es in das Feld „ZEILEN“. Das Feld „Manager“ wird in „Spalten“ und der „Betrag“ in „WERTE“ gezogen:

Pivot-Tabellen sind eines der effektivsten Tools in MS Excel. Mit ihrer Hilfe können Sie eine Million Datenzeilen in Sekundenschnelle in einen prägnanten Bericht umwandeln. Pivot-Tabellen ermöglichen nicht nur eine schnelle Zusammenfassung der Ergebnisse, sondern ermöglichen es Ihnen, die Art und Weise Ihrer Analyse im Handumdrehen zu ändern, indem Sie Felder von einem Bereich des Berichts in einen anderen ziehen.

Pivot-Tabellen gehören ebenfalls zu den am meisten unterschätzten Excel-Tools. Die meisten Benutzer sind sich der Möglichkeiten, die ihnen zur Verfügung stehen, nicht bewusst. Stellen wir uns vor, dass Pivot-Tabellen noch nicht erfunden wurden. Sie arbeiten für ein Unternehmen, das seine Produkte an verschiedene Kunden verkauft. Der Einfachheit halber umfasst das Sortiment nur 4 Artikel. Die Produkte werden regelmäßig von einigen Dutzend Kunden in verschiedenen Regionen gekauft. Jede Transaktion wird in die Datenbank eingetragen und stellt eine separate Zeile dar.

Ihr Direktor weist Sie an, einen kurzen Bericht über den Verkauf aller Waren nach Region (Region) zu erstellen. Das Problem kann wie folgt gelöst werden.

Erstellen wir zunächst ein Tabellenlayout, also eine Kopfzeile, die aus eindeutigen Werten für Produkte und Regionen besteht. Erstellen wir eine Kopie der Produktspalte und entfernen Sie Duplikate. Anschließend transponieren wir die Spalte mithilfe von „Insert Special“ in eine Zeile. Das Gleiche machen wir mit Regionen, nur ohne Transponierung. Wir erhalten den Header des Berichts.

Dieses Schild muss ausgefüllt werden, d.h. Fassen Sie den Umsatz für die relevanten Produkte und Regionen zusammen. Dies ist mit der SUMIFS-Funktion einfach zu bewerkstelligen. Fügen wir auch die Ergebnisse hinzu. Sie erhalten einen zusammenfassenden Bericht über die Verkäufe nach Region und Produkt.

Sie haben die Aufgabe erledigt und zeigen den Bericht dem Direktor. Beim Blick auf die Tabelle kommen ihm mehrere tolle Ideen auf einmal.

— Ist es möglich, einen Bericht nicht über den Umsatz, sondern über den Gewinn zu erstellen?

— Ist es möglich, Produkte nach Zeilen und Regionen nach Spalten anzuzeigen?

— Ist es möglich, solche Tabellen für jeden Manager separat zu erstellen?

Selbst wenn Sie ein erfahrener Excel-Benutzer sind, wird die Erstellung neuer Berichte viel Zeit in Anspruch nehmen. Ganz zu schweigen von möglichen Fehlern. Wenn Sie jedoch Pivot-Tabellen kennen, lautet die Antwort: Ja, ich brauche 5 Minuten, vielleicht weniger.

So wird es gemacht. Öffnen Sie die Quelldaten. Eine Pivot-Tabelle kann mit einem regulären Bereich erstellt werden, richtiger wäre es jedoch, ihn zu transformieren. Dadurch wird das Problem der automatischen Erfassung neuer Daten sofort gelöst. Wählen Sie eine beliebige Zelle aus und gehen Sie zur Registerkarte Einfügen. Auf der linken Seite des Menübands befinden sich zwei Schaltflächen: und Empfohlene PivotTables.

Wenn Sie nicht wissen, wie Sie die vorhandenen Daten organisieren sollen, können Sie den Befehl verwenden Empfohlene PivotTables. Excel zeigt Miniaturansichten möglicher Layouts basierend auf Ihren Daten an.

Klicken Sie auf die entsprechende Option und die Pivot-Tabelle ist fertig. Es bleibt nur noch, sich daran zu erinnern, denn... Es ist unwahrscheinlich, dass ein Standardpräparat Ihren Wünschen vollständig entspricht. Wenn Sie eine Pivot-Tabelle von Grund auf erstellen müssen oder eine alte Version des Programms haben, klicken Sie auf die Schaltfläche. Es erscheint ein Fenster, in dem Sie den Quellbereich (wenn Sie eine Excel-Tabellenzelle aktivieren, wird diese von selbst bestimmt) und den Speicherort der zukünftigen Pivot-Tabelle (standardmäßig wird ein neues Blatt ausgewählt) angeben müssen.

Sehr oft besteht hier keine Notwendigkeit, etwas zu ändern. Nachdem Sie auf „OK“ geklickt haben, wird eine neue Excel-Tabelle mit einem leeren PivotTable-Layout erstellt.

Bevor wir mit den Einstellungen fortfahren, machen wir uns mit der Benutzeroberfläche und den Grundkonzepten vertraut. Das Tabellenlayout kann im Panel angepasst werden Pivot-Tabellenfelder, die sich auf der rechten Seite des Blattes befindet.

Oben im Panel befindet sich eine Liste aller verfügbaren Felder, also Spalten in den Quelldaten. Wenn Sie dem Layout ein neues Feld hinzufügen müssen, können Sie das Kontrollkästchen daneben aktivieren. Excel bestimmt dann selbst, wo dieses Feld platziert werden soll. Dies ist jedoch nicht immer korrekt, daher ist es besser, die Maus an die gewünschte Stelle im Layout zu ziehen. Felder können auch durch Deaktivieren oder Zurückziehen gelöscht werden.

Die Pivot-Tabelle besteht aus 4 Bereichen, die sich am unteren Rand des Panels befinden: Werte, Zeilen, Spalten, Filter. Schauen wir uns ihren Zweck genauer an.

Wertebereich– Dies ist der zentrale Teil der Pivot-Tabelle mit Werten, die durch Aggregation der Quelldaten mit der ausgewählten Methode erhalten werden.
In den meisten Fällen erfolgt die Aggregation durch Summe. Wenn alle Daten im ausgewählten Feld im numerischen Format vorliegen, weist Excel eine Standardsumme zu. Wenn die Quelldaten mindestens einen Text oder eine leere Zelle enthalten, wird anstelle der Summe diese berechnet Menge Zellen. In unserem Beispiel ist jede Zelle die Summe aller entsprechenden Produkte in der entsprechenden Region.

Sie können andere Berechnungsmethoden in PivotTable-Zellen verwenden. Es gibt etwa 20 Typen (Durchschnitt, Mindestwert, Anteil usw.). Es gibt mehrere Möglichkeiten, die Berechnungsmethode zu ändern. Am einfachsten ist es, mit der rechten Maustaste auf eine beliebige Zelle des gewünschten Felds in der Pivot-Tabelle selbst zu klicken und eine andere Aggregationsmethode auszuwählen.

Linienbereich– die Namen der Zeilen, die sich in der Spalte ganz links befinden. Dies sind alle eindeutigen Werte des ausgewählten Felds (Spalte). Im Zeilenbereich können mehrere Felder vorhanden sein, dann wird die Tabelle mehrstufig. Hier werden in der Regel qualitative Variablen wie Produktnamen, Monate, Regionen usw. platziert.

Spaltenbereich– Zeigt ähnlich wie Zeilen die eindeutigen Werte des ausgewählten Felds an, nur in Spalten. Auch Spaltentitel sind in der Regel ein qualitativer Indikator. Zum Beispiel Jahre und Monate, Produktgruppen.

Filterbereich– wird, wie der Name schon sagt, zum Filtern verwendet. Der Bericht selbst zeigt beispielsweise Produkte nach Region. Sie müssen Ihre Pivot-Tabelle auf eine bestimmte Branche, einen bestimmten Zeitraum oder einen bestimmten Manager beschränken. Anschließend wird im Filterbereich ein Filterfeld platziert und der gewünschte Wert aus der Dropdown-Liste ausgewählt.

Durch das Hinzufügen und Entfernen von Feldern zu bestimmten Bereichen können Sie jeden gewünschten Teil Ihrer Daten in Sekundenschnelle anpassen.

Mal sehen, wie das in der Praxis funktioniert. Lassen Sie uns zunächst dieselbe Tabelle erstellen, die bereits mit der SUMIFS-Funktion erstellt wurde. Ziehen Sie dazu in den Bereich Werte Feld „Umsatz“, im Bereich Saiten Ziehen Sie das Feld „Region“ (Verkaufsregion) nach Säulen- "Produkt".

Als Ergebnis erhalten wir eine echte Pivot-Tabelle.

Der Aufbau dauerte buchstäblich 5-10 Sekunden. Auch das Ändern einer vorhandenen Pivot-Tabelle ist einfach. Mal sehen, wie sich die Wünsche des Regisseurs leicht in die Realität umsetzen lassen.

Ersetzen wir den Umsatz durch den Gewinn.

Produkte und Bereiche können auch durch Ziehen mit der Maus ausgetauscht werden.

Zum Filtern von Pivot-Tabellen stehen mehrere Tools zur Verfügung. In diesem Fall platzieren wir einfach das Feld „Manager“ im Filterbereich.

Alles dauerte ein paar Sekunden. So einfach ist die Arbeit mit Pivot-Tabellen wirklich. Natürlich sind nicht alle Aufgaben so trivial. Es gibt auch Fälle, in denen es notwendig ist, eine ausgefeiltere Aggregationsmethode zu verwenden, berechnete Felder hinzuzufügen, bedingte Formatierung usw. hinzuzufügen. Dies ist jedoch eine fortgeschrittenere Verwendung von Pivot-Tabellen.

Ausgangsdaten

Um erfolgreich mit Pivot-Tabellen arbeiten zu können, müssen die Quelldaten eine Reihe von Anforderungen erfüllen. Voraussetzung ist das Vorhandensein von Namen über jedem Feld (Spalte), anhand derer diese Felder identifiziert werden. Nun einige nützliche Tipps.

1. Das beste Format für Daten ist eine Excel-Tabelle. Das ist gut, denn jedes Feld hat einen Namen und wenn neue Zeilen hinzugefügt werden, werden diese automatisch in die Pivot-Tabelle aufgenommen.

2. Vermeiden Sie sich wiederholende Gruppen in Spalten. Beispielsweise sollten alle Daten in einem Feld stehen und nicht beispielsweise nach Monaten in separate Spalten unterteilt werden.

3. Entfernen Sie Lücken und leere Zellen, da diese Zeile sonst möglicherweise aus der Analyse herausfällt.

4. Wenden Sie die richtige Formatierung auf die Felder an. Zahlen müssen im numerischen Format vorliegen, Datumsangaben müssen Datumsangaben sein. Andernfalls kommt es zu Problemen bei der Gruppierung und mathematischen Verarbeitung. Aber Excel hilft Ihnen hier weiter, denn... Es bestimmt das Datenformat recht gut.

Im Allgemeinen gibt es nur wenige Anforderungen, die Sie jedoch kennen sollten.

Wenn Sie Änderungen an der Quelle vornehmen (z. B. das Hinzufügen neuer Zeilen), ändert sich die Pivot-Tabelle erst, wenn Sie sie mit der rechten Maustaste aktualisieren

oder
über den Befehl im Tab Daten – Alle aktualisieren.

Dies geschah mit Absicht, da die Pivot-Tabelle viel Platz im RAM beansprucht. Um Computerressourcen sparsamer zu nutzen, wird daher nicht direkt mit der Quelle gearbeitet, sondern mit dem Cache, in dem sich ein Snapshot der Quelldaten befindet.

Durch die Verwendung von Pivot-Tabellen können Sie bereits auf dieser Grundebene die Geschwindigkeit und Qualität der Verarbeitung großer Datenmengen erheblich steigern. Wenn noch etwas unklar bleibt, schreiben Sie es in die Kommentare.

Pivot-Tabellen ist eines der leistungsstärksten Excel-Tools. Sie ermöglichen es Ihnen, mit wenigen Klicks verschiedene Ergebnisse für große Datenmengen zu analysieren und zusammenzufassen. In diesem Artikel lernen wir Pivot-Tabellen kennen, verstehen, was sie sind, und lernen, wie man sie erstellt und konfiguriert.

Beim Schreiben dieses Artikels habe ich Excel 2010 verwendet. Das Konzept der Pivot-Tabellen ist seit vielen Jahren weitgehend unverändert geblieben, aber die Art und Weise, wie sie erstellt werden, unterscheidet sich in jeder neuen Excel-Version geringfügig. Wenn Sie eine andere Excel-Version als 2010 haben, müssen Sie damit rechnen, dass die Screenshots in diesem Artikel von dem abweichen, was Sie auf Ihrem Bildschirm sehen.

Eine kleine Geschichte

Zu Beginn der Entwicklung von Programmen zur Erstellung von Tabellenkalkulationen lauteten die Regeln von Lotus 1-2-3. Seine Dominanz war so umfassend, dass die Bemühungen von Microsoft, eine eigene Software (Excel) als Alternative zu Lotus zu entwickeln, als Zeitverschwendung erschienen. Jetzt schnell vorwärts ins Jahr 2010! Excel dominiert die Tabellenkalkulationsbranche mehr als Lotus jemals in seiner Geschichte, und die Zahl der Menschen, die Lotus immer noch verwenden, geht gegen Null. Wie konnte das passieren? Was war der Grund für eine so dramatische Wendung der Ereignisse?

Analysten identifizieren zwei Hauptfaktoren:

• Zunächst entschied Lotus, dass diese neue 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 erstellen (aber nur für ein paar Jahre), weil sie voraussagten, 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 entwickelte Microsoft in Excel ein Tool namens Pivot-Tabellen, das in Lotus 1-2-3 nicht verfügbar war. Pivot-Tabellen, eine Excel-Exklusivität, waren so unglaublich nützlich, dass die Leute eher dazu neigten, die neue Excel-Software zu erlernen, als bei Lotus 1-2-3 zu bleiben, das sie nicht hatte.

Pivot-Tabellen, gepaart mit der Unterschätzung des Erfolgs von Windows im Allgemeinen, waren der Todesmarsch für Lotus 1-2-3 und markierten den Beginn des Erfolgs von Microsoft Excel.

Was sind Pivot-Tabellen?

Wie lässt sich also am besten beschreiben, was Pivot-Tabellen sind?

Vereinfacht ausgedrückt handelt es sich bei Pivot-Tabellen um Zusammenfassungen einiger Daten, die erstellt werden, um die Analyse dieser Daten zu erleichtern. Im Gegensatz zu manuell erstellten Summen sind Excel-PivotTables interaktiv. Sobald sie erstellt sind, können Sie sie problemlos ändern, wenn sie nicht das gewünschte Bild liefern. Mit nur wenigen Klicks können Summen umgedreht werden, sodass Spaltenüberschriften zu Zeilenüberschriften werden und umgekehrt. Es gibt viele verschiedene Dinge, die Sie mit Pivot-Tabellen tun können. Anstatt zu versuchen, alle Möglichkeiten von Pivot-Tabellen in Worten zu beschreiben, ist es einfacher, sie in der Praxis zu demonstrieren ...

Bei den Daten, die Sie mit PivotTables analysieren, kann es sich nicht um beliebige Daten handeln. Dies sollten Rohdaten sein, etwa eine Liste. Dies könnte beispielsweise eine Liste der abgeschlossenen Verkäufe im Unternehmen in den letzten sechs Monaten sein.

Schauen Sie sich die in der folgenden Abbildung gezeigten Daten an:

Bitte beachten Sie, dass es sich hierbei nicht um Rohdaten handelt, da diese bereits zusammengefasst wurden. In Zelle B3 sehen wir 30.000 US-Dollar, was wahrscheinlich der Gesamtsumme entspricht, die James Cook im Januar verdient hat. Wo sind dann die Quelldaten? Woher kommt die Zahl von 30.000 US-Dollar? Wo ist die ursprüngliche Verkaufsliste, aus der diese monatliche Gesamtsumme abgeleitet wurde? Es ist klar, dass jemand große Anstrengungen unternommen hat, um alle Verkaufsdaten der letzten sechs Monate zu organisieren, zu sortieren und sie in die Übersichtstabelle umzuwandeln, die wir sehen. Wie lange hat es Ihrer Meinung nach gedauert? Stunde? Zehn Uhr?

Tatsache ist, dass es sich bei der obigen Tabelle nicht um eine Übersichtstabelle handelt. Es wurde manuell aus anderswo gespeicherten Rohdaten erstellt und die Verarbeitung dauerte mindestens ein paar Stunden. Dies ist genau die Art von Übersichtstabelle, die mit Pivot-Tabellen in nur wenigen Sekunden erstellt werden kann. Lassen Sie uns herausfinden, wie...

Wenn wir zur ursprünglichen Verkaufsliste zurückkehren, würde diese etwa so aussehen:

Sie werden vielleicht überrascht sein, dass wir aus dieser Liste von Handelstransaktionen mithilfe von Pivot-Tabellen in nur wenigen Sekunden einen monatlichen Verkaufsbericht in Excel erstellen können, den wir oben besprochen haben. Ja, das und noch viel 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 das typischste, was man findet. Tatsächlich könnte es sich um eine Liste mit allem Möglichen handeln: Kontaktinformationen von Mitarbeitern, eine Sammlung von CDs oder die Kraftstoffverbrauchsdaten Ihres Unternehmens.

Also, starten wir Excel... und laden diese Liste...

Sobald 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 PivotTable(Übersichtstabelle):

Es erscheint ein Dialogfeld Erstellen Sie eine PivotTable(Erstellen einer PivotTable) mit zwei Fragen an Sie:

• Welche Daten sollte ich verwenden, um eine neue Pivot-Tabelle zu erstellen?
• Wo soll ich den Pivot-Tisch platzieren?

Da wir im vorherigen Schritt bereits eine der Listenzellen ausgewählt haben, wird die gesamte Liste automatisch ausgewählt, um eine Pivot-Tabelle zu erstellen. Beachten Sie, dass wir einen anderen Bereich, eine andere Tabelle oder sogar eine externe Datenquelle auswählen können, beispielsweise eine Access- oder MS-SQL-Datenbanktabelle. Darüber hinaus 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(Auf einem neuen Blatt):

Excel erstellt ein neues Arbeitsblatt 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 in der Originalliste. Mithilfe der vier leeren Bereiche am unteren Bildschirmrand können Sie der PivotTable mitteilen, wie Sie die Daten zusammenfassen möchten. Während diese Bereiche leer sind, gibt es in der Tabelle auch nichts. Wir müssen lediglich die Überschriften vom oberen Bereich in die leeren Bereiche darunter ziehen. In diesem Fall wird gemäß unserer Anleitung automatisch eine Pivot-Tabelle generiert. Wenn wir einen Fehler gemacht haben, können wir die Überschriften aus dem unteren Bereich entfernen oder andere durch Ziehen ersetzen.

Region 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.). Dabei handelt es sich fast immer um numerische Werte. Ein ausgezeichneter Kandidat für eine Stelle in diesem Bereich – Daten unter der Überschrift Menge(Kosten) unseres Originaltisches. Ziehen wir diesen Titel in den Bereich Werte(Werte):

Bitte beachten Sie, dass der Titel Menge ist nun mit einem Häkchen markiert, und im Bereich Werte Der Eintrag (Werte) wurde angezeigt Summe des Betrags(Feld „Summe nach Betrag“), das angibt, dass die Spalte Menge zusammengefasst

Wenn wir uns die Pivot-Tabelle selbst ansehen, sehen wir die Summe aller Werte aus der Spalte Menge Originaltisch.

Damit ist unsere erste Pivot-Tabelle erstellt! Praktisch, aber nicht besonders beeindruckend. Wir möchten wahrscheinlich mehr Informationen über unsere Daten haben, als wir derzeit haben.

Schauen wir uns die Quelldaten an und versuchen, eine oder mehrere Spalten zu identifizieren, die zur Aufschlüsselung dieser Summe verwendet werden können. Beispielsweise können wir unsere Pivot-Tabelle so erstellen, dass der Gesamtumsatz für jeden Verkäufer separat berechnet wird. Diese. Zu unserer Pivot-Tabelle werden Zeilen mit dem Namen jedes Unternehmensverkäufers und seinem Gesamtumsatz hinzugefügt. Um dieses Ergebnis zu erzielen, ziehen Sie einfach den Titel Verkäufer Zeilenbeschriftungen(Linien):

Es wird immer interessanter! Unser Pivot-Tisch 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 gewisser Weise ist unsere Pivot-Tabelle bereits fertig. Wir haben eine hilfreiche Zusammenfassung der Rohdaten erstellt. Wichtige Informationen liegen bereits vor! Im Rest dieses Artikels werden wir uns einige Möglichkeiten zum Erstellen komplexerer Pivot-Tabellen ansehen und lernen, wie man sie individuell anpasst.

Einrichten einer Pivot-Tabelle

Zunächst können wir eine zweidimensionale Pivot-Tabelle erstellen. Machen wir das mit der Spaltenüberschrift Bezahlverfahren(Zahlungsmethode). Ziehen Sie einfach den Titel Bezahlverfahren zur Region Spaltenbeschriftungen(Säulen):

Wir erhalten das Ergebnis:

Sieht sehr cool aus!

Lassen Sie uns nun eine dreidimensionale Tabelle erstellen. Wie könnte so ein Tisch aussehen? Werfen wir einen Blick...

Titel ziehen Paket(Komplex) zur Gegend Berichtsfilter(Filter):

Beachten Sie, wo er gelandet ist ...

Dies gibt uns die Möglichkeit, den Bericht nach dem Attribut „Welche Ferienanlage wurde bezahlt“ zu filtern. Wir können beispielsweise eine Aufschlüsselung nach Verkäufern und Zahlungsmethoden für alle Komplexe anzeigen oder mit ein paar Klicks die Ansicht der Übersichtstabelle ändern und dieselbe Aufschlüsselung nur für diejenigen anzeigen, die den Komplex bestellt haben Sonnenhungrige.

Wenn Sie das also richtig verstehen, kann unsere Pivot-Tabelle als dreidimensional bezeichnet werden. Lasst uns weiter konfigurieren...

Sollte sich plötzlich herausstellen, dass in der Pivot-Tabelle nur Zahlungen per Scheck und Kreditkarte (also Banküberweisung) angezeigt werden sollen, dann können wir die Header-Anzeige deaktivieren Kasse(Kasse). Um dies nebenan zu tun Spaltenbeschriftungen Klicken Sie auf den Abwärtspfeil und deaktivieren Sie das Element im Dropdown-Menü Kasse:

Mal sehen, wie unsere Pivot-Tabelle jetzt aussieht. Wie Sie sehen können, die Spalte Kasse ist von ihr verschwunden.

Pivot-Tabellen in Excel formatieren

Offensichtlich sind PivotTables ein sehr leistungsfähiges Tool, aber bisher sehen die Ergebnisse etwas schlicht und langweilig aus. Beispielsweise sehen die Zahlen, die wir addieren, nicht wie Dollarbeträge aus – es sind nur Zahlen. Lassen Sie uns das beheben.

In einer solchen Situation ist es verlockend, die üblichen Aktionen auszuführen 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 besteht darin, dass die Formatierung verloren geht, wenn Sie in Zukunft jemals die Struktur der Pivot-Tabelle ändern (und die Wahrscheinlichkeit, dass dies geschieht, 99 % beträgt). Wir brauchen einen Weg, es (fast) dauerhaft zu machen.

Suchen wir zunächst den Eintrag Summe des Betrags im Gebiet Werte(Werte) und klicken Sie darauf. Wählen Sie im angezeigten Menü das Element aus Wertfeldeinstellungen(Wertfeldoptionen):

Es erscheint ein Dialogfeld Wertfeldeinstellungen(Wertfeldparameter).

Drück den Knopf Zahlenformat(Zahlenformat) wird ein Dialogfeld angezeigt Zellen formatieren(Zellenformat):

Von der Liste Kategorie(Zahlenformate) auswählen Buchhaltung(Finanzen) und setzen Sie die Anzahl der Dezimalstellen auf Null. Drücken Sie nun mehrmals OK um zu unserer Pivot-Tabelle zurückzukehren.

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

Nachdem wir nun mit der Formatierung fertig sind, richten wir das Format für die gesamte Pivot-Tabelle ein. Dafür gibt es mehrere Möglichkeiten. Nehmen wir die einfachere Variante...

Öffnen Sie die Registerkarte PivotTable-Tools: Design(Arbeiten mit Pivot-Tabellen: Designer):

Wählen Sie einen geeigneten Stil aus und sehen Sie sich das Ergebnis in Ihrer Pivot-Tabelle an:

Weitere Einstellungen für Pivot-Tabellen in Excel

Manchmal müssen Sie Daten nach Datum filtern. Beispielsweise enthält unsere Liste der Handelstransaktionen viele, viele Daten. Excel bietet ein Tool zum Gruppieren von Daten nach Tag, Monat, Jahr usw. Mal sehen, wie es gemacht wird.

Entfernen Sie zunächst den Eintrag. Bezahlverfahren aus der Region Spaltenbeschriftungen(Säulen). Ziehen Sie es dazu zurück in die Überschriftenliste und verschieben Sie an seiner Stelle die Überschrift Gebuchtes Datum(Buchungsdatum):

Wie Sie sehen, 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. Dadurch 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 es aus dem Kontextmenü aus Gruppe(Gruppe):

Das Gruppierungsdialogfeld wird angezeigt. Wir wählen Monate(Monate) und drücken Sie OK:

Voila! Diese Tabelle ist viel nützlicher:

Diese Tabelle ist übrigens fast identisch mit der am Anfang des Artikels gezeigten, in der die Verkaufsergebnisse 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 es wird so aussehen...

Das Gleiche kann mit Spaltenüberschriften (oder sogar Filtern) gemacht werden.

Kehren wir zur ursprünglichen Tabellenansicht zurück und sehen wir uns an, wie Durchschnittswerte anstelle von Summen angezeigt werden.

Um zu beginnen, klicken Sie auf Summe des Betrags und wählen Sie im angezeigten Menü die Option aus Wertfeldeinstellungen(Wertfeldoptionen):

Auf der Liste Wertfeld zusammenfassen nach(Vorgang) im Dialogfeld Wertfeldeinstellungen(Wertfeldoptionen) auswählen Durchschnitt(Durchschnitt):

Gleichzeitig wollen wir uns, während wir hier sind, ändern Benutzerdefinierter Name(Benutzerdefinierter Name) mit Durchschnittlicher Betrag(Feld „Menge nach Betrag“) auf etwas Kürzeres. Geben Sie in dieses Feld etwa Folgendes ein Durchschn:

Klicken OK und schau, was passiert ist. Beachten Sie, dass sich alle Werte von Gesamtwerten zu Durchschnittswerten geändert haben und der Tabellentitel (in der Zelle oben links) in geändert wurde Durchschn:

Wenn Sie möchten, können Sie die Summe, den Durchschnitt und die Menge (Umsätze) sofort in einer Pivot-Tabelle ablegen.

Hier ist eine Schritt-für-Schritt-Anleitung dazu, beginnend mit einer leeren Pivot-Tabelle:

1. Titel ziehen Verkäufer(Vertriebsmitarbeiter) in der Region Spaltenbeschriftungen(Säulen).
2. Ziehen Sie den Titel dreimal Menge(Kosten) pro Fläche Werte(Werte).
3. Für das erste Feld MengeÄndern Sie den Namen in Gesamt(Betrag) und das Zahlenformat in diesem Feld ist Buchhaltung(Finanzielle). Die Anzahl der Dezimalstellen ist Null.
4. Zweites Feld Menge Name Durchschnittlich e, legen Sie die Operation dafür fest Durchschnitt(Durchschnitt) und ändern Sie das Zahlenformat in diesem Feld in Buchhaltung(Finanzwesen) mit der Anzahl der Dezimalstellen gleich Null.
5. Für das dritte Feld Menge Titel festlegen Zählen und eine Operation für ihn - Zählen(Menge)
6. Im Gebiet Spaltenbeschriftungen(Spalten) Feld automatisch erstellt ΣWerte(Σ-Werte) – ziehen Sie es in den Bereich Zeilenbeschriftungen(Saiten)

Das bekommen wir am Ende:

Gesamt-, Durchschnitts- und Anzahl der Verkäufe – alles in einer Pivot-Tabelle!

Abschluss

Pivot-Tabellen in Microsoft Excel enthalten viele, viele Funktionen und Einstellungen. Ein so kurzer Artikel kann sie nicht einmal annähernd alle abdecken. Um alle Funktionen von Pivot-Tabellen vollständig zu beschreiben, wäre ein kleines Buch oder eine große Website erforderlich. Mutige und neugierige Leser können weiterhin Pivot-Tabellen erkunden. 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). Haben Sie keine Angst, einen Fehler zu machen; Sie können die Pivot-Tabelle jederzeit löschen und von vorne beginnen. Sie haben eine Chance, die langjährige DOS- und Lotus 1-2-3-Benutzer nie hatten.

Mit Pivot-Tabellen in Excel können Sie Daten analysieren, die sich in einem großen Bereich befinden. Darüber hinaus kann die Quelltabelle, die für die Analyse verwendet wird, entweder in Excel oder in einer anderen Datenbank oder einem anderen Dokument erstellt werden.

Sie müssen lediglich die erforderlichen Überschriften für Zeilen und Spalten aus den vorhandenen auswählen. Sie können einen Filter auch auf die gesamte Pivot-Tabelle als Ganzes anwenden oder nur auf die Werte, die in den Namen der Zeilen und Spalten angegeben sind.

Nun zu den Anforderungen, die beim Erstellen in Excel beachtet werden müssen. Die Quelldaten müssen in Form einer Tabelle dargestellt werden, die über einen Titel für die Spalten, also eine Kopfzeile, verfügen muss. Für diese Zwecke ist die Erstellung einer intelligenten Tabelle in Excel perfekt. Es sollte auch keine leeren Zeilen, Spalten oder Zellen enthalten. Es sollten keine ausgeblendeten Zeilen/Spalten oder verbundenen Zellen vorhanden sein.

So erstellen Sie

Schauen wir uns nun ein Beispiel für die Erstellung einer Pivot-Tabelle an. Angenommen, wir haben Daten über die Verkäufe eines Bekleidungsgeschäfts: Welcher Verkäufer, welche Anzahl, welche Artikel er verkauft hat und für welchen Betrag.

Um eine Pivot-Tabelle zu erstellen, wählen Sie eine beliebige Zelle aus der Originalzelle aus, gehen Sie dann zur Registerkarte „Einfügen“ und klicken Sie auf die Schaltfläche "Übersichtstabelle".

Das folgende Dialogfeld wird angezeigt. Darin müssen Sie den Bereich oder Namen der Tabelle mit den Quelldaten angeben, und hier können Sie sie aus einer anderen Quelle auswählen. Markieren Sie dann mit einer Markierung, wo der zu erstellende Bericht platziert werden soll. Machen wir es auf einem neuen Blatt. OK klicken.

In der geöffneten Excel-Arbeitsmappe wird ein neues Blatt erstellt, auf dem die noch leere Pivot-Tabelle platziert wird.

Auf der rechten Seite erscheint eine Liste mit Feldern und Bereichen. Bei den Feldern handelt es sich um alle Spaltenüberschriften, die sich im ursprünglichen Bereich befanden. Mit der Maus ziehen wir sie in einen der vier Bereiche unten. So entsteht eine Pivot-Tabelle.

Die hinzugefügten Felder werden mit einem Häkchen markiert. In den Tabellenbereichen können Sie deren Position ändern, um das für die Analyse Ihrer Quelldaten am besten geeignete Erscheinungsbild zu erhalten.

So ordnen Sie Felder an

Lassen Sie uns nun entscheiden, nach welchem ​​Prinzip wir die Daten analysieren. Sie müssen beispielsweise herausfinden, welcher Verkäufer welches Produkt in jedem Monat und für welchen Betrag verkauft hat.

Wir filtern die Daten nach dem ausgewählten Sortiment nach Verkäufern. Das heißt, wir wählen einen Verkäufer aus und in der Tabelle werden die von ihm verkauften Waren angezeigt. Klicken Sie mit der linken Maustaste auf das Feld „Anbieter“ und ziehen Sie es in den Bereich „Berichtsfilter“. Die Tabelle hat sich geändert und das hinzugefügte Feld ist nun mit einem Häkchen markiert.

Wählen Sie für die Zeilen „Produkte“ aus. Ziehen Sie auf ähnliche Weise das gewünschte Feld in den Bereich „Zeilentitel“.

Bitte beachten Sie, dass Sie in einem Bereich mehrere Felder einfügen können. In den Zeilen wählen wir beispielsweise Produkte aus und geben den Preis an. Produkte sind zu Dropdown-Listen geworden, die den Preis anzeigen. Wenn Sie zuerst den Preis und dann die Produkte angeben, wird aus der Dropdown-Liste der Preis. Hier kommt es auf die Reihenfolge der Felder an.

Wenn wir in der Originaltabelle eine Spalte „Einheiten“ hätten. Dann würde diese Übersichtstabelle zeigen, welcher Verkäufer in welchem ​​Monat wie viele Wareneinheiten zu einem bestimmten Preis verkauft hat.

Zur Region „Spaltentitel“ Ziehen Sie das Feld „Datum“. Um die Verkäufe nicht für jeden Tag, sondern beispielsweise pro Monat anzuzeigen, klicken Sie mit der rechten Maustaste auf ein beliebiges Datum und wählen Sie „Gruppe“ aus dem Menü.

Die Tabelle wird die folgende Form annehmen.

Ziehen Sie nun das Feld „Betrag“ in den Bereich „Werte“.

Wie Sie sehen, wurden nur Zahlen angezeigt, obwohl im ursprünglichen Bereich das numerische Format der Zellen für diese Spalte festgelegt war; es kann auch monetär oder finanziell sein. In der Spalte „Datum“ war auch das Zellenformat angemessen – Datum.

Um dies zu beheben, wählen Sie den gewünschten Zellbereich in der Zusammenfassung aus und drücken Sie die rechte Maustaste. Wählen Sie dann aus dem Menü aus "Zahlenformat".

Wählen Sie im nächsten Fenster „Numerisch“ und aktivieren Sie das Kontrollkästchen „Bitgruppentrennzeichen“ und klicken Sie auf „OK“.

Wie man mit Daten arbeitet

Nachdem wir alle notwendigen Felder berücksichtigt haben, können wir mit der Arbeit mit der Pivot-Tabelle in Excel beginnen. Auswahl eines Verkäufers: Sie können einen, mehrere oder alle gleichzeitig auswählen, indem Sie das Kontrollkästchen aktivieren „Mehrere Elemente auswählen“.

Sie können auch einen Filter für Zeilen und Spalten verwenden. Im Beispiel sind dies Produkte und Monate. Wenn Sie beispielsweise die Kästchen für Anzug und Hose markieren, können Sie herausfinden, für wie viel sie von allen Verkäufern oder von einem bestimmten Verkäufer verkauft wurden.

Im Bereich Werte können Sie Einstellungen für das Feld konfigurieren. Das Beispiel zeigt die Summe der Werte: Roma verkaufte im Februar Hemden im Wert von 1.800,00. Mal sehen, wie viele Stücke es sind. Klicken Sie mit der linken Maustaste auf die Linie „Summe nach Feld…“ und wählen Sie aus dem Menü aus „Wertfeldoptionen“.

Wählen Sie im nächsten Fenster „Menge“ aus der Liste aus, wählen Sie aus, was speziell für Ihren Fall geeignet ist, und klicken Sie auf „OK“.

Wenn wir uns nun die Werte ansehen, können wir verstehen, dass Roma im Februar zwei Trikots verkauft hat.

Lassen Sie uns nun den allgemeinen Filter für die Tabelle nach Monat erstellen. Wir ändern die Bereiche: Im „Berichtsfilter“ ziehen wir das Feld „Datum“ hinein „Spaltentitel“- "Verkäufer" .

Eine solche Übersichtstabelle zeigt, wie viel und welche Art von Waren jeder Verkäufer im gesamten Zeitraum oder in einem bestimmten Monat verkauft hat.

Achten Sie auch auf das Klebeband. Wenn Sie Zellen aus einer Pivot-Tabelle auswählen, wird darin eine Registerkarte angezeigt „Arbeiten mit Pivot-Tabellen“ mit zwei Unterregistern „Parameter“ und „Designer“.

Das Problem, mit dem wir alle konfrontiert sind, ist nicht ein Mangel an Daten; im Gegenteil – das sind riesige Datenmengen! Aus diesem Grund plädiere ich für die Verwendung einer großartigen Excel-Funktion – Pivot-Tabellen um Ihre Daten zusammenzufassen und zu analysieren.

Um die Leistungsfähigkeit von PivotTables zu erkennen, genügt ein Vergleich der Originaldaten mit einer Version der PivotTable. Mit nur wenigen Klicks konnte ich den Durchschnittspreis eines Artikels nach Bundesstaat sehen.

Als Finanzprofi habe ich eine genetische Vorliebe für Tabellenkalkulationen. Aber ich habe auch festgestellt, dass ich Tabellenkalkulationen verwende, um meine kreative und freiberufliche Arbeit zu organisieren. Ganz gleich, wofür Sie Tabellenkalkulationen verwenden, eine Pivot-Tabelle kann Ihnen dabei helfen, Ihre Daten sinnvoller zu nutzen.

Wir werden diese Lektion entsprechend unserer aufbauen, um eine bessere Arbeit mit Daten zu ermöglichen. Ich zeige Ihnen fünf meiner liebsten Best Practices für Pivot-Tabellen.

In diesem Tutorial verwende ich die von Microsoft auf dieser Seite bereitgestellten Beispieldaten. Verwenden Sie diese Daten, um die Beispiele nachzubilden oder die von mir demonstrierten Funktionen zu testen.

So verwenden Sie erweiterte PivotTable-Techniken in Excel (kurzes Video)

Ich unterrichte gerne mithilfe von Screencasts, die Ihnen die Möglichkeit geben, mich Schritt für Schritt bei der Nutzung von Funktionen zu beobachten. Schauen Sie sich das kurze Video unten an, das fünf meiner bevorzugten erweiterten Excel-PivotTable-Funktionen behandelt:

5 fortgeschrittene Pivot-Table-Tricks in Excel

Lesen Sie weiter, um in der folgenden Lektion zu erfahren, wie Sie jede dieser fünf Funktionen verwenden, darunter: Slicer, Zeitleiste, Tabellenansicht, berechnete Felder und ausgewählte Pivot-Tabellen. Lass uns anfangen.

1. Abschnitte

Scheiben– Ein Point-and-Click-Tool zum Verfeinern der in einer Excel-Pivot-Tabelle enthaltenen Daten. Fügen Sie einen Slicer ein und Sie können die in der Pivot-Tabelle enthaltenen Daten problemlos ändern.

In diesem Beispiel habe ich ein Slice für den Typ eingefügt Artikel. Nachdem ich auf geklickt habe Rucksack, zeigt die Pivot-Tabelle nur diesen Parameter in der Tabelle an.

Oft entwickle ich Dashboard-Berichte, die von anderen verwendet werden. Durch das Hinzufügen von Slicern kann der Endbenutzer den Bericht nach seinen Wünschen anpassen.

Um einen Slicer hinzuzufügen, klicken Sie in die Pivot-Tabelle und suchen Sie die Registerkarte Analyse auf dem Excel-Menüband.

Aktivieren Sie mehrere Kontrollkästchen, um diese Spalten einzuschließen, von denen jede ein Segment darstellt.

Halten Strg auf der Tastatur, um mehrere Slicer-Elemente auszuwählen, die mehrere Auswahlen aus einer Spalte als PivotTable-Daten enthalten.

2. Zeitleiste

Zeitleisten Hierbei handelt es sich um einen speziellen Slicer-Typ, mit dem die in den PivotTable-Daten enthaltenen Daten angepasst werden. Wenn Ihre Daten Datumsangaben enthalten, müssen Sie Timeline unbedingt ausprobieren, um Daten aus bestimmten Zeiträumen auszuwählen.

Gehen Sie zum Abschnitt Analysieren > Zeitleiste einfügen um eine Zeitleiste hinzuzufügen, einen speziellen Slicer-Typ, der die in einer erweiterten Excel-PivotTable enthaltenen Daten basierend auf dem Datum verwaltet.

Tipp: Wenn diese Funktion bei Ihnen nicht funktioniert, stellen Sie sicher, dass Ihre Quelldaten ein Datum haben, das in der Tabelle als Datum formatiert ist.

Um eine Zeitleiste hinzuzufügen, stellen Sie sicher, dass Sie die PivotTable auswählen (klicken Sie hinein) und dann auf die Schaltfläche klicken Analysieren > Zeitleiste einfügen auf dem Excel-Menüband. Wählen Sie im Popup-Fenster eine Datumsspalte (oder mehrere Spalten) aus und klicken Sie OK um eine Zeitleiste zu erstellen.

Klicken und ziehen Sie im Zeitleistenfenster, um einen bestimmten Zeitbereich für die PivotTable auszuwählen.

Sobald die Zeitleiste eingefügt ist, können Sie auf die Ziehpunkte auf der Zeitleiste klicken und sie verschieben, um zu ändern, was in der Pivot-Tabelle enthalten ist.

Sie können die Funktionsweise der Zeitleiste ändern, indem Sie auf die Dropdown-Liste in der unteren rechten Ecke klicken. Sie können die Zeitleiste ändern, um beispielsweise vierteljährliche oder jährliche Daten anstelle bestimmter Daten anzuzeigen.

3. Tabellarische Ansicht

Die Standardansicht von PivotTables in Excel sieht aus wie ein Wasserfall; Wenn Sie weitere Felder in Zeilen ziehen, erstellt Excel weitere „Ebenen“ in den Daten.

Das Problem besteht darin, dass es schwierig ist, Formeln in PivotTables in der Standardansicht einzufügen. Wenn Sie Daten in einer PivotTable haben, diese aber als normale Tabelle anzeigen möchten, müssen Sie eine Tabellenansicht für die PivotTable verwenden.

Wählen Designer > Berichtslayout > In Tabellenform anzeigen um mit Ihrer Pivot-Tabelle als reguläre Tabelle zu arbeiten.

Warum sollten Sie eine Tabellenansicht verwenden? Wenn Sie Ihre PivotTable-Daten in einen klassischen Tabellenstil umwandeln, können Sie Datenformeln einfacher eingeben oder in einen separaten Bericht einfügen.

Verwenden Sie die Tabellenansicht, um Ihre PivotTable eher wie Standardzeilen und -spalten aussehen zu lassen.

Meistens bevorzuge ich die Tabellenansicht von Excel. Es sieht eher aus wie eine Standardtabelle und erleichtert das Schreiben von Formeln und das Arbeiten mit den darin enthaltenen Daten. Ich könnte diese Ansicht genauso gut übernehmen und in einen neuen Tab einfügen.

4. Berechnete Felder

Berechnete Felder sind eine Möglichkeit, einer PivotTable eine Spalte hinzuzufügen, die nicht in den Quelldaten enthalten ist. Sie können standardmäßige mathematische Operationen verwenden, um völlig neue Felder für die Arbeit zu erstellen. Nehmen Sie zwei vorhandene Spalten und erstellen Sie mithilfe der Mathematik völlig neue.

Nehmen wir an, wir haben Verkaufsdaten in einer Tabelle. Wir haben die Anzahl der verkauften Artikel und den Verkaufspreis für jeden Artikel. Dies ist der ideale Zeitpunkt, um ein berechnetes Feld zur Berechnung der Bestellsumme zu verwenden.

Um mit der Arbeit mit berechneten Feldern zu beginnen, klicken Sie zunächst in die PivotTable und suchen Sie dann auf der Multifunktionsleiste Analyse. Klicken Sie auf Menü Felder, Elemente und Mengen und dann auswählen Berechnetes Feld.

Verwenden Analyse > Felder, Elemente und Sätze > Berechnetes Feld um das berechnete Feld in Ihre PivotTable einzufügen.

Geben Sie im neuen Popup-Fenster zunächst dem berechneten Feld einen Namen. in meinem Fall nenne ich es Gesamtbestellung. Der Gesamtbestellwert ist die Menge multipliziert mit dem Preis jeder Einheit. Dann habe ich in der Liste der Felder in diesem Fenster auf das erste Titelfeld (Menge) doppelgeklickt.

Um die Gesamtkosten der Bestellung zu berechnen, habe ich das vorhandene Feld für den Einzelpreis multipliziert ( Einzelpreis) auf dem Mengenfeld ( Menge).

Nachdem ich den Namen dieses Feldes hinzugefügt habe, füge ich ein Multiplikationszeichen hinzu * , und doppelklicken Sie dann auf die Gesamtmenge (Menge). Lass uns weitermachen und klicken OK.

Jetzt hat Excel meine erweiterte PivotTable aktualisiert und ein neues berechnetes Feld hinzugefügt. Außerdem wird in der Feldliste eine Liste der PivotTables angezeigt, sodass Sie sie bei Bedarf an eine beliebige Stelle im Bericht ziehen können.

Wenn Sie keine zweispaltige Arithmetik verwenden möchten, können Sie auch eigene Rechenwerte in das berechnete Feld eingeben. Wenn ich beispielsweise bei jeder Bestellung einfach 5 % Umsatzsteuer hinzufügen möchte, könnte ich Folgendes in das berechnete Feld schreiben:

Ich habe die Gesamtkosten der Bestellung mit 1,05 multipliziert, um die Kosten einschließlich Umsatzsteuer zu berechnen. Sie können numerische Werte zusammen mit vorhandenen Feldern verwenden.

Berechnete Felder können grundsätzlich alle gängigen mathematischen Operatoren wie Addition, Subtraktion, Multiplikation und Division enthalten. Verwenden Sie diese berechneten Felder, wenn Sie die Originaldaten nicht aktualisieren möchten.

Gehe zu Einfügen > um diese Funktion auszuprobieren.

Diese Funktion ist so einfach zu bedienen, dass sie selbstverständlich ist. Sie können damit schnell erweiterte Pivot-Tabellen in Excel erstellen. Markieren Sie einfach Ihre Daten und gehen Sie zur Registerkarte Einfügen auf dem Excel-Menüband und wählen Sie Empfohlene PivotTables.

Das Popup-Fenster enthält viele Optionen zum Erstellen einer Pivot-Tabelle aus den Quelldaten. Klicken Sie auf die Miniaturansichten auf der linken Seite dieses Fensters, um die empfohlenen PivotTable-Optionen anzuzeigen, die von Excel bereitgestellt werden.

Die PivotTable-Empfehlungsfunktion bietet viele Optionen zur Analyse Ihrer Daten mit einem Klick.

Auch wenn dies eine erweiterte Funktion ist, die nur wenigen Benutzern bekannt ist, ist sie auch ein großartiges Tool zum Ausführen von Pivot-Tabellen. Nichts hindert Sie daran, die PivotTable zu ändern, indem Sie die Felder selbst ersetzen, aber es ist ein guter Ausgangspunkt.

Außerdem gefällt mir diese Funktion als Datenexplorationstool. Wenn ich zu Beginn der Datenerkundung nicht weiß, wonach ich suche, sind die empfohlenen Excel-Pivot-Tabellen oft aufschlussreicher als ich!

Wiederholen und weiter lernen (mit noch mehr Excel-Lektionen)

Diese fortgeschrittene Excel-Lektion hat Ihnen geholfen, tiefer in PivotTables einzutauchen, eine meiner Lieblingsfunktionen zum Analysieren und Anzeigen einer Excel-Tabelle. Ich verwende Pivot-Tabellen, um in großen Datenmengen die Bedeutung zu finden, damit ich bessere Entscheidungen treffen und Maßnahmen ergreifen kann.

Diese Lektionen bringen Ihre Kenntnisse im Umgang mit Excel und Pivot-Tabellen auf die nächste Stufe. Schau sie dir an:

• ExcelZoo bietet in seinem Artikel „10 Lektionen zum Beherrschen von Pivot-Tabellen“ (auf Englisch) einen großartigen Überblick über Pivot-Table-Techniken.
• Wir von Envato Tuts+ haben Pivot-Tabellen mit einem Anfänger-Tutorial behandelt.
• Für einen einfacheren Einstieg in Microsoft Excel schauen Sie sich unsere Tutorial-Reihe an.

Was möchten Sie noch über Pivot-Tabellen wissen? Teilen Sie mir Ihre Ideen oder Fragen in den Kommentaren unter diesem Tutorial mit.