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

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

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

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

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