✅ Erstellen Sie eine Excel-Dropdown-Liste mit Suchvorschlägen

Inhaltsverzeichnis

Durchsuchbare Dropdown-Liste in Excel

Wir alle verwenden Google als Teil unserer täglichen Routine. Eine seiner Funktionen ist der Suchvorschlag, bei dem Google intelligent handelt und uns während der Eingabe eine Liste mit Vorschlägen gibt.

In diesem Tutorial erfahren Sie, wie Sie in Excel eine durchsuchbare Dropdown-Liste erstellen, d. h. eine Dropdown-Liste, die die passenden Elemente während der Eingabe anzeigt.

Unten ist ein Video dieses Tutorials (falls Sie lieber ein Video ansehen als den Text zu lesen).

Durchsuchbare Dropdown-Liste in Excel

Für dieses Tutorial verwende ich die Daten der Top-20-Länder nach BIP.

Die Absicht besteht darin, eine Excel-Dropdown-Liste mit einem Suchvorschlagsmechanismus zu erstellen, sodass ein Dropdown mit den passenden Optionen angezeigt wird, während ich in die Suchleiste tippe.

Etwas wie unten gezeigt:

Um mitzumachen, laden Sie die Beispieldatei von hier herunter

Das Erstellen der durchsuchbaren Dropdown-Liste in Excel wäre ein dreiteiliger Prozess:

Konfigurieren des Suchfelds.
Einstellen der Daten.
Schreiben Sie einen kurzen VBA-Code, damit es funktioniert.

Schritt 1 - Konfigurieren des Suchfelds

In diesem ersten Schritt verwende ich ein Kombinationsfeld und konfiguriere es so, dass der Text bei der Eingabe auch in Echtzeit in einer Zelle widergespiegelt wird.

Hier sind die Schritte, um dies zu tun:

Gehen Sie zur Registerkarte Entwickler -> Einfügen -> ActiveX-Steuerelemente -> Kombinationsfeld (ActiveX-Steuerelement).
- Es besteht die Möglichkeit, dass Sie die Registerkarte Entwickler im Menüband nicht finden. Standardmäßig ist es ausgeblendet und muss aktiviert werden. Klicken Sie hier, um zu erfahren, wie Sie die Registerkarte Entwickler im Menüband in Excel aufrufen.
Bewegen Sie den Cursor in den Arbeitsblattbereich und klicken Sie irgendwo. Es wird ein Kombinationsfeld eingefügt.
Klicken Sie mit der rechten Maustaste auf das Kombinationsfeld und wählen Sie Eigenschaften.
Nehmen Sie im Eigenschaftendialog die folgenden Änderungen vor:
- Automatische Wortauswahl: Falsch
- Verknüpfte Zelle: B3
- ListFillRange: DropDownList (wir werden in Schritt 2 einen benannten Bereich mit diesem Namen erstellen)
- Match-Eintrag: 2 - fmMatchEntryNone

(Zelle B3 ist mit dem Kombinationsfeld verknüpft, was bedeutet, dass alles, was Sie in das Kombinationsfeld eingeben, in B3 eingegeben wird)

Gehen Sie zur Registerkarte Entwickler und klicken Sie auf Entwurfsmodus. Dadurch können Sie Text in das Kombinationsfeld eingeben. Da Zelle B3 mit dem Kombinationsfeld verknüpft ist, wird jeder Text, den Sie in das Kombinationsfeld eingeben, auch in Echtzeit in B3 wiedergegeben.

Schritt 2 - Einstellen der Daten

Nun, da das Suchfeld eingerichtet ist, müssen wir die Daten an Ort und Stelle bringen. Die Idee ist, dass, sobald Sie etwas in das Suchfeld eingeben, nur die Elemente angezeigt werden, die diesen Text enthalten.

Dazu verwenden wir

Drei Helferspalten.
Ein dynamischer benannter Bereich.

Helferspalte 1

Fügen Sie die folgende Formel in Zelle F3 ein und ziehen Sie sie für die gesamte Spalte (F3:F22)

=--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),""))

Diese Formel gibt 1 zurück, wenn der Text im Kombinationsfeld im Namen des Landes auf der linken Seite steht. Wenn Sie beispielsweise UNI eingeben, werden nur die Werte für United Staaten und United Kingdom sind 1 und alle verbleibenden Werte sind 0.

