LWP | SUBTOTALで絞り込んだ行の金額を集計する
SUBTOTALで絞り込んだ行の金額を集計する
図解1|絞り込んだのに、合計が変わらない

図1|見えている二行の10400円と、表全体の14000円を区別する。
解説1|絞り込んだのに、合計が変わらない
ストーリー
ナマズさん「C001の受注だけにしたのに、合計は同じ?」
フナさん「表示する行と、足す範囲を確かめましょう。」
全国規模の中華食材卸・淡水社の架空例である。表の一行は一つの受注明細。金額は数量×取引単価で求めた受注金額で、売上確定額ではない。税・送料・値引きは扱わない。
入力例
検証用の新しいExcelブックで、A1:C4へ次の表を入れる。集計式はフィルターで隠れない別の位置に置く。
行 |
A列 |
B列 |
C列 |
|---|---|---|---|
1 |
明細ID |
得意先 |
受注金額 |
2 |
R01 |
C001 |
7200 |
3 |
R02 |
C001 |
3200 |
4 |
R03 |
C002 |
3600 |
C001だけをフィルターで表示すると、見える金額は7200円と3200円になる。しかしSUMでC2:C4を足した結果は14000円のままである。フィルターは行を表示対象から外す操作で、元の金額を削除する操作ではない。
読むポイント
R01は干ししいたけ500g入り6袋×1200円、R02は豆板醤1kg入り4本×800円、R03は同じ干ししいたけ3袋×1200円の受注例に対応する。袋と本の数量を足さず、単位が円の受注金額を集計する。
図解2|表示する行と、足す範囲を分ける

図2|表示状態を変えても、SUMへ指定した範囲は変わらない。
解説2|表示する行と、足す範囲を分ける
ストーリー
フナさん「フィルターは行の表示を変え、SUMは指定範囲を足します。」
表の見え方と式の参照範囲は、別の仕組みで決まる。SUM(C2:C4)はその範囲の金額を足す。フィルターによって4行目が見えなくなっても、C4はC2:C4の一部として残っている。
比較する数式
次のSUM式はE6へ入力する。解説3で示す二つのSUBTOTAL式はE7とE8へ置く。いずれもフィルター範囲A1:C4より下の行にあり、R03の4行目を非表示にしても集計セルは表示される。
=SUM(C2:C4)結果の読み方
状態 |
見える明細 |
SUMの結果 |
|---|---|---|
全行表示 |
R01、R02、R03 |
14000 |
C001でフィルター |
R01、R02 |
14000 |
この挙動は誤計算ではない。表全体の金額を表示したいなら、その目的に合っている。フィルターで見えている行の金額を求めたい場合は、表示状態を反映する集計方法を選ぶ。
読むポイント
「画面に見えない行は集計されない」という前提を、すべての関数へ当てはめない。何を足したいのかを決めてから、関数の扱う範囲と行の状態を確認する。
図解3|SUBTOTALでフィルター後を集計する

図3|関数番号9と109は、どちらもフィルターで除かれた行を含めない。
解説3|SUBTOTALでフィルター後を集計する
ストーリー
ナマズさん「見えている二行の金額になった。」
フナさん「C001で絞った状態をそろえて比べます。」
SUBTOTALは、第一引数の関数番号で合計や件数などの集計方法を指定する。9と109はどちらも合計で、フィルターの結果から除かれた行を含めない。C001の二行を表示している状態なら、7200+3200で10400になる。
比較する数式
=SUBTOTAL(9,C2:C4)=SUBTOTAL(109,C2:C4)集計結果
数式 |
C001でフィルターした結果 |
|---|---|
=SUM(C2:C4) |
14000 |
=SUBTOTAL(9,C2:C4) |
10400 |
=SUBTOTAL(109,C2:C4) |
10400 |
読むポイント
9と109がいつも同じ、という意味ではない。ここではフィルターでR03を除いているため一致する。次はフィルターを解除し、手動で行を非表示にした場合を比べる。
図解4|手動の非表示では、9と109が違う

図4|同じように行が見えなくても、非表示の方法で集計対象が変わる。
解説4|手動の非表示では、9と109が違う
ストーリー
ナマズさん「同じ非表示でも、隠した方法が違うのか。」
フィルターを解除してから、4行目を手動で非表示にする。関数番号9は手動非表示の行を含み、109は含めない。同じ元表と参照範囲でも、9は14000、109は10400になる。
結果の比較
非表示の方法 |
SUBTOTAL 9 |
SUBTOTAL 109 |
|---|---|---|
フィルターでR03を除外 |
10400 |
10400 |
フィルター解除後、R03を手動非表示 |
14000 |
10400 |
表示している金額だけを見たいのか、手動で隠した補足行も集計したいのかで使い分ける。見た目をそろえるために行を隠した操作が、109の集計結果を変えることもある。
読むポイント
本稿は縦に並んだ金額列を対象にする。SUBTOTALの101〜111が除くのは手動で非表示にした行であり、列を非表示にする操作へ同じ説明を移さない。どの操作で見えなくしたかを、結果と一緒に残す。
図解5|集計条件を戻して、数字を照合する

図5|元の表示へ戻したときに、同じ表全体の金額へ戻るかを確かめる。
解説5|集計条件を戻して、数字を照合する
ストーリー
フナさん「表示条件と参照範囲を、集計結果と一緒に残します。」
比較を終えたら、フィルターと手動非表示をどちらも解除する。三行すべてを表示した状態では、今回のSUM、SUBTOTALの9、109はすべて14000になる。条件を戻して再確認すると、非表示操作の置き忘れを見つけやすい。
照合表
状態 |
SUM |
SUBTOTAL 9 |
SUBTOTAL 109 |
|---|---|---|---|
全三行を表示 |
14000 |
14000 |
14000 |
C001でフィルター |
14000 |
10400 |
10400 |
解除後R03を手動非表示 |
14000 |
14000 |
10400 |
まとめと今回の仕掛け
表示行の金額と表全体の金額は、異なる目的の集計である。式の参照範囲、フィルター条件、手動非表示をそろえて比べる。行を追加したらC2:C4のような固定範囲に新しい行も入るかを別に確認する。表示状態だけでなく、何の金額を求めているかまで見出しへ示す。
出典
公式仕様から独立した受注明細例を設計した。Microsoft Support SUBTOTAL。数式は関数の違いを示す最小例であり、実運用の数式アプリケーション全体を設計するものではない。
