Besser als SVERWEIS: Alle Werte finden 13

Was der SVERWEIS nicht kann: Alle Treffer zu einem Suchkriterium liefern. So geht's trotzdem.
 

Der SVERWEIS ist mit Sicherheit eine der wichtigsten Funktionen, die dem versierten Excel-Anwender zur Verfügung stehen und sie gehört natürlich auch zu meinen Favoriten.

Die SVERWEIS-Funktion liefert bekanntermaßen zu einem Suchkriterium in der einen Tabelle den ersten gefundenen Treffer aus einer anderen Tabelle zurück. Was macht man aber, wenn der gesuchte Wert mehrmals vorkommt und man gerne alle Ergebnisse angezeigt bekommen möchte?

Hier ist der SVERWEIS leider hoffnungslos überfordert. Wie man so etwas trotzdem hinbekommt, zeigt der heutige Artikel.

Die Ausgangslage

Ich habe wieder eine lange Tabelle mit Millionenstädten vorbereitet. Jeder Datensatz besteht aus den Feldern Land, Stadt und Einwohneranzahl:

Beispiel: Liste der Millionenstädte

Beispiel: Liste der Millionenstädte


(Die Beispieldatei kannst du dir gerne hier herunterladen)

Nun möchte ich mir für das Land, welches ich in Zelle F1 eingebe, die größte Millionenstadt anzeigen lassen. Da die Städteliste noch Einwohnerzahl absteigend sortiert ist, ist das kein Problem für den SVERWEIS:

SVERWEIS liefert nur ein Ergebnis

SVERWEIS liefert nur ein Ergebnis

Aber du weißt ja schon, was kommt: Es gibt in vielen Ländern natürlich mehrere Millionenstädte und die möchte ich alle gerne aufgelistet bekommen. Für Brasilien soll das Ganze also etwa so aussehen:

Ziel: Alle Städte zu einem Land

Ziel: Alle Städte zu einem Land

Die gute Nachricht: Auch hierfür gibt es eine Lösung, nämlich die INDEX-Funktion

Die nicht ganz so gute Nachricht: INDEX alleine reicht leider nicht aus. Es müssen mehrere Funktionen kombiniert und obendrein in eine Array-Formel gepackt werden. Und um das Endergebnis schon vorwegzunehmen, zeige ich hier die fertige Formel:
{=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)))}

So, das war’s schon.

Wenn du also faul bist, dann kannst du hier aufhören zu lesen. Kopiere dir einfach diese Formel in deine Arbeitsmappe und vergiss dabei nicht, dass es sich um eine Array-Formel handelt. Das heißt, die geschweiften Klammern dürfen nicht von Hand eingegeben werden. Stattdessen muss die Eingabe mit der Tastenkombination STRG+Umschalt+Eingabe abgeschlossen werden.

Wenn du jedoch ein wenig neugierig bist (und davon gehe ich ehrlich gesagt aus), dann liest du weiter und lernst, was sich hinter diesem komischen Konstrukt verbirgt.

Die Array-Formel wird aufgedröselt

Fangen wir also beim Endergebnis an und bewegen uns Schritt für Schritt durch die einzelnen Elemente der Formel. Im Grunde handelt es sich also um eine spezielle Variante der INDEX-Funktion:
=INDEX(Bezug; Zeile)

Sie gibt als Ergebnis den Wert zurück, der sich in der angegebenen Zeile im Bereich des angegebenen Bezugs befindet. In unserem Beispiel wäre der Bezug also die Liste mit den Städtenamen. Die Schwierigkeit liegt nun darin, die richtige Zeile zu finden. Genauer gesagt, die richtigen Zeilen, denn wir wollen ja alle relevanten Städte finden. Und genau das ist der Grund, warum die INDEX-Funktion hier in einer Array-Formel zum Einsatz kommt:

INDEX-Funktion in der Array-Variante

INDEX-Funktion in der Array-Variante

Nehmen wir uns also den Teil der INDEX-Funktion vor, der die Zeilen bestimmen soll. Dabei handelt es sich um die KGRÖSSTE-Funktion:
=KGRÖSSTE(Matrix; k)
Sie liefert den k-größten Wert einer Matrix oder Liste. Also den 2.-größten, 3.-größten usw.

In unserem Fall sieht die Funktion so aus:
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))

Für das erste Argument der Funktion können wir jedoch keinen festen Bezug angeben, denn uns interessieren ja von der ursprünglichen Ausgangsliste nur die Städte, die in dem Land liegen, welches wir in Zelle F1 eingegeben haben. Das macht dieser Teil:
($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1)

