合っているはずなのに、なぜか数字が違う
月次集計でSUMIFSを使っていると、こんな経験はありませんか？
「条件は合っているはずなのに、合計が合わない。」
一部だけ抜けている。なぜか少ない。ときどき多い。数式を見てもエラーは出ていない。それなのに結果が違う。上司に報告する直前になって初めて気づき、慌てて原因を探した経験がある方も、少なくないのではないでしょうか。 「SUMIF」は「1つの条件」で合計する関数でしたが、SUMIFSは「複数の条件」を同時に指定できる分、条件と範囲の組み合わせが増えるぶん、ズレに気づきにくいという側面があります。式が正しいのに合わない場合、設計に問題があることもあります。「1セル完結は危険？読めないExcelになる原因」については、以前の記事でも取り上げていますので、複雑な条件を1つのセルに詰め込みすぎていないか、あわせて確認してみてください。
SUMIFSは便利ですが、”静かに間違う”関数です。エラー表示が出ないため、間違いに気づきにくいのが厄介なところです。
なぜエラーが出ないのか、少し整理しておきます。SUMIFSは「条件に一致する行がなければ、単純に0を返す」という仕組みで動いています。範囲がズレていても、文字列が一致しなくても、数値が文字列扱いになっていても、Excelから見れば「条件に合う行が見つからなかった」というだけのことなので、エラーにはなりません。人間から見れば明らかにおかしい結果でも、Excelにとっては正常な動作の一部なのです。この性質を知っておくだけでも、「エラーが出ていないから大丈夫」という思い込みを手放しやすくなります。
今回は、実務で本当によくある3つの確認ポイントを解説します。
原因① 条件範囲と合計範囲のサイズが違う
まず一番多いのがこれです。
=SUMIFS(C2:C100, A2:A90, "東京")一見問題なさそうですが、合計範囲はC2:C100、条件範囲はA2:A90と、範囲の行数が違います。SUMIFSは基本的に**すべての範囲が同じサイズである必要があります。**ズレていると、意図しない位置同士が対応してしまいます。
この手のズレは、表の途中で行を挿入したり削除したりしたときに起こりやすくなります。見出しが複数行になっている表のように、表そのものの構造が複雑だと、どこからどこまでを範囲に含めるべきかが分かりにくくなり、結果として範囲のズレにつながることもあります。
対処法
- すべて同じ行数に揃える
- テーブル機能を使う
- 範囲を列全体にする（例：A:A）
どの方法を選ぶにしても、共通して大切なのは「複数の範囲を、常にセットで見直す」という意識です。合計範囲だけを更新して条件範囲を忘れる、あるいはその逆、というミスは、範囲を個別に管理している限り、どうしても起こりやすくなります。
範囲を列全体で指定しておくと、後から行が増えても自動的に対象に含まれるため、範囲のズレそのものが起こりにくくなります。ただし、列全体を指定すると、条件に合わないはずの空白セルまで計算対象になり、動作がわずかに重くなることもあるため、データ量が多い表では、テーブル機能を使うほうが安全です。
実際にどんな事故につながるかというと、たとえば「先月は90行だったから」という理由でA2:A90と決め打ちしていた条件範囲が、今月はデータが増えて100行になっていた、というケースがよくあります。合計範囲だけをC2:C100に更新して、条件範囲の更新を忘れてしまうと、91行目から100行目までのデータは、条件判定そのものから漏れてしまいます。エラーは出ないため、集計担当者は「今月も正しく計算できた」と思い込んだまま、資料を提出してしまうことになります。
原因② 文字列の「見えないズレ」
次に多いのがこれ。見た目は同じ「東京」でも、全角と半角の違い、末尾にスペース、コピー貼り付けの影響で、実際には別の文字列として扱われていることがあります。
SUMIFSは完全一致です。「東京 」と「東京」は別物です。
確認方法
別セルに
=LEN(A2)を入れて文字数を確認します。想定より1文字多ければ、スペースの可能性があります。
対処法
- TRIM関数でスペース削除
- CLEAN関数で不要文字削除
- データ入力ルールを統一
この「見た目は同じなのに一致しない」という問題は、VLOOKUPでもまったく同じ形で発生します。VLOOKUPで#N/Aが消えない原因でも、スペースの混入が原因の一つとして紹介していますので、あわせて確認しておくと、この手のトラブルへの対応力がぐっと上がります。
厄介なのは、条件として直接「”東京”」と文字列を打ち込んでいる場合と、別のセルを条件として参照している場合とで、原因の探し方が変わってくる点です。前者であれば数式内の文字列を直接見直せば済みますが、後者の場合は、参照しているセルの中身までさかのぼって確認する必要があります。とくに、他部署から共有された一覧をそのまま条件として使っている場合は、入力ルールが自分たちと異なっていることも多いため、一度LEN関数などで文字数を確認しておくと安心です。
原因③ 数値が”文字列”になっている
これも本当に多いです。見た目は数字なのに、左寄せになっている場合は要注意です。文字列扱いになっていると、条件に一致しません。
例：
=SUMIFS(C2:C100, B2:B100, 100)B列の「100」が文字列だと、合計されません。
確認方法
- ISNUMBER関数で判定
- 左寄せになっていないか確認
対処法
- VALUE関数で変換
- 区切り位置で数値化
- データ形式を統一
「セルの表示形式が原因で数値として認識されないケース」は、SUM関数の記事でも取り上げましたが、SUMIFSでも構造はまったく同じです。他システムから出力したデータや、CSVを取り込んだ直後のデータでは、数字がすべて文字列として入力されていることがよくあるため、特に注意しておきたいポイントです。
このパターンでとくに厄介なのは、条件範囲側（B列）だけが文字列になっているケースです。合計範囲（C列）の数値は問題なく数値として認識されているため、SUMIFS自体は正常に動いているように見えます。ところが、条件として指定した「100」（数値）と、B列に入っている「100」（文字列）が一致しないため、該当する行がまるごと集計から漏れてしまいます。合計欄がなんとなく少ない、と感じたときは、条件側のセルが本当に数値として入力されているかを、真っ先に疑ってみてください。
COUNTIFS・AVERAGEIFSでも同じことが起こる
ここまでSUMIFSを例に説明してきましたが、範囲のズレ・文字列のズレ・数値形式のズレという3つの原因は、COUNTIFSやAVERAGEIFSでもまったく同じように起こります。「AVERAGEIF」を使った平均集計でも、条件を見ている範囲と、計算対象にしている範囲がズレていれば、結果はやはり静かにおかしくなります。
「〇〇IF」「〇〇IFS」という名前がついた関数はすべて、条件と対象範囲という2種類（あるいはそれ以上）の範囲を扱う構造になっているため、範囲のサイズを揃えることと、条件側のデータをきれいに保っておくことが、正確な集計の土台になります。1つの関数だけを個別に覚えるのではなく、「条件系の関数はすべて同じ弱点を持っている」と理解しておくと、初めて使う関数でも身構えやすくなります。
実務で覚えておくべきチェック順
数字が合わないときは、次の順番で確認するのがおすすめです。
- 範囲のサイズ：条件範囲と合計範囲の行数が一致しているか
- 文字列のズレ：スペースや全角・半角の違いが紛れ込んでいないか
- 数値の形式：条件に使っている数値が、文字列として入力されていないか
この順番で見れば、ほとんどの問題は解決します。焦って式全体を書き直す前に、まずはこの3つを機械的にチェックする習慣をつけておくと、原因究明にかかる時間をぐっと減らせます。
実際の確認作業では、いきなり数式を疑うのではなく、まず「範囲」から見るのがコツです。範囲さえ揃っていれば、次に疑うべきは文字列、それでも解決しなければ数値形式、という順に絞り込んでいくと、闇雲に式全体を作り直すよりもずっと早く原因にたどり着けます。会議直前などで時間がないときほど、この順番を機械的になぞることが、結果的に一番の近道になります。
まとめ
SUMIFSは非常に便利な関数です。しかし、範囲のズレ、文字のズレ、数値形式のズレ、この3つがあると、”エラーなしで間違う”という厄介な状態になります。
SUMIFSで数字が合わないときは、焦って式を書き直す前に、条件範囲とデータの整合性を確認する。これが実務での正解です。
条件が増えるほど、それぞれの範囲・文字列・数値形式のどこにズレがあるのかは見つけにくくなります。だからこそ、普段から範囲をテーブル化しておく、データ入力のルールを統一しておく、といった「事故が起きにくい表」を作っておくことが、結果的に一番の近道になります。
SUMIFSに限らず、条件を指定して集計する関数は、便利であるほど「結果が正しいかどうか」を人間の目で確かめる機会が減っていきます。だからこそ、集計結果をそのまま信じるのではなく、件数や合計のおおよその見込みを頭の中で用意しておき、実際の結果とズレていないかを確認する習慣を持っておくと、”静かな間違い”に気づける確率がぐっと上がります。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数についても実務目線で整理していますので、あわせてご覧ください。
SUMIFSは条件を増やせば増やすほど強力な反面、範囲のズレや表記ゆれが重なったときの原因切り分けも複雑になります。COUNTIFS・AVERAGEIFSも含めた複合的なトラブル対応や、チェックリストとして使えるまとめについては、noteで会員限定コンテンツとして扱っています。よろしければあわせてご覧ください。
コメント