Alle Treffer ausgeben: Der Spezialfilter 7

Ein alltägliches Problem für Excel-Anwender - und verschiedene Lösungsmöglichkeiten dafür!
 

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.

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

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

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

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

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

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

Den Spezialfilter in die Symbolleiste aufnehmen

Ab sofort kannst du den Spezialfilter über das neue Filtersymbol ohne irgendwelchen Umwege aufrufen:

Das neue Filtersymbol

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

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

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

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

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.

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

7 Gedanken zu “Alle Treffer ausgeben: Der Spezialfilter

  • Avatar-Foto
    Tim

    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?

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      Jochen

      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.

  • Avatar-Foto
    Martin

    Hallo Martin,
    mich würde interessieren, ob der Listenbereich auch in einem anderen Tabellenblatt liegen kann?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Michael Schröter

    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

    • Avatar-Foto
      Martin Weiß

      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