Direkt zum Inhalt

Wie erstelle ich eine Dropdown-Liste, ignoriere leere Zellen in Excel?

Angenommen, Sie haben eine Liste mit Werten, in die mehrere leere Zellen eingetragen sind. Wenn Sie eine Dropdown-Liste zur Datenüberprüfung mit dieser Datenliste erstellen, werden die leeren Zellen zur Dropdown-Liste hinzugefügt, selbst wenn Sie die Option Ignorieren aktiviert haben Leere Option beim Erstellen. Tatsächlich gibt es keine direkte Methode, um eine Dropdown-Liste ohne die leeren Zellen zu erstellen. In diesem Tutorial bieten wir Ihnen einen Trick zum Trennen der Werte und leeren Zellen basierend auf den Originaldaten und zum Erstellen einer Dropdown-Liste für das Extrahieren von Daten.

Erstellen Sie eine Dropdown-Liste, ignorieren Sie leere Zellen in Excel


Erstellen Sie eine Dropdown-Liste, ignorieren Sie leere Zellen in Excel

Zum Beispiel haben Sie unten Daten in einem Bereich B2: B13, um eine Dropdown-Liste ohne leere Zellen zu erstellen, können Sie zuerst die Daten kopieren und in eine neue Spalte ohne Leerzeichen einfügen. Erstellen Sie anschließend eine Dropdown-Liste basierend auf dieser neuen Werteliste.

1. Wenden Sie die folgende Formel an, um nur die nicht leeren Zellenwerte zu kopieren und einzufügen. Geben Sie diese Formel ein: =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(B:B,SMALL(IF($B$1:$B$13<>"",ROW($B$1:$B$13)),ROWS($D$1:D1))))) in eine leere Zelle D1 und drücken Sie dann Ctrl + Shift + Enter Tasten zusammen, um das folgende Ergebnis zu erhalten:

Note: In der obigen Formel ist B1: B13 die Datenliste, die Sie verwenden möchten. Sie können die Zellreferenz nach Bedarf ändern.

2. Wählen Sie dann die Zelle D1 aus und ziehen Sie den Füllpunkt nach unten in den Bereich, in dem diese Formel enthalten sein soll. Alle nicht leeren Zellenwerte wurden extrahiert. Siehe Screenshot:

3. Erstellen Sie nun mit dieser neuen Datenliste eine Dropdown-Liste zur Datenüberprüfung. Wählen Sie die Zellen aus, in denen Sie die Dropdown-Liste suchen möchten, und klicken Sie dann auf Datum > Datenvalidierung.

4. In dem Datum Validierungsdialogfeld müssen Sie:

1). Gehen Sie zur Registerkarte Einstellungen und wählen Sie Liste der Erlauben Dropdown-Liste;
2). Wählen Sie den Zellbereich mit den Werten aus, die Sie oben in extrahiert haben Quelle Box;
3). Drücke den OK Taste. Siehe Screenshot:

5. Anschließend werden die Dropdown-Listen sofort ohne Leerzeichen erstellt.


In Verbindung stehende Artikel:

Beste Office-Produktivitätstools

Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate   |  Leere Zeilen löschen   |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren   |   Runde ohne Formel ...
Super-Lookup: VLookup mit mehreren Kriterien    VLookup mit mehreren Werten  |   VLookup über mehrere Blätter hinweg   |   Unscharfe Suche ....
Erweiterte Dropdown-Liste: Erstellen Sie schnell eine Dropdown-Liste   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl Dropdown-Liste ....
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Vergleichen Sie Bereiche und Spalten ...
Ausgewählte Funktionen: Rasterfokus   |  Designansicht   |   Große Formelleiste    Arbeitsmappen- und Blattmanager   |  Ressourcen (Autotext)   |  Datumsauswahl   |  Arbeitsblätter kombinieren   |  Zellen verschlüsseln/entschlüsseln    Senden Sie E-Mails nach Liste   |  Superfilter   |   Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen, ...)   |   50+ Chart Typen (Gantt-Diagramm, ...)   |   40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag, ...)   |   19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen, ...)   |   12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion, ...)   |   7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Zellen teilen, ...)   |   ... und mehr

Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen.  Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...

kte tab 201905


Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
Comments (7)
Rated 1 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
kalo setting validate nya tetep dari D1:D10 ya percuma pak.
kalau isiannya jadi 11 kan jadinya ada yg gak masuk list.
This comment was minimized by the moderator on the site
Hi Fadli,

Sorry, I don't quite understand your question. This trick helps to extract all the values from the list, excluding the blank ones. You need to make sure that all values are extracted and then create a dropdown list based on the extracted values.
This comment was minimized by the moderator on the site
Hilft leider nicht weiter, wenn man die Anzahl an Zeilen nicht kennt.
In meinem Fall habe ich eine Liste mit bis zu 40 Einträgen und aus einer der Spalten soll ein Drop-Down-Feld erstellt werden. Da ich aber nicht weiß wieviel Einträgen das sind muss ich immer noch, wenn sich die Liste ändert, das Drop-Down-Feld neu erzeugen und das für über 100 Listen jede Woche neu. Da hilft dann auch kein VBA, denn das kann zwar die Zeilen herausfinden, aber bei Änderungen (in mehr Einträge fehlen die neuen, in weniger Einträge sind am Ende wieder Leerzeilen) muss das VBA-Makro auch ständig wieder ausgeführt werden.
Rated 1 out of 5
This comment was minimized by the moderator on the site
The idea of having to create a second column seems a work around a defective option. The check box right beside the allow option says to ignore blanks. What is the real function of this check box if it does NOT ignore the blanks.
This comment was minimized by the moderator on the site
<p>The function works perfectly for me, but I still have one question. In my case I need to apply the formula for column range 2:2 instead of row range B:B.</p><p>Many thanks</p>
This comment was minimized by the moderator on the site
Suggestion: Just copy and paste with transpose (columns to rows) then press F5 (go to) select special and click on blanks. then delete the cells.
This comment was minimized by the moderator on the site
<p>The function works perfectly for me, but I still have one question.</p><p>how do i make this work?</p><p>Many thanks</p>
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations