Dropdown-Listenfilter in Excel und Google Sheets erstellen
In diesem Tutorial zeigen wir Ihnen, wie Sie einen Dropdown-Listenfilter in Excel und Google Sheets erstellen können.
Sie können eine Dropdown-Liste verwenden, um Datenzeilen zu extrahieren, die dem Eintrag in dieser Auswahlliste entsprechen, und diese Zeilen in einen separaten Bereich im Arbeitsblatt zurückzugeben.
Es gibt drei Hauptschritte, um diese Aktion durchzuführen.
- Erstellen Sie eine eindeutige Liste von Elementen, die in der Dropdown-Liste erscheinen sollen, da die Daten möglicherweise sich wiederholende Elemente enthalten.
- Erstellen Sie die Dropdown-Liste zum Filtern der Daten.
- Erstellen Sie den Filter mit Hilfe von Hilfsspalten, die die für die Extraktion der Daten erforderlichen Formeln enthalten.
Eine eindeutige Liste erstellen
Für den ersten Schritt benötigen Sie eine Liste mit eindeutigen Elementen für das Feld, nach dem gefiltert werden soll (hier: Produkte).
- Markieren Sie die Elemente, die in der Dropdown-Liste erscheinen sollen.
- Kopieren Sie dann die Liste und fügen Sie sie in einen anderen Bereich im Arbeitsblatt ein.
- Entfernen Sie die Duplikate. Markieren Sie die eingefügte Liste und gehen Sie dann in der Menüleiste auf Daten > Datentools > Duplikate entfernen.
- Klicken Sie auf OK und dann erneut auf OK, um die Duplikate zu entfernen und zu Excel zurückzukehren. Jetzt gibt es eine eindeutige Liste für die Dropdown-Liste.
Dropdown-Liste erstellen
Im zweiten Schritt erstellen Sie nun die Dropdown-Liste für den Filter.
- Markieren Sie die Zelle, in der Sie die Dropdown-Liste platzieren möchten, und gehen Sie dann in der Menüleiste auf Daten > Datentools > Datenüberprüfung.
- Wählen Sie im Fenster Datenüberprüfung in der Dropdown-Liste Zulassen die Option Liste, und klicken Sie auf den Pfeil neben dem Feld Quelle.
- Wählen Sie den Zellenbereich, der die oben erstellten eindeutigen Elemente enthält, und drücken Sie die EINGABETASTE.
- Klicken Sie auf OK, um zu bestätigen und das Fenster Datenüberprüfung zu schließen.
Sie können nun aus der Dropdown-Liste auswählen.
Hinweis: Sie können Ihre eigene Fehlermeldung für die Datenüberprüfung hinzufügen. Sie können auch eine Eingabemeldung zu den Zellen mit Datenüberprüfung hinzufügen, um Informationen darüber zu geben, welche Werte zulässig sind.
Daten mithilfe von Formeln extrahieren
In der dritten und letzten Phase fügen Sie Spalten mit Formeln hinzu, die neben der Dropdown-Liste als Filter dienen sollen.
- Erstellen Sie zunächst „Hilfsspalten“ in der Datentabelle. Fügen Sie rechts von der Datentabelle drei Spalten ein: Hilfsspalte 1, Hilfsspalte 2 und Hilfsspalte 3.
- Markieren Sie die erste Zelle in Hilfsspalte 1 und geben Sie dann die folgende ZEILEN-Formel ein:
=ZEILEN($B$3:B3)Kopieren Sie dann die Formel in die übrigen Zeilen der Hilfsspalte.
Diese Formel gibt jeder Zeile in Ihrer Datentabelle eine Nummer, beginnend mit der ersten Datenzeile. Beachten Sie, dass sie nicht mit der Zeilennummer in Excel übereinstimmt.
- Markieren Sie die erste Zelle in Hilfsspalte 2 und geben Sie dann die folgende Formel ein:
=WENN(C3=$J$3;F3;"")Kopieren Sie die Formel in die übrigen Zeilen der Hilfsspalte.
Diese Formel gibt die Zeilennummer aus Hilfsspalte 1 zurück , wenn der Wert in der Dropdown-Liste (J3) gleich dem Wert in derselben Zeile in Spalte C (C3) ist.
- Markieren Sie nun die erste Zelle in Hilfsspalte 3 und geben Sie die folgende Formel unter Verwendung der Funktionen WENNFEHLER und KKLEINSTE ein:
=WENNFEHLER(KKLEINSTE($G$3:$G$21;F3);"")Kopieren Sie dann die Formel in die übrigen Zeilen der Hilfsspalte.
Diese Formel gibt die 1., 2., 3. kleinste Zahl usw. in Hilfsspalte 2 zurück, basierend auf der Zeilennummer in Hilfsspalte 1.
- Nachdem Sie nun die Hilfsspalten erstellt haben, erstellen Sie die Formel in der Filterergebnistabelle.
Klicken Sie in die erste Zelle der Filterergebnistabelle (z. B. L3) und geben Sie dann die folgende Formel unter Verwendung der Funktionen WENNFEHLER, INDEX und SPALTEN ein:
=WENNFEHLER(@INDEX($B$3:$E$21;$H3;SPALTEN($L$3:L3));"")Kopieren Sie diese Formel dann nach unten und quer, um die restlichen Zellen in der Filterergebnistabelle auszufüllen.
Je nachdem, welchen Wert Sie in der Dropdown-Liste ausgewählt haben, sollte der Filter alle Ergebnisse dafür anzeigen.
In diesem Beispiel haben Sie eine Liste von Produktbestellungen und Sie haben gefiltert, um die Bestellungen anzuzeigen, bei denen das Produkt dem in der Dropdown-Liste ausgewählten Element (Kopfhörer) entspricht.
- Ändern Sie die Auswahl in der Dropdown-Liste, um eine andere Liste von Bestellungen anzuzeigen.
Hinweis: Um das Arbeitsblatt ästhetisch ansprechender zu gestalten und die Formeln in den Hilfsspalten zu schützen, blenden Sie diese aus. Klicken Sie mit der rechten Maustaste auf die Spalten und dann auf Ausblenden.
Drop-Down-Filter in Google Sheets erstellen
Der Drop-Down-Filter funktioniert in Google Sheets auf die gleiche Weise wie in Excel. Folgen Sie den oben beschriebenen Schritten, um die Dropdown-Liste und drei Hilfsspalten mit denselben Formeln wie in Excel zu erstellen.
Bei der Erstellung der Formeln für die endgültige Filtertabelle gibt es jedoch einen kleinen Unterschied:
=WENN($H3<>"";INDEX($B$3:$E$21;$H3;SPALTEN($L$3:L3));"")Erstellen Sie eine WENN-Anweisung, um zu prüfen, ob der Wert in der Hilfsspalte 3 vorhanden ist. Wenn der Wert vorhanden ist, können Sie die INDEX-Formel ausführen, aber wenn dieser nicht vorhanden ist, geben Sie ein Leerzeichen zurück.
