Excelで縦横の合計が合わない原因5つと直し方【1円ズレ解決】

Excelで集計表を作ったら、縦の合計と横の合計が一致しない。同じデータを足しているはずなのに、なぜか数円〜数百円ズレる──。

結論から言うと、これはExcelのバグではありません。原因は次の5つのどれかにほぼ絞られます。

丸め誤差（表示は整数でも、実際のセルには小数が入っている）← 最も多い
SUM関数の範囲ミス（同じセルの重複選択・範囲の漏れ）
数値が「文字列」になっている
非表示の行・列に数値が含まれている
計算方法が「手動」になっていて再計算されていない

この記事では、まず「差額」から原因を一発で見分ける診断方法を紹介し、その後で原因別の直し方を解説します。最後に、飛び飛びのセルを安全に合計するテクニック（SUMPRODUCT・FILTER関数）と、再発を防ぐ表の作り方までまとめました。

まずはここから！「差額」で原因を見分ける診断法
原因1：丸め誤差──「1円合わない」の正体（最頻出）
原因2：SUM関数の範囲ミス──重複選択と範囲の漏れ
原因3：数値が「文字列」になっている
1. 確認方法：3つのサイン
2. 対策：文字列を数値に一括変換する3つの方法
原因4：非表示の行・列に数値が含まれている
1. 対策：SUBTOTAL関数で可視セルのみ合計する
原因5：計算方法が「手動」になっている
1. 対策：自動計算に戻す
見落としやすいその他の原因3つ
飛び飛びのセルを安全に合計する3つの方法
再発防止：そもそもズレない表を作る
1. テーブル機能で範囲ミスをなくす
2. 集計はピボットテーブルに任せる
まとめ：縦横の合計が合わないときのチェックリスト

まずはここから！「差額」で原因を見分ける診断法

やみくもに数式を見直す前に、縦計と横計の差額を確認しましょう。差額のパターンで原因がほぼ特定できます。

差額のパターン	疑うべき原因	読むべき章
1円〜数円だけ合わない	丸め誤差（小数の隠れ）	原因1
表の中のどれかの値とピッタリ同じ額ズレる	範囲の重複選択 or 漏れ	原因2
特定のセルの分だけ丸ごと足りない	数値が文字列になっている	原因3
見えていない値の分だけ多い	非表示行・フィルター	原因4
値を直したのに合計が変わらない	計算方法が「手動」	原因5
合計が0になる・警告が出た	循環参照（合計セル自身を含めている）	その他の原因

差額の確認にはステータスバーが便利です。合計したい範囲をドラッグで選択すると、画面右下に「合計」が自動表示されます。数式を作らずに正解の合計値を検算できるので、SUM関数の結果と見比べてみてください。

原因1：丸め誤差──「1円合わない」の正体（最頻出）

縦横の合計ズレで実務上いちばん多い原因がこれです。読者からも「数円だけ合わない」というケースが目立ちます。

Excelのセルは、画面に表示されている値と、実際に保持している値が違うことがあります。たとえば消費税計算の結果「1234.567」が入ったセルに「小数点以下を表示しない」書式を設定すると、見た目は「1235」ですが、計算には1234.567が使われます。

このため、

横計：行ごとに小数を含んだまま合計 → 最後に丸めて表示
縦計：列ごとに小数を含んだまま合計 → 最後に丸めて表示

という経路の違いで、丸めるタイミングの差が1円〜数円のズレになって現れます。さらに、コンピューターは小数を2進数で扱う関係上、0.1のような単純な小数でも内部的にはごくわずかな誤差を含みます（浮動小数点誤差）。これはExcel固有の不具合ではなく、表計算ソフト全般の仕様です。

確認方法：小数点以下の桁数を増やしてみる

ズレている表の数値セルを選択し、「ホーム」タブ →「数値」グループの「小数点以下の表示桁数を増やす」ボタンを数回クリックしてください。整数に見えていた値の下に小数が隠れていれば、原因はこれで確定です。

対策：ROUND関数で「丸めてから」合計する

各セルの計算式をROUND関数で囲み、丸めた値そのものをセルに持たせてから合計します。

=ROUND(単価*数量*1.1, 0)

こうすれば「表示されている値」と「実際の値」が一致するため、縦計と横計は必ず合います。切り捨てたい場合はROUNDDOWN、切り上げたい場合はROUNDUPを使います（消費税の端数処理は社内ルールに合わせてください）。

補助的な対策：「表示桁数で計算する」オプション

Excelには、表示中の値そのもので計算させるオプションもあります。

「ファイル」タブ →「オプション」→「詳細設定」
「次のブックを計算するとき」欄の「表示桁数で計算する」にチェック
「データの正確さが失われます。元に戻すことはできません。」という警告で「OK」

ただし警告のとおり、セルが保持していた小数部分は完全に失われ、チェックを外しても元に戻りません。ブック全体に影響するため、Microsoftも基本的にはROUND関数での対処を推奨しています。使うなら必ずファイルのコピーを取ってからにしましょう。

