Pivot Tabelle in Excel erstellen: Schritt für Schritt

Eine Pivot Tabelle gruppiert die Zeilen einer Tabelle nach einer Kategorie, etwa Region, und bildet für jede Gruppe die Summe einer Zahl, etwa Sales, ohne Formeln. Um eine zu erstellen, klickst du in eine Zelle der Daten, gehst zu Einfügen > PivotTable, drückst OK und ziehst Region in Zeilen und Sales in Werte. Die Tabelle unten ist keine Pivot Tabelle: Sie baut dieselbe Übersicht mit Formeln, damit du zusehen kannst, wie sich die Summen ändern. Sie zeigt die englischen Funktionsnamen, =UNIQUE(A2:A9) und SUMIF; du kannst die Formeln dort auch deutsch eingeben, mit Semikolons: =EINDEUTIG(A2:A9) und =SUMMEWENN($A$2:$A$9;E2;$C$2:$C$9).

FormelnZurücksetzen

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SUMMEWENN($A$2:$A$9;E2;$C$2:$C$9)

EINDEUTIG (englisch UNIQUE) listet jede Region einmal, und SUMMEWENN (englisch SUMIF) bildet ihre Summe: North 455, South 305 und East 170, also 49 %, 33 % und 18 % der insgesamt 930. Ändere C3 in 185, und die Summe für South und alle drei Prozentwerte ziehen sofort mit. Eine Pivot Tabelle würde dieselben Zahlen zeigen, aber erst, nachdem du sie aktualisiert hast.

So erstellst du eine Pivot Tabelle

Bevor du anfängst, prüf die Quelldaten: eine Überschriftenzeile mit einem Namen in jeder Spalte, ein Datensatz pro Zeile, keine leeren Zeilen oder Spalten mittendrin und keine Zwischensummenzeilen.

1. Klick in eine beliebige Zelle der Daten.
2. Geh zu Einfügen > PivotTable (in manchen Versionen Einfügen > PivotTable > Aus Tabelle/Bereich).
3. Excel trägt den Bereich ein. Wähle Neues Arbeitsblatt und drück OK.
4. Eine leere Pivot Tabelle erscheint, rechts daneben der Bereich PivotTable-Felder mit deinen Spaltenüberschriften.
5. Zieh Region in das Feld Zeilen und Sales in das Feld Werte. Die Pivot Tabelle zeigt jede Region einmal, daneben Summe von Sales, und eine Zeile Gesamtergebnis.
6. Um zu ändern, was angezeigt wird, ziehst du Felder zwischen den Feldern hin und her oder aus dem Bereich heraus.

Weißt du nicht, wo du anfangen sollst, zeigt Einfügen > Empfohlene PivotTables einige fertige Layouts für deine Daten. Auf dem Mac ist das Menü dasselbe: Einfügen > PivotTable.

Zeilen, Spalten, Werte und Filter

Der Bereich PivotTable-Felder hat vier Felder, und jede Pivot Tabelle ist eine Entscheidung, welche Spalte in welches Feld kommt:

• Zeilen: die Kategorien an der linken Seite, eine Zeile pro verschiedenem Wert (Region).
• Spalten: Kategorien oben quer, eine Spalte pro verschiedenem Wert (Product).
• Werte: die Zahlen, die für jede Kombination berechnet werden. Summe ist der Standard für eine Zahlenspalte; Anzahl, Mittelwert, Max, Min und weitere findest du in den Wertfeldeinstellungen.
• Filter: ein Feld, nach dem die ganze Pivot Tabelle gefiltert wird, als Dropdown darüber angezeigt.

Mit Region in Zeilen, Product in Spalten und Sales in Werte sieht die Pivot Tabelle für die Daten oben so aus (in englischem Excel; deutsches Excel beschriftet mit Summe von Sales, Spaltenbeschriftungen, Zeilenbeschriftungen und Gesamtergebnis):

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

Die Formelvariante dieses Layouts listet die Regionen mit EINDEUTIG nach unten, die Produkte mit MTRANS(EINDEUTIG()) quer und berechnet jede Zelle des Rasters mit einem einzigen SUMMEWENNS, das die beiden Listen als Kriterien nimmt:

FormelnZurücksetzen

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SUMMEWENNS(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1)

E2 lässt North, South und East nach unten überlaufen, F1 lässt Apple und Pear quer überlaufen, und das SUMMEWENNS in F2, in deutschem Excel =SUMMEWENNS(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1), füllt das Raster aus 3 mal 2 Zellen dazwischen: eine Summe für jedes Paar aus Region und Produkt. Ändere B5 von Apple in Pear, und beide Zellen für East ändern sich. Die Reihenfolge ist hier die, in der die Werte zuerst vorkommen; eine Pivot Tabelle sortiert ihre Beschriftungen alphabetisch.

Anzahl, Mittelwert oder Prozent statt Summe

Klick in der Pivot Tabelle auf das Feld im Feld Werte und wähle Wertfeldeinstellungen. Die Registerkarte Werte zusammenfassen nach wechselt zwischen Summe, Anzahl, Mittelwert, Max und Min; die Registerkarte Werte anzeigen als macht aus den Zahlen % des Gesamtergebnisses, % des Spaltengesamtergebnisses, ein laufendes Ergebnis und anderes. Jede Formel hat ein direktes Gegenstück:

