Excelで数字が文字列になっている時の直し方|SUMが合わない原因と一括変換

症状:見た目は数字なのに、合計が合わない

Excelで、金額や数量の列を、SUMで合計したところ、明らかに合計が小さい、あるいは0になってしまう。このような相談を受けることがあります。式を何度確認しても、間違いが見つからない。

実は、見た目が「1,000」と数字に見えるセルが、中身は「文字列」として扱われていることが、原因として、少なくありません。SUM関数は、文字列として扱われているセルを、計算の対象に入れません。そのため、見た目には数字が並んでいても、合計が実際より小さくなります。

私が受けた相談の中でも、この「見た目は1,000なのに、文字列になっていてSUMの対象外だった」というケースは、特に印象に残っています。式は正しいのに結果が合わないため、原因が見つかるまでに、時間がかかるからです。

文字列の数字かどうかを見分ける3つの方法

もう一つ、COUNT関数とCOUNTA関数を比べる方法もあります。COUNTは数値のセルだけ、COUNTAは空白以外のセルすべてを数えます。同じ範囲で、COUNTAのほうが大きければ、数値以外のもの(文字列など)が混ざっているということです。

主な原因

直し方5つ

状況に応じて、次の5つから選びます。作業の前に、必ず元のファイルのコピーを保存してください。

方法① 「数値に変換」のマークを使う(少ない数のセル向け)

セルを選ぶと、左側に、黄色いひし形の警告マークが出ます。これをクリックし、「数値に変換」を選びます。範囲をまとめて選んでから行えば、一括で変換できます。

方法② 「区切り位置」を使う(列全体を直す時)

1. 直したい列を、選択する
2. 「データ」タブの「区切り位置」をクリックする
3. 表示される画面で、設定は変えずに、そのまま「完了」までクリックする

列全体が、数値に変換されます。1列ずつの作業になる点に、注意してください。

方法③ 「1を掛ける」(範囲を、まとめて直す時)

1. 空いているセルに「1」と入力し、コピーする
2. 直したい範囲を選択する
3. 右クリックから「形式を選択して貼り付け」を開く
4. 演算の「乗算」を選び、「OK」をクリックする

文字列の数字に1を掛けることで、数値に変わります。作業後、空いたセルの「1」は削除してください。

方法④ VALUE関数を使う(別の列に、直した値を作る時)

=VALUE(C2) と入力すると、C2の文字列を、数値に変換した値が返ってきます。元の列を残したまま、別の列で数値を作りたい場合に、便利です。

方法⑤ スペースや全角数字が原因の場合

前後にスペースが入っている時は、=TRIM(C2) で、余分なスペースを取り除けます。全角の数字は、=ASC(C2) で、半角に変換できます。両方の問題があるなら、組み合わせて、=VALUE(TRIM(ASC(C2))) のように使います。通常のスペースではない、特殊なスペースが入っている場合は、SUBSTITUTE 関数で除く方法もあります。

「標準」に変えても、直らない理由

「表示形式を『標準』に変更したのに、数値にならない」という質問も、よくあります。表示形式が「文字列」のセルでは、後から形式を変えても、すでに入っている値は、文字列のままです。値を数値にするには、セルを選んでF2キーを押し、Enterキーで確定し直す必要があります。多くのセルがあるなら、上の方法②や③が、現実的です。

直さないほうがよい数字もある

注意したいのは、数字に見えても、計算しない値です。

• 会員番号、社員番号
• 郵便番号、電話番号
• 先頭が「0」で始まる番号

これらを数値に変換すると、先頭のゼロが消えてしまいます。計算に使わない番号は、文字列のままにしておくのが、正しい扱いです。

再発を防ぐコツ

• 他のシステムから取り込む時は、列の形式を、取り込みの段階で指定する
• 見た目だけで、判断しない:合計が合わない時は、式より先に、データの中身(文字列か数値か)を疑う
• 作業前に、COUNTとCOUNTAで、件数を比べる習慣をつける
• 計算に使う列には、数値だけを入れる:単位や注記は、別の列に書く

AIに相談する時の注意

Excelの式をAIに相談する時も、同じです。式が正しくても、データが文字列では、結果は合いません。式を直しても結果が変わらない時は、「式が間違っている」と決めつけず、データ側の問題を、先に疑ってください(ExcelでAIに関数を書かせるコツ)。表の中身を貼る時は、個人情報や取引先の情報が含まれていないか、確認してください。

まとめ

• 見た目が数字でも、中身が文字列だと、SUMなどの計算の対象にならない
• 見分けは、左寄せ・緑の三角・ISNUMBER関数
• 直す方法は、数値に変換マーク・区切り位置・1を掛ける・VALUE関数・TRIMとASC
• 会員番号や電話番号は、文字列のままにしておく
• 作業前に、元のファイルをコピーしておく

Excelでよくつまずく点は、Excel初心者がよくつまずくポイントでも解説しています。

関連記事:文字化けの直し方

関連記事:Excelの数式が計算されない時の原因と直し方

関連記事:Excelで並べ替えできない原因と直し方

関連記事:ExcelでCSVを開くと文字化けする時の直し方