【Excel】オートSUMの合計が合わないとき:範囲・文字列・非表示行の確認

オートSUMを押したのに合計が少ないときは、Excelの計算より先に「どのセルを合計する式になったか」を確認します。オートSUMは表の配置から範囲を提案する機能で、担当者が意図した明細を判断する機能ではありません。空白行、文字列の数字、フィルターによる非表示は、それぞれ違う原因として調べます。

オートSUMの候補範囲は、確定前に確認する

Windows版Excelでは、合計を出したい空きセルを選び、[ホーム]または[数式]の[オートSUM]を選びます。数式と参照範囲が表示された段階で、先頭の明細から最後の明細まで含まれているか確認し、正しければEnterで確定します。

一般的には数値列の直下や数値行の右側に合計を置きます。ただし、上にも左にも数値がある配置、途中で表が分かれている配置では、期待とは違う範囲が選ばれることがあります。選択された枠を見ずにEnterを押すと、その違いに気付かないまま数式が残ります。

確定後も合計セルを選べば、数式バーで「=SUM(B2:B6)」のような式を読めます。結果がそれらしい金額でも、範囲が正しいとは限りません。見出し、月初の行、最終日の行を目印にし、合計セル自身を範囲に含めないようにします。

途中の空白は、合計漏れとデータ未確定を分ける

B2が100、B3が200、B4が空欄、B5が300、B6が400という例では、全明細の合計は1000です。もし提案された式が「=SUM(B5:B6)」なら、計算結果700は式として正しくても、B2とB3が対象外です。空欄の前後に数値がある表では、候補を特に注意して見ます。

対象がB2:B6だと分かっているなら、合計セルへ次の式を直接入力しても構いません。範囲内に空白があってもSUMは残りの数値を合計できます。空白をなくすためだけに、未入力の売上へ0を入れる必要はありません。

=SUM(B2:B6)

「未報告」と「売上0」は意味が違います。未報告を0へ置き換えると、集計表は完成したように見えても業務上の欠損が隠れます。必要なら別の列で確認状況を管理し、合計の対象範囲と、入力完了の確認を分けてください。途中に小計行がある表では、明細と小計を二重に足していないかも確認します。

数字に見える文字列は、範囲内でも足されない

CSVの取り込みや貼り付けで、100という見た目でも文字列として保存されることがあります。SUMにセル範囲を渡した場合、その中の文字列は合計されません。例えば先ほどのB3が文字列の「200」なら、範囲をB2:B6へ直しても合計は800です。

空きセルに「=ISNUMBER(B3)」を入れると、B3が数値ならTRUE、文字列ならFALSEになります。左寄せや右寄せは手動でも変更でき、緑の三角も別の警告で出るため、見た目だけで断定しません。数式バーで先頭のアポストロフィ、空白、通貨記号や「円」などが含まれていないかも確認します。

本当に数量・金額の列であれば、元の値と照合したうえでエラー表示の[数値に変換する]などを使います。ただし、商品コードや郵便番号まで一括で数値にすると先頭の0が失われます。変換前の列を残し、数件ずつ確認してから対象を広げます。文字列を無差別に0に変える修正も避けましょう。

変換後は、同じ合計式で結果が変わったかと、変換したセルの値が元資料と一致するかを両方確認します。100円や1,000という文字から記号を取り除く作業では、桁区切りと小数点の地域差にも注意が必要です。異なる形式が混在する列は、出所ごとに整理してから処理する方が確認しやすくなります。

非表示行を含めるかどうかで、関数を選ぶ

通常のSUMは、行を隠しても、フィルターで表示から外しても、参照範囲内の数値を合計します。画面に見える行だけの集計が目的なら、SUBTOTALを使います。オートSUMの結果は配置やフィルターの状態によっても異なるので、関数名まで確認してください。

式	縦方向の集計対象
=SUM(B2:B6)	非表示を含む数値
=SUBTOTAL(9,B2:B6)	フィルター除外行を除く。手動非表示は含む
=SUBTOTAL(109,B2:B6)	フィルター除外行と手動非表示行を除く

B3の200をフィルターで除外し、B5の300を手動で非表示にした例なら、SUMは1000、SUBTOTALの9は800、109は500です。これは元の数値がすべて数値として入力されている場合です。SUBTOTALでも文字列の数字が自動的に数値へ修復されるわけではありません。

範囲の追加や小計行で、後からずれることもある

月末まで使う表では、式を作った時点だけでなく、明細を追加した後も確認します。固定された「B2:B6」の外側へ値を追記しても、その新しいセルが必ず合計に入るとは限りません。挿入位置や表の構造によって参照の変化は違うため、最終明細の行番号と式の末尾を見比べます。

見栄えのために空白行を挟む表、部門ごとの小計を含む表は、単純に列全体をSUMすると二重集計になることがあります。集計用の明細だけを連続した表にまとめ、必要ならExcelテーブルの集計行を利用する方法もあります。既存の帳票を急に組み替える場合は、式の参照先や印刷範囲への影響を先に確認します。

SUBTOTALは範囲内にある別のSUBTOTALによる小計を除外しますが、通常のSUMで作った小計まで一律に除外する機能ではありません。また、9と109の違いは非表示の「行」に対するものです。横方向に並んだ表で列を隠す用途へ、そのまま当てはめないでください。

合計が合わないときは、順番に切り分ける

まず元データを残し、合計セルの式を読みます。次に範囲の先頭・末尾と、抜けているブロックを確認します。それでも合わなければ、足されるはずのセルが数値か、フィルターや手動非表示があるか、小計を重ねて足していないかを順に調べます。

選択範囲の合計をステータスバーで見る方法は、確認の補助になります。ただし、同じ選び間違いや文字列の混在が残っていれば、同じ誤った数字を確認しているだけです。元帳の明細件数、数量、日付の範囲など、別の観点でも照合してください。COUNTで数値セルの件数を調べ、想定件数と比べる方法も役立ちます。

式は正しいのに入力変更が反映されない場合は、[数式]の計算方法が手動になっていないかも確認します。共同利用のブックでは計算方法を変える前に担当者へ確認し、再計算後に値を照合します。エラーをIFERRORで0にするだけでは、集計漏れの原因は解決しません。

修正後は、戻し方も含めて確認する

式の範囲を変更した直後ならCtrl+Zで戻せます。数値への変換や表の並べ替えまで行った場合は、どの操作まで戻すかを確認しながら進めます。保存済みの元ファイルと修正後のコピーを比較できる状態にしておくと、入力値を誤って変えたときも調査できます。

修正後の合計は、対象の明細と非表示行の扱いを照合します。同じ1000・800・500でも、範囲の抜けや文字列の混在で偶然一致していないかを見ます。最後は「何を集計した数字か」を説明できる式になっているか確認しましょう。

公式資料:オートSUMの操作、SUBTOTALの非表示行の扱い。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】