CSV、基幹システム、Webページ、PDFなどからExcelへデータを取り込むと、見た目は数字でも内部では文字列として保存されることがあります。セルの左上に緑色の三角形が表示されたり、数値が左寄せになったり、SUM関数の合計から除外されたりする場合は、文字列として扱われている可能性があります。
さらに実務データには、全角数字、桁区切りカンマ、通貨記号、単位、パーセント記号、記号付き負数、海外形式の小数などが混在します。これらを一律にVALUE関数へ渡しても、すべてが正しく数値になるとは限りません。
本稿では、最初に一つのサンプル表を作り、その同じ表へVALUE・NUMBERVALUE・ASC・SUBSTITUTE・LET・MAP・ISNUMBERを順に適用します。途中でデータを切り替えず、そのまま数値化できる値、前処理が必要な値、区切り記号を明示すべき値の違いを確認します。
| 基本 | =VALUE(A2) |
|---|---|
| 前処理 | =VALUE(SUBSTITUTE(SUBSTITUTE(ASC(A2),",",""),"円","")) |
| 海外形式 | =NUMBERVALUE(A2,",",".") |
| 一括処理 | =MAP(A2:A10,B2:B10,LAMBDA(value,purpose,...)) |
最初に、記事全体で使用するサンプルデータを作る
A列に「元データ」、B列に「形式」を入力します。単純な数字文字列だけでなく、全角数字、通貨、パーセント、単位、記号付き負数、海外形式を混ぜています。
| A | B | |
|---|---|---|
| 1 | 元データ | 形式 |
| 2 | 1234 | 通常数値 |
| 3 | １，２３４ | 全角数値 |
| 4 | 1,234円 | 通貨・単位 |
| 5 | ￥3,600 | 通貨・単位 |
| 6 | 12.5% | パーセント |
| 7 | 2,500個 | 通貨・単位 |
| 8 | ▲450 | 記号負数 |
| 9 | 1.234,56 | 海外形式 |
| 10 | 欠品 | 非数値 |
文字列として確実に入力するため、Excel上では先頭にアポストロフィを付けるか、次のような数式で再現します。
| A2 | ="1234" |
|---|---|
| A3 | ="１，２３４" |
| A4 | ="1,234円" |
| A5 | ="￥3,600" |
| A6 | ="12.5%" |
| A7 | ="2,500個" |
| A8 | ="▲450" |
| A9 | ="1.234,56" |
| A10 | ="欠品" |
以降はA2:A10を固定して使用します。C列へVALUEの結果、D列へ前処理後の結果、E列へ用途別の最終結果、F列以降へ検証結果を追加します。
POINT
文字列数値の修正では、最初に「数字以外の何が含まれているか」を確認します。変換関数より先に、文字列の構造を仕分けすることが重要です。
文字列の数字は、計算・並べ替え・集計を不安定にする
「1234」と表示されていても、Excel内部で数値の1234として保存されている場合と、4文字の文字列として保存されている場合があります。セルの表示だけでは区別しにくいため、次のような問題が起こります。
- SUM・AVERAGEなどの計算対象から外れる
- 数値順ではなく文字列順に並べ替えられる
- 大小比較や条件付き集計が期待どおり動かない
- XLOOKUPで数値の1234と文字列の「1234」が一致しない
- ピボットテーブルで同じ値が別項目として扱われる
- グラフや統計処理で欠損扱いになる
文字列数値の修正は、見た目を変える処理ではありません。計算対象として扱えるデータ型へ変換するデータクレンジングです。
VALUE関数で、そのまま解釈できる文字列を数値へ変換する
C1に「VALUE結果」と入力し、C2へ次の数式を入力してC10までコピーします。
| C2 | =IFERROR(VALUE(A2),"変換不可") |
|---|
VALUE関数は、数値として解釈できる文字列を数値へ変換します。ただし、文字列に独自の単位や未対応の記号が含まれている場合はエラーになります。ここではIFERRORを使い、変換できない行を「変換不可」と表示します。
| A | B | C | |
|---|---|---|---|
| 1 | 元データ | 形式 | VALUE結果 |
| 2 | 1234 | 通常数値 | 1234 |
| 3 | １，２３４ | 全角数値 | 1234 |
| 4 | 1,234円 | 通貨・単位 | 変換不可 |
| 5 | ￥3,600 | 通貨・単位 | 3600 |
| 6 | 12.5% | パーセント | 0.125 |
| 7 | 2,500個 | 通貨・単位 | 変換不可 |
| 8 | ▲450 | 記号負数 | 変換不可 |
| 9 | 1.234,56 | 海外形式 | 変換不可 |
| 10 | 欠品 | 非数値 | 変換不可 |
A2（通常数値）・A3（全角数値）・A5（￥表記）・A6（パーセント）はVALUEだけで変換できます。Excelの日本語環境では、全角数字・全角カンマ・￥記号をVALUEが解釈できるためです。一方、「円」「個」などの和文単位、記号付き負数、海外形式の区切り記号には前処理または別の関数が必要です。
PRACTICAL NOTE
VALUEは「数字に見える文字列をすべて直す万能関数」ではありません。Excelが現在の地域設定で数値として解釈できる文字列だけを変換します。
ASC・SUBSTITUTEで、数値化を妨げる文字を先に取り除く
A3やA5はVALUEで変換できますが、実務では全角・半角が混在したデータが届くこともあります。ASCで半角へ統一してからSUBSTITUTEで単位・記号を除去する前処理を挟むことで、どの形式でも同じ処理規則を適用できます。A4・A7のように和文の単位が含まれる場合はVALUEだけでは変換できないため、前処理が必須です。
| 全角数値 | =VALUE(SUBSTITUTE(ASC(A3),",","")) |
|---|---|
| 円表記 | =VALUE(SUBSTITUTE(SUBSTITUTE(ASC(A4),",",""),"円","")) |
| 個数表記 | =VALUE(SUBSTITUTE(SUBSTITUTE(ASC(A7),",",""),"個","")) |
すべての式でASCを最初に通しています。A4の「1,234円」の桁区切りはすでに半角カンマのためASCなしでも動作しますが、全角文字が混入した場合も含めて一律にASCで半角へ寄せてから処理すると、規則を統一しやすくなります。なお、A5（￥3,600）はVALUEで直接3600に変換できるため、前処理は不要です。
会計データでは、マイナス値を「-450」ではなく「▲450」と表すことがあります。記号を単に削除すると正の450になってしまうため、先頭の負数記号を判定して符号を反転します。
| A8用 | =LET(source,A8,isNegative,LEFT(source,1)="▲",numberText,IF(isNegative,MID(source,2,LEN(source)-1),source),number,VALUE(SUBSTITUTE(numberText,",","")),IF(isNegative,-number,number)) |
|---|
この式では、先頭が「▲」なら記号を除いた数値部分を取り出し、VALUEで数値化したあとにマイナスを付けます。見た目だけを削除するのではなく、記号が持つ意味を数値へ反映することが重要です。
NUMBERVALUE関数で、小数点と桁区切りを明示する
A9の「1.234,56」は、ピリオドが桁区切り、カンマが小数点を表す形式です。日本語環境のExcelではそのまま正しく解釈できないため、NUMBERVALUE関数で区切り記号を明示します。
| A9用 | =NUMBERVALUE(A9,",",".") |
|---|
第2引数に小数点記号、第3引数に桁区切り記号を指定します。結果は1234.56です。NUMBERVALUEは、ExcelやWindowsの地域設定に依存せず、文字列内で使われている記号を数式側から指定できます。
💡 区切り記号を削除する前に、小数点か桁区切りかを確認する
「1.234,56」のピリオドを機械的に削除し、カンマも削除すると「123456」になります。記号の意味を確認せずにSUBSTITUTEを重ねると、桁を100倍変えてしまう危険があります。
形式列を使って、同じサンプル表に用途別の最終結果を作る
E1に「最終結果」と入力し、E2へ次の数式を入力してE10までコピーします。
| E2 | =LET(source,A2,purpose,B2,halfWidth,ASC(source),plain,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(halfWidth,",",""),"円",""),"¥",""),"個",""),isNegative,LEFT(plain,1)="▲",core,IF(isNegative,MID(plain,2,LEN(plain)-1),plain),IFS(purpose="海外形式",NUMBERVALUE(source,",","."),purpose="非数値",NA(),purpose="パーセント",VALUE(source),isNegative,-VALUE(core),TRUE,VALUE(core))) |
|---|
式の中では、ASCで全角数字・記号を半角へ寄せ、SUBSTITUTEで通貨記号・単位・桁区切りを削除します。「▲」はこの段階では除去せず plain
に残しておき、isNegative で先頭文字を判定してから符号処理します。その後、形式に応じてVALUEまたはNUMBERVALUEを使い分けます。
| A | B | E | |
|---|---|---|---|
| 1 | 元データ | 形式 | 最終結果 |
| 2 | 1234 | 通常数値 | 1234 |
| 3 | １，２３４ | 全角数値 | 1234 |
| 4 | 1,234円 | 通貨・単位 | 1234 |
| 5 | ￥3,600 | 通貨・単位 | 3600 |
| 6 | 12.5% | パーセント | 0.125 |
| 7 | 2,500個 | 通貨・単位 | 2500 |
| 8 | ▲450 | 記号負数 | -450 |
| 9 | 1.234,56 | 海外形式 | 1234.56 |
| 10 | 欠品 | 非数値 | #N/A |
非数値の「欠品」は0へ変換せず、NA関数で欠損として残しています。文字列を無理に0へ置き換えると、実際に数量が0だった行と、値が得られなかった行を区別できなくなるためです。
IMPORTANT
「欠品」「不明」「未回答」などを0へ変換するか、欠損として残すかは分析結果を変えます。数値化できない値を一律に0へ置換するのは危険です。
LET・MAPで、形式別の変換規則を一か所へまとめる
STEP 7の数式では、変数名を
source、purpose、halfWidth、plain、isNegative、core
に統一しています。
| source | 元の文字列 |
|---|---|
| purpose | 形式・処理区分 |
| halfWidth | ASCで半角へ標準化した文字列 |
| plain | 桁区切り・通貨記号・単位を除去した文字列 |
| isNegative | 記号付き負数かどうか |
| core | 負数記号を除いた数値部分 |
LET関数によって結果が変わるわけではありません。文字列の標準化、不要文字の除去、符号判定、数値化という工程を、数式内で追跡しやすくすることが目的です。
F1に「MAP一括処理」と入力し、F2へ次の数式を入力します。
| F2 | =MAP(A2:A10,B2:B10,LAMBDA(value,purpose,LET(halfWidth,ASC(value),plain,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(halfWidth,",",""),"円",""),"¥",""),"個",""),isNegative,LEFT(plain,1)="▲",core,IF(isNegative,MID(plain,2,LEN(plain)-1),plain),IFS(purpose="海外形式",NUMBERVALUE(value,",","."),purpose="非数値",NA(),purpose="パーセント",VALUE(value),isNegative,-VALUE(core),TRUE,VALUE(core))))) |
|---|
結果はF2:F10へスピルします。変換規則を一つの数式へ集約できるため、単位や形式が追加された場合も修正箇所を限定できます。IFSの条件には isNegative
のような論理値をそのまま渡せるため、isNegative=TRUE のように書き直す必要はありません。
※MAP関数を利用できないExcelでは、STEP 7の数式を各行へコピーしてください。
💡 数式を長くしすぎないため、形式ごとに列を分ける設計も有効
実務では、通貨、割合、数量、海外形式を同じ列へ混在させない方が安全です。本稿の式は混在データを説明するための例であり、可能なら入力段階で列や形式を分けてください。
変換後に、本当に数値になったかを検証する
G1に「数値判定」と入力し、G2へ次の数式を入力してG10までコピーします。
| G2 | =IFERROR(IF(ISNUMBER(E2),"数値","非数値"),"非数値") |
|---|
表示が数字になっただけでは、数値へ変換できたと断定できません。ISNUMBER関数で数値かどうかを判定し、NA関数による#N/AなどのエラーはIFERRORで「非数値」として表示します。
| A | B | E | G | |
|---|---|---|---|---|
| 1 | 元データ | 形式 | 最終結果 | 数値判定 |
| 2 | 1234 | 通常数値 | 1234 | 数値 |
| 3 | １，２３４ | 全角数値 | 1234 | 数値 |
| 4 | 1,234円 | 通貨・単位 | 1234 | 数値 |
| 5 | ￥3,600 | 通貨・単位 | 3600 | 数値 |
| 6 | 12.5% | パーセント | 0.125 | 数値 |
| 7 | 2,500個 | 通貨・単位 | 2500 | 数値 |
| 8 | ▲450 | 記号負数 | -450 | 数値 |
| 9 | 1.234,56 | 海外形式 | 1234.56 | 数値 |
| 10 | 欠品 | 非数値 | #N/A | 非数値 |
数値化に成功しても、変換結果が意味として正しいとは限りません。特に次の点を確認します。
- パーセントの12.5%を12.5ではなく0.125として扱うか
- 「▲」付き数値を負数と解釈してよいか
- 海外形式の小数点・桁区切りの指定が正しいか
- 商品コードの先頭ゼロを数値化して消してよいか
- 「欠品」「不明」「未回答」を0へ置き換えてよいか
IMPORTANT
郵便番号、商品コード、会員番号などは、数字だけで構成されていても計算対象ではありません。「00123」を数値化すると「123」になり、識別子としての意味を失います。数字に見える文字列をすべて数値へ変換するのも危険です。
まとめ ―― 数値化の前に、文字列の形式と意味を仕分けする
本稿ではA2:A10の同じサンプルデータを使い、VALUEによる基本変換、ASC・SUBSTITUTEによる前処理、記号付き負数の符号処理、NUMBERVALUEによる海外形式の変換、LET・MAPによる一括化、ISNUMBERによる検証まで進めました。
| 基本 | =VALUE(A2) |
|---|---|
| 全角対応 | =VALUE(SUBSTITUTE(ASC(A2),",","")) |
| 単位除去 | =VALUE(SUBSTITUTE(SUBSTITUTE(A2,",",""),"円","")) |
| 海外形式 | =NUMBERVALUE(A2,",",".") |
| 数値判定 | =IFERROR(IF(ISNUMBER(E2),"数値","非数値"),"非数値") |
| MAP一括 | =MAP(A2:A10,B2:B10,LAMBDA(value,purpose,LET(...))) |
- 単純な数字文字列：VALUEで数値へ変換する。
- 全角数字：ASCで半角へ統一してから数値化する。
- 通貨記号・単位：SUBSTITUTEで意味のない文字だけを除去する。
- パーセント：表示値ではなく、計算に使う内部値を確認する。
- 記号付き負数：負数を表す記号を削除するだけでなく、その意味を負の符号として数値へ反映する。
- 海外形式：NUMBERVALUEで小数点と桁区切りを明示する。
- 非数値：0へ置換せず、欠損として残す選択肢を持つ。
- 識別子：郵便番号や商品コードは、数字だけでも文字列のまま保持する。
- 検証：ISNUMBERで本当に数値へ変換されたか確認する。
数値として認識されない文字列を直す目的は、数字らしい見た目へ整えることではありません。値の形式と業務上の意味を確認し、計算すべき値だけを正しい数値へ変換することが重要です。