Excel・スプシの集計が合わない？SUMIFやVLOOKUPがズレる5つの原因と確かめ方

「請求の合計と、売上一覧の合計が1円だけ合わない」

「取引先ごとにSUMIFで集計したら、どう見ても少ない」

「VLOOKUPで引いてきた単価が、なぜか隣の商品のものになっている」

ExcelやGoogleスプレッドシートで毎月の集計をしていると、こうした「数字のズレ」に出会うことがあります。

数式そのものは正しく見えるのに結果が合わないと、どこを疑えばいいのか分からず、電卓で1件ずつ確かめ直すことになりがちです。

じつは、集計のズレの原因はある程度パターンが決まっています。

この記事では、SUMIF・COUNTIF・VLOOKUPなどの集計がズレる代表的な5つの原因と、自分で原因を見つけるための確かめ方の順番をまとめます。

━━━━━━━━━━━━━━

■ ズレの多くは「数式」ではなく「データ」にある

━━━━━━━━━━━━━━

集計が合わないとき、まず数式を書き直したくなります。

ですが実際には、数式は正しく、集計の元になる「データの入り方」に原因があることがほとんどです。

・人の目には同じに見えるのに、パソコンにとっては別の文字になっている

・数字に見えるのに、中身は「文字」として入っている

・新しく足した行が、集計の範囲に入っていない

こうした違いは画面を眺めているだけでは気づきにくいため、「どこが違うか」を確かめる手順を知っておくと、原因にたどり着くのがぐっと早くなります。

━━━━━━━━━━━━━━

■ 原因1：数字が「文字」として入っている

━━━━━━━━━━━━━━

CSVを貼り付けたり、別のシステムからコピーしたりすると、数字が「文字（テキスト）」として入ることがあります。

SUMなどの合計は、範囲の中にある文字の数字を計算に入れないため、見た目は数字なのに合計から抜けてしまいます。

見分けるヒントは次のとおりです。

・セルの中で数字が左に寄っている（数値は通常右寄せ）

・Excelでセルの左上に小さな緑の三角が出ている

・=ISNUMBER(A2) が FALSE になる

日付も同じで、「2026/11/01」が日付ではなく文字で入っていると、月ごとの集計から漏れます。

━━━━━━━━━━━━━━

■ 原因2：見えない空白・全角と半角の違い

━━━━━━━━━━━━━━

SUMIFやCOUNTIFは、条件と「完全に同じ文字」のものだけを数えます。

そのため、次のような違いがあると別の取引先として扱われ、集計から外れます。

・「株式会社サンプル」と「株式会社サンプル 」（末尾に空白）

・「ＡＢＣ商事」（全角）と「ABC商事」（半角）

・「㈱サンプル」と「株式会社サンプル」

確かめるときは、=LEN(A2) で文字数を出してみるのが手軽です。見た目は同じなのに文字数が違えば、どこかに空白や別の文字が入っています。

━━━━━━━━━━━━━━

■ 原因3：VLOOKUPの「検索方法」を省略している

━━━━━━━━━━━━━━

VLOOKUPの4つ目の引数（Excelでは「検索方法」、スプレッドシートでは「並べ替え済み」）を省略すると、完全一致ではなく「近似一致」で探します。

近似一致は、検索する列が並べ替えられている前提の探し方なので、そうでない表では、探している商品と違う行の値が返ってくることがあります。

商品コードや取引先名で探すときは、4つ目に FALSE（または 0）を入れて完全一致にしておくのが基本です。

Googleのヘルプでも、FALSE（完全一致）がおすすめとされています。

━━━━━━━━━━━━━━

■ 原因4：集計の範囲から行がはみ出している

━━━━━━━━━━━━━━

=SUM(B2:B100) のように範囲を決めて集計していると、101行目以降に足したデータは計算に入りません。

また、数式を下の行へコピーしたときに範囲まで一緒にずれてしまい、行ごとに違う範囲を集計していた、ということもよくあります。

範囲を固定したいときは $B$2:$B$100 のように「$」を付ける、データを足す場所を範囲の内側に決めておく、といった工夫で防げます。

━━━━━━━━━━━━━━

■ 原因5：表示は丸めていても、中身は小数のまま

━━━━━━━━━━━━━━

単価×数量に税率を掛けると、小数が出ることがあります。

セルの表示形式で小数点以下を表示しないようにしていても、セルの中身は小数のまま残っており、合計はその小数を含めて計算されます。

その結果、「1行ずつの表示を足した金額」と「合計欄の金額」が1円ずれる、ということが起きます。

請求書のように1円単位で合わせたい表では、ROUND・ROUNDDOWNなどの関数で、どの段階で端数を処理するかを決めておくと安心です。

━━━━━━━━━━━━━━

■ 原因を見つける確かめ方の順番

━━━━━━━━━━━━━━

ズレに気づいたら、次の順番で確かめると原因を絞り込みやすくなります。

① 合わない1件を決める：取引先1社や1か月分など、小さく区切って比べる

② 範囲を確かめる：数式の範囲に、対象の行がすべて入っているか

③ 数値かどうか確かめる：ISNUMBERで、文字の数字が混じっていないか

④ 文字のそろい方を確かめる：LENで、空白や全角・半角の違いがないか

⑤ 端数を確かめる：表示桁数を増やして、隠れた小数がないか

いきなり表全体を見直すより、「合わない1件」に絞ってから順番に確かめるほうが、結果的に早く終わります。

━━━━━━━━━━━━━━

■ 直したあとは「同じズレが起きない形」にしておく

━━━━━━━━━━━━━━

原因が見つかって数式やデータを直しても、入力のしかたが変わらなければ、翌月また同じズレが起きます。

・取引先名はリスト（プルダウン）から選ぶようにする

・CSVを貼り付ける列は、数値・日付の形式をそろえてから集計する

・端数を処理する列を1つ決めて、合計はその列から出す

このように「ズレが入り込みにくい形」にしておくと、毎月の確認がぐっと楽になります。

━━━━━━━━━━━━━━

■ 原因が分からないときは、ご相談ください

━━━━━━━━━━━━━━

ここまで確かめても原因が分からない、確かめる時間が取れない、という場合は、ココナラで修正のご依頼を承っています。

ExcelやGoogleスプレッドシートの関数エラー（#N/A・#REF!・#VALUE! など）や、SUMIF・COUNTIFの集計ズレ、VLOOKUP・XLOOKUPで値が見つからない問題、GAS（Google Apps Script）の小さなエラーを直し、原因・直した場所・今後の注意点を、分かりやすい言葉でお伝えします。

購入の前に、メッセージで困りごととファイル（見本でもかまいません）をお送りいただければ、直せるかどうかと、何か所の修正になるかをお答えします。

※Excelマクロ（VBA）の修正は対象外です。

毎月の集計で「なぜか合わない」が続いている方の、お役に立てればうれしいです。