質問: エクセル 関数で空白を入れたセルと空白セルは違う? エクセル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に空白が返されているのなら、それを空白として扱ってもらうようにするにはどうすれば良いですか?
回答: この循環参照の問題を回避するには、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]ボタンをクリック