Eine Funktion liefert alle Treffer 18

Eine neue Tabellenfunktion in Excel liefert ALLE gesuchten Werte
 

Vor etwa 4 Jahren hatte ich einen Artikel unter dem Titel „Besser als SVERWEIS: Alle Werte finden“ veröffentlicht. Damit habe ich offensichtlich einen Schmerzpunkt bei vielen Excel-Anwendern angesprochen, denn dieser Beitrag ist einer der beliebtesten und am meisten kommentierten überhaupt.

Die dort gezeigte Formel, um das Problem zu lösen, ist jedoch relativ kompliziert und für Otto-Normal-Excelanwender nur schwer durchschaubar. Mittlerweile sind 4 Jahre vergangen und Excel hat sich weiterentwickelt. Und bekanntlich führen viele Wege nach Rom. Grund genug also, in diesem und den folgenden Artikeln ein paar Alternativen aufzuzeigen.

Den Anfang macht eine geradezu unfassbar einfache Lösung. Worin genau das genannte Problem besteht und wie die heutige Lösung aussieht, erfährst du in diesem Beitrag.

Und so geht’s:

Das Problem

Wie jeder Anwender weiß, der sich mit dem SVERWEIS schon einmal auseinandersetzen durfte, lassen sich mit dieser Funktion zwei Tabellen gegeneinander abgleichen, die wenigstens ein gemeinsames Kriterium aufweisen. Hat man beispielsweise eine Liste mit den Lagerbeständen nach Artikelnummer, lassen sich dazu aus einer zweiten Liste die Verkaufspreise pro Artikel einlesen, indem über die Artikelnummer aus der ersten Liste die Preisliste durchsucht wird.

Die SVERWEIS-Funktion liefert also den gefundenen Verkaufspreis. Das funktioniert in dem beschriebenen Beispiel wunderbar, denn es gibt zu jeder Artikelnummer üblicherweise genau einen Verkaufspreis.

Wenn man aber Daten hat, für die es mehrere Treffer gibt, hilft der SVERWEIS nicht weiter, denn der kann immer nur den ersten gefundenen Wert ausgeben. Er scheitert also bei Fragestellungen wie:

  • Zeige mir aller Adressen zu PLZ 85465
  • Zeige mir alle Millionenstädte eines bestimmten Landes
  • Zeige mir alle Aufträge für den Artikel XYZ

Im eingangs erwähnten Beitrag Besser als SVERWEIS: Alle Werte finden hatte ich dafür ein echtes Formelmonster vorgestellt, welches die Funktionen INDEX, KGRÖSSTE, ZÄHLENWENN und ZEILE miteinander kombinierte und obendrein noch als Array-Funktion eingegeben werden musste (Strg+Umschalt+Eingabe). Aber die Formel funktioniert und schließlich zählt ja das Ergebnis.

Die Lösung im Jahr 2020

Excel bietet in der aktuellsten Version im Rahmen von Office 365 einige geniale neue Funktionen, die mittelfristig alles auf den Kopf stellen werden, was man bisher aus Formeln so kennt. Sogenannte dynamische Array-Funktionen. Einen kleinen Vorgeschmack darauf hatte ich in den Artikeln hier und hier schon einmal gegeben. Und jetzt kommt leider auch die schlechte Nachricht:

Die für die heutige Lösung vorgestellten Funktionen FILTER und SORTIEREN stehen dir heute (Stand April 2020) nur zur Verfügung, wenn du Office 365 im monatlichen Kanal nutzt. Ob das bei dir der Fall ist, siehst du im Menü Datei | Konto:

Welche Office-Version ist im Einsatz?

Welche Office-Version ist im Einsatz?

Anwender des halbjährlichen Kanals müssen sich voraussichtlich noch bis Juli gedulden, mehr dazu findest du auf dieser Seite von Microsoft: FILTER-Funktion

Alle Nicht-Office-365-Anwender kommen leider überhaupt nicht in den Genuss dieser Funktionen. In diesem Fall musst du entweder auf meine klassische Variante oder ein paar andere Alternativen zurückgreifen.

Ok, genug der langen Vorrede, wie sieht jetzt die neue Lösung aus?

Ich verwende das gleiche Beispiel wie in meinem ursprünglichen Artikel, um dir den Vorher-Nachher-Effekt besser demonstrieren zu können. Die Datei kannst du dir hier herunterladen. Ausgangspunkt ist eine Liste mit Ländern und den dazugehörigen Millionenstädten. Die Aufgabe ist es, zu einem angegebenen Land in einer Tabelle daneben sämtliche dazu passenden Städte aufzulisten:

Die Ausgangsliste

Die Ausgangsliste

Und mit dieser einfachen Formel löst du das Problem:
=FILTER(B2:C309;A2:A309=$F$1)