原因2：SUM関数の範囲ミス──重複選択と範囲の漏れ

飛び飛びのセルをCtrlキーを押しながらクリックして選択していくと、同じセルをうっかり2回クリックしてしまうことがあります。

=SUM(A1,A2,A2,A3)   ← A2が2回足されている

範囲指定でも同様で、次の式ではA3:A5の3セルが二重にカウントされます。

=SUM(A1:A5, A3:A6)   ← A3:A5が重なっている

見た目の表は正しいのに合計だけ大きくなるため、気づきにくいミスです。逆に、選択し忘れたセルがあれば合計は小さくなります。差額が表の中のどれかの値と一致していたら、まずこれを疑ってください。

確認方法：F2キーで参照範囲を色枠表示する

合計セルを選択してF2キーを押す（またはダブルクリックする）と、数式が参照しているセルが色付きの枠で表示されます。重複している範囲や、枠から漏れているセルがないか目視で確認できます。数式バーに表示された引数を読み上げて確認するのも有効です。

対策1：Ctrl+クリックで「選択解除」できる（Excel 2016以降）

意外と知られていませんが、Excel 2016（2017年1月の更新）以降では、選択済みのセルをCtrlキーを押しながらもう一度クリックすると選択を解除できます。二重クリックに気づいたら、その場で外せば最初からやり直す必要はありません。

対策2：そもそも飛び飛び選択をやめる（後述の関数を使う）

手動の飛び飛び選択は、ミスの温床であるうえ、行の挿入・削除にも弱い方法です。「1行おき」「特定の項目だけ」のような規則性があるなら、後述のSUMIF・SUMPRODUCT・FILTERで数式化するのが根本対策になります。

原因3：数値が「文字列」になっている

セルの値が数値ではなく文字列として入力されていると、SUM関数はそのセルを無視して合計します（エラーにはならず、静かに足し算から外れるのが厄介な点です）。他システムからコピーしたデータやCSVの取り込みでよく起こります。

確認方法：3つのサイン

セルの左上に緑色の三角形（エラーインジケーター）が出ている
書式を変えていないのに数値が左寄せになっている
=COUNT(範囲)の結果が、目視のデータ件数より少ない（COUNTは数値のみを数えるため）

対策：文字列を数値に一括変換する3つの方法

方法1：エラーインジケーターから変換（最速）
文字列セルの範囲を選択 → 左上に表示される「！」マークをクリック →「数値に変換する」を選択。複数セルをまとめて変換できます。

方法2：区切り位置を使う
対象の列を選択 →「データ」タブ →「区切り位置」→ 何も変更せず「完了」をクリック。列単位で一括変換できる定番テクニックです。

方法3：VALUE関数で変換
作業列に=VALUE(A1)と入力して数値化します。元データを直接書き換えたくない場合に有効です。

なお、数値の前後にスペースや改行が紛れ込んでいると区切り位置でも変換できないことがあります。その場合は=VALUE(TRIM(A1))のようにTRIM関数を組み合わせてください。

原因4：非表示の行・列に数値が含まれている

行や列を非表示にしていたり、フィルターで絞り込んでいたりしても、SUM関数は隠れたセルも含めて合計します。「見えているデータの合計」と思い込んでいると、縦横でズレが生じます。

対策：SUBTOTAL関数で可視セルのみ合計する

=SUBTOTAL(109, A1:A10)

第1引数を109にすると、フィルターで非表示の行に加えて、手動で非表示にした行も除外して合計します（9を指定すると、フィルターによる非表示のみ除外）。フィルター付きの表で「オートSUM」ボタンを押すと、Excelが自動的にSUBTOTAL関数を入れてくれるのも覚えておくと便利です。

エラー値の無視なども含めてより細かく制御したい場合は、上位互換のAGGREGATE関数も使えます。

原因5：計算方法が「手動」になっている

「元の値を修正したのに合計が変わらない」「数式をコピーしたのに前のセルと同じ値が表示される」という場合は、ブックの計算方法が「手動」になっている可能性があります。誰かが重いファイルで設定を変えたまま保存すると、そのブックを開いた人にも引き継がれます。

対策：自動計算に戻す

「数式」タブ →「計算方法の設定」→「自動」を選択します。とりあえず今すぐ再計算したいだけならF9キー（ブック全体を再計算）を押してください。

見落としやすいその他の原因3つ

5大原因で解決しない場合は、次の3つも確認してください。

循環参照──合計セル自身を範囲に含めていないか

意外と多いのが、合計セル自身をSUMの範囲に含めてしまうミスです。たとえばB11セルに次の式を入れるケースです。

=SUM(B2:B11)   ← B11自身が範囲に入っている

この場合「循環参照」の警告が表示され、計算結果は0になります（警告を閉じてそのまま作業を続けてしまい、後から気づくパターンが典型です）。縦計だけ・横計だけが0や明らかに小さい値になっていたら、これを疑ってください。

