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:
- Der Original-Artikel: Besser als SVERWEIS: Alle Werte finden
- Alternative 1: FILTER-Funktion (nur Office 365)
- Alternative 2: Der Spezialfilter
- Alternative 3: Eine Pivot-Tabelle – davon handelt der heutige Artikel
- Alternative 4: Power Query
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.
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:
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
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
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
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
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
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
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
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
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
Im nächsten Fenster gibst du dann die gesuchten Einwohnergrenzen ein und erhältst nach einem Klick auf „OK“ das entsprechende Ergebnis:
Wertebereiche angeben
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.
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.
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
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