【Excel】SUMの合計が合わないとき|文字列の数字と緑の三角を確認

数字が並んでいるのにSUMの合計が小さい、または0になる場合は、その数字が文字列として保存されていないかを調べます。緑の三角は手掛かりですが、別の問題を示すこともあります。警告の内容を読み、合計すべき金額・数量のセルだけを数値へ変換してください。

緑の三角だけで原因を決めつけない

セル範囲を指定したSUMは、その範囲内の文字列を合計対象にしません。たとえばA2に数値の100、A3に文字列の200、A4に数値の300があれば、=SUM(A2:A4)の結果は400です。A3も数値に変われば600になります。見た目は数字でも、保存されている種類が違うと結果が変わります。

セルの左上に緑の三角があれば選択し、近くに出る警告ボタンの内容を確認します。「数値が文字列として保存されています」などの案内なら、このケースに当てはまります。周囲と違う数式など、別の理由で三角が出る場合もあるため、すべてを同じ処理で直さないでください。

左寄せ・右寄せだけでも判断できません。配置を手動で変えられるためです。空いた列に=ISNUMBER(A3)を入れてTRUEなら数値、=ISTEXT(A3)がTRUEなら文字列と確認できます。警告を表示しない設定でも、この方法で種類を調べられます。

まず合計の式が意図した範囲を指しているかを見てから、金額列の代表的なセルを確認します。範囲の外にある数字は、数値へ変換してもそのSUMには入りません。

数値に変える列と、文字列のまま残す列を決める

金額・個数・距離など、計算に使う列は通常数値で扱います。一方、郵便番号・顧客番号・伝票番号は、数字だけでも識別のための文字列として残すことがあります。合計に使う列へ限定して変換してください。

「00123」という商品コードを数値にすると、先頭のゼロは数値として保持されず123になります。見た目を5桁の書式へ戻せても、元の桁数が何桁だったかを一律に復元できるわけではありません。また、Excelの数値の精度は15桁なので、16桁以上の番号を安易に数値化すると桁の情報を失います。

コピーで届いた売上一覧なら、「商品コード」「数量」「金額」を分け、数量と金額だけ処理するのが基本です。混在した列を選んで緑の三角を一掃しようとしないでください。正しい文字列に出ている警告は、変換しない判断もあります。

元データを書き換える前にブックを複製します。変換後に桁が変わったと気付いた場合、直後ならCtrl+Zで戻せますが、すでに保存して閉じたあとに元の番号を推測して補うのは避け、原本から戻します。

警告メニューから「数値に変換する」を選ぶ

文字列として保存された数値と確認できた場合は、Excelの警告メニューから変換できます。最初は1セルで試して、意図する金額や数量になることを確認してから同じ列へ広げます。

1. 対象のセルを選び、警告ボタンを開きます。
2. 案内が文字列として保存された数値についてのものかを読みます。
3. 「数値に変換する」を選びます。
4. 数値の種類と合計結果を確認します。

複数セルをまとめて変えるときは、対象の範囲を選び、文字列数値の警告が出ているセルをアクティブにして操作します。見出しやコード、数式を含めないようにしてください。警告ボタンが出ない場合は、無理に繰り返さず、次の節のVALUE関数を使って結果を確認できます。

「エラーを無視する」は、警告表示を消す操作です。文字列を数値へ変換する処理ではないため、三角が消えてもSUMの結果は改善しません。また「ホーム」で表示形式を数値に変えるだけでは、既存の文字列が数値へ変わらない場合があります。

変換後はISNUMBERがTRUEになるかを見てから、合計を確認します。文字の寄せ方や三角の有無だけを成功の基準にしないのがポイントです。

元データを残すならVALUEで作業列へ変換する

A列に元の金額があるなら、B2に=VALUE(A2)を入れて下へコピーします。Excelが認識できる数値の文字列なら数値を返し、認識できなければエラーになります。変換できない行を見つけやすいため、外部データの確認にも使えます。

空欄を空欄のままにしたい場合は、=IF(A2="","",VALUE(A2))とできます。ただし、空白文字が入ったセルは空欄とは違います。「未定」「未入力」「500円」のような値をまとめて0へ変える式は、データが不足している事実を隠すので避けます。

エラーの行では、通貨記号や単位、特殊な空白、小数点と桁区切りの表記を調べます。海外のデータでは「1,234」と「1.234」の意味が異なることがあります。表記のルールを確認する前に区切り記号を全部削除すると、金額を大きく変える恐れがあります。

全行を確認できたら作業列をSUMで合計します。B列の結果を元へ戻す場合は、コピーして「形式を選択して貼り付け」の「値」を使います。式のまま参照元のA列へ貼ると循環参照などの原因になるため、値貼り付けであることを確認してください。

乗算貼り付けは数値にできる範囲へ限定する

単純な数字だけの文字列が多数ある場合は、1を掛ける「形式を選択して貼り付け」で数値にする方法もあります。ただし、あらゆる文字列を強制的に正しい数字へ変えられるわけではありません。内容が混在した列ではVALUEの作業列の方が原因を追いやすくなります。

1. 空いているセルへ数値の1を入力し、コピーします。
2. 変換する金額・数量の範囲だけを選びます。
3. 「形式を選択して貼り付け」で、貼り付けを「値」、演算を「乗算」にして実行します。
4. 確認後、補助として入力した1を削除します。

数式、コード、日付、空欄を含む広い範囲へ一括適用しないでください。選んだ範囲の内容へ直接演算するので、事前のコピーが必要です。値が意図せず変わったら直後に元へ戻し、どのセルで変化したかを見ます。

この方法を使っても変換できない値は、単位や空白などの確認へ戻ります。区切り位置の機能を使う場合も、隣の列へ分割して上書きしないよう、出力先と区切り条件を確認してから実行してください。

数値に直しても合計が変わらない場合

ISNUMBERで数値と確認できるのに合計が動かない場合は、数式バーでSUMの参照範囲を見ます。最終行を追加したのに範囲へ入っていない、別の列を合計している、といった問題は型変換では直りません。合計セルを編集して、対象範囲の色付きの枠を確認します。

合計結果が古いままなら、計算方法が手動になっていないかを確認します。「数式」→「計算方法の設定」が手動なら、再計算して変化を見るか、他のブックへの影響も確認して自動へ戻します。数式自体が文字として見える場合は、表示形式や先頭のアポストロフィ、数式の表示設定も調べます。

フィルターで非表示になった行も、通常のSUMは範囲に含まれていれば合計します。見えている行だけを足したいという目的なら、SUBTOTALなど別の集計方法を検討します。結果が大きいから文字列変換が失敗した、とは限りません。

また、小数点以下を表示していないだけで、実際の値には端数がある場合があります。表示されている整数の手計算と合計が違うときは、数式バーや小数点の表示桁を確認してください。

合計だけでなく件数と代表値を確認して完了する

変換後の合計が予定額と合っていても、偶然の相殺や対象漏れがないかを確かめます。数量や金額の数値件数はCOUNTで数えられます。見出しや合計行を除いた対象件数と比べ、空欄、未定、エラーがどの行に残っているかを確認します。

確認する代表値には、0、負数、小数、桁数の大きい金額を含めます。値の種類が違う行も正しく変換されたことを見てから、確定した出力を保存します。緑の三角が消えたという見た目だけで作業を終えないでください。

同じ問題が毎回起きるなら、CSVなどを取り込む際の列の種類を見直します。金額は数値、コードは文字列と列ごとに指定し、取り込んだ直後に代表行と合計を確認します。貼り付け先を数値書式にしただけで必ず解決するわけではないので、実際の値の種類まで確認する運用にします。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】