Excelの合計が合わない原因｜SUMの範囲・文字列・小数点・非表示行を確認

ExcelでSUM関数を使ったのに、手計算や想定していた合計と数字が合わないことがあります。

この状態になると、

• 数式が間違っているのか
• 入力した数字に問題があるのか
• Excelの計算がおかしいのか

を切り分けられず、表全体を確認して余計な時間を取られがちです。

私自身も、表をコピーして使った際に参照セルがずれ、別の範囲を合計していたことがあります。また、貼り付けた数字が文字列になり、一部のセルだけ合計されていないこともありました。

Excelの合計が合わないときは、最初からすべてのセルを見直す必要はありません。

次の順番で確認すると、原因を切り分けやすくなります。

1. SUM関数の合計範囲
2. 文字列になっている数字
3. 非表示行やフィルター
4. 表示されていない小数点以下

この記事では、Excelの合計が合わない原因と直し方を、確認する順番に沿って解説します。

Excelの合計が合わない原因を、ずれ方から絞る

最初に、合計がどのようにずれているかを確認します。

原因が分からない場合は、まずSUM関数の範囲から確認してください。

数式の範囲は目で確認しやすく、行の追加やコピーによるずれも起こりやすいためです。

最初にSUM関数の合計範囲を確認する

合計セルを選択し、数式バーに表示されているSUM関数を確認します。

たとえば、次の数式ではB2からB10までが合計対象です。

=SUM(B2:B10)

本来B11まで合計する必要がある場合、B11は合計に含まれていません。

Microsoftの公式サポートでも、SUM関数は指定した数値やセル範囲を合計する関数と説明されています。

参考：SUM関数｜Microsoftサポート

表をコピーした後は参照セルを確認する

私が実際に経験したのは、以前作った表をコピーして使った際に、参照セルが想定と違う場所へずれていたケースです。

見た目は同じ表でも、コピー先や数式の移動方法によって、参照範囲が変わることがあります。

一部の列だけ合計が合わない場合は、正しい列と合わない列の数式を見比べます。

たとえば、C列の合計なら、次のようにC列を参照しているか確認します。

=SUM(C2:C10)

B列を参照したままになっていたり、開始行や終了行が他の列と違っていたりすると、合計も変わります。

後から追加した行が範囲外になっていないか確認する

表の下へデータを追加しても、SUM関数の範囲が自動で広がっていない場合があります。

行を追加した後は、数式の最後のセルが最新の行まで含んでいるか確認してください。

不要な行を含めていないか確認する

合計が想定より多い場合は、次のようなデータを一緒に足していないか確認します。

• 途中に作った小計行
• 同じ内容が入力された重複行
• 前回分として残しているデータ
• 確認用に入力した数字

明細と小計を同じ範囲で合計すると、同じ数字を二重に足すことになります。

数字が文字列になっていないか確認する

SUM関数の範囲が正しいのに合計が少ない場合は、数字が文字列になっていないか確認します。

Excelでは、見た目が「100」でも、セルの中では数値ではなく文字列として扱われることがあります。

私も、別の表から数字をコピーして貼り付けた際に、一部のセルが文字列になり、SUM関数の合計から抜けていたことがあります。

文字列になりやすいのは、次のような数字です。

• CSVから取り込んだ数字
• 他のシステムからコピーした数字
• 先頭にアポストロフィが付いている数字
• 表示形式を「文字列」にしたセルへ入力した数字

緑色の三角形を確認する

文字列として保存されている数字には、セルの左上に緑色の三角形が表示されることがあります。

対象セルを選択し、警告アイコンから「数値に変換」を選びます。

参考：文字列として保存されている数値を数値に変換する｜Microsoftサポート

変換した後は、SUM関数の結果が変わったか確認してください。

VALUE関数で数値へ変換する

警告が表示されない場合は、VALUE関数を使って文字列を数値へ変換できます。

B2の文字列を数値へ変える場合は、次の式を使います。

=VALUE(B2)

変換結果を元の列へ戻す場合は、結果をコピーして「値として貼り付け」ます。

