SUBTOTAL関数で万能集計！SUM関数などとの違いも

スポンサーリンク

「この方法で、この範囲を集計してね」
という風に、数式内で集計方法を指定するので、SUBTOTAL関数1つで合計や平均、カウントなどの集計ができます。

じゃぁ、SUMやAVERAGE、COUNT関数と何が違うの？となりますが、最大の違いは、非表示の行があったときに、どう計算されるか。

これは合計の例。
フィルターで抽出がかかっていない状態では、すべての行が表示されているので、SUM関数でもSUBTOTAL関数でも結果は同じ。

でも！
フィルターで抽出がかかると違いが！
SUM関数は最初と変わらず、抽出されずに隠れているものも含めて指定範囲を合計しますが、SUBTOTAL関数は、非表示になっている行は含めず、表示されているものだけで集計しています！

スポンサーリンク

SUBTOTAL関数の集計方法

練習用データを操作しながらご覧いただいている方は、画面左下でSheet2に切り替えてください。

SUBTOTAL関数1つで、合計や平均など様々な集計ができるので、どんな集計をしたいのかを、数式内に数字で指定します。
1ー11と、100ー111の2パターンがありますが、いずれも、フィルターで抽出されているものだけを集計することに変わりはありません。
じゃぁどこが違うんだとなりますが、それは、この表の下で解説しています。

※スマホでご覧いただいている方は、表を横にスクロールできます。

集計方法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に切り替えてください。

Excelの超重要機能のテーブルでは、「集計行」というテーブル専用機能のおかげで、超簡単にワンタッチでSUBTOTAL関数を設定できます。
しかも集計方法も、一覧から選ぶだけの簡単操作。

また、数式も、集計範囲を列見出しで表示するため、どこの集計をしているかが一目瞭然です！

関連リンク

• フィルターでデータを抽出！
• 簡単すぎて驚き！テーブルでデータ集計

スポンサーリンク

かつて、こんな後悔のご経験はありませんか？
もっと早くからパソコンを触っておけば...
今まさしく、あの時とは比べものにならない大きな進化が、今まで経験したことのない驚異的なスピードで私たちを巻き込んでいます。
そうです、AIです。
あの時の何倍も何十倍も大きな後悔をしないために、本当に必要な情報とテクニックが手に入る場所、それがBe Cool UsersのAI特化メンバーシップです！

Be Cool Users管理人の略歴・得意なことをまとめました！
お仕事のご依頼も「自己紹介・お仕事のご依頼につきまして」からご連絡くださいませ。
「こんなことできる？」もお気軽にご相談ください。