エクセル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ベストアンサー
- 回答日時:
まず質問文にある 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
- 回答日時:
<br /> この循環参照の問題を回避するには、A5セルに5が入力された場合には、AL5セルにおいて<br /> <br /
500,$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]ボタンをクリック
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelについて 4 2023/03/12 17:43
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Excel(エクセル) エクセル表作成について 5 2023/03/12 13:25
- Excel(エクセル) WORKDAY関数 4 2023/06/08 13:23
- Visual Basic(VBA) 【VBA】Excelで罫線を引きたい 3 2022/07/14 12:04
- Excel(エクセル) エクセルの昇順での数値入力のミスの見つけ方を教えてください。 4 2022/06/26 20:41
- Excel(エクセル) Excel 特定セルの数値を参照したセルの0表示が空白にならないのはどうしてか? 3 2022/04/28 22:23
- Excel(エクセル) エクセルの関数式を教えてください。 2 2022/11/29 21:09
- Excel(エクセル) 列の複数ある空白セルを飛ばして、セルに並べて表示したい 3 2023/02/12 16:49
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelのセルを飛ばして入力する
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excel 2019 のピボットテーブル...
-
エクセルの行の抽出について質...
-
【マクロ】エクセルにかいてあ...
-
スプレッドシート クエリ関数 1...
-
エクセルでセルに「氏名を入力...
-
MOS365 Excel Expert / Excel R...
-
excelの不要な行の削除ができな...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelで全角を半角にしたいので...
-
Excel初心者です。 詳しい方、...
-
エクセルの数式で教えてください。
-
4つのパターンを表示するEXACT...
-
スマートな関数を教えて下さい。
-
【Excel】セル内の時間帯が特定...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報