【Excel】エクセルで小計の合計を求める方法（SUBTOTAL関数・総計・中計）

エクセルで売上や数量を集計するとき、部署別や担当者別の小計を並べたあとに、その小計だけを合計したい場面があります。

単純なSUM関数では明細まで二重に数えてしまうことがあり、フィルターで非表示にした行まで含まれる点にも注意が必要です。

小計の合計を正しく求めるには、集計対象が明細なのか小計行なのかを分けて考えることが重要です。

この記事では、SUBTOTAL関数で中計を作成し、総計や小計の合計を求める方法をわかりやすく解説していきます。

 

SUBTOTAL関数による小計と総計の計算方法

それではまず、SUBTOTAL関数を使って小計と総計を分けて計算する方法について解説していきます。

部署	担当者	売上
営業一課	田中	120000
営業一課	佐藤	90000
営業一課 小計		210000
営業二課	鈴木	150000

SUBTOTAL関数は、指定した範囲の合計や平均などを求める関数です。

SUBTOTAL関数の大きな特徴は、範囲内にある別のSUBTOTAL関数の結果を自動的に集計から除外できることです。

この性質により、小計を含む表でも総計を二重計上せずに計算できます。

 

小計行へのSUBTOTAL関数の入力

小計を作る部署ごとの最後の行に、対象となる売上範囲を指定したSUBTOTAL関数を入力します。

たとえば、1行目が見出しで、営業一課の売上がC2からC3にある場合は、C4に数式を入力しましょう。

数式の最初の9は、合計を求めるSUMを表す集計方法の番号です。

この数式ではC2からC3の数値が合計され、C4には210000と表示されます。

セルに数式を入力したら、セルの表示形式を桁区切りや通貨表示に整えると、金額を確認しやすくなります。

SUBTOTAL関数は明細ごとの集計ブロックごとに設定するため、部署や商品分類が変わる位置に小計行を置くのが基本です。

 

総計行へのSUBTOTAL関数の入力

続いて、表の最終行に総計用のSUBTOTAL関数を入力します。

売上データと小計行を含めてC2からC9に配置している場合、総計セルには次の数式を入力します。

この総計では、C4やC8などにあるSUBTOTAL関数の小計結果は自動的に無視されます。

つまり、総計セルは明細の売上だけを一度ずつ合計します。

SUM関数で同じ範囲を足すと、小計行と明細行の両方が対象となり、実際より大きな金額になる可能性があります。

小計を含む範囲の最終合計には、SUMよりSUBTOTALを使うと二重計上を防ぎやすくなります。

 

集計番号と非表示行の扱い

SUBTOTAL関数では、9を指定するとフィルターで非表示になった行を合計から除外します。

手動で行を非表示にしたデータも除外したい場合は、集計番号を109に変更します。

9と109はどちらも合計を意味しますが、109ではフィルター非表示と手動非表示の両方を除外します。

月次報告で不要な担当者の行を隠して集計するなら、109を選ぶと表示中の数値だけを確認できます。

フィルター操作を前提とする一覧表では、SUBTOTAL関数を使うことで画面上に見えているデータに合わせた集計が可能です。

 

データタブの小計機能による中計の作成

続いては、データタブの小計機能を使って中計を自動挿入する方法を確認していきます。

部門	商品	金額
東京	A商品	50000
東京	B商品	70000
大阪	A商品	60000

エクセルの小計機能は、同じ分類のデータが続く場所に合計行を挿入する機能です。

分類別の売上や支店別の経費など、並べ替え済みの一覧を素早く集計したいときに役立ちます。

小計機能を実行すると、各グループの末尾にSUBTOTAL関数を含む中計行が作成されます。

 

分類列による並べ替え

小計機能を使う前に、グループ分けの基準となる列でデータを並べ替えます。

部門別に小計を出すなら、表内のセルを選択し、データタブの並べ替えから部門列を昇順に設定しましょう。

東京、大阪、名古屋のように同じ部門名が連続していれば、部門が切り替わる位置に小計を作成できます。

