Excel集計の数字が合わない｜差が出た原因を順番に探す方法

Excelの集計表と元データで、合計が合わない。数式は先月のコピーだから合っているはずなのに、どこから調べればよいか分からない。差額だけを見続けて、同じセルを何度も開いてしまうことがあります。

数字が合わないときは、数式を書き換える前に「同じ対象を、同じ条件で集計しているか」を確かめます。対象期間、件数、重複、数値の扱いを順に見てから、数式の範囲や更新状態へ進みましょう。

この記事には架空の練習データと期待値を載せています。例の件数・合計は検算していますが、Excel各版での画面操作の実機検証は行っていません。機能の説明はMicrosoft公式資料に基づきます。実務ファイルは社内ルールに従って保全し、検証用コピーで確認してください。

まず「何と何が、いくら違うか」を一文にする

「売上が合わない」では、比較する対象が広すぎます。集計表のセル、照合する資料、集計条件、差額を残します。

【記録例】10月の確定分を集計した表の合計が6,000円、照合資料は6,800円で、800円少ない。どちらも税込金額の想定。取消を除く条件と、対象期間が同じかを確認中。

照合資料も常に正しいとは限りません。速報値と確定値、受注日と計上日、税込と税抜など、数字の定義が違えば、計算が正しくても一致しません。社内でどの定義を使うかは、データの担当者へ確認します。

合計だけでなく、件数と対象条件を並べる

次の順で確認すると、どの段階で差が出たかを追いやすくなります。

1. 元データの版・取得日時・対象期間が一致しているか
2. 対象にする状態や区分が一致しているか
3. 件数が一致し、重複や欠落がないか
4. 金額が数値として扱われているか
5. 数式の対象範囲や、ピボット等の更新状態が合っているか

先に表示される合計へ合わせて値を足す、最後の行を手入力で調整する、といった方法は避けます。一致して見えても、次回の更新で同じ問題が起きるためです。

練習：同じ元データから、違う集計条件を見分ける

空のシートで、次の表をA1からD6へ入力する例です。金額は通貨記号やカンマを付けず、1200のように数値で入力します。ここでは全行が同じ対象月に属するものとします。

このデータの期待値は、全5件で7,700円、取消を除いた確定4件で6,800円、東部門の確定2件で3,600円です。金額だけでなく、「何件を、何の条件で集計したか」を一緒に見ます。

1．全件合計と確定分の合計を分ける

別のセルに=SUM(D2:D6)を入れた場合、全件の期待値は7,700です。確定分だけなら、条件に対応する式の例は=SUMIF(C2:C6,"確定",D2:D6)で、期待値は6,800です。

二つの差900円は、取消のR003を含むかどうかによって生じています。7,700と6,800のどちらかが常に誤りなのではなく、提出する数字の定義に合っているかで判断します。条件付き集計の仕様はMicrosoft「SUMIF関数」で確認できます。

2．同じ金額と、重複した明細を区別する

この表には2,400円が二つありますが、R002とR005は別の伝票です。金額が同じという理由で片方を消してはいけません。

練習用コピーの7行目へR002の行全体をもう一度追加し、合計の範囲もD2:D7へ広げると、全件の期待値は10,100円、確定分は9,200円です。元の値との差は2,400円になります。伝票IDを見れば、R002が二度入っていることを確認できます。

実務では伝票IDだけで一意にならず、明細番号などとの組み合わせが必要な場合もあります。重複を判断するキーと、複数行で正しいケースを担当者へ確認し、いきなり重複削除を実行しないようにします。

3．見た目が数字でも、数値とは限らない

別の練習用コピーで、D5の800を先頭に半角のアポストロフィを付けた'800として入力してみる例を考えます。文字列として保持された場合、見た目は800でも、範囲を指定したSUMでは数値として加算されません。

元の5行を使った=SUM(D2:D6)の期待値は、この条件では6,900です。D5が数値かを調べる式の例は=ISNUMBER(D5)で、文字列ならFALSEになります。SUMがテキストを無視する仕様はMicrosoft「SUM関数」、型の判定はMicrosoft「IS関数」を参照してください。

実務のデータでは、空白や単位、全角文字など別の要因もあります。表示形式を変えるだけで解決したと考えず、変換後に件数・値・合計を照合します。商品コードや先頭にゼロのある番号まで一括で数値化しないでください。

フィルター後の合計は、何を足す式か確認する

画面で一部の行が見えなくなっても、通常のSUMの範囲からその行が自動で除かれるわけではありません。「表示している行だけ」と「元の範囲すべて」を区別します。

行方向の表で可視行を合計する例として、=SUBTOTAL(109,D2:D6)があります。元の数値データで状態を「確定」に絞った場合の期待値は6,800です。109は手動で非表示にした行も除く指定ですが、非表示の列を同じように除くものではありません。出典：Microsoft「SUBTOTAL関数」。

検算のために一時的に絞り込んだ状態と、提出時に使う条件も区別しましょう。確認したフィルター条件を記録しておけば、別の人が同じ数字を再現できます。

数式・集計の更新は、入力条件の後に見る

対象と件数が合っているのに差が残るなら、式が最終行まで含んでいるか、追加したデータが集計対象へ入っているかを確認します。ピボットテーブルや外部データを利用している場合は、参照元と更新状況も見ます。

小数のある金額では、表示上丸めた数と実際に保存された数の違いも確認します。表示だけを変えた場合と、ROUND等で計算結果を丸めた場合は同じではありません。詳細はMicrosoftの丸め表示の説明を参照してください。

仕組みが分からないまま数式や外部接続を変更しないでください。引き継いだファイルなら、Excelの数式と参照元を調べる順番で、まず入力・計算・出力の関係を整理できます。

差が見つかったら、原因と確認結果を残す

修正したセルだけでなく、原因、変更内容、修正前後の件数と合計を残します。例えば「取消を含む式だったため、確定のみの条件へ変更。5件7,700円から4件6,800円へ。照合資料と一致」のような記録です。

判断できない場合は、未確認のことを特定して引き継ぎます。「まだ合いません」より、「期間と件数は一致。金額が800円少なく、R004の金額の型がほかと違う。数値変換してよい項目か確認したい」と伝える方が、次の人も調べやすくなります。

条件付き集計やデータ整形を学び直す必要が見えたら、その課題に絞って学びます。Udemy講座を仕事の課題から選ぶ方法では、必要な機能を練習できるかを確かめるポイントを整理しています。質問や学習のために、記入済みの機密資料や個人情報を外部へ送らないでください。この記事のような架空データに置き換えて確認しましょう。

最初にするのは、数字を合わせる操作ではありません。「同じ対象・条件で比較しているか」を確かめ、差が生まれた場所を一つずつ絞ることです。