【Excel】#N/Aを0・未登録・空白に変える方法|IFNAとIFERRORの使い分け

VLOOKUPの結果が「#N/A」になるとき、見つからなかった項目を「未登録」と表示することはできます。ただし、検索失敗と、本当に金額がゼロの取引は別です。エラーをすべて0にすると、欠けている売上や単価に気づかず、合計・平均を確定してしまいます。

#N/Aだけを置き換えるならIFNA、複数種類のエラーをまとめて扱う必要があるならIFERRORを使います。ここでは商品コードから単価を探す表を例に、入力前の空欄、未登録のコード、実際のゼロを区別する式を作ります。

#N/Aを置き換える前に、検索条件を確認する

A2に検索する商品コード、F2:G100に商品コードと単価の一覧があるとします。基本の式は=VLOOKUP(A2,$F$2:$G$100,2,FALSE)です。最後のFALSEは完全一致で検索する指定です。まずこの式だけで、登録済みの商品から正しい単価が返ることを確認してください。

コードがあるはずなのに#N/Aになる場合は、検索範囲から該当行が外れていないか、数値の123と文字列の「123」を混在させていないか、前後に空白がないかを確認します。先頭ゼロを含む管理番号は、見た目を合わせるためだけに数値へ変換すると別の番号になり得ます。

こうした不一致を直す前にエラーを隠すと、登録済みの品目まで「未登録」と扱われます。置き換えは検索の修理ではなく、検索結果をどう伝えるかの設定です。

IFNAとIFERRORの範囲を分ける

=IFNA(VLOOKUP(A2,$F$2:$G$100,2,FALSE),"未登録")は、検索結果が#N/Aのときだけ「未登録」を返します。一方、=IFERROR(VLOOKUP(A2,$F$2:$G$100,2,FALSE),"要確認")では、#REF!などの参照エラーも同じ表示になります。

元の結果	IFNAで「未登録」に置換	IFERRORで「要確認」に置換
単価1,200	1,200のまま	1,200のまま
#N/A	未登録	要確認
#REF!	エラーを残す	要確認

商品一覧の列を削除した場合など、直すべき問題を表に残したいならIFNAが向きます。IFERRORを使う場合も、原因を調べるための元の計算列やチェック欄を別に残すと、表示だけを整えて問題を見落とすことを防げます。

なおIFNAが区別するのはエラーの種類であり、発生理由ではありません。コードが見つかっても、返す先の単価セル自体が#N/Aなら同じく置換されます。「未登録」というラベルを使う前に、マスターの単価列がエラーを含まないことを確認してください。複数の原因があり得る表なら「単価要確認」とする方が実態に合います。

「未入力」と「未登録」を別々に表示する

A2が空ならまだ検索する段階ではなく、コードを入力したのに見つからなければ登録状況の確認が必要です。この二つを分けるには、先にIFで入力欄を判定します。

=IF(A2="","未入力",IFNA(VLOOKUP(A2,$F$2:$G$100,2,FALSE),"未登録"))

この式では、A2が空なら「未入力」、存在しないコードなら「未登録」、見つかったコードなら単価が出ます。入力前は何も表示したくない場合に限り、最初の「未入力」を""に変えてください。スペースが入力されたセルは空欄と同じではないため、入力規則や入力内容の点検も必要です。

下の行へコピーする場合、A2はA3、A4と変わり、検索一覧の$F$2:$G$100は固定されます。一覧を100行より先へ増やしたら範囲の見直しも必要です。追加行が多い台帳ではテーブル化も検討できます。

0への置き換えが集計に与える影響

数値のゼロを返す式は=IFNA(元の式,0)です。文字の「0」ではなく数値を返すため、引用符を付けません。ただし使ってよいのは、「該当データがないことを、この集計ではゼロとみなす」と決められる場合です。

たとえば3件の単価が100円、200円、未確認だったとします。未確認を0に変えた列をAVERAGEで集計すると100円です。「未確認」という文字を残した列を範囲指定で平均すると、数値2件だけが対象なので150円です。どちらも3件すべての単価が判明したことにはなりません。

SUMは範囲内の文字列を集計しないため、「未登録」を残しても合計が出ることがあります。合計が出たことと、集計に必要な全件がそろったことは別です。金額の合計と未登録件数を併記し、未登録がある間は暫定値として扱う方が、単に0へ変えるより状況が伝わります。

たとえば結果をB2:B100へ入れているなら、=COUNTIF(B2:B100,"未登録")で要確認の件数を表示できます。担当者が一覧を修正した後に件数が0へ戻るかも確認します。この件数は「未登録」という文字を付けた行だけなので、別のエラーや単価の空欄まで含む総合的な検査にはなりません。

空文字にするときは、空白判定とグラフも点検する

=IFNA(元の式,"")は見た目を空欄にしますが、セルには数式が残ります。完全に何も入っていないセルとは異なり、ISBLANKではFALSEになります。COUNTBLANKは空文字を返す数式セルも数えるため、確認に使う関数によって結果が違います。

後続の式がそのセルを直接足し算する場合と、SUMで範囲を集計する場合でも、文字列の扱いが違います。「空白にしたから計算の邪魔にならない」と一括りにせず、実際に使う集計式まで確認してください。

折れ線グラフでは0への置換により線がゼロまで落ち、「未取得」なのに「実績なし」に見える場合があります。グラフ用の欠損表示は、グラフの種類や空白・#N/Aの設定も含めて確認します。帳票向けの文字表示と、グラフ向けのデータは分ける方法もあります。

置き換え後は4種類のデータで試す

1. 一覧にある通常のコードを入れ、元の単価と一致するか確認します。
2. 一覧にないコードを入れ、「未登録」など意図した表示になるか確認します。
3. 検索欄を空にして、入力前の表示を確認します。
4. 単価が実際に0の商品を入れ、未登録扱いにしていないか確認します。

参照エラーを残す設計なら、本番ではなく検証用コピーで参照範囲を変えた場合も確かめます。表示の確認後、未登録の件数、合計、平均、グラフを順に見ます。単価未入力の商品が見つかった場合、VLOOKUPが0を返すこともあるため、商品コードの有無だけでなくマスター側の単価欄も点検してください。

すでに置換済みで原因が分からなくなった式は、別の確認列へ内側のVLOOKUPだけをコピーし、元のエラーを表示させます。結果列の数式を一括で削除して調べる必要はありません。調査用の式が参照する行と固定範囲を確認し、原因を修正した後に確認列を片付けます。

エラーを隠す目的と、残す情報を決める

#N/Aを置き換えるときは、IFNAで対象を絞り、入力前・未登録・実績ゼロを分けるのが基本です。IFERRORはエラーの種類を問わず同じ扱いにしてよい箇所に限定し、修正の手掛かりを別に残します。

公式の仕様は、MicrosoftのIFNA、IFERROR、AVERAGE、COUNTBLANKの説明でも確認できます。見た目を整えた後も、未確認のデータが残っているかを読める表にしてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】