どうやら、空白と空の文字列は Excel では異なるものです。セル内のデータ検証ドロップダウンに空の文字列を含むセル範囲を使用し、空白を無視するように指示しても、空の文字列は表示されます。
これを再現するには、=""セル A1 に入力してから=ISBLANK(A1)別のセルに入力すると、 が返されますFALSE。A1 から数式を削除すると、 が返されますTRUE。
機能があればいいですね=BLANK()。
を返すことも試みました=NA()が、ドロップダウン リストでもエラーが無視されないことがわかりました。
答え1
データ検証ドロップダウンは、この点では協力してくれません。ドロップダウンは、静的なリスト、または指定されたセルのみを参照しますが、そのセルの内容は考慮しません。
希望どおりに動作させることができないため、妥協しましょう。表示したい値のみを表示する連続した範囲を指定します (最後に、ほとんどの人が見ない空白を追加します)。
私の例では、データ検証リストの値 (および空白) を含むセルは A1:A15 です。別の列に移動して、次の数式を入力します。
=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")
これを入力する必要があります配列数式なので、数式を入力し終わったら、Ctrl + SHIFT + ENTER キーを押します。次に、15 行をコピー/貼り付けするか、下に入力します。結果は、A1:A15 の空白以外の値がすべて表示され、最後に空白がすべて表示されます。
ここで、データの入力規則ダイアログを開き、リストを数式を含むセル (A1:A15 ではない) に向けます。空白はまだ残っていますが、末尾にあるため、下にスクロールして何も見ない限り、誰も空白を見る必要はありません。
完全にクリーンではありませんが、動的であり、マクロを回避します。
それで、その式は何をしたのでしょうか?
=INDEX($A$1:$A$15,
値と空白を含む元の範囲を調べ、調べるセルを番号で指定できるようにします。
SMALL(
一連の数字を見て、最小のものから数えて1を返します。
IF($A$1:$A$15<>"",
範囲内の各セルが空白文字列以外の文字列と一致するかどうかを確認します
ROW($A$1:$A$15)
行番号を返す。
,999),
一致しない場合は 999 を返します。行数が 1000 を超える場合は、ここでより大きな数値が必要になりますが、データ検証の選択肢としてはこれで十分でしょう (うまくいけば)。SMALL 関数には行番号のリストが追加され、空白の場合は実際の行番号の代わりに 999 が返されます。
ROW(1:1)
は、この数式を入力したセルからカウントを開始する動的な方法です。数式を下にコピーすると、数値が大きくなります。これにより、数式を含む 1 行目に 1 番目に小さい数値、2 行目に 2 番目に小さい数値などが必要であることが示されます。
は行SMALL番号を に渡しINDEX、その行の値を取得します。これらの 999 はすべて最後に #REF エラーになりますが、 で自動的に空白に戻しますIFERROR。
この数式は理解しにくいかもしれません。問題がある場合は、新しいシートの A1:A15 にサンプル データを設定し、別の列のセルを選択して、数式バーに私の数式をそのままコピーしてみてください (CTRL+SHIFT+ENTER を忘れないでください)。少し操作してみると、コツがつかめるはずです。
幸運を！
答え2
このようなものは機能するでしょうか?
=IF(AND(NOT(ISFORMULA(J3)),J3=""),"Truly Empty","Blank via Formula")
注: 文字通りに実行するなどのカスタム関数が必要な場合は=blank(A1)、UDF を作成するために VBA が必要になります。上記の数式は、検索対象のセルに数式があり、かつ空白であるかどうかをチェックします。
答え3
私も同じ問題に遭遇しましたが、解決方法を見つけました。AjimOthy が共有した例に従って、A1:A15 の下にカウント数式を含めます (A16 がこのセルであるとします)。これは (A1:A15) より上のセルのみをカウントし、そこに数字を表示します。
。
次に間接的に
データ検証でソースの選択を求められた場合。間接を使用すると、カウントに基づいて流動的な変更が可能になり、count 関数では "" 空白がカウントされないため、リストに表示される空白も削除されます。