Viele Anwender stehen vor der Aufgabe, auf Basis eines Filterkriteriums einen kleinen Auszug einer vorhandenen Tabelle an anderer Stelle auszugeben. Der klassische SVERWEIS versagt hier, da er bekanntermaßen nur ein einziges Ergebnis liefert, auch wenn es mehrere Treffer gibt.
Eine Lösungsmöglichkeit hatte ich vor langer Zeit im Artikel „Besser als SVERWEIS: Alle Werte finden“ aufgezeigt, der sich immer noch sehr großer Beliebtheit erfreut. Aber es gibt eine ganze Reihe anderer Lösungsansätze, die ich in diesem und ein paar weiteren Artikeln vorstellen möchte.
- Der Original-Artikel: Besser als SVERWEIS: Alle Werte finden
- Alternative 1: FILTER-Funktion (nur Office 365)
- Alternative 2: Der Spezialfilter – davon handelt der heutige Artikel
- Alternative 3: Eine Pivot-Tabelle
- Alternative 4: Power Query
Und so geht’s:
Die Ausgangslage
Um die verschiedenen Möglichkeiten besser vergleichbar zu machen, verwende ich wieder eine Liste mit Ländern und deren Millionenstädten. Das Ziel soll sein, nach der Auswahl eines Landes sämtliche Millionenstädte des betreffenden Landes in einer separaten Liste auszugeben.
Beispiel: Länder mit Millionenstädten
Die Beispieldatei kannst du dir hier herunterladen.
Die Lösung: Spezialfilter
Neben diversen mehr oder weniger komplexen Formellösungen bietet Excel auch eine gerne übersehene Filterfunktion an: Den Spezialfilter. Dieser ist zu finden im Menü „Daten | Erweitert“
Der Spezialfilter ist gut versteckt
Im sich nun öffnenden Fenster passen wir die Einstellungen an unsere Beispieldatei an. Das Wichtigste zuerst: Bei den Aktionen muss die zweite Option „An eine andere Stelle kopieren“ ausgewählt werden, ansonsten werden die Ergebnisse direkt in der Länderliste gefiltert.
Optionen des Spezialfilters
Als nächstes markieren wir im Feld „Listenbereich“ die Ausgangstabelle. Achte darauf, dass auch die Zeile mit den Überschriften enthalten ist.
Das Feld „Kriterienbereich“ verweist im Beispiel auf die Zellen E1:E2. Dabei muss E1 den exakten Feldnamen enthalten, so wie er auch in der Ausgangstabelle geschrieben ist. Das Feld E2 unmittelbar darunter enthält dann das gesuchte Land.
Da die gefilterten Ergebnisse in eine separate Tabelle geschrieben werden soll, muss der gewünschte Bereich noch im Feld „Kopieren nach“ eingetragen werden. Da wir ja nicht wissen, wieviele Treffer wir erhalten werden, reicht es aus, nur einen Überschriftenbereich zu markieren. In meinem Beispiel habe ich dafür den grün markierten Bereich E4:F4 vorgesehen und die Namen der gewünschten Ausgabespalten dort schon eingetragen. Auch hier ist auf die exakte Schreibweise zu achten.
Die Option „Keine Duplikate“ können wir in unserem Beispiel ignorieren. Falls du aber später mit anderen Daten arbeitest, kannst du über dieses Häkchen etwaige Dubletten ausschließen.
Nach einem beherzten Klick auf OK wird auch schon das gewünschte Ergebnis angezeigt:
Die gefilterte Ergebnisliste
War doch gar nicht so schwer, oder? Wenn du anschließend nach einem anderen Land filtern möchtest, musst du nur wieder das Menü „Daten | Erweitert“ aufrufen. Diesmal musst du jedoch nur die Aktion „An eine andere Stelle kopieren“ auswählen, die drei verschiedenen Tabellenbereiche hat Excel praktischerweise automatisch vom letzten Aufruf übernommen.
Noch schneller als über das Menü geht es, wenn du dir diesen Filter oben in die Symbolleiste für den Schnellzugriff legst:
Symbolleiste für den Schnellzugriff anpassen
Im Optionsfenster wählst du oben „Registerkarte Daten“ und markierst dann in der Liste der Befehle den Eintrag „Spezialfilter“. Über die „Hinzufügen“-Schaltfläche wird dieser Menüpunkt nach rechts in die Schnellzugriffsleiste übernommen und mit OK bestätigt:
Den Spezialfilter in die Symbolleiste aufnehmen
Ab sofort kannst du den Spezialfilter über das neue Filtersymbol ohne irgendwelchen Umwege aufrufen:
Das neue Filtersymbol
Zusätzliches Filterkriterium
Selbstverständlich kannst du auch ein weiteres Filterkriterium einrichten, um zum Beispiel zusätzlich zum Land auch die Einwohnerzahl einzuschränken. Dazu wird rechts neben den vorhandenen Filterbereich in den Zellen F1:F2 das Einwohnerfeld und ein Eingabefeld vorgesehen.
Ein zweites Filterkriterium
In dem Eingabefeld kannst du jetzt eine exakte Einwohnerzahl eintippen oder die zusätzlich Vergleichsoperatoren <, >, <= und >= angeben, um flexibler zu suchen.
Wichtig:
Im Fenster des Spezialfilters musst du jetzt den Kriterienbereich natürlich an die neuen Verhältnisse anpassen, damit du auch das gewünschte Ergebnis bekommst:
Das Ergebnis des erweiterten Filters
Um eine Bandbreite von Einwohnern zu definieren, musst du das Einwohner-Feld ein zweites Mal angeben und anschließend auch hier wieder nicht vergessen, den Kriterienbereich anzupassen:
Bandbreite einschränken
Immer wenn mehrere Kriterienfelder nebeneinander in der gleichen Zeile stehen, werden die Suchkriterium über ein logisches UND verknüpft. Das heißt, alle Kriterien müssen erfüllt sein.
Was ist nun, wenn ich stattdessen zwei verschiedene Länder angeben möchte? Hier kann ich nicht einfach das Land-Feld ein zweites Mal in die gleiche Zeile schreiben, denn ich benötige ein logisches ODER.
Noch mehr Flexibilität
Aber auch das ist kein Problem. Für diesen Zweck schreibe ich das zweite (oder dritte) Land einfach in eine neue Zeile. Und für die maximale Flexibilität kann ich für dieses zweite Land eigene Einwohnerzahlen als Auswahlkriterium festlegen. Im folgenden Bild sind die Kriterien folgendermaßen zu lesen:
- Zeige alle Städte in China mit mindestens 3 Millionen und maximal 9 Millionen Einwohnern
- Zeige alle Städte in Brasilien mit weniger als 2 Millionen Einwohnern
Maximale Flexibilität beim Filtern
Hier sind also nur durch deine Fantasie Grenzen gesetzt. Und du musst zugeben, dass diese Lösung deutlich einfacher ist als ein Matrix-Formel-Monster!
Eine weitere Lösungsvariante werde ich im nächsten Artikel vorstellen.
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.
Dass er die Ergebnisse bei einer Änderung des Landes im Dropdown-Feld nicht selbständig aktualisiert, ist für mich ein KO-Kriterium. Bei relativ offensichtlichen Werten, wie Ländern und Städten kann man oft noch erkennen, falls die Ergebnisse noch nicht aktualisiert worden sind. Aber wenn man z.B. Zweigstellen und Mitarbeiter hätte, dann kann es sicher schnell vorkommen, dass man zwar im Dropdown-Feld eine neue Zweigstelle gewählt hat, aber vergisst, den Filter manuell zu aktualisieren.
Ist das überhaupt logisch, dass das nicht automatisch passiert?
Hallo Tim,
ja, diese Variante ist sicherlich nicht für alle Anwendungsfälle geeignet. Und natürlich wäre es schöner, wenn sich alles automatisch aktualisieren würde. Aber das ist beim Spezialfilter halt einfach nicht vorgesehen.
Schöne Grüße,
Martin
Für eine Kollegin habe ich einmal eine einfache Lösung geschrieben, die per VBA auf Änderungen im Filterbereich reagiert hat und den Spezialfilter automatisch aktualisiert hat.
Hallo Martin,
mich würde interessieren, ob der Listenbereich auch in einem anderen Tabellenblatt liegen kann?
Hallo Namensvetter 🙂
ja, der Listenbereich kann auch in einem anderen Tabellenblatt liegen. Der Spezialfilter funktioniert sogar dann, wenn alle drei Bereiche (Listenbereich, Kriterienbereich und Ausgabebereich) in drei verschiedenen Blättern liegen.
Schöne Grüße,
Martin
Hallo Zusammen,
vielleicht kann mir aus dem Blog ja jemand helfen. Habe bisher hier nichts dazu gefunden um den mathematischen Wert Null „0“ in allen Möglichkeiten darzustellen.
Ausgangssituation ist wie folgt.
in Zelle x entscheide ich über eine dropdown Funktion, ob eine Berechnung aus Zelle y und z erfolgen soll, hier y + z = A = Ergebnis
y und z sind alle natürlichen Zahlen inklusive „Null“.
In der Zelle A soll also das Ergebnis aus der Berechnung von y und z stehen bei aktiver dropdown Funktion in Zelle x.
Das Ergebnis in Zelle A ist solange in Ordnung solange die Zellen y und x natürliche Zahlen inklusive „0“ sind und die dropdown Funktion aktiv ist.
Und jetzt mein Problem:
Ist die Zelle y und z leer (kein Eintrag) soll in Zelle A auch kein Ergebnis stehen (auch nicht „0“) bei aktiver dropdown Funktion.
Vielleicht hat jemand eine Idee.
Danke im Voraus
Michael
Hallo Michael,
mir fallen spontan zwei Möglichkeiten ein:
Entweder du arbeitest mit einer WENN-Abfrage. Also etwa in der Art
=WENN(Dropdown gesetzt;Y+Z;““)
Damit wird die Berechnung eben nur ausgeführt, wenn das Dropdown gesetzt wurde. Ansonsten wird ein leerer Wert in die Zelle geschrieben (zweimal doppelte Anführungszeichen)
Oder du arbeitest mit einer bedingten Formatierung, die die Schriftfarbe in der Zelle auf weiß setzt, wenn das Ergebnis der Berechnung 0 ist. Der Wert steht natürlich trotzdem in der Zelle, wird nur nicht mehr angezeigt.
Schöne Grüße,
Martin
Ich nutze den Spezialfilter schon seit Jahren. Heute hat er allerdings das erste mal ein falsches Ergebnis ausgegeben. Es wurden eine Handvoll Zeilen dargestellt, die eigentlich nicht sichtbar sein sollten laut dem angegebenen Kriterienbereich. Das hat mich extrem irritiert. Ich habe alle Möglichkeiten bereits ausgeschlossen (Formatierung, Datei selbst) und konnte es auch auf ein Minimalbeispiel herunterbrechen. Falls du Interesse hast, dieses Mysterium zu lösen, kannst du mir gerne schreiben.
VG, Marcel
Hallo Marcel,
du kannst mir die Datei an info@tabellenexperte.de schicken, dann werde ich sie mir bei Gelegenheit mal ansehen.
Schöne Grüße,
Martin