【Excel／図解】SUBTOTAL関数でフィルター抽出後、表示セルのみを合計。SUMとの違いや「9」「109」の違いも解説

このような悩みはありませんか？

僕も以前、悩みました。でも大丈夫です。

ポイントは上記２点ですが、本記事では簡単な例を用い解説します。

オートフィルター使うなら「SUBTOTAL」

まずはじめに「SUBTOTAL(9,指定範囲)」 について、ざっくり次のようなイメージだけお持ちください。

それでは、簡単な表で説明していきます。

この例では、C3からC7までの合計をSUBTOTAL関数とSUM関数のそれぞれで計算します。

計算結果はいずれも33,000。

オートフィルターで交通費以外の項目だけを抽出してみます。

計算結果が変わりました。

正しいのはSUBTOTALで計算した1,000。

ここでSUBTOTAL関数の意味を説明します。

ここが今回のポイントでした。

ということで、オートフィルターを使う場合はSUBTOTALをご活用ください。

第一引数「9」と「109」の違い

違いは以下のとおりです。

ややこしいので、実例でみてみます。

フィルターで項目を抽出する場合

抽出前の表はこちら。

全ての項目を表示しているので、計算結果は当然「9」でも「109」でも同じ。

では、次にフィルターで交通費以外を抽出してみます。

結果は次のとおり。

この場合も、計算結果は同じ。

⇒フィルターを使い、抽出後に表示されているセルを合計する場合は「9」・「109」どちらでも大丈夫。

非表示にする場合（フィルターは使用しない）

では、フィルターは一切使用せず、４行目（＝蛍光ペン100円）を「非表示」にしてみます。

すると、計算結果が異なりました。正しいのは、「109」の方です。

⇒「非表示」を使い、表示されたセルだけを合計したい場合は「109」を使うのが正しい。

これが 「109」…「非表示」にした行は除外して合計する の意味です。

SUBTOTALの他の使い道（小計）

SUBTOTAL関数は小計を計算するのに便利です。

次の画像の右の表をご覧ください。

その特徴を生かせば、小計の計算は楽勝です。

（なお、左の表のSUM関数は計算が誤っています。）

ここでも「9」と「109」の違いをチェック

まずは、非表示にする前の状態です。

当然、計算結果は同じです。

では、9行目（値「5」）を非表示にしてみます。

「109」の方は正しい計算結果50に変わりました。「9」の方は55のまま。

先述のとおりですが、非表示セルを計算したくない場合は「109」をご活用ください。

さいごに

仕事などでオートフィルターで項目を抽出するシーンは多々あると思います。

その際、SUBTOTAL関数を使えば、抽出後のデータに対して適切に計算ができます。

なお、「SUBTOTAL (9,指定範囲)」 や「SUBTOTAL(109,指定範囲)」は一字一句完璧に覚える必要はありません。

「＝SU」まで入力すれば予測変換で「＝SUBTOTAL」が候補に出てきます。

そして、「＝SUBTOTAL」を選択すれば

第一引数である「集計方法」の候補も出てきます。

どういうシーンでSUBTOTALを使えば便利かを何となく知っておけば、実務で使えると思います。

といったところで今回はこのあたりで。

最後までご覧いただきありがとうございました。