Viele Excel-Anwender haben schon davon gehört, aber viele können nicht so richtig etwas damit anfangen oder haben gar Angst vor ihnen: Pivot-Tabellen. Wozu braucht man sie und wie funktionieren sie?
Mit Pivot-Tabellen lassen sich umfangreiche Datenbestände in Excel per Mausklick verdichten, neu anordnen und analysieren. Und das Schöne dabei: Es geht wirklich kinderleicht und die Ursprungsdaten werden nicht verändert!
Und so geht’s:
Vorbereitung
Zuerst brauchen wir unser Datenmaterial, das wir anschließend mit Hilfe einer Pivot-Tabelle analysieren wollen. Ich greife dazu wieder auf meine Beispieltabelle mit den Hauptstädten zurück (du kannst die Datei hier herunterladen.)
Beispiel: Liste der Hauptstädte
Bevor wir nun die Pivot-Tabelle erstellen, möchte ich noch ein paar generelle Hinweise zur Datentabelle geben, die analysiert werden soll. Wenn du die berücksichtigst, wirst du die besten Ergebnisse mit einer Pivot-Tabelle ziehen:
- Jede Spalte muss eine eindeutige Überschrift besitzen
- Überschriften dürfen nicht über mehrere Spalten hinweg zentriert sein
- Es dürfen sich keine leeren Spalten in der Datentabelle befinden
- Es sollten sich idealerweise keine leeren Zeilen in der Datentabelle befinden
Kurz:
Bei den zu analysierenden Daten sollte es sich um einen zusammenhängenden Tabellenbereich handeln. Das lässt sich am schnellsten feststellen, indem du in deine Datentabelle klickst und mit der Tastenkombination STRG+A die Tabelle markierst. Damit siehst du sofort, auf welchen Tabellenbereich die Pivot-Tabelle aufbauen wird.
(Noch besser ist es, wenn du deine Daten als formatierte Tabelle einrichtest).
Pivot-Tabelle erstellen
Dann kann’s ja losgehen. Stelle also wieder die aktive Zelle irgendwo in deine Datentabelle und klicke dann im Register „Einfügen“ die Schaltfläche Pivot-Table:
Einfügen der Pivot-Tabelle
Daraufhin wird um die Datentabelle herum ein gestrichelter Laufrahmen angezeigt und es erscheint das folgende Eingabefenster:
Kontrolliere den markierten Bereich
Im ersten Feld kannst du nochmal den ausgewählten Datenbereich überprüfen. Und wenn du die voreingestellte Option „Neues Arbeitsblatt“ belässt, dann wird die Pivot-Tabelle auf einem eigenen Blatt erstellt. Mit einem Klick auf „OK“ wird die zunächst noch leere Pivot-Tabelle erzeugt:
Die (noch) leere Pivot-Tabelle in einem neuen Blatt
Hier gibt es den eigentlichen Datenbereich, den wir gleich befüllen lassen. Zusätzlich erscheint am rechten Rand der Arbeitsbereich „PivotTable-Felder, der die Liste mit allen Feldern der Hauptstadtliste enthält und darunter vier verschiedene Gruppierungsbereiche.
Hinweis:
Die Feldliste wird übrigens ausgeblendet, wenn sich die aktive Zelle außerhalb der leeren Pivot-Tabelle befindet. In diesem Fall muss man die aktive Zelle nur wieder in die Pivot-Tabelle stellen, und schon ist die Feldliste wieder sichtbar. Außerdem gibt es bei ausgewählter Pivot-Tabelle oben im Menü zwei zusätzliche Registerblätter namens „PivotTable-Analyse“ und „Entwurf“ (in älteren Excel-Versionen gibt es stattdessen eine Menügruppe „PivotTable-Tools“ mit den Menüs „Optionen“ und „Entwurf“):
PivotTable-Felder und Pivot-Menüs nur innerhalb der Pivot-Tabelle sichtbar
Ich biete auch einen Online-Kurs für den Pivot-Einstieg an. Live und in Farbe! Alle Infos dazu findest du auf dieser Seite.
Pivot-Tabelle mit Leben füllen
Die Feldliste am rechten Rand enthält oben alle Spaltenbezeichnungen unserer Datentabelle. Nehmen wir an, dass wir unsere Daten nach Kontinenten verdichten wollen. Dazu wählst du mit der Maus das Feld „Kontinent“ aus und ziehst es einfach nach unten in den Bereich „Zeilen“, wo du dann die Maus wieder loslässt:
Das erste Feld kommt in den Zeilen-Bereich
Damit werden in unserer Pivot-Tabelle schon mal die Namen sämtlicher Kontinente angezeigt:
Pivot-Tabelle zeigt alle Kontinente
Als nächstes ziehst du das Feld „Einwohnerzahl“ mit der Maus nach unten in den Bereich „Werte“:
Das Einwohner-Feld kommt in den Werte-Bereich
Da es sich bei den Einwohnern um ein Zahlenfeld handelt, verwendet Excel automatisch die Summenfunktion. Und schon haben wir in unserer Pivot-Tabelle die Summe sämtlicher Hauptstadtbewohner je Kontinent:
Die erste fertige Pivot-Tabelle
War doch wirklich einfach, oder?
Als nächstes wollen wir noch wissen, wie viele Hauptstädte es pro Kontinent gibt. Ziehe also das Feld „Hauptstadt“ ebenfalls nach unten in die Werte-Gruppe. Hier möchte ich dich gleich auf eine kleine Besonderheit aufmerksam machen. Da es sich bei den Hauptstädten um ein Textfeld handelt, ist Excel so schlau und verwendet automatisch die Anzahl-Funktion (es sollen ja die Städte gezählt werden), und nicht wie bei den Einwohnern die Summenfunktion:
Das Hauptstadt-Feld kommt in den Werte-Bereich
Fine-Tuning
Um die Pivot-Tabelle besser lesbar zu machen, wollen wir das Zahlenformat der Einwohnerspalte mit einem Tausendertrennpunkt versehen. In einer normalen Tabelle würdest du jetzt zuerst alle Zellen mit Einwohnern markieren und dann das Zahlenformat anpassen. Hier läuft es aber ein wenig anders. Da eine Pivot-Tabelle ein dynamisches Objekt ist, könnten später ja weitere Datensätze dazukommen und du müsstest dann erneut das Format anpassen. Excel macht es uns hier aber sehr leicht.
Du musst nur ein eine beliebige Zelle innerhalb der Einwohnerspalte einen Rechtsklick ausführen und im Kontextmenü den Eintrag „Zahlenformat…“ auswählen:
Zahlenformat anpassen
Damit öffnet sich das bekannte Dialogfenster, wo die die gewünschten Anpassungen vornehmen kannst. Also das Häkchen beim 1000er-Trennzeichen und die Dezimalstellen auf Null setzen:
1000er-Trennzeichen und keine Dezimalstellen
Sieht doch schon viel besser aus:
Die erweiterte Pivot-Tabelle
Erweiterte Pivot-Funktionen
Pivot-Tabellen bieten aber noch viel mehr Möglichkeiten der Auswertung, von denen wir uns eine noch ansehen wollen. Nehmen wir an, du möchtest jetzt den prozentualen Anteil der Hauptstadteinwohner je Kontinent am Gesamtergebnis wissen. Nichts leichter als das: Ziehe einfach das Feld „Einwohnerzahl“ ein zweites Mal in die Werte-Gruppe, so dass wir zunächst folgende Ansicht erhalten:
Einwohnerzahl kommt ein weiteres Mal in den Werte-Bereich
Jetzt klicke mit der rechten Maustaste auf einen beliebigen Wert in der zweite Einwohnerspalte der Pivot-Tabelle und wähle den Menüpunkt „Werte anzeigen als“ und dann den Eintrag „% des Gesamtergebnisses“:
Werte als % des Gesamtergebnisses anzeigen
Und schon haben wir das gewünschte Ergebnis, ohne eine einzige Formel eingegeben zu haben:
Die fertige Pivot-Tabelle
Wie du siehst, sind Pivot-Tabellen gar nicht so schwer. Mit dieser Einführung bist du hoffentlich auf den Geschmack gekommen und traust dich jetzt selbst ein wenig damit herumzuspielen.
Passend zum Thema gibt es auch die erweiterte Neuauflage meines Buchs „Excel Pivot-Tabellen für Dummies„.
Excel-Pivot-Tabellen für dummies (2. Auflage)
Wenn du also tiefer in das Thema einsteigen möchtest, kann ich dir dieses Buch nur wärmstens ans Herz legen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Danke für die Einführung. Ich hätte es schwerer vermutet. 🙂
Hallo Sebastian,
sehr gerne. Manchmal sind die Dinge tatsächlich nicht so kompliziert, wie man glaubt…
Grüße,
Martin
Hi Martin,
ich erinnere mich gerne daran zurück wie ich mich gegen die Pivottabellen gewährt habe 🙂
Irgendwann nahm ich sie dann an und jetzt kann ich gar nicht mehr ohne 🙂
Dein Hinweis mit STRG + A ist super 🙂
Und noch etwas: Ich war etwas verwundert, als du schriebst, dass Excel bei der Einwohnerzahl automatisch eine Summe bildet. Bei mir kommt immer Anzahl. Hat das vielleicht damit zu tun, dass ich die Werte meistens aus irgendeiner Datenbank fische? Sollte ich vielleicht vorher das Format ändern, bevor ich die Pivottabelle drüberlege?
Ich ändere halt immer Anzahl zu Summe
Alles Liebe
Cornelia
Hallo Cornelia,
ja, bei Pivottabellen läuft es vielen Leuten kalt den Buckel runter. Aber eigentlich zu unrecht, wie man sieht.
Und ja, es kommt normalerweise tatsächlich auf das Quellformat an. Wenn das Datenbankfeld, aus dem die die Werte holst, kein numerisches Feld ist, dann wird eben die Anzahl vorgeschlagen. Aber ich habe das auch innerhalb von Excel schon erlebt, dass das Feld nicht immer zu 100% korrekt erkannt wird. Dann hilft, wie Du schon sagst, nur die manuelle Änderung auf die Summe.
Schöne Grüße,
Martin
Pingback: Der Horror geht weiter: Pivot-Tabellen Teil 2 | Der Tabellen-Experte
Hallo zusammen,
dann hoffe ich mal, dass mir jemand helfen kann.
Ich habe eine Pivottabelle in Excel 2013 über mehrere Arbeitsblätter, die Datenquelle ist eine andere Exceldatei. Ich benutze pro Arbeitsblatt einen Filter auf eine Spalte in der Ausgangstabelle.
Jetzt kommt mein Problem, wenn der Wert des Filters in der Ausgangstabelle nicht mehr vorhanden ist (die Tabelle kann ständig neu generiert werden), ändert sich der Filter in der Pivotauswertung und man weiß nicht mehr, welcher Filter dort eigentlich drin war. Gibt es eine Möglichkeit, das zu unterbinden?
Gruss,
Frank
Hallo Frank,
wenn ich es richtig verstehe, sollen die Filter in der Pivotabelle erhalten bleiben, obwohl es den Filterwert in den Quelltabellen nicht mehr gibt?
Mir ist dafür leider keine Lösung bekannt, denn das ist ja eine der Grundeigenschaften der Filter in Excel, dass sie dynamisch sind und nur Werte anbieten, die auch in der Tabelle existieren.
Als einzige Krücke könnte ich mir vorstellen, dass Du in den verschiedenen Arbeitsblättern für jeden möglichen Filterwert einen Dummy-Eintrag erstellst, der immer vorhanden ist. Und damit auch im Filter verfügbar bleibt. Ob das allerdings in Deinem konkreten Szenario möglich und praktikabel ist, kann ich nicht sagen.
Schöne Grüße,
Martin
Hallo Martin,
das löst zwar nicht mein Problem, aber trotzdem Danke für die Antwort!
Gruss,
Frank
Pingback: Wenn’s auf den Einzelnen ankommt: Werte in Pivot-Tabellen ansprechen | Der Tabellen-Experte
Hallo Martin,
danke erstmal für Deine gelungene Pivot-Tabellen-Einführung. Bei mir funktioniert das jetzt schon ganz gut, allerdings ergibt sich ein Problem, an dem ich jetzt schon eine Weile rumtüftle: aus irgendeinem Grund übernimmt die Tabelle nicht alle Daten aus der Datenbank; dies scheint mir auch irgendwie recht willkürlich zu passieren, denn in der einen Auswertungstabelle werden alle Werte aus den Spalten G zu H in Relation zu einander gesetzt und in der nächsten Auswertungstabelle G zu J warden aus Spalte G nicht alle Werte angezeigt/miteinbezogen, obwohl ebenfalls beide Spalten komplett mit validen Daten befüllt sind. Ich komme einfach nicht darauf, woran dies liegen kann. Hast Du vielleicht einen Tipp oder eine gute Idee?
Vielen Dank im voraus und beste Grüße,
Ingo
Hallo Ingo,
ich kann mir momentan zwar noch nicht so genau vorstellen, welche Daten wie zueinander in Relation gesetzt werden. Grundsätzlich gilt aber: Wenn in der Pivot-Tabelle nicht alle Daten aus der Quelltabelle angezeigt werden, solltest Du einfach nochmal die Datenquelle überprüfen und ggf. den Bereich anpassen. Dazu einfach die Pivot-Tabelle anklicken und dann in den Pivottable-Tools im Register „Analysieren“ die Schaltfläche „Datenquelle ändern“ klicken. Dann kannst Du den kompletten Quellbereich nochmal auswählen.
Oder habe ich Dich völlig missverstanden?
Grüße,
Martin
Hallo Martin,
perfekter Tipp, danke! Jetzt funktioniert es wie gewünscht!
Super Service! 😉
Beste Grüße,
Ingo
Hallo Martin,
du hast recht meistens sitzt das Problem vor dem Computer. Ich kenne mich mit Pivot Tabellen einigermaßen gut aus benutze das schon seit Jahren finde das super, aber heute bin ich auf ein Problem gestoßen dass mich schon einiges an Zeit gekostet hat. Ich habe eine ganz normale Pivot Tabelle erstellt jedoch verweigert Exel eine einzige Zahl, das Feld wird als leer bezeichnet. Ich habe alles mögliche versucht aktualisiert, Datenbankbereich neu gewählt, pivot tabelle erneuert. Irgendwie stehe ich vor einem Rätsel. Hast du eine Idee was das sein könnte?
Hallo Martin,
Es ist wie immer man muss manchmal nur eine Zeit lang abschalten und noch mal neu von vorne beginnen. Lange Rede kurzer Sinn ich hab meinen Fehler gefunden bitte um Entschuldigung dass ich nicht früher darauf gekommen bin.
Danke
Hallo Axel,
freut mich, wenn Du doch noch eine Lösung gefunden hast. Du weißt ja: Das Problem sitzt meistens…. 😉
Schöne Grüße,
Martin
Pingback: Verdichten und Gruppieren in Pivot-Tabellen | Der Tabellen-Experte
Hallo zusammen
Ich brauche Hilfe
Habe mir von meinen Kunden Umsätzen eine Pivot Tabelle angelegt.
Erst als ich das Jahr 2015 hinzu gefügt habe funktionierte es nicht mehr richtig
Das heisst die Zahlen werden übernommen und stimmen in den einzelnen Tabellen auch
Was aber nicht funktioniert sind die Prozentzahlen
Ich möchte wissen wie viele % sind das zum Vorjahr
Ich mache ein Beispiel
Umsatz 24 Umsatz 2015 Differenz in %
28’306 22’685 -0,64
Nachdem neue Daten eingeben wurden, gehe ich auf Alle aktualisieren
aber mit den % Zahlen passiert einfach nichts
Was mache ich falsch, resp. wo kann ich das einstellen
Herzlichen Dank im Voraus
Walti
Pingback: Auf Wiedersehen… | Der Tabellen-Experte
Sehr gut. Ist es auch möglich die Spaltenbeschriftung, also in dem Beispiel in der Feldliste unter Spalten „Werte“ auch nicht anzeigen zu lassen?
Hallo Dirk,
ich bin mir nicht sicher, ob ich die Frage richtig verstehe: Was genau soll nicht angezeigt werden? Im Beispiel oben im Artikel gibt es drei Spalten „Einwohnerzahl (absolut)“, „Einwohnerzahl (prozentual)“ und „Anzahl Hauptstädte“. Wenn eine dieser Spalten nicht angezeigt werden soll, musst Du sie nur rechts unten aus dem Bereich „Werte“ entfernen. Sollen hingegen nur einzelne Zeilen nicht angezeigt werden – beispielsweise für Asien – dann kannst Du direkt in der Pivottabelle oben in der Spalte „Kontinente“ in der Dropdownliste den entsprechenden Kontinent abwählen.
Oder habe ich Dich völlig falsch verstanden?
Schöne Grüße,
Martin
Hallo Martin,
ich habe da mal eine Frage. Ich arbeite schon einige Zeit mit Pivot. Was ich aber jetzt nicht hinbekomme ist ein Vergleich von Umsätzen innerhalb eines Feld „Jahre“ Hier hinter verbergen sich die Umsätze von 3 Jahren (2015-2017). Ich brauche den Vergleich von 2016 – 2017. Über das berechnete Feld bekommt man dies ja nicht hin oder?
VG
Michael
Hallo Michael,
das ist pauschal etwas schwierig zu sagen, da ich den genauen Aufbau der Quell- und der Pivot-Tabelle nicht kenne. Grundsätzlich könnte es aber so gehen:
Die Jahre ziehst Du in die Spaltenbereich, so dass jedes Jahr also in einer eigenen Spalte auftaucht.
Das Umsatzfeld ziehst Du zweimal in den Wertebereich. Einmal ganz normal als Umsatz und einmal änderst Du die Wertfeldeinstellungen. Im Register „Werte anzeigen als“ wählst Du „Differenz von“, als Basisfeld das Jahresfeld und als Basiselement das Jahr 2016.
Schöne Grüße,
Martin
Hallo,
ich habe eine Exceltabelle mit unterschiedlichen Begriffen die in einer Spalte stehen. Viele Zellen haben nur einen Begriff andere Zellen beinhalten mehrere die mit Komma getrennt sind. Wenn ich nun eine Pivot Tabelle erstelle, erscheinen automatisch die Zellen mit mehreren Begriffen zusammengefasst in einer Kategorie. Meine Frage ist nun, kann ich Pivot so konfigurieren, dass die Begriffe aus einer Zelle nach den Kommas getrennt werden und einzeln in der Pivot erscheinen?
Hallo,
das ist leider nicht möglich. Jeder Wert, der in irgendeiner Form in einer Pivot-Tabelle ausgewertet werden soll, muss in einer eigenen Zelle stehen. Es bleibt also nichts übrig, als die einzelnen Begriffe erst einmal in einzelne Zellen aufzuteilen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Veröffentlichung der vielen Möglichkeiten von Excel. Um meine Grundkenntnisse zu erweitern und festigen leistest du super Arbeit 🙂
Mir ist hier aufgefallen das Bild mit der Leeren Pivot-Tabelle in mehrere Bereich unterteilt ist „Wertfelder“, „Zeilenfelder“,etc.
Auf der Seite mit dem Thema „Verdichten und Gruppieren in Pivot-Tabellen Nr.9“ ist dieses anders abgebildet wenn eine Leere Pivot-Tabelle erstellt wird (so kenn ich es bis jetzt nur).
Kannst du bitte erklären wie man zu dem kommt das du bildlich unter dem Thema „Der beste Freund des Excel-Analysten: Pivot-Tabellen Nr.24“ abgelegt hat?
Über eine erklärung würde ich mich riesig freuen.
Vielen Dank
Gruß
Thomas
Hallo Experte,
vielen Dank für diesen Artikel – und all die anderen wirklich hilfreichen Tipps und Kniffe.
Ich habe aber ein Problem für das ich hier – und auch anderswo – keine Lösung finden kann… 🙁
Ich arbeite viel mit dem Importieren von Daten mittels PowerQuery, aus den eingelesenen Daten baue ich relativ große Pivot Tabellen.
Hierbei ist es immer so, dass die Zellinhalte der einzelnen Spalten nicht sortiert angezeigt werden, wenn man mittels Schnellfilter selektieren möchte. Nicht auf- oder absteigend und auch sonst nach keinem erkennbaren Muster:
034
036
030
063
061
064
(…)
Außerdem werden auch Werte angezeigt, die aufgrund Filter in anderen Spalten nicht zu sehen sein dürften.
Woran um Himmels Willen kann dass liegen?!
Vielen Dank, ich bin dankbar für alle helfenden Antworten,
Lars
Hallo Lars,
die Werte in den Pivot-Filtern bekommst du mit einem kleinen Trick sortiert: Ziehe einfach temporär das Feld aus dem Filterbereich in den Zeilenbereich. Dort kannst du das Feld auf- oder absteigend sortieren. Und dann ziehst du es zurück in den Filterbereich. Jetzt sollten die Werte dort sortiert aufgeführt werden.
Leider „wissen“ die Pivot-Filter nichts von einander. Das heißt, selbst wenn du nach einem Feld gefiltert hast, werden dir im zweiten Filterfeld alle Werte angezeigt, selbst wenn diese aufgrund des ersten Filters nicht mehr relevant wären (so wie man das eigentlich aus den normalen Tabellenfiltern kennt). Und mir ist hierzu auch nichts bekannt, wie man dieses Verhalten ändern könnte.
Schöne Grüße,
Martin
Hallo Martin,
angenommen, ein Zeilenwert einer Pivot Tabelle ist per Hand direkt in der Pivot Tabelle geändert worden.
Wie kann diese manuelle Änderung, die vielleicht erst Tage später bemerkt wird, rückgängig gemacht werden?
Die Aktualisierung der Tabelle hilft hier nicht, denn sie ändert einen Wert in der Pivot Tabelle nur dann, wenn sich der Wert in der Datenquelle geändert hat, dieser ist aber nicht geändert worden!!
Folgendes Vorgehen stellt den Wert in der Pivot Tabelle wieder her:
1. Wert der Datenquelle separat sichern
2. Wert in der Datenquelle ändern
3. aktualisieren der Pivot Tabelle
4. Wert in der Datenquelle wiederherstellen
5. aktualisieren der Pivot Tabelle
Dieses Szenario ist sehr umständlich.
Gibt es eine einfachere Lösung per Menü-Befehl oder per VBA?
Kann die manuelle Änderung von Werten verhindert werden?
Viele Grüße
Lutz
Hallo Lutz,
das was du beschreibst, ist tatsächlich sehr ärgerlich. Mir ist aber leider auch keine Möglichkeit bekannt, die ursprünglichen Inhalte schneller wiederherzustellen bzw. die manuelle Änderung ganz zu verhindern.
Schöne Grüße,
Martin
Zu „prozentualen Anteil der Hauptstadteinwohner je Kontinent“
Das ist jetzt keine Excel-Technik Frage sondern eine zu den Rückschlüssen, die ich mit diesen schönen Excel-Techniken ziehen kann: Gegeben sind Anzahl von Einwohnern von Hauptstädten, die jew.einem Kontinent zugeordnet sind.
Die Zahl „23,39%“ für Afrika sagt nicht aus, daß 23% der AfrikanerInnnen in Hauptstädten wohnen,
sondern das von den Hauptstadt-Einwohnern aller Kontinente 23% in Hauptstädten auf dem afrikanischen Kontinent wohnen.
Oder habe ich da einen Denkfehler?
Hallo Ilka,
du hast natürlich völlig recht mit deiner Aussage. Da meine Beispieldatei überhaupt nur Informationen zu den Hauptstädten enthält, beziehen sich alle Aussagen auch immer nur auf Hauptstadteinwohner. Zugegeben ist das von der inhaltlichen Aussagekraft eher suboptimal. Aber es ging hier auch mehr darum, die grundsätzlichen Möglichkeiten von Pivot-Tabellen aufzuzeigen.
Und du machst es absolut richtig: Man sollte immer die Zahlen hinterfragen, die einem vorgesetzt werden 🙂
Schöne Grüße,
Martin