並べ替えを省略すると、同じ部門が表の複数箇所に散らばり、意図しない中計が複数作られるかもしれません。

小計機能では、最初に並べ替える列と、グループ化したい列を一致させることが欠かせません。

 

小計ダイアログでの集計設定

表の任意のセルを選択した状態で、データタブにあるアウトラインの小計をクリックします。

小計ダイアログでは、グループの基準を部門、集計方法を合計、集計するフィールドを金額に設定します。

選択後にOKを押すと、部門が変わるごとに部門の集計行が追加されます。

既に小計がある表で設定をやり直す場合は、現在の小計をすべて置き換える設定を確認してから実行しましょう。

 

アウトライン表示による中計の確認

小計機能を実行すると、ワークシート左端にレベル番号とアウトラインの記号が表示されます。

レベル2を選ぶと明細を隠して小計行だけを表示でき、レベル3を選ぶと明細を含む詳細表示に戻せます。

小計だけを一覧で確認したい会議資料では、レベル2の状態で印刷範囲を確認する方法も便利です。

アウトラインは行を削除せずに明細を折りたためるため、集計結果と根拠データを一つの表で管理できます。

 

SUM関数とSUMIF関数による小計行だけの合計

続いては、作成済みの小計行だけを合計したい場合の数式を確認していきます。

A列	B列	C列
営業一課 小計	小計	210000
営業二課 小計	小計	180000
小計の合計	総計	390000

小計行が一定間隔で並んでいる場合でも、すべての数値セルをSUM関数で合計すると明細も一緒に足されます。

小計セルを個別に指定する方法、または小計を示す文字列を条件にして合計する方法を使い分けましょう。

小計行だけの合計を作る目的は、中計の一覧を再集計したい場合や、別表に転記した集計値を確認したい場合にあります。

 

離れた小計セルを指定するSUM関数

小計セルの位置が決まっている場合は、SUM関数の引数に小計セルだけを指定できます。

たとえばC4、C8、C12がそれぞれの部署の小計なら、総計用セルには次の数式を入力します。

カンマで区切ったセル参照だけが対象になるため、途中にある明細の数値は加算されません。

ただし、部署の追加によって小計行の位置が変わると、数式を修正する必要があります。

固定した帳票や、小計の数が少ない表に向く方法です。

 

文字列を条件にするSUMIF関数

A列に小計という文字が入り、C列に金額がある表では、SUMIF関数で小計行だけを抽出できます。

1行目が見出しの場合、A2からA100を条件範囲、C2からC100を合計範囲として指定します。

アスタリスクは任意の文字列を表すワイルドカードです。

営業一課 小計や大阪支店 小計のように、前に異なる文字があっても小計を含むセルを条件として判定できます。

SUMIF関数を使うと、小計行の追加や位置の変化があっても、文字列のルールが同じなら数式を直さずに対応できます。

一方で、明細の説明欄にも小計という文字を入れると誤集計の原因になるため、集計用の表示ルールを決めておくことが大切です。

 

総計と小計の使い分け

総計は通常、全明細を一度ずつ足した最終的な合計額を指します。

小計の合計は、各分類で算出した中計を再度足した数値であり、明細と小計が混在する表では計算対象を意識する必要があります。

SUBTOTAL関数で作られた小計なら、総計にもSUBTOTAL関数を使う方法がもっとも管理しやすい選択です。

別シートへ小計値だけを並べた場合や、特定の小計だけを合算する場合にはSUMまたはSUMIF関数が役立ちます。

 

フィルター表示に対応する集計方法

続いては、フィルターで表示を絞り込んだ表の小計と合計について確認していきます。

担当者	状態	受注額
田中	完了	80000
佐藤	保留	50000
鈴木	完了	90000

フィルターで完了だけを表示しているのに、合計セルに保留の金額まで含まれると確認作業がしにくくなります。

SUM関数は非表示の行も集計するため、画面に見えているデータだけを合計したいときには向きません。

フィルターと連動した合計には、SUBTOTAL関数の9または109を使うのが基本です。

 

フィルター非表示を除く集計番号

