Wie kann man zuvor verwendete Elemente in einer Dropdown-Liste ausblenden?
In Excel ist das Erstellen einer Standard-Dropdown-Liste eine gängige Dateneingabe-Technik. Was aber, wenn Sie möchten, dass die Dropdown-Liste jedes Mal kürzer wird, wenn Sie eine Auswahl treffen – indem zuvor ausgewählte Elemente entfernt werden, sodass jede Option nur einmal ausgewählt werden kann? Stellen Sie sich vor, Sie haben eine Dropdown-Liste mit 100 eindeutigen Namen: Nachdem Sie einen Namen ausgewählt haben, wird dieser aus den Dropdown-Auswahlmöglichkeiten entfernt und es bleiben 99 Optionen übrig. Während Sie weiterhin Auswahlen treffen, schrumpft die Liste automatisch, bis keine Optionen mehr übrig sind. Dieses Maß an Interaktivität kann in Szenarien wie der Aufgabenverteilung ohne Duplikate, Sitzplatzanordnungen oder Lotterien, wo jede Wahl einzigartig sein sollte, sehr praktisch sein. Excel bietet diese Funktionalität jedoch nicht direkt an, daher müssen Sie spezifische Workarounds verwenden. In den folgenden Abschnitten finden Sie Schritt-für-Schritt-Anweisungen, wie Sie dies erreichen können.
Zuvor verwendete Elemente in einer Dropdown-Liste mit Hilfsspalten ausblenden
Zuvor verwendete Elemente in einer Dropdown-Liste mit Hilfsspalten ausblenden
Angenommen, Sie haben eine Liste von Namen in Spalte A, wie im Screenshot unten gezeigt. Um eine Dropdown-Liste einzurichten, die zuvor verwendete Elemente ausblendet, gehen Sie wie beschrieben Schritt für Schritt vor. Dieser Ansatz verwendet zusätzliche Hilfsspalten, um nachzuverfolgen, welche Elemente bereits ausgewählt wurden und um die dynamische Dropdown-Quellliste aufzubauen. Obwohl diese Methode kompliziert erscheinen mag, ist sie dennoch unkompliziert und erfordert keine Programmierkenntnisse.
Anwendbare Szenarien umfassen Terminplanung, Ressourcenzuweisung oder alle Fälle, in denen ein Element nur einmal ausgewählt werden sollte, bis die Liste erschöpft ist. Der Vorteil liegt in der Klarheit durch sichtbare Formeln und nachvollziehbare Logik; jedoch erfordert sie die Pflege zusätzlicher Spalten in Ihrer Arbeitsmappe.
1. Neben Ihrer Namensliste geben Sie in Zelle B1 die folgende Formel ein, um zu überprüfen, ob ein Name bereits im Ziel-Dropdown-Bereich ausgewählt wurde:
=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())Diese Formel vergleicht jeden Namen mit den getroffenen Auswahlentscheidungen in der Dropdown-Liste (Bereich F1:F11). Wenn der Name bereits ausgewählt wurde, gibt sie eine leere Zelle zurück; andernfalls gibt sie die Zeilennummer als Hilfswert zurück. Passen Sie unbedingt den Bereich F1:F11 so an, dass er dem Ort entspricht, an dem Sie Ihre Dropdown-Listen platzieren möchten, und verweisen Sie bei A1 auf den Ort Ihrer Namensliste.
Hinweis: Überprüfen Sie doppelt, dass der Bereich 'F1:F11' alle Dropdown-Zellen umfasst. 'A1' sollte auf die aktuelle Zeile in Ihrer Namensliste verweisen.
2. Ziehen Sie den Ausfüllkästchen nach unten, um diese Formel auf alle Zeilen Ihrer Namensliste anzuwenden. Dadurch wird eine Reihe von Hilfsresultaten erstellt, die nicht verwendete Namen identifizieren.
3. In Spalte C richten Sie eine weitere Hilfsformel in Zelle C1 ein, um dynamisch eine saubere Liste nur der nicht verwendeten Namen zu erstellen:
=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1))))Diese Formel sammelt alle nicht verwendeten Namen, indem sie die entsprechenden Einträge aus Spalte A basierend auf den Hilfswerten in Spalte B extrahiert. Wenn Namen ausgewählt und aus B entfernt werden, aktualisiert sich diese Liste in Spalte C automatisch. Wenn Ihre Liste länger als 11 Namen ist, stellen Sie sicher, alle Bereiche entsprechend anzupassen.
4. Kopieren Sie diese Formel nach unten, um die Länge Ihrer ursprünglichen Namensliste zu entsprechen. Der Bereich, den Sie ausfüllen, sollte so lang sein wie Ihre Liste in Spalte A.
5. Um diese dynamisch aktualisierte Liste für Ihr Dropdown nutzbar zu machen, definieren Sie einen benannten Bereich. Wählen Sie die neu erstellte Liste in Spalte C aus (z.B. C1:C11), und klicken Sie dann auf Formeln > Namen definieren.
6. Geben Sie im Dialogfeld Neuer Name einen Namen ein (z.B. namecheck) und verwenden Sie diese dynamische Referenzformel, um sicherzustellen, dass der benannte Bereich korrekt dimensioniert bleibt, wenn Namen ausgewählt werden:
=OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1)Dies stellt sicher, dass nur nicht leere Werte in Spalte C für die Dropdown-Optionen verwendet werden. Überprüfen Sie sorgfältig Ihren Arbeitsblattnamen und Zellreferenzen – unter Verwendung exakter Adressformate – um mit Ihrem eigenen Arbeitsblatt übereinzustimmen.
Hinweis: Wenn Sie die Namensliste ändern, Zeilen hinzufügen oder entfernen oder ein anderes Arbeitsblatt verwenden, stellen Sie sicher, die Formel entsprechend zu aktualisieren, um Fehler zu vermeiden.
7. Um nun die eigentliche Dropdown-Liste zu erstellen, wählen Sie die Zellen aus, in denen Benutzer ihre Auswahl treffen sollen (z.B. F1:F11). Gehen Sie zu Daten > Datenüberprüfung > Datenüberprüfung.
8. Im Dialogfeld Datenüberprüfung wählen Sie unter dem Reiter Einstellungen die Option Liste und geben Sie =namecheck ins Quellenfeld ein, wobei Sie auf den dynamischen benannten Bereich verweisen, den Sie definiert haben.
Klicken Sie auf OK, um den Vorgang abzuschließen. Jedes Mal, wenn ein Name in der Dropdown-Liste ausgewählt wird, wird er aus der Liste für die anderen Dropdown-Menüs entfernt, wodurch sichergestellt wird, dass alle Auswahlmöglichkeiten einzigartig sind. Wenn Sie versuchen, denselben Namen in einer anderen Zelle auszuwählen, werden Sie feststellen, dass er nicht mehr als Option verfügbar ist.
Tipp: Löschen oder überschreiben Sie keine der Hilfsspalten (Spalten B und C), da sie für die richtige Aktualisierung der Dropdown-Liste unerlässlich sind. Sie können diese Spalten ausblenden, wenn Sie Ihr Arbeitsblatt ordentlich halten möchten, ohne die Funktionalität zu beeinträchtigen. Wenn Sie Probleme mit Listenaktualisierungen haben, überprüfen Sie Formeln auf Bereichsfehler oder stellen Sie sicher, dass alle Links zur Datenüberprüfung korrekt sind und auf den beabsichtigten benannten Bereich verweisen.
Eine Einschränkung dieses Ansatzes ist, dass bei gleichzeitiger Auswahl durch viele Benutzer (z.B. auf einem freigegebenen Arbeitsblatt) weiterhin Konflikte auftreten können. Für fortschrittlichere, skalierbare Lösungen oder um diese Aufgabe mit weniger sichtbarem Arbeitsblatt-Unordnung zu automatisieren, sollten Sie VBA in Betracht ziehen – der folgende Abschnitt demonstriert diese Alternative.
Verwandte Artikel:
Wie fügt man eine Dropdown-Liste in Excel ein?
Wie erstellt man eine Dropdown-Liste mit Bildern in Excel?
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 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...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins