【Excel】VALUE関数で文字列の数字を数値へ変換する手順と注意点

セルに「120」と見えているのに合計へ入らないときは、数値ではなく文字列として保存されている可能性があります。VALUE関数は、Excelが数値として解釈できる文字列を数値へ変換する関数です。元データを残して別列で変換すれば、変わった値を確認してから置き換えられます。

ただし、商品コードや電話番号まで数値にする必要はありません。先頭ゼロや長い番号を守るためにも、最初に「計算する量」と「識別する番号」を分けます。

見た目ではなくISNUMBERで型を確かめる

A2に対象のデータがある場合、別セルへ=ISNUMBER(A2)と入力します。TRUEならすでに数値、FALSEなら数値以外です。文字列かどうかは=ISTEXT(A2)で確認できます。FALSEだから必ず文字列とは限らず、空欄やエラーなどの可能性もあります。

標準の配置では文字列が左、数値が右へ寄ることがありますが、手動で配置を変えている表では見分けられません。セル左上の印も設定次第で表示されないため、配置だけで一括変換するのは避けます。

SUMでセル範囲を集計すると、その範囲にある文字列の数字は通常、合計の対象になりません。例えば文字列の120と80だけを含む範囲では、見た目は200でもSUMは0になります。一方、AVERAGEで範囲内に数値がなければ#DIV/0!になるなど、関数によって症状は異なります。

VALUEを別列へ入力して結果を比較する

練習ではA1を「元データ」、B1を「数値化」とし、A2へ先頭にアポストロフィを付けて'120、A3へ'80と入力します。アポストロフィは文字列として入力するための指定で、セルの表示は120、80になります。

1. B2へ=VALUE(A2)を入力します。
2. B2の式をB3へコピーします。
3. 空いているセルで=ISNUMBER(B2)を確認し、TRUEになることを確かめます。
4. =SUM(B2:B3)が200になることを確認します。

式を文字列へ直接適用する場合は=VALUE("120")のように引用符で囲みます。セルを参照する場合のA2には引用符を付けません。=VALUE("A2")ではA2という文字を数値にしようとしてしまいます。

数値の120も文字列の120も、表示だけでは同じに見えます。VALUEを使った後に見た目が変わらなくても、ISNUMBERと計算結果で確認できます。

空欄と変換できない行を0で埋めない

未入力のセルを空欄のまま残すなら、=IF(A2="","",VALUE(A2))とします。A2が空欄または空文字列のときは空欄を表示し、それ以外のときだけ変換します。数値の0が入力されているセルは0として残ります。

変換エラーを隠すためにIFERRORで一律0へ置き換えると、「未入力」「読み取れない文字」「本当に0だった値」が同じになります。集計で問題が見えなくなるため、まず#VALUE!になった行を調べ、必要なら確認欄へ「要確認」と記録します。

特に注文数や請求額では、誤って0にした1行を合計だけから見つけるのは困難です。元の文字列、変換結果、確認状況を並べると、どこまで変換できたかを追いやすくなります。欠損を0と扱う業務ルールがある場合も、元データの欠損情報は別に残します。

小数点と桁区切りが異なるデータを扱う

VALUEで認識できる表記はExcelの地域設定などに関係します。海外データの「1.234,50」を、日本の一般的な「1,234.50」と同じルールで読むと、解釈を誤る原因になります。記号を全部消す前に、出力元がどちらを小数点として使っているかを確認してください。

小数点がピリオド、桁区切りがカンマなら、=NUMBERVALUE(A2,".",",")で明示できます。小数点がカンマ、桁区切りがピリオドなら=NUMBERVALUE(A2,",",".")です。いずれも、それぞれの表記で1234.50を表す文字列なら、数値の1234.5を返します。

変換後の小数点以下の表示桁数は、セルの表示形式で設定します。1234.50が1234.5と表示されても、値の意味は同じです。小数2桁をそろえる作業と、数値へ変える作業を分けると、不要な丸めを避けられます。

単位や余分な空白は意味を確認して除く

「12個」のような単位付き文字列は、VALUEだけでは変換できません。すべての行が個数で、末尾の「個」を削除してよいと確認できた列なら、=VALUE(SUBSTITUTE(A2,"個",""))のような処理が使えます。ただし、この式は文字列中の「個」をすべて除くため、別の説明が混ざる列には適しません。

余計な半角スペースにはTRIMが役立ちますが、あらゆる空白を消す関数ではありません。全角スペースを含むと分かっているデータなら、確認用の列で=SUBSTITUTE(A2,"　","")を試す方法があります。式の引用符の間には全角スペースを1つ入れています。

負数のマイナスや小数点まで不要文字として削除しないでください。「-12」を12へ変えると、変換は成功しても意味が逆になります。整形は、消してよい文字が明確な列に限定し、代表的な値とエラー行を元の資料で照合します。

日付や時刻も数値になることを理解する

VALUEは数字だけでなく、Excelが認識できる日付や時刻の文字列も数値へ変換します。例えば=VALUE("12:00")は0.5です。時刻は1日を1とする値なので、正午が半日に当たります。表示形式を時刻にすれば12:00と表示できます。

この性質は時刻差の計算に役立ちますが、日付らしいコードを誤って日付にする原因にもなります。日付を扱うと決めた列だけで変換し、「03/04」のように月日が曖昧な文字列は、元データのルールを先に確認してください。

年月日が別々の数値列にあるなら、DATE関数で組み立てるほうが意図を明示できます。VALUEが変換できたことだけでは、その日付が意図した年月日だとは分かりません。変換後は年まで含む表示形式で確認します。

先頭ゼロや16桁以上の番号は文字列で残す

「00123」をVALUEで変換すると123になります。数量なら同じ値ですが、5桁の商品コードなら別の表記です。桁数が固定のコードを数値にしてからゼロ埋め表示しても、元の文字列をそのまま保存したことにはなりません。

さらに、Excelの数値は最大15桁の精度です。16桁以上の会員番号や口座関連の識別番号などを数値へ変換すると、末尾の数字が失われる場合があります。変換後に文字列書式を設定しても、元の数字は復元できません。

このような列は取り込み時から文字列として扱います。すでに桁が変わっている場合は、元のCSVや出力元から取り込み直します。識別番号を数値へ変換せずに照合する設計にすると、先頭ゼロと桁の精度を守れます。

件数を照合してから値に置き換える

変換後は=COUNT(B2:B100)などで数値になった件数を調べ、元資料の件数と照合します。見た目が空欄の式を含むため、単にセル数を数えるだけでは足りません。金額なら合計、数量なら最小値・最大値も確認し、負数や小数が意図どおりかを見ます。

結果が正しいと確認できたら変換列をコピーし、[貼り付け]→[値]で固定します。元列を削除する前に値へ置き換えないと、参照先がなくなり式がエラーになることがあります。元の文字列を別シートに保存しておくと、後日照会を受けたときにも比較できます。

誤変換に気付いた直後ならCtrl+Zで戻します。閉じた後は保存した元ファイルからやり直してください。VALUEは読み取れる表記を数値にする道具なので、変換前の意味と変換後の計算結果の両方を確かめて使いましょう。

📊

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

🏆

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