【Excel／図解】SUBTOTAL関数でフィルター抽出後、表示セルのみを合計。SUMとの違いや「9」「109」の違いも解説
Excelでオートフィルターはよく使う。
抽出後データの合計をSUM関数で求めると、なぜか計算が合わない…
このような悩みはありませんか？
僕も以前、悩みました。でも大丈夫です。
オートフィルター使用時に合計を求める場合は
「SUBTOTAL(9,指定範囲)」か
「SUBTOTAL(109,指定範囲)」
を使いましょう。
なお、オートフィルターでSUBTOTAL関数を使う場合、第一引数は「9」でも「109」でも大勢に影響がないことがほとんどです。
でも本記事では具体例を用い、違いにもふれます。
「SUM(指定範囲)」は
オートフィルターや非表示で隠れたセルも計算します。
ポイントは上記２点ですが、本記事では簡単な例を用い解説します。
オートフィルター使うなら「SUBTOTAL」
まずはじめに「SUBTOTAL(9,指定範囲)」 について、ざっくり次のようなイメージだけお持ちください。
この予測変換からもわかるように
「SUBTOTAL(9,指定範囲)」は
⇒SUMと「ほぼ」同じ意味を持つ
それでは、簡単な表で説明していきます。
この例では、C3からC7までの合計をSUBTOTAL関数とSUM関数のそれぞれで計算します。
計算結果はいずれも33,000。
結果が同じなんだから、SUMでも良さそうな気もします。
でも、オートフィルターでデータを抽出する可能性がある場合は、SUMではダメ。
次の例をご覧ください。
オートフィルターで交通費以外の項目だけを抽出してみます。
計算結果が変わりました。
正しいのはSUBTOTALで計算した1,000。
ここでSUBTOTAL関数の意味を説明します。
- SUBTOTALは、第一引数は「集計方法を指定」し、第二引数で「範囲を指定」
- 第一引数「9」は「合計を求める」 を意味する
⇒SUBTOTAL(9,C3:C7) は SUM(C3:C7) と同じ計算となる。
ただし、SUMとの違いが１つあります。それは、
SUBTOTALは「オートフィルターで隠れたセルは計算しない」
ここが今回のポイントでした。
ということで、オートフィルターを使う場合はSUBTOTALをご活用ください。
第一引数「9」と「109」の違い
違いは以下のとおりです。
- 「9」…フィルター抽出後、表示されているセルのみを合計する
- 「109」…「非表示」にした行は除外して合計する
（「9」と同様、「フィルター抽出後、表示されているセルのみを合計する」としても使用可能）
ややこしいので、実例でみてみます。
フィルターで項目を抽出する場合
抽出前の表はこちら。
全ての項目を表示しているので、計算結果は当然「9」でも「109」でも同じ。
では、次にフィルターで交通費以外を抽出してみます。
結果は次のとおり。
この場合も、計算結果は同じ。
⇒フィルターを使い、抽出後に表示されているセルを合計する場合は「9」・「109」どちらでも大丈夫。
非表示にする場合（フィルターは使用しない）
では、フィルターは一切使用せず、４行目（＝蛍光ペン100円）を「非表示」にしてみます。
右クリック「非表示」でも同じ結果ですが、今回は見やすくするために
アウトライン⇒「グループ化」で非表示を行っています。
すると、計算結果が異なりました。正しいのは、「109」の方です。
⇒「非表示」を使い、表示されたセルだけを合計したい場合は「109」を使うのが正しい。
これが 「109」…「非表示」にした行は除外して合計する の意味です。
【参考】
フィルターで一部の項目を抽出し、その後、表示されている行を一部非表示にする場合は「9」でも「109」でも結果は同じになります。
もう一度、次の例をご覧ください。
フィルターで「コピー用紙」「蛍光ペン」「封筒」を抽出した例です。
仮に、この状態で４行目を非表示にすると、どうなるでしょうか？
「109」だけが正しい計算になりそうな気もしますが…
実は、答えは同じになります。
⇒フィルターで抽出し、表示されたセルを合計する場合は「9」・「109」どちらでも大丈夫。
SUBTOTALの他の使い道（小計）
SUBTOTAL関数は小計を計算するのに便利です。
というか、英単語の意味からすれば、こちらが本来の使い方かな…
次の画像の右の表をご覧ください。
SUBTOTAL関数は「範囲内のSUBTOTAL関数セルを計算しない」
その特徴を生かせば、小計の計算は楽勝です。
（なお、左の表のSUM関数は計算が誤っています。）
ここでも「9」と「109」の違いをチェック
まずは、非表示にする前の状態です。
当然、計算結果は同じです。
では、9行目（値「5」）を非表示にしてみます。
「109」の方は正しい計算結果50に変わりました。「9」の方は55のまま。
先述のとおりですが、非表示セルを計算したくない場合は「109」をご活用ください。
さいごに
仕事などでオートフィルターで項目を抽出するシーンは多々あると思います。
その際、SUBTOTAL関数を使えば、抽出後のデータに対して適切に計算ができます。
なお、「SUBTOTAL (9,指定範囲)」 や「SUBTOTAL(109,指定範囲)」は一字一句完璧に覚える必要はありません。
Excelには予測変換機能があります！
「＝SU」まで入力すれば予測変換で「＝SUBTOTAL」が候補に出てきます。
そして、「＝SUBTOTAL」を選択すれば
第一引数である「集計方法」の候補も出てきます。
どういうシーンでSUBTOTALを使えば便利かを何となく知っておけば、実務で使えると思います。
といったところで今回はこのあたりで。
最後までご覧いただきありがとうございました。
この記事が気に入ったら
フォローしてね！