Besser als SVERWEIS: Alle Werte finden 57

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.



57 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ß Autor des Beitrags

      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ß Autor des Beitrags

      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ß Autor des Beitrags

      Hallo Steffi,

      gern geschehen 🙂

      Schöne Grüße,
      Martin

  • Christian Fette

    Gibt es für so etwas nicht die Datenbank-Funktionen bzw. den Spezialfilter?
    Die kommen ohne Array aus…

    • Martin Weiß Autor des Beitrags

      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

  • Thomas

    Ist auch für einen Artikelkatalog sergut wenn mann das als suchfunktion benutzt erscheinen alle änlichen Artikel super

  • 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ß Autor des Beitrags

      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

      • Max

        Hallo Martin,

        ich habe ebenfalls Zeile durch Spalte ersetzt und jetzt funktioniert es auch bei mir. Allerdings habe ich ein Problem, das ich momentan nicht lösen kann. Und so ganz verstehe ich die Formel selbst mit Erklärung nicht ;-).

        Und zwar wird mir immer ein Ergebnis weniger angezeigt als verfügbar (bei mir sind es gekaufte Artikel von einer E-Mail). Ich habe also eine E-Mail-Adresse mit 5 Produktkäufen, erhalte als Ergebnis zu dieser E-Mail-Adresse nur 4 Produkte.

        Vielleicht kannst du nochmal erklären, warum man dieses “+1-SPALTE” eintragen muss. Bei mir hat er dann immer den falschen Wert gezogen (Produkt aus darüberligender Zeile). Erst mit “+0-SPALTE” hat es funktioniert. Vielleicht habe ich dadurch aber auch die Formel zerstört.

        So sieht meine Formel aus:

        =INDEX(Basis!$P:$P;KGRÖSSTE((Basis!$O:$O=$A2)*(ZEILE(Basis!$O:$O)-1);ZÄHLENWENN(Basis!$O:$O;$A2)+0-SPALTE(A1)))

        VG
        Max

        • Martin Weiß Autor des Beitrags

          Hallo Max,

          das -1/+1 hängt nur damit zusammen, dass in meinem Beispiel in der ersten Zeile die Überschriften stehen und die Daten erst ab Zeile 2 beginnen. Wenn bei Dir die Überschriften in Zeile 2 stehen, musst Du in der ZEILE-Funktion -2 rechnen und in der ZÄHLENWENN-Funktion +2. Wichtig ist nur, dass in beiden Funktionen der gleiche Wert addiert bzw. subtrahiert wird.

          Grüße,
          Martin

          • Max

            Hallo Martin, danke für die schnelle Antwort. Ich habe auch Überschriften in Zeile 1. Dennoch funktioniert die Formel bei mir nur wenn ich statt -1/+1 -0/+0 eintrage.

            Am Ende gibt er mir aber trotzdem immer einen Treffer zu wenig zurück, als im Datensatz vorhanden. Und zwar immer den letzten. Vielleicht liegt es auch am Aufbau meiner Tabelle, aber ich bin etwas ratlos wo der Fehler liegt.

            VG
            Max

          • Max

            ich habe jetzt mal meine daten in deine tabelle kopiert und dort funktioniert alles. Problem also gelöst, verstanden allerdings nicht 😉

            Danke Dennoch!

  • 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

  • Martin

    Salut Martin
    ich konnte dank deiner Seite ein Problem lösen und bin dabei mich mit der Formel auseinanderzusetzen. Ich hab offenbar die Funktionsweise noch nicht durchschaut, denn durch try&error habe ich mit verschiedenen Formeln das gleiche Resultat erhalten. Siehst du wieso beide das gleiche Ergebnis liefern?

    {=INDEX(A:A;KGRÖSSTE(($J$3:$J$369=$AD$4)*(ZEILE($J$3:$J$369));ZÄHLENWENN($J$3:$J$369;$AD$4)+1-ZEILE(J1)))}

    {=INDEX($A$3:$A$369;KGRÖSSTE(($J$3:$J$369=$AD$4)*(ZEILE($J$3:$J$369))-2;ZÄHLENWENN($J$3:$J$369;$AD$4)+1-ZEILE(J1)))}

    Frohe Festtage!
    Grüsse aus der Schweiz
    Martin

    • Martin Weiß Autor des Beitrags

      Servus Martin,

      die Formeln sind ja fast identisch. Bei der ersten Version verwendest Du die INDEX-Funktion für die komplette Spalte A (A:A). Bei der zweiten Version verwendest Du hingegen den Bereich A3:A369, also erst ab Zeile 3, dafür wird bei der ZEILE-Funktion der Wert 2 abgezogen. Somit kommst Du wieder auf das gleiche Ergebnis.

      Schöne Grüße und einen guten Rutsch,
      Martin

  • Alexandra

    Hallo Martin,

    vielen Dank, diese Formel ist endlich die Lösung auf meine Monatelange Suche!
    Ich bin sehr glücklich!
    Eine Frage hätte ich noch. Mein Suchkriterium ist ein Datum, es kann aber in zwölf Spalten (Monaten) vorkommen, das Ergebnis zieht der Formel immer aus Spalte B (Projektnummer).
    Soll ich in diesem Fall diese Formel 12 mal nacheinander verknüpfen, oder gibt es eine elegantere Lösung?
    Kann ich irgendwie angeben, dass die Suche erstmal in Spalte C, dann in Spalte F (in jede dritte Spalte, 12 mal) durchgeführt wird?

    Herzlichen Dank für deine Hilfe!
    Viele Grüße
    Alexa

    • Martin Weiß Autor des Beitrags

      Hallo Alexandra,

      wenn ich es richtig verstehe, kommt das Datum zwar nur einmal vor, aber es kann in einer von 12 Spalten stehen. Dann würde eine zusätzliche 13. Spalte anlegen, in der das Datum aus den 12 Spalten geholt wird und dieses Feld dann als Kriterium verwenden. Wenn tatsächlich nur ein Datum innerhalb der 12 Spalten vorhanden ist, könntest Du das in der 13. Spalte z.B. über die MAX-Funktion abgreifen.

      Schöne Grüße,
      Martin

  • Alexander

    Hallo Martin,

    vielen Dank für deine tolle Hilfe.
    Ich möchte die Formel so umbauen, dass ich mehrere Suchworte in einer Liste von Namen finde. Geht das ohne weiteres?
    In Gedanken bau ich ebenfalls ein Array aus Suchworte die ich nacheinander abarbeite, allerdings bekomme ich das in Excel nicht wirklich aufs Papier.

    Danke schon im voraus für deine Hilfe.

    Viele Grüße
    Alex

    • Martin Weiß Autor des Beitrags

      Hallo Alexander,

      mir ist keine Formellösung bekannt, mit der so etwas funktionieren würde. Ich fürchte, da musst Du auf VBA zurückgreifen.

      Schöne Grüße,
      Martin

  • Dietmar

    Hallo Martin,

    immer wieder faszinierend, was Leute aus einem solchen Programm herausholen können.
    Ich versuche mich seit geraumer Zeit an einer Lösung für ein Bestellformular für vorhandene Kunden, die ihre Kundennummer natürlich oft nicht wissen. Eine Suche nach der Postleitzahl wäre das einfachste, eine klare Identifikation habe ich aber nur in der Kundennummer.
    Es gibt eine Tabelle mit KD-Nr, Name, Straße, PLZ, Ort…. auf die jeder zugreifen kann.
    Jeder hat ein Formular auf seinem Rechner, der mit SVerweis über die Kundennummer die Daten “holt”.
    Wie kann ich über die PLZ (mehrere Treffer möglich) den passenden Kunden finden, die Kundennummer erkennen und verwenden in einem Formular auf meinem Rechner?
    Vielenb Dank für bisherige Tips und hoffentlich zukünftige 🙂

    • Martin Weiß Autor des Beitrags

      Hallo Dietmar,

      im Grunde genommen könntest Du so vorgehen, wie oben im Artikel beschrieben. Über die Array-Funktion könntest Du im ersten Schritt nach der PLZ suchen und alle Treffer anzeigen lassen. Im zweiten Schritt könnte diese Trefferliste dann die Basis für die Auswahl der Kundennummer sein (z.B. per Dropdown-Feld).

      Schöne Grüße,
      Martin

  • Michael

    Hallo Martin, super gelöst und sehr gut erklärt. ich bin selbst ein absoluter Excelfreak (laut Aussagen meiner Kollegen) und sowas wie eine Allroundlösung bei uns im Unternehmen geworden. Nun stoße ich aber auf meine Grenzen, bzw. komme gedanklich nicht weiter (wahrscheinlich hänge ich immer wieder am gleichen Fehler). Deine hier aufgezeigt Formel war im Ansatz genau das was ich brauchte… nun wollen meine lieben Kollegen das Spiel aber weiter drehen…
    Wir haben eine Liste die sehr gut funktioniert mit dieser Formel und einem Suchwert. nun hätten meine Kollegen aber auch gerne die Möglichkeit das ganze zu “verfeinern” also sprich einen zweiten Suchwert mit einzubauen… alle von mir probierten Lösungen gehen bisher schief, vllt. kannst Du mir kurz helfen?
    Danke schon einmal

    • Martin Weiß Autor des Beitrags

      Hallo Michael,

      jetzt werden Deine Kollegen aber langsam ziemlich fordernd 🙂
      Bleiben wir beim Beispiel oben. Ich füge in der Liste eine neue Spalte zwischen Land und Stadt, nennen wir sie Kategorie. Jede Stadt wird einer Kategorie A, B, C zugeordnet. Zusätzlich zum Land soll jetzt nach der Kategorie gefiltert werden, die ich in Zelle G2 auswähle. Die neue Formel lautet dann:
      {=WENNFEHLER(INDEX($C$2:$C$309;KGRÖSSTE(($A$2:$A$309=$G$1)*($B$2:$B$309=$G$2)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENNS($A$2:$A$309;$G$1;$B$2:$B$309;$G$2)+1-ZEILE(A1)));””)}

      Beachte: Statt ZÄHLENWENN verwende ich jetzt ZÄHLENWENNS, um mehrere Kriterien zu berücksichtigen.

      Schöne Grüße,
      Martin

  • Herbst Daniel

    Hallo Martin,

    leider sitzt hier auch das Problem vor dem Computer und kommt nicht weiter.
    Ich habe 3 Spalten (Status, Stadt, Alter) und möchte, dass mir als Ergebnis die Anzahl angezeigt wird
    die ich zuvor in einem Dropdown auswähle.
    Bsp. Aktiv, München+Ingolstadt = 123 gleiche Zeilen

    Mein Ansatz war, wobei hier ohne Dropdown
    {=SVERWEIS(C1&C2;WAHL({1.2}F:F&U:U);2;0)}

    C1&C2 sind die Auswahlfelder, F und U die Spalten die untersucht werden sollen, wie kann ich es
    ermöglichen ,dass wenn in Spalte F und U in der gleichen Zeile vorkommt der Zähler um 1 erhöht
    wird und am Ende die ganze Zahl zutreffender Zeilen angezeigt wird.

    • Martin Weiß Autor des Beitrags

      Hallo Daniel,

      ich stehe momentan noch ein wenig auf dem Schlauch, was den Aufbau Deiner Tabelle angeht.
      Was genau steht in den Spalten C, F und U. In C steht der Status? In F die Stadt und in U das Alter oder auch eine Stadt?
      Und was soll in Spalte F und U in der gleichen Zeile vorkommen?

      Ich bin verwirrt…

      Schöne Grüße,
      Martin

  • Matthias K

    Hallo Martin,
    Deine Formel ist Spitze! Sie hat mir sehr weitergeholfen. Jetzt habe ich aber ein anderes Problemchen. meine Formel sieht wie folgt aus:
    =WENNFEHLER(INDEX(“”&StueliPos_mit_VK_Artikeln!$D$2:$D$154;KGRÖSSTE((“”&StueliPos_mit_VK_Artikeln!$A$2:$A$154=$A$2)*(ZEILE(StueliPos_mit_VK_Artikeln!$A$2:$A$154)-1);ZÄHLENWENN(StueliPos_mit_VK_Artikeln!$A$2:$A$154;$A$2)+1-ZEILE(StueliPos_mit_VK_Artikeln!A1)));””)

    Sie funktioniert wie sie soll. Nun möchte ich das Ergebnis dieser Formel als SVERWEIS benutzen, um auf andere Daten zurückzugreifen. Das Ergebnis der Formel ist, je nach Suchkriterium “0173-6”. Die Formel die ich aufgestellt habe sieht wie folgt aus: =SVERWEIS(“”&A3;StueliPos_mit_VK_Artikeln!A2:F156;2;0)
    Der SVERWEIS erkennt scheinbar das Ergebnis des Index-Arrays nicht, bisher konnte ich Formatierungen der Zellen mit “”& unberücksichtigt lassen. über einen Vorschlag wäre ich sehr Dankbar 🙂

    Die Matrix ist eine SQL-Abfrage, welche ich in VB geschrieben habe, funktioniert auch einwandfrei.

    • Martin Weiß Autor des Beitrags

      Hallo Matthias,

      auf Anhieb kann ich zumindest in den Formeln keinen Fehler entdecken. Allerdings kenne ich den Aufbau Deiner Tabellen und die Inhalte nicht, daher kann ich nur dort den Fehler vermuten.

      Schöne Grüße,
      Martin

      • Matthias K

        Hallo Martin,
        ich habs gelöst bekommen. Ich habe in der Matrix einfach nur die gewünschte Spalte eingegeben und mir so die Daten herausgezogen, wie ich sie brauche. Sprich auf den SVERWEIS kann ich komplett verzichten.

        Trotzdem vielen Dank, denn mit dem Index Array kann ich super Arbeiten !!

  • Chris

    Hätte hierzu eine Frage:

    Kann ich diese Formel auch so einbauen, dass ich sie in den Bezug einer weiteren VERWEIS Funktion verschachtele?

    Sprich: In Zeile 1 stehen Werte, manche eben mehrfach. In Zeile 2 stehen ebenfalls verschiedene Werte, auch mehrfach. Nun muss ich also zuerst alle übereinstimmende Werte aus Zeile 1 suchen, und dann die gefundenen Werte mit einem 2. Wert vergleichen. In Zeile 3 stehen letztendlich Die Ergebnisse, von denen das übrig gebliebene ausgegeben werden soll.

    Kann mir hier jemand helfen?

  • Roger

    Hallo zusammen,

    Sollte jemand das Ganze mit der Suche nach Teilstrings benötigen, hier ist die angepasste Formel dafür.

    E1: Suchstring, A2:A260:Matrix in der gesucht wird.

    {=INDEX($A$2:$A$260;KGRÖSSTE(ISTZAHL(SUCHEN($E$1;$A$2:$A$260))*(ZEILE($A$2:$A$260)-1);ZÄHLENWENN($A$2:$A$260;”*”&$E$1&”*”)+1-ZEILE(A1)))}

    Die Suche verwendet Wildcards, so dass mittels “bras” Brasilien auch gefunden wird wenn in der Zelle z.B. “Brasilien – Rio de Janeiro” steht. Aber auch “Rio” wird fündig…

    LG
    Roger

  • Frank Benner

    Hallo Martin,
    erst mal vielen Dank für die ausführliche Darstellung. Das ist super, selten eine so gute Erläuterung gelesen, hat mir sehr weiter geholfen.

    Leider kommt bei mir nicht das Ergebnis raus, das ich mir erhofft hatte: in jeder Zeile wird das selbe Ergebnis angezeigt (immer den letzten gefundenen Wert). Wenn ich aber den “Zählenwenn”-Teil in jeder Zeile manuell eingeben (1, 2, 3, …), wirft er mir die verschiedenen (korrekten) Ergebnisse aus. Ich nutze Excel 2007. Funktioniert die komfortable Version nur mit neueren Excel-Versionen? Oder gibt es etwas, was man an den Einstellungen (Optionen) hierzu einstellen muss?

    Ich fand es irritierend, dass ich das System verstanden habe, meine Software aber nicht so spurt wie sie soll (der Workaround mit der manuellen Eingabe funktioniert, ist aber nicht sehr elegant) 😉

    Viele Grüße,
    Frank

    • Martin Weiß Autor des Beitrags

      Hallo Frank,

      diese Formel funktioniert auch mit Excel 2007. Zwei “beliebte” Fehler:
      Entweder Du hast die erste Formel nicht mit STRG+Umschalt+Enter abgeschlossen.
      Oder Du hast Dich bei den Zellbezügen verzettelt (Stichwort absolute und relative Zellbezüge). Ich tippe mal darauf. Also ggf. nochmal genau prüfen, ob überall die Dollarzeichen korrekt gesetzt sind.

      Schöne Grüße,
      Martin

  • Giacomo Simon

    Eine richtig tolle Formel! Funktioniert hervorragend.
    Was ist, wenn ich noch nach einem zweiten Kriterium suche? Das erste wird ja mit F1 für A2:A309 angegeben. Jetzt habe ich noch ein zweites F2 in der Spalte D2:D309; jetzt möchte ich nur die Städte ausgespuckt bekommen, die beide Kriterien erfüllen.

    Ist es möglich noch nach einem zweiten Kriterium zu suchen, zum Beispiel:
    {=Summenprodukt(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)))+{=INDEX($B$2:$B$309;KGRÖSSTE(($D$2:$A$309=$G$1)*(ZEILE($D$2:$A$309)-1);ZÄHLENWENN($D$2:$D$309;$G$1)+1-ZEILE(A1)))}}

    Wäre super wenn ihr mir helfen könntet 🙂
    Mit freundlichen Grüßen
    Giacomo

    • Martin Weiß Autor des Beitrags

      Hallo Giacomo,

      tut mir leid, wenn die Antwort ziemlich spät kommt. Hier wäre meine Lösung dazu. Die Annahme ist, dass in Spalte D das zusätzliche Kriterium steht und in Zelle G1 dieses Kriterium ausgewählt wird:
      {=WENNFEHLER(INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*($D$2:$D$309=$G$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENNS($A$2:$A$309;$F$1;$D$2:$D$309;$G$1)+1-ZEILE(A1)));””)}

      Schöne Grüße,
      Martin

  • Jana

    Hallo Martin,
    vielleicht kannst du auch mir mit meinem Excel Problem helfen.

    Ich möchte prüfen, ob eine Spalte mit Namen (Auszug aus einer Datenbank) einen von mehreren gesuchten Namen aus einem zweitem Sheet enthält. Überall dort, wo ein Name aus der Liste entdeckt wird soll in der Zielzelle ja ansonsten nein stehen.
    Ich habe schon etliche Formelkombinationen versucht, komme aber nicht auf das gewünschte Ergebnis.

    Hoffe, du kannst mir helfen. Danke!

  • Daniel

    Hallo Martin,

    eine echt tolle Idee, hat mir gerade sehr geholfen.

    Ein Fehler produziert die Formel bei mir jedoch, es wird zwar die richtige Anzahl an verschiedenen Konten angezeigt, aber immer nur der erste Eintrag in Spalte B. Mache ich hier etwas falsch?

    Beste Grüße
    Daniel

    • Martin Weiß Autor des Beitrags

      Hallo Daniel,

      bist Du sicher, dass Du die Formel mit STRG+Umschalt+Eingabe abgeschlossen hast? Und danach die Formel soweit nach unten wie nötig kopiert hast?

      Schöne Grüße,
      Martin

  • Roland Wißler

    Hallo Martin,
    du hast ja supertolle Formeln, die ich in meine Listen einbaue,
    aber wie suche ich aus einer Reihe von Datum´s, die gleichen werte.
    Ich will wissen welche Ereignisse (wie z.B. Geburtsage) fallen auf einen Tag.

    bestimmt ganz einfach.
    Gruß Roland

    • Martin Weiß Autor des Beitrags

      Hallo Roland,

      das machst Du einfach so, wie oben im Artikel beschrieben. Nur eben nicht mit Ländern und Städten, sondern mit Datum und Ereignissen. Funktioniert ganz genauso.

      Schöne Grüße,
      Martin

  • Kevin

    Hallo Martin,

    erst mal danke für den tollen Artikel und die super Erklärung. Hat mir sehr weitergeholfen.

    Prinzipiell funktioniert die Matrixformel bei mir auch. Allerdings habe ich die zu durchsuchenden Werte nicht in der Datei in der die Formel steht, sondern in einer separaten Datei. Solange ich beide Dateien geöffnet habe, funktioniert die Formeln einbahnfrei. Wenn ich allerdings die Datei mit den Ursprungsdaten geschlossen habe, zieht die Formel mit keine Werte.

    Hast du hierfür eine Lösung? Dachte eigentlich das Matrixformlen dieses Problem umgehen sollten.?!

    Grüße Kevin

    • Martin Weiß Autor des Beitrags

      Hallo Kevin,

      dafür kenne ich leider auch keine Lösung. Manche Dinge funktionieren tatsächlich nur, wenn alle alle verknüpften Dateien geöffnet sind.

      Schöne Grüße,
      Martin

    • Matthias K.

      Sind die Daten auf dem Anderen Blatt zufällig Datenbankauszüge in VB geschrieben oder ein sonstiges Makro, das sich erst beim öffnen aktiviert? Denn dann können auch keine Daten gezogen werden, da sich diese erst beim öffnen Daten beziehen.

  • Wolfram

    Hallo Martin,

    Deine Formel war sehr nützlich bei der Lösung eines aktuellen Problems – vielen Dank! Könntest Du die Formel um eine weitere Bedingung erweitern? In meinen Fall wäre das die Prüfung, ob in der jeweils gleichen Zeile bestimmte Zellen leer sind, also z.B. in der Zeile 6 sinngemäß Anzahl2(H6:K6)=0.

    Für einen Hinweis wäre ich Dir sehr dankbar.

    Viele Grüße
    Wolfram

  • Lukas

    Hallo Martin

    Besten Dank für deine Erklärungen.
    Ich bin mir aber nicht ganz sicher, ob ich mein Problem mit deiner Lösung auch beheben kann.

    In einem Register habe ich eine Sammlung von Artikelnummern.
    In einem weiteren Register habe ich eine Übersicht der Lieferanten, welche den entsprechenden Artikeln zugewiesen sind.
    Nun möchte ich im ersten Register in einer beliebigen Zelle die möglichen Lieferanten aufgelistet haben.
    Am liebsten mit einem Zeichen (/,;:) getrennt. Möglich wäre aber auch, dass der Lieferant in einzelnen Zellen hintereinander aufgelistet werden.

    Kannst du mir da auch helfen? Ich habe es auch schon mit Pivot versucht, muss aber die Angaben in der entsprechenden Zeile haben und nicht untereinander.

    Danke bestens für deine/eure Hilfe.

    Gruss Lukas

    • Martin Weiß Autor des Beitrags

      Hallo Lukas,

      im Grund geht es nur darum, die von mir im Artikel gezeigte vertikale Darstellung in eine horizontale Darstellung zu überführen.
      Dazu musst Du nur statt die letzte ZEILE-Funktion in eine SPALTE-Funktion ändern.

      Also aus
      {=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)));””)}
      wird
      {=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-SPALTE(A1)));””)}

      Und dann natürlich die Formel nach rechts in jeweils in eine eigene Spalte kopieren. Wenn Du jetzt ein Land auswählst, werden die passenden Städte nebeneinander angezeigt. Und das kannst Du natürlich auf Deine Artikel und Lieferanten analog anwenden.

      Schöne Grüße,
      Martin

  • Florian Seißler

    Ich finde diese Formel für mein Vorhaben echt perfekt. Ist es möglich diese Formel auch Tabellenübergreifend anzuwenden?
    Tabelle 1 = Datensatz
    Tabelle 2= Suchfunktion

    • Martin Weiß Autor des Beitrags

      Hallo Florian,

      ja, das sollte keine Problem sein. Einfach die Formel entsprechend anpassen, so dass der jeweilige Blattname enthalten ist.

      Schöne Grüße,
      Martin

  • Florian Seißler

    Was muss ich machen damit leere Zeilen nicht als 0 angezeigt werden?

    =WENNFEHLER(INDEX($B$2:$B$96;KGRÖSSTE(($A$2:$A$96=$J$1)*(ZEILE($A$2:$A$96)-1);ZÄHLENWENN($A$2:$A$96;$J$1)+1-ZEILE(A1)));””)

    Wenn ich ein Land eingebe und habe keine Stadt hinterleget wird mir die eigentlich leere Zeile mit einer 0 gefüllt.
    Ist es möglich das die leere Zeile auch leer angezeigt wird?

    • Martin Weiß Autor des Beitrags

      Hallo Florian,

      du müsstest dazu denn INDEX-Teil der Formel nochmal mit einer WENN-Funktion umschließen:
      =WENNFEHLER(WENN(INDEX($B$2:$B$96;KGRÖSSTE(($A$2:$A$96=$J$1)*(ZEILE($A$2:$A$96)-1);ZÄHLENWENN($A$2:$A$96;$J$1)+1-ZEILE(A1)))=0;””;INDEX($B$2:$B$96;KGRÖSSTE(($A$2:$A$96=$J$1)*(ZEILE($A$2:$A$96)-1);ZÄHLENWENN($A$2:$A$96;$J$1)+1-ZEILE(A1))));””)

      Alternativ kannst Du natürlich in den Excel-Optionen die Anzeige von Nullwerten für das betreffende Arbeitsblatt generell unterdrücken:
      Excel-Optionen | Erweitert | Optionen für dieses Arbeitsblatt anzeigen | In Zellen mit Nullwert eine Null anzeigen

      Schöne Grüße,
      Martin

  • Ric

    Hallöchen,

    ich bin Absoluter Excel-Anfänger, und dein Formel und vor allem die Erklärung hat mir super geholfen, leider macht sie bei mir Fehler.
    Ich verwende sie über mehrer Spalten und ab und zu kann es vorkommen, dass in einer Spalte in jeder Zelle das Suchkriterium steht, dann soll er mir einfach alle ausgeben er gibt mir dann aber nur jedes weite Ergebnis. Auch wenn nicht komplette Spalte gefüllt ist, sondern nur recht voll, fehlen auch schon einige Einträge, wie kann ich das Lösen?

    • Martin Weiß Autor des Beitrags

      Hallo Ric,

      die hier vorgestellte Formel ist alles andere als trivial, daher Respekt, wenn Du Dich als Excel-Anfänger daran wagst. Deine Frage ist aber leider ein wenig zu allgemein gehalten, um hier einen konkreten Tipp geben zu können. In vielen Fällen liegt das Problem darin, dass der Wert nicht passt, der zur ZEILE-Funktion addiert und hinterher wieder abgezogen wird (in meinem Beispiel 1). Ist auch weiter oben in den Kommentaren schon mal beschrieben. Ansonsten hilft nur weiterprobieren…

      Schöne Grüße,
      Martin

  • Lisa

    Hallo Martin,

    ich bin ebenfalls kein Excel-Experte – von daher vorab schon einmal vielen Dank für die ausführlichen Erklärungen! Dadurch hab ich die Formel super verstanden.

    Allerdings bin ich bei der Weiterführung meiner Tabelle soeben auf das nächste Problem gestoßen und hoffe du kannst mir hier ebenfalls helfen. Ich habe nun, dank deiner Hilfe, eine Tabelle mit mehreren Spalten (T1, T2, T3, T4,….). Unter diesen Spalten sind Werte aufgelistet S1 bis S20. Hier können einmal nur 2 Werte stehen, manchmal aber auch mehr. Innerhalb der Spalten gibt es keine doppelten Werte. Spaltenübergreifend aber schon. Ich benötige nun eine Auflistung aller Werte ohne Dopplungen mit einer möglichst verständlichen Excelformel. Kannst du mir hier weiterhelfen?

    Viele Grüße
    Lisa

    • Martin Weiß Autor des Beitrags

      Hallo Lisa,

      eine einfache Formel, wie man spaltenübergreifend alle eindeutigen Werte ausgibt, habe ich spontan leider auch nicht. Vielleicht hat ja ein anderer Leser noch einen Tipp.

      Schöne Grüße,
      Martin

Kommentare sind geschlossen.