SUBTOTALで絞り込んだ行の金額を集計する

LWP | SUBTOTALで絞り込んだ行の金額を集計する

SUBTOTALで絞り込んだ行の金額を集計する

図解1|絞り込んだのに、合計が変わらない

得意先C001で二行を表示してもSUMは三行の14000円を合計する違いを示す図

図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|表示する行と、足す範囲を分ける

フィルターが選ぶ表示行と、SUMが読むC2:C4の範囲を同じ表へ描き分ける図

図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でフィルター後を集計する

C001のフィルター状態でSUMは14000、SUBTOTALの9と109は10400になる比較図

図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が違う

手動でR03を非表示にした場合、9は14000、109は10400になる違いを示す図

図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|集計条件を戻して、数字を照合する

全行表示、フィルター、手動非表示の三条件を表にし、解除後の14000円を再確認する図

図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。数式は関数の違いを示す最小例であり、実運用の数式アプリケーション全体を設計するものではない。