【Excel】#VALUE!と文字列の数値を確認する方法|元データを残して変換する手順

Excelの#VALUE!は、数式に渡した値や引数が想定と合わない時などに出るエラーです。文字列の混入は原因の一つですが、すべての#VALUE!を数値変換で直せるわけではありません。まず問題の式と参照セルを確認し、変換が必要な値だけを処理します。

商品コード、社員番号、電話番号などは、数字だけでも計算用の数値ではありません。先頭ゼロや長い桁を守るため、元データを残し、列全体を一律に変換しないでください。

1. エラーの数式と参照先を確認する

エラーが出たセルを選び、数式バーで参照しているセルを見ます。関数の引数の順序、範囲の大きさ、文字列を渡してよい箇所かを確認してください。Windowsのデスクトップ版では「数式」タブの「数式の検証」から、どの段階でエラーになるかを追える場合があります。

例えば金額欄に「未定」や空白文字が入っていれば、四則演算でエラーになることがあります。一方、SUMが参照範囲の文字列を無視して、エラーを出さず少ない合計を返す場合もあります。エラーが消えたことだけで正しく集計できたとは判断しません。

別セルに=ISTEXT(A2)、=ISNUMBER(A2)を入れると型を確認できます。左右どちらに揃っているかは手動書式でも変わるため、それだけを判定基準にしないでください。

2. 数値へ変える列と、文字列のまま残す列を分ける

金額や数量は数値へ変える候補ですが、コードや番号は通常そのまま保持します。文字列の「00123」を数値にすれば、先頭の0は失われます。また、Excelの数値には精度の上限があり、16桁以上の識別子を数値として扱うと元どおりに保存できない場合があります。

見た目が数字でも、計算する金額と識別に使うコードでは扱いが違います。次の例では、金額の文字列だけを別列で数値へ変換し、先頭ゼロを含む商品コードと長い管理番号は文字列のまま残します。

CSVから取り込む時は、必要な列を文字列として指定して読み込む方法を検討します。一度失われた先頭ゼロや長い番号の桁は、後から表示形式を文字列へ変えるだけでは復元できません。元CSVと見比べてください。

1. 元のファイルを別名で保存するか、対象シートを複製します。
2. 計算に使う列と識別子の列を分類します。
3. 数値にしたい少数のセルだけを選び、変換結果を確かめます。
4. 桁・小数点・負数・空欄が期待どおりなら、対象範囲を広げます。

3. 元データを残して文字列の数値を変換する

別列に=VALUE(A2)を入れる方法なら、元の文字列と結果を比較できます。認識できない記号が含まれていればエラーになるので、数式バーで内容を確認します。小数点や桁区切りの記号が異なるデータでは、地域設定や取り込み側の設定も確認してください。

緑の三角のエラーチェックから「数値に変換」が選べる場合もあります。対象が金額・数量であり、識別子ではないことを確認して使います。緑の三角がないことは、すべて数値だという証拠にはなりません。

「区切り位置」を使う場合は、複製した対象範囲でプレビューを確認し、区切り記号や出力先を設定します。区切り設定によって隣の列へ分かれることがあるため、何も見ずに「次へ」「完了」を押さないでください。「1を乗算して貼り付ける」方法も元のセルを書き換えるので、出所不明の列全体へ適用する運用にはしません。

4. 空白・単位・見えない文字を点検する

数字の前後に空白や通貨記号、単位が混ざっていると、取り込み方や式によって結果が変わります。=LEN(A2)で文字数を見て、見た目が同じセルと比べる方法もあります。

TRIMやCLEANを使っても、あらゆる空白・不可視文字が取り除かれるわけではありません。確認できた文字だけを置換し、住所や商品名など、必要な空白まで削らないようにします。記号を削除して数値にする場合も、その記号が小数点や負数の意味を持っていないか確認します。

文字列を除外する関数へ置き換えるだけでは、集計漏れを隠すことがあります。金額のはずのセルが集計対象から外れていないか、件数と合計の両方を確認してください。

5. 繰り返す取り込みでは列の型を指定する

同じ形式のCSVを繰り返し扱うなら、Power Queryなどの取り込み処理で列ごとの型を決める方法があります。金額は必要な数値型、コードはテキストというように、用途に合わせて指定します。自動で追加された型変換のステップが、コードを数値へ変えていないかも確認します。

型を指定しても、次回のデータに文字や異なる日付形式が混ざればエラーになる場合があります。エラー行を捨てて更新を終えるのではなく、その行数と元データを確認できる運用にします。

6. 修正後はエラー件数と集計結果を確かめる

変換したセルの型、先頭ゼロ、桁数、合計、データ件数を照合します。IFERRORで空白や0に置き換えるのは、原因の修正ではありません。未処理の金額まで見えなくなるため、必要なら別の列でエラー件数を残してください。

文字列以外の原因や関数ごとの対処は、Microsoftの#VALUE!の説明も参照できます。計算の精度は再計算と精度の仕様で確認できます。

📊

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

🏆

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