Excel – Datenüberprüfung mit Suchfunktion

Bei der Datenüberprüfung bzw. Datengültigkeit kann man auf starre bzw. dynamisch ermittelte Listen zurückgreifen. Doch was, wenn es zu viele Einträge gibt? Durch Eingabe eines Bestandteils der Auswahl kann man das Angebot der Datenüberprüfung auf mögliche Treffer eingrenzen.

Meine Lösung nutzt die Funktion AGGREGAT und ist somit ab Excel 2010 verfügbar. Bei Verwendung der Funktion SUMMENPRODUKT könnte man Funktionen auch für ältere Excel-Versionen generieren.

Zunächst werden in Zelle B1 alle Einträge aus Spalte D [Namen] angeboten. Wenn jedoch wie hier unter (1) ein Textfragment eingegeben wird und anschließend mit STRG + ENTER bzw. Enter die Eingabe bestätigt wird, werden anschließend (2)die Daten in Spalte E [Drowpdown] neu berechnet und anschließend für die Auswahl in B1 zur Verfügung gestellt (3).

425

Die Liste der Namen wird als Tabelle formatiert (Strg + T). Somit erweitert sich diese automatisch, wenn Namen ergänzt oder gelöscht werden. In die Spalte daneben werden per Formel die Werte eingetragen, die sich mit der Eingabe in B1 decken.

Formel für Zelle E2, Spalte [Dropdown]:
=WENNFEHLER(INDEX([Namen];AGGREGAT(15;6;(ZEILE([Namen])-1)/(--SUCHEN($B$1;[Namen])>0);ZEILE()-1);1);"")

Was macht die Formel? Die Funktion Suchen prüft, ob in den Zellen von [Namen] der Text aus Zelle B1 steht. Falls ja, kommt bei Suchen ein Zahlenwert heraus. Falls Nein, resultiert hier ein Fehler, der aber durch den Parameter 6 bei AGGREGAT eliminiert wird.

Wenn also nun bei SUCHEN der Textteil gefunden wird und somit durch die Prüfung ein WAHR resultiert, wird dieses durch — zu einer 1. Es wird jede Zeile aus Namen durch 1 oder einen Fehler geteilt. Es resultieren alle Zeilen, in denen der Text aus B1 steht. Bei der Zeile muss noch um -1 korrigiert werden, da der Bereich [Namen] in Zeile 2 startet.

Nun kommt AGGREGAT ins Spiel. AGGREGAT(15) entspricht KKLEINSTE. Nur, dass hier im ARRAY besser Fehler eliminiert werden können und keine Matrixformelschreibweise erforderlich ist. Bei AGGREGAT schaue ich nun nach dem kleinsten Zeilenwert, dem zweitkleinsten und so weiter. Die Formel kopiert sich automatisch nach unten durch und zeigt alle Zeilennummern der Bereiche aus Namen, in denen der Texteintrag aus B1 steht.

Letzt endlich werden nicht die Zeilennummern, sondern per INDEX die Texte direkt angezeigt.
Mit WENNFEHLER werden dann alle resultierenden Fehlermeldungen in ein „“ verwandelt.

Zur Vorbereitung muss ein Bereich per Namen definiert werden.
Namen können über Formeln – Namens-Manager manuell eingegeben werden.

425a

Im Namen wird der Bereich ermittelt, in dem bei [Dropdown] tatsächlich nur die Namen stehen. Die leeren Zellen sollen ausgeschlossen werden.

Dieser Bereich wird als Liste in der Datenüberprüfung zugelassen. Der Schalter dazu befindet sich im Register Daten.

425b

Im Register Fehlermeldung muss noch der obere Haken deaktiviert werden oder auf Information gewechselt werden, damit unfertige Einträge in Zelle B1 getätigt werden können.

Hier gibt es die Demodatei:
E425_DatenueberpruefungSuche

Und natürlich das Video – Teil 1:

Und der zweite Teil: