SUBTOTAL関数で万能集計！SUM関数などとの違いも
Excel 2021, 2019, 2016 Microsoft 365
解説記事内の画像はExcel 2021のものです。
スポンサーリンク
「この方法で、この範囲を集計してね」
という風に、数式内で集計方法を指定するので、SUBTOTAL関数1つで合計や平均、カウントなどの集計ができます。
じゃぁ、SUMやAVERAGE、COUNT関数と何が違うの？となりますが、最大の違いは、非表示の行があったときに、どう計算されるか。
これは合計の例。
フィルターで抽出がかかっていない状態では、すべての行が表示されているので、SUM関数でもSUBTOTAL関数でも結果は同じ。
でも！
フィルターで抽出がかかると違いが！
SUM関数は最初と変わらず、抽出されずに隠れているものも含めて指定範囲を合計しますが、SUBTOTAL関数は、非表示になっている行は含めず、表示されているものだけで集計しています！
スポンサーリンク
SUBTOTAL関数の集計方法
練習用データを操作しながらご覧いただいている方は、画面左下でSheet2に切り替えてください。
SUBTOTAL関数1つで、合計や平均など様々な集計ができるので、どんな集計をしたいのかを、数式内に数字で指定します。
1～11と、100～111の2パターンがありますが、いずれも、フィルターで抽出されているものだけを集計することに変わりはありません。
じゃぁどこが違うんだとなりますが、それは、この表の下で解説しています。
| 集計方法 | 集計方法 | 種類 | |
|---|---|---|---|
| 1 | 101 | 平均 | AVERAGE |
| 2 | 102 | カウント | COUNT |
| 3 | 103 | 空白以外をカウント | COUNTA |
| 4 | 104 | 最大値 | MAX |
| 5 | 105 | 最小値 | MIN |
| 6 | 106 | 掛け算 | PRODUCT |
| 7 | 107 | 標本で標準偏差 | STDEV.S |
| 8 | 108 | 母集団の標準偏差 | STDEV.P |
| 9 | 109 | 合計 | SUM |
| 10 | 110 | 標本で分散を推定 | VAR.S |
| 11 | 111 | 母集団の分散 | VAR.P |
※スマホでご覧いただいている方は、表を横にスクロールできます。
集計方法1～11と、100～111では、フィルターを使っているときは同じですが、フィルターを使っていないときに違いが出てきます。
この図は、フィルターを使っていない表。
でも、まだ違いが出ていません。
ここで、行を選択（セルじゃなくて行を選択）して、非表示にすると、
集計方法1～11と、100～111で集計結果に違いが出ます！
というわけで、集計方法1～11と、100～111では、フィルターで抽出をかけているときは同じですが、フィルターをかけていない普通の表で、行を選択して非表示にしたときに違いが出ます。
| 集計方法1～11 | 集計方法100～111 | |
|---|---|---|
| フィルター | 見えているものだけ集計 | 見えているものだけ集計 |
| 行を選択して非表示 | 隠れているものも集計 | 見えているものだけ集計 |
※スマホでご覧いただいている方は、表を横にスクロールできます。
ここまでで、SUM関数とSUBTOTAL関数の違い、同じSUBTOTAL関数でも集計方法1～11と、100～111の違いを見ていただいたので、次の章「二重集計を防ぐSUBTOTAL関数」をご覧いただきたいのですが、どうしても下記が気にかかって仕方がない方は、このまま読み進めてください。
さて、もしかすると気になる人は気になってしまう、フィルター機能と行の非表示を併用した場合。
ですが、フィルターはそもそも、［▼］で抽出、または抽出のクリア（解除）のいずれかの操作で、行を直接選択して非表示にした行も、再表示されてしまう特性があります。
（どちらのタイミングで再表示されるかは、ここでは端折ります。）
なので、フィルター機能と行の非表示の併用自体が、そもそも非現実的。
非現実的なので覚える必要まったく無しですが、調べちゃったので書き残しだけしておきます。
【規定どおり1～11と、100～111で集計結果に違いが出る場合の操作手順】
- 行を選択して非表示 → フィルターON（［▼］で抽出をかける前）
- フィルターON（［▼］で抽出をかける前） → 行を選択して非表示
【イレギュラーで集計結果が同じになってしまう場合の操作手順】
- 行を選択して非表示 → フィルターON → ［▼］で抽出
- フィルターON → ［▼］で抽出 → 行を選択して非表示
フィルターで抽出がかかっている場合には、集計方法1～11も、100～111も、行を選択して非表示にしたかどうかにかかわらず、見えているものだけ集計します。
スポンサーリンク
二重集計を防ぐSUBTOTAL関数
練習用データを操作しながらご覧いただいている方は、画面左下でSheet3に切り替えてください。
SUBTOTAL関数には、集計範囲内にある他のSUBTOTAL関数を無視するという特徴もあります。
これについて、小計と総合計のある表で見ていきましょう。
小計や総合計は、おなじみSUM関数を使うほかに、SUBTOTAL関数を使うこともできます。
この表で、小計や総合計がどんな風に計算されているかを視覚的に見るために、
［数式］タブにある、［数式の表示］ボタンをONにします。
まずは、SUM関数で総合計を出しているB11番地を選択すると、この数式は、小計のB5と、B10番地を合計していることが分かります。
今度は、同じく総合計でSUM関数を使っていますが、別の例。
C11番地を選択すると、小計を含めないよう、個々のデータを合計して総合計を求めていることが分かります。
SUM関数では、指定した範囲を忠実に合計しますので、途中の小計は合計範囲に含まれないように指定したわけです。
でも！
SUBTOTAL関数が設定してあるD11番地を選択してみましょう！
D列は、小計もSUBTOTAL関数で集計しているわけですが、SUBTOTAL関数には、集計範囲内にある他のSUBTOTAL関数を無視するという特徴があるので、総合計の範囲指定は、小計があるセルもおかまいなく、1つの連続した範囲として指定してしまっても、ちゃんと計算されるわけです！
［数式］タブにある、［数式の表示］ボタンをOFFにしてみましょう。
SUM関数でもSUBTOTAL関数でも集計できるわけですが、階層になっている表では、SUBTOTAL関数の特性を使う手もあるよという例です。
テーブルの集計は自動でSUBTOTAL関数
練習用データを操作しながらご覧いただいている方は、画面左下でSheet4に切り替えてください。
また、数式も、集計範囲を列見出しで表示するため、どこの集計をしているかが一目瞭然です！
スポンサーリンク
