【Excel】#VALUE!エラーを直す手順 | 数値と文字列を見分けて確認する

足し算や掛け算の結果に#VALUE!が出るときは、計算できない文字や空白が参照先に入っていないか確認します。ただし、#VALUE!は文字列の混入だけを示すエラーではありません。関数に渡した値や範囲など、数式の使い方が原因になる場合もあります。

エラーが消えたことと、正しい金額や件数になったことは別です。SUMへ置き換えて文字列を無視すると、必要な数値まで集計から落ちるおそれがあります。ここでは元データを残して原因を調べ、計算に使える形へ直す方法を説明します。

1. どの値を計算しようとしているか確認する

まずエラーセルを選び、数式バーを読みます。=A2+B2ならA2とB2、=A2*B2なら同じ2セルの値を確認します。多くの式が同時にエラーになっている場合は、最初のエラーを他の式が参照していることもあるため、結果のセルを片端から書き換えないでください。

【要点】数字に見えるかより、数値として格納されているかを確認します。例えばA2が数値100、B2が文字列「200個」の場合、=A2+B2は計算できません。一方、表示形式で「個」を付けて見せている数値200は、文字列の「200個」とは違います。

セルの表示だけで判断しにくければ、数式バーと表示形式を見比べます。右寄せ・左寄せは初期状態での手掛かりにはなりますが、配置を変更したセルでは型を判断できません。修正する前に、元の列を残すかファイルのコピーを保存し、どの値を変えたか追える状態にします。

2. ISNUMBERとISTEXTで型を調べる

調査用の空いている列に=ISNUMBER(B2)を入れると、B2が数値ならTRUEになります。=ISTEXT(B2)は文字列ならTRUEです。どちらもデータを直す関数ではなく、状態を調べるための関数です。まず数行だけで確かめ、問題のある列を絞ります。

文字列の200を再現するには、先頭にアポストロフィを付けて入力する方法があります。この文字列が別の計算で数値として解釈される場合もあるため、「文字列なら+演算で必ず#VALUE!」と一律には判断しません。どの式で、どの入力が原因になるかを確認します。

入力済みの値に対して表示形式だけを「数値」に変えても、格納済みの文字列が必ず数値へ変わるとは限りません。型の確認と変換を分けて進めます。

3. 計算に使う文字列の数字を数値へ変換する

数値として扱うべき列なら、Excelが表示するエラーインジケーターの「数値に変換する」を使えます。インジケーターがない場合や、元列を変えたくない場合は、隣の列でVALUE関数を使う方法があります。

1. 元データB2を残し、隣の空きセルに=VALUE(B2)を入力します。
2. 得られた数値を元の記載と照合します。
3. 問題がなければ必要な行へコピーします。
4. 変換後の列を使って合計や件数を比較します。
5. 元列を置き換える必要がある場合だけ、確認済みの結果をコピーして「値」として貼り付けます。

VALUEは、Excelが認識できる数値の文字列を変換する関数です。「200個」や任意の説明文を、何でも数値にするわけではありません。単位が必ず「個」で、数量欄だけを処理するなら、=VALUE(SUBSTITUTE(B2,"個",""))のように単位を除く方法を検討できます。混在する単位や注記がある列では、まず入力ルールを整理します。

商品番号、郵便番号、電話番号などは計算用の量ではありません。数値化で先頭の0が消えることがあるため、数字の列をすべて一括変換しないでください。

4. 空白や見えない文字は範囲を絞って直す

Webや他のシステムから貼り付けた数字には、空白や制御文字が含まれることがあります。必要なら=LEN(B2)で文字数を確認し、見た目より長くないか調べます。空欄に見えるセルにも、空白文字や空文字を返す式が入っている場合があります。

TRIMで除ける空白と、Web由来の改行しないスペースなどは同じではありません。TRIMやCLEANを使えば、全角スペースを含むあらゆる不可視文字が必ずなくなる、と考えないでください。どの文字が含まれているかを確認し、対象を絞って置換するか、少数なら正しい値を入力し直します。

Ctrl + Hで置換する場合は、先に数量や金額の対象範囲を選び、「検索する文字列」と「置換後」を確認します。ブック全体から「円」や空白を削ると、名称、注記、数式内の文字まで変わる可能性があります。一部のセルで試し、型と合計を確認してから範囲を広げてください。変換後のデータが数値になったかは、再びISNUMBERで確かめます。

データの意味に関係する小数点、桁区切り、負号は、見た目を整えるために削除しません。異なる地域の数値形式を受け取った場合は、元データ側の表記ルールも確認します。

5. SUMへ置き換えると集計漏れになる場合がある

A2が数値100、B2が文字列「200個」なら、=A2+B2は#VALUE!になります。これを=SUM(A2:B2)へ変えると、参照範囲の文字列を無視して100になります。エラーは消えますが、数量200も含めたいなら、正しい合計300にはなっていません。

SUMが文字列を無視する性質は、文字の見出しを含む範囲を集計する場面では役立ちます。しかし、本来数値であるべきデータの不備を直したことにはなりません。範囲内に文字列として格納された数字がないか、行数に対して数値の個数が合うかも確認します。

参照先にある#VALUE!などのエラー値自体まで、SUMが自動的に無視するわけではありません。また、=IFERROR(元の式,0)はエラーを0に置き換えて見せる式であり、原因の修復ではありません。0が正しい業務上の扱いなのかを決める前に使うと、未入力や不正データを見落とします。合計表では、修正前後の差額が何行分に相当するかまで確かめると安心です。

6. 数値化しても直らない場合は関数の条件を見る

参照セルが数値なのにエラーが残るなら、関数の引数を確認します。文字列を検索する関数、日付を扱う関数、複数範囲を計算する関数などでは、渡せる値や範囲の条件が異なります。エラーがあるという理由だけで、すべてをVALUEで包むのは避けます。

Windowsのデスクトップ版で「数式の検証」が使える場合は、式を段階的に確認します。長い数式なら、コピー上で一部を補助セルへ分け、どの部分が先にエラーになるかを調べる方法もあります。元セルに#N/Aや#REF!など別のエラーがある場合は、その原因から直します。

調査時は、使用している関数名、式、参照先の入力例、期待する結果を一組にして整理します。例えば「SUMを使ったら100になったが期待値は300」という情報があれば、文字列を無視してしまった可能性を検討できます。修正が広がり過ぎたら、保存したコピーへ戻り、最初に判明した原因の列だけを直し直します。

7. まとめ:エラーの有無と集計の正しさを別に確認する

#VALUE!への対処は、式と参照先を調べ、必要な値だけを数値へ変換することから始めます。元データを残して別列で変換すれば、途中の結果を比較でき、間違った変換にも気付きやすくなります。

最後は、数値の個数、代表的な数行の値、合計の3点を照合してください。再発する場合は、数量欄に単位を直接入力せず表示形式で見せる、コード列は文字列として扱うなど、入力する列の役割を整理します。

Microsoftの#VALUE!エラーの修正と文字列の数値を変換する手順にも確認方法があります。エラーが消えたら終了ではなく、必要なデータを落とさず集計できたことまで確かめましょう。

📊

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

🏆

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