Offenbar sind Leerzeichen und leere Zeichenfolgen in Excel zwei verschiedene Dinge. Wenn ich einen Zellbereich mit leeren Zeichenfolgen für ein Dropdown-Menü zur Datenüberprüfung in der Zelle verwenden und Leerzeichen ignorieren möchte, werden trotzdem die leeren Zeichenfolgen angezeigt.
Sie können dies reproduzieren, indem Sie die Eingabe =""in Zelle A1 und dann =ISBLANK(A1)in eine andere Zelle vornehmen FALSE. Das Ergebnis ist . Löschen Sie die Formel aus A1, und das Ergebnis ist TRUE.
Wäre schön wenn es da eine =BLANK()Funktion gäbe.
Ich habe auch versucht, zurückzugeben =NA(), aber es stellte sich heraus, dass die Dropdown-Liste Fehler auch nicht ignoriert.
Antwort1
Ein Dropdown-Menü zur Datenüberprüfung wird Ihnen dabei nicht helfen. Es möchte nur eine statische Liste oder genau die Zellen betrachten, die Sie angeben, ohne darüber nachzudenken, was darin steht.
Da wir es nicht dazu bringen können, das zu tun, was wir wollen, gehen wir einen Kompromiss ein: Wir geben ihm einen kontinuierlichen Bereich, der nur die Werte zeigt, die wir sehen wollen (plus die Leerzeichen am Ende, wo die Leute meistens nicht hinschauen).
In meinem Beispiel sind die Zellen, die die Werte (und Leerzeichen) für meine Datenüberprüfungsliste enthalten, A1:A15. Ich gehe zu einer anderen Spalte und gebe die folgende Formel ein:
=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")
Ich muss dies eingeben alsArray-Formel, also drücke ich STRG + UMSCHALT + EINGABE, wenn ich mit dem Eintippen der Formel fertig bin. Als Nächstes kopiere/füge ich ein oder fülle 15 Zeilen nach unten aus. Das Ergebnis sind alle meine nicht leeren Werte von A1:A15 und dann alle meine leeren Werte am Ende.
Jetzt öffne ich das Dialogfeld „Datenüberprüfung“ und richte meine Liste auf die Zellen, die meine Formeln enthalten (nicht A1:A15). Die Leerzeichen sind immer noch da, aber am Ende, sodass sie niemand sehen muss, es sei denn, er scrollt nach unten und sieht nichts.
Es ist nicht ganz sauber, aber es ist dynamisch und vermeidet Makros.
Was also hat diese Formel bewirkt?
=INDEX($A$1:$A$15,
betrachtet den ursprünglichen Bereich, der Ihre Werte und Leerzeichen enthält, und ermöglicht Ihnen, anhand der Nummer anzugeben, welche Zelle Sie betrachten möchten.
SMALL(
schaut sich eine Reihe von Zahlen an, zählt von der kleinsten aufwärts und gibt eine zurück,
IF($A$1:$A$15<>"",
prüft, ob jede Zelle in Ihrem Bereich mit etwas anderem als einer leeren Zeichenfolge übereinstimmt
ROW($A$1:$A$15)
gibt die Zeilennummer zurück, wenn dies der Fall ist,
,999),
und gibt 999 zurück, wenn dies nicht der Fall ist. Wenn Sie mehr als 1000 Zeilen hätten, bräuchten Sie hier eine größere Zahl, aber dies ist (hoffentlich) mehr als genug für Ihre Datenüberprüfungsoptionen. Die SMALL-Funktion hat jetzt eine Liste von Zeilennummern, wobei 999 anstelle der tatsächlichen Zeilennummer für alle Leerzeichen steht.
ROW(1:1)
ist eine dynamische Möglichkeit, mit dem Zählen in der Zelle zu beginnen, in die Sie diese Formel eingeben. Wenn Sie die Formel nach unten kopieren, wächst die Zahl. Dies sagt dem Kleinen, dass Sie die 1. kleinste Zahl in der 1. Zeile mit der Formel haben möchten, die 2. kleinste in der 2. Zeile usw.
Das SMALLgibt die Zeilennummer an zurück INDEX, wodurch Sie den Wert für diese Zeile erhalten. Diese 999er werden am Ende alle zu #REF-Fehlern, aber mit dem verwandeln wir diese stillschweigend wieder in Leerzeichen IFERROR.
Diese Formel kann schwierig zu verstehen sein. Wenn Sie Probleme haben, versuchen Sie, Beispieldaten in A1:A15 auf einem neuen Blatt einzurichten, eine Zelle in einer anderen Spalte auszuwählen und meine Formel genau in die Formelleiste zu kopieren (vergessen Sie nicht STRG+UMSCHALT+EINGABE). Probieren Sie ein wenig damit aus, und Sie sollten den Dreh raushaben.
Viel Glück!
Antwort2
Wird so etwas funktionieren?
=IF(AND(NOT(ISFORMULA(J3)),J3=""),"Truly Empty","Blank via Formula")
Hinweis: Wenn Sie eine benutzerdefinierte Funktion möchten, z. B. „literally“ =blank(A1), benötigen Sie VBA, um eine UDF zu erstellen. Die obige Formel prüft, ob die Zelle, in der Sie suchen, eine Formel enthält und außerdem leer ist.
Antwort3
Ich bin auf dasselbe Problem gestoßen und habe eine Lösung gefunden. Folgen Sie dem Beispiel von AjimOthy und fügen Sie eine Zählformel unter A1:A15 ein (sagen wir, A16 ist diese Zelle). Es werden nur die Zellen über (A1:A15) gezählt und Ihnen wird dort eine Zahl angezeigt.
.
Ich verwende dann eine indirekte wie in
wenn Sie bei der Datenüberprüfung aufgefordert werden, die Quelle auszuwählen. Die Verwendung der indirekten Methode ermöglicht eine fließende Änderung basierend auf der Zählung und entfernt auch Leerzeichen, die in der Liste mit der Zählfunktion erscheinen, da die ""-Leerzeichen nicht gezählt werden.