Excel で #N/A などのエラーを修正する方法
Excel でエラーを直すには、エラーコードが示す内容を読みましょう。エラーごとに原因が異なります。#N/A は検索で値が見つからないこと、#DIV/0! は 0 で割っていること、#VALUE! は値の型が合っていないことを意味します。まず原因を直しましょう。IFERROR でエラーを隠すのは、そのエラーが起こると予想できる場合だけにしてください。
1. #N/A: 検索で値が見つからない
VLOOKUP、XLOOKUP、MATCH は、探している値がリストにないと #N/A を返します。次の 3 点を確認しましょう。末尾の空白(TRIM で削除できます)、片方だけ文字列として保存されている数値、テーブルの範囲にその行が含まれていないことです。値がないのが普通の場合は、IFNA を使うとこのエラーだけを置き換えられます。
例:コードが見つからないときに読みやすいメッセージを出す
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | コード | 製品 | 検索値 | 結果 | |
| 2 | P-100 | ペン | P-999 | 見つかりません | |
| 3 | P-101 | メモ帳 | |||
| 4 | P-102 | インク |
P-999 はリストにないため、VLOOKUP は #N/A を返します。IFNA がそれを「見つかりません」というメッセージに置き換えます。
2. #DIV/0!: 0 による除算
Excel は 0 で割ったり、空のセルで割ったりすることはできません。割る数が 0 または空白になっているセルを探しましょう。0 になるのが普通の場合(たとえばまだ注文がない顧客など)は、IFERROR で捕まえましょう。
例:注文がないときの平均
| A | B | C | |
|---|---|---|---|
| 1 | 合計 | 注文数 | 平均 |
| 2 | 10 | 0 | 0 |
10 を 0 で割ると #DIV/0! になるため、IFERROR は指定した 0 を返します。
3. #VALUE!、#REF!、#NAME?、#NUM!
#VALUE! は、値の型が合っていないことを意味します。たとえば計算に文字列を使ったり、数値のセルに空白が入っていたりする場合です。ISNUMBER でセルを確認しましょう。#REF! は、削除されたセルを参照していることを意味します。削除を元に戻すか、参照を書き直しましょう。#NAME? は、Excel が数式内の単語を理解できないことを意味します。関数名のスペルミス、テキストを囲む引用符の抜け、存在しない名前などが原因です。#NUM! は、ありえない数値になる計算であることを意味します。たとえば負の数の平方根などです。
4. #SPILL! と ####
#SPILL! は、動的配列の数式が結果を表示するための領域を必要としているのに、その下や横のセルが邪魔をしているときに表示されます。そのセルを空にしましょう。#### が並ぶ場合は数式のエラーではありません。数値や日付を表示するには列の幅が狭すぎるため、列幅を広げましょう。
5. 数式の評価で原因を探す
[数式] > [数式の評価] に進み、[評価] をクリックします。Excel は数式を一部ずつ順に計算するため、どの部分がエラーになるかがわかります。セルの左上隅にある緑色の三角形を選ぶと、エラーチェックのオプションが表示されます。
よくあるミス
最初からすべての数式を IFERROR で囲む。 範囲の誤りなどの本当の問題が隠れてしまい、シートには 0 が静かに表示されるようになります。
修正: まず原因を見つけて直しましょう。IFNA は、検索の失敗だけが想定される場合に使いましょう。
数値を文字列と比べて検索する。 数値の 100 と文字列の「100」は同じに見えますが、検索では一致しません。
修正: 両方を同じ型にしましょう。たとえば VALUE を使うか、1 を掛けます。
数式が使っている列を削除する。 数式が #REF! と表示されます。
修正: すぐに Ctrl + Z で元に戻すか、セルを削除せずに内容だけを消しましょう。
よくある質問
IFERROR と IFNA の違いは何ですか?
IFERROR はすべてのエラーを捕まえます。IFNA は #N/A だけを捕まえます。検索では IFNA のほうが安全です。範囲の誤りやタイプミスを隠さないからです。
値がリストにあるのに #N/A になるのはなぜですか?
通常は、末尾の空白、文字列として保存された数値、1 文字だけ違う値での完全一致が原因です。両方を TRIM と ISNUMBER で確認しましょう。
シート内のエラーをすべて見つけるにはどうすればよいですか?
Ctrl + G を押し、[セル選択] をクリックして、[数式] を選び、[エラー] だけにチェックを入れます。Excel はエラーを表示しているすべてのセルを選択します。
関連する関数
- IFERRORIFERROR は、数式が #N/A や #DIV/0! などのエラーを返すときに、指定した値を返します。それ以外のときは通常の結果を返します。構文、例、使うべきでない場合を説明します。.
- VLOOKUPVLOOKUP は、表の最初の列から値を探し、同じ行の別の列の値を返します。構文、例、よくある間違い、練習問題を説明します。.
- XLOOKUPXLOOKUP は、1 つの列または行を検索し、どの方向でも、別の列または行から対応する値を返します。見つからなかったときのメッセージも組み込まれています。構文、例、練習問題を説明します。.
- TRIMTRIM は、テキストの余分なスペースを取り除きます。先頭と末尾のスペース、単語の間の連続したスペースが対象です。構文、例、#N/A になる検索を直す理由を紹介します。.
- IFIF は条件を確かめ、真なら一方の値、偽なら別の値を返します。文字列と数値の例、ネストした IF、よくある間違いを説明します。.
Excel のエラー について詳しく
このガイドでは各エラーの意味と修正方法を説明しました。ここでは、長い数式で原因を素早く見つける方法、計算前にセルを確認する方法、合計にエラーを含めない方法を説明します。
長い数式で原因を見つける
数式バーで数式の一部を選択し、F9 を押しましょう。Excel はその部分だけの値を表示するため、どの部分がエラーを返しているかがわかります。その後は Esc を押してください。押さないと、数式がその値に置き換わってしまいます。[数式] > [数式の評価] を使えば、同じことを一歩ずつ、リスクなしで確認できます。
計算する前にセルを確認する
IS 系の関数はエラーを発生させず、TRUE または FALSE を返します。ISNUMBER は値が本当の数値かどうかを、ISBLANK はセルが空かどうかを教えてくれます。これらを使って、次にどうするかを決めましょう。
=IF(ISNUMBER(B2),B2*2,"check input")合計にエラーを含めない
範囲内のエラーが 1 つあると、その範囲の SUM もエラーになります。修正方法は、元のセルを直すか、エラーを無視できる AGGREGATE を使うことです。手早く合計したい場合は、各セルを IFERROR で囲み、0 を返すようにできます。ただし、その行で 0 が正しい答えである場合だけにしましょう。空白やダッシュのほうが誠実なことが多いです。0 は実際の測定値のように見え、平均を下げてしまうからです。どちらを選ぶにしても、ルールを 1 つ決めてすべてに適用しましょう。そうすれば、どの列でもシートの動作がそろいます。
続けて、[エラーのレッスン](/learn/errors-iferror)、[IS 関数のレッスン](/learn/is-functions)、[IFERROR の関数ページ](/functions/iferror) に進みましょう。