Excel約5分で読めます

ExcelのSUMが合わない|文字列になった数字を見つける

ExcelのSUMが合わないを表したイメージ
記事の内容を表したイメージ(AI生成)

セルに数字が並んでいるのに、SUMが0になる、または合計が明らかに少ない。そういうときは、数字に見えて実は「文字列」として入っているセルが混ざっているケースがほとんどです。

Excelは数字と文字列を別の種類のデータとして扱います。SUMは数値しか足さないため、文字列のセルは黙って無視されます。エラーメッセージは出ないので、気づかないまま集計が終わってしまうことがあります。

この記事では、文字列になった数字を見つける方法と、数値に戻す手順を具体的に説明します。最初にやることは「怪しいセルを目で確認する」ではなく、「関数で一括して炙り出す」です。


文字列の数字とは何か

Excelのセルには「型」があります。数値型のセルは右揃えで表示されるのがデフォルトです。文字列型のセルは左揃えになります。同じ「1000」という表示でも、右揃えなら数値、左揃えなら文字列である可能性が高い、という目安があります。

ただし、表示形式を手動で変えていると揃え方だけでは判断できません。もう一つのわかりやすいサインは、セルの左上に小さな緑色の三角印(エラーインジケーター)が出ること。これは「数字のように見えるが文字列として入っている」とExcelが警告しているものです。

文字列になる主な原因は次のとおりです。

  • 他のシステムやCSVからデータをコピーしたとき、書式ごと貼り付けられた
  • 数字の前後に半角スペースや全角スペースが入っている
  • セルの書式が「文字列」に設定された状態で入力した
  • 「'(アポストロフィ)」を先頭に付けて入力した(Excelは先頭にアポストロフィがあると強制的に文字列扱いする)

関数で文字列のセルを見つける

目視で探すのは数行のデータなら可能ですが、数百行になると現実的ではありません。関数を使って「このセルは数値か、文字列か」を判定する列を追加します。

ISNUMBER関数を使う方法

ISNUMBER(対象セル) は、セルの値が数値であれば TRUE、そうでなければ FALSE を返します。

架空の例です。A列に「申込金額」として次のデータが入っているとします。

表は横にスクロールできます

A列(申込金額)表示の揃え
5000右揃え
3000左揃え
2000右揃え
1000左揃え

B列に =ISNUMBER(A2) と入れて下にコピーすると、文字列のセル(左揃えの行)では FALSE と表示されます。これでどの行が文字列かを一目で把握できます。

COUNTIF で件数を把握する

列全体のうち何件が文字列か知りたい場合は、B列に出した FALSE の数を COUNTIF(B2:B101,FALSE) で数えると確認できます。

VALUE関数で変換後の結果を確認する

VALUE(対象セル) は文字列を数値に変換しようとする関数です。変換できれば数値が返り、できない場合は #VALUE! エラーになります。単純にスペースが入っているだけなら VALUE(TRIM(A2)) のようにTRIMと組み合わせると取り除けます。どちらにせよ、このC列の値でSUMを試すと合計が正しくなるか確かめられます。


数値に戻す手順

確認が終わったら、元のファイルをコピーして保存しておいてください。変換の操作は元に戻せないことがあるため、作業前のバックアップが安全です。また、個人情報を含む名簿などを外部サービスや生成AIに入力する前には、所属組織のルールを確認してください。

画面の表示は版や設定により異なります。メニュー名が見つからない場合は公式のヘルプで確認してください。

方法1:エラーインジケーターから変換する

緑の三角印が出ているセルを選択し、セルの左に現れる「!」マークをクリックすると「数値に変換する」という選択肢が出ることがあります。複数セルをまとめて選択してから操作すると一括で変換できます。ただし、この方法は三角印が出ていないケース(スペース込みの文字列など)には対応できません。

方法2:空白セルを使った「値貼り付け」

  1. 空いているセルに 1 と入力して、そのセルをコピーする
  2. 文字列になっているセルの範囲を選択する
  3. 「形式を選択して貼り付け」→「乗算」で貼り付ける

この操作により、選択範囲の各セルに1を掛けた結果(つまり値そのもの)が数値として上書きされます。ExcelとGoogleスプレッドシートのどちらでも使える方法です。Googleスプレッドシートでは「特殊貼り付け」という表記になっていることがあります。

方法3:VALUE関数の結果を値として貼り付ける

別の列にVALUE関数やTRIM+VALUEで変換した列を作り、その結果を「値のみ貼り付け」で元の列に上書きする方法です。

  1. 空き列(例:C列)に =VALUE(TRIM(A2)) を入力し、下にコピーする
  2. C列全体をコピーする
  3. A列を選択し、「形式を選択して貼り付け」→「値のみ」で貼り付ける
  4. C列を削除する

この手順を踏むと、スペース込みや書式起因の文字列を一通り数値に変換できます。ただし #VALUE! が出たセルは元のデータに数字以外の文字が混じっているため、そのセルだけ内容を個別に確認する必要があります。


迷いやすい例外ケース

SUMは合っているのに、一部のセルだけ足されていないケース

全体の合計が「なんとなくおかしい」と気づいたときにIBNUMBERで確認すると、中間の数行だけFALSEになっていることがあります。これは元データの一部だけ書式が違うCSVや、別のシートからコピーされた部分に由来することが多いです。こういう場合、目視では「数字に見えるのになぜ?」と原因を特定しにくいため、まずIBNUMBERで全行を一括確認する習慣をつけておくと見落としを防ぎやすくなります。

数字のように見えるが変換できない文字列

「¥5,000」や「5,000円」のように単位や記号が混じっている場合、VALUEでは #VALUE! になります。この場合はSUBSTITUTE関数で不要な文字を除去してから変換します。

=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"¥",""),",",""))

「¥」と「,」を空文字に置き換えてから数値化する式です。単位の種類に応じてSUBSTITUTEの引数を変えてください。


再発を防ぐ入力ルール

文字列になる原因の多くは、「書式が文字列に設定された状態で入力した」か「他システムからの貼り付け」です。定期的にインポートする作業がある場合は、インポート直後にISNUMBER列を追加して確認するステップを手順書に組み込んでおくと、毎回発見が遅れる状況を避けやすくなります。

CSVを取り込む際は、Excelが自動で書式を判断するため、数字のように見える列でも文字列に化けることがあります。Power QueryやGoogleスプレッドシートのIMPORTDATA関数を使う場合も書式の確認は都度必要です。どのツールでも「貼り付けた直後にISNUMBERで確認する」を習慣にしておくのが現実的な対策です。


次の行動

今開いているファイルのSUMが合わない場合は、集計対象列の隣にISNUMBER列を追加して、FALSEが出る行を確認してください。FALSEが出たセルに対して「空白セルで1を乗算」か「VALUE+TRIM」のどちらかで数値に変換し、変換後にSUMが正しくなるかをB列の合計とA列のSUMで突き合わせて確かめてください。定期的に同じデータをインポートする業務では、確認ステップを手順書に一行追加しておくと次回以降の対応が早くなります。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