空欄なら「要確認」にしたい。ただし、B列が文字列の行ではエラーにしたくない。そう考えて、次の数式を組んだとします。
=IF(OR(A2="",B2*2>10),"要確認","OK")A2が空欄なら、ORの1つ目はTRUEです。2つ目を見なくても結果は決まるように思えます。ところがB2が文字列の行では、この数式は #VALUE! を返します。ExcelのORは、1つ目で結果が決まっても短絡評価されません。
この記事では、ORとANDにエラーが混ざるとどうなるかと、IFERRORや入れ子のIFで避ける書き方を、Excelで実測した結果で紹介します。
想定読者
- ORやANDの中に、エラーになりうる式を入れている人
- 1つ目の条件で結果が決まるのに、なぜエラーになるのか分からない人
OR関数の構文は、Microsoft SupportのOR関数のページで確認できます。
OR・ANDは短絡評価されない：エラーを引き継ぐ
「短絡評価」とは、1つ目の条件で結果が決まったときに、残りを評価しない仕組みです。実測では次の結果でした。
- IFは、選ばれなかった側の式にエラーがあっても、結果に影響しない
- OR・ANDは、1つ目で結果が決まる場合でも、他の引数にエラーがあれば結果もエラーになる
エラーが前の引数にあっても、後ろの引数にあっても同じです。検証環境はExcel 16.0（Windows、日本語環境）です。他のバージョンやMac版は未検証です。
IF・OR・ANDにエラーを混ぜた実測
1つの式の中にエラーを入れたときの結果です。
| 数式 | 結果 |
|---|---|
=IF(TRUE,"OK",1/0) | OK |
=IF(FALSE,1/0,"OK") | OK |
=IF(1/0>0,"a","b") | #DIV/0! |
=OR(TRUE,1/0) | #DIV/0! |
=OR(1/0,TRUE) | #DIV/0! |
=AND(FALSE,1/0) | #DIV/0! |
=AND(1/0,FALSE) | #DIV/0! |
=OR(TRUE,NA()) | #N/A |
=AND(FALSE,NA()) | #N/A |
IFでも、条件式そのものがエラーなら結果はエラーです（3行目）。
ここで確認できたのは、エラーが結果に伝わるかどうかです。内部で全部の引数を計算しているかどうかまでは、直接は見ていません。
対処1：IFERRORで守る
エラーになりうる項だけを、IFERRORで包みます。
=IF(OR(A2="",IFERROR(B2*2>10,TRUE)),"要確認","OK")IFERRORの第2引数は、エラーのときに使う値です。TRUEにするとエラー行は「要確認」、FALSEにするとエラー行は「OK」になります。
対処2：IFを入れ子にして避ける
ORをやめて、IFを入れ子にする書き方もあります。
=IF(A2="","要確認",IF(B2*2>10,"要確認","OK"))A2が空欄なら、IFはB2の式を使いません。そのため、B2が文字列でもエラーになりません。
ただし、A2が空欄でない行でB2がエラーなら、そのままエラーです。守るなら、IFERRORも併用します。
=IF(A2="","要確認",IF(IFERROR(B2*2>10,TRUE),"要確認","OK"))5つの書き方を実測で比較する
A列とB列の組み合わせを4通り用意し、5つの書き方で計20通りを実測しました。
| A2／B2 | ORのまま | 入れ子IF | OR＋IFERROR（TRUE） | OR＋IFERROR（FALSE） | 入れ子IF＋IFERROR（TRUE） |
|---|---|---|---|---|---|
| 空欄／"abc" | #VALUE! | 要確認 | 要確認 | 要確認 | 要確認 |
| "x"／"abc" | #VALUE! | #VALUE! | 要確認 | OK | 要確認 |
| "x"／3 | OK | OK | OK | OK | OK |
| "x"／6 | 要確認 | 要確認 | 要確認 | 要確認 | 要確認 |
エラーが出ない行（3・4行目）は、どの書き方でも同じ結果です。違いが出るのは、B2が文字列の行です。
- A2が空欄の行は、入れ子IFならエラーを避けられる
- A2が入力済みの行は、入れ子IFだけではエラーが残る
- IFERRORの第2引数がTRUEかFALSEかで、エラー行の結果が変わる
よくある失敗
1. 入れ子IFだけで安心する
表の2行目のとおり、A2が入力済みでB2がエラーなら、入れ子IFでもエラーです。空欄の行しか試していないと、気づきにくい点です。
2. IFERRORの第2引数を機械的にFALSEにする
表の2行目で、OR＋IFERROR（FALSE）はエラー行を「OK」にしました。エラー行を「要確認」にしたいのか「該当なし」にしたいのかを決めてから、第2引数を選びます。
3. ANDでも同じことが起きる
ANDにも同じ考え方が当てはまります。
=IF(AND(A2<>"",IFERROR(B2*2>10,FALSE)),"要確認","OK")A2が空欄でB2が文字列の行は、ANDのままだと #VALUE! です。IFERRORで守ると「OK」になりました。A2が入力済みでB2が文字列の行でも、同じ式で「OK」でした。
4. IFERRORを広く使いすぎる
#VALUE!・#DIV/0!・#N/A は、どれもIFERRORの代替値になりました。他のエラー値は未検証です。想定外のエラーまで隠れていないか、注意が必要です。
IFERRORは、#REF! や #VALUE! も同じ代替値に置き換えます。エラー値ごとの最初の確認ポイントと、#N/A だけを置き換えるIFNAとの違いは、【Excel関数】#N/A・#VALUE!・#REF!が出たら最初に確認にまとめています。
応用例：IFERRORで値を差し替えたあとの集計
IFERRORで値を差し替えると、その行を後続の集計が数えるかどうかも変わります。AVERAGEでの例は、【Excel関数】率の平均はAVERAGEでなくSUM/SUMで実測しました。
IFERRORで守る形は、【Excel】手入力と自動計算を共存させる上書き優先列の設計のキー引きにも使っています。
IFERRORで包んだ条件をANDに渡して、不整合を検知するフラグ列にした例は、【Excel】打刻と集計値の不整合を検知するフラグ列の設計で紹介しています。
まとめ
- IFは、選ばれなかった側のエラーを無視する
- OR・ANDは、他の引数にエラーがあると結果もエラーになる
- 守る方法は、IFERRORで包むか、IFを入れ子にするか。入れ子IFだけでは、後ろの引数のエラーは防げない
- IFERRORの第2引数は、エラー行を「要確認」にするか「該当なし」にするかで決める
コメント