エクセル 関数で空白を入れたセルと空白セルは違う？

質問

エクセル2010を使っている者です。

「データ」というシートのAL5というセルに
IF(ISERROR(INDEX(AL1:AL500,$A$5)),"",INDEX(AL1:AL500,$A$5))
と関数を入れており、その結果、空白が戻り値となり、見た目上、AL5は空白となっています。

しかし、別のシートのあるセル（A1とします）に
=IF('データ'!AL5="","",DATE(YEAR('データ '!AL5)-1,MONTH('データ '!AL5),DAY('データ '!AL5)))
と関数を入力すると、AL5は空白として扱われていないようで、そのセル（A1)は空白にならずに「H1810.12.31」と表示されてしまいます。

AL5に空白が返されているのなら、それを空白として扱ってもらうようにするにはどうすれば良いですか？

回答No.1

MarcoRossiItaly · ベストアンサー

まず質問文にある 1 本目の数式は、Excel 2007 以後に追加された IFERROR 関数を使って、もっと簡潔に、次式のとおり書いても同じ結果になります。ご存じなかった場合は、今後の参考にしてください。

=iferror(index(al1:al500,$A$5),"")

AL5 セルに上式を記入し、かつ A5 セルに「5」と記入するならば、どちらの関数であれ、循環参照となります。

どちらの関数であれ、AL5 がいったん空文字列を返した後、循環参照を発生させる「5」という値を A5 に記入すると、Excel の仕様の限界により、AL5 では「0」が新たに算出されたにも関わらずその表示は変化していないという現象が起こる可能性があります。

その場合は、AL5 をダブルクリックしてください。空文字列から別の値に表示が切り替わります。別の値というのは恐らく、「693962」か、この値をシリアル値とする日付です。なぜなら、その場合に 2 本目の数式の計算結果が「H1810.12.31」となるからです。

2 本目の数式は、「データ 」→「データ」というふうに半角スペースを削除すれば、正しく機能すると思います。

ということは、上の循環参照が不具合の原因ではなかった場合において、1 本目の数式によって空文字列を返しているように見える AL5 は、実は空文字列ではなく、数値のゼロなどを非表示するセルの書式またはオプションが適用されている可能性を確認する必要がありそう。

●ゼロを非表示にするセルの書式の例
　「G/標準;-G/標準;」、「0;-0;」、「#,##0;-#,##0;」、「;;」等々

●エラー値を除くあらゆる値を非表示にするセルの書式
　「;;;」

●ゼロを非表示にするオプション（シートごとに設定）
　「Excel のオプション > 詳細設定 > 次のシートで作業するときの表示設定 > ゼロ値のセルにゼロを表示する」のチェックなし

＞AL5に空白が返されているのなら、それを空白として扱ってもらうようにするにはどうすれば良いですか？

「データ!al5=""」というふうに空文字列を指定する記述のままで問題ありません。なお「'データ'!al5=""」と書いても特にエラーにはなりませんが、今回の場合、「'」は不要です。そのため数式の確定時に、Excel によって自動的に削除されます。

また、真の空白セルを指定したい場合は、同じく「データ!al5=""」と記述するか、「データ!al5=0」と書いてもいいことになっています。空白セル以外に、前者には空文字列のセル、後者には「0」のセルも該当することは、言うまでもありません。ほとんど出番はないと思いますが一応、「and(データ!al5={"",0})」と書けば、空白セルのみ該当となります。

お礼

遅くなりましたが、ありがとうございます。
解決しました。

回答No.2

kagakusuki

この循環参照の問題を回避するには、A5セルに5が入力された場合には、AL5セルにおいて

INDEX(AL1:AL500,$A$5)

の参照を行わない様にすれば良い訳です。
　その様な関数は次の様なものとなります。

=IF($A$5=ROWS(AL1:AL5),"",IF(ISERROR(1/(INDEX(AL1:AL500,$A$5)<>"")),"",INDEX(AL1:AL500,$A$5)))

　A5セルの値が5である場合には、

$A$5=ROWS(AL1:AL5)

の部分の判定式がTRUEとなりますから、1つ目のIF関数によって表示は空欄となるだけで、INDEX(AL1:AL500,$A$5)の参照は行われない様になっています。
　因みに、

ISERROR(INDEX(AL1:AL500,$A$5))

ではなく

ISERROR(1/(INDEX(AL1:AL500,$A$5)<>""))

となっているのは、参照先のセルが空欄の場合において、関数の戻り値を0ではなく空欄とする事で、「0値を表示しない」という設定にする事無く、空欄を参照した際に0が表示される事を防ぐ事により、もしも、参照先が空欄ではなく数値の0が入力されていた場合に、0を表示させる事が出来る様にするためです。
　因みに「0値を表示しない」という設定から「0値を表示する」という設定に切り替えるには、次の様な操作を行います。

[ファイル]タブをクリック
　　↓
現れた[オプション]アイコンをクリック
　　↓
現れた「Excelのオプション」ダイアログボックスの左側の囲みの中にある[詳細設定]をクリック
　　↓
現れた「次のシートで作業するときの表示設定」欄の中にある[ゼロ値のセルにゼロを表示する]と記された箇所をクリックして、チェックを入れる
　　↓
「Excelのオプション」ダイアログボックスの[OK]ボタンをクリック

　後それから、INDEX関数内で指定されているセル範囲が

AL1:AL500

という具合に行と列の両方ともに相対参照の形式となっていますが、これは

AL$1:AL$500

の間違いではないでしょうか？

　又、別の原因となり得る事には、計算方法のモードが自動ではなく手動となっているために、別のシートA1セルの表示が「H1810.12.31」となった後で、

INDEX(AL1:AL500,$A$5)

の計算がエラーとなる様な値がA5セルに入力され、その上でAL5セルに対して関数の編集等が行われますと、AL5セルの表示が空欄となるにもかかわらず、計算が自動的には行われないため、別のシートA1セルの表示は変化せず、「H1810.12.31」のままとなっているという事も考えられます。
　計算モードを自動にするには、次の様な操作を行って下さい。

[ファイル]タブをクリック
　　↓
現れた[オプション]アイコンをクリック
　　↓
現れた「Excelのオプション」ダイアログボックスの左側の囲みの中にある[数式]をクリック
　　↓
現れた「計算方法の設定」欄の中にある[自動]と記された箇所をクリックして、チェックを入れる
　　↓
「Excelのオプション」ダイアログボックスの[OK]ボタンをクリック

お礼

ありがとうございます。
解決しました。