Excelで作った表の合計が、電卓で足した数字と合わないことがあります。
1円だけずれていたり、一部の数字が足されていなかったりすると、どこが間違っているのか不安になります。
数字を直したのに合計が変わらず、困っている人もいるでしょう。
合計が合わないとき、ExcelのSUM関数が計算を間違えているわけではありません。
原因は、見た目と中身の数値の違い、文字列として入った数字、合計の範囲や計算の設定の3つに分けられます。
どれに当てはまるかは、合計がどのようにずれているかで見当をつけられます。
この記事では、ずれの原因を症状から見分けるやり方と、原因ごとの対処法を具体例で解説します。
表示形式で丸めた数値と、ROUND関数で丸めた数値の違いも紹介します。
症状に合う見出しから順に確かめていけば、多くのずれは自分で直せると考えられます。
合計を求めるSUM関数や、数値の個数を数えるCOUNT関数の基本は、関数の使い分けをまとめた記事で確認できます。
Excelで合計が合わない原因とは？ずれ方で見分けるやり方
Excelは、セルに保存されている数値をもとに計算します。
画面に見えている数字と保存されている数値が違うと、見た目の上で合計が合わなくなります。
まずは、合計がどのようにずれているかを確かめるところから始めます。
ずれ方には特徴があり、1円程度の小さなずれか、ある数字がまるごと抜けているかで原因を絞れます。
数字を直しても合計が変わらない場合は、データではなく設定の問題が考えられます。
合計が合わない症状と表示形式・文字列の見分け方
症状と考えられる原因を、次の表にまとめます。
自分の表に近い症状を探して、対応する見出しへ進んでください。
| 症状 | 考えられる原因 | 解説している見出し |
|---|---|---|
| 合計が1円や数円だけずれる | 表示形式で小数点以下が隠れている | 原因① |
| ある数字がまるごと足されていない | 数字が文字列として入っている | 原因② |
| 追加した行の数字が足されていない | 合計の範囲に新しい行が入っていない | 原因③ |
| 数字を直しても合計が変わらない | 計算方法の設定が手動になっている | 原因③ |
| 非表示にした行の数字まで足されている | SUM関数は隠れた行も合計する | 原因③ |
たとえば、税込金額の合計が1円だけずれるなら、表の1行目の小数点以下が疑わしいといえます。
ほかのシステムから貼り付けた売上の合計が少ないなら、表の2行目の文字列が考えられます。
症状が2つ以上当てはまることもあるため、上の行から順に確かめると効率的です。
合計の数式の範囲を数式バーとステータスバーで確かめるやり方
どの原因でも、最初に合計の数式がどのセルを足しているかを確かめておきます。
次の表は、費目ごとの金額を入力した例です。
| A | B | |
|---|---|---|
| 1 | 費目 | 金額 |
| 2 | 交通費 | 12000 |
| 3 | 消耗品 | 8000 |
| 4 | 通信費 | 5000 |
セルB5に次の数式を入力します。
=SUM(B2:B4)
セルB2からB4までの金額を合計する数式で、B5には25000と表示されます。
B5を選ぶと、画面上部の数式バーに数式が表示され、足している範囲がB2:B4だと確かめられます。
F2キーを押してセルを編集できる状態にしても、同じように数式を確かめられます。
次に、合計したいセルB2からB4をドラッグで選び、画面下部のステータスバーを見ます。
ステータスバーには、数式を入れなくても、選んだセルの合計が表示されます。
ここで、B5の数式が誤って次のようになっていた場合を考えてみます。
=SUM(B2:B3)
この数式はB2とB3だけを足すため、B5には20000と表示されます。
ステータスバーの合計25000との差の5000が、範囲から外れたB4の金額だと分かります。
数式とステータスバーの合計を見比べると、範囲のずれをすぐに見つけられます。
人から引き継いだ表では、見た目が整っていても、途中の行で数式の範囲が変わっていることがあります。
合計が合わないと感じたら、まず合計のセルを選んで数式バーを確認する習慣をつけると安心です。
ステータスバーに合計が出ない場合は、ステータスバーを右クリックして、表示する項目を確かめます。
表示された一覧で、「合計」にチェックが入っているかを見てください。
チェックが入っていれば、セルを選ぶだけで合計が表示されるようになります。
数式バーに表示される数式の読み方や、セル参照の考え方は、数式の基本をまとめた記事で確認できます。
合計が合わない原因①｜表示形式で小数点以下が隠れている
表示形式は、セルの数値をどのように見せるかを決める設定です。
表示形式で小数点以下を隠しても、セルに保存されている数値はそのまま残ります。
SUM関数は保存されている数値で計算するため、見えている数字を足した結果と合わないことがあります。
表示形式で小数点以下を隠すと合計がずれる具体例
次の表は、単価に消費税の10％を加えた税込金額を求める例です。
| A | B | |
|---|---|---|
| 1 | 商品 | 単価 |
| 2 | 商品A | 1234 |
| 3 | 商品B | 2234 |
| 4 | 商品C | 3234 |
セルC2に次の数式を入力し、C4までコピーします。
=B2*1.1
単価に1.1を掛けて税込金額を求める数式で、C2からC4には1357.4、2457.4、3557.4と表示されます。
続けて、セルC5に次の数式を入力します。
=SUM(C2:C4)
税込金額を合計する数式で、C5には7372.2と表示されます。
ここで、C2からC5を選び、「ホーム」タブの「小数点以下の表示桁数を減らす」を1回押します。
すると、C2からC4は1357、2457、3557、C5は7372という整数の表示になります。
見えている3つの金額を足すと7371なのに、合計は7372と表示され、1円ずれて見えます。
隠れている小数点以下の0.4が3つ分足され、7372.2になっているためです。
ROUND関数の数式で四捨五入して合計を合わせるやり方
見えている金額どおりの合計にしたい場合は、税込金額そのものをROUND関数で丸めます。
ROUND関数は、数値を指定した桁数で四捨五入する関数です。
セルC2の数式を次のように書き換えて、C4までコピーします。
=ROUND(B2*1.1,0)
B2に1.1を掛けた結果を、小数点以下0桁、つまり整数に四捨五入する数式です。
C2からC4には1357、2457、3557が保存され、C5の合計は7371になります。
見えている数字と保存されている数値が同じになるため、電卓で足した結果とも一致します。
構成比の表示形式で合計が100％と合わなく見える場合
小数点以下が隠れることによる見た目のずれは、割合を求める表でも起こります。
次の表は、3つの店舗の売上が同じ150000だった場合の例です。
| A | B | |
|---|---|---|
| 1 | 店舗 | 売上 |
| 2 | 本店 | 150000 |
| 3 | 駅前店 | 150000 |
| 4 | 郊外店 | 150000 |
セルB5には、合計の450000を求める次の数式を入力しておきます。
=SUM(B2:B4)
セルC2に次の数式を入力し、C4までコピーします。
=B2/$B$5
各店舗の売上を合計で割り、構成比を求める数式です。
$を付けた$B$5は、数式をコピーしても参照先がずれない絶対参照です。
C2からC4を、小数点以下を表示しないパーセント表示にすると、どれも33％と表示されます。
続けて、セルC5に次の数式を入力します。
=SUM(C2:C4)
C5には100％と表示されますが、見えている33％を3つ足すと99％です。
保存されている値は33.333…％のため、合計はちょうど100％になります。
この場合は合計が正しいため、表示桁数を増やして33.3％まで見せると、読む人の誤解を防げます。
請求書や見積書では、1件ごとの金額を丸めてから合計するか、合計してから丸めるかを決めておきます。
どちらを選ぶかで、合計が1円変わることがあるためです。
取引先の書類と合わせたい場合は、相手の計算方法を確かめてからROUND関数を入れる位置を決めると安心です。
表示形式を変えても、セルの数値そのものは変わりません。
小数点以下が隠れているか分からないときは、セルを選んで「小数点以下の表示桁数を増やす」を押します。
隠れていた数字が表示されれば、このずれが原因だと判断できます。
ROUND関数の桁数の指定のしかたや、表示形式で丸める方法との違いは、ROUND関数の記事で詳しく紹介しています。
合計が合わない原因②｜数字が文字列として入っている
Excelのセルには、計算に使える数値のほかに、文字として扱われる文字列も入力できます。
見た目は数字でも、文字列として入っているセルは、SUM関数の合計に入りません。
SUM関数は、範囲の中の文字列を無視して、数値だけを合計するしくみだからです。
文字列の数字は、ほかのソフトからデータを貼り付けたときによく生まれます。
表示形式を「文字列」にしたセルに数字を入力したときも、同じように文字列になります。
セルの左上に緑色の三角形が出ていたり、数字が左に寄っていたりするのが見分けるサインです。
緑色の三角形はエラーインジケーターと呼ばれ、入力した内容にエラーの可能性があることを知らせる目印です。
文字列の数字が混ざった範囲の合計が小さくなる具体例
最初の見出しと同じ、費目ごとの金額の表を使います。
| A | B | |
|---|---|---|
| 1 | 費目 | 金額 |
| 2 | 交通費 | 12000 |
| 3 | 消耗品 | 8000 |
| 4 | 通信費 | 5000 |
表の3行目のB3の8000は、ほかのシステムから貼り付けた文字列だったとします。
セルB5に次の数式を入力します。
=SUM(B2:B4)
本来は25000になるはずが、B5には17000と表示されます。
SUM関数が文字列のB3を飛ばし、B2の12000とB4の5000だけを足したためです。
一方で、同じセルを＋でつないだ次の数式では、結果が変わります。
=B2+B3+B4
＋などの計算の記号には、数字の文字列を数値に変えて計算するしくみがあるため、25000と表示されます。
SUM関数と＋の数式で結果が違うときは、範囲に文字列の数字が混ざっていると考えられます。
COUNT関数の数式で文字列のセルを見つけるやり方
緑色の三角形が表示されない設定のファイルでも、COUNT関数を使えば文字列のセルがあるかを確かめられます。
COUNT関数は、数値が入ったセルの個数を数え、文字列のセルは数えない関数です。
セルB6に次の数式を入力します。
=COUNT(B2:B4)
B2からB4のうち数値のセルだけを数える数式で、B6には2と表示されます。
続けて、セルB7に次の数式を入力します。
=COUNTA(B2:B4)
空白ではないセルをすべて数える数式で、B7には3と表示されます。
2つの結果の差の1が、文字列として入っているセルの数です。
「数値に変換する」で範囲の数字をまとめて直す手順
文字列の数字は、エラーインジケーターのメニューから数値に直せます。
手順は次のとおりです。
1. 緑色の三角形が出ているセル（ここではB3）を選ぶ。複数ある場合はまとめて選ぶ
2. 選んだセルの横に表示される「！」のアイコンをクリックする
3. 表示されたメニューから「数値に変換する」をクリックする
B3が数値の8000に変わり、B5の合計は25000、B6のCOUNT関数の結果は3になります。
CSVなどほかのシステムのデータを貼り付けたときは、合計を出す前にCOUNT関数で数値の個数を確かめておくと安心です。
件数の多い表ほど、1つだけ文字列が混ざっていても気づきにくいためです。
メニューの「エラーを無視する」を選ぶと、緑色の三角形は消えますが、セルは文字列のままです。
三角形が消えても合計に入らない状態は変わらないため、合計を合わせたいときは「数値に変換する」を選びます。
また、表示形式を数値に変えただけでは、すぐに数値として扱われないことがあります。
表示形式が「文字列」のセルで数式が計算されない場合の直し方は、数式が反映されない原因をまとめた記事でも紹介しています。
合計が合わない原因③｜合計の範囲や計算方法の設定が違う
数値も表示形式も問題ないのに合計が合わない場合は、合計の数式の範囲や、Excelの計算の設定を確かめます。
SUM関数は、指定した範囲の数値を、指定したとおりに合計するだけです。
行を追加したり、行を隠したりしたときに、自分の意図と範囲がずれていることがよくあります。
追加した行が合計の範囲に入っていない場合
次の表は、4つの費目の金額と、その合計を別の列に表示する例です。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 費目 | 金額 | 合計 | |
| 2 | 交通費 | 12000 | ||
| 3 | 消耗品 | 8000 | ||
| 4 | 通信費 | 5000 | ||
| 5 | 印刷費 | 3000 |
セルD2には、次の数式が入っているとします。
=SUM(B2:B4)
5行目の印刷費をあとから追加したため、範囲がB4までのままで、D2には25000と表示されます。
本来の合計の28000にするには、D2の数式を次のように直します。
=SUM(B2:B5)
B2からB5までの4つの金額を合計する数式で、D2には28000と表示されます。
範囲のすぐ隣に数値があると、合計のセルに緑色の三角形が表示されることがあります。
その場合は、表示されるアイコンから「数式を更新してセルを含める」を選ぶと、範囲を広げられます。
あとから行を足すときは、範囲の最後の行の上に挿入すると、SUM関数の範囲も自動で広がります。
範囲の外に足した行は、自動では合計に入らない点に注意が必要です。
計算方法が手動で数式の合計が更新されない場合
数字を直しても合計が変わらないときは、Excelの計算方法の設定が手動になっている可能性があります。
手動のときは、セルの値を変えても、数式が自動で計算し直されません。
たとえば上の表で、B2の12000を15000に直しても、D2の合計が28000のまま変わらない状態です。
計算方法を自動に戻す手順は次のとおりです。
1. 「数式」タブをクリックする
2. 「計算方法の設定」をクリックする
3. 表示された一覧から「自動」を選ぶ
自動に戻すと、値を変えるたびに合計が計算し直されるようになります。
設定を変えずにその場で計算し直したいときは、F9キーを押すと、D2が31000になります。
重いファイルや、ほかの人が手動に変えて保存したファイルでは、手動のままになっていることがあります。
非表示の行も範囲に含めて合計するSUM関数とSUBTOTAL関数の違い
次の表は、部署ごとの売上を入力し、3行目を非表示にした例です。
| A | B | |
|---|---|---|
| 1 | 部署 | 売上 |
| 2 | 営業部 | 120000 |
| 3 | 企画部 | 80000 |
| 4 | 総務部 | 50000 |
セルB5に次の数式を入力します。
=SUM(B2:B4)
B5には250000と表示され、隠れているB3の80000も合計に入ります。
SUM関数は、非表示の行の数値も範囲に含めて合計するためです。
見えている行だけを合計したい場合は、セルB6に次の数式を入力します。
=SUBTOTAL(109,B2:B4)
109は非表示の行を除いて合計する指定で、B6には見えているB2とB4を足した170000が表示されます。
フィルターで絞り込んだ表では、109の代わりに9を指定しても、隠れた行を除いて合計できます。
行番号を右クリックして非表示にした行は、9では合計に入り、109では除かれます。
どちらを使うか迷ったら、見えている行だけを合計する109を選ぶと分かりやすいでしょう。
非表示の行の見つけ方や、SUM関数とSUBTOTAL関数の結果の違いは、非表示の行・列の記事でも紹介しています。
合計が合わないときの対処法まとめ｜見た目・データ・範囲の順に確かめる
Excelの合計が合わないときは、SUM関数の計算ではなく、表示や入力、範囲や設定に原因があります。
症状ごとの確かめ方と対処法を、もう一度整理します。
- 1円や数円だけずれる：「小数点以下の表示桁数を増やす」で隠れた数字を確かめ、必要ならROUND関数で丸める
- ある数字が足されていない：緑色の三角形やCOUNT関数で文字列を見つけ、「数値に変換する」で直す
- 追加した行が足されていない：数式バーで範囲を確かめ、新しい行まで広げる
- 数字を直しても変わらない：「数式」タブの「計算方法の設定」を「自動」に戻す
- 隠した行まで足されている：SUBTOTAL関数で見えている行だけを合計する
原因を探すときは、まず数式バーとステータスバーで合計の範囲を確かめます。
次に見た目と中身の数値の違い、最後に計算方法の設定という順で見ていくと、原因を絞り込みやすくなります。
請求書や集計表のように合計の正確さが大事な表では、作るときの工夫でずれを防げます。
端数はROUND関数で処理し、行は範囲の内側に挿入するようにしておくと、合計が合わなくなる場面を減らせます。
貼り付けたデータは、合計を出す前にCOUNT関数で数値の個数を確かめておくと安心です。
SUM関数やROUND関数、COUNT関数など、まず覚えたい関数は、Excel関数の基本まとめで一覧できます。