確認するには、画面左下のステータスバーに「循環参照」とセル番地が表示されていないかを見るか、「数式」タブ →「エラーチェック」の▼ →「循環参照」で該当セルを特定できます。範囲から合計セル自身を除外すれば解決します。

結合セルが表に混ざっている

セルを結合すると、値は結合範囲の左上セルにしか保持されません。見た目は2行分・2列分にまたがっていても、SUMで足されるのは1回だけです。このため、結合セルを含む表では縦方向と横方向で「値を拾う位置」が食い違い、合計がズレることがあります。範囲選択時に結合セルへ触れると選択範囲が意図せず広がるのも、ミスを誘発する一因です。

集計対象の表ではセルの結合は避けるのが原則です。見出しを中央に見せたいだけなら、結合の代わりに「セルの書式設定」→「配置」タブ →「選択範囲内で中央」を使えば、レイアウトを保ったまま結合を回避できます。

別シート・別ブックの参照ミス

「4月」「5月」のように同じ構成のシートが並ぶブックでは、数式をコピーした際に意図と違うシートのセルを参照していることがあります。値がもっともらしいだけに、目視では非常に気づきにくいミスです。

注意したいのは、別シート参照はF2キーを押しても色枠が同じ画面に表示されない点です。確認は数式バーでシート名（Sheet2!A1 など）を直接読むか、「数式」タブ →「参照元のトレース」を使うと、他シートへの参照が点線の矢印とアイコンで可視化されます。

飛び飛びのセルを安全に合計する3つの方法

原因2で触れたとおり、Ctrl+クリックでの手動選択はミスの元です。規則性のある飛び飛び合計は、数式で自動化しましょう。

方法1：項目名があるならSUMIF関数（最も簡単）

「上期/下期」「予算/実績」のように、合計したい行を区別できる項目列があるなら、SUMIF関数が最も簡単で確実です。

=SUMIF($A$2:$A$13, "実績", B2:B13)

A列の項目が「実績」の行だけを合計します。行を挿入・削除しても自動で追従するのが大きな利点です。可能であれば、表に項目列を設けてこの方法を使うのが第一候補です。

方法2：項目列がないならSUMPRODUCT関数（全バージョン対応）

項目列を追加できない表で「1行おき」に合計したい場合は、SUMPRODUCT関数とMOD関数・ROW関数を組み合わせます。

奇数行の合計：=SUMPRODUCT((MOD(ROW(B2:B13),2)=1)*B2:B13)
偶数行の合計：=SUMPRODUCT((MOD(ROW(B2:B13),2)=0)*B2:B13)

仕組みはシンプルで、MOD(ROW(範囲),2)が各行番号を2で割った余り（1=奇数行、0=偶数行）を返し、条件に一致する行がTRUE(=1)、しない行がFALSE(=0)になります。これを各セルの値に掛けて合計するため、対象行だけが足し算に残るというわけです。

MODの除数と余りを変えれば「2行おき」「3行おき」にも応用でき、ROW関数をCOLUMN関数に変えれば1列おきの合計もできます。

この数式は通常のEnterキーだけで確定できます。かつて定番だった=SUM(IF(MOD(ROW(範囲),2)=1,範囲,""))をCtrl+Shift+Enterで確定する配列数式は、古いバージョンでは今も動きますが、Microsoft 365やExcel 2021では動的配列の導入によりCtrl+Shift+Enter自体が不要になっており、あえて選ぶ理由はなくなりました。

注意点として、SUMPRODUCTは掛け算を行うため、範囲内に文字列が混ざっていると#VALUE!エラーになります（原因3とあわせてデータを確認してください）。

方法3：Microsoft 365 / Excel 2021ならFILTER関数

新しいバージョンなら、FILTER関数で「対象の行だけ抽出してから合計」と書けます。考え方が直感的で、数式の検証もしやすい方法です。

=SUM(FILTER(B2:B13, MOD(ROW(B2:B13),2)=1))

FILTER関数はExcel 2019以前では使えないため、ファイルを共有する相手の環境が古い場合は方法1・2を選んでください。

再発防止：そもそもズレない表を作る

最後に、合計ズレを「直す」のではなく「起こさない」ための習慣を2つ紹介します。

テーブル機能で範囲ミスをなくす

表内のセルを選択してCtrl+Tでテーブルに変換すると、数式は=SUM(テーブル1[売上])のような構造化参照になり、行の追加・削除に合計範囲が自動で追従します。「範囲の指定し忘れ」「重複選択」というミスの入り込む余地がなくなります。

集計はピボットテーブルに任せる

クロス集計（縦横に合計を持つ表）を手作業のSUMで組むこと自体がリスクです。元データを1行1レコードの形で持ち、集計表はピボットテーブル（「挿入」タブ →「ピボットテーブル」）で作れば、縦計・横計・総計が常に同じデータソースから計算されるため、構造的にズレが発生しません。データ更新時は右クリック→「更新」を忘れずに。