Excel(エクセル)でVLOOKUP関数などの関数を使っていると、関数の結果がエラー値「#N/A」で返されることがあります。
「#N/A」は、該当なしなどを意味する “Not Applicable”、または、無効、入手不能などを意味する “Not Available” を意味する略号です。
今回の記事では、見栄えが気になるこの「#N/A」を非表示にしたり、別の値に変換する５つの方法を解説します。
「#N/A」を非表示/別の値に変換する方法
- IFERROR関数を使う
- IFNA関数を使う
- IF関数を使う
- ISNA関数と条件付き書式を使う
- 印刷画面で設定する
おすすめ度が高い順から各見出しで手順を紹介しますので、ぜひ参考にしてみてください。
エラー値「#N/A」が発生する原因
「#N/A」が表示されるもっとも多い原因は、関数で使用している参照値や参照セルが見つからないことです。
そのため、検索値を指定するVLOOKUP関数、HLOOKUP関数、XLOOKUP関数、MATCH関数などで発生するケースが多くなります。
つまり、検索範囲から見つからず、エラー値で表示するしか選択がなかった、というエクセルの自動判定だと考えてください。
「#N/A」は、下のVLOOKUP関数の使用例のように、１つ目の引数「検索値」に指定した”19”が２つ目の引数「検索範囲」から見つからない、などの場合に表示されます。
VLOOKUP関数のエラー発生原因を詳しく紹介した記事は、以下のリンク記事を参考にしてください。
IFERROR関数で「#N/A」を非表示/変換
１つ目の「#N/A」の解決方法は、IFERROR関数(イフ・エラー)を利用する方法です。
IFERROR関数の構文は以下のとおりで、２つの引数を設定します。
IFERROR(値, エラーの場合の変換値)
IFERROR関数は、『値』がエラー値「#N/A」「#DIV/0!」「#NULL!」「#NUM!」「#VALUE!」「#NAME?」「#REF!」いずれかであればエラーの場合の変換値を返し、そうでなければ『値』をそのまま返します。
それでは実際にIFERROR関数を使って、エラー値を回避する方法を紹介します。
使い方は簡単です。エラーを回避したい関数全体をIFERROR関数を被せるように、下の赤文字部分を追加してください。
=IFERROR(VLOOKUP(E4,$B$4:$C$15,2,FALSE),"")
IFERROR関数の２つ目の引数を””と設定することで、エラー値「#N/A」は「(空白)」で表示されます。
もちろん、セルの値がエラーでない場合は、元のVLOOKUP関数の結果が表示されます。また、２つ目の引数を変えることで、エラー値は”該当なし”や”－”など任意の値に変換することが出来ます。
=IFERROR(VLOOKUP(E6,$B$4:$C$15,2,FALSE),"該当なし")
=IFERROR(VLOOKUP(E7,$B$4:$C$15,2,FALSE),"－")
IFNA関数で「#N/A」を非表示/変換
2つ目の「#N/A」の解決方法は、IFNA関数(イフ・エヌ・エー)を利用する方法です。
さきほど紹介したIFERROR関数は、『値』がエラー値「#N/A」「#DIV/0!」「#NULL!」「#NUM!」「#VALUE!」「#NAME?」「#REF!」のいずれかであればエラーの場合の変換値を返しましたが、IFNA関数はエラー値「#N/A」限定でエラー値を変換出来る関数です。
IFNA(値, エラーの場合の変換値)
それでは実際にIFNA関数を使って、エラー値を回避する方法を紹介します。
IFNA関数の使い方はIFERROR関数と同じです。エラーを回避したい関数全体をIFNA関数を包むように、下の赤文字部分を追加してください。
=IFNA(VLOOKUP(E4,$B$4:$C$15,2,FALSE),"－")
IFNA関数もエラー値を変換する値を「該当なし」や「(空白)」など任意に設定することが出来ますが、下の図のようにエラー値「#NAME?」など『#N/A』以外のエラー値は変換することが出来ません。
IF関数で「#N/A」を非表示/変換
３つ目の「#N/A」の解決方法は、IF関数(イフ)を利用する方法です。
下の図では、VLOOKUP関数などの関数を複数のセル(F5～F8セル)に先行で挿入した場合、検索値が空白セルのためにエラー「#Ｎ/Ａ」が発生してます。
この見出しで紹介するIF関数を用いたエラーの解決方法は、空白セルによって発生したエラー「#Ｎ/Ａ」の解消する際に便利です。
IF関数は、条件によって表示する内容を変えたい時に使用する関数で、構文は以下のとおりです。
=IF(論理式,[値が真の場合],[値が偽の場合])
論理式に当てはまるものは、[値が真の場合]の結果を表示し、そうでない場合は[値が偽の場合]の結果を表示します。
条件フロー図で表すと以下のようになります。
IF関数の論理式に空白かどうかを判定する数式(＝””)を用いることで、VLOOKUP関数の参照先が空白セルであれば空白セルのまま表示し、そうでなければVLOOKUP関数の結果を表示します。
下の図のように、空白セルの右隣りのセルにIF関数とVLOOKUP関数を組み合わせた以下の関数を挿入します。赤字部分がエラー解消のために追加する関数となります。
=IF(E5="","",VLOOKUP(E5,$B$4:$C$15,2,FALSE))
ISNA関数と条件付き書式で「#N/A」を非表示/変換
４つ目の「#N/A」の解決方法は、ISNA関数(イズ・エヌエー)と条件付き書式を利用する方法です。
ISNA関数の構文は以下のとおりです。
＝ISNA(セルの値)
ISNA 関数はセルの値や数式が 「#N/A」かどうか判定する関数です。「#N/A」であれば ”TRUE” を返します。それ以外のときは ”FALSE”を返す関数です。
=ISNA(A1) のように利用し、A１セルがエラー「#N/A」であれば ”TRUE”が返されます。
このISNA関数と「条件付き書式」機能を用いて、セルがエラー「#N/A」かどうかをISNA関数で判定し、判定結果が”TRUE” であれば文字を白文字にするという条件付き書式を設定します。
手順１．条件付き書式を選択
エラー値を変換したいセル範囲を選択してから、「ホーム」タブ→「条件付き書式」→「新しいルール」を選択します。
手順２．ルールにISNA関数を設定
ルールの種類を「数式を使用して、書式設定するセルを決定」を選択し(図③)、書式設定の入力欄に、「＝ISNA(F4)」と入力します(図④)。次に右下の「書式」ボタンをクリックします。
手順３．フォントを白色に設定
「セルの書式設定」画面で「フォント」タブの色を白色に設定し(図⑥)、「OK」で画面を閉じます(図⑦)。「新しい書式ルール」画面に戻りますので、その画面も「OK」で画面を閉じます。
手順４．完成
下の図のように、VLOOKUP関数の結果が「#N/A」となるセルはフォントが白色に変わったことにより、表面上は非表示となります。
印刷画面で「#N/A」を非表示/変換
５つ目の「#N/A」の解決方法は、印刷設定でエラー値を変換する方法です。
１～４つ目の方法はエクセル画面上でエラー値を解決しましたが、この５つ目の方法は印刷時に「#N/A」が印刷されないようにする方法です。
手順１．「印刷タイトル」を選択
「ページレイアウト」タブ→「印刷タイトル」を選択します。
手順２．「セルのエラー」を〈空白〉を選択
「ページ設定」画面が表示されるので、「シート」タブ→「セルのエラー」を〈空白〉を選択します。これでエラー値が空白になる設定がうまくいったか確認するために、「印刷プレビュー」を選択します。
手順３．印刷プレビュー画面で最終確認
下の図のように、「印刷プレビュー」画面でエラー値「#N/A」が空白で印刷されることを確認することが出来ます。
エラー値の表示は今後減っていく！？
今回はエクセルでエラー値「#N/A」を非表示にする方法や別の値に変換する方法を５つ紹介しました。すべて覚えなくても、５つの中でIFERROR関数だけ使い方を覚えればまず困ることは無くなるかと思います。実践でエラーを解消するためのこれらの関数をどんどん使用して習得してみてください。
また、XLOOKUP関数のように、該当する値が無い場合に表示する値を指定できる関数が登場してきています。将来は、エクセルのエラー値が表示されづらい関数の仕様に変化していくかもしれませんね。