外部システムやWebページ、CSVなどからExcelへ数字を貼り付けると、見た目は「1000」でも文字列として扱われ、計算や集計がうまくできないことがあります。
元データを消さずに確認しながら数値へ変換したい場合は、元の列を残したまま、隣の空き列でVALUE関数を使う方法が分かりやすく安全です。
たとえばA2に変換したいデータがあるなら、B2に次の数式を入力します。
=VALUE(A2)VALUE関数は、Excelが数値・日付・時刻として認識できる文字列を数値へ変換します。一方、Excelが認識できない文字列は#VALUE!になります。
重要なのは、VALUE関数を「文字を何でも取り除いて数値だけを抜き出す関数」と考えないことです。変換できない行があれば、元データを残したまま原因を確認するのが確実です。
Excelの文字列の数字をVALUE関数で数値へ直す方法
VALUE関数の基本的な書式は次のとおりです。
=VALUE(文字列)セルを指定する場合は、次のように記述します。
=VALUE(A2)A2にExcelが数値として認識できる文字列が入っていれば、B2には計算に利用できる数値が返されます。
外部から貼り付けたデータを扱う場合は、A列をそのまま残し、B列を変換用として使うと確認しやすくなります。
| A列：元データ | B列：変換用 |
|---|---|
| 元の文字列 | =VALUE(A2) |
| 元の文字列 | =VALUE(A3) |
| 元の文字列 | =VALUE(A4) |
この構成なら、変換前後を横に並べて比較できます。変換に失敗しても元データが残るため、原因を確認してから対処できます。
元の列を残して別列で変換するのがおすすめ
貼り付けた数字を数値化するとき、最初から元データを書き換える必要はありません。
特に業務データでは、次のように「元データ」「変換結果」を分けておくと扱いやすくなります。
| A列 | B列 |
|---|---|
| 元データ | 数値変換後 |
| 1000 | =VALUE(A2) |
| 2500 | =VALUE(A3) |
| 4800 | =VALUE(A4) |
手順はシンプルです。
- 貼り付けた元データをA列に残す
- B列などの空き列を変換結果用にする
- B2に
=VALUE(A2)を入力する - 必要な行まで数式をコピーする
- 正常に変換された行と
#VALUE!の行を確認する - 問題がないことを確認してから、元データを置き換えるか判断する
先に別列で変換しておけば、途中で予想外の結果が出ても元の文字列と比較できます。
大量データほど、「まず別列で変換して確認する」という進め方が重要です。
VALUE関数で変換できるもの
VALUE関数は、単純な整数だけを対象にした関数ではありません。
Microsoftの説明では、Excelが認識できる数値、日付、時刻を表す文字列を数値へ変換できます。
たとえば、考え方としては次のようになります。
| 文字列の種類 | VALUE関数 |
|---|---|
| Excelが認識できる数値表記 | 数値へ変換できる |
| Excelが認識できる日付表記 | 日付を表す数値へ変換できる |
| Excelが認識できる時刻表記 | 時刻を表す数値へ変換できる |
| Excelが認識できない文字列 | #VALUE!になる |
ただし、Excelが認識できる表記は環境や地域設定などの影響を受ける場合があります。
「通貨記号が付いていれば必ず変換できる」「どの区切り記号でもVALUE関数なら対応できる」とは考えず、実際のデータで変換結果を確認してください。
#VALUE!になったら元の文字列を確認する
VALUE関数を使って#VALUE!が表示された場合、まず確認したいのは元データです。
VALUE関数は、文字列の中から数字だけを探して抜き出す関数ではありません。Excelが数値・日付・時刻として認識できない形式であれば、そのまま変換することはできません。
たとえば外部システムから取得したデータには、数字以外の情報が含まれていることがあります。
商品:1000約2500円数量10個このようなデータを見て、「VALUE関数を使えば文字を取り除いて1000や2500、10だけにしてくれる」と考えるのは適切ではありません。
#VALUE!になった行は、変換式を無理に変更する前に、A列に残してある元データを確認します。
エラー行だけ元データと見比べる
別列でVALUE関数を使うメリットは、エラーになったときに元データをすぐ確認できることです。
たとえば次のような状態です。
| A列：元データ | B列：変換結果 |
|---|---|
| 数値として認識可能な文字列 | 数値 |
| 数値として認識可能な文字列 | 数値 |
| 認識できない形式 | #VALUE! |
#VALUE!が出たら、「VALUE関数が壊れている」と考えるのではなく、まずExcelが認識できる形式になっているかを確認します。
データの一部だけエラーになる場合は、正常行とエラー行の元文字列を比較すると違いを見つけやすくなります。
時刻を変換すると見た目が変わることがある
VALUE関数では、Excelが認識できる時刻の文字列も変換できます。
ここで注意したいのが、Excelでは時刻も計算用の数値として扱われることです。
そのため、変換後のセルの表示形式によっては、元の「時刻らしい表示」と変換後の見た目が大きく異なる場合があります。
Microsoftが示している例では、
=VALUE("16:48:00")-VALUE("12:00:00")によって、4時間48分に対応する値として0.2が得られます。
つまり、時刻の文字列をVALUE関数へ渡した結果が小数になったとしても、それだけで変換失敗とは判断できません。
値そのものと、セルにどのような形式で表示されているかは分けて考える必要があります。
日付や時刻を扱う列では、変換後の値だけでなく、セルの表示形式も確認してください。
数値に変換すべきデータと文字列のまま残すデータを分ける
「数字が入っているなら全部数値化したほうがよい」とは限りません。
変換するかどうかは、見た目ではなくそのデータを何に使うかで判断します。
金額や数量は数値化する意味が大きい
次のように計算するためのデータは、数値として扱える状態にするメリットがあります。
- 売上金額
- 購入金額
- 数量
- 重量
- 回数
- 人数
- 集計対象となる数値
たとえば数量が文字列のまま取り込まれていて、集計や計算で問題になる場合は、VALUE関数による変換を検討できます。
管理コードは文字列のまま扱うことがある
一方、数字だけで構成されていても「計算するための数字」ではないものがあります。
たとえば次のようなデータです。
- 商品コード
- 社員コード
- 顧客番号
- 管理番号
- 伝票番号
- 数字だけで構成されたID
これらは数値ではなく、識別子として扱うほうが適切な場合があります。
特に注意したいのが先頭のゼロです。
001234このようなコードを数値として扱うと、数値としては1234になります。
元の6桁という情報に意味があるなら、むやみにVALUE関数で数値化しないほうが安全です。
先頭のゼロをVALUE関数で復元することはできない
VALUE関数を使う前に確認したいのが、元データに必要な桁情報が残っているかです。
たとえば本来、
001234という管理コードだったものが、データを取得する途中ですでに、
1234になっていたとします。
この状態からVALUE関数を使っても、元が001234だったことを判断して復元することはできません。
VALUE関数は、文字列として保持されている数字を数値へ変換するための関数です。失われた元データを推測して復元する機能ではありません。
先頭ゼロが重要なデータでは、数値変換よりも先に元データの形式を確認してください。
「見た目が数字」だけでは変換すべきか判断しない
外部データを扱うときは、次の2つを分けて考えると整理しやすくなります。
計算値
: 足し算、平均、集計、比較などに利用する値
識別子
: 商品、顧客、伝票などを識別するための文字列
たとえば「000123」というデータがあったとしても、それだけでは数値化すべきか判断できません。
数量の123をゼロ埋め表示しているだけなら、数値として扱う選択肢があります。一方、商品コード「000123」なら文字列のまま保持したほうが自然です。
数値化する前に、「この列は計算するための値なのか、それとも識別するための値なのか」を確認しましょう。
VALUE関数で変換するときの実務的な手順
外部から貼り付けたデータを安全に処理するなら、次の順番で進めると確認しやすくなります。
元データの用途を確認する
最初に、その列が金額や数量などの計算対象なのか、商品コードなどの識別子なのかを確認します。
識別子であれば、数字だけに見えても数値へ変換しない判断が必要です。
元データを残したまま別列を用意する
A列が元データなら、B列を変換確認用として使用します。
A列を直接変更しないことで、変換結果がおかしい場合にも元データへ戻れます。
VALUE関数を入力する
B2に次の数式を入力します。
=VALUE(A2)必要な行までコピーします。
#VALUE!になった行を確認する
正常に数値化された行と#VALUE!になった行を分けて確認します。
エラーになった行では、元の文字列がExcelの認識できる形式になっているかを調べます。
日付や時刻は表示形式も確認する
日付や時刻を表す文字列を変換した場合、結果はExcel内部で計算に利用される数値になります。
見た目が変わっていても、値がおかしいとは限りません。セルの表示形式も含めて確認します。
問題がないことを確認してから上書きを判断する
変換が完了しても、すぐに元の列を削除する必要はありません。
計算や集計に利用して問題がないことを確認し、必要であればその後にデータ構成を整理します。
元データを残す必要がある業務では、A列とB列をそのまま併存させる方法もあります。
VALUE関数で失敗しやすいポイント
文字列の数字を数値化するときは、次の点に注意してください。
| 注意点 | 確認すること |
|---|---|
| VALUE関数は万能な文字除去関数ではない | Excelが認識できる形式か確認する |
#VALUE!が出る | 元の文字列を確認する |
| 日付・時刻の見た目が変わる | 値だけでなく表示形式を確認する |
| 先頭ゼロが消える | 数値ではなくコードではないか確認する |
| 桁情報がすでに失われている | VALUE関数では元の桁を復元できない |
| 外部データの表記が特殊 | 地域設定なども含め実データで確認する |
| 元列をすぐ上書きする | 別列で変換結果を確認してから判断する |
特に重要なのは、「数字に見えるから数値へ変換する」のではなく、その列を計算に使う必要があるかを基準にすることです。
外部から貼り付けた数字は「別列でVALUE」が確認しやすい
Excelで外部から貼り付けた数字が文字列になっている場合は、元データを残し、別の列でVALUE関数を使うと安全に確認できます。
A2を変換するなら、基本形は次のとおりです。
=VALUE(A2)VALUE関数は、Excelが認識できる数値・日付・時刻を表す文字列を数値へ変換します。認識できない形式では#VALUE!になるため、エラーになった行は元の文字列を確認してください。
また、金額や数量のような計算用データと、商品コードや管理番号のような識別子は分けて考える必要があります。
実務では、元データを残す → 別列で変換する → エラーを確認する → 問題がなければ上書きや整理を判断する、という順序で進めると失敗を防ぎやすくなります。
コメント