Pivot-Tabellen gelten als fortgeschritten. Das sind sie nicht. Eine Pivot-Tabelle beantwortet eine bestimmte Art von Frage, nämlich "Wie viel davon pro dem hier?", und sie tut das mit ein paar Klicks statt mit einer Formel. Umsatz pro Region. Anzahl Bestellungen pro Kunde. Durchschnittliche Lieferzeit pro Versanddienstleister und Monat.
Hier siehst du, was eine Pivot-Tabelle tatsächlich berechnet, wie du eine Schritt für Schritt erstellst und wie du die Handvoll Probleme löst, über die fast alle stolpern. Du bekommst auch die Formeln, die dieselben Zahlen liefern. Wenn du sie kennst, liest du eine Pivot-Tabelle viel sicherer, und manche Jobs, Tests und Tabellen verlangen genau die Formelvariante. Wenn du die Anleitung gelesen hast, übe mit echten Daten in den 15 Pivot-Tabellen-Übungen mit Lösungen und schreib die Formelvariante selbst in den Übungen zu Auswertungstabellen.
Was eine Pivot-Tabelle macht
Du startest mit einer Tabelle, in der jede Zeile ein Datensatz ist: eine Bestellung, eine Buchung, eine Zeiterfassung. Eine Pivot-Tabelle fasst die Zeilen nach den Werten einer Spalte zu Gruppen zusammen und wertet dann für jede Gruppe eine andere Spalte aus.
Stell dir acht Bestellungen mit Region, Produkt und Betrag vor. Legst du Region in Zeilen und Umsatz in Werte, bekommst du eine Zeile pro Region mit der Summe ihres Umsatzes. Legst du zusätzlich Produkt in Spalten, bekommst du ein Raster: die Regionen an der Seite, die Produkte oben und eine Summe in jeder Zelle. Das ist die ganze Idee. Der Rest sind Zusatzfunktionen, die du lernen kannst, sobald du sie brauchst.
Unter der Haube ist jede Zahl im Raster eine bedingte Summe, eine bedingte Anzahl oder ein bedingter Durchschnitt: "die Summe von Umsatz, wo die Region Nord und das Produkt Schuhe ist". Genau das berechnet SUMMEWENNS (SUMIFS), und deshalb liefern die Formeln weiter unten dieselben Zahlen.
Bereite deine Daten vor
Die meisten Probleme mit Pivot-Tabellen sind in Wahrheit Probleme mit den Daten. Bevor du eine einfügst, prüf, ob deine Daten diese Regeln erfüllen:
- Eine Überschriftenzeile mit einem eindeutigen Namen in jeder Spalte und ohne leere Überschriften. Die Pivot-Tabelle nutzt die Überschriften als Feldnamen.
- Ein Datensatz pro Zeile. Keine Zwischensummen, keine leeren Zeilen mittendrin und keine Notizen unter den Daten.
- Eine Art von Wert pro Spalte. Die Spalte Umsatz darf nur Zahlen enthalten. Ein einziges "k. A." reicht, damit die Pivot-Tabelle die ganze Spalte als Text behandelt.
- Keine verbundenen Zellen. Heb die Verbindung auf und füll die Werte aus.
Der schnellste Weg, deine Daten vorzubereiten: Klick irgendwo hinein und drück Strg+T, um eine Excel-Tabelle zu erstellen. Eine Tabelle wächst von selbst, wenn du Zeilen hinzufügst, also bekommt eine Pivot-Tabelle, die darauf aufbaut, die neuen Daten beim Aktualisieren mit.
Pivot-Tabelle erstellen, Schritt für Schritt
So geht es in Excel unter Windows und auf dem Mac. Die Menüs sehen von Version zu Version etwas anders aus, aber die Schritte sind dieselben.
- Klick in eine Zelle deiner Daten. Du musst nicht alles markieren. Excel findet die Ränder selbst.
- Wähle Einfügen und dann PivotTable. Excel schlägt den Bereich oder die Tabelle vor und bietet an, die Pivot-Tabelle auf einem neuen Blatt anzulegen. Sag zu beidem ja.
- Schau dir den Bereich PivotTable-Felder an. Oben stehen deine Spaltenüberschriften. Unten gibt es vier Bereiche: Filter, Spalten, Zeilen und Werte.
- Zieh ein Feld in Zeilen. Zieh Region in Zeilen, dann bekommst du eine Zeile pro Region.
- Zieh ein Zahlenfeld in Werte. Zieh Umsatz in Werte. Excel fügt "Summe von Umsatz" hinzu, also die Summe pro Region, und ein Gesamtergebnis.
- Zieh bei Bedarf ein Feld in Spalten. Zieh Produkt in Spalten, und die Summenspalte wird zu einem Raster aus Regionen und Produkten.
- Zieh bei Bedarf ein Feld in Filter. Ein Feld in Filter gibt dir eine Dropdown-Liste über der Pivot-Tabelle, mit der du zum Beispiel nur ein Jahr anzeigen kannst.
Das ist alles. Willst du das Layout ändern, ziehst du die Felder zwischen den vier Bereichen hin und her. Willst du ein Feld entfernen, ziehst du es aus dem Bereich heraus.
Summe, Anzahl, Mittelwert und mehr
Standardmäßig summiert Excel Zahlenfelder und zählt Textfelder. Um das zu ändern, klick auf das Feld in Werte, wähle Wertfeldeinstellungen und dann Summe, Anzahl, Mittelwert, Maximum, Minimum oder eine der anderen Optionen.
Hier siehst du, was die gängigsten Einstellungen berechnen, geschrieben als die Formel, die du stattdessen verwenden würdest. Zuerst die Summe pro Region:
=SUMMEWENN(1$A$2:$A$9;2E2;3$C$2:$C$9)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Produkt | Umsatz | Region | Summe von Umsatz | |
| 2 | Nord | Schuhe | 420 | Nord | 830 | |
| 3 | Süd | Taschen | 310 | Süd | ||
| 4 | Nord | Taschen | 150 | Ost | ||
| 5 | Ost | Schuhe | 280 | |||
| 6 | Süd | Schuhe | 500 | |||
| 7 | Nord | Schuhe | 260 | |||
| 8 | Ost | Taschen | 190 | |||
| 9 | Süd | Taschen | 240 |
In einfachen Worten: Addiere 3den Umsatz in C2:C9 in den Zeilen, in denen 1die Region in A2:A9 2die Region in E2 ist.
Die Summe für Nord, so wie die Spalte Summe von Umsatz in einer Pivot-Tabelle sie zeigt. Kopierst du die Formel nach unten, ergibt sie 1.050 für Süd und 470 für Ost.
Anzahl sagt dir, wie viele Zeilen in jeder Gruppe stehen, egal wie hoch die Beträge sind:
=ZÄHLENWENN(1$A$2:$A$9;2E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Produkt | Umsatz | Region | Anzahl von Umsatz | |
| 2 | Nord | Schuhe | 420 | Nord | 3 | |
| 3 | Süd | Taschen | 310 | |||
| 4 | Nord | Taschen | 150 | |||
| 5 | Ost | Schuhe | 280 | |||
| 6 | Süd | Schuhe | 500 | |||
| 7 | Nord | Schuhe | 260 | |||
| 8 | Ost | Taschen | 190 | |||
| 9 | Süd | Taschen | 240 |
In einfachen Worten: Zähle die Zellen 1in A2:A9, die gleich 2der Region in E2 sind.
Anzahl von Umsatz: wie viele Zeilen Nord hat, egal wie hoch die Beträge sind.
Und Mittelwert teilt die Summe durch die Anzahl:
=MITTELWERTWENN(1$A$2:$A$9;2E2;3$C$2:$C$9)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Produkt | Umsatz | Region | Mittelwert von Umsatz | |
| 2 | Nord | Schuhe | 420 | Süd | 350 | |
| 3 | Süd | Taschen | 310 | |||
| 4 | Nord | Taschen | 150 | |||
| 5 | Ost | Schuhe | 280 | |||
| 6 | Süd | Schuhe | 500 | |||
| 7 | Nord | Schuhe | 260 | |||
| 8 | Ost | Taschen | 190 | |||
| 9 | Süd | Taschen | 240 |
In einfachen Worten: Berechne den Durchschnitt 3des Umsatzes in C2:C9 in den Zeilen, in denen 1die Region in A2:A9 2die Region in E2 ist.
Mittelwert von Umsatz für Süd: 1.050 verteilt auf drei Bestellungen.
Zwei weitere Einstellungen solltest du kennen. Werte anzeigen als (im selben Dialog) macht aus Summen Prozente: % des Gesamtergebnisses, % des Spaltenergebnisses, % des Zeilenergebnisses oder die Differenz zum Vormonat. Und du kannst dasselbe Feld zweimal in Werte legen, einmal als Summe und einmal als Anzahl, damit du beides nebeneinander siehst.
Prozent vom Gesamtergebnis
"% des Gesamtergebnisses" teilt die Summe jeder Gruppe durch die Summe des Ganzen. Als Formel ist das ein SUMMEWENN geteilt durch eine SUMME:
=RUNDEN(1SUMMEWENN($A$2:$A$9;E2;$C$2:$C$9)/SUMME($C$2:$C$9);23)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Produkt | Umsatz | Region | % vom Gesamt | |
| 2 | Nord | Schuhe | 420 | Nord | 0,353 | |
| 3 | Süd | Taschen | 310 | |||
| 4 | Nord | Taschen | 150 | |||
| 5 | Ost | Schuhe | 280 | |||
| 6 | Süd | Schuhe | 500 | |||
| 7 | Nord | Schuhe | 260 | |||
| 8 | Ost | Taschen | 190 | |||
| 9 | Süd | Taschen | 240 |
In einfachen Worten: Teile 1den Umsatz der Region in E2 durch den gesamten Umsatz in C2:C9 und runde auf 23 Nachkommastellen.
Der Anteil von Nord am Gesamtergebnis, gerundet auf drei Nachkommastellen: 830 von 2.350. Formatierst du die Zelle als Prozent, steht dort 35,3 %.
Die Pivot-Tabelle ändert sich, wenn du sie aktualisierst. Die Formel ändert sich, sobald sich deine Daten ändern. Unsere Übung zum Anteil am Gesamtergebnis verlangt genau diese Formel, und die Anleitung zur Prozentrechnung hat den Rest der Prozentrechnung, von der prozentualen Veränderung bis zur Marge.
Zeilen und Spalten gleichzeitig
Legst du ein Feld in Zeilen und ein anderes in Spalten, hat jede Zelle im Raster zwei Bedingungen. Als Formel ist das SUMMEWENNS mit einer Bedingung aus der Zeilenbeschriftung und einer aus der Spaltenüberschrift:
=SUMMEWENNS(1$C$2:$C$9;2$A$2:$A$9;3$E2;4$B$2:$B$9;5F$1)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Produkt | Umsatz | Schuhe | Taschen | ||
| 2 | Nord | Schuhe | 420 | Nord | 680 | ||
| 3 | Süd | Taschen | 310 | Süd | |||
| 4 | Nord | Taschen | 150 | Ost | |||
| 5 | Ost | Schuhe | 280 | ||||
| 6 | Süd | Schuhe | 500 | ||||
| 7 | Nord | Schuhe | 260 | ||||
| 8 | Ost | Taschen | 190 | ||||
| 9 | Süd | Taschen | 240 |
In einfachen Worten: Addiere 1den Umsatz in C2:C9 in den Zeilen, in denen 2die Region in A2:A9 3die Region in Spalte E ist und 4das Produkt in B2:B9 5das Produkt in Zeile 1 ist.
Regionen nach unten, Produkte nach rechts. $E2 hält die Spalte fest, F$1 hält die Zeile fest, und so füllt eine einzige Formel den ganzen Block.
Die Dollarzeichen sorgen dafür, dass es funktioniert. $E2 fixiert die Spalte, lässt aber die Zeile mitlaufen, wenn du nach unten kopierst, sodass jede Zeile ihre eigene Region liest. F$1 fixiert die Zeile, lässt aber die Spalte mitlaufen, wenn du nach rechts kopierst, sodass jede Spalte ihr eigenes Produkt liest. Schreib die Formel einmal in F2, kopier sie nach rechts und nach unten, und du hast die Pivot-Tabelle mit Formeln nachgebaut. Die Übung zur Kreuztabelle trainiert genau das, und sie ist einer der besten Wege, gemischte Bezüge zu verstehen.
Datumswerte gruppieren
Legst du ein Datumsfeld in Zeilen, gruppieren neuere Excel-Versionen es automatisch nach Jahren, Quartalen und Monaten. Tut deine das nicht, oder willst du eine andere Gruppierung, klick mit der rechten Maustaste auf ein Datum in der Pivot-Tabelle und wähle Gruppieren. Du kannst nach Tagen, Monaten, Quartalen, Jahren oder einer Mischung gruppieren.
Ist Gruppieren ausgegraut, steht in der Datumsspalte mindestens ein Wert, der kein echtes Datum ist. Meistens ist es Text, der wie ein Datum aussieht. Such ihn, korrigier ihn und aktualisiere.
Auch Zahlen lassen sich gruppieren. Klick mit der rechten Maustaste auf eine Zahl in Zeilen, wähle Gruppieren und gib Start, Ende und Intervall ein. Dann bekommst du Klassen wie 0-99, 100-199 und so weiter. Das ist praktisch für Bestellgrößen oder Altersgruppen.
Filter und Datenschnitte
Der Bereich Filter gibt dir eine Dropdown-Liste über der Pivot-Tabelle. Datenschnitte tun dasselbe mit Schaltflächen: Markier die Pivot-Tabelle, wähle die Registerkarte PivotTable-Analyse und dann Datenschnitt einfügen und hak die Felder an, die du nutzen willst. Datenschnitte sind für andere leichter zu bedienen, und ein Datenschnitt kann mehrere Pivot-Tabellen steuern, die auf denselben Daten beruhen. Damit bist du schon auf dem Weg zu einem einfachen Dashboard.
Du kannst auch innerhalb von Zeilen oder Spalten filtern, mit dem kleinen Pfeil an der Beschriftung, zum Beispiel um nur die zehn größten Kunden nach Umsatz zu sehen.
Mach die Pivot-Tabelle lesbar
Eine neue Pivot-Tabelle ist richtig, aber selten schön. Mit ein paar Einstellungen wird sie zu etwas, das du in einen Bericht stellen kannst.
Zahlenformat. Formatierst du die Zellen direkt, verschwindet das Format, sobald die Pivot-Tabelle aktualisiert wird. Öffne stattdessen die Wertfeldeinstellungen, klick auf Zahlenformat und wähle das Format dort: Tausendertrennzeichen, keine Nachkommastellen oder Prozent. Dann bleibt es.
Berichtslayout. Auf der Registerkarte Entwurf hat Berichtslayout drei Optionen. Das Kurzformat ist Standard und stellt alle Zeilenfelder eingerückt in eine Spalte. Gliederungsformat und Tabellenformat geben jedem Zeilenfeld eine eigene Spalte, das ist leichter zu lesen und leichter auf ein anderes Blatt zu kopieren. Wähle außerdem Alle Elementbeschriftungen wiederholen, wenn du das Ergebnis woanders hin kopieren willst, damit jede Zeile alle ihre Beschriftungen hat.
Teilergebnisse und Gesamtergebnisse. Auf derselben Registerkarte kannst du Teilergebnisse und Gesamtergebnisse ausschalten oder verschieben. Hast du zwei Felder in Zeilen, können Teilergebnisse pro Gruppe nützlich sein. Hast du nur eins, wiederholen sie nur das Gesamtergebnis.
Sortierung. Klick auf den Pfeil an einer Zeilenbeschriftung und wähle Weitere Sortieroptionen, um nach dem Wert zu sortieren. Zum Beispiel die Regionen nach Umsatz, die größte zuerst. Standardmäßig wird nach der Beschriftung sortiert, und das will der Leser selten.
Feldnamen. "Summe von Umsatz" ist genau, aber sperrig. Klick auf die Überschrift in der Pivot-Tabelle und gib einen neuen Namen ein, etwa "Umsatz". Der Name darf nicht exakt so heißen wie die Quellspalte, deshalb ist "Umsatz " mit einem Leerzeichen am Ende ein bekannter Trick.
Ein Diagramm. Klick in die Pivot-Tabelle und wähle PivotChart auf der Registerkarte PivotTable-Analyse, um sie zu zeichnen. Das Diagramm zieht jedes Mal mit, wenn du filterst oder neu gruppierst. Die Anleitung zu Diagrammen erklärt, wann ein PivotChart die richtige Wahl ist und wie du stattdessen ein Diagramm aus Formeln baust.
Berechnete Felder
Manchmal steht die Zahl, die du brauchst, nicht in deinen Daten, lässt sich aber daraus berechnen. Etwa der Deckungsbeitrag aus Umsatz und Einstandspreis oder der Stückpreis aus Umsatz und Menge. Ein berechnetes Feld fügt der Pivot-Tabelle ein neues Feld mit einer Formel wie =Umsatz-Kosten hinzu. Du findest es unter PivotTable-Analyse, Felder, Elemente und Gruppen, Berechnetes Feld.
Berechnete Felder rechnen mit den Summen und nicht mit den einzelnen Zeilen. Eine Marge, definiert als =Gewinn/Umsatz, liefert für jede Gruppe das richtige Ergebnis, weil sie den gesamten Gewinn der Gruppe durch ihren gesamten Umsatz teilt. Ein Feld dagegen, das zwei Spalten Zeile für Zeile multipliziert, etwa Preis mal Menge, liefert ein falsches Ergebnis, weil es die Summen multipliziert. Berechne das stattdessen in einer neuen Spalte in deinen Daten und aktualisiere.
Ein Beispiel von Anfang bis Ende
So sieht eine typische Aufgabe aus. Deine Chefin schickt eine Tabelle mit den 600 Bestellungen des letzten Quartals und fragt: "Welche Region hat am meisten verkauft, und wie lief jedes Produkt in jeder Region?"
- Klick in die Daten und drück Strg+T, damit daraus eine Tabelle wird. Prüf, ob die Spalte Umsatz bis ganz unten rechtsbündig steht. Dann sind es Zahlen.
- Füg eine Pivot-Tabelle auf einem neuen Blatt ein. Zieh Region in Zeilen und Umsatz in Werte. Sortier die Regionen nach Summe von Umsatz, die größte zuerst. Damit hast du die erste Frage beantwortet.
- Zieh Produkt in Spalten. Jetzt ist jede Zelle der Umsatz einer Region mit einem Produkt. Stell das Zahlenformat auf ganze Zahlen mit Tausendertrennzeichen.
- Leg Umsatz ein zweites Mal in Werte und stell Werte anzeigen als auf % des Zeilenergebnisses. Jetzt siehst du den Anteil jedes Produkts in jeder Region, und das ist oft die interessanteste Antwort.
- Prüf eine Zahl von Hand, bevor du sie schickst. Ein SUMMEWENNS für eine Region und ein Produkt muss dieselbe Summe ergeben wie die Pivot-Tabelle. Tut es das, schick es ab.
Das dauert fünf Minuten, und Schritt 5 bewahrt dich vor einer peinlichen Korrektur hinterher. Mach es dir zur Gewohnheit, auch wenn es eilt.
Willst du das mit echten Daten üben, lade die Umsatzdaten mit 240 Bestellungen herunter und löse die 15 Pivot-Tabellen-Übungen. Jede Aufgabe hat eine Lösung, und du siehst, wie die Felder in der Pivot-Tabelle liegen müssen.
Aktualisieren und was sonst schiefgeht
Hier sind die Probleme, auf die Leute am häufigsten stoßen, ungefähr in der Reihenfolge, wie oft sie vorkommen.
Die Zahlen haben sich nicht geändert, obwohl die Daten es haben. Eine Pivot-Tabelle ist eine Momentaufnahme. Hast du die Daten geändert, klick mit der rechten Maustaste in die Pivot-Tabelle und wähle Aktualisieren, oder drück Alt+F5. Alle aktualisieren auf der Registerkarte Daten aktualisiert alle Pivot-Tabellen der Arbeitsmappe.
Neue Zeilen fehlen. Hast du Zeilen unter dem ursprünglichen Bereich hinzugefügt, reicht Aktualisieren nicht. Wähle Datenquelle ändern und vergrößere den Bereich. Oder noch besser: Wandle deine Daten mit Strg+T in eine Tabelle um, dann wächst der Bereich von selbst.
"Anzahl von Umsatz" statt "Summe von Umsatz". Irgendwo in der Spalte stehen Text oder leere Zellen. Korrigiere die Daten und stell die Feldeinstellung dann auf Summe um. Der übliche Übeltäter ist eine Zahl, die als Text gespeichert ist. Sie steht linksbündig in der Zelle.
Dieselbe Gruppe steht zweimal da. Für eine Pivot-Tabelle sind "Nord" und "Nord " (mit Leerzeichen) zwei verschiedene Werte. Bereinige die Spalte mit GLÄTTEN oder Suchen und Ersetzen und aktualisiere.
"(Leer)" steht als Gruppe da. Einige Zeilen haben in diesem Feld eine leere Zelle. Füll sie aus oder filtere (Leer) heraus, wenn die Werte wirklich fehlen.
Eine Formel, die auf die Pivot-Tabelle zeigt, liefert seltsame Ergebnisse. Klickst du beim Schreiben einer Formel auf eine Zelle in einer Pivot-Tabelle, setzt Excel die Funktion PIVOTDATENZUORDNEN statt eines normalen Bezugs ein. Sie ist dafür gemacht, weiter zu funktionieren, wenn die Pivot-Tabelle ihre Form ändert. Willst du einen normalen Bezug, tipp die Zelladresse, statt zu klicken, oder schalt GetPivotData generieren in den Optionen der Pivot-Tabelle aus.
Pivot-Tabelle oder Formeln?
Beide liefern dieselben Zahlen, aber sie sind für Verschiedenes gut.
Nimm eine Pivot-Tabelle, wenn:
- Du deine Daten erkundest und noch nicht weißt, welche Übersicht du brauchst. Felder herumzuziehen geht viel schneller, als Formeln umzuschreiben.
- Du die Übersicht nur einmal brauchst, für ein Meeting oder eine Mail.
- Du nach Monat oder Quartal gruppieren oder schnell eine Top Ten bauen willst.
Nimm Formeln, wenn:
- Die Übersicht ein festes Layout haben soll, etwa ein Monatsbericht mit jedes Mal denselben Zeilen.
- Die Zahlen sich aktualisieren sollen, ohne dass jemand auf Aktualisieren klickt.
- Andere Berechnungen auf den Zahlen aufbauen, etwa ein Plan-Ist-Vergleich oder eine Provisionsberechnung.
- Die Übersicht Daten auf eine Art kombinieren soll, die eine Pivot-Tabelle nicht kann, etwa ein Verhältnis zwischen zwei verschiedenen Quellen.
Viele gute Tabellen nutzen beides: eine Pivot-Tabelle zum Erkunden und Prüfen und eine Tabelle mit SUMMEWENNS für die Version, die in den Bericht kommt. Wenn beide dasselbe ergeben, kannst du den Zahlen trauen. Diese doppelte Kontrolle ist auch in der Finanzarbeit eine gute Gewohnheit. Mehr dazu in unserer Anleitung zu Excel im Finanzbereich.
In Excel 365 liefert dir die Funktion EINDEUTIG (UNIQUE) die Liste der Zeilenbeschriftungen ganz von selbst: =EINDEUTIG(A2:A9) verteilt die verschiedenen Regionen nach unten in eine Spalte. Stell ein SUMMEWENN daneben, und du hast eine Übersicht aus Formeln, die mit deinen Daten wächst.
Pivot-Tabellen in Google Sheets
Google Sheets hat ebenfalls Pivot-Tabellen. Markier deine Daten und wähle Einfügen und dann Pivot-Tabelle. Der Bereich rechts hat dieselben vier Bereiche, Zeilen, Spalten, Werte und Filter, und dieselben Optionen wie Summe, Anzahl und Mittelwert. Zwei Unterschiede: Sheets aktualisiert seine Pivot-Tabellen von selbst, wenn sich die Daten ändern, und es kann dir Pivot-Tabellen vorschlagen. Alle Formeln hier funktionieren auch in Sheets.
Pivot-Tabellen in Excel-Tests
Excel-Tests von Arbeitgebern fragen Pivot-Tabellen auf zwei Arten ab. Wissensfragen wollen wissen, in welchen Bereich ein Feld gehört oder wie du Summe auf Anzahl umstellst. Praktische Aufgaben geben dir Daten und verlangen eine Übersicht, etwa "Umsatz pro Region und Produkt". Die kannst du mit einer Pivot-Tabelle bauen, wenn der Test eine hat, oder mit SUMMEWENN und SUMMEWENNS, wenn es ein Simulator im Browser ohne Pivot-Tabellen ist. Kannst du beides, bist du in jedem Fall abgesichert.
Bereitest du dich auf einen Test vor, übe den Formelweg, bis er sitzt. Dann sieht, wer dich bewertet, dass du verstehst, was die Übersicht berechnet. Sorg danach dafür, dass du in deinem eigenen Excel eine Pivot-Tabelle einfügen, die Zusammenfassungsfunktion umstellen und sie aktualisieren kannst.
Kurz gesagt
Eine Pivot-Tabelle fasst die Zeilen einer Tabelle nach einem oder zwei Feldern zu Gruppen zusammen und summiert, zählt oder mittelt für jede Gruppe ein anderes Feld. Du erstellst eine mit Einfügen, PivotTable und ziehst dann Felder in Zeilen, Spalten, Werte und Filter. Halte deine Daten sauber und in einer Tabelle, aktualisiere nach jeder Änderung und prüf die Wertfeldeinstellung, wenn aus einer Summe plötzlich eine Anzahl wird.
Jede Zahl in einer Pivot-Tabelle ist eine bedingte Summe, eine bedingte Anzahl oder ein bedingter Durchschnitt, und du kannst sie auch als SUMMEWENN, ZÄHLENWENN, MITTELWERTWENN oder SUMMEWENNS schreiben. Die Übungen zu Auswertungstabellen unten bauen sie Schritt für Schritt auf, von einer einzelnen Summe pro Region bis zu einer ganzen Kreuztabelle. Fang mit der ersten an und schreib die Formel selbst.

Über den Autor
Nicolaj, Gründer von ExcelPraxis
Ich bin Softwareentwickler und habe ExcelPraxis von Grund auf gebaut, auch die Tabelle selbst und die Engine, die die Formeln berechnet. Damit das funktioniert, musste ich jede Funktion bis ins Detail verstehen: wie sich SVERWEIS verhält, wenn ein Code ein Leerzeichen zu viel hat, warum ein Datum eigentlich eine Zahl ist und wann RUNDEN ein anderes Ergebnis liefert als das Zahlenformat.
Üb, was diese Anleitung behandelt
Übungen mit Korrektur in einer echten Tabelle, in der Reihenfolge der Anleitung.
- Summe pro Region, bereit zum KopierenAuswertungstabellen →
- Eine Kreuztabelle mit gemischten BezügenAuswertungstabellen →
- Bestellungen pro Region mit ZÄHLENWENNAuswertungstabellen →
- Durchschnittliche Bestellung pro RegionAuswertungstabellen →
- Der Anteil jeder Region am GesamtumsatzAuswertungstabellen →
- SUMMEWENN mit der Bedingung in einer ZelleSUMMEWENN →
- SUMMEWENNS mit den Bedingungen in ZellenSUMMEWENNS →
- ZÄHLENWENN mit einer TextbedingungZÄHLENWENN →
- Durchschnitt einer Gruppe mit MITTELWERTWENNMITTELWERTWENN und MITTELWERTWENNS →
- Eine ganze Tabelle markierenBewegen und markieren →
Funktionsseiten
Tests auf Zeit
- Excel-Test im Vorstellungsgespräch · 20 Aufgaben in 30 Minuten
- Übe für den Excel-Test von TestGorilla · 20 Aufgaben in 20 Minuten
Fragen
Was ist eine Pivot-Tabelle in Excel?
Eine Pivot-Tabelle fasst eine lange Datentabelle zusammen, zum Beispiel den Umsatz pro Region und Monat. Du ziehst Felder in Zeilen, Spalten, Werte und Filter, und Excel gruppiert die Zeilen und berechnet die Summen für dich.
Warum zeigt meine Pivot-Tabelle die neuen Daten nicht?
Pivot-Tabellen aktualisieren sich nicht von selbst. Klick mit der rechten Maustaste in die Pivot-Tabelle und wähle Aktualisieren, oder drück Alt+F5. Stehen die neuen Zeilen unter dem ursprünglichen Bereich, musst du auch die Datenquelle ändern. Noch besser ist es, die Daten vorher in eine Excel-Tabelle umzuwandeln, denn dann wächst der Bereich von selbst.
Warum zählt meine Pivot-Tabelle, statt zu summieren?
Stehen Text oder leere Zellen in der Wertespalte, wählt Excel Anzahl. Korrigiere die Daten, sodass die Spalte nur Zahlen enthält, und stell die Wertfeldeinstellung dann auf Summe um.
Soll ich eine Pivot-Tabelle oder Formeln verwenden?
Nimm eine Pivot-Tabelle, wenn du Daten erkunden willst oder die Übersicht nur einmal brauchst. Nimm Formeln wie SUMMEWENNS, wenn die Übersicht ein festes Layout haben, sich selbst aktualisieren oder in anderen Berechnungen weiterverwendet werden soll.
Hat Google Sheets Pivot-Tabellen?
Ja. In Google Sheets wählst du Einfügen und dann Pivot-Tabelle. Du arbeitest mit denselben Zeilen, Spalten, Werten und Filtern, und die Formeln hier funktionieren auch in Sheets.
Weitere Anleitungen
- Prozentrechnung in Excel: Prozent, Mehrwertsteuer und Veränderung berechnenProzent von einem Wert, Aufschlag, Rabatt, Mehrwertsteuer drauf- und herausrechnen, prozentuale Veränderung und Handelsspanne in Excel, jeweils mit einer kurzen Formel.
- Excel im Finanzbereich: die Fähigkeiten, die du wirklich brauchstDie Excel-Fähigkeiten, die Buchhaltung und Controlling täglich nutzen, von SUMMEWENNS und Budgetabweichungen bis zu Rundung, Monatsabschluss, RMZ und Kontrollsummen.
- SVERWEIS, XVERWEIS oder INDEX VERGLEICH: Welcher Verweis ist der richtige?So funktionieren SVERWEIS, XVERWEIS und INDEX mit VERGLEICH, wo jeder dich austrickst, und welchen du für einen Test, eine alte Tabelle oder eine neue nimmst.
- Excel im Vorstellungsgespräch: was Arbeitgeber testen und wie du dich vorbereitestWas Excel-Tests von Arbeitgebern abfragen, welche Formeln immer wiederkommen, wie die großen Testplattformen funktionieren, und ein kostenloser Übungstest auf Zeit.
- Diagramme in Excel: Säulen-, Linien- und Kreisdiagramme erstellenErstell ein Diagramm in Excel in drei Schritten, wähl den richtigen Diagrammtyp für deine Daten und behebe die typischen Fehler. Mit den Formeln für die Tabelle dahinter.
- Pivot-Tabellen üben: 15 Aufgaben mit Lösungen an echten UmsatzdatenÜb Pivot-Tabellen in Excel an 240 Bestellungen. 15 Aufgaben von der ersten Summe bis zu Quartalen, Prozent vom Gesamtergebnis und Filtern, mit Lösungen und Feldaufbau.
- Übungsdaten für Excel: kostenlose Datensätze mit Aufgaben und LösungenZwei kostenlose Excel-Dateien zum Üben: 240 Bestellungen und 60 Mitarbeitende. Mit 29 Aufgaben zu SUMMEWENN, ZÄHLENWENN, Verweisen und Datumswerten, mit Lösungen.