Alle Treffer ausgeben: Die Pivot-Tabelle 2

Eine einfache Methode, um gesuchte Daten aus einer Tabelle herauszufiltern
 

Heute gibt es den nächsten Teil einer kleinen Artikelserie zu folgender Aufgabenstellung:
In einer Tabelle soll nach einem bestimmten Begriff oder Wert gesucht werden und dazu sollen alle Treffer an einer anderen Stelle ausgegeben werden.

Bekanntlich führen viele (alle?) Wege nach Rom, und so gibt es auch für dieses Problem ganz unterschiedliche Lösungsansätze, die alle ihre Vor- und Nachteile haben: Relative komplizierte Array-Formeln, die ganz neue FILTER-Funktion oder ein Spezialfilter. Der heutige Artikel löst das Problem ganz banal mit einer Pivot-Tabelle.

Und so geht’s:

Bevor es losgeht, hier noch einmal die Übersicht über die verschiedenen Möglichkeiten:

Ein vielseitiges Analysewerkzeug auf Abwegen

Die Pivot-Tabelle ist eines der mächtigsten und – entgegen aller Vorurteile – sehr einfach zu bedienenden Analysewerkzeuge, die Excel zu bieten hat. Es müssen nicht immer nur komplexe Auswertungen sein, für die man Pivot-Tabellen nutzen kann. Auch für die heutige Filter-Aufgabe sind sie bestens geeignet.

Für alle, die bisher noch nicht so viel mit Pivot-Tabellen zu tun hatten, gibt es erst noch ein wenig Hintergrundinformationen.

Exkurs: Grundvoraussetzungen für Pivot-Tabellen
Damit man überhaupt Pivot-Tabellen einsetzen kann, müssen die Quelldaten einige wenige Voraussetzungen erfüllen.

  • Keine leeren Spalten
  • Keine verbundenen Zellen
  • Jede Spalte braucht eine Spaltenüberschrift

Sollte eine dieser Bedingungen nicht erfüllt sein, reagiert Excel beim Versuch, eine Pivot-Tabelle zu erstellen, mit einer Fehlermeldung.

Leere Zeilen dürfen grundsätzlich in der Tabelle enthalten sein, man muss dann aber den Datenbereich zuvor von Hand markieren, bevor man die Pivot-Tabelle einfügen kann.
Und die Spaltenüberschriften sollten idealerweise eindeutig sein, ansonsten hängt Excel später in den Pivotfeldern zur Unterscheidung einfach eine fortlaufende Nummer an den Feldnamen an.

Tipp:
Noch besser ist es, wenn du deine Liste direkt in eine formatierte Tabelle umwandelst (Menü „Start | Als Tabelle formatieren“). Dann musst du später den Datenbereich für die Pivot-Tabelle auch dann nicht mehr anpassen, wenn neue Datensätze in der Quelltabelle dazukommen.

Falls du bisher um Pivot-Tabellen einen großen Bogen gemacht hast, empfehle ich dir diese beiden Einführungsartikel:

Und falls du richtig tief in die Pivot-Tabellen einsteigen möchtest, solltest du dir den Online-Kurs von Lukas Rohr anschauen.

So, nachdem das geklärt wäre, können wir die Pivot-Tabelle erstellen (die Beispieldatei kannst du dir hier herunterladen). Positioniere dazu die aktive Zelle irgendwo innerhalb der Länderliste und rufe dann das Menü „Einfügen | PivotTable“ auf:

Die Länderliste als Datenquelle

Die Länderliste als Datenquelle

Da es sich in unserem Beispiel um eine durchgängige Liste ohne Leerzeilen handelt, markiert Excel automatisch den gesamten Datenbereich:

Einfügen einer Pivot-Tabelle

Einfügen einer Pivot-Tabelle

Wir wollen die Pivot-Tabelle aber direkt neben der Länderliste ausgeben lassen. Daher wählen wir die Option „Vorhandenes Arbeitsblatt“ und geben im Feld „Ziel“ die Zelle E3 an:

Anpassung der Erstellungsoptionen

Anpassung der Erstellungsoptionen

Nach einem Klick auf „OK“ wird ganz oben der leere Pivot-Tabellenbereich eingefügt. Außerdem blendet Excel am rechten Fensterrand den Arbeitsbereich „PivotTable-Felder“ ein:

Der leere Pivot-Bereich

Der leere Pivot-Bereich

Und nun wird es ganz einfach: Du musst nur die einzelnen Felder aus der Feldliste mit der Maus nun unten in den jeweiligen Pivot-Bereich ziehen.

  • „Stadt“ in den Zeilenbereich
  • „Einwohner“ in den Wertebereich
  • „Land“ in den Filterbereich