=SUBTOTAL(9,C2:C100)と入力すると、オートフィルターで隠れた行を除いた合計を表示できます。

状態列で完了だけに絞り込めば、画面上に表示された完了分の受注額だけが合計されます。

集計結果がフィルターの変更に応じて自動で変わるため、担当者別や月別の確認にも使いやすいでしょう。

小計行にもSUBTOTAL関数を使っていれば、絞り込み後の総計でも重複を避けられます。

 

手動非表示を除く集計番号

行を右クリックして非表示にした行も合計から外したい場合は、集計番号109を使用します。

9では手動で非表示にした行が合計に残るのに対し、109では対象外になります。

確認済みの明細を一時的に隠す運用や、除外候補を折りたたむ作業では109が便利です。

フィルターだけを使うのか、手動で非表示にするのかによって、9と109を選び分けましょう。

 

テーブル機能との併用

データ範囲をテーブルとして設定すると、見出しのフィルターや集計行を管理しやすくなります。

テーブルデザインの集計行を有効にすると、列の下部から合計や平均などを選択できます。

テーブルの集計行はフィルターの表示状態に連動するため、絞り込み後の確認に適しています。

ただし、部門ごとの中計を複数作る用途では、データタブの小計機能やSUBTOTAL関数を組み合わせた表のほうが見やすい場合もあります。

 

小計と総計で起こりやすい集計ミス

続いては、小計や総計を計算するときに起こりやすいミスと確認方法を解説していきます。

確認項目	誤りの例	確認結果
総計の数式	=SUM(C2:C20)	小計を二重計上
条件文字列	小計の表記ゆれ	SUMIFの漏れ
範囲	行追加後に未拡張	新規データ未集計

合計額が合わないときは、数式そのものだけでなく、範囲や表示方法、文字列の統一も確認します。

特に小計行を含めたSUM関数は、見た目では気づきにくい二重計上の代表例です。

 

SUM関数による二重計上

明細行の間に小計行がある表で、最終行に=SUM(C2:C50)と入力すると、小計も明細も同時に足されます。

たとえば明細の合計が100万円で、小計行の合計も100万円なら、SUM関数の結果は200万円になることがあります。

最終総計には=SUBTOTAL(9,C2:C50)を使用するか、小計を含まない明細範囲だけを指定しましょう。

数式バーで参照範囲を確認し、小計行がどの関数で作られているかを見る習慣が役立ちます。

 

小計ラベルの表記ゆれ

SUMIF関数で小計という文字列を条件にする場合、ラベルの表記が統一されていないと対象から漏れることがあります。

小計、部門計、合計、課別小計などが混在すると、”*小計*”という条件では部門計が集計されません。

集計用の列を別に作り、小計行には必ず中計、総計行には総計のような判定用文字を入れる方法もあります。

人が読むラベルと数式が判定するラベルを同じルールで管理すると、表の更新後も集計が安定します。

 

追加行による参照範囲の不足

月ごとに明細が増える表では、C2:C100のような固定範囲が不足する可能性があります。

101行目以降に追加した金額が総計に反映されていない場合は、数式の範囲を確認します。

テーブルとして管理すれば、データを追加したときに数式の参照範囲が自動拡張されやすくなります。

また、余裕を持った範囲を設定する場合でも、別の集計表まで含めないように配置を整えることが必要です。

 

まとめ エクセルで総計と中計の小計を合計する方法

エクセルで小計の合計を求める場合は、明細と小計のどちらを合計したいのかを最初に明確にします。

明細と小計が混在する一覧の総計には、SUBTOTAL関数を使うことで二重計上を防げます。

部門別や商品別の中計を手早く作るなら、データタブの小計機能が便利です。

作成済みの小計行だけを合計したいときは、位置が固定ならSUM関数、行が増減するならSUMIF関数を使い分けましょう。

フィルターで表示中のデータだけを確認したい場合は、SUBTOTAL関数の9を選び、手動で隠した行も除外したい場合は109を指定します。

数式の参照範囲や小計ラベルの表記を定期的に確認すれば、売上、経費、在庫などの集計表でも正確な総計を維持できます。