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関数を組み合わせて使います。 
見やすい表にするための工夫として取り入れてみて下さい。

<おまけ>
新しいコーヒー豆が今日届く予定です。 
楽しみです。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする