=VALUE(A2)は、A2に文字列として保存された数値を本物の数値に変えます。文字列の数値は普通に見えますが、SUM、AVERAGE、COUNTはそれを無視するので、合計が0になることがあります：
| A | B | C | |
|---|---|---|---|
| 1 | Imported | VALUE | |
| 2 | 120 | 120 | |
| 3 | 85 | 85 | |
| 4 | 240 | 240 | |
| 5 | 15 | 15 | |
| 6 | 0 | 460 |
A列の4つのセルは文字列なので、A6の合計は0です(CSVやWebページから取り込んだデータによくあるように、アポストロフィ付きで入力されています)。C列はそれぞれを変換していて、C6は本当の合計460になります。
数値が文字列として保存されているか見分ける
Excelで文字列として保存された数値は：
- セルの左側に寄ります。数値は右側に寄ります(配置を変えていない場合)。
- セルの左上に小さな緑色の三角形が付き、選択すると「数値が文字列として保存されています」という警告アイコンが表示されます。
- COUNTAでは数えられますが、COUNTでは数えられません。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value | ISNUMBER | ISTEXT | Text numbers | |
| 2 | 120 | TRUE | FALSE | 2 | |
| 3 | 85 | FALSE | TRUE | ||
| 4 | 240 | TRUE | FALSE | ||
| 5 | 15 | FALSE | TRUE |
E2は、範囲の中で入力されているのに数値ではないセルの数を数えます。ここではアポストロフィ付きで入力された2つです。数値だけのきれいな列では0を返します。
変換する4つの数式
| A | B | C | |
|---|---|---|---|
| 1 | Text | Result | How |
| 2 | 1250 | 1250 | VALUE |
| 3 | 1250 | double minus | |
| 4 | 1250 | multiply by 1 | |
| 5 | 1250 | add 0 |
どんな計算でも、Excelは文字列を数値として読もうとします。VALUEはそれを明示的に書いたものです。--(マイナス記号2つ：マイナスしてもう一度マイナス)は短いので、ほかの数式の中でよく使われます。=SUMPRODUCT(--A2:A5)は作業列なしで文字列の数値の列を合計します。VALUEは通貨記号、桁区切り、パーセント記号の付いた文字列も読めます。VALUE("$1,250")は1250、VALUE("12%")は0.12です。
| A | B | |
|---|---|---|
| 1 | Quantity (text) | Quantity |
| 2 | 125 |
やってみよう: A2の数量は文字列として取り込まれました。B2で、それを数値に変換してください。
単位や別の区切り文字が入った文字列
数値として読めないものが文字列に入っていると、VALUEは#VALUE!を返します。よくあるケースが2つあります：
| A | B | C | |
|---|---|---|---|
| 1 | Text | Fixed | Without the fix |
| 2 | 120 kg | 120 | #VALUE! |
| 3 | 1.234,5 | 1234.5 | #VALUE! |
- 単位や単語：B2のように、先にSUBSTITUTEで取り除きます。
- 小数点のカンマ：ドイツやブラジルのシステムから来た
1.234,5のような数値です。NUMBERVALUE(Excel 2013以降)は、2つ目と3つ目の引数で小数点の記号と桁区切りの記号を受け取ります。VALUEは自分のExcelの区切り文字しか知らないので、C3は失敗します。
数字の前後のスペースではVALUEは止まりませんが、Webページから来たノーブレークスペースでは止まることがあります。SUBSTITUTE(A2,CHAR(160),"")で先に取り除きます。このエラーの一般的な原因は#VALUE!を参照してください。
| A | B | |
|---|---|---|
| 1 | Amount | Total |
| 2 | 120 | |
| 3 | 45 | |
| 4 | 80 |
やってみよう: A2:A4の金額は文字列として保存された数値です。B2で、1つの数式でその合計を返してください。
数式を使わずにその場で変換する
数式は数値を新しい列に入れます。セルそのものを直すには：
- 数値に変換する。 セルを選択し(最初に選択するセルは緑色の三角形が付いたものにします)、選択範囲の横の警告アイコンをクリックして「数値に変換する」を選びます。いちばん手早い方法です。
- 区切り位置。 列を選択し、データ > 区切り位置 で、すぐに「完了」をクリックします。Excelがすべてのセルを入力し直し、文字列の数値が数値になります。
- 形式を選択して貼り付けの乗算。 空いているセルに1と入力してコピーします。文字列の数値を選択し、ホーム > 貼り付け > 形式を選択して貼り付け で「乗算」を選び、OKをクリックします。
セルの表示形式が文字列になっている(ホーム > 数値の書式 に「文字列」と表示される)ときは、先に「標準」にしてください。そうしないと、入力したものがまた文字列として保存されます。
よくある間違い: 文字列と数値の間で検索する
検索値の101は文字列の101と一致しません。2つのセルが同じに見えても、VLOOKUP、XLOOKUP、MATCHは#N/Aを返し、=A2=101はFALSEです。どちらか片方を変換して、両方を同じ種類にします。検索する列のほうが文字列なら、代わりに探す値を変換します：
=VLOOKUP(TEXT(E2,"0"), A2:C6, 3, FALSE) E2 is a number, column A holds text numbers
=VLOOKUP(--E2, A2:C6, 3, FALSE) E2 holds a text number, column A holds numbers
それぞれの側を確かめる方法はISNUMBERとISTEXTのページで紹介しています。
よくある質問
Excelで文字列を数値に変換するには？
数式なら=VALUE(A2)か=--A2です。数式を使わないなら、セルを選択し、横に表示される警告アイコンをクリックして「数値に変換する」を選びます。
ExcelでSUMが0を返すのはなぜですか？
数値が文字列として保存されていて、SUMは文字列を無視するからです。そうしたセルはたいてい左に寄っていて、小さな緑色の三角形が付いています。=VALUE(A2)で変換するか、=SUMPRODUCT(--A2:A10)で直接合計します。
「数値に変換する」が効かない、または表示されないのはなぜですか？
Excelがそれを表示するのは、数値として読める文字列だけです。Webページから来たノーブレークスペース、kgのような単位、自分のExcelが使わない小数点のカンマがあると、緑色の三角形が出ません。先に文字列をきれいにします：=VALUE(SUBSTITUTE(A2,CHAR(160),""))または=NUMBERVALUE(A2,",",".")。
小数点にカンマを使う数値を変換するには？
NUMBERVALUEを使って区切り文字を指定します。=NUMBERVALUE(A2,",",".")は1.234,5を1234.5にします。VALUEが理解するのは、自分のExcelの設定の区切り文字だけです。
