Excelで小計を除いた値を合計するにはどうすればよいですか？

Author Siluvia Last modified 2025-08-26

Excelでデータセットを扱う際、異なるグループやカテゴリごとに小計を計算して数値を整理・分析することが一般的です。しかし、これらの小計を追加した後、最終的な合計で二重カウントを防ぐために、小計の値を除外した総合計を計算したい場合があります。問題は、すべての数値を直接合計すると、小計が全体の合計に含まれてしまい、意図したよりも大きな合計になってしまうことです。これを避けるためには、小計行を除外して元のデータのみを合計する方法を見つける必要があります。この記事では、さまざまなシナリオで効率的に正確な結果を計算できるように、この問題を解決するためのいくつかの実用的な方法を紹介します。

A screenshot showing data mixed with subtotal values in Excel

ExcelでSUM関数を使用して小計を除いた値を合計する
 ExcelでSUBTOTAL関数を使用して小計を除いた値を合計する
 VBAコード - 小計行を除外して値を合計する
 フィルター - フィルターとSUBTOTALを使用して表示されている（小計以外の）行のみを合計する

ExcelでSUM関数を使用して小計を除いた値を合計する

グループごとの小計を計算するための広く採用されている方法は、各関連グループ内またはその下にSUM関数を挿入することです。ただし、これらのグループ小計行を無視してリスト全体の総合計を計算したい場合は、通常、追加の手順が必要です。以下に、これを行うための方法を示します：

1. 各グループ内でSUM関数を使用してそれぞれの小計を生成します。以下のスクリーンショットのようにしてください：

A screenshot showing the SUM function used to calculate group subtotals in Excel

2. 次に、小計行を含まない総合計を求めるために、次の数式を空白セルに入力します：
=SUM(B2:B21)/2

Enterキーを押して正しい合計を得ます。この数式が機能するのは、関連するグループの直後にSUM関数を使用して小計を挿入した場合、生データの合計が実質的に2倍になるためです。2で割ることで重複した合計を取り除きます。なお、この方法は、元の値と小計の合計が元のデータの合計のちょうど2倍になるようにデータ構造が整っている場合に最も適しています。

A screenshot showing how to sum values without subtotals using a formula in Excel

データ構造がより複雑である場合や、小計行の数が異なる場合、以下で説明する代替方法の方がより正確で柔軟なソリューションを提供する可能性があります。

Excelで小計を除いた値を合計する（SUBTOTAL関数を使用）

ExcelのSUBTOTAL関数は、フィルタリングされた行または表示されている行のみに対して計算を実行する組み込みの方法を提供します。これは、小計を含むリストを扱う際に特に有用です。SUBTOTAL関数は範囲内の他のSUBTOTAL結果を無視するように設定でき、これにより値の二重カウントを防ぎます。

この状況でSUBTOTAL関数を使用するには、次の手順に従ってください：

1. 各グループに対して次のようにSUBTOTAL関数を入力します：
=SUBTOTAL(9,B2:B10)

これにより、指定された範囲にSUM操作（関数番号9）が適用され、Excelの組み込み機能である「データ」→「小計」機能の一部としてよく使用されます。

A screenshot showing the SUBTOTAL function used to calculate group subtotals in Excel

2. 内部の小計を除外した総合計については、選択したセルに次の数式を入力します：
=SUBTOTAL(9,B2:B21)

Enterキーを押すと、範囲内のネストされたSUBTOTAL関数を無視してデータ値が自動的に合計されます。このアプローチは、グループ要約にSUBTOTAL関数を使用しているリストに適しており、小計を複数回合計するリスクを排除します。

A screenshot showing how to use the SUBTOTAL function to sum values without including subtotals in Excel

SUBTOTAL関数は、小計セルがSUMではなくSUBTOTALを使用して生成された場合に最も効果的です。また、行をフィルタリングしたり非表示にしたりした場合、SUBTOTALは表示されている（非表示ではない）データのみを合計するように設定できます。これは動的なレポート作成時に役立ちます。

VBAコード - 小計行を除外して値を合計する

