- Home »
- エクセル関数の技 »
- VLOOKUP関数のエラー値#N/Aを表示しない
- VLOOKUP関数で表示されるエラー値#N/Aを表示しないようにする方法をいくつか説明します。
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
- このページではVLOOKUP関数で表示されるエラー値 #N/A を表示しない方法を例として説明します。
VLOOKUP関数以外でも、MATCH関数で表示されるエラー値 #N/A を表示しないケースでも同様に使用できます。
- また、ここで使用する関数は#N/Aだけではなく、他のエラー値に対しても有効になるものもありますので、それぞれの関数の説明に注意してください。
- エラー値の説明は Excel2010(エクセル2010)基本講座:エラー値とその対策 を参照してください。
- セルにエラー値を表示しないようにする方法はいろいろありますが、ここでは数式でエラーを表示しない方法について書いています。
- IFERROR関数はExcel2007で追加された関数です。
構文は =IFERROR(数式,エラーの場合の値) となります。
- 評価されるエラーの種類には、#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! があります。
これらのエラーに対して検出ができますので、Excel2003以前と比較して簡素な数式にすることができます。
- (例1)VLOOKUP関数のエラー値に対処する
- 商品の単価を別表から探して計算しています。
下図の例では商品名が未入力のため #N/A エラーが表示されています。
- C2セルの数式を =IFERROR(VLOOKUP(A2,$G2ドル:$H5,2,ドルFALSE)*B2,"") とIFERROR関数を使って、#N/A
を非表示にしています。
なお、下図のデータはC2セルの数式をC5セルまでフィルハンドルを使ってコピーしています。
- (例2)この例はIFERROR関数で#N/A 以外の他のエラー値にも対処できるという例です。
- 下表で単価を=C3/D3で求める場合、販売数が0の時(E列)はエラー #DIV/0! となります。
エラーが表示されないようにしなさい。なお、エラーの時は数値の 0 (ゼロ)を表示することとします。
- IFERROR関数を使って処理することができます。
下図は =IFERROR(C3/D3,0) とした例です。
- 下図は =IFERROR(C3/D3,"") として、エラーのときは空白"" にする例です。
- ちなみに、Excel2003以前では、IF関数とISERROR関数を利用して計算できます。
ISERROR関数でエラーか否かを判定し、IF関数で処理を分けます。
E2セル:=IF(ISERROR(C3/D3),0,C3/D3)
(追記) (追記ここまで)
- IFNA関数はExcel2013で追加された関数です。
構文は =IFNA(数式,#NAエラーの時返す値) となっています。
- 数式の結果が #N/A エラーの場合は指定した値を返し、それ以外の場合は数式の結果を返します。
- 【例題】
- C列にはG2:H5の単価表から単価を表引きして数量とを掛け合わせる数式が入っています。
A列の商品名が入力されていないときには#NAエラーとなり、合計=SUM(C2:C4)が計算されません。
C2:C4の数式を修正して、エラーが表示さないようにしなさい。
- 【解答例】
- C2セルの数式を =IFNA(B2*VLOOKUP(A2,$G2ドル:$H5,2,ドルFALSE),"") として、C4までコピーします。
- ちなみに、Excel2013より前のバージョンではIFNA関数は使えないので、IF関数とISNA関数を組み合わせて使うこともできます。
=IF(ISNA(VLOOKUP(A2,$G2ドル:$H5,2,ドルFALSE)),"",B2*VLOOKUP(A2,$G2ドル:$H5,2,ドルFALSE))
- また、Excel2007、Excel2010であればIFERROR関数を使うこともできます。
=IFERROR(B2*VLOOKUP(A2,$G2ドル:$H5,2,ドルFALSE),"")
- 対象がエラー値か否かを調べることができる関数には以下のものがあります。
- ISERR:テストの対象が #N/A を除くエラー値を参照するとき TRUE を返します。
- ISERROR:テストの対象が任意のエラー値 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! のいずれか) を参照するとき TRUE を返します。
- ISNA:テストの対象がエラー値 #N/A (使用する値がない) を参照するとき TRUE を返します。
- 使用例1
- C1セルに=A1/B1と入力し、C3セルまでコピーしています。
B1セルが0や文字列の場合にはエラーとなります。
- このケースでは、#VALUE!、#DIV/0!の2種類が考えられるので、ISERROR関数を使います。
C1セルに=IF(ISERROR(A1/B1),"",A1/B1) と入力し、C3セルまでコピーします。
- 使用例2 :VLOOKUP関数やMATCH関数
- VLOOKUP関数やMATCH関数で検索する値が無い場合には、#N/Aエラーとなります。
B1セルに=VLOOKUP(A1,$D1ドル:$E7,2,ドルFALSE) と入力し、B3セルまでコピーした例です。
- このケースでは#N/Aエラーのみに対処すればよいので、ISNA関数を使います。
もちろん、ISERROR関数を使っても同じ結果が得られます。
B1セルに=IF(ISNA(VLOOKUP(A1,$D1ドル:$E7,2,ドルFALSE)),"",VLOOKUP(A1,$D1ドル:$E7,2,ドルFALSE)) と入力し、B3セルまでコピーしています。
- 上記の使用例2ではA1セルの値が検索範囲に無いときにエラーとなるので、COUNTIF関数を利用することもできます。
つまり、参照する範囲に捜したい値が有るのか/無いのかをCOUNTIF関数を使って調べます。
B1セルに=IF(COUNTIF($D1ドル:$D7,ドルA1)=0,"",VLOOKUP(A1,$D1ドル:$E7,2,ドルFALSE))と入力し、B3セルまでコピーしています。
- なお、Excelのワークシートでは0をFALSEとみなし、0以外をTRUEと見なすので、
=IF(COUNTIF($D1ドル:$D7,ドルA1),VLOOKUP(A1,$D1ドル:$E7,2,ドルFALSE),"") と書くこともできます。
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
よねさんのWordとExcelの小部屋 »
エクセル関数の技 »
VLOOKUP関数のエラー値#N/Aを表示しない
PageViewCounter
Counter
Since2006/2/27