Dynamisches Excel-Filtersuchfeld (Daten während der Eingabe extrahieren)

Excel-Filter ist eine der am häufigsten verwendeten Funktionen, wenn Sie mit Daten arbeiten. In diesem Blogbeitrag zeige ich Ihnen, wie Sie ein dynamisches Excel-Filtersuchfeld erstellen, sodass die Daten basierend auf Ihrer Eingabe in das Suchfeld gefiltert werden.

Etwas wie unten gezeigt:

Dies hat eine doppelte Funktionalität – Sie können den Namen eines Landes aus der Dropdown-Liste auswählen oder die Daten manuell in das Suchfeld eingeben, und es werden Ihnen alle passenden Datensätze angezeigt. Wenn Sie beispielsweise „I“ eingeben, werden Ihnen alle Ländernamen mit dem Alphabet I angezeigt.

Video ansehen - Erstellen eines dynamischen Excel-Filtersuchfelds

Erstellen eines dynamischen Excel-Filtersuchfelds

Dieser dynamische Excel-Filter kann in 3 Schritten erstellt werden:

1. Abrufen einer eindeutigen Liste von Elementen (in diesem Fall Länder). Dies würde beim Erstellen des Dropdown-Menüs verwendet werden.
2. Erstellen des Suchfelds. Hier habe ich eine Combo Box (ActiveX Control) verwendet.
3. Einstellen der Daten. Hier würde ich drei Hilfsspalten mit Formeln verwenden, um die übereinstimmenden Daten zu extrahieren.

So sehen die Rohdaten aus:

NÜTZLICHER TIPP: Es ist fast immer eine gute Idee, Ihre Daten in eine Excel-Tabelle umzuwandeln. Sie können dies tun, indem Sie eine beliebige Zelle im Datensatz auswählen und die Tastenkombination Strg + T verwenden.

Schritt 1 - Eine einzigartige Liste von Elementen erhalten

1. Wählen Sie alle Länder aus und fügen Sie sie in ein neues Arbeitsblatt ein.
2. Wählen Sie die Länderliste -> Gehe zu Daten -> Duplikate entfernen.
3. Wählen Sie im Dialogfeld Duplikate entfernen die Spalte aus, in der sich die Liste befindet, und klicken Sie auf OK. Dadurch werden Duplikate entfernt und Sie erhalten eine eindeutige Liste wie unten gezeigt:
4. Ein zusätzlicher Schritt besteht darin, einen benannten Bereich für diese eindeutige Liste zu erstellen. Um dies zu tun:
   • Gehen Sie zur Registerkarte Formel -> Name definieren
   • Im Dialogfeld „Namen definieren“:
     • Name: Länderliste
     • Geltungsbereich: Arbeitsbuch
     • Bezieht sich auf: =UniqueList!$A$2:$A$9 (Ich habe die Liste in einer separaten Registerkarte namens UniqueList in A2:A9. Sie können darauf verweisen, wo sich Ihre einzigartige Liste befindet)

HINWEIS: Wenn Sie die Methode „Duplikate entfernen“ verwenden und Ihre Daten erweitern, um weitere Datensätze und neue Länder hinzuzufügen, müssen Sie diesen Schritt erneut wiederholen. Alternativ können Sie auch eine Formel verwenden, um diesen Prozess dynamisch zu gestalten.

Schritt 2 - Erstellen des Suchfelds für den dynamischen Excel-Filter

Damit diese Technik funktioniert, müssen wir ein „Suchfeld“ erstellen und mit einer Zelle verknüpfen.

Wir können das Kombinationsfeld in Excel verwenden, um diesen Suchfeldfilter zu erstellen. Auf diese Weise wird jedes Mal, wenn Sie etwas in das Kombinationsfeld eingeben, dies auch in Echtzeit in einer Zelle widergespiegelt (wie unten gezeigt).

Hier sind die Schritte, um dies zu tun:

1. Gehen Sie zur Registerkarte Entwickler -> Steuerelemente -> Einfügen -> ActiveX-Steuerelemente -> Kombinationsfeld (ActiveX-Steuerelemente).
   • Wenn die Registerkarte "Entwickler" nicht sichtbar ist, können Sie sie wie folgt aktivieren.
2. Klicken Sie irgendwo auf das Arbeitsblatt. Es wird das Kombinationsfeld einfügen.
3. Klicken Sie mit der rechten Maustaste auf das Kombinationsfeld und wählen Sie Eigenschaften.
4. Nehmen Sie im Eigenschaftenfenster die folgenden Änderungen vor:
   • Verknüpfte Zelle: K2 (Sie können eine beliebige Zelle auswählen, in der die Eingabewerte angezeigt werden sollen. Wir verwenden diese Zelle zum Festlegen der Daten).
   • ListFillRange: CountryList (dies ist der benannte Bereich, den wir in Schritt 1 erstellt haben. Dies würde alle Länder in der Dropdown-Liste anzeigen).
   • MatchEntry: 2-fmMatchEntryNone (dadurch wird sichergestellt, dass ein Wort während der Eingabe nicht automatisch vervollständigt wird)
5. Wenn das Kombinationsfeld ausgewählt ist, gehen Sie zur Registerkarte Entwickler -> Steuerelemente -> Klicken Sie auf Entwurfsmodus (dadurch verlassen Sie den Entwurfsmodus und jetzt können Sie alles in das Kombinationsfeld eingeben. Jetzt würde alles, was Sie eingeben, in Zelle K2 widergespiegelt werden in Echtzeit)

Schritt 3 - Einstellen der Daten

Schließlich verknüpfen wir alles durch Hilfsspalten. Ich verwende hier drei Hilfsspalten, um die Daten zu filtern.

Helfer Spalte 1: Geben Sie die Seriennummer für alle Datensätze ein (in diesem Fall 20). Dazu können Sie die ROWS()-Formel verwenden.

Helfer Spalte 2: In Hilfsspalte 2 prüfen wir, ob der im Suchfeld eingegebene Text mit dem Text in den Zellen der Länderspalte übereinstimmt.

Dies kann mit einer Kombination von IF-, ISNUMBER- und SEARCH-Funktionen erfolgen.

Hier ist die Formel:

=WENN(ISNUMMER(SUCHE($K$2,D4)),E4,"")

Diese Formel sucht nach dem Inhalt im Suchfeld (das mit Zelle K2 verknüpft ist) in der Zelle mit dem Ländernamen.

Bei Übereinstimmung gibt diese Formel die Zeilennummer zurück, andernfalls gibt sie ein Leerzeichen zurück. Wenn das Kombinationsfeld beispielsweise den Wert "US" hat, haben alle Datensätze mit dem Land "US" die Zeilennummer und der Rest wäre leer ("")

Helferspalte 3: In der Hilfsspalte 3 müssen wir alle Zeilennummern aus der Hilfsspalte 2 zusammenfassen. Dazu können wir eine Kombination aus if IFERROR und SMALL Formeln verwenden. Hier ist die Formel:

=WENNFEHLER(KLEIN($F$4:$F$23,E4),"")

Diese Formel stapelt alle übereinstimmenden Zeilennummern zusammen. Wenn die Combo Box beispielsweise den Wert US hat, werden alle Zeilennummern mit „US“ gestapelt.

Wenn wir nun die Zeilennummern zusammengestapelt haben, müssen wir nur die Daten in dieser Zeilennummer extrahieren. Dies kann ganz einfach mit der Indexformel erfolgen (fügen Sie diese Formel dort ein, wo Sie die Daten extrahieren möchten. Kopieren Sie sie in die obere linke Zelle, in die die Daten extrahiert werden sollen, und ziehen Sie sie dann nach unten und rechts).

=IFERROR(INDEX($B$4:$D$23,$G4,SPALTEN($I$3:I3)),"")

Diese Formel besteht aus 2 Teilen:
INDEX - Dies extrahiert die Daten basierend auf der Zeilennummer.
WENNFEHLER - Dies gibt leer zurück, wenn keine Daten vorhanden sind.

Hier ist eine Momentaufnahme von dem, was Sie schließlich erhalten:

Die Combo Box ist sowohl ein Dropdown- als auch ein Suchfeld. Sie können die ursprünglichen Daten- und Hilfsspalten ausblenden, um nur die gefilterten Datensätze anzuzeigen. Sie können die Rohdaten- und Hilfsspalten auch in einem anderen Arbeitsblatt haben und diesen dynamischen Excel-Filter in einem anderen Arbeitsblatt erstellen.

Werde kreativ! Probieren Sie einige Variationen aus

Sie können versuchen, es an Ihre Anforderungen anzupassen. Möglicherweise möchten Sie mehrere Excel-Filter anstelle eines erstellen. Sie können beispielsweise Datensätze filtern, bei denen der Vertriebsmitarbeiter Mike und das Land Japan ist. Dies kann genau nach den gleichen Schritten mit einigen Änderungen der Formel in den Hilfsspalten erfolgen.

Eine andere Variante könnte darin bestehen, Daten zu filtern, die mit den Zeichen beginnen, die Sie in das Kombinationsfeld eingeben. Wenn Sie beispielsweise „I“ eingeben, möchten Sie möglicherweise Länder extrahieren, die mit I beginnen (im Vergleich zum aktuellen Konstrukt, bei dem Sie auch Singapur und Philippinen erhalten, da es das Alphabet I enthält).

Wie immer sind die meisten meiner Artikel von den Fragen/Antworten meiner Leser inspiriert. Ich würde mich freuen, Ihr Feedback zu bekommen und von Ihnen zu lernen. Hinterlassen Sie Ihre Gedanken im Kommentarbereich.

Hinweis: Falls Sie Office 365 verwenden, können Sie die FILTER-Funktion verwenden, um die Daten während der Eingabe schnell zu filtern. Es ist einfacher als die in diesem Tutorial gezeigte Methode.