SUMIFSの範囲サイズがずれて、月次集計の数字が合わなかった話

「今月の数字、ちょっと合わなくない？」上司からの何気ない一言です。また、これで背筋が凍る思いをしたことがあります。特に経理や総務をしていると、こういう瞬間があります。月次報告の数字が合わないのは恐ろしいものです。私は集計用のExcelシートを前に考え込みました。しかも、何時間もかけて作り上げた代物です。何度も溜息をつくばかりでした。使用したのはお馴染みのSUMIFS関数です。複数条件に一致する金額を合計してくれます。条件が増えた集計には助かる関数です。しかし計算結果が手元の電卓と一致しませんでした。

そこで、原因を探そうと数式を隅々まで確認します。ところが、条件の指定やセルの参照先は正しいように見えました。Excelのバグではないかと現実逃避したくなります。どこを直せばいいのか分からなかったのです。結局その日は深夜まで残業することになりました。そして、一点ずつ明細を突き合わせる羽目になりました。その結果たどり着いたのが、「SUMIFSの範囲サイズ不一致」という落とし穴でした。

実は、数式の書き方そのものは間違っていないことが多いです。むしろ、参照している「範囲の大きさ」が原因のこともあります。同じところでつまずく人がいたら、少しでも早く原因にたどり着いてほしいと思いました。提出前の数字に自信を持ってほしいのです。そこで、自分が次から確認するようにしたポイントを、順番に残しておきます。

本記事にはプロモーション（アフィリエイトリンク）を含みます。

Excel集計ミス、参照範囲のずれ

まず確認する範囲を固定する

Excelで集計作業をしている時のことです。計算結果が期待通りにならないと焦ってしまいます。そして、つい関数の名前や条件式の書き方を疑いがちです。しかし実務で数字がずれる原因は別にあります。数式のロジックではないことが多いのです。参照しているセルの範囲に問題が隠れています。SUMIFS関数では合計対象列と条件判定列を別々に選びました。そのため、範囲がずれてしまうリスクがあります。しかも、知らず知らずのうちに間違えてしまいます。

原因は、合計範囲だけが1行多く指定されていたことでした。月次決算の締め切り前のことです。売上集計の数字が合わずパニックになりました。合計範囲を1行多く指定していただけのミスです。それに気づくまでに貴重な時間を空費しました。全ての数式を一から書き直すことになりました。締め切りを少し過ぎて提出した苦い記憶です。

最初は条件の文字列が間違っていると思いました。そのため、何度もフィルターをかけて確認したのです。しかし、どれだけ条件を精査しても合計値は変わりません。行数が1行だけずれているとは思いもしませんでした。まず、合計範囲と条件範囲をよく見比べてください。数式が動いているからといって安心はできません。

#VALUE!エラーの正体：範囲不一致の確認

セルに「#VALUE!」というエラーが出たとします。つまり、これはExcelからの警告です。エラーが出る最大の原因はサイズ不一致です。合計範囲と条件範囲の大きさが合っていません。例えば合計範囲を2行目から100行目とします。一方の条件範囲を101行目まで指定すると、Excelは正しく計算できません。その結果、エラーを返して処理を止めてしまいます。

そこで、エラーが出たときは数式バーをクリックします。カラーハイライトされた範囲をチェックしましょう。視覚的に確認すると原因に近づきやすいです。合計範囲の四角枠をよく見てください。条件範囲の四角枠の高さとぴたりと揃っていますか。数式の中に手入力で数字が混ざっていませんか。「100」と「101」の違いを落ち着いて見直します。たとえわずか1行の不一致でも、計算はストップするのです。逆に、行数をそろえるだけで直ることもあります。

エラーなき参照行ずれ

一方で、最も厄介なのがエラーが出ないパターンです。合計金額だけが微妙にずれてしまう現象と言えます。これは参照の「開始行」がずれているときに起きます。ただし、合計範囲と条件範囲の行数だけは同じ状態です。例えば合計範囲は2行目から始まっているとします。一方、条件範囲が3行目から始まっているケースです。すると、Excelは1行分ずれた状態で集計を続けてしまいます。

過去の集計ファイルをコピーして使った時の話です。一部の列だけ1行下にずれて参照されていました。特定店舗の売上が加算されていないことに気づきます。参照の枠が階段状にズレているのを見た瞬間です。全身から血の気が引く感覚を味わいました。

なお、このようなズレは、セルのドラッグや行の挿入で発生します。条件範囲も必ず「$A$2:$A$100」でなければなりません。2行目から始まったら2行目で終わるのが鉄則です。

見出し行のズレによる計算ミス

実務でよくあるミスが見出し行の扱いです。条件列は見出しを含めて1行目から指定します。別の列では2行目から指定してしまうパターンです。これが混ざるとデータが1行ずつシフトしてしまいます。その結果、本来合計されるべき値が切り捨てられます。さらに、別の条件の行と合算されてしまうこともあるのです。

1行の違いなら大きな差は出ないと考えるのは危険です。月次集計のように大量のデータを扱う場面があります。こうした場面では、その1行のズレが大きな計算ミスを招くのです。数万円や数百万円単位の違いになることもあります。そこで私は、必ず2行目から指定するルールにしています。見出し行を含めるかどうかを気分で決めてはいけません。集計範囲の起点は共通の行番号に固定しましょう。これだけでも悩む時間はかなり短くなりました。

数式コピー後の3か所確認

前月のファイルをコピーして集計表を作ります。確かに、数式を使い回すと作業は早くなります。しかし同時に最もミスが起きやすいタイミングです。だからこそ、データを貼り替えた後の確認作業が欠かせません。

コピー後は、行の最終番号、絶対参照の$マーク、全範囲の行数一致の3か所だけを見ています。数式をコピーした後に「3か所」を見ておきましょう。

まず1つ目は「行の最終番号」です。例えば、先月のデータが100行だったと仮定します。範囲が100行目のままだと集計から漏れてしまいます。2つ目は「絶対参照の$マーク」です。数式を右や下にコピーした際のズレを確認します。そして3つ目が「全範囲の行数一致」です。今回いちばん見たいポイントです。

以前の私は数式をオートフィルして安心していました。空白行まで余分に参照したまま放置したのです。それ以来最終行の確認は慎重に行っています。

行数ズレ解消！Excelテーブルのすすめ

ただ、行数のズレを減らす方法があります。Excelの「テーブル機能」を活用することです。これで「$A$2:$A$100」のような番地指定を減らせます。代わりに「売上データ[金額]」のような列名指定に変わりました。列名で範囲を指定できるので、行数の確認がかなり楽になります。

この方法で助かったのは自動拡張です。たとえデータが10行から100行に増えても、問題ありません。行数が食い違う事故を減らせます。

もっとも、同僚から使いにくいと言われ、敬遠していた時期もありました。しかし月次の更新作業に限界を感じて導入したのです。ミスが減り、作業時間も短くなりました。大量の#VALUE!エラーに涙することもありませんでした。自分の頑固さを反省した出来事です。

もちろん、最初は列名の表記に戸惑うかもしれません。しかし慣れてしまえば数式の意味も一目で理解できます。範囲サイズ不一致の不安もかなり軽くなりました。

計算ミス防止の検算術

とはいえ、どんなに注意してもミスは残るものです。そこで検知のための防御策を置きます。私は集計表の端に必ず「検算用」のセルを設けます。元データの金額列を単純にSUM関数で合計するのです。これだけで提出前の確認が楽になりました。

仮に1円でも差があれば、何かが間違っています。つまり、どこかの範囲が漏れている証拠です。さらに、この比較結果をIF関数で判定させましょう。一致していれば「OK」と表示させます。ずれていれば赤文字で「NG」と出るようにするのです。これがあるだけで提出前の安心感が全く違います。

数字が合わない理由を探すのは大変な作業です。この検算ステップを月次のルーチンに入れました。提出後に誤りを指摘されて青ざめることがなくなります。数式が数式をチェックする仕組みを作るのです。非エンジニアの自分には、この泥臭い確認が一番合っていました。

SUMIFS計算ミスは範囲のズレ

=SUMIFS($D$2:$D$100,$A$2:$A$100,G2,$B$2:$B$100,H2)

SUMIFS関数の計算が合わないトラブルに直面したとします。確かに、反射的に条件の書き方を疑ってしまうのは仕方ありません。しかし原因の多くはサイズや開始位置の不一致です。数字が合わないときは数式のロジックから離れてください。参照されているセルの枠の形をじっくりと眺めましょう。物理的なズレに気づくだけで解決は早くなります。

この場面で確認すること

まず、合計範囲と条件範囲の開始行はずれていませんか。次に、行数が1行だけ多くなっていないか確認してください。解決までの時間はかなり短くなりました。そして可能であればテーブル機能を取り入れてみましょう。範囲の管理をExcelに任せてしまうのです。そのうえで、数字の意味を読み解く業務に時間を使いましょう。

月次資料の作成は毎月やってくる孤独な戦いです。今回共有したチェックポイントが、提出前の確認に役立てばうれしいです。提出前の数分間の点検が大きなトラブルを防ぎます。夜遅くまでの修正作業を未然に防いでくれるのです。「範囲をそろえる」というルールだけでも、かなり救われました。同じような集計表を触っている人がいたら、まず範囲の高さだけでも見てみてください。

実際のデータで試すときは、正常な例だけでなく空欄や追加行がある例も確認します。計算結果や登録件数を処理前後で照合し、問題が起きた入力を記録しておくと、次回の修正が早くなります。

確認結果が合わない場合は、入力範囲、列名、データ型を一つずつ見直します。途中で手作業の修正を加えると原因が分からなくなるため、同じ入力で再現できるかを確かめてから変更します。小さなサンプルで処理が安定した後に、本番ファイルへ広げる進め方が安全です。

実際のファイルで確認するときは、入力データの例を複数用意し、処理前後の結果を照合してください。正常に動いた一例だけで判断せず、空欄や表記ゆれがある場合も試すと、運用後の手戻りを減らせます。

関連リンクとチェックリスト

学習サービスとアンケート