SUMの結果が、電卓やシステムの数字と合わない。こういうとき、多くの人は表を上から1行ずつ見直します。42行ならまだ我慢できても、400行あると午後がまるごと消えます。
先に見たいのは表ではなく、ずれた金額そのものです。差額がいくらで、どんな形をしているか。それだけで、原因はかなりの確率で絞り込めます。この記事では、差額の形ごとに原因と確かめ方を整理し、それぞれの直し方を詳しく書いた記事へつなぎます。
最初の1分で、差額を1つのセルに出す
ここからは作例で考えます。経費精算の一覧が42行あり、B列の金額をSUMで合計すると1,286,400円。ところが経理システムから出した合計は1,324,800円でした。
まず、空いているセルに次の式を入れます。
=1324800-SUM(B2:B43)
結果は38,400。この「38,400」という数字が、ここから先の手がかりになります。
正しい合計がわからないときは、比べる相手を別に用意します。前月の同じ表、元データの件数、紙の伝票の合計など、「これは合っている」と言える数字が1つでもあれば差額は出せます。何もなければ、次に紹介する件数のチェックから入ってください。
差額を出したら、次の3つも同じ場所に並べておきます。
| 確認すること | 式 | 作例の結果 |
|---|---|---|
| 数値として数えられたセル | =COUNT(B2:B43) | 41 |
| 何か入っているセル | =COUNTA(B2:B43) | 42 |
| 文字のまま入っているセル | =SUMPRODUCT(--ISTEXT(B2:B43)) | 1 |
COUNTとCOUNTAが1つずれていて、文字のセルが1つある。差額38,400は「どこか1セルが計算に入っていない」形をしていそうだ、と見当がつきます。この見当の付け方を、パターンごとに見ていきます。
パターンに当てはめる前に、2つだけ確かめる
差額はプラスか、マイナスか
差額の式は「正しい数字－SUMの結果」で作ります。こうしておくと、符号だけで向きがわかります。
- プラス（SUMが少ない）：計算に入っていないセルがある。文字の数字、範囲の漏れ、単位付きの入力など
- マイナス（SUMが多い）：数えすぎている。小計行の二重計算、同じ明細の重複、円と千円の混在など
向きが決まると、次の章の7つのパターンのうち、見るべきものが半分ほどに減ります。作例の38,400はプラスなので、「足りない側」の原因から当たればよいことになります。
比べた数字のほうは、本当に正しいか
意外と見落とされるのが、比べる相手の数字のずれです。システムの合計は締め日の時点の数字で、手元の表はその後に1件追加されていた。紙の伝票の束に、取り消し分が1枚混ざっていた。こういうことは珍しくありません。
確かめ方は、件数をそろえて比べることです。システムの件数が42件、表のCOUNTAも42なら、少なくとも対象の明細は同じだと考えられます。件数が違うときは、金額の差を追う前に、どの明細がどちらにしかないのかを先に洗い出してください。件数のずれを放っておくと、どのパターンにも当てはまらない差額を延々と追いかけることになります。
差額の形で分かれる7つのパターン
差額が、どこか1つのセルの値とぴったり同じ
いちばん多いのがこの形です。SUMは文字として入っている数字を黙って飛ばすので、その1セル分だけ合計が少なくなります。
見た目は数字でも、中身が文字になる理由はいくつかあります。全角で入力された「３８４００」、先頭にアポストロフィが付いた「’38400」などです。これらは文字になった数字の見分け方と直し方にまとめています。
もう1つ多いのが、数字の前後に空白が入っているケースです。「38400 」のように末尾に半角スペースがあると、セルの中では文字として扱われます。別のシステムからコピーした値や、Webの画面から貼り付けた値で起きやすく、画面ではまず気づけません。こちらは見えない空白や改行を探す方法で、LEN関数を使った見つけ方から説明しています。
確かめ方は簡単です。金額の列でフィルターを開き、差額と同じ値を探します。文字の数字は左寄せになっていることが多いので、配置でも見分けられます。
合計が0、または本来よりけた違いに小さい
列のほとんど、あるいは全部が文字になっているときの形です。COUNTが0か、COUNTAより大幅に少なくなります。
この形は、CSVファイルを開いたときや、基幹システムからデータを書き出したときに集中して起きます。1セルずつ直すより、取り込み方そのものを変えたほうが早く、翌月も同じことが起きません。手順はCSVを壊さずに取り込む方法で、取り込みの3つのルートを比べています。
「850円」「1,200円」のように単位まで打ち込まれている列も、この形になります。SUMは0を返し、エラーも出ません。単位付きで入力された金額の直し方では、表示だけに「円」を付ける設定へ切り替える方法を書いています。
合計が、本来のちょうど2倍になる
SUMの結果が正しい合計のぴったり2倍なら、ほぼ間違いなく小計の二重計算です。部署ごとや月ごとの小計行を表の途中に挟み、その列ごとSUMで足すと、明細と小計を両方数えてしまいます。
2倍ちょうどでなくても、多すぎる分が「どこかの小計行の値」と一致するなら同じ原因です。小計行が3つあって、1つだけ範囲に入っていた、という形です。
直し方は、小計と総計をSUBTOTAL関数に置き換えるのが基本です。SUBTOTALは、範囲の中にある別のSUBTOTALを数えません。詳しくは小計の二重計算を防ぐ書き方を見てください。
差額が1円から数円
金額の大きさに対して差額がとても小さいなら、端数の処理を疑います。税率を掛けた金額や按分した金額は、セルの表示が整数でも、中身に小数が残っています。表示された数字を電卓で足した結果と、Excelが中身で足した結果が1円ずれる、という形です。
確かめるには、金額の列の表示桁数を増やしてみてください。「3,300」と見えていたセルが「3,299.6」だった、ということがよくあります。行数が多い表ほど、この小さなずれが積み重なって、数円から数十円の差になります。
直し方は、計算した時点でROUND関数を入れて、中身も表示どおりの整数にそろえることです。「表示桁数で計算する」という設定もありますが、ブック全体の値が書き換わるので使い方に注意が必要です。両方の違いは小数のずれをROUNDで直す方法で説明しています。
一部の行だけ、ほかより3けた大きい（小さい）
前年と比べて経費が急に増えた、一部の支店だけ数字が飛び抜けている、という形です。差額に規則は見えませんが、特定の行だけがほかより3けた大きい（または小さい）ことがあります。
これは単位の混在です。千円単位で入れる表に誰かが円単位で入力すると、その行だけが1,000倍になります。エラーは一切出ません。=LEN(INT(B2)) のように桁数を数える列を足すと、ほかより3けた多い行が浮かび上がります。続きは円と千円が混ざった列を見つける方法にあります。
全体の合計は合うのに、条件を付けた合計だけ合わない
SUMの総額は合っているのに、SUMIFやSUMIFSで「東京だけ」「10月だけ」に絞ると数字がずれる形です。この場合、金額の列ではなく条件の側に原因があります。
よくあるのは次の3つです。
- 「東京」と「東京 」のように、条件の列に表記ゆれや空白がある
- データを足したときに、合計範囲と条件範囲が古い行数のまま止まっている
- 結合セルを使っていて、2行目以降の条件が空欄になっている
前の2つはSUMIFSが合わないときの3つの原因で、確認の式つきで説明しています。結合セルが原因のときは、結合を外したあとの空欄を埋める作業が必要です。結合セルを外して値を残す方法を参照してください。
差額に規則がない、毎月ちがう額だけずれる
ここまでのどれにも当てはまらないときは、表の作り方を疑います。
1つは、合計の範囲から行が漏れているケースです。表の途中に空白行があると、Alt＋＝（オートSUM）で入れた範囲がそこで止まります。空白行が集計を止める仕組みで、範囲が切れる場面を実験しています。
もう1つは、同じ意味の列が2本以上あるケースです。「金額」と「金額（修正後）」が並んでいて、合計の式が古いほうの列を見ている、という形で、差額は修正した行の差を足したものになります。重複した列を1本にまとめる手順を見てください。
さらに、列の使い方が途中で変わっていることもあります。ある行から下だけ、備考欄に別の情報を書き始めた、といった形です。これは列の意味が途中で変わる表の直し方で扱っています。
作例で、差額38,400円の原因を特定するまで
冒頭の経費精算の作例に戻ります。手順は4つです。
- 差額を出す：
=1324800-SUM(B2:B43)→ 38,400 - 件数を比べる：COUNTが41、COUNTAが42。数えられていないセルが1つある
- 文字のセルを探す：C列に
=ISTEXT(B2)を入れて下までコピーし、フィルターでTRUEだけにする。27行目の1件が残った - そのセルを見る：B27は「38400 」。数式バーでカーソルを末尾に置くと、数字の後ろに空白が1つあった
差額とB27の値が同じなので、原因はこの1セルだけと判断できます。B27を打ち直すと、SUMは1,324,800円になり、システムの数字と一致しました。
1件だけなら打ち直せば済みますが、同じ取り込み方で毎月作る表なら、空白が混ざる仕組みのほうを直す必要があります。文字の数字が何十件もあるときは、=VALUE(TRIM(B2)) のような掃除用の列を作り、そちらを合計するほうが安全です。
原因が2つ重なっているときの分け方
差額がどのパターンにもきれいに当てはまらないとき、原因が2つ以上重なっていることがあります。たとえば、文字の数字が1件と、小計行の二重計算が1か所、という組み合わせです。
こういうときは、わかったものから1つずつ取り除き、そのたびに差額を出し直します。
- 文字の数字だけを足す：Microsoft 365やExcel 2021なら
=SUMPRODUCT(IFERROR(--B2:B43,0))-SUM(B2:B43)で、文字として入っている数字の合計が出ます - その額を差額から引く
- 残った差額で、もう一度パターンを当てはめる
一度に全部を直そうとすると、どの修正が効いたのかわからなくなります。差額が0になるまで、1つ直すたびに数字を確かめるのが、結局いちばん早い進め方です。
もう一歩踏み込んで、複数の原因を順番に切り分けたい方向けに、合わない原因を6つの分岐で絞り込む診断フローをnoteにまとめています。差額だけでは分けきれないときに使ってください。
合計が合ったあとに、もう一度だけ確かめる
差額が0になっても、そこで終わりにしないほうが安心です。合計が合ったのが偶然ということもあるからです。文字になって抜けていた1件と、二重に数えていた1件の金額がたまたま近いと、合計はほぼ合って見えます。
報告の前に、次の3点を見ておきます。
- 件数：COUNTの結果が、元データの件数と同じか
- 最大と最小：
=MAX(B2:B43)と=MIN(B2:B43)が、ありえる範囲に収まっているか（単位の混在はここで見つかります） - 空欄：
=COUNTBLANK(B2:B43)が0か。0でないなら、空欄に理由があるか
3つとも問題がなければ、表の数字はかなり信用できます。作例の経費精算なら、件数は42、最大は1件の出張費で21万円台、最小は文房具の数百円、空欄は0でした。どれも普段の月と同じ範囲です。毎月同じ表を作るなら、この3つの式を表の横に置いたままにしておくと、翌月は差額を出す前に異常に気づけます。
合計の式そのものの書き方や、オートSUMの範囲の確かめ方は、SUM関数の基本と症状別の診断で順を追って説明しています。
コメント