Beitrag aus SmartTools Excel Weekly
So finden Sie das x-te Vorkommen eines Suchkriteriums
Excel 365 2024 2021 2019 2016 2013
Die Verweisfunktionen von Excel sind leistungsfähig und vielseitig einsetzbar, aber nicht immer liefern sie direkt die gewünschten Ergebnisse. Nehmen wir beispielsweise an, dass Sie ein Tabellenblatt für Zahlungseingänge angelegt haben. Darin halten Sie unter anderem die Kundennummer und den Betrag fest. In einem anderen Tabellenblatt sollen nun die Zahlungen eines bestimmten Kunden ausgewertet werden. Wenn Sie mit der Funktion SVERWEIS in der Tabelle mit den Zahlungseingängen nach einer Kundennummer suchen, findet Excel immer nur die erste Zahlung. Bei mehrfachem Vorkommen der Kundennummer können Sie nie die Datensätze für die zweite, dritte usw. Zahlung ausfindig machen. Unser Tipp zeigt eine flexible Alternative.
Mit der Funktion SVERWEIS kommen Sie hier nicht weiter. Sie können aber mit einer Kombination aus den Funktionen INDEX und KKLEINSTE weitere Vorkommen eines Suchkriteriums in einer Liste finden. Dazu ein Beispiel.
Angenommen, Sie hätten die Zahlungseingänge in einer Tabelle mit folgendem Aufbau erfasst: Spalte A enthält die Kundennummern, Spalte B den eingegangenen Betrag und Spalte C das Eingangsdatum.
Für die im Anschluss vorgestellte Formel ist es hilfreich, den relevanten Bereichen mit Formeln-Definierte Namen-Namen definieren Namen zuzuweisen: Weisen Sie dem gesamten Bereich von der ersten Spaltenüberschrift in Spalte A bis zum letzten Zahlungsdatum in Spalte C den Namen "Tabellenwerte" zu.
Weisen Sie dem Spaltenbereich, in dem Sie nach einem Kriterium suchen wollen, den Namen "Suchspalte" zu. Im Beispiel müssten Sie also den Bereich von der ersten Spaltenüberschrift in Spalte A bis zum letzten Datensatz in Spalte A als "Suchspalte" benennen. Wichtig ist, dass der Bereich genauso hoch ist wie der mit "Tabellenwerte" benannte Bereich.
Gehen Sie dann zu dem Tabellenbereich, in dem Sie die Auswertungen vornehmen wollen, und weisen Sie der Zelle für das Suchkriterium den Namen "Kriterium" zu.
Nun können Sie in einer Zelle neben dem Suchkriterium die Formeln eingeben, mit denen Sie die verschiedenen Zahlungsbeträge zur gesuchten Kundennummer ermitteln.
Den ersten Zahlungsbetrag erhalten Sie mit folgender Formel:
=WENN(ZEILE(A1)<=ZÄHLENWENN(Suchspalte;Kriterium);
INDEX(Tabellenwerte;KKLEINSTE(WENN(Suchspalte=Kriterium;
ZEILE(INDIREKT("$1:$"&ZEILEN(Suchspalte))));
ZEILE(A1));2);"")
Schließen Sie die Formeleingabe mit Strg + Umschalt + Return ab, damit Excel die Formel als Matrixformel einträgt. In der Bearbeitungszeile wird die Formel dann automatisch mit geschweiften Klammern umgeben.
Danach kopieren Sie die Formel einfach in die darunter liegenden Zellen, um die weiteren Zahlungseingänge ausgeben zu lassen.
Die Formel beginnt mit einer WENN-Prüfung, mit der Sie testen, ob es überhaupt eine x-te Übereinstimmung mit dem Suchkriterium gibt. Dabei entspricht ZEILE(A1) dem aktuellen Zähler - in der ersten Formelzelle also dem Wert 1. Durch das Kopieren der Formel wird daraus in der nächsten Formelzelle ZEILE(A2), dann ZEILE(A3) usw., wodurch sich die Werte 2, 3 usw. ergeben. Die ZÄHLENWENN-Funktion gibt Auskunft, wie oft das Suchkriterium in der Suchspalte vorkommt. Nur wenn der Zähler kleiner oder gleich dieser Anzahl ist, fährt die WENN-Funktion mit der INDEX-Funktion fort. Ansonsten gibt sie eine leere Zeichenfolge aus.
Die INDEX-Funktion sucht eine bestimmte Position innerhalb des Bereichs "Tabellenwerte" und gibt in unserer Formel den Wert aus der zweiten Spalte zurück:
INDEX(Tabellenwerte;[...];2)
In Ihren Tabellen müssen Sie am Ende der Formel den festen Wert "2" dahingehend ändern, dass er auf die für Ihre Situation passende Spalte im Bereich "Tabellenwerte" verweist.
Die Position für die INDEX-Funktion liefert die Funktion KKLEINSTE. Sie soll jeweils den x-kleinsten Wert aus einer fortlaufenden Zahlenfolge von 1 bis zur Anzahl der Zeilen im Bereich "Suchspalte" liefern. Diese Zahlenfolge erhalten Sie mit folgendem Teil der Formel:
ZEILE(INDIREKT("$1:$"&ZEILEN(Suchspalte)))
Die Zahlenfolge entspricht den Zeilennummern im Bereich der Tabellenwerte. Dabei interessieren aber nur die Nummern der Zeilen, in denen das Suchkriterium gefunden wird. Darum kümmert sich die WENN-Funktion
WENN(Suchspalte=Kriterium;[...])
die nur die relevanten Zeilennummern zurückgibt und alle anderen Positionen auf den Wert "Falsch" setzt. Da dies der Zahl "0" entspricht, kann KKLEINSTE jeweils die x-kleinste Zeilennummer mit dem Suchkriterium ermitteln. Der wievielt kleinste Wert es sein soll, ergibt sich durch ZEILE(A1). Diese Funktion dient wie oben als Zähler, der durch das Kopieren der Formel die Werte 1, 2, 3 usw. annimmt.
So ergibt sich die Position für die INDEX-Funktion, die dann den passenden Spaltenwert zurückgibt.