Excel関数は組み合わせて使う 関数のエラーを、IF関数とISERROR関数で非表示に

Excelで関数を使っていると「#VALUE!」「#DIV/0」などのエラーが出ることがあります。この表示自体はエラーを直すヒントになるのでありがたいのですが、見た目は良くありません。関数にエラーがあった時、セルに何も表示させない方法を考えます。

スポンサーリンク

VLOOKUP関数でよくあるエラー

次のような表を考えます。

 

スクリーンショット 2015 03 04 9 40 27

 
この表は、2015年シーズンのJリーグのクラブと監督をまとめたものです。

 
表の上には、

 

スクリーンショット 2015 03 04 9 42 24

 
このようなセルがあり、

 
A2セルにクラブの名前を入力すると、監督名が出てくるように、VLOOKUP関数を入力します。

 
VLOOKUP関数で入力するのは、

 
①探したいデータ

 
②調査するセルの範囲

 
③「①探したいデータ」が見つかったときに、データを表示する場所 
 =「①探したいデータ」が見つかったセルから、右にいくつ分進んだセルを表示させるか
 
④検索の型

 
この4つです。

 
先ほどの表で、VLOOKUP関数の動きを確認すると、
 

スクリーンショット 2015 03 04 10 05 16

 
①探したいデータを「FC東京」とすると、

 
「②調査するセルの範囲」である表の、一番左の列に、「FC東京」がないかを探します。

 
「FC東京」がみつかったら、③で指定した個数分に進んだセルを表示します。

 
監督名が入力されているのは、隣のセルなので”2””と指定しています。

 
すると、

 

スクリーンショット 2015 03 04 10 16 28

 
マッシモ・フィッカデンティ監督の名前が正しく表示されました。

 
関数の形でまとめると、

 
VLOOKUP(①A2,②$A$5:$B$23,③2,④0)

 

 
①探したいデータ:A2セル(”FC東京”と入力)
 
②調査するセルの範囲:表の範囲(A5セルからB23セルまで)
 
③「①探したいデータ」見つかった時、 
 そのセルから右にいくつ分進んだセルを表示させるか 
 =2
 
④検索の型:0(一致する場合以外はエラーにする)

 
このようになっています。

 
「①探したいデータ」が表の中にあれば上のように正しく表示されますが、該当するデータがないと、

 

スクリーンショット 2015 03 04 10 23 16

 
「#N/A」とエラーが出てしまいます。

 
このようなエラー表示は見た目が良くありません。 
特に、大きな表の中にエラー表示がたくさんあると見づらくなってしまいます。

 

スクリーンショット 2015 03 04 10 29 33

(これでは見づらいです)
 

そこで、このようなエラーを非表示にする方法を考えます。

 
IF関数とISERROR関数を使います。

 

IF関数とISERROR関数で関数のエラーを非表示に

まずは、IF関数です。

 

IF関数の仕組み

IF関数は、次の要素でできています。

 
①条件式
 
②「①条件式」が正しいときの表示内容
 
③「①条件式」が誤りのときの表示内容

 
この3つです。

 
「①条件式」は、関数や「+, ー, *, /, =」で作る式のことです。 
たとえば、「1+1=2」や「A1=B1」(A1のデータとB1のデータが同じという意味)などがそうです。

 
②の「『①条件式』が正しいときの表示内容」は、 
たとえば、条件式「1+1=2」は正しいですから、その場合の表示内容のことです。

 
「OK」と表示させたい場合は「OK」とします。

 
「OK」のように文字列(文字データ)を入力する時は、「” ”」で囲まなければいけないので注意が必要です。

 
③「①条件式」が誤りのときの表示内容は、 
たとえば、条件式「1+1=0」は誤っていますから、その場合の表示内容のことです。

 
「あうと〜」と表示させたい時は、「あうと〜」とします。

 
文字列を表示させるときに「” ”」で囲まなければいけないのは先ほどと同じです。

 
実際に、IF関数の式を表示させてみます。

 
=IF(1+1=0,”OK”,”あうと〜”)

 
と入力すると、

 