Der relevante Bezug für KGRÖSSTE

Um besser zu verstehen, was diese Formel genau macht, schreiben wir die beiden Elemente in eigene Zellen in den Spalten E und F:

Prüfung: Stimmt das Land überein?

Prüfung: Stimmt das Land überein?

Wichtig: Wenn du das Beispiel selbst nachvollziehen möchtest, musst du dazu erst den Bereich von E2 bis E309 markieren, dann die Formel eingeben und mit STRG+Umschalt+Eingabe abschließen. Damit wird die Formel in alle Zellen als Array-Formel übernommen. Das ist wirklich wichtig, ansonsten funktioniert es nicht!

Damit wird für jedes einzelne Land in Spalte A geprüft, ob es mit dem in Zelle F1 eingegebenen Land übereinstimmt (= WAHR) oder eben nicht (= FALSCH).

In Spalte F verfahren wir mit dem zweiten Teil der Formel genauso:

Bestimme die Zeilennummer

Bestimme die Zeilennummer

Hier passiert nichts anderes, als dass wir die Position eines jeden einzelnen Landes in der Liste bestimmen. Da in meiner Tabelle in Zeile 1 die Spaltenüberschrift steht, muss bei der ZEILE-Funktion der Wert 1 abgezogen werden. Steht in deiner Tabelle die Überschrift in Zeile 5, ziehst du hier den Wert 5 ab.

In Spalte G werden die beiden Ergebnisse aus E und F noch miteinander multipliziert:

Nur die relevanten Zeilen

Nur die relevanten Zeilen

Da “FALSCH” dem Wert 0 (Null) und “WAHR” dem Wert 1 entspricht, haben nur die Zeilen mit dem von uns gesuchten Land einen Wert ungleich Null.

Und somit haben wir eine Liste aller relevanten Datensätze als Bezug für unsere KGRÖSSTE-Funktion.

Der k-Wert in KGRÖSSTE

Kommen wir jetzt zum zweiten Argument der KGRÖSSTE-Funktion. Diesen bestimmen wir über die ZÄHLENWENN-Funktion, welche ich zur besseren Veranschaulichung in Spalte H eingetragen habe:

ZÄHLENWENN liefert die Anzahl

ZÄHLENWENN liefert die Anzahl

Dieser Teil ist vielleicht nicht ganz so einfach nachvollziehbar. Die ZÄHLENWENN-Funktion ermittelt die Anzahl der Datensätze in Spalte A, die dem Land in F1 entspricht. Für Brasilien gibt es also insgesamt 13 Einträge. Da wir jeden einzelnen auflisten wollen, brauchen wir für die KGRÖSSTE-Funktion auch alle Werte von 1 bis 13.

Das ist der Grund, warum wir an die ZÄHLENWENN-Funktion noch eine kleine Berechnung anhängen:
=ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1)

Der addierte Wert 1 steht wieder für die Spaltenüberschrift, die sich in Zeile 1 befindet. Dann ziehen wir noch die aktuelle Zeilennummer ab und erhalten somit für jede einzelne Zeile einen niedrigeren Wert.

In Spalte I habe ich an die KGRÖSSTE-Funktion die ermittelten Ergebnisse aus den Spalten G und H übergeben, so dass man sieht, was hier eigentlich passiert:

KGRÖSSTE liefert die jeweilige Position

KGRÖSSTE liefert die jeweilige Position

Ich bekomme eine aufsteigende Liste aller relevanten Zeilennummern. Die Liste enthält genau 13 gültige Einträge, danach wird der Fehler #ZAHL! geliefert, das KGRÖSSTE mit Null bzw. negativen Werten für k nichts anfangen kann.

Und genau diese Liste mit den Zeilennummern wird in der zu Beginn gezeigten INDEX-Funktion verwendet, um die gewünschten Städte zu finden:

INDEX liefert die Stadt zur Position

INDEX liefert die Stadt zur Position

Die Formel kopiert man einfach so weit nach unten, bis es wieder zu einem #ZAHL!-Fehler kommt. Die Einwohnerzahl in Spalte F lässt sich nun wieder mit einer einfachen SVERWEIS-Funktion ermitteln.

Kosmetik

Da die vielen #ZAHL!-Fehler nicht gerade schön aussehen, umschließen wir die INDEX-Funktion noch mit der WENNFEHLER-Funktion, so dass im Fehlerfall einfach eine leere Zelle angezeigt wird:

Schönheitskorrektur mit WENNFEHLER

