SUBTOTAL関数の使い方
リストやデータベースの部分集計を返します。
200720102013201620192021365
- 構文
-
=SUBTOTAL(集計方法, 参照1, [参照2], ...) - 引数
-
- 集計方法必須
- 参照1必須
- 参照2省略できる
このあとも、同じように続けて渡せます。
- どんな関数か
数値(9=SUM, 1=AVERAGEなど)で集計方法を指定します。オートフィルターを適用している場合、非表示になっている行を自動的に除外して計算できる点が最大の特長です。
- 使用例
-
B2 に
=SUBTOTAL(9, A2:A4)を入れると、こうなります。A B 1 表示計 2 100 300 3 (非表示) 4 200 数式を入れたセル
結果: 表示されている行のみの合計
つまずきやすいところ
- 1〜11 と 101〜111 で意味が違う。101〜111 は手で隠した行も除く
- どちらもフィルターで隠れた行は除く
- 範囲の中に SUBTOTAL があると、二重には数えない
よくあるエラーと、その直し方
SUBTOTALを使う実例レシピ（7件）
- フィルタで隠された行を除外して合計するオートフィルタで「東京」を選んだ時、画面に見えている行だけの合計を出したい。
- フィルタ後、見えている行だけの平均を出す「食品」カテゴリで絞り込んだ後、その平均単価を表示したい。
- 手動で隠した行を除いて合計する非表示にした行（オートフィルタではない）を計算から除外したい。
- スライサーでグラフと数式を連動させるボタン1つで「支店」を切り替え、グラフと合計値を同時に更新したい。
- 検索結果に合わせて見出しの件数を動的に変える「現在 15 件が該当しています」というタイトルを検索状況で自動更新する。
- 見えている行の「平均」だけを出す（自動フィルタ連動）フィルタで絞るたびに、タイトル横にある平均値も自動で変わるように。
- フィルタで絞り込んだ後の「有効な回答数」を数えるアンケートを拠点で絞った時、何名が記入してくれたか回答数だけ出したい。