スクリーンショット 2015 03 04 10 48 27

 
「1+1=0」は誤っているので、誤ったときの表示内容である「あうと〜」が表示されました。

 
つまり、IF関数は、条件式を入力すると、その式が正しいかどうかを判定して、 
正しい場合と誤っている場合に分けて、表示内容を変えて表示してくれる関数であることがわかりました。

 

ISERROR関数

ISERROR関数は、非常に分かりやすい関数で、エラーがあるかどうかを判定してくれる関数です。

 
ISERROR関数の要素は何でもOK。 
とにかく”エラーがあるかどうか”を判定してくれます。

 
条件式を「1+”あうと〜”」として、ISERROR関数を入力します。

 
=ISERROR(1+”あうと〜”)

 
その結果は、

 

スクリーンショット 2015 03 04 11 01 40

 
「TRUE」と表示されました。

 
ISERROR関数の表示は独特で、慣れるまでは注意が必要です。

 
ISERROR関数では、

 
エラーがあると、「TRUE(=正しい)」 
エラーがないと、「FALSE(=誤っている)」

 
と表示されるのです。

 
つまり、上で判定した「1+”あうと〜”」について「TRUE」と判定されたのですから、「1+”あうと〜”」には、エラーがあると判定していることになります。

 
別のセルに「1+”あうと〜”」と入力してみると、

 

スクリーンショット 2015 03 04 11 07 30

 
「#VALUE!」とエラーが表示されます。

 

IF関数とISERROR関数を組み合わせる

2つの関数の特徴を理解していただけたと思うので、両方の関数を組み合わせることを考えます。

 
IF関数は、条件式が正しいかどうかを判定して、 
正しい場合の表示と、誤っている場合の表示を、分けて表示する 
という関数でした。

 
そして、ISERROR関数は、

 
エラーがあると「TRUE」=正しい、 
エラーがないと「FALSE」=誤り、

 
と判定されます。

 
ここで、IF関数の「①条件式」を、ISERROR関数にすることを考えます。 
上のISERROR関数の例を借りると、

 
=IF(①ISERROR(1+”あうと〜”)、

 
こうなります。

 
ここで、もう一度、ISERROR関数の表示について考えます。 
ISERROR関数は「エラーがあると「TRUE」=正しい」ですから、

 
IF関数の中に組み込むと、「①ISERROR(1+”あうと〜”)」は「正しい」と判定され、 
「②「①条件式」が正しいときの表示内容」が表示されることになります。

 
そして、エラーがあるときにセルに何も表示しないためには、 
「②「①条件式」が正しいときの表示内容」を空白を意味する、

 
「””」
 
にすればOK。

 
IF関数でまとめると、

 
=IF(①ISERROR(1+”あうと〜”),②””

 
こうなります。

 
次は、条件式であるISERROR関数にエラーがない場合です。

 
ISERROR関数ではエラーがないと、「FALSE(=誤り)」と判定されるので、IF関数の中に組み込んだ場合、「③「①条件式」が誤りのときの表示内容」が表示されることになります。

 
ISERROR関数でエラーがないことが分かれば、エラーのない式をそのまま表示させてしまえばいいのですから、 
ISERROR関数で判定した式や関数を「③「①条件式」が誤りのときの表示内容」にすればOKです。

 
IF関数でまとめると、

 
=IF(①ISERROR(1+”あうと〜”),②””,③1+”あうと〜”)

 
こうなります。

 
せるにエラーがある場合に、エラーを表示させない方法が分かりましたので、もう一度最初に戻ります。

 
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)

 
こうなります。

その結果は、
 

スクリーンショット 2015 03 04 11 58 28

 
左がVLOOKUP関数のみ、右がVLOOKUP関数、IF関数、ISERROR関数を組み合わせたものです。

 
右の方が見やすい表になっているのが分かると思います。

 

 

まとめ

エラーがあるセルを非表示にするには、IF関数とISERROR関数を組み合わせて使います。 
見やすい表にするための工夫として取り入れてみて下さい。
 
<おまけ>
新しいコーヒー豆が今日届く予定です。 
楽しみです。

タイトルとURLをコピーしました