Excelで数式を入力した際に、セルへ「#VALUE!」や「#REF!」と表示されると、計算結果をすぐに確認できず困るものです。
これらは単なる表示上の不具合ではなく、数式に渡したデータの種類や参照先に問題があることを知らせるエラーメッセージです。
エラーが出たセルだけを見て修正すると再発することもあるため、数式バー、参照範囲、データ形式を順番に確認する姿勢が大切になります。
#VALUE!は値や文字列など、計算できないデータの組み合わせで起こりやすいエラーです。
#REF!は数式が参照していたセルや列、シートが削除されて見つからないときに表示されます。
この記事では、VALUEエラーとREFエラーの原因を見分け、数式を安全に修正するための確認手順を解説します。
サンプルでは1行目を見出し行として扱い、2行目以降にデータが入力されているものとします。
エクセルのVALUE・REFエラーを直す確認手順
それではまず、エクセルのエラーを直すために最初に行いたい確認手順について解説していきます。
| A | B | C |
|---|---|---|
| 商品名 | 単価 | 税込価格 |
| りんご | 120 | =B2*1.1 |
| みかん | 百円 | #VALUE! |
エラーが出た場合は、まずセルをクリックし、数式バーに表示される式と参照範囲を確認します。
確認の順序は、エラー表示の種類、数式バーの内容、参照しているセルの値、セルの表示形式の順が基本です。
エラーセルと数式バーの確認
エラーが表示されたセルを選択すると、数式バーには実際に計算へ使用している数式が表示されます。
たとえばC3に「=B3*1.1」と入力されていて#VALUE!が表示される場合、C3そのものではなく、参照元のB3に文字列や不要な記号が入っている可能性があります。
結果セルだけを直そうとせず、式の中で参照しているセルをたどることが解決への近道です。
数式内のセル参照をクリックすると、Excel上で対応するセルが色付きの枠線で示されるため、どのデータが計算に使われているかを視覚的に確認できます。
参照範囲が複数あるSUMPRODUCT関数やVLOOKUP関数では、見落としが一つでもあるとエラーの原因になり得ます。
数式バーで参照先を一つずつ確認し、数値、日付、文字列が意図どおり入力されているかを調べましょう。
エラーチェック機能による原因表示
エラーセルの左上に緑色の三角形が表示されているときは、セルを選択した際に警告アイコンが現れます。
このアイコンをクリックすると、数式のエラー確認、トレース、ヘルプなどの候補を利用できます。
特に「数式のトレース」は、参照元と参照先の関係を矢印で表示できるため、複雑な表で便利です。
ただし、エラーチェックは原因を推測して示す補助機能です。
表示された案をそのまま適用するのではなく、元の計算目的と照合して判断する必要があります。
【操作のポイント】警告アイコンは原因確定の機能ではありません。数式バーと参照セルを併せて確認すると、誤った修正を避けやすくなります。
修正前の数式を残す準備
重要な集計表や共有ファイルでは、数式を修正する前に対象セルの式をメモ帳や別シートへ控えておくと安心です。
誤って広い範囲へ貼り付けると、正常な数式まで置き換えてしまうかもしれません。
Excelの元に戻す操作は便利ですが、保存や共同編集の状況によっては戻せる範囲が限られます。
修正対象が多い場合は、ファイルを複製してから作業する方法も有効です。
エラー修正では、正しい値を出すことと、元の計算ロジックを失わないことの両方が重要です。
【操作のポイント】修正前の数式を控え、1セルずつ結果を確認しながら進めると、大きな表でも原因の切り分けがしやすくなります。
VALUEエラーの原因となる文字列と空白
続いては、#VALUE!エラーが表示される代表的な原因を確認していきます。
| A | B | C |
|---|---|---|
| 商品 | 数量 | 数量×10 |
| ノート | 3 | 30 |
| ペン | 3本 | #VALUE! |
#VALUE!は、数値として計算するはずの場所に文字列、認識できない日付、空白文字などが含まれると発生しやすいエラーです。
たとえば「=B3*10」でB3に「3本」と入力されている場合、Excelは「3本」を数値として掛け算できず#VALUE!を返します。
数値に見える文字列の判定
「100」や「1,000」のように見えても、セルが文字列として保存されている場合があります。
セルの左上に緑色の三角形が出ている場合や、数値が左寄せで表示されている場合は、文字列として入力されている可能性を確認しましょう。
文字列の数値を数値へ変換するには、警告アイコンから「数値に変換」を選ぶ方法があります。
また、VALUE関数を使い「=VALUE(B2)」として数値へ変換する方法もあります。
ただし、単位や全角文字が混ざっている値はVALUE関数だけで変換できないことがあります。
見た目が数字でも、数式が計算できるデータ型になっているとは限りません。
【操作のポイント】数量や金額の列には「個」「円」などの単位を直接入力せず、表示形式で単位を付けると計算エラーを防げます。
余分な空白と記号の除去
コピーしたデータには、セルの先頭や末尾に半角スペース、全角スペース、改行コードが混入することがあります。
このような不要な文字は目視で気付きにくく、検索や計算の結果を狂わせる要因になります。
半角スペースを除去するにはTRIM関数を利用できます。
=TRIM(B2)
TRIM関数は連続した半角スペースを整えるのに役立ちますが、全角スペースは対象外です。
全角スペースも削除したい場合は、SUBSTITUTE関数を組み合わせます。
=SUBSTITUTE(TRIM(B2),” ”,””)
数値へ変換する必要があれば、この式をVALUE関数で包みます。
元データの仕様を確認せずに空白をすべて削除すると、氏名や商品名など本来必要な空白まで消すおそれがあるため注意が必要です。
【操作のポイント】空白の除去は対象列を限定して行い、変換後の値を数件確認してからオートフィルを使いましょう。
日付と時刻の演算における注意点
Excelでは日付や時刻も内部的には数値として扱われますが、文字列の日付は計算に利用できない場合があります。
たとえば「2026年4月1日」が文字列として取り込まれた状態で、別の日付との差を求めると#VALUE!が表示されることがあります。
DATEVALUE関数で日付へ変換できることもありますが、入力形式や地域設定によって結果が変わる点に注意しましょう。
日付列を扱う際は、セルの表示形式だけでなく、数式バーで実際の値を確認することが大切です。
日付らしく見える文字列と、Excelが日付として認識している値は別物です。
データを外部システムから読み込む場合は、インポート直後に日付列の形式を確認すると後工程のエラーを減らせます。
【操作のポイント】日付計算で#VALUE!が出たら、両方のセルを選択し、数式バーに同じ種類の日付データとして表示されているか確認します。
VALUEエラーを防ぐ関数と数式の修正
続いては、VALUEエラーを数式側から防ぐ方法を確認していきます。
| A | B | C |
|---|---|---|
| 担当者 | 売上 | 税込売上 |
| 田中 | 1000 | =IFERROR(B2*1.1,”確認”) |
| 佐藤 | 未入力 | 確認 |
データの入力ゆれを完全になくすことが難しい表では、エラーを隠すだけではなく、利用者が原因に気付ける数式を設計することが重要です。
IFERROR関数は便利ですが、すべてのエラーを空白にすると異常を見逃すことがあります。
IFERROR関数による表示の整備
IFERROR関数は、数式の結果がエラーになった場合に指定した値を表示する関数です。
=IFERROR(B2*1.1,”入力内容を確認”)
この式ではB2が数値なら税込金額を表示し、計算できない場合は「入力内容を確認」と表示します。
利用者に修正を促す表では、空白よりも具体的な案内文を表示したほうが原因を見つけやすくなります。
一方で、集計の途中にIFERRORを多用すると、元のエラーが分からなくなることがあります。
元データを検証する列と、提出用の表示を整える列を分ける方法も検討しましょう。
【操作のポイント】IFERRORはエラーの根本解決ではなく、利用者に必要な表示を返すための関数として使い分けます。
ISNUMBER関数による入力判定
ISNUMBER関数は、指定したセルが数値ならTRUE、数値でなければFALSEを返します。
数値だけを計算対象にしたい場合は、IF関数と組み合わせると安全です。
=IF(ISNUMBER(B2),B2*1.1,”数値を入力”)
この数式では、B2が数値のときだけ税率を掛け、文字列や空白なら入力を促すメッセージを表示します。
計算前にデータの種類を判定する設計は、VALUEエラーの予防に効果的です。
入力規則を設定して数値以外を入力できないようにすれば、より安定した表になります。
ただし、貼り付け操作では入力規則を回避できる場合もあるため、関数による判定も残しておくと安心です。
【操作のポイント】金額や数量の入力欄には入力規則を設定し、計算列ではISNUMBER関数などで二重に確認する方法が実務的です。
配列範囲と関数の引数の確認
SUMPRODUCT関数、INDEX関数、MATCH関数などでは、引数に指定する範囲の大きさや形式が一致していないとエラーになることがあります。
たとえばSUMPRODUCTでB2:B10とC2:C9を掛け合わせようとすると、行数が異なるため正しく計算できません。
数式をコピーした後に範囲がずれていないか、絶対参照のドル記号を付ける必要がないかを確認しましょう。
関数の引数の区切り方や括弧の閉じ忘れも、数式エラーの原因になります。
数式バーで該当箇所をクリックし、Excelが色分けする参照範囲を確認するとずれを見つけやすくなります。
【操作のポイント】複数範囲を使う関数では、開始行と終了行をそろえ、コピー後の参照先も必ず確認します。
REFエラーの原因となる参照先の削除
続いては、#REF!エラーが発生する仕組みと、参照先を確認する方法について解説していきます。
| A | B | C |
|---|---|---|
| 商品 | 売上 | 確認用 |
| りんご | 1200 | =B2 |
| 列Bを削除後 | 削除済み | #REF! |
#REF!は、数式の中に存在しないセル参照が含まれている状態です。
列、行、シートを削除した結果、数式が参照先を失うと表示されます。
#REF!が出ている数式では、削除された参照先が「#REF!」という文字列に置き換わっています。
列や行を削除した場合の影響
通常、数式が参照するセルの近くで行や列を削除しても、Excelは参照を自動調整します。
しかし、参照対象そのものを削除した場合は、調整できるセルが存在しないため#REF!になります。
たとえば「=B2*C2」という数式で、B列またはC列そのものを削除すると、削除された部分は#REF!に置き換わります。
構造を大きく変更する作業では、数式のある列を確認してから削除を実行する習慣が必要です。
不要に見える列であっても、別シートの集計式やグラフの参照元になっていることがあります。
【操作のポイント】行や列を削除する前に、数式タブの参照元のトレースを使い、ほかの計算に利用されていないかを確認します。
シート名変更とシート削除の確認
シート名をExcelの画面から変更した場合は、多くの数式が自動更新されます。
一方で、参照元のシートを削除すると、別シートにある数式は参照先を失い#REF!になることがあります。
外部ブックを参照する数式や、INDIRECT関数で文字列から参照を作る数式では、特に注意が必要です。
INDIRECT関数は参照先を自動修正できないことがあるため、シート名を変更する前に使用箇所を検索するとよいでしょう。
シート構成を整理する前には、数式だけでなく名前の定義やグラフ、ピボットテーブルの参照先も確認します。
【操作のポイント】シートを削除する作業は元に戻せない場合があります。不要と判断したシートも、まずは非表示にして影響を確認する方法が安全です。
詳細イメージ図による数式バーの確認
REFエラーを見つけたら、エラーセルを選択して数式バー内の#REF!の位置を確認します。
図のように数式バーで#REF!と表示されている部分だけを選び、復元した列や正しいセル参照へ置き換えます。
単純な参照式であれば、数式を編集した状態で正しいセルをクリックするとセル番地を入力できます。
同じ形式の数式が下方向へ並んでいる場合でも、最初に1行だけ修正して結果を確認してからオートフィルを行いましょう。
【操作のポイント】#REF!の文字を消すだけでは計算は戻りません。削除前に参照していた値がどこへ移ったかを確認して、正しいセル番地へ置き換えます。
REFエラーを復元する数式とバックアップ
続いては、REFエラーが起きた後に数式を復元する考え方を確認していきます。
| A | B | C |
|---|---|---|
| 商品 | 売上 | 税込売上 |
| りんご | 1200 | =B2*1.1 |
| みかん | 1500 | =B3*1.1 |
REFエラーの修正では、削除前の数式を推測するのではなく、周囲の正常な数式や履歴から規則性を確認することが重要です。
同じ列に正常な数式が残っていれば、相対参照の規則から正しい数式を復元できる場合があります。
周囲の正常な数式から規則を確認
たとえばC2が「=B2*1.1」、C4が「=B4*1.1」であり、C3だけが#REF!なら、C3には「=B3*1.1」を入力する可能性が高いと判断できます。
ただし、途中に小計行、割引行、条件分岐がある表では、単純に上の式をコピーすると誤計算になることがあります。
数式を修正する前に、同じ種類の行かどうかを列見出しや行の内容から確認しましょう。
周囲の数式の形が同じでも、参照すべきセルの意味まで同じとは限りません。
売上表なら数量と単価、勤怠表なら開始時刻と終了時刻のように、列の役割を理解してから式を再作成する必要があります。
【操作のポイント】正常な数式を参照する際は、セル番地だけでなく、どの項目同士を計算している数式かを確認します。
元に戻す操作とバージョン履歴
削除直後であれば、クイックアクセスツールバーの元に戻す操作で、列やシートの削除そのものを取り消せることがあります。
作業を保存して閉じた後の場合は、OneDriveやSharePointに保存しているファイルならバージョン履歴を確認できる場合があります。
ローカル保存のファイルでも、Windowsのファイル履歴やバックアップが有効なら過去の版を復元できる可能性があります。
復元したファイルを上書き保存する前に、現在のファイルを複製して比較用に残しておくと安全です。
履歴から復元する場合は、必要な数式だけを確認して現在のブックへ戻す方法が、ほかの更新を失いにくい方法です。
【操作のポイント】削除に気付いた直後は、ほかの操作を続けずに元に戻す操作を試します。時間が経った場合はバックアップや履歴を確認します。
外部参照と名前の定義の点検
別ブックのセルを参照する外部参照では、元ファイルの移動、改名、削除によって参照エラーが起こることがあります。
データタブや数式タブからリンクや名前の定義を確認し、古いパスや存在しないシート名が残っていないか点検しましょう。
名前の定義に#REF!が含まれていると、直接その名前を使っていないように見える数式やグラフにも影響する場合があります。
共有用のブックでは、参照元となるファイルの保存場所を勝手に変更しないルールを決めることも大切です。
【操作のポイント】外部参照を含むブックは、元ファイルと参照先ファイルを同じ管理場所に置き、移動や改名の前に影響範囲を確認します。
まとめ エクセルのエラーの原因と対処法
Excelの#VALUE!と#REF!は、どちらも数式を正しく計算できないことを知らせる重要なメッセージです。
#VALUE!が出たときは、数値として扱うセルに文字列、単位、余分な空白、認識されない日付が混ざっていないかを確認します。
#REF!が出たときは、数式バー内の#REF!を探し、削除された行、列、シート、外部ファイルの参照先を確認しましょう。
エラーを非表示にするだけではなく、参照元のデータと数式の意図を確認して修正することが再発防止につながります。
重要なファイルでは、行や列を削除する前に数式の依存関係を確認し、修正前にはコピーや履歴を残す習慣を付けると安心です。
数値入力のルール、表示形式、入力規則、IFERRORやISNUMBERの使い分けを整えることで、エラーに強いExcel表を作れるようになります。
