(Überarbeitet am 26.04.2021)
Pivot-Tabellen: Für die einen ein mächtiges Analysewerkzeug, für die anderen der Inbegriff von Excel-Horror. Falls du zur letzteren Kategorie gehören solltest, empfehle ich dir zunächst meine wirklich leichte Einführung in Pivot-Tabellen.
Im heutigen Artikel zeige ich dir ein paar weitere Tipps im Umgang mit Pivot-Tabellen:
- Datenquelle verändern
- Drilldown
- Gruppieren von Elementen
Und los geht’s!
Wenn du möchtest, kannst du dir die von mir verwendete Beispieltabelle hier herunterladen.
Ich biete auch einen Online-Kurs für den Pivot-Einstieg an. Live und in Farbe! Alle Infos dazu findest du auf dieser Seite.
Datenquelle ändern
Nehmen wir an, du hast eine Liste mit allen Ländern der Erde, deren Einwohnerzahlen sowie den zugehörigen Kontinenten:
Ausgangsdatei: Liste der Länder
Und daraus hast du eine kleine Pivot-Tabelle erstellt, um die Einwohnerzahlen nach Kontinent zu verdichten. So wie in meinem Beispiel hier:
Einwohner nach Kontinent
Doch leider hat sich danach etwas an den Quelldaten geändert. Es sind zwei neue Spalten dazugekommen, die du jetzt ebenfalls gerne in deiner Pivot-Tabelle auswerten möchtest:
Neue Spalten in der Quelltabelle
Wie bekommst du diese neuen Spalten nun in deiner Pivot-Tabelle angezeigt?
Stelle die aktive Zelle irgendwo innerhalb deiner Pivot-Tabelle und wähle im Menüband dann die PivotTable-Tools. Im Register „PivotTable-Analyse“ klickst du auf die Schaltfläche „Datenquelle ändern“:
Datenquelle ändern
Dadurch wird das Blatt mit den bisherigen Quelldaten angezeigt. Der aktuelle Datenbereich wird durch einen gestrichelten Laufrahmen gekennzeichnet:
[
Bisheriger Quelldaten-Bereich
Nun braucht man nur mit der Maus (oder den Pfeiltasten) den gewünschten neuen Datenbereich markieren, so dass auch die beiden zusätzlichen Spalten ausgewählt werden:
Erweiterter Datenbereich
Nach einem Klick auf OK werden auch die beiden neuen Spalten in meiner Feldliste angeboten:
Die neuen Felder in der Feldliste
Aber es geht es geht auch einfacher!
Pivot’s bester Freund: Formatierte Tabelle
Grundsätzlich empfehle ich, wo es geht, den Einsatz von formatierten Tabellen. Die bringen neben der schöneren Optik nämlich viel Komfort mit. Und insbesondere im Zusammenhang mit Pivot-Tabellen solltest du auf jeden Fall formatierte Tabellen einsetzen, denn diese beiden ergänzen sich hervorragend.
Also: Wandeln wir zunächst unsere ursprüngliche Quelltabelle in eine formatierte Tabelle um. Stelle dazu die aktive Zelle irgendwo in die Länderliste und wähle das Menü Start | Als Tabelle formatieren (oder über die Tastenkombination Strg+T):
Die Länderliste wird als Tabelle formatiert
Da unsere Liste keine leeren Zeilen und Spalten enthält, erkennt Excel automatisch den ganzen Datenbereich und setzt auch gleich das Häkchen „Tabelle hat Überschriften“:
Der Datenbereich wird automatisch erkannt
Im Menüband wirst du einen neuen Eintrag „Tabellenentwurf“ finden (in älteren Excel-Versionen heißt es hier „Tabellentools | Entwurf“), wo du beispielsweise jederzeit über die Tabellenformatvorlagen ein anderes Layout auswählen kannst. Außerdem ist es möglich, der Tabelle einen Namen zu geben und so ersetzen wir den Vorgabewert „Tabelle1“ in „tblLänder“.
Layout ändern, Tabellennamen vergeben
Nun erzeugen wir wieder eine Pivot-Tabelle über das Menü „Einfügen | PivotTable“. Und hier sieht man einen kleinen, aber entscheidenden Unterschied. Anstelle eines Zellenbezugs taucht jetzt unser Tabellenname auf:
Der Bereich verweist auf den Tabellennamen
Die Pivot-Tabelle bauen wir genau wie beim ersten Mal, also Kontinent in den Zeilenbereich und Einwohnerzahl in den Spaltenbereich. Bis hierher sieht also noch alles aus wie zuvor. Aber jetzt kommen in der Quelltabelle die neuen Spalten dazu. Der Einfachheit halber habe ich die Spalten etwas weiter rechts neben der Tabelle schon vorbereitet. Über die Tastenkombination Strg+X schneide ich sie dort aus und füge sie über Strg+V unmittelbar neben der Länderliste wieder ein:
Neue Spalten anfügen
Wie man sieht, wurden die neuen Spalten automatisch in die formatierte Tabelle aufgenommen, denn auch in diesen Spalten erscheint oben das Menü „Tabellenentwurf“:
Der Tabellenbereich wurde automatisch erweitert
Und genau das ist einer der großen Vorteile von formatierten Tabellen. Sie erweitern sich automatisch, wenn man unten an ihrem Ende neue Daten anfügt oder rechts eine komplett neue Spalte eingibt.
Wechseln wir auf das Blatt mit der Pivot-Tabelle. Alles was wir hier noch tun müssen, ist ein Rechtsklick in die Pivot-Tabelle und den Punkt „Aktualisieren“ auswählen:
Pivot-Tabelle aktualisieren
Und auf gar wundersame Weise erscheinen die beiden neuen Tabellenfelder, ganz ohne den Datenquellbereich neu angeben zu müssen.
Die neuen Felder in der Feldliste
Merke:
Pivot-Tabelle + Formatierte Tabelle = Beste Freunde
Drilldown
Sinn einer Pivot-Tabelle ist es ja, Daten verdichtet anzuzeigen. Manchmal möchte man aber sehen, welche Einzelwerte sich hinter einem verdichteten Ergebnis verbergen, ohne dass man dazu die Pivot-Tabelle verändert. Überhaupt kein Problem!
In meinem Beispiel möchte ich sehen, welche Details im Kontinent „Amerika/Westindien“ zusammengefasst sind. Dazu mache ich mit der Maus einfach einen Doppelklick auf den Wert in der Ergebnisspalte:
Drilldown per Doppelklick
Damit wird ein sogenannter „Drilldown“ ausgeführt, d.h. ich bohre mich quasi eine Stufe tiefer in die Daten hinein, und bekomme die einzelnen Länder ein einem neuen Arbeitsblatt angezeigt und kann somit genau überprüfen, welche Daten sich hinter dieser einen Zahl verbergen:
Die Datensätze hinter dem geklickten Wert
Gut zu wissen: Ich kann hinterher dieses neue Arbeitsblatt jederzeit wieder löschen. Weder die Quelltabelle noch die Pivot-Tabelle werden dadurch verändert.
Gruppieren
Das Verdichten und Gruppieren von Daten in einer Pivot-Tabelle erfolgt normalerweise automatisch auf Basis der einzelnen Datenelemente in meiner Quelltabelle. Bei einer Gruppierung nach Kontinenten werden mir somit alle Kontinente aufgeführt, die in meiner Quelltabelle vorhanden sind:
Standardgruppierung
In meiner Beispieltabelle ist Amerika in vier Teilkontinente unterteilt. Was aber, wenn ich diese vier Teilkontinente jedoch als einen einzigen darstellen möchte? Und zwar ohne die Originaldaten zu verändern?
Ganz einfach: Ich füge eine eigene Gruppierung hinzu.
Dazu markiere ich in der Pivot-Tabelle die Einträge, die ich zusammenfassen möchte (Hinweis: Die Einträge müssen nicht zwingend aufeinanderfolgend sein. Ich kann mit der STRG-Taste auch getrennte Einträge markieren). Anschließend wähle ich aus dem Menü „PivotTable-Analyse“ die Schaltfläche „Auswahl gruppieren“:
Kontinente markieren und gruppieren
Dadurch erzeugt Excel ein weiteres Datenfeld „Kontinent2“, welches die Gruppe für meine vier Amerikas enthält:
Ein neues Feld in der Feldliste
Den automatisch erzeugten Namen „Gruppe1“ kann ich in der Eingabezeile einfach überschreiben und z.B. „Amerika“ draus machen. Wenn ich dann noch das ursprüngliche Kontinent-Feld aus dem Zeilenbereich der Pivot-Tabelle entferne, sieht das ganze gleich viel besser aus:
Gruppe umbenennen, Zeilenbereich anpassen
Entfernen lässt sich die Gruppierung übrigens auch ganz leicht:
Einfach das gruppierte Element in der Pivot-Tabelle auswählen und dann die Schaltfläche „Gruppierung aufheben“ klicken:
Gruppierung wieder aufheben
Das war’s für die zweiteilige Einführungsserie! Wenn du auf den Geschmack gekommen bist und wirklich tiefer in die Welt der Pivot-Tabellen einsteigen möchtest, dann empfehle ich dir mein Buch Excel Pivot-Tabellen für Dummies.
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.
Hallo Martin,
habe deine Seite heute gefunden, finde alles ganz prima erklärt zum Thema Pivot Tabelle, aber die Lösung meines Problems habe ich auch auf deiner Seite leider nicht gefunden.
Es geht um folgendes Problem:
meine Excel Datei hat 65 Spalten und mittlerweile über 20.000 Zeilen.
Für meine Auswertungen habe ich mir diverse Pivottabellen angefertigt.
Wenn ich nun per Doppelklick auf einen Wert einer Ergebnisspalte drücke (drilldown), wird mir als Ergebnis die Tabelle mit allen 65 Spalten angezeigt.
Ich benötige aber z.Teil nur einige wenige Spalten. Bisher muss ich dann in der Tabelle alle nicht benötigten Spalten manuell löschen.
Geht das auch anders und vor allem einfacher? Bisher habe ich keine Lösung dafür gefunden, selbst unsere „Excel-Päbste“ in der Firma waren da mit ihrem Latain am Ende.
Gerne höre ich von dir und verbleibe einen guten Jahresstart wünschend
Frank Freudenberg
Hallo Frank,
mir ist keine Möglichkeit bekannt, wie du die Spalten beeinflussen könntest, die nach einem Doppelklick in die Pivot-Tabelle angezeigt werden. Dieser Drilldown zeigt immer sämtliche in der Quelltabelle enthaltenen Spalten an – woher sollte Excel auch wissen, welche du tatsächlich sehen möchtest.
Es gibt aber noch eine andere Möglichkeit: Man kann innerhalb der Pivot-Tabelle die gewünschten Felder in den Zeilenbereich übernehmen und dann über die entsprechenden Plus- und Minus-Schaltflächen ein- und ausblenden. Damit kann man die Tabelle übersichtlich halten und nur bei Bedarf die einzelnen Details direkt innerhalb der Pivot-Tabelle anzeigen lassen. Zu finden in den Pivottabel-Tools im Register Analysieren.
Eine bessere Möglichkeit ist mir leider nicht bekannt.
Schöne Grüße,
Martin
Hallo Martin,
folgende Frage: Bei mir kommt es häufiger vor, dass ich, zur besseren Übersichtlichkeit, Spalten aus- und wieder einblende (über Feldliste). Was nur ziemlich nervt ist, dass bereits geänderte Feldnamen (entfernen von „Summe von“ etc.) und Zahlenformate dadurch wieder verloren gehen. Kann man diese nicht irgendwie festsetzen?
Gruß
Udo
Hallo Udo,
ich fürchte, da sieht es schlecht aus. Man kann zwar die Feldnamen grundsätzlich ändern und z.B. auch verhindern, dass sich manuell eingestellte Spaltenbreiten wieder verändern. Wenn Du über die Feldliste ein Feld aber komplett raus nimmst und anschließend wieder einfügst, dann greifen wieder dir Grundeinstellungen. Zumindest ist mir kein Weg bekannt, das zu verhindern.
Grüße,
Martin
Hallo Martin
Danke für deine sehr gut verständlichen Tipps. Ich habe eine Frage zu vordefinierten (!) Gruppen und Pivotsortierungen.
Ich arbeite mit Ländern / Nationalitäten und würde diese gerne nach verschiedenen Gruppen auswerten (EU, Schengen, Nato, UNO, Fifa usw.). Bei den Dateneinträgen tauchen immer wieder neue Nationalitäten auf, die ich dann mühsam und manuell der richtigen Gruppierung zuordnen muss.
Ich stelle mir vor, dass ich die jeweiligen Gruppen separat definiere, so dass neue Dateneinträge dann automatisch den definierten Gruppen zugeordnet werden können.
Beispiel: bislang habe ich Datensätze mit 25 verschiedenen afrikanischen Staaten. Jetzt kommt ein neuer Datensatz dazu, der erste aus Angola. Ich will, dass dieser neue Datensatz automatisch zur Gruppe Afrika gezählt wird. Kann ich also eine Liste mit allen Staaten Afrikas / der UNO / Fifa usw. erstellen, so dass der neue Datensatz automatisch eingeordnet wird?
Bislang muss ich die Gruppen jeweils neu manuell erstellen, was keine nachhaltige Lösung ist. Kannst du mir einen Tipp geben, wie ich vorgehen muss?
Herzlichen Dank dafür.
Hallo,
der beste Weg wäre, die entsprechende Gruppe bereits in den Rohdaten aufzunehmen. Bezogen auf Dein Beispiel würde es also eine weitere Spalte in der Datentabelle geben, in der dann die jeweilige Gruppe eingetragen ist. Aber ich vermute mal, dass das in Deinen Fall vielleicht nicht möglich ist.
Einen Weg, eine Gruppe unabhängig von der Datentabelle direkt für die Pivottabelle vorzudefinieren, kenne ich leider nicht.
Grüße,
Martin
Herzlichen Dank für deine Rückmeldung.
Der von dir vorgeschlagene Weg ist für mich tatsächlich nicht möglich.
Naja, dann werd ich also weiter manuell gruppieren, aber jetzt weiss ich, dass ich es nicht besser machen kann, das erleichtert mich 🙂
Hallo Martin,
ich habe auch eine Frage zu Pivot-Tabellen in Excel:
ist es möglich den Inhalt der Pivot-Tabelle zu verändern so dass die Quelldaten sozusagen rückläufig verändert werden, also the-other-way-round? Ich hoffe du weißt wie ich das meine, ich möchte nicht mühevoll die Daten der Quelldatei ändern, sondern die Änderung in der Pivot-Tabelle vornehmen so dass die ’nderung dann übernommen werden kann.
Vielen Dank für deine Hilfe.
lg Pip
Pingback: Wenn’s auf den Einzelnen ankommt: Werte in Pivot-Tabellen ansprechen | Der Tabellen-Experte
Hallo, Martin,
Deine Excel-Tipps -insbesondere zum Thema Pivot- sind immer sehr hilfreich. Allerdings habe ich für ein Problem noch keine Lösung gefunden:
In der Pivot-Quelldatei lauten Spaltenüberschriften z. B. VK 01.18, VK 02.18, VK 03.18, usw. Nach einem Jahr sollen die bestehenden Spaltenüberschriften in VK 01.19, VK 02.19, VK 03.19, usw. geändert werden. In der Quelldatei erledige ich dies durch Suchen/Ersetzen.
In der Pivot sind dann logischerweise die alten Felder (z. B. VK 01.18) verschwunden und die neuen Felder (z. B. VK 01.19) stehen in der Feldliste und müssen nun einzeln neu eingefügt und formatiert werden (auf Summe stellen, „Summe von“ entfernen, Zweinachkommastellen, Tausendertrennung usw.). Dies ist immer ziemlich müßig, insbesondere, da immer viele Felder in der Quelldatei betroffen sind.
Kann man der Pivot sagen, dass immer der Zellbezug und nicht der Feldinhalt genommen werden soll oder gibt es vielleicht eine andere Möglichkeit?
Vielen Dank und viele Grüße
ako
Hallo Antje,
da muss ich dich leider enttäuschen: Spaltenüberschriften sind fix und daher werden die Wertfelder immer verloren gehen, wenn sich die Spaltennamen ändern.
Allerdings ist es ohnehin nicht optimal, wenn die Quelltabelle einzelne Monatsspalten enthält. Denn damit hat die Quelltabelle ja bereits ein Kreuztabellenformat. Besser wäre es stattdessen, eine einzige Monatsspalte zu haben (und damit einen fixen Namen) und für jeden Monat eine separate Zeile. Damit wird man wesentlich flexibler in der Auswertung und umgeht das beschriebene Problem.
Schöne Grüße,
Martin
Danke für den tollen Beitrag, Martin.
Du bist inzwischen zum Pivot-Tabellenexperten geworden.
Meinen Glückwunsch auch noch zur 2. Auflage Deines Buches über Pivottabellen.
Hallo Gerhard,
vielen Dank, freut mich sehr! Pivot-Tabellen sind mir mittlerweile einfach ans Herz gewachsen 🙂
Schöne Grüße,
Martin
Bei dem Thema „Datenquelle ändern“ könnte noch eine kleine Ergänzung aufgenommen werden? 🙂
++ und 1x auf klicken und ich sehe den Datenbereich.
Es soll heißen: Alt+N+P und einmal auf zurück klicken.
Die Sonderzeichen kleiner als und größer als wurden im gesendeten Text verschluckt.
Hallo Manfred,
danke für die Ergänzung für die Tastatur-Freaks unter uns 😉
Schöne Grüße,
Martin
Hallo Martin,
meine Datenquelle (aus der ich eine Pivot-Table erstelle), beinhaltet neben ganz normalen Spalteninhalten (Zahlen, Text) nun mal leider auch eine Spalte mit Grafiken! Wenn ich diesen Feldnamen (der die Grafiken enthält) in die Pivot-Zeilen ziehe, zeigt der Feldname (Leer) an.
Na gut…Datenleer, aber Grafik ist doch immer noch mehr als Leer?
Muss ich das Feld mit irgendwelchen Eigenschaften ausstatten, damit ich die Grafiken in der Pivot sehen kann?
Beste Grüße
Carsten
Hallo Carsten,
Grafiken (Bilder, Formen etc.) befinden sich nicht in einer Zelle, sondern schweben als eigenständige Objekte gewissermaßen über den Zellen. Daher können Grafiken auch nicht in Pivot-Tabellen dargestellt werden.
Schöne Grüße,
Martin
Hallo Martin,
manche Felder, die ich einmal gruppiert habe, verschwinden trotzdem nicht aus der Feldwertliste nachdem ich die Gruppierung aufgelöst habe. Woran kann das liegen?
LG Steffi
Hallo Steffi,
merkwürdig, spontan habe ich da jetzt auch keine Erklärung dafür. Falls du für ein Feld mehrere manuelle Gruppierungen eingerichtet hast: Bist du sicher, dass du sie dann auch wirklich für jede Gruppe wieder aufgehoben hast? Am einfachsten geht es, wenn man die aktive Zelle in der Pivot-Tabelle open direkt auf die jeweilige Spaltenüberschrift stellt und dann die Gruppierung aufhebt. Dann werden auch sämtliche Gruppen in der betreffenden Spalte aufgehoben.
Einen anderen Tipp habe ich leider auch nicht.
Schöne Grüße,
Martin
Danke für den Artikel!
Ich würde gerne die Quelle einer Pivottabelle von einer formatierten Tabelle auf eine externe Datenquelle umstellen. Im Pivot-Tabelle-Datenquelle ändern-Dialog ist jedoch „externe Datenquelle verwenden“ ausgegraut. Haben Sie zufällig eine Lösung dafür?
lg Siegi
Hallo Siegi,
es ist leider nicht möglich, die Datenquelle einer bestehenden Pivot-Tabelle von einer Tabelle auf eine externe Verbindung zu ändern. Hier musst du die Pivot-Tabelle neu erstellen und direkt die gewünschte externe Verbindung angeben (Menü „Einfügen | PivotTable | Aus externer Datenquelle)
Schöne Grüße,
Martin