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:

14 Kommentare

  1. Hagen Pflueger

    Das nennt man wohl dann Massgeschneidert.
    Herzlichen Dank! Genau was ich brauche.

    • thehosblog

      na, bitte schön. Hatte im Zug Langeweile und dachte mir, dass man doch sicher Dein Problem lösen kann.

  2. Ikarus

    Genau die Funktion habe ich gesucht… allerdings habe ich leider nur Excel 2007 und müsste demnach mit Summenprodukt arbeiten. Ich bekomm es aber leider nicht hin… Wär es möglich hier ebenfalls Hilfestellung zu leisten??? 🙂

    • thehosblog

      Hallo, aktuell schaffe ich das zeitlich nicht…
      aber gucke doch mal bei excelformeln.de – dort gibt es Funktionen, die bei älteren Excel-Versionen funktionieren.

  3. plutsch

    Hallo, wie kann ich den diese Formel auch ohne AGGREGAT (Ich habe auch Excel 2007) … bei ecxelformeln.de ist das nicht ganz so einfach zu finden… das wäre suuuuper

    • Ikarus

      …versuchs mal mit der Nr. 343 in exelformel.de (Listen/Datensätze und dann Suchbegriffsliste). Hat mir auch weitergeholfen! Aber nach Eingabe der Formel (ohne geschweifte Klammer) „Strg + Shift + Enter“ nicht vergessen!!!

  4. Fetter40

    Hallo zusammen,
    alle Beiträge sind gut erklärt und helfen mir bei meiner Arbeit. Man merkt, das viel Praxiswissen dahinter steckt.
    Danke und weiter so!
    Tschad Norbert

    • Rogue

      Guten Morgen,

      wie kann man es über 2 Tabellenbaltt erstellen?
      Soll heißen Die daten Stehen steht in 1. Tabellenblatt, und die Suchfunkton im 2. Tabellenblatt.
      Wie kann man das verändern bzw. erstellen?

      Gruß
      Rogue

  5. Rogue

    Guten Tag,
    wie kann man die Forumel verwenen, wenn man die daten aus einen anderen Tabellenblatt nehmen möchte?
    und dann auch als Tabelle? ich habe es nur bis 1 Tabelleblat hinbekommen, möchte es aber mit 2 hinbekommen!

    hier meine Formel:

    1 tabellenblatt: =WENNFEHLER(INDEX([Bezeichnung Konsolen];AGGREGAT(15;6;(ZEILE([Bezeichnung Konsolen])-1)/(–SUCHEN($B$1;[Bezeichnung Konsolen])>0);ZEILE()-1);1);““)

    klappt aber nicht….2 Tabellenblatt: =WENNFEHLER(INDEX([Bezeichnung Konsolen];AGGREGAT(15;6;(ZEILE([Bezeichnung Konsolen])-1)/(–SUCHEN($D$2;[Bezeichnung Konsolen])>0);ZEILE()-1);1);““)

    Kann mir da einer weiter helfen?

    Gruß
    Rogue

  6. Dieter Emonts

    Hallo!
    Sehr schöne Lösung, danke.

    Ich suche aber noch die Möglichkeit, dass die nächste Auswahl in der Zelle „B2“ und dann in „B3“, usw. stattfinden soll.
    Da scheitert die hier vorgestellte Methode leider.

    Vielleicht gibt es ja dafür auch noch einen Nachschlag!

    Vielen Dank und alle Daumen hoch,
    demonts

    • thehosblog

      Hallo, kannst Du Dein Vorhaben noch einmal kurz mit anderen Worten beschreiben? Ich kann mir unter Deinen Auswahlfeldern leider gerade nichts konkretes vorstellen. Merci und Danke für das Feedback, Andreas

  7. Oliver

    Hallo Andreas,
    erstmal tausend Dank für deine vielen Tipps, Erklärungen, Videos und und und …
    Habe deine „Dropdown mit Suchfunktion“- Formel aus dem Video in mein Arbeitsblatt übernommen und es funktioniert, aber leider nur wenn die Dropdown Funktion im gleichen Arbeitsblatt platziert ist. Ich habe die Dropdown Funktion dann auf einem anderen Blatt eingesetzt und es kam die Fehlermeldung bei der Datenüberprüfung: Die Quelle untersucht gerade einen möglichen Fehler.
    Wodurch kommt die Meldung zu Stande und wie löse ich das Problem?

    Vielen Dank im voraus!

  8. Martin

    Hallo Andreas
    Ich habe deine Formel gerade erfolgreich verwenden können. Super Sache…aber….Ich das Eingabefeld nicht im gleichen Blatt. Daten sind im Blatt Tab2 und das Eingabefeld in Tab1. Wie muss ich deine „rngBereich“ Formel (Indirekt) abändern, damit ich auf das zweite Blatt zugreifen kann?
    Danke für deine Unterstützung
    Martin