非表示行やフィルター後の行を確認する

画面に表示されている数字を手計算した結果と、Excelの合計が違う場合は、非表示になっている行を確認します。

SUM関数は、指定範囲内の数値を合計します。

フィルターや行の非表示によって画面から見えなくなっても、SUM関数の範囲内にある数字は合計に含まれます。

表示されている行だけを合計したい場合は、SUBTOTAL関数を使います。

SUBTOTALの9は、フィルターで除外された行を合計から外します。

109を使うと、フィルターで除外された行に加えて、手動で非表示にした行も合計から外れます。

参考：SUBTOTAL関数｜Microsoftサポート

SUMとSUBTOTALのどちらを使うかは、非表示の行を合計へ含めたいかどうかで決めます。

表示されていない小数点以下を確認する

画面上の数字とExcelの合計が数円だけ違う場合は、表示されていない小数点以下を確認します。

たとえば、次の3つの値が入力されているとします。

• 10.4
• 10.4
• 10.4

小数点以下を表示しない設定にすると、画面上ではすべて「10」に見えます。

表示された数字を手計算すると30ですが、Excelに保存されている実際の値の合計は31.2です。

表示する桁数を減らしても、セルに保存されている値そのものは変わりません。

計算する値を丸める場合はROUND関数を使う

小数点以下を計算に含めたくない場合は、表示だけではなく、ROUND関数で値を丸めます。

A2の数字を整数に四捨五入する場合は、次の式です。

=ROUND(A2,0)

参考：ROUND関数｜Microsoftサポート

ただし、各セルを先に丸めてから合計する場合と、合計後に丸める場合では結果が変わることがあります。

合計後に整数へ丸める場合は、次の式です。

=ROUND(SUM(B2:B20),0)

売上や数量など、何をどの時点で丸めるかを決めてから数式を設定してください。

Excelの合計が合わないときの確認チェックリスト

原因と思われる場所を直した後は、次の項目を確認します。

1. SUM関数の開始セルと終了セルは正しいか
2. コピーした数式の参照セルがずれていないか
3. 後から追加した行も合計範囲に入っているか
4. 文字列になっている数字が残っていないか
5. 非表示行を合計に含めるか決めているか
6. 表示されていない小数点以下を確認したか
7. 小計行や重複データを二重に足していないか

一度に表全体を調べるのではなく、

合計範囲 → 文字列 → 非表示行 → 小数点以下

の順番で確認すると、原因を見つけやすくなります。

Excelの合計が合わないときのQ&A

値を変更しても合計が変わらないのはなぜですか？

Excelの計算方法が「手動」になっている可能性があります。

「数式」タブから「計算方法の設定」を開き、「自動」になっているか確認してください。

SUMIFやSUMIFSの合計が合わない場合も同じですか？

合計範囲、文字列、小数点以下などは共通して確認できます。

ただし、SUMIFやSUMIFSでは、条件範囲と合計範囲の位置や行数、検索条件も確認する必要があります。

電卓とExcelでは、どちらの合計が正しいですか？

同じセルを同じ桁数で計算していれば、通常は一致します。

結果が違う場合は、次を確認してください。

• 同じ範囲を足しているか
• 非表示行が含まれていないか
• 表示されていない小数点以下がないか
• 小計や重複した数字を含めていないか

まとめ｜参照範囲と文字列から確認する

Excelの合計が合わないときは、まずSUM関数の参照範囲を確認します。

表や数式をコピーした後は、参照セルが想定と違う場所へ移動していることがあります。

範囲が正しい場合は、数字が文字列になっていないかを確認します。

その後に、非表示行や小数点以下を確認してください。

元になる合計が間違っていると、その後に計算する前週比や増減率も正しく判断できません。

まず合計値を信用できる状態にしてから、比較へ進みます。

次に読む記事

週ごとの合計を作成した後、前週からどれだけ増減したかを確認したい場合は、次の記事で差額・比率・増減率の違いと計算方法を確認してください。

Excelで前週比・前月比を計算する方法｜差額・比率・増減率の違い