2024年10月15日
Excel で「#N/A」や「#VALUE!」などが表示され困ったことはありませんか？
また、関数入力中にエラーメッセージが表示されたけれど、直し方が分からなかったという経験はありませんか？
Excel の数式に関するエラーには様々な種類があります。
それぞれのエラーの意味と直し方がわかると対応しやすいですね。 今回は、Excel の数式に関するエラーの意味と直し方をご紹介します。
目次
Excel の数式に関するエラーには、エラー値が表示される場合と、日本語のエラーメッセージが表示される場合があります。
エラー値とは、数式の結果が正しく求められないときに表示される値です。入力した関数に間違いがあったり、数式に含まれるセル参照の参照先が不適切だったりなど、原因に応じて表示される値が違うので、エラー値を参考に数式を修正できます。
今回は以下の7つのエラー値についてご紹介します。
また、数式の入力を確定するタイミングで日本語のエラーメッセージが表示されることもあります。
今回は、以下の2つのメッセージについてご紹介します。
では、以下の売り上げ管理表を例に、エラー値について、またその修正方法についてそれぞれ詳しく説明します。
「バリュー」エラーと読み、入力した数式や参照先のセルに問題があるとき、また引数が不適切なときに表示されます。様々な原因が考えられますが、数値が必要な箇所で文字列を参照してしまっている場合が多いです。
例えば、四則演算の計算式で文字列が参照されているなどです。
このエラーを直すには、正しい参照先を指定し直したり、文字列を数値に修正したりします。
税額を「価格×消費税率」で求めます。
セル【 D4 】に「=C4*K3」を入力し、オートフィルでセル【 D8 】まで数式をコピーします。
「#VALUE!」が表示されました。
セル【 D7 】に「#VALUE!」が表示されたのは、セル【 D7 】に入力された数式「=C7*K6」で参照されているセル【 K6 】が「商品名」という文字列であるためです。
では、数式を入力しなおしてエラーを修正しましょう。
この場合、絶対参照を使ってエラーを修正する方法があります。
セルの参照には、「相対参照」と「絶対参照」があります。
相対参照とはセルの位置を相対的に参照する方法であり、絶対参照とは特定の位置にあるセルを必ず参照する方法です。数式をオートフィルでコピーすると、相対参照しているセルは自動的に調整されますが、絶対参照しているセルは固定され必ず参照されます。
絶対参照にするには、< F4 > キーを押して「$（ドルマーク）」をつけます。
詳しくは、「相対参照と絶対参照をわかりやすく解説」をご覧ください。
ここでは、セル【 D4 】に「=C4*K3」と入力し、< F4 > キーを押します。
「=C4*$K$3」となったことを確認し、< Enter > キーを押すと、計算結果が表示されました。
セル【 D4 】をクリックして選択し、オートフィルでセル【 D8 】まで数式をコピーします。
計算結果が表示され、セル【 D7 】を確認すると「=C7*$K$3」と入力されています。
「ネーム」エラーと読み、認識できない名前が使われたときに表示されます。
例えば、関数名が間違っている場合や定義されていない名前を使って数式を入力した場合です。
このエラーを直すには、正しい名前に修正する必要があります。
関数名が間違っているときは、正しい関数名に直します。
関数名の入力ミスを回避するためには、関数ダイアログボックスを使用すると良いでしょう。
定義されていない名前を使って数式を入力してしまったときは、名前を正確に入力し直します。
税額を求める式を、定義した名前を使う方法で入力しなおします。
まず、消費税率の数値が入力されているセル【 K3 】に「消費税率」という名前をつけます。
ここでは、セル【K3】をクリック、[名前ボックス] をクリックし、「消費税率」と入力します。
< Enter > キーを押すと名前を定義できます。
次に、セル【 D4 】に、定義した名前を使って税額を求める式を入力しますが、定義した名前の入力が間違えていた時、どのようにエラーが表示されるか確認しましょう。
セル【 D4 】をクリックし、「=C4*消費税」と入力し、< Enter > キーを押します。
「#NAME?」が表示されました。
これは、数式で使った名前が間違っているためです。「消費税」は正しくは「消費税率」です。数式を修正します。
では、名前を入力しなおしてエラーを修正しましょう。
この場合、[数式] タブの機能を使ってエラーを修正する方法があります。
数式内で定義した名前を参照するには、名前を直接入力します。また、[数式] タブの [定義された名前] グループにある [数式で使用] をクリックして選択することもできます。
今回は、セル【 D4 】をクリックし、「=C4*」と入力したあと、[数式] タブの [定義された名前] グループにある [数式で使用] をクリックします。
[消費税率] をクリックします。
「=C4*消費税率」と入力されていることを確認し、< Enter > キーを押すと、計算結果が表示されます。
セル【 D4 】をクリックして選択し、オートフィルでセル【 D8 】まで数式をコピーします。
計算結果が表示されました。
「リファレンス」エラーと読み、数式が無効なセルを参照しているときに表示されます。「REF」は「REFERENCE」の略であり、「参照」という意味があります。
例えば、数式が参照しているセルを削除してしまった場合です。
このエラーを直すには、削除したセルの代わりに、値を直接入力したり、別のセルを参照したりして数式を修正します。
税込価格を「価格＋税額」で求めます。
セル【 E4 】に「=C4+D4」と入力し、オートフィルでセル【 E8 】まで数式をコピーします。
その後、「税額」が入力されている列【 D 】を削除すると、どのようにエラーが表示されるか確認しましょう。
まず、セル【 E4 】をクリックして「=C4+D4」と入力し、< Enter > キーを押します。
セル【 E4 】をクリックして選択し、オートフィルでセル【 E8 】まで数式をコピーします。
計算結果が表示されました。
列【 D 】を削除します。
列番号【 D 】の上でクリックして選択し、[ホーム] タブ [セル] グループにある [削除] をクリックします。
列が削除され、「税込価格」以降の列が左にずれました。セル【 D4 】から【 D8 】まで「＃REF!」と表示されました。
これは、数式が参照していたセルを削除したためです。
では、数式を入力しなおしてエラーを修正しましょう。
税込価格を「価格×（１＋消費税率）」で求めておくとエラーになりません。
セル【 D4 】をクリック、「=C4*(1+消費税率)」と入力し、< Enter > キーを押します。
計算結果が表示されました。
セル【 D4 】をクリックして選択し、オートフィルでセル【 D8 】まで数式をコピーします。
計算結果が表示されました。
「ヌル」エラーと読み、数式で正しくない範囲演算子を使っているときに表示されます。つまり、「:（コロン）」や「,（カンマ）」が不適切である場合です。
例えば、関数の引数に、半角スペースを空けて複数のセル範囲を指定すると、「#NULL!」と表示されます。
（セル範囲とセル範囲の間に半角スペースを空けると、2つのセル範囲の重なる部分のみを指定するという意味になります。以下の例では、重なる部分がなかったためエラーとなります。）
このエラーを直すには、半角スペースを「:（コロン）」や「,（カンマ）」に修正します。
各商品の売上金額を求め、売上合計を求めます。
売上金額は「税込価格×売上数量」で求め、売上合計はSUM関数で求めます。
SUM関数とは、合計を求める関数です。
SUM関数の引数で、セル範囲のコロンの代わりにスペースを入力した場合、どのようにエラーが表示されるか確認しましょう。
まず、セル【 F4 】をクリック、「=D4*E4」と入力し、< Enter > キーを押します。
セル【 F4 】をクリックして選択し、オートフィルでセル【 F8 】まで数式をコピーします。
計算結果が表示されました。
セル【 F9 】をクリック、「=SUM(F4 F8)」と入力し、< Enter > キーを押します。
「#NULL!」が表示されました。
これは、セル範囲を表すコロンが抜けてしまったためです。正しくは「=SUM(F4:F8)」です。
では、数式を入力しなおしてエラーを修正しましょう。
セル【 F9 】をクリックし、[ホーム] タブ [編集] グループにある [オートSUM] をクリックします。
「=SUM(F4:F8)」と入力されていることを確認し、< Enter > キーを押します。
計算結果が表示されました。
「ディバイド・バイ・ゼロ」エラーと読み、割り算の割る方が「０」または「空白のセル」を参照しているときに表示されます。「DIV」は「divide」の略で、「割る」という意味であり、「DIV/0」は「ゼロで割る」ということを表しています。
例えば、「=5/0」のような数式を入力した時や、空白のセルを参照して割り算の式を入力した時です。
このエラーを直すには、数式の除数（割り算で割る方の数）を0または空白値でない別のセルに変更します。
売上割合を「売上合計÷売上金額」で求めます。
セル【 G4 】に「=F4/$F$9」を入力し、オートフィルでセル【 G9 】まで数式をコピーします。
売上合計のセル【 F9 】を空白にした場合、どのようにエラーが表示されるか確認しましょう。（その後、元に戻します。）
セル【 G4 】をクリック、「=F4/$F$9」と入力し、< Enter > キーを押します。
計算結果が表示されました。
セル【 G4 】をクリックして選択し、オートフィルでセル【 G9 】まで数式をコピーします。
計算結果が表示されます。
セル【 F9 】をクリックし、< Delete > キーを押し、入力されていた式を削除します。
セル【 G4 】からセル【 G9 】に「#DIV/0!」が表示されました。
これは、割る数にあたるセル【 F9 】が空白のためです。
では、元に戻しましょう。
[元に戻す] をクリックします。
セル【 F9 】が入力された状態に戻り、セル【 G4 】からセル【 G9 】に計算結果が表示されました。
※セル【 G4 】からセル【 G9 】を選択し、[ホーム] タブ [数値] グループにある [パーセントスタイル] をクリックすると、パーセント表示になります。
「ナンバー」エラーと読み、数式に無効な数値が含まれているときに表示されます。
関数の引数に使われる数値に問題がある場合や、計算結果がExcel で処理できる範囲より大きな値または小さな値である場合が多いです。
Excel が処理できる最大値と最小値は、表の通りです。
Eの前の数値に、E+の後の数値の回数分10をかけるという意味です。
例えば、10の310乗を表す「=10^310」と入力すると、Excel が処理できる正の最大値を超えるので、エラーとなります。
Excel の計算式で、ここまで大きい数値や小さい数値を扱うことはまれだと思うので、このエラーが表示されたときは、数式の数値や参照先を確認しましょう。 また、関数入力時に「#NUM!」エラーが表示された場合は、引数の数値が適切か確認します。
セル【 G11 】にLARGE関数を使って最高売上金額を求める式を入力します。
LARGE関数とは、範囲内の特定の順位にあたる数値を求める関数です。
今回は、5つの売上金額を「配列」として指定するので、「順位」には「1～5」のいずれかの数値を入力する必要があります。
LARGE関数の引数「順位」に適切でない数値を入力した場合、どのようにエラーが表示されるか確認しましょう。
セル【 G11 】をクリックし、「=LARGE(F4:F8,11)」と入力します。
< Enter > キーを押します。
「#NUM!」が表示されました。
これは、引数「順位」が「11」となっており、範囲内に存在する数値の数を超えてしまったためです。
最高得点は正しくは「=LARGE(F4:F8,1)」です。
では、引数を入力しなおしてエラーを修正しましょう。
セル【 G11 】に「=LARGE(B2:B6,1)」と入力し、< Enter > キーを押します。
計算結果が表示されました。
「エヌエー」エラーと読み、利用可能な値が入力されていないときに表示されます。「N/A」は「No value is available」の略で「利用可能な値がありません」という意味です。
主にVLOOKUP関数で表示されるエラーです。
VLOOKUP関数とは、コードや番号をもとに参照用の表から該当するデータを検索し、表示する関数です。
VLOOKUP関数の引数「検索値」が参照先のセル範囲に存在しないとき、「#N/A」と表示されます。
このエラーを修正するには、検索値を参照先にある値に入力し直します。または、IFERROR関数を使ってエラー処理をします。
IFERROR関数とは、数式がエラーかどうかチェックして、エラーの場合は指定した値を、エラーでない場合は数式の結果を表示する関数です。
セル【 B4 】にVLOOKUP関数を使って商品名を表示する式を入力します。ここでは、セル【 A4 】のコードを検索値とし、「商品名一覧」を参照表とします。
セル【 A4 】にはコードが入力されているので、正しく商品名が表示されますが、その後、セル【 B4 】の数式をオートフィルでセル【 B8 】までコピーした場合、どのようにエラーが表示されるか確認しましょう。
セル【 B4 】をクリックし、「=VLOOKUP(A4,$I$7:$J$11,2,FALSE)」と入力します。
< Enter > キーを押すと、結果が表示されました。
セル【 B4 】をクリックして選択し、オートフィルでセル【 B8 】に数式をコピーします。
セル【 B8 】に「#N/A」が表示されました。
これは、引数「検索値」が空白のためです。
では、エラーを修正するために、IFERROR関数を使って「部署コード」が空白の場合は空白を表示するようにします。空白は「””」（ダブルクォーテーション2つ）で表します。
セル【 B4 】をクリックし、「=IFERROR(VLOOKUP(A4,$I$7:$J$11,2,FALSE),””)」と入力します。
< Enter > キーを押すと、結果が表示されました。
セル【 B4 】をクリックして選択し、オートフィルでセル【 B8 】まで数式をコピーします。
セル【 B8 】は空白になりました。
続いて、日本語のエラーメッセージについて、またその修正方法についてそれぞれ詳しく説明します。
関数を入力しているときに、必要な「()（かっこ）」や「,（カンマ）」を入れ忘れたまま数式を確定させようとすると表示されます。関数の中に関数を入れる「入れ子構造」のときは特に注意が必要です。
このエラーが表示されたら、数式を見直して修正します。かっこやカンマに抜けがないか確認しましょう。
「#N/A」エラーが表示されている状態まで戻し、IF関数とVLOOKUP関数を組み合わせてエラーが表示されないように入力します。
このとき、「入れ子構造」でかっこを忘れてしまった場合にどのようにエラーが表示されるか確認しましょう。
IF関数とは、条件をもとに結果を表示する関数です。
IF関数とVLOOKUP関数を組みあわせて
「もし部署コードが空白の場合は空白、そうでなければVLOOKUP関数で部署名を表示させる」
という数式を入力します。
[元に戻す] を２回クリックし、「#N/A」エラーが表示される状態に戻します。
セル【 B4 】をクリックし、「=if(A4=””,””VLOOKUP(A4,$I$7:$J$11,2))」と入力します。
< Enter > キーを押すと、エラーメッセージが表示されました。
これは、IF関数の「,（カンマ）」の数が1つ足りないためです。
正しくは「=IF(A4=””,””,VLOOKUP(A4,$I$7:$J$11,2))」です。
では、数式を修正します。
[OK] をクリックします。
カーソルを「V」の左に移動し、「,（カンマ）」を入力します。
「=IF(A4=””,””,VLOOKUP(A4,$I$7:$J$11,2))」と入力されていることを確認し、< Enter > キーを押します。
計算結果が表示されました。
セル【 B4 】をクリックして選択し、オートフィルでセル【 B8 】まで数式をコピーします。
セル【 B8 】は空白になりました。
数式を入力中に、循環参照がおこると表示されます。
循環参照とは、数式で「自分が入力されているセル」を参照する状態のことです。
例えば、セル【 A1 】に「＝A1+B2」と入力すると循環参照が発生し、このメッセージが表示されます。
メッセージが表示されるとエラーのように見えますが、実は循環参照はエラーではありません。
以前はよく使われていた計算方法の１つです。現在では使われることが少なくなり、数式の入力ミスの元になっている場合が多いため、注意喚起のためのメッセージを表示するようになっています。
このメッセージが表示されたら、数式の中で循環参照が起きている個所を探し修正しましょう。
売上合計前年度比を「売上合計÷前年度の売上合計」で求めます。
セル【 G12 】に式を入力する際、正しくは「=F9/J14」ですが、「=G12/J14」と入力した場合、どのようにメッセージが表示されるか確認しましょう。
セル【 G12 】をクリックし、「=G12/J14」と入力します。
< Enter > キーを押すと、メッセージが表示されました。
[OK] をクリックします。
「0」と表示されました。
では、数式を修正します。
セル【 G12 】をダブルクリック、「G12」を削除し、「F9」と入力します。
< Enter > キーを押します。
結果が表示されました。
※セル【 G12 】を選択し、[ホーム] タブ [数値] グループにある [パーセントスタイル] をクリックすると、パーセント表示になります。
