Der beste Freund des Excel-Analysten: Pivot-Tabellen 20

Mit Pivot-Tabellen lässt sich umfangreiches Datenmaterial kinderleicht analysieren.
 

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 (am Ende des Artikels findest du einen Link, mit dem du die Musterdatei auf deinen PC herunterladen kannst):

Datentabelle: Hauptstädte

Datentabelle: Hauptstädte

Bevor wir nun die Pivot-Tabelle erstellen, noch ein paar generelle Hinweise zur Datentabelle, die analysiert werden soll:

  • Jede Spalte sollte eine eindeutige Überschrift besitzen
  • Überschriften sollten 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.

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:

Pivot-Tabelle einfügen

Pivot-Tabelle einfügen

Daraufhin wird um die Datentabelle herum ein gestrichelter Laufrahmen angezeigt und es erscheint das folgende Eingabefenster:

Pivot-Tabelle erstellen

Pivot-Tabelle erstellen

Im ersten Feld kannst du nochmal den ausgewählten Datenbereich überprüfen. Außerdem empfehle ich, die Option “Neues Arbeitsblatt” auszuwählen, denn damit wird die Pivot-Tabelle auf einem eigenen Blatt erstellt. Mit einem Klick auf “OK” wird die zunächst noch leere Pivot-Tabelle erzeugt:

Noch leere Pivot-Tabelle

Noch leere Pivot-Tabelle

Hier gibt es den eigentlichen Datenbereich (blaue Ränder), den wir gleich befüllen lassen. Zusätzlich erscheint am rechten Rand eine Liste mit Feldern und verschiedenen Gruppierungsbereichen.

Hinweis:
Die Feldliste wird ausgeblendet, wenn sich die aktive Zelle außerhalb der leeren Pivot-Tabelle befindet. Ebenso sind die blauen Rahmen verschwunden. In diesem Fall muss man die aktive Zelle nur wieder in die Pivot-Tabelle stellen, und schon ist die Feldliste wieder sichtbar:

Pivot-Tabelle aktiv oder nicht

Pivot-Tabelle aktiv oder nicht

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 klicken wir mit der Maus auf den Feldnamen “Kontinent” und ziehen ihn einfach nach unten in den Bereich “Zeilen”:

Zeilenbeschriftung auswählen

Zeilenbeschriftung auswählen

Damit haben wir schon einmal festgelegt, was in den Zeilen dargestellt werden soll:

Zeilenbeschriftung

Zeilenbeschriftung

Als nächstes wählen wir das Feld “Einwohnerzahl” und ziehen es mit der Maus nach unten in die Gruppe “Werte”:

Erste Wertedimension

Erste Wertedimension

Da es sich bei den Einwohnern um ein Zahlenfeld handelt, verwendet Excel automatisch die Summenfunktion. Damit sehen wir in unserer Pivot-Tabelle schon die Summe sämtlicher Hauptstadtbewohner je Kontinent.

Nun wollen wir noch wissen, auf wieviele Hauptstädte sich diese Einwohner verteilen. Ziehen wir also das Feld “Hauptstadt” ebenfalls nach unten in die Werte-Gruppe. Und da es sich bei den Hauptstädten um ein Textfeld handelt, verwendet Excel in diesem Fall die Anzahl-Funktion:

Zweite Wertedimension

Zweite Wertedimension

Fine-Tuning

Um die Pivot-Tabelle besser lesbar zu machen, wollen wir das Zahlenformat der Einwohnerspalte mit einem Tausendertrennpunkt versehen. Da eine Pivot-Tabelle ein dynamisches Objekt ist, das heißt, es könnten weitere Datensätze dazukommen, nehmen wir die Formatierung nicht direkt in den Zellen vor, sondern benutzen eine spezielle Funktion.

Stellen wir dazu die aktive Zelle irgendwo in die Spalte mit den Einwohnerzahlen. Dann wählen wir oben in der Menüleiste aus den Pivot-Table-Tools das Register “Analysieren” (in Excel 2013) bzw. “Optionen” (in Excel 2007/2010). Hier gibt es die Schaltfläche “Feldeinstellungen”:

Pivottable-Tools

Pivottable-Tools

Wichtig: Die Feldeinstellungen beziehen sich immer nur auf das momentan ausgewählte Wertefeld. Daher ist es wichtig, dass die aktive Zelle in der richtigen Spalte steht. Im sich nun öffnenden Dialogfenster klicken wir auf die etwas unscheinbare Schaltfläche “Zahlenformat”:

Wertfeldeinstellungen

Wertfeldeinstellungen

Jetzt können wir das gewünschte Zahlenformat einstellen und mit OK übernehmen. Sieht doch schon besser aus:

Angepasstes Zahlenformat

Angepasstes Zahlenformat

Erweiterte Pivot-Funktionen

Pivot-Tabellen bieten noch weitere eingebaute Zusatzfunktionen, von denen wir uns eine noch ansehen wollen. Dazu ziehen wir das Feld “Einwohnerzahl” ein zweites Mal in die Werte-Gruppe, so dass wir zunächst folgende Ansicht erhalten:

Eine weitere Wertespalte

Eine weitere Wertespalte

Jetzt stellen wir die aktive Zelle irgendwo in unsere zweite Einwohnerspalte und rufen erneut die Feldeinstellungen auf:

Wertefeld anpassen

Wertefeld anpassen

Im Dialogfenster öffnen wir nun das Register “Werte anzeigen als” und wählen dort aus der Liste die Option “% des Gesamtergenisses”:

Anzeige als Prozentanteil

Anzeige als Prozentanteil

Und so sieht unsere Tabelle jetzt aus:

Angepasste Pivot-Tabelle

Angepasste 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.

Download: Pivot_Übungsdatei.xls

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.



Kommentar erstellen

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

20 Gedanken zu “Der beste Freund des Excel-Analysten: Pivot-Tabellen

    • Martin Beitragsautor

      Hallo Sebastian,

      sehr gerne. Manchmal sind die Dinge tatsächlich nicht so kompliziert, wie man glaubt…

      Grüße,
      Martin

  • Cornelia

    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

    • Martin Beitragsautor

      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

  • Frank Schröder

    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

    • Martin Weiß Beitragsautor

      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

  • Ingo

    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

    • Martin Weiß Beitragsautor

      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

  • Axel

    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?

  • Axel

    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

    • Martin Weiß Beitragsautor

      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

  • Walti

    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

  • Dirk

    Sehr gut. Ist es auch möglich die Spaltenbeschriftung, also in dem Beispiel in der Feldliste unter Spalten “Werte” auch nicht anzeigen zu lassen?

    • Martin Weiß Beitragsautor

      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