Die Pivot-Felder in den entsprechenden Bereich ziehen

Die Pivot-Felder in den entsprechenden Bereich ziehen

Jetzt noch kurz die Spaltenbreiten angepasst und für die Einwohner ein besser lesbares Zahlenformat gewählt. Dazu führst du einen Rechtsklick auf einen beliebigen Einwohnerwert aus und wählst dort den Menüpunkt „Zahlenformat…“

Das Zahlenformat anpassen

Das Zahlenformat anpassen

Hinweis:
Der Unterschied zwischen den beiden Menüpunkten „Zellen formatieren…“ und „Zahlenformat…“ ist klein aber fein:
„Zellen formatieren…“ bezieht sich nur auf die markierte Zelle oder den markierten Zellenbereich, „Zahlenformat…“ hingegen bezieht sich auf das komplette Wertefeld, auch wenn nur eine einzige Zelle markiert wurde.

Wähle dann als Zahlenformat wählst du dann die Kategorie „Zahl“, ohne Dezimalstellen, aber mit 1000er-Trennzeichen:

Keine Dezimalstellen, 1000er-Trennzeichen

Keine Dezimalstellen, 1000er-Trennzeichen

Und das war’s auch schon. Jetzt kannst du nach Belieben im Filterfeld ein Land auswählen. Möchtest du nach mehreren Ländern gleichzeitig filtern, musst du nur das Häkchen „Mehrere Elemente auswählen“ setzen:

Nach einem Land filtern

Nach einem Land filtern


Die gefilterte Pivot-Tabelle

Die gefilterte Pivot-Tabelle

Die Einwohnerzahl als weiteres Filterkriterium

Das war doch wirklich einfach bis hierher, oder? Und keine Angst, es wird auch nicht schwieriger 🙂
Wenn du nun zusätzlich zum Land auch noch die Einwohnerzahl als weiteres Filterkriterium nutzen möchtest, öffnest du in der Pivot-Tabelle das Auswahlfeld „Stadt“, wählst dort den Eintrag „Wertefilter“ und dann den gewünschten Vergleichsoperator, z.B. „Zwischen…“

Zusätzlichen Wertefilter setzen

Zusätzlichen Wertefilter setzen

Im nächsten Fenster gibst du dann die gesuchten Einwohnergrenzen ein und erhältst nach einem Klick auf „OK“ das entsprechende Ergebnis:

Wertebereiche angeben

Wertebereiche angeben


Die gefilterten Ergebnisse

Die gefilterten Ergebnisse

Fertig!

Wie du siehst, sind Pivot-Tabellen wirklich kinderleicht und für viele Zwecke einsetzbar. Und anders als beim Spezialfilter, den ich im letzten Artikel vorgestellt habe, wird sofort das neue Ergebnis angezeigt, sobald man die Filter ändert. Lediglich, wenn sich an den Quelldaten etwas verändert, neue Datensätze dazukommen oder vorhandene entfernt werden, muss die Pivot-Tabelle über einen Rechtsklick in die Option „Aktualisieren“ aufgefrischt werden.

 

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.

2 Gedanken zu “Alle Treffer ausgeben: Die Pivot-Tabelle

  • Benrhard ug

    Mit Pivot zu arbeiten schätze ich ebenfalls sehr.
    Leider ist bei einem Ergebnisausdruck nicht erkennbar, welche Filtergrenzen gerade gelten.
    Deshalb die Frage, ist es möglich hier auf elegante Weise alle Filtereinstellungen in einem Ergebnis darzustellen ?
    M.E. eine wichtige Information für jeden Betrachter der die Tabelle nicht selbst erstellt hat.
    Also z.B., bei einer Meeting-Vorlage etc.

    mfg
    Benrhard Hug

    • Martin Weiß Autor des Beitrags

      Hallo Bernhard,

      ja, es ist leider ein Schwachpunkt von Pivot-Tabellen, dass man mehrere gesetzte Werte in einem Filter leider nicht direkt erkennen kann. Aber es gibt tatsächlich eine sehr komfortable Alternative: Datenschnitte.
      Dazu hatte ich vor längerer Zeit mal einen Artikel geschrieben:
      Bequemer filtern mit Datenschnitten

      Diese Datenschnitte lassen sich beliebig im Arbeitsblatt positionieren und auch in Größe und Layout anpassen. Und man sieht vor allem, was genau gefiltert wird.

      Schöne Grüße,
      Martin