Der Horror geht weiter: Pivot-Tabellen Teil 2 13

Die Arbeit mit Pivot-Tabellen: Datenquelle ändern, Drilldown und eigene Gruppierungen von Daten
 

(Ü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.

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

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

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

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

Datenquelle ändern

Dadurch wird das Blatt mit den bisherigen Quelldaten angezeigt. Der aktuelle Datenbereich wird durch einen gestrichelten Laufrahmen gekennzeichnet:
[

Bisheriger Quelldaten-Bereich

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

Erweiterter Datenbereich

Nach einem Klick auf OK werden auch die beiden neuen Spalten in meiner Feldliste angeboten:

Die neuen Felder in der Feldliste

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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, das kürzlich in einer erweiterten Neuauflage erschienen ist.
 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

13 Gedanken zu “Der Horror geht weiter: Pivot-Tabellen Teil 2

  • Frank

    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

    • Martin Weiß Autor des Beitrags

      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

  • Udo Stahl

    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

    • Martin Weiß Autor des Beitrags

      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

  • ko_lohr

    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.

    • Martin Weiß Autor des Beitrags

      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

      • ko_lohr

        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 🙂

  • Pip

    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

  • Antje Köhler

    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

    • Martin Weiß Autor des Beitrags

      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

    • Martin Weiß Autor des Beitrags

      Hallo Gerhard,

      vielen Dank, freut mich sehr! Pivot-Tabellen sind mir mittlerweile einfach ans Herz gewachsen 🙂

      Schöne Grüße,
      Martin