概要
Excelの空白セルの判定でハマッたが、般若心経をヒントに悟りに至る。
要件
とある管理表にて、枝番をつける・つけないを特定のセルの数値から判定する関数を作る。
- 数値セル(B列)が空白、半角ハイフン、0、1だったら、表示セル(C列)に「経典」と表示する。
- 数値セルが2以上だったら、「経典_2」のように枝番をつける。
- 数値セルには、他のExcelファイルからデータをコピーして値貼り付けする。
要件から、表示セルに以下の関数を記入。
="経典"&IF(B3="-","",IF(B3>=2,"_"&B3,""))
数値セルが空白の場合、「2以上」の判定はFALSEになるため、表示セルは「経典」になる想定。
処理の流れ（Before）
対象が半角ハイフンか？ YES→ 経典
NO
↓
対象が2以上か？ YES→ 経典_数字
NO （空白セルは、2以上ではないのでNOになる）
↓
経典
事象
数値セルが空白なのに、「経典_」と表示されてしまう。
原因（検証は後述）
数値セルに貼り付けるコピー元のセル(X1セルとする)が、さらに他のセル(Y1セルとする)を参照しており、=Y1&""となっていたため。
元ファイルにおいて、Y1セルが空白(未入力)のとき、それを参照するX1セルも空白にしたいが、=Y1だと0が表示されて美しくないので、&""をつけて、空白表示にしていると思われる。
結論から言うと、0表示回避のための&""が原因だった。
&""によって作られた空白セルは、 「空白であり、空白でない」と判定される。
&""セルをコピー・値貼り付けして作った直後の空白セルも、「空白であり、空白でない」状態になる。こちらは、F2→Enterすれば「空白である」の状態になる。
検証方法
空白を値貼り付けした直後の状態がどう扱われているかを検証。空白の調査のための関数は、Office公式サポートページからコピペした。
Officeサポート：IFを使用してセルが空白かどうかを確認する
https://support.office.com/ja-jp/article/if-%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%82%BB%E3%83%AB%E3%81%8C%E7%A9%BA%E7%99%BD%E3%81%8B%E3%81%A9%E3%81%86%E3%81%8B%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B-dff4eda1-6187-4b83-b7f6-4c3c0a1e2188
また、空白でないと判定された場合は、2以上かを判定している。ここで2以上と判定されると/_2のように枝番が付加されるので、これも検証。
検証結果
C8、C9、C12にて、ふたつの検証関数で差異があり、「空白であり、空白でない」という状態に。般若心経でよく知られた、色即是空 空即是色そのものである。
また、これら3つと、半角ハイフンが「2以上」と判定された。
対応
まずは、現世の不条理「空白であり、空白でない」を受け容れるべし。きっとExcelの仕様。
さて、元ファイルは自分の管理外のファイルなので、編集できない。自分の管理しているファイルの中で解決策を考える必要がある。
検証したとおり、当初使っていた「2以上であるか」の判定、Officeサポートに記載されている「ISBLANK関数がTRUEか」 の判定では、&""による空白を正しく判定できない。空白であるかの判定としては「対象が=""か」が適切であり、これを明示的に判定する必要がある。
そこで、ひとつめのIF関数に=""かどうかの判定を追加した。IF関数のネストだとゴチャつくので、最近はOR関数推し。
Before：="経典"&IF(B3="-","",IF(B3>=2,"_"&B3,""))
↓
After：="経典"&IF(OR(B3="",B3="-"),"",IF(B3>=2,"_"&B3,""))
これで想定通りの処理になることを確認。
処理の流れ（After）
対象が空白（""）あるいは半角ハイフンか？ YES→ 経典
NO
↓
対象が2以上か？ YES→ 経典_数字
NO
↓
経典
教訓
-
&""による空白セルは不条理を生み出すと知り、扱いに注意する。空白として確実に処理するには、明示的に判定する必要がある。 - たとえOffice公式サポートの記載であっても、検証は必要。
- Qiitaには「仏教」タグがある。
余談
元ファイルを修正できるなら、参照先のセルが空白の場合はハイフンを表示させるのもアリかも。
X1：=IF(Y1="","-",Y1)