Datenüberprüfung ohne leere Zwischenwerte
Schritt-für-Schritt-Anleitung
Um eine Datenüberprüfung in Excel zu erstellen, die leere Zellen ignoriert, folge diesen Schritten:
-
Hilfsspalte erstellen: Nutze eine Hilfsspalte (z.B. Spalte E), um die Werte aus deiner Ausgangsspalte (z.B. Spalte B) zu filtern. Verwende dazu die folgende Formel in Zelle E2:
=WENNFEHLER(INDEX($B$2:$B$10000;KKLEINSTE(WENN($B$2:$B$10000<>"";ZEILE($B$2:$B$10000)-ZEILE($B$2)+1);ZEILE(1)));"")
Drücke Strg+Shift+Enter, um die Formel als Matrixformel einzugeben.
-
Definierten Namen erstellen: Gehe zu "Formeln" > "Namens-Manager" und erstelle einen neuen Namen (z.B. "Liste"), der sich auf den Bereich in der Hilfsspalte bezieht:
=BEREICH.VERSCHIEBEN(Tabelle1!$E$2;0;0;ANZAHL2(Tabelle1!$E$2:$E$10000);1)
-
Dropdown erstellen: Wähle die Zelle aus, in der du das Dropdown-Menü haben möchtest (z.B. G2), und gehe zu "Daten" > "Datenüberprüfung". Wähle "Liste" als Zulassung und gebe als Quelle =Liste ein. Aktiviere die Option "Leere Zellen ignorieren".
-
Fertigstellen: Jetzt solltest du ein Dropdown haben, das nur die Werte anzeigt, die in der Hilfsspalte stehen, ohne leere Zellen zu berücksichtigen.
Häufige Fehler und Lösungen
-
Fehler: Dropdown ignoriert leere Zellen nicht: Überprüfe, ob die Datenüberprüfung richtig eingerichtet ist. Stelle sicher, dass die Option "Leere Zellen ignorieren" aktiviert ist.
-
Fehler: Formel gibt #Zahl! zurück: Dies kann passieren, wenn der Bereich in der INDEX-Anweisung nicht korrekt definiert ist. Stelle sicher, dass du die Zeilenanzahl und Bezug korrekt angegeben hast.
-
Fehler: Dropdown zeigt keine Werte an: Stelle sicher, dass der definierte Name korrekt auf die Hilfsspalte verweist und dass die Formel in E2 als Matrixformel eingegeben wurde.
Alternative Methoden
Eine andere Möglichkeit, die Datenüberprüfung ohne leere Zellen zu realisieren, ist die Verwendung von VBA. Hier ist ein einfaches Beispiel:
Sub DropdownOhneLeere()
Dim rng As Range
Set rng = Range("B2:B100") ' Der Bereich, der überprüft wird
Dim cell As Range
Dim values As String
For Each cell In rng
If cell.Value <> "" Then
values = values & cell.Value & ","
End If
Next cell
values = Left(values, Len(values) - 1) ' Letztes Komma entfernen
Range("G2").Validation.Delete
Range("G2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=values
End Sub
Praktische Beispiele
-
Beispiel 1: Setze die Formel in Spalte E, um nur Werte von Spalte B anzuzeigen, die nicht leer sind. Erstelle dann ein Dropdown in Zelle G2, das diese Werte anzeigt.
-
Beispiel 2: Wenn du eine Liste von Produkten hast, und einige davon nicht verfügbar sind (leere Zellen), kannst du mit obiger Methode nur die verfügbaren Produkte in deinem Dropdown anzeigen.
Tipps für Profis
-
Verwende die Funktion BEREICH.VERSCHIEBEN um dynamische Bereiche zu erstellen, die sich automatisch anpassen, wenn du Daten hinzufügst oder entfernst.
-
Achte darauf, dass du bei der Eingabe von Matrixformeln die Tasten Strg+Shift+Enter verwendest, um die korrekte Funktionalität sicherzustellen.
-
Du kannst auch die bedingte Formatierung nutzen, um leere Zellen visuell hervorzuheben, bevor du sie in deiner Datenüberprüfung verwendest.
FAQ: Häufige Fragen
1. Wie kann ich sicherstellen, dass meine Dropdown-Liste immer aktuell bleibt?
Wenn du die Hilfsspalte dynamisch definierst (z.B. mit BEREICH.VERSCHIEBEN), wird die Dropdown-Liste automatisch aktualisiert, wenn du Werte hinzufügst oder entfernst.
2. Was mache ich, wenn die Dropdown-Liste immer noch leere Zellen anzeigt?
Stelle sicher, dass du die Option "Leere Zellen ignorieren" in den Einstellungen der Datenüberprüfung aktiviert hast und überprüfe die Formel in der Hilfsspalte auf Richtigkeit.
3. Funktioniert diese Methode in allen Excel-Versionen?
Die beschriebenen Methoden funktionieren in Excel-Versionen ab 2007 und höher. Achte darauf, dass die verwendeten Funktionen in deiner Excel-Version verfügbar sind.