実際にあった話：範囲を直し忘れて87万円が消えていた
先月、経理部から回ってきた「今月の東京支店の売上、先月と比べて急に減っていませんか？」という一言がきっかけでした。SUMIFSで集計した東京支店の合計は412万6,000円。ところが手元の請求書を人力で足し合わせてみると、実際は500万円を超えていたのです。差額はおよそ87万円。
原因を探るのに1時間40分かかりました。結局たどり着いたのは、条件範囲をA2:A90のまま固定していたことでした。先月は90件だったデータが、今月は113件に増えていたのに、合計範囲だけをC2:C113に更新して、条件範囲の更新を忘れていたのです。91行目から113行目、23件分の売上が、条件判定そのものから漏れていました。
厄介だったのは、エラーが一切出ていなかったことです。数式は正しく計算され、緑色のチェックマークも赤い警告も出ません。COUNTIFSで件数を数えてみて初めて「合計対象になっているのは90件だけで、113件のはずが合わない」ということに気づけました。件数の検算をしていなければ、おそらくそのまま資料を提出していたと思います。
この記事では、私と同じように「エラーは出ていないのに数字が合わない」という状況に陥ったときに、実務でチェックすべき3つのポイントを、症状の見分け方から順番に整理します。
なぜエラーが出ないのか
SUMIFSは「条件に一致する行がなければ、単純に0を返す」という仕組みで動いています。範囲がズレていても、文字列が一致しなくても、数値が文字列扱いになっていても、Excelから見れば「条件に合う行が見つからなかった」というだけのことなので、エラーにはなりません。
人間から見れば明らかにおかしい結果でも、Excelにとっては仕様どおりの正常な動作です。「エラーが出ていないから大丈夫」という思い込みを手放しておくだけでも、この手のトラブルにはかなり強くなれます。
まず10秒セルフチェック：あなたの症状はどれに近いか
SUMIFSの結果がおかしいとき、原因はだいたい次の3パターンのどれかに当てはまります。当てはまる項目をチェックしてみてください。
- 先月まで合っていたのに、今月だけ急に合計が少なくなった
- 特定の条件（部署名・商品名など）だけ、合計が0になったり異様に少なくなったりする
- 他部署やCSVから受け取ったデータをそのまま条件に使っている
- 条件に指定しているのが「数字」で、しかもB列などの数値が左寄せになっている
1つ目に当てはまった方は「範囲ズレ型」、2つ目・3つ目は「表記ゆれ型」、4つ目は「文字列化型」の可能性が高いです。それぞれ順番に見ていきます。
ケースA：範囲ズレ型――行が増えたのに範囲を固定していた
これが冒頭のエピソードのパターンで、実務ではもっとも多く見かける原因です。
=SUMIFS(C2:C100, A2:A90, "東京")合計範囲はC2:C100なのに、条件範囲はA2:A90。行数がずれています。SUMIFSはすべての範囲が同じ行数でなければ、意図しない位置同士が対応してしまい、正しく動きません。
| 比較項目 | Before（事故が起きる書き方） | After（安全な書き方） |
|---|---|---|
| 範囲指定 | A2:A90（先月の行数で固定） | A:A（列全体で指定） |
| データが増えたとき | 範囲を手動で書き換える必要がある | 自動的に対象へ含まれる |
| 起きやすい事故 | 更新忘れによる集計漏れ | 範囲ズレそのものが発生しにくい |
対処法
- 条件範囲・合計範囲をすべて同じ行数に揃える
- 範囲を列全体（A:Aなど）で指定する
- テーブル機能を使い、データが増えても範囲が自動で追随する状態にする
列全体で指定すると、条件に合わない空白セルまで計算対象になるため、データ量が数万行を超えるような表では動作がわずかに重くなることがあります。日常的な業務範囲であれば気にする必要はありませんが、大規模な表ではテーブル機能を優先するほうが安全です。
なお、この手のズレは表そのものの構造が複雑なときほど起こりやすくなります。見出しが2行になっていると集計がおかしくなる理由と直し方でも触れていますが、範囲の境界が分かりにくい表ほど、条件範囲と合計範囲をズレたまま放置してしまうリスクが高まります。
ケースB：表記ゆれ型――見た目は同じでも一致しない
「東京」と「東京 」（末尾に半角スペース）は、人間の目には同じでも、SUMIFSにとっては完全に別の文字列です。SUMIFSは完全一致でしか条件を拾いません。
とくに危ないのは、他部署から共有された一覧やCSVをそのまま条件範囲に使うケースです。以前、経理から送られてきた部署名一覧を条件として使ったところ、部署名の先頭に見えない全角スペースが1文字だけ紛れ込んでいて、該当する部署の合計がまるごと0になっていたことがありました。画面を目視で見比べる限りでは、まず気づけないレベルのズレです。
確認方法
別セルに =LEN(A2) を入力し、文字数を確認します。想定よりも文字数が1つでも多ければ、スペースが混入している可能性が高いです。
対処法
- TRIM関数で余分なスペースを削除する
- CLEAN関数で目に見えない制御文字を除去する
- 入力ルールをチーム内で統一する（全角・半角、スペースの有無など）
条件として直接「”東京”」と文字列を打ち込んでいる場合と、別セルを条件として参照している場合とでは、原因の探し方が変わります。前者は数式内の文字列を見直せば済みますが、後者は参照元のセルの中身までさかのぼって確認する必要があります。
この「見た目は同じなのに一致しない」問題は、VLOOKUPでもまったく同じ形で起こります。VLOOKUPで#N/Aが消えないときの原因の探し方でも、スペース混入が主要な原因のひとつとして紹介していますので、あわせて確認しておくと、この手のトラブルへの対応力がぐっと上がります。
ケースC：文字列化型――数字なのに数字として認識されていない
セルの中身が見た目は数字なのに、左寄せになっている場合は要注意です。それは「数値」ではなく「文字列としての数字」として入力されています。
=SUMIFS(C2:C100, B2:B100, 100)B列の「100」が文字列として入力されていた場合、この式は該当する行を1件も拾えません。合計欄が「なんとなく少ない」と感じたときほど、実はこのパターンであることが多いです。
| 判定材料 | 数値として認識されている場合 | 文字列として認識されている場合 |
|---|---|---|
| セル内の配置 | 右寄せ | 左寄せ |
| ISNUMBER関数の結果 | TRUE | FALSE |
| SUMIFSでの扱い | 条件に一致する | 一致せず、無視される |
このパターンでとくに厄介なのは、条件範囲側（B列）だけが文字列になっているケースです。合計範囲（C列）の数値は問題なく数値として認識されているため、SUMIFS自体は正常に動いているように見えてしまいます。ところが条件として指定した「100」（数値）と、B列に入っている「100」（文字列）は一致しないため、該当する行がまるごと集計から漏れます。
対処法
- ISNUMBER関数で数値かどうかを判定する
- VALUE関数で文字列を数値に変換する
- 区切り位置ウィザードを使い、一括で数値化する
他システムから出力したデータや、CSVを取り込んだ直後のデータでは、数字がすべて文字列として入力されていることがよくあります。この構造はSUM関数の基本の使い方と、合計が合わないときの対処法でも取り上げていますので、SUM関数側で同じような経験がある方は、あわせて読んでおくと理解が早まります。
COUNTIFS・AVERAGEIFSでも同じことが起きる
ここまでSUMIFSを例に説明してきましたが、範囲ズレ・表記ゆれ・文字列化という3つの原因は、COUNTIFSやAVERAGEIFSでもまったく同じように発生します。AVERAGE関数の使い方と注意点でも、条件範囲と計算対象範囲がズレていると、平均が実感と合わなくなるケースを紹介しています。
「〇〇IF」「〇〇IFS」という名前がついた関数は、条件範囲と対象範囲という複数の範囲を扱う構造を持っているため、ここで紹介した3つのチェックポイントは、初めて使う関数でもそのまま応用できます。1つの関数だけを個別に覚えるのではなく、「条件系の関数はすべて同じ弱点を持っている」と理解しておくと、初見の関数でも身構えやすくなります。
よくある質問
Q. 範囲を列全体（A:A）にすれば、必ず安全になりますか？
範囲ズレという意味では、ほぼ解消されます。ただし空白セルまで計算対象に含まれるため、データ量が非常に多い表（数万行以上）では動作がわずかに重くなることがあります。日常的な業務範囲であれば気にする必要はありませんが、大規模な表ではテーブル機能を優先してください。
Q. TRIM関数を使えば、表記ゆれの問題はすべて解決しますか？
半角・全角スペースの多くは解消できますが、改行コードや特殊な制御文字までは除去できないことがあります。TRIMで直らない場合は、CLEAN関数も併用してみてください。
Q. 条件範囲が他部署から共有されたデータの場合、どこから疑えばいいですか？
まずはLEN関数で文字数を確認するのが最短ルートです。想定より文字数が多ければスペース混入、ISNUMBER関数でFALSEが返れば文字列化が疑われます。自分の表ではなく他部署のデータが原因であることは実務では非常に多いため、自分の数式より先に「相手のデータ」を疑う習慣をつけておくと、原因究明がぐっと早くなります。
Q. そもそも、こういうズレに気づくきっかけがありません。どうすれば早く気づけますか？
一番確実なのは、SUMIFSの結果と一緒に、必ずCOUNTIFSで件数も出しておくことです。冒頭の87万円のケースでも、私が異変に気づけたのは、合計だけを見ていたからではなく、たまたまCOUNTIFSで件数を数えてみたことがきっかけでした。合計範囲は113行分あるはずなのに、条件に一致した件数は90件しか返ってきません。「あれ、23件足りない」というこの違和感が、範囲ズレを見つける最初のサインになりました。金額の大小だけでは違和感に気づきにくくても、件数のズレは意外と目に留まりやすいものです。
報告前の検算ルーティン：件数もあわせて数える
SUMIFSの結果をそのまま資料に転記する前に、私は次の3ステップを検算のルーティンにしています。時間にすると1件あたり3分もかからない作業ですが、これを習慣化してから、範囲ズレによる報告ミスは一度も起きていません。
- 件数を数える：
=COUNTIFS(A2:A113, "東京")のように、SUMIFSと同じ条件でCOUNTIFSを計算し、対象件数を確認する - 概算と照らし合わせる：元データの行数や、前月の実績から「だいたいこれくらいのはずだ」という見込みを頭の中で用意しておき、実際の件数とズレていないか確認する
- 差分が出たら範囲から疑う：件数が想定より少なければ、まず条件範囲と合計範囲の行数が一致しているかを確認する
この検算ルーティンが有効なのは、金額のズレよりも件数のズレのほうが直感的に気づきやすいためです。「87万円少ない」という違和感は、元の金額を正確に把握していない限り気づけませんが、「90件のはずが90件しか出ていない」「113件あるはずなのに90件しか拾えていない」という件数の違和感は、データの行数さえ把握していれば誰でも気づけます。月次集計やクライアントへの報告など、数字の正確性が重要な場面ほど、このひと手間を挟む価値があります。
チームで表を共有している場合は、COUNTIFSの結果を集計表の隅に小さく表示しておくのもおすすめです。次に表を触る人が範囲を変更したときにも、件数のズレという形で異変に気づきやすくなります。私の部署でも、この一手間を入れるようになってから、集計ミスを本人以外が先に見つけてくれるケースが増えました。件数という「もう一つの数字」を横に置いておくだけで、チーム全体のチェック体制が一段階強くなります。
実務で覚えておくべきチェック順
数字が合わないときは、次の順番で確認するのがおすすめです。
- 範囲のサイズ：条件範囲と合計範囲の行数が一致しているか
- 文字列のズレ：スペースや全角・半角の違いが紛れ込んでいないか
- 数値の形式：条件に使っている数値が、文字列として入力されていないか
この順番で見れば、ほとんどの問題は解決します。焦って数式全体を書き直す前に、まずはこの3つを機械的にチェックする習慣をつけておくと、原因究明にかかる時間をぐっと減らせます。実際、私が87万円のズレに気づいたときも、範囲から順番にチェックしていれば、1時間40分ではなく10分もかからなかったはずです。
まとめ：エラーが出ないからこそ、機械的にチェックする
SUMIFSは非常に便利な関数ですが、範囲ズレ・表記ゆれ・文字列化という3つの落とし穴があると、エラーを一切出さないまま結果だけが静かに狂います。冒頭で紹介した87万円のズレも、エラーメッセージが出ていれば1分で気づけたはずですが、実際には1時間40分もかけてようやく原因にたどり着きました。
数字が合わないと感じたら、数式全体を書き直す前に、まず「範囲」→「表記」→「文字列形式」の順番で機械的にチェックしてみてください。この順番さえ習慣にしておけば、次に同じトラブルに遭遇したときの原因究明の時間は、体感で半分以下に縮まります。
SUMIFSに限らず、条件を指定して集計する関数はすべて、便利であるほど「結果が正しいかどうか」を目で確かめる機会が減っていきます。だからこそ、集計結果をそのまま信じるのではなく、おおよその見込みを頭の中に用意しておき、実際の結果とズレていないかを確認する習慣を持っておくことが、”静かな間違い”に気づく一番の近道です。
SUMIFSの引数の順番や条件の書き方そのものに不安がある場合は、SUMIFS関数の使い方で基本から確認できます。
原因が1つに絞れず、範囲ズレと表記ゆれが同時に起きているような複合的なケースの切り分け方や、チェックリストとして使える整理は、noteの会員限定コンテンツでまとめています。原因が2つ以上重なっているときの絞り込み方をnoteにまとめました
コメント