FormelnZurücksetzen

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =EINDEUTIG(A2:A9)

North hat 3 Bestellungen mit einem Mittelwert von 151.7, South 3 mit 101.7, East 2 mit 85.0. Die Formeln heißen in deutschem Excel ZÄHLENWENN und MITTELWERTWENN. Die Spalte % des Gesamtergebnisses steht in der ersten Tabelle auf dieser Seite.

Die Übersicht nach einem Produkt filtern

Das Feld Filter setzt ein Dropdown über die Pivot Tabelle. Die Formelvariante ist eine Zelle mit einer Dropdown-Liste und SUMMEWENNS, das SUMMEWENN um eine weitere Bedingung ergänzt. Wähle in F1 ein Produkt:

FormelnZurücksetzen

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.

Mit Apple als Auswahl zeigt North 215, South 150 und East 60. Wähle Pear, und sie ändern sich in 240, 155 und 110. In deutschem Excel lautet die Formel in F4 =SUMMEWENNS($C$2:$C$9;$A$2:$A$9;E4;$B$2:$B$9;$F$1). Weitere Bedingungen stehen unter SUMMEWENNS, und wie du die Liste in Excel hinzufügst, auf der Seite zur Dropdown-Liste.

Eine Pivot Tabelle aktualisieren

Eine Pivot Tabelle behält eine Kopie der Quelldaten (den Pivot-Cache) und rechnet nicht neu, wenn sich eine Zelle in der Quelle ändert. Nachdem du die Daten bearbeitet hast:

• Klick mit der rechten Maustaste irgendwo in die Pivot Tabelle und wähle Aktualisieren, oder drück unter Windows Alt+F5.
• Daten > Alle aktualisieren (Strg+Alt+F5) aktualisiert jede Pivot Tabelle der Arbeitsmappe.
• Um bei jedem Öffnen der Datei zu aktualisieren, klickst du mit der rechten Maustaste auf die Pivot Tabelle, wählst PivotTable-Optionen und hakst auf der Registerkarte Daten Aktualisieren beim Öffnen der Datei an.

Neue Zeilen, die unter dem Quellbereich hinzugefügt werden, sind nicht dabei, auch nach einem Aktualisieren nicht. Ändere den Bereich unter PivotTable-Analyse > Datenquelle ändern, oder, besser, mach aus der Quelle eine Tabelle, bevor du die Pivot Tabelle erstellst: Markiere die Daten und drück Strg+T, oder nimm Einfügen > Tabelle. Eine Tabelle wächst, wenn du Zeilen hinzufügst, und die Pivot Tabelle übernimmt sie beim nächsten Aktualisieren.

Jede Region mit einer Formel summieren

FormelnZurücksetzen

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.

Du bist dran: Bilde in F2 mit einer Formel die Summe der Umsätze für jede Region, die in E2:E4 steht.

PrüfenLösung zeigen

Die Lösung lässt 455, 305 und 170 überlaufen, in deutschem Excel mit =SUMMEWENN(A2:A9;E2:E4;C2:C9). Gibst du SUMMEWENN die ganze Liste E2:E4 als Kriterium, liefert es eine Summe pro Region, also musst du nichts nach unten ausfüllen. Excel 2019 und älter haben weder EINDEUTIG noch Überlaufen: Tippe die Regionen in E2:E4 und füll =SUMMEWENN($A$2:$A$9;E2;$C$2:$C$9) nach unten aus. Ohne die $-Zeichen wandern die Bereiche mit jeder Zeile nach unten, und die Summen stimmen nicht.

GRUPPIERENNACH und PIVOTMIT: eine Pivot Tabelle in einer Formel

Excel für Microsoft 365 hat zwei Funktionen, die eine ganze Übersicht aus einer Formel bauen und wie jede Formel neu rechnen, ohne Aktualisieren. Sie brauchen ein aktuelles Microsoft-365-Abonnement. Für die Daten oben, in englischer Schreibweise:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

In deutschem Excel: =GRUPPIERENNACH(A2:A9;C2:C9;SUMME) und =PIVOTMIT(A2:A9;B2:B9;C2:C9;SUMME). GRUPPIERENNACH (englisch GROUPBY) nimmt das Zeilenfeld, die Werte und die Funktion (SUMME, ANZAHL2, MITTELWERT, MAX, PERCENTOF). PIVOTMIT (englisch PIVOTBY) ergänzt dazwischen ein Spaltenfeld. Beide sortieren die Beschriftungen und fügen Summenzeilen hinzu, wie eine Pivot Tabelle.

Pivot Tabelle oder Formeln: was du nimmst

Nimm eine Pivot Tabelle, um Daten zu erkunden und eine Frage einmal zu beantworten; nimm Formeln für eine Übersicht, die in einem Bericht steht, andere Formeln speist und immer aktuell sein muss. Um die Zahlen einer Pivot Tabelle zu prüfen, baust du eine ihrer Zellen mit SUMMEWENNS nach: Stimmen die beiden nicht überein, muss die Pivot Tabelle meist aktualisiert werden, oder ihr Quellbereich ist zu kurz.