Eine Funktion liefert alle Treffer 2

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. Grund genug also, einen geradezu unfassbar einfachen Lösungsweg mit aktuellen Bordmitteln vorzustellen.

Worin genau das genannte Problem besteht und wie die neue Lösung aussieht, erfährst du im heutigen 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 weiter auf meine klassische Variante 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.

Trotzdem sollst du wissen, dass es so etwas gibt und was man damit machen kann. Und wenn es in deiner individuellen Arbeitsumgebung passt und du keine Dateien mit Anwendern anderer Excel-Versionen austauschen musst, dann sollte man solche Mittel auch nutzen und sich nicht das Leben unnötig schwer machen.

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

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

2 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