Excelで関数を使っていると「#VALUE!」「#DIV/0」などのエラーが出ることがあります。この表示自体はエラーを直すヒントになるのでありがたいのですが、見た目は良くありません。関数にエラーがあった時、セルに何も表示させない方法を考えます。
VLOOKUP関数でよくあるエラー
次のような表を考えます。
この表は、2015年シーズンのJリーグのクラブと監督をまとめたものです。
表の上には、
このようなセルがあり、
A2セルにクラブの名前を入力すると、監督名が出てくるように、VLOOKUP関数を入力します。
VLOOKUP関数で入力するのは、
①探したいデータ
②調査するセルの範囲
③「①探したいデータ」が見つかったときに、データを表示する場所
＝「①探したいデータ」が見つかったセルから、右にいくつ分進んだセルを表示させるか
④検索の型
この４つです。
先ほどの表で、VLOOKUP関数の動きを確認すると、
①探したいデータを「FC東京」とすると、
「②調査するセルの範囲」である表の、一番左の列を縦に、「FC東京」がないかを探します。
「FC東京」がみつかったら、③で指定した個数分右に進んだセルを表示します。
監督名が入力されているのは、隣のセルなので”２””と指定しています。
すると、
マッシモ・フィッカデンティ監督の名前が正しく表示されました。
関数の形でまとめると、
VLOOKUP(①A2,②$A$5:$B$23,③2,④0)
①探したいデータ：A2セル（”FC東京”と入力）
②調査するセルの範囲：表の範囲（A5セルからB23セルまで）
③「①探したいデータ」見つかった時、
そのセルから右にいくつ分進んだセルを表示させるか
＝２
④検索の型：０（一致する場合以外はエラーにする）
このようになっています。
「①探したいデータ」が表の中にあれば上のように正しく表示されますが、該当するデータがないと、
「#N/A」とエラーが出てしまいます。
このようなエラー表示は見た目が良くありません。
特に、大きな表の中にエラー表示がたくさんあると見づらくなってしまいます。
(これでは見づらいです)
そこで、このようなエラーを非表示にする方法を考えます。
IF関数とISERROR関数を使います。
IF関数とISERROR関数で関数のエラーを非表示に
まずは、IF関数です。
IF関数の仕組み
IF関数は、次の要素でできています。
①条件式
②「①条件式」が正しいときの表示内容
③「①条件式」が誤りのときの表示内容
この３つです。
「①条件式」は、関数や「＋, ー, ＊, /, ＝」で作る式のことです。
たとえば、「1+1=2」や「A1＝B1」（A1のデータとB1のデータが同じという意味）などがそうです。
②の「『①条件式』が正しいときの表示内容」は、
たとえば、条件式「１＋１＝２」は正しいですから、その場合の表示内容のことです。
「OK」と表示させたい場合は「”OK”」とします。
「OK」のように文字列（文字データ）を入力する時は、「” ”」で囲まなければいけないので注意が必要です。
③「①条件式」が誤りのときの表示内容は、
たとえば、条件式「１＋１＝0」は誤っていますから、その場合の表示内容のことです。
「あうと〜」と表示させたい時は、「”あうと〜”」とします。
文字列を表示させるときに「” ”」で囲まなければいけないのは先ほどと同じです。
実際に、IF関数の式を表示させてみます。
=IF(1+1=0,”OK”,”あうと〜”）
と入力すると、
「１＋１＝０」は誤っているので、誤ったときの表示内容である「あうと〜」が表示されました。
つまり、IF関数は、条件式を入力すると、その式が正しいかどうかを判定して、
正しい場合と誤っている場合に分けて、表示内容を変えて表示してくれる関数であることがわかりました。
ISERROR関数
ISERROR関数は、非常に分かりやすい関数で、エラーがあるかどうかを判定してくれる関数です。
ISERROR関数の要素は何でもOK。
とにかく”エラーがあるかどうか”を判定してくれます。
条件式を「１＋”あうと〜”」として、ISERROR関数を入力します。
＝ISERROR（１＋”あうと〜”）
その結果は、
「TRUE」と表示されました。
ISERROR関数の表示は独特で、慣れるまでは注意が必要です。
ISERROR関数では、
エラーがあると、「TRUE（＝正しい）」
エラーがないと、「FALSE（＝誤っている）」
と表示されるのです。
つまり、上で判定した「１＋”あうと〜”」について「TRUE」と判定されたのですから、「１＋”あうと〜”」には、エラーがあると判定していることになります。
別のセルに「１＋”あうと〜”」と入力してみると、
「#VALUE!」とエラーが表示されます。
IF関数とISERROR関数を組み合わせる
２つの関数の特徴を理解していただけたと思うので、両方の関数を組み合わせることを考えます。
IF関数は、条件式が正しいかどうかを判定して、
正しい場合の表示と、誤っている場合の表示を、分けて表示する
という関数でした。
そして、ISERROR関数は、
エラーがあると「TRUE」＝正しい、
エラーがないと「FALSE」＝誤り、
と判定されます。
ここで、IF関数の「①条件式」を、ISERROR関数にすることを考えます。
上のISERROR関数の例を借りると、
＝IF（①ISERROR（１＋”あうと〜”）、
こうなります。
ここで、もう一度、ISERROR関数の表示について考えます。
ISERROR関数は「エラーがあると「TRUE」＝正しい」ですから、
IF関数の中に組み込むと、「①ISERROR（１＋”あうと〜”）」は「正しい」と判定され、
「②「①条件式」が正しいときの表示内容」が表示されることになります。
そして、エラーがあるときにセルに何も表示しないためには、
「②「①条件式」が正しいときの表示内容」を空白を意味する、
「””」
にすればOK。
IF関数でまとめると、
＝IF（①ISERROR（１＋”あうと〜”）,②””
こうなります。
次は、条件式であるISERROR関数にエラーがない場合です。
ISERROR関数ではエラーがないと、「FALSE（＝誤り）」と判定されるので、IF関数の中に組み込んだ場合、「③「①条件式」が誤りのときの表示内容」が表示されることになります。
ISERROR関数でエラーがないことが分かれば、エラーのない式をそのまま表示させてしまえばいいのですから、
ISERROR関数で判定した式や関数を「③「①条件式」が誤りのときの表示内容」にすればOKです。
IF関数でまとめると、
＝IF（①ISERROR（１＋”あうと〜”）,②””,③１＋”あうと〜”)
こうなります。
せるにエラーがある場合に、エラーを表示させない方法が分かりましたので、もう一度最初に戻ります。
VLOOKUP関数で「#N/A」のエラーを非表示にするのが目的でした。
方針は、VLOOKUP関数にエラーがあるかどうかをISERROR関数で判定。
エラーがある場合は空白にしたいので、IF関数の「②「①条件式」が正しいときの表示内容」は「””」。
エラーがない場合は、VLOOKUP関数の結果をそのまま表示させたいので、IF関数の「③「①条件式」が誤りのときの表示内容」は、もとのVLOOKUP関数
「VLOOKUP(A2,$A$5:$B$23,2,0)」
にします。
少し長いですが、まとめると、
=IF(①ISERROR(VLOOKUP(A2,$A$5:$B$23,2,0)), ②””, ③VLOOKUP(A2,$A$5:$B$23,2,0)
こうなります。
その結果は、
左がVLOOKUP関数のみ、右がVLOOKUP関数、IF関数、ISERROR関数を組み合わせたものです。
右の方が見やすい表になっているのが分かると思います。
まとめ
エラーがあるセルを非表示にするには、IF関数とISERROR関数を組み合わせて使います。
見やすい表にするための工夫として取り入れてみて下さい。
＜おまけ＞
新しいコーヒー豆が今日届く予定です。
楽しみです。