Schönheitskorrektur mit WENNFEHLER

Auch zum Schluß nochmal der Hinweis: Die Eingabe muss mit STRG+Umschalt+Eingabe abgeschlossen werden!

Das war’s! Zugegeben, nicht ganz trivial. Aber schließlich bist du ja hier, um etwas zu lernen 🙂

 

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.



Kommentar erstellen

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

13 Gedanken zu “Besser als SVERWEIS: Alle Werte finden

  • Andi

    Hallo Martin,
    durchaus interessant und vor kurzem hätte ich das sogar benötigt – gelöst hab ichs dann mit einer Pivottabelle. Gibts irgendeinen Vorteil der Formellösung gegenüber Pivottabellen? Auf die schnelle fällt mir nichts ein.

    • Martin Weiß Beitragsautor

      Hallo Andi,

      Pivot ist natürlich auch eine sehr gute Lösung. Im Grunde ist es nur eine Geschmacksache. Eine Formellösung würde ich persönlich dann bevorzugen, wenn man seine Ergebnisse z.B. innerhalb eines fest definierten Formats oder Rahmens liefern soll. Also z.B. innerhalb eines Dashboards. Aber möglich ist natürlich beides. Danke für den Tipp.

      Schöne Grüße,
      Martin

  • Ron

    Hallo,
    …für weiter Verwendungszwecke ist das natürlich gut. Ansonsten Pivot, obwohl man da ja auch auslesen kann.
    Sinn macht es dennoch. Bspw. Welche Einzelteile gehören zu Artikelnummer XY?, dann kann man dann dies wiederum mit weiteren Formeln außerhalb einer Pivot sehr gut kombinieren.
    Grundvoraussetzung ist hier die (Absteigende) Sortierung, richtig?

    • Martin Weiß Beitragsautor

      Hallo Ron,

      die Sortierung der Ausgangsliste spielt für diese Lösung nur eine untergeordnete Rolle. Sie ist eigentlich nur dann notwendig, wenn die Ergebnisse auch sortiert sein sollen. Die Städte in meinem Beispiel werden auch gefunden, wenn die Ausgangsliste unsortiert ist.

      Schöne Grüße,
      Martin

  • Steffi

    Hallo Martin,

    da ich mit Pivot-Tabellen ungeübt bin und ist diese Formel echt eine Tolle Angelegenheit.
    Ganz lieben Dank mach weiter so Steffi

    • Martin Weiß Beitragsautor

      Hallo Christian,

      es gibt in Excel so gut wie immer mehrere Lösungen. Datenbank-Funktionen sind auf jeden Fall auch ein guter Ansatz.

      Schöne Grüße,
      Martin

  • Caro

    Hallo Martin,
    vielen Dank schon mal für die super Erklärung!

    Wie muss ich die Formel denn aufbauen, wenn die Ergebnisse nicht untereinander, sondern nebeneinander (in Spalten statt Zeilen) aufgelistet werden sollen?

    Danke & Gruß,
    Caro

    • Martin Weiß Beitragsautor

      Hallo Caro,

      Du musst im letzten Teil der Formel die Funktion ZEILE durch SPALTE ersetzen, also:
      {=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-SPALTE(A1)))}

      Das war’s.

      Grüße,
      Martin

  • Mario

    Hallo Martin,
    kenne mich leider nur sehr limitiert mit excel aus
    aber vielen Dank! Sehr schöne Erklärungen der einzelnen schritte. Damit hab konnte ich das ganze nach kurzem Kopfschütteln auch verstehen 😛

    Aber:

    ich hatte vor das ganze für die arbeit zu verwenden und stoße dabei auf einige Probleme.
    ich möchte eine Materialliste mit Materialcodes, Namen, Alternativnamen und Lieferanten mit einer Suchfunktion ausstatten, welche mir dann alle gesuchten teile der liste (also im Prinzip die ganze Zeile) ausspuckt bzw wenn die suche nicht eindeutig ist mir alternativen anzeigt.

    eines der Probleme ist, dass die suche nur funktioniert, wenn ich das Suchkriterium genau eingebe.was aber bei teilweise wirren Namen (Chemiekonzern – hahaha) nicht immer möglich ist. mit den Mat.codes funktioniert das aber diese möchte ich mir oft heraussuchen.

    weiters kann ich so leider auch nicht entweder/oder suchen sondern bin auf z.B. mat.codes beschränkt.

    fällt dir vielleicht eine Lösung dafür ein?

    Danke und Gruß,

    Mario