データ構造が複雑であったり、小計を除いた生データ行のみを合計する自動化された方法が必要な場合には、簡単なVBAマクロを利用できます。このアプローチでは、特定の書式、数式の有無、または他の識別属性に基づいて、条件を満たす行のみをプログラムで識別し合計することができます。

例えば、小計行が数式（SUMやSUBTOTALなど）で計算されており、生データ行には静的な値しかない場合、VBAコードを設定して特定の列で数式を含まないセルのみを合計するようにできます。以下にその方法を示します：

1. クリックします 開発ツール > Visual Basic Microsoft Visual Basic for Applicationsウィンドウを開きます。
2. 新しいウィンドウで挿入 > モジュール をクリックし、次のコードをモジュールに貼り付けます：

Sub SumNonSubtotalRows()
    Dim WorkRng As Range
    Dim SumResult As Double
    Dim cell As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
    SumResult = 0
    For Each cell In WorkRng
        If Not cell.HasFormula Then
            SumResult = SumResult + cell.Value
        End If
    Next
    MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub

3. 実行ボタンをクリックしてコードを実行します。データを含む範囲を選択するように求められるプロンプトが表示されます。その後、マクロは数式を含まないセルのみを合計し、典型的な小計行をスキップします。

実践的なヒント: VBAループ内の基準をデータセットの構造に合わせて調整してください。例えば、特定のセル書式、特定のテキスト（隣接する列の「小計」など）、またはデータ行と小計を区別する他の特徴に基づいて小計行を識別するロジックを追加できます。

フィルター - フィルターとSUBTOTALを使用して表示されている（小計以外の）行のみを合計する

データセットが小計行をフィルタリングできる場合（例えば、それらを識別するラベル、キーワード、またはパターンがある場合）、Excelの組み込みフィルター機能とSUBTOTAL関数を一緒に使用して、表示されている（フィルタリングされていない）データ行のみを合計できます。この手法は、小計に一貫した指標やラベルがある場合に実用的です。

1. データセット内のどこかをクリックし、「 データ 」タブに移動して フィルター をクリックし、列にフィルターを有効にします。
2. 補助列のドロップダウンをクリックし、「小計」のチェックを外して小計行を非表示にし、元のデータのみを表示します。
3. 別のセルに次の数式を入力して、表示されている（フィルタリングされた）行のみを合計します。

=SUBTOTAL(9,B2:B21)

SUBTOTAL関数（SUMの場合の関数番号9）は、非表示の行を無視し、現在表示されている行（つまり、フィルタリング後に残った小計以外の行）の合計を返します。このソリューションは迅速で動的です。フィルターを再適用したりデータセットを拡張したりしても、数式はそれに応じて更新されます。

最高のオフィス業務効率化ツール

🤖	Kutools AI Aide：データ分析を革新します。主な機能：Intelligent Execution｜コード生成｜カスタム数式の作成｜データの分析とグラフの生成｜Kutools Functionsの呼び出し……
	人気の機能：重複の検索・ハイライト・重複をマーキング｜空白行を削除｜データを失わずに列またはセルを統合｜丸める……
	スーパーLOOKUP：複数条件でのVLookup｜複数値でのVLookup｜複数シートの検索｜ファジーマッチ……
	高度なドロップダウンリスト：ドロップダウンリストを素早く作成｜連動ドロップダウンリスト｜複数選択ドロップダウンリスト……
	列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示/非表示の切替｜範囲＆列の比較……
	注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブック＆ワークシートの管理｜オートテキストライブラリ｜日付ピッカー｜データの統合｜セルの暗号化/復号化｜リストで電子メールを送信｜スーパーフィルター｜特殊フィルタ（太字/斜体/取り消し線などをフィルター）……
	トップ15ツールセット：12 種類のテキストツール（テキストの追加、特定の文字を削除など）｜50種類以上のグラフ（ガントチャートなど）｜40種類以上の便利な数式（誕生日に基づいて年齢を計算するなど）｜19 種類の挿入ツール（QRコードの挿入、パスから画像の挿入など）｜12 種類の変換ツール（単語に変換する、通貨変換など）｜7種の統合＆分割ツール（高度な行のマージ、セルの分割など）｜… その他多数