Pivot-Tabelle
Eine Pivot-Tabelle ist eine Tabelle mit gruppierten Werten, die die einzelnen Elemente einer umfangreicheren Tabelle (z. B. aus einer Datenbank, Tabellenkalkulation oder einem Business-Intelligence-Programm) in einer oder mehreren diskreten Kategorien zusammenfasst. Diese Zusammenfassung kann Summen, Durchschnittswerte oder andere Statistiken enthalten, die die Pivot-Tabelle mithilfe einer ausgewählten Aggregationsfunktion, die auf die gruppierten Werte angewendet wird, zusammenfasst. ⓘ
Obwohl die Pivot-Tabelle ein allgemeiner Begriff ist, ließ sich Microsoft den Begriff PivotTable 1994 in den Vereinigten Staaten markenrechtlich schützen (die Marke wurde 2020 wieder gelöscht). ⓘ
Der Nutzen liegt darin, große Datenmengen auf überschaubare Größen zu reduzieren. ⓘ
Geschichte
In ihrem Buch Pivot Table Data Crunching bezeichnen Bill Jelen und Mike Alexander Pito Salas als den "Vater der Pivot-Tabellen". Bei der Arbeit an einem Konzept für ein neues Programm, das später zu Lotus Improv werden sollte, stellte Salas fest, dass Tabellenkalkulationen Datenmuster aufweisen. Ein Tool, das dem Benutzer helfen könnte, diese Muster zu erkennen, würde ihm helfen, schnell fortgeschrittene Datenmodelle zu erstellen. Mit Improv könnten die Benutzer Kategorien definieren und speichern und dann die Ansichten durch Ziehen der Kategorienamen mit der Maus ändern. Diese Kernfunktionalität würde das Modell für Pivot-Tabellen liefern. ⓘ
Lotus Development brachte Improv 1991 auf der NeXT-Plattform heraus. Einige Monate nach der Veröffentlichung von Improv veröffentlichte Brio Technology eine eigenständige Macintosh-Implementierung namens DataPivot (deren Technologie 1999 patentiert wurde). Borland kaufte die DataPivot-Technologie 1992 und implementierte sie in seine eigene Tabellenkalkulation, Quattro Pro. ⓘ
Im Jahr 1993 erschien die Microsoft Windows-Version von Improv. Anfang 1994 brachte Microsoft Excel 5 eine neue Funktionalität namens "PivotTable" auf den Markt. Microsoft verbesserte diese Funktion in späteren Versionen von Excel weiter:
- Excel 97 enthielt einen neuen und verbesserten PivotTable-Assistenten, die Möglichkeit, berechnete Felder zu erstellen, und neue Pivot-Cache-Objekte, die es Entwicklern ermöglichen, Visual Basic for Applications-Makros zum Erstellen und Ändern von Pivot-Tabellen zu schreiben.
- Excel 2000 führte "Pivot Charts" ein, um Pivot-Tabellendaten grafisch darzustellen ⓘ
2007 stellte die Oracle Corporation die Operatoren PIVOT und UNPIVOT in Oracle Database 11g zur Verfügung. ⓘ
Die Bezeichnung Pivot wurde zuerst 1991 in den USA von der Firma Brio Technology im Produkt DataPivot verwendet. Obwohl Pivot-Tabelle ein allgemein verwendeter Begriff ist, hat Microsoft 1994 PivotTable in den USA als Marke eingetragen (aufgegeben bzw. verfallen 2020). ⓘ
Mechanik
Bei der typischen Dateneingabe und -speicherung werden die Daten in der Regel in flachen Tabellen angezeigt, d. h. sie bestehen nur aus Spalten und Zeilen, wie im folgenden Teil einer Beispieltabelle mit Daten zu Hemdentypen:
| A | B | C | D | E | F | G ⓘ | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Geschlecht | Stil | Schiffsdatum | Einheiten | Preis | Kosten |
| 2 | Osten | Junge | Abschlag | 2005-01-31 | 12 | 11.04 | 10.42 |
| 3 | Osten | Junge | Golf | 2005-01-31 | 12 | 13.00 | 12.60 |
| 4 | Osten | Junge | Fancy | 2005-01-31 | 12 | 11.96 | 11.74 |
| 5 | Osten | Mädchen | Abschlag | 2005-01-31 | 10 | 11.27 | 10.56 |
| 6 | Osten | Mädchen | Golf | 2005-01-31 | 10 | 12.12 | 11.95 |
| 7 | Osten | Mädchen | Fancy | 2005-01-31 | 10 | 13.74 | 13.33 |
| 8 | West | Junge | Abschlag | 2005-01-31 | 11 | 11.44 | 10.94 |
| 9 | West | Junge | Golf | 2005-01-31 | 11 | 12.63 | 11.73 |
| 10 | West | Junge | Fancy | 2005-01-31 | 11 | 12.06 | 11.51 |
| 11 | West | Mädchen | Abschlag | 2005-01-31 | 15 | 13.42 | 13.29 |
| 12 | West | Mädchen | Golf | 2005-01-31 | 15 | 11.48 | 10.67 |
| ⋮ | … | … | … | … | … | … | … |
Tabellen wie diese können zwar viele Datenelemente enthalten, aber es kann schwierig sein, aus ihnen zusammengefasste Informationen zu erhalten. Eine Pivot-Tabelle kann helfen, die Daten schnell zusammenzufassen und die gewünschten Informationen hervorzuheben. Die Verwendung einer Pivot-Tabelle ist sehr vielfältig und hängt von der jeweiligen Situation ab. Die erste Frage, die man sich stellen muss, ist: "Was suche ich?" In unserem Beispiel fragen wir: "Wie viele Einheiten haben wir in jeder Region für jedes Versanddatum verkauft?":
| Summe der Einheiten | Versanddatum ▼. ⓘ | |||||
|---|---|---|---|---|---|---|
| Region ▼. | 2005-01-31 | 2005-02-28 | 2005-03-31 | 2005-04-30 | 2005-05-31 | 2005-06-30 |
| Osten | 66 | 80 | 102 | 116 | 127 | 125 |
| Norden | 96 | 117 | 138 | 151 | 154 | 156 |
| Süd | 123 | 141 | 157 | 178 | 191 | 202 |
| West | 78 | 97 | 117 | 136 | 150 | 157 |
| (leer) | ||||||
| Gesamtsumme | 363 | 435 | 514 | 581 | 622 | 640 |
Eine Pivot-Tabelle besteht normalerweise aus Zeilen-, Spalten- und Daten- (oder Fakten-) Feldern. In diesem Fall ist die Spalte das Versanddatum, die Zeile die Region und die Daten, die wir sehen möchten, sind die (Summe der) Einheiten. Diese Felder ermöglichen verschiedene Arten von Aggregationen, darunter: Summe, Durchschnitt, Standardabweichung, Anzahl usw. In diesem Fall wird die Gesamtzahl der versendeten Einheiten mit Hilfe einer Summenaggregation angezeigt. ⓘ
Implementierung
Anhand des obigen Beispiels findet die Software alle eindeutigen Werte für Region. In diesem Fall sind dies: Nord, Süd, Ost, West. Außerdem werden alle eindeutigen Werte für das Versanddatum ermittelt. Auf der Grundlage des Aggregationstyps Summe fasst sie die Tatsache, die Mengen der Einheit, zusammen und zeigt sie in einem mehrdimensionalen Diagramm an. Im obigen Beispiel ist das erste Datum 66. Diese Zahl wurde ermittelt, indem alle Datensätze gefunden wurden, bei denen sowohl die Region Ost als auch das Versanddatum der 31.1.2005 war, und die Einheiten dieser Datensatzsammlung (d. h. die Zellen E2 bis E7) addiert wurden, um ein Endergebnis zu erhalten. ⓘ
Pivot-Tabellen werden nicht automatisch erstellt. In Microsoft Excel beispielsweise muss man zunächst die gesamten Daten in der ursprünglichen Tabelle markieren und dann auf der Registerkarte Einfügen die Option "Pivot-Tabelle" (oder "Pivot-Diagramm") auswählen. Der Benutzer hat dann die Möglichkeit, die Pivot-Tabelle entweder in ein bestehendes Blatt einzufügen oder ein neues Blatt zu erstellen, das die Pivot-Tabelle enthält. Dem Benutzer wird eine Pivot-Tabellenfeldliste zur Verfügung gestellt, in der alle in den Daten vorhandenen Spaltenüberschriften aufgeführt sind. Wenn eine Tabelle beispielsweise Verkaufsdaten eines Unternehmens enthält, könnte sie Verkaufsdatum, Verkäufer, verkaufter Artikel, Farbe des Artikels, verkaufte Einheiten, Preis pro Einheit und Gesamtpreis enthalten. Dadurch werden die Daten leichter zugänglich. ⓘ
| Datum des Verkaufs | Verkaufsperson | Verkaufter Artikel | Farbe des Artikels | Verkaufte Einheiten | Preis pro Einheit | Gesamtpreis ⓘ |
|---|---|---|---|---|---|---|
| 2013-10-01 | Jones | Notebook | Schwarz | 8 | 25000 | 200000 |
| 2013-10-02 | Prinz | Laptop | Rot | 4 | 35000 | 140000 |
| 2013-10-03 | Georg | Maus | Rot | 6 | 850 | 5100 |
| 2013-10-04 | Larry | Notebook | Weiß | 10 | 27000 | 270000 |
| 2013-10-05 | Jones | Maus | Schwarz | 4 | 700 | 2800 |
Die zu erstellenden Felder werden auf der rechten Seite des Arbeitsblatts angezeigt. Standardmäßig wird das Pivot-Tabellen-Layoutdesign unter dieser Liste angezeigt. ⓘ
Pivot-Tabellenfelder sind die Bausteine von Pivot-Tabellen. Jedes der Felder aus der Liste kann auf dieses Layout gezogen werden, das vier Optionen bietet:
- Filter
- Spalten
- Zeilen
- Werte ⓘ
Einige Verwendungszwecke von Pivot-Tabellen beziehen sich auf die Analyse von Fragebögen mit optionalen Antworten, aber einige Implementierungen von Pivot-Tabellen lassen diese Anwendungsfälle nicht zu. Zum Beispiel ist die Implementierung in LibreOffice Calc seit 2012 nicht in der Lage, leere Zellen zu verarbeiten. ⓘ
Filter
Berichtsfilter werden verwendet, um einen Filter auf eine gesamte Tabelle anzuwenden. Wenn zum Beispiel das Feld "Farbe des Artikels" in diesen Bereich gezogen wird, wird in die konstruierte Tabelle ein Berichtsfilter über der Tabelle eingefügt. Dieser Berichtsfilter enthält Dropdown-Optionen (im obigen Beispiel Schwarz, Rot und Weiß). Wenn eine Option aus dieser Dropdown-Liste ausgewählt wird (in diesem Beispiel "Schwarz"), enthält die sichtbare Tabelle nur die Daten der Zeilen, die die "Farbe des Elements= Schwarz" haben. ⓘ
Spalten
Spaltenbeschriftungen werden verwendet, um einen Filter auf eine oder mehrere Spalten anzuwenden, die in der Pivot-Tabelle angezeigt werden sollen. Wenn zum Beispiel das Feld "Verkäufer" in diesen Bereich gezogen wird, enthält die Tabelle Werte aus der Spalte "Verkäufer", d.h. die Anzahl der Spalten entspricht der Anzahl der Spalten "Verkäufer". Es wird auch eine zusätzliche Spalte "Total" geben. Im obigen Beispiel werden mit dieser Anweisung fünf Spalten in der Tabelle erstellt - eine für jeden Verkäufer und die Gesamtsumme. Über den Daten - den Spaltenbeschriftungen - befindet sich ein Filter, mit dem man einen bestimmten Verkäufer für die Pivot-Tabelle auswählen oder die Auswahl aufheben kann. ⓘ
Diese Tabelle enthält keine numerischen Werte, da kein numerisches Feld ausgewählt ist, aber wenn es ausgewählt wird, werden die Werte automatisch in der Spalte "Gesamtsumme" aktualisiert. ⓘ
Zeilen
Zeilenbeschriftungen werden verwendet, um einen Filter auf eine oder mehrere Zeilen anzuwenden, die in der Pivot-Tabelle angezeigt werden sollen. Wenn zum Beispiel das Feld "Verkäufer" in diesen Bereich gezogen wird, enthält die andere Ausgabetabelle Werte aus der Spalte "Verkäufer", d.h. die Anzahl der Zeilen entspricht der Anzahl der Zeilen "Verkäufer". Es wird auch eine Zeile "Gesamtsumme" hinzugefügt. Im obigen Beispiel werden mit dieser Anweisung fünf Zeilen in der Tabelle erstellt - eine für jeden Verkäufer und die Gesamtsumme. Über den Daten - Zeilenbeschriftungen - befindet sich ein Filter, mit dem man einen bestimmten Verkäufer für die Pivot-Tabelle auswählen oder die Auswahl aufheben kann. ⓘ
Diese Tabelle enthält keine numerischen Werte, da kein numerisches Feld ausgewählt ist, aber wenn sie ausgewählt wird, werden die Werte automatisch in der Zeile "Gesamtsumme" aktualisiert. ⓘ
Werte
Dazu wird normalerweise ein Feld mit numerischen Werten verwendet, die für verschiedene Arten von Berechnungen genutzt werden können. Es wäre aber auch nicht verkehrt, Textwerte zu verwenden; anstelle der Summe wird dann eine Anzahl angegeben. Wenn also im obigen Beispiel das Feld "Verkaufte Einheiten" zusammen mit der Zeilenbeschriftung "Verkäufer" in diesen Bereich gezogen wird, fügt die Anweisung eine neue Spalte "Summe der verkauften Einheiten" hinzu, die Werte für jeden Verkäufer enthält. ⓘ
| Zeilenbeschriftungen | Summe der verkauften Einheiten ⓘ |
|---|---|
| Jones | 12 |
| Prinz | 4 |
| Georg | 6 |
| Larry | 10 |
| Gesamtsumme | 32 |
Anwendungsunterstützung
Aus Performancegründen werden Pivot-Tabellen im Allgemeinen nicht automatisch bei jeder Änderung der Ausgangsdaten aktualisiert, wie dies bei sonstigen Funktionen in Tabellenkalkulationen üblich ist. Die Aktualisierung muss der Benutzer bei Bedarf manuell starten. ⓘ
Die Auswertung erfolgt nach Datentypen, so werden z. B. die Zahl 12.03, der Text 12.03 und das Datum 12.03 separat ausgewertet. Bei nicht konsistent gepflegter Datengrundlage kann dies zu Fehlern führen. ⓘ
Bei einigen Office-Paketen kann man Pivot-Tabellen grafisch als interaktiven Pivot-Chart (deutsch: Pivot-Diagramm) darstellen. ⓘ
Tabellenkalkulationen
- Microsoft Excel unterstützt Pivot-Tabellen, die durch PivotCharts visualisiert werden können.
- Apache POI "Erstellen einer Pivot-Tabelle".
- LibreOffice Calc und Openoffice Calc unterstützen Pivot-Tabellen. Vor Version 3.4 wurde diese Funktion "DataPilot" genannt.
- Calligra Sheets unterstützt Pivot-Tabellen.
- Google Sheets unterstützt Pivot-Tabellen von Haus aus.
- Numbers von Apple Inc. unterstützt Pivot-Tabellen seit der Version 11.2. ⓘ
Datenbank-Unterstützung
Pivot-Tabellen ermöglichen es, in einer Tabellenkalkulation Auswertungen durchzuführen, die in der Datenbankabfragesprache SQL mit einer Group-by-Klausel erzielt werden können. Mit den üblichen Funktionen, die Tabellenkalkulationen zur Berechnung von Zellinhalten bereitstellen, sind solche Gruppierungen nur sehr eingeschränkt oder gar nicht möglich. ⓘ
Die verschiedenen Typen von Feldern einer Pivot-Tabelle entsprechen bestimmten Teilen einer SQL-Abfrage:
- Zeilen- und Spaltenfelder von Pivot-Tabellen entsprechen Feldern in der Group-by-Klausel.
- Datenfelder entsprechen Ausdrücken im Select-Teil des SQL-Befehls. Diese Ausdrücke enthalten notwendigerweise Aggregationsfunktionen wie z. B. die Summenfunktion.
- Seitenfelder entsprechen einfachen Bedingungen in der Having- bzw. Where-Klausel des SQL-Befehls. ⓘ
Nicht alle Möglichkeiten, die SQL oder die Tabellenkalkulation selbst bieten, werden von Pivot-Tabellen unterstützt. Zum Beispiel stehen in Microsoft Excel nur einige vordefinierte Aggregationsfunktionen zur Verfügung; Median und 95-%-Quantil fehlen etwa. ⓘ
Datenbankabfrageprogramme sind im Allgemeinen flexibler als Pivot-Tabellen, d. h. mit SQL können mehr Fragen beantwortet werden. Die Abfrageprogramme bieten jedoch typischerweise weniger Möglichkeiten zur ansprechenden Aufbereitung der Ergebnisse und sind weniger komfortabel zu bedienen. Viele Programme können Datensätze nicht als Kreuztabelle ausgeben und spezielle Formatierungen sind nur in Handarbeit (d. h. ohne Assistenten und Steuerelemente) oder gar nicht möglich. ⓘ
- PostgreSQL, ein objektrelationales Datenbankmanagementsystem, ermöglicht die Erstellung von Pivot-Tabellen mit dem tablefunc-Modul.
- MariaDB, eine MySQL-Abspaltung, ermöglicht Pivot-Tabellen unter Verwendung der CONNECT-Speicher-Engine.
- Microsoft Access unterstützt Pivot-Abfragen unter dem Namen "Crosstab"-Abfrage.
- Microsoft SQL Server unterstützt Pivot ab SQL Server 2016 mit den Schlüsselwörtern FROM...PIVOT
- Oracle Database unterstützt die PIVOT-Operation.
- Einige gängige Datenbanken, die die Pivot-Funktionalität nicht direkt unterstützen, wie z. B. SQLite, können die Pivot-Funktionalität normalerweise durch eingebettete Funktionen, dynamisches SQL oder Unterabfragen simulieren. Das Problem beim Pivoting besteht in solchen Fällen normalerweise darin, dass die Anzahl der Ausgabespalten zum Zeitpunkt der Ausführung der Abfrage bekannt sein muss; beim Pivoting ist dies nicht möglich, da die Anzahl der Spalten auf den Daten selbst basiert. Daher müssen die Namen fest kodiert werden, oder die auszuführende Abfrage muss dynamisch (d. h. vor jeder Verwendung) auf der Grundlage der Daten erstellt werden. ⓘ
Webanwendungen
- ZK, ein Ajax-Framework, erlaubt auch die Einbettung von Pivot-Tabellen in Webanwendungen. ⓘ
Programmiersprachen und Bibliotheken
- Programmiersprachen und Bibliotheken, die für die Arbeit mit tabellarischen Daten geeignet sind, enthalten Funktionen, die die Erstellung und Bearbeitung von Pivot-Tabellen ermöglichen. Das Python-Datenanalyse-Toolkit pandas verfügt über die Funktion pivot_table und die xs-Methode, die nützlich ist, um Ausschnitte von Pivot-Tabellen zu erhalten. In R gibt es das Tidyverse-Metapaket, das eine Sammlung von Tools mit Pivot-Tabellen-Funktionalität enthält, sowie das pivottabler-Paket. ⓘ
Analytische Online-Verarbeitung
Excel-Pivot-Tabellen bieten die Möglichkeit, Daten direkt von einem OLAP-Server (Online Analytical Processing) abzufragen, anstatt die Daten aus einer Excel-Tabelle zu beziehen. In dieser Konfiguration ist eine Pivot-Tabelle ein einfacher Client für einen OLAP-Server. Die PivotTable von Excel ermöglicht nicht nur die Verbindung mit dem Analysis Service von Microsoft, sondern mit jedem OLAP-Server, der dem XML for Analysis (XMLA)-Standard entspricht. ⓘ
Struktur
Eine Pivot-Tabelle besteht aus mehreren Bereichen, von denen jeder beliebige Felder (Spaltenüberschriften) der Originaldaten aufnehmen kann. Typischerweise werden die erforderlichen Felder bei Erstellung der Pivot-Tabelle aus einer Liste ausgewählt und mit der Maus in den gewünschten Bereich gezogen. ⓘ
Datenbanken
Das Prinzip der Pivotierung liegt auch den Ansichten in MOLAP-Datenbanken zugrunde. Diese nichtrelationalen Datenbanken sind multidimensional. Für Datenanalysen sind zwei Dimensionen in der Ansichtsebene, die weiteren Dimensionen befinden sich wie in der Pivotsicht aggregiert im Hintergrund. ⓘ