SUBTOTAL関数の使い方: 小計を除いた合計とフィルター後の集計

=SUBTOTAL(9,C2:C8)はSUMと同じようにC2:C8の数値を足しますが、違いが2つあります。範囲内のほかのSUBTOTALの数式を飛ばすことと、フィルターで非表示になった行を飛ばすことです。最初の引数の9は、どの計算をするかを指定しています。

数式リセット

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

C8の総計は小計の行も含めて列全体を対象にしていますが、それでも445を表示します。C4とC7にはSUBTOTALの数式が入っているので、SUBTOTALがそれらを除くからです。F2は同じことをSUMで行い、すべての売上が2回数えられて890を表示します。合計の行をすべてSUBTOTALにしておけば、グループを追加したり移動したりしても総計を書き直さずに済みます。

SUBTOTAL関数の集計方法の番号

=SUBTOTAL(function_num, ref1, [ref2], ...)

=SUBTOTAL(と入力するとExcelがこの一覧を表示するので、覚える必要はありません。よく使うのは9と109(SUM)、1(AVERAGE)、103(表示されている行を数える)です。

数式リセット

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

ここでは何も非表示になっていないので、どの行もふつうの関数と同じ結果です。平均は88.33、行数は6、MAXは200、MINは30、SUMは530です。違いが出るのは行が非表示になったときだけで、それが次の節の内容です。

SUBTOTALの9と109、フィルターで非表示の行

データ > フィルター(Ctrl+Shift+L、MacではCmd+Shift+F)でフィルターをオンにし、Regionのドロップダウンリストで North を選びます。ほかの地域の行が非表示になります：

• =SUM(C2:C7)は相変わらず6行すべてを足します。
• =SUBTOTAL(9,C2:C7)と=SUBTOTAL(109,C2:C7)は表示されているNorthの行だけを足します。
• =SUBTOTAL(103,A2:A7)は画面に残っている行を数えます。2で、ステータスバーの「6 レコード中 2 個が見つかりました」と同じ数です。

2つのグループの違いが出るのは、手動で非表示にした行(行を選んで右クリック > 非表示)だけです。9はそれらを足し、109は足しません。合計を常に画面の表示と合わせたいなら109を使います。見た目を整えるためだけに行を隠していて、合計には含めたいなら9を使います。

SUBTOTALが対象にするのは行だけです。非表示の列は常に含まれるので、行方向の=SUBTOTAL(109,B2:G2)は非表示の列も足します。

SUBTOTALをいちばん手早く入れるには、フィルターをオンにした状態でオートSUMボタンを使います。ExcelはSUMではなく=SUBTOTAL(9,...)を書きます。データ > 小計はさらに進んで、ある列で並べ替えたリストのグループごとに合計の行と総計を挿入し、すべてSUBTOTALで書いたうえで、グループを折りたたむアウトラインのボタンも付けます。

AGGREGATE: エラーを飛ばせるSUBTOTAL

範囲の1つのセルにエラーがあると、SUMとSUBTOTALはそのエラーを返します。AGGREGATE(Excel 2010以降)はオプションの引数が1つ多いSUBTOTALで、オプション6はエラー値を無視します。

数式リセット

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

C3には#N/Aが入っているので、F2も#N/Aを表示します。F3はそれを無視して残りの5つを足し、450になります。最初の引数はSUBTOTALと同じ番号を使います(9はSUM、4はMAX)。ほかのオプションとして、5は非表示の行を無視し、7は非表示の行とエラーを無視し、3は非表示の行、エラー、入れ子のSUBTOTALとAGGREGATEの数式を無視します。C3を数値に置き換えると、F2はF3と同じ合計を表示します。

練習: 小計の上の総計

数式リセット

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: リストには地域ごとに小計があります。C9に、C2:C8を対象とし、小計の行を二重に数えない総計を入れてください。

答え合わせ答えを見る

SUBTOTALの合計がまだ合わない理由

• グループの合計にSUMを使っている。 SUBTOTALが範囲内で飛ばすのはほかのSUBTOTALの数式で、SUMの数式ではありません。=SUM(C2:C3)と書いたグループの合計はもう一度数えられます。合計の行をすべてSUBTOTALに変えてください。
• 行を手動で非表示にしていて、集計方法の番号が9になっている。 109を使ってください。
• データが行ではなく列に並んでいる。 非表示の列は飛ばされません。
• 必要なのはフィルターではなく条件。 SUBTOTALはフィルターで隠したものに従います。フィルターをかけずにNorthを合計するならSUMIFを使います。すべてのグループの集計を一度に出すなら、データに合計の行を入れなくてもピボットテーブルでできます。