Wenn ihr in einer Excel-Tabelle mit Texteinträgen nach Namen suchen wollt, könnt ihr hierfür Formeln nutzen. In diesem Guide zeigen wir euch Schritt für Schritt, wie ihr Namen nachvollziehbar und leicht finden könnt. Auf geht’s!
Diese Beispielbilder und -Funktionen sind mit Excel 365 bearbeitet worden, doch sollten auch mit älteren Versionen, wie Office 2010, funktionieren.
Als erstes bekommt ihr eine kurze Einleitung, welche Formeln zum Suchen von Namen verwendet werden und welche Funktion jede von ihnen hat:
- FINDEN steht für das Suchen und Finden von Zeichen und Buchstaben. Diese Funktion unterscheidet zwischen Groß- und Kleinschreibung, was beispielsweise bei Namen sehr wichtig ist!
- Mit AGGREGAT könnt ihr nicht bloß nach einzelnen Werten zu suchen, sondern nach allen, die in einer Tabelle aufgelistet sind.
- INDEX sucht nicht nach Werten, sondern nach Inhalten, deren Position aber klar definiert werden muss.
Was habt ihr alles schon mit Excel angestellt?
Funktion „Finden” nachvollziehen
Ihr habt folgendes Beispielszenario: In der Spalte A sind verschiedene Texteinträge vorgegeben, das sind zum Beispiel irgendwelche Vorgänge, die Namen enthalten. In der Spalte D sind Namen vorgegeben, nach denen ihr sucht. Das sind jene Namen, die ihr in den Texteinträgen in Spalte A finden wollt. Die Spalte B kennzeichnet, ob der Name enthalten in Spalte D in Spalte A gefunden wurde oder nicht, doch vorerst steht da noch nichts:
Zunächst stellen wir fest, dass ihr hier keine klassischen Formeln wie S-Verweis oder eine Index-Kombination nutzen müsst, da zum Beispiel „Karl” in „Karlson” enthalten ist, dies aber mit diesen Formeln doch gefunden wird. Deswegen nutzt die Funktion FINDEN und zwar folgendermaßen:
In Spalte B schreibt ihr die Formel =FINDEN(“Müller”;[@Texteintrag]). Ihr könnt anstatt [@Texteintrag] zu schreiben das Feld daneben in der Spalte A anklicken, das wird dann verallgemeinert, weil der Vorgang in eine Tabelle hinein greift und somit die gesamte Tabellenspalte (nicht die gesamte Spalte der Excel Tabelle) ausgewählt wird.
Ihr seht dann folgendes Bild:
„Müller” wurde gefunden und beginnt mit dem neunten Zeichen in Zeile 3. Wird der gesuchte Name nicht gefunden, kommt ein Fehler #WERT! zustande. Gebt ihr nun statt „Müller” „Karl” ein, seht ihr folgendes Bild:
Hier werden alle Einträge, die „Karl” enthalten, aufgelistet. Somit ergänzt ihr die Funktion folgendermaßen, um beispielsweise „Karlson” beim Suchen nach „Karl” auszuschließen – ihr hängt ein & mit einem Leerzeichen hinter den Namen an: =FINDEN("Karl"&" ";[@Texteintrag]) So findet Excel alle Namen, die nur „Karl” und dahinter keine weiteren Buchstaben enthalten. Doch ergänzen wir die Tabelle um einen weiteren Texteintrag, wo „Karl” ganz hinten steht, findet ihr diesen nun nicht mehr. Also ergänzt ihr die Formel wieder und hängt hinter dem Texteintrag & mit einem Leerzeichen an: =FINDEN("Karl"&" ";[@Texteintrag]&" ") Ihr habt die Funktion Finden jetzt kennengelernt und könnt sie anwenden. Doch nun kommen wir zur eigentlichen Hauptformel, angefangen mit der Funktion AGGREGAT.
Anleitung der Hauptformel:
- Ihr löscht die Funktionen in der Tabellenspalte „Name” und tippt =AGGREGAT( ein, dann wählt ihr den Parameter 15 für den kleinsten Wert, tippt ein Semikolon und eine 6 für den nächsten Parameter ein, dieser ignoriert Fehlerwerte.
- Nach einem weiteren Semikolon setzt ihr mit einer neuen Formel fort und tippt ZEILE ein. Dann markiert ihr mit der Maus den Bereich „Namenliste“ in Spalte D. Das heißt, ihr sagt Excel: „Suche kleinste Werte, ignoriere Fehlerwerte und gucke in der Namenliste in die einzelnen Zeilen, wo die Namen stehen, rein und mache folgendes…“
- Schließt die Klammer. Teilt durch die Frage, ob der Text in der Namenliste in den Texteinträgen gefunden werden kann. Tippt dazu weiter die Funktion FINDEN ein, die ihr schon kennengelernt habt: /(FINDEN([@Namenliste]&” “;[@Texteinträge]&” “). Hierbei werden Zahlen als Ergebnis angezeigt, was bedeutet, dass der Wert gefunden wird.
- Ihr fragt Excel nun, ob bei der Formel eine Zahl oder ein Fehlerwert als Ergebnis herauskommt, hängt hierfür also die Befehle >=1 und *1 rechts an. Das dient der Vorbeugung von Fehlermeldungen.
- Ihr schließt nun eine Klammer und wollt, dass der kleinste Wert herauskommt. Tippt dazu ;1 ein und schließt eine weitere Klammer.
Die Formel sieht nun folgendermaßen aus: =AGGREGAT(15;6;ZEILE(Tabelle2[Namenliste])/((FINDEN(Tabelle2[Namenliste]&" ";[@Texteintrag]&" ")>=1)*1);1)
Ihr seht nun folgendes Bild:
Nun könnt ihr sehen, dass Excel ausgibt, in welcher Zeile der „Namenliste” sich der gefundene Name im Texteintrag in Tabelle 1 befindet. Soweit so gut.
- Fügt am Anfang vor dem Wort AGGREGAT die Funktion INDEX hinzu, schreibt dazu INDEX( und markiert die Namenliste, dann ein Semikolon, sodass es so aussieht: =INDEX([@Namenliste];
- Es folgt die Funktion AGGREGAT. Nach der geschlossenen Klammer tippt ihr -1 ein. Das ist nötig, weil die Zeilennummer der gesamten Tabelle 2, also der Namenliste, erkannt wird und ihr umgesetzt den ersten Eintrag abzieht.
- Schließt die Klammer und tippt ;1 ein, um zu bewirkten, dass die erste Spalte der Tabelle ausgelesen wird. Schließt die nächste Klammer.
- Es werden aber noch Fehler angezeigt, daher schreibt ihr vor die INDEX-Funktion WENNFEHLER (und ganz hinten dran ;”Nicht in Liste”)
So teilt ihr Excel mit, dass, wenn in der ersten Klammer ein Fehlerwert erscheint, der Name nicht in der Liste vorhanden ist. - Das ist die endgültige Formel: =WENNFEHLER(INDEX(Tabelle2[Namenliste];AGGREGAT(15;6;ZEILE(Tabelle2[Namenliste])/((FINDEN(Tabelle2[Namenliste]&" ";[@Texteintrag]&" ")>=1)*1);1)-1;1);"Nicht in Liste")
Das Ergebnis sieht folgendermaßen aus:
Anmerkung: Da hier der kleinste Wert gesucht wird, ist hier eine Mehrfachsuche nicht enthalten.
Beispiel: Falls ihr nach „Maier mag Müller” sucht, findet Excel nur den ersten Wert der Tabelle 2 und somit den ersten Inhalt „Müller”.
Ihr könnt die nun gelernte Formel auch euren Bedürfnissen anpassen. Wenn ihr euch nicht sicher seid, dann experimentiert einfach ein bisschen herum.
Nutzt ihr kommerzielle oder Open-Source-Software?