Helferspalte 2

Fügen Sie die folgende Formel in Zelle G3 ein und ziehen Sie sie für die gesamte Spalte (G3: G22)

=WENN(F3=1,ZÄHLENWENN($F$3:F3,1),"")

Diese Formel gibt 1 für das erste Vorkommen zurück, wobei der Kombinationsfeldtext mit dem Ländernamen übereinstimmt, 2 für das zweite Vorkommen, 3 für das dritte und so weiter. Wenn Sie beispielsweise UNI eingeben, zeigt die Zelle G3 1 an, wenn sie den Vereinigten Staaten entspricht, und G9 zeigt 2 an, wenn sie Großbritannien entspricht. Der Rest der Zellen bleibt leer.

Helferspalte 3

Fügen Sie die folgende Formel in Zelle H3 ein und ziehen Sie sie für die gesamte Spalte (H3:H22)

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")

Diese Formel stapelt alle übereinstimmenden Namen ohne leere Zellen dazwischen. Wenn Sie beispielsweise UNI eingeben, würde diese Spalte 2 und 9 zusammen anzeigen, und alle übrigen Zellen wären leer.

Erstellen des dynamischen benannten Bereichs

Nachdem die Hilfsspalten nun vorhanden sind, müssen wir den dynamischen benannten Bereich erstellen. Dieser benannte Bereich bezieht sich nur auf die Werte, die mit dem in das Kombinationsfeld eingegebenen Text übereinstimmen. Wir verwenden diesen dynamischen benannten Bereich, um die Werte im Dropdown-Feld anzuzeigen.

Notiz: In Schritt 1 haben wir DropDownList in die ListFillRange-Option eingegeben. Jetzt erstellen wir den benannten Bereich mit dem gleichen Namen.

Hier sind die Schritte, um es zu erstellen:

Gehen Sie zu Formeln -> Namensmanager.
Klicken Sie im Dialogfeld des Namensmanagers auf Neu. Es öffnet sich ein Dialogfeld Neuer Name.
Geben Sie im Namensfeld DropDownList . ein
Geben Sie im Feld Bezieht sich auf die Formel ein: =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Schritt 3 - Den VBA-Code zum Laufen bringen

Wir sind fast da.

Der letzte Teil besteht darin, einen kurzen VBA-Code zu schreiben. Dieser Code macht das Dropdown-Menü dynamisch, sodass die übereinstimmenden Elemente/Namen angezeigt werden, während Sie in das Suchfeld eingeben.

So fügen Sie Ihrer Arbeitsmappe diesen Code hinzu:

Klicken Sie mit der rechten Maustaste auf die Registerkarte Arbeitsblatt und wählen Sie Code anzeigen aus.

Kopieren Sie im VBA-Fenster den folgenden Code und fügen Sie ihn ein:

Private Sub ComboBox1_Change() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub

Das ist es!!

Sie sind mit Ihrer eigenen Google-Suchleiste ausgestattet, die während der Eingabe übereinstimmende Elemente anzeigt.

Für ein besseres Erscheinungsbild können Sie Zelle B3 mit dem Kombinationsfeld abdecken und alle Hilfsspalten ausblenden. Mit diesem erstaunlichen Excel-Trick können Sie jetzt ein wenig angeben.

Um mitzumachen, laden Sie die Datei hier herunter

Was denken Sie? Könnten Sie diese Dropdown-Liste mit Suchvorschlägen in Ihrer Arbeit verwenden? Teilen Sie mir Ihre Meinung mit, indem Sie einen Kommentar hinterlassen.

Wenn Ihnen dieses Tutorial gefallen hat, möchten Sie sicher auch die folgenden Excel-Tutorials:

Dynamischer Filter - Extrahieren Sie übereinstimmende Daten während der Eingabe.
Extrahieren Sie Daten basierend auf einer Dropdown-Listenauswahl.
Erstellen von abhängigen Dropdown-Listen in Excel.
Die ultimative Anleitung zur Verwendung der Excel SVERWEIS-Funktion.
So treffen Sie eine Mehrfachauswahl in einer Dropdown-Liste in Excel.
So fügen Sie ein Kontrollkästchen in Excel ein und verwenden es.