Lösung mit der FILTER-Funktion

Lösung mit der FILTER-Funktion

Ganz allgemein hat die FILTER-Funktion folgende Syntax:
=FILTER(Matrix;Einschließen;Wenn_leer)

„Matrix“ enthält den zu filternden Quellbereich.
„Einschließen“ gibt das Filterkriterium an. In meinem Bereich also der Vergleich von Spalte A mit dem Land in Zelle F1.
Mit dem letzten Parameter „Wenn_leer“ könnte man noch einen Wert definieren, der zurückgeliefert wird, wenn nichts gefunden wird. Also beispielsweise:

Optionaler Parameter

Optionaler Parameter

Ohne diesen optionalen Parameter gibt die Formel einen #KALK!-Fehler aus.

Das Besondere an der FILTER-Funktion ist, dass die Funktion automatisch mehrere Ergebnisse zurückliefern kann, denn es handelt sich um eine dynamische Array-Funktion. Alle bisher bekannten Funktionen, wie beispielsweise SUMME, SVERWEIS usw. können zwar mehrere Zellen als Eingabe verarbeiten, aber immer nur ein einzelnes Ergebnis ausgeben.

Die FILTER-Funktion hingegen wird nur in die jeweils erste Zelle eingegeben und läuft automatisch in alle anderen Zellen über, je nachdem, wie viel Platz benötigt wird. Man erkennt das an dem blauen Rahmen, der den Ausgabebereich umgibt, sobald sich die aktive Zelle innerhalb des Bereichs befindet:

Die Formel läuft über

Die Formel läuft über

Wenn man die Formel nachträglich anpassen möchte, ist dies auch nur in der jeweils ersten Zelle möglich. Alle anderen Zellen im Ausgabebereich sind gesperrt und die Formel wird dort leicht ausgegraut in der Bearbeitungszeile dargestellt:

Alle anderen Zellen sind unveränderbar

Alle anderen Zellen sind unveränderbar

Wichtig zu wissen: Wenn nicht genügend leere Zellen für die komplette Ausgabe zur Verfügung stehen, erscheint stattdessen ein #ÜBERLAUF!-Fehler:

#ÜBERLAUF!-Fehler bei Platzmangel

#ÜBERLAUF!-Fehler bei Platzmangel

Sobald das Hindernis jedoch beseitigt wurde, breitet sich die Formel automatisch wieder aus. Sehr genial!

  • Eine einzige, unfassbar simple Funktion, eingegeben in einer einzigen Zelle!
  • Kein verschachteltes Formelmonster!
  • Keine komplizierte Erfassung über Strg+Umschalt+Eingabe!
  • Dynamische Anpassung des Ausgabebereichs

Zum Vergleich:
Um die gleiche Aufgabe mit herkömmlichen Mitteln per Formel zu lösen, ist dieses Konstrukt notwendig:
{=WENNFEHLER(INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1)));"")}

Lösung mit bisherigen Mitteln

Lösung mit bisherigen Mitteln

Einschließlich der damit verbundenen Nachteile:

  • Schwer nachvollziehbares Monster
  • Erfassung als Array-Formel mit Strg+Umschalt+Eingabe
  • Zusätzliche SVERWEIS-Funktion für die Ausgabe der dazugehörigen Einwohnerzahlen
  • Kopieren der Formel nach unten „auf Verdacht“ in so viele Zeilen, wie vermutlich maximal benötigt werden

Noch eine Schippe obendrauf

Der aufmerksame Leser wird bemerkt haben, dass meine Ausgangsliste nach Einwohnerzahl absteigend sortiert war. Dementsprechend wird auch die Ergebnisliste in der gleichen Weise sortiert ausgegeben.

Was jedoch, wenn du lieber eine alphabetische Sortierung nach Städtenamen hättest? Kein Problem, dafür gibt es die (ebenfalls neue) SORTIEREN-Funktion.

=SORTIEREN(Matrix;Sortierindex;Sortierreihenfolge;Nach_Spalte)
„Matrix“ ist der zu sortierende Tabellenbereich.
„Sortierindex“ ist eine Zahl, die die Zeilen- oder Spaltennummer angibt, nach der sortiert werden soll.
Im Parameter „Sortierreihenfolge“ steht 1 (oder keine Angabe) für aufsteigende, -1 für absteigende Reihenfolge.
Für den letzten, ebenfalls optionalen Parameter „Nach_Spalte“ gibt man FALSCH an, um nach Zeilen zu sortieren oder WAHR, wenn horizontal nach Spalten sortiert werden soll.

In meinem Beispiel mache ich es mir aber ganz einfach und verwende nur den ersten Parameter mit der Matrix, was ja wiederum die FILTER-Funktion ist:
=SORTIEREN(FILTER(B2:C309;A2:A309=$F$1))

Die Kombination von FILTER mit SORTIEREN

Die Kombination von FILTER mit SORTIEREN

Einfacher geht es wohl kaum, oder?

Fazit

Natürlich kann ich den Aufschrei vieler Leser schon hören und die zu erwartende Kritik ist berechtigt:
Ohne Office 365 sieht es schlecht aus. Abwärtskompatibilität zu anderen Excel-Versionen ist nicht gegeben und Probleme beim Datenaustausch sind damit vorprogrammiert.

Aber es gibt ja auch verschiedene Alternativen:

Die Entscheidung liegt bei dir!

 

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 zu Andi Antworten abbrechen

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

18 Gedanken zu “Eine Funktion liefert alle Treffer

  • Andi

    Hallo Martin,
    das ist eine weitere nette Funktion, die praktische Möglichkeiten eröffnet. Wie du schon schreibst, ist der eingeschlagene Weg von MS aber bedenklich aus Sicht der Anwender. Eine Fragementierung auf verschiedene Versionen bringt nur Ärger für alle, die Excel ein wenig mehr nutzen als für =Summe() und Autofilter. Ich warte schon drauf, wie es mit VBA weitergeht. Offline dürfte alles beim Alten sein, aber mit Excel Online fängt der Spass schon an. Ab und an dürften manche Entwickler (bzw diejenigen, die den Fahrplan für die Entwickler entwerfen) vergessen, dass es auch User gibt, die auf gewachsene Dinge angewiesen sind und die nicht ständig gut funktionierende Abläufe neu entwickeln können.

    • Martin Weiß Autor des Beitrags

      Hallo Andi,

      ich gebe dir schon Recht, dass solche neuen Funktionen immer ein zweischneidiges Schwert sind, gerade am Anfang, wenn sie neu eingeführt werden. Trotzdem finde ich es wichtig, dass man sie zumindest kennenlernt. Aber im Einzelfall muss natürlich jeder selbst entscheiden, ob man sie nutzen kann/darf/möchte oder eben nicht.

      Schöne Grüße,
      Martin

  • Bruno Schlecht

    Hallo Martin,
    diese Filter-Funktion ist der Hammer und deiner Erklärung dazu auch, danke dafür.
    Wie sooft in solchen Situationen kommen bei gravierenden Neuerungen gleich neue Wünsche auf. Meine Tabelle umfasst > 3000 Einträge und die möchte ich nach 3 Kriterien filtern, also bräuchte ich die Funktion FILTERS mit mehr als einem Kriterium ähnlich der SummewennS-Funktion. Ist so etwas in der näheren Zukunft zu erwarten oder wie kann man diese Problem elegant lösen?
    Beste Grüße
    Bruno

    • Martin Weiß Autor des Beitrags

      Hallo Bruno,

      da habe ich gute Nachrichten für dich, denn Filtern mit mehreren Kriterien ist jetzt schon möglich! Du musst nur die Kriterien mit + (entspricht einer Oder-Verknüpfung) oder * (entspricht einer Und-Verknüpfung) eingeben. Bezogen auf die Städteliste aus dem Artikel sieht das wie folgt aus.

      Alle Städte aus Brasilien oder aus Deutschland:
      =FILTER(B2:C309;(A2:A309=“Brasilien“)+(A2:A309=“Deutschland“))

      Alle Städte aus Brasilien mit mehr als 2.000.000 Einwohnern:
      =FILTER(B2:C309;(A2:A309=$F$1)*(C2:C309>2000000))

      Schöne Grüße,
      Martin

    • Bruno Schlecht

      Hallo Martin,
      Sorry, man sollte erst mal alles genau lesen und testen bevor man was sagt, oder schreibt.
      Die FILTER-Funktion kann natürlich mehrere Kriterien gleichzeitig bearbeiten:
      =FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);““). Dazu müssen die einzelnen Filter in Klammer gesetzt und mit „*“ verkettet werden.
      Ich brauche also kein „FILTERS“. Damit erfüllt die Funktion alles was ich brauche und löst mein Problem auf elegante Weise: Cool.
      Beste Grüße
      Bruno

  • Martin

    Vielen Herzlich Dank
    für die Beschreibung der Filter Funktion das hat mir wahnsinnig weitergeholfen bei meinen Office365 Excel geht es zwar nicht aber in der Anwendung für iche es gesucht habe Google Tabellen funktioniert es super

    • Martin Weiß Autor des Beitrags

      Hallo Martin,

      es irritiert mich zwar jetzt, dass es bei Dir mit Office 365 nicht funktioniert. Denn gerade dort ist diese Funktion ja überhaupt erst verfügbar. Aber wenn es mit Google Tabellen auch klappt, soll es mir Recht sein 😉

      Schöne Grüße,
      Martin

  • Denise Burmann

    Lieber Martin
    Du hast hier eine wirklich informative und hilfreiche Seite aufgebaut. Ich komme immer mehr auf den Geschmack von Excel. Nur beisse ich mir gerade die Zähne aus.
    Ich bin ziemlich sicher, dass du eine gute Idee aus dem Hut zaubern kannst.
    In Tabelle2 sind folgende Daten:
    Spalte A Spalte B
    1.1 Text1.1
    1.2 Text1.2
    2.1 Text2.1
    2.2 Text2.2
    In Tabelle1 habe ich in Spalte B5:B70 eine Liste mit den Nummern der Spalte A der Tabelle2 erstellt. Wenn der Nutzer nun in Tabelle1 die Nummer 1.1 auswählt, soll auf der derselben Zeile in Spalte D der Text1.1 automatisch eingefügt werden. Auch sollten die Nutzer in der Tabelle1 bei Bedarf Zeilen hinzufügen können ohne dass die Formel „zerstört“ wird. Hast du eine Idee? Vielen Dank und
    herzlicher Gruss aus der Schweiz
    Denise

    • Martin Weiß Autor des Beitrags

      Hallo Denise,

      ich glaube, ich weiß was du meinst. Aber diese Variante ist zum Scheitern verurteilt und ich würde dringend von einer solchen zeilenabhängigen Lösung abraten. Denn nur eine unbedacht gelöschte Zeile lässt alles zusammenfallen wie ein Kartenhaus. Verwende stattdessen besser einen SVERWEIS oder etwas in der Art, das ist bedeutend zuverlässiger.

      Schöne Grüße,
      Martin

  • Volker Kestler

    Hallo
    Martin finde deine Internetseite super
    Habe einfache Frage hoffe ich 🙂
    habe zwei Spalten Wechselweise stehen dort größere und kleinere Wert –> Auswahl immer den kleineren Wert nehmen von Spalten A und Spalte B als Ergebnis in Spalte C
    Beispiel
    Spalte A / Spalte B / Spalte C –> Ergebnis
    3,3 4 –> 3,3
    6 5,2 –> 5,2
    Es wäre nett wenn es hierfür ein Lösung gibt. Vielleicht Denke ich zu kompliziert

      • Sue

        Hallo Martin,
        mein erster Gedanke war: da nimmt man ganz simpel
        C1 = MIN(A1;B1).
        Bei Deiner Lösung komme ich jetzt echt ins Grübeln… Wer von uns hat die Frage nicht verstanden?;-)

        Bei der Variante mit WENN müsste im ersten Parameter der Vergleichsoperator umgedreht werden, alternativ könnte man die then- und else-Parameter vertauschen:
        However: Es führen immner viele Wege nach Rom und manchmal ist der gerade Weg stolperfreier.

        Gruß
        Sue

        • Martin Weiß Autor des Beitrags

          Hallo Sue,

          du hast natürlich vollkommen Recht: die Variante mit der MIN-Funktion ist wirklich einfacher und direkter, genau dafür ist sie ja eigentlich da 🙂
          Manchmal sehe ich den Wald auch vor lauter Bäumen nicht…

          Schöne Grüße,
          Martin

  • Arno

    Hallo Martin,
    hierzu eine Frage:
    das Land „Brasilien“ muss genau richtig geschrieben werden.
    Aber kann ich die Formel auch irgendwie verändern, damit ich Platzhalterzeichen verwenden kann?
    also das ich nur noch bei Land: „Bras*“ einzugeben brauche ?

    • Martin Weiß Autor des Beitrags

      Hallo Arno,

      eine sehr interessante Frage. Standardmäßig kann die FILTER-Funktion so etwas nicht, aber in Kombination mit ein paar anderen geht es dann doch:
      =FILTER(B2:C309;ISTZAHL(SUCHEN(F1;A2:A309)))

      Vielleicht schreibe ich da nochmal einen Artikel dazu…

      Schöne Grüße,
      Martin

  • Michele Jerabek

    Hallo lieber Martin,
    ich schließe mich den vielen vielen Positiven Kommentaren bezüglich dieses Themas mehr als nur gerne an.
    Du erklärst wirklich toll, auch wenn ich nicht alles sofort verstehe – teils benötige ich noch etwas Zeit.
    Wie dem auch sei möchte ich Dir meinen absoluten Dank aussprechen für Deine tolle Seite.
    Ganz herzliche Grüße
    Michele

    • Martin Weiß Autor des Beitrags

      Hallo Michele,

      Ihr seid alle so nett zu mir 🙂
      Vielen Dank also für Deinen lieben Kommentar und es freut mich immer zu hören, wenn ich mit meinen Artikeln weiterhelfen kann.

      Schöne Grüße,
      Martin