最新版「高還元チャージルート」をチェック

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

  • URLをコピーしました!

\細かいポイ活も良いけど、まずは固定費から/

困り人
困り人

Excelでオートフィルターはよく使う。

抽出後データの合計をSUM関数で求めると、なぜか計算が合わない…

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

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

オートフィルター使用時に合計を求める場合は

「SUBTOTAL(9,指定範囲)」

「SUBTOTAL(109,指定範囲)」

を使いましょう。

パパ
パパ

なお、オートフィルターでSUBTOTAL関数を使う場合、第一引数は「9」でも「109」でも大勢に影響がないことがほとんどです。

でも本記事では具体例を用い、違いにもふれます。

「SUM(指定範囲)」は

オートフィルターや非表示で隠れたセルも計算します

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

目次

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

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

Excelの予測変換

この予測変換からもわかるように

「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との違いが1つあります。それは、

SUBTOTALは「オートフィルターで隠れたセルは計算しない」

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

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

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

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

  • 「9」…フィルター抽出後、表示されているセルのみを合計する
  • 「109」…「非表示」にした行は除外して合計する

(「9」と同様、「フィルター抽出後、表示されているセルのみを合計する」としても使用可能)

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

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

抽出前の表はこちら。

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

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

結果は次のとおり。

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

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

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

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

パパ
パパ

右クリック「非表示」でも同じ結果ですが、今回は見やすくするために

アウトライン⇒「グループ化」で非表示を行っています。

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

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

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

【参考】

フィルターで一部の項目を抽出し、その後、表示されている行を一部非表示にする場合は「9」でも「109」でも結果は同じになります。

もう一度、次の例をご覧ください。

フィルターで「コピー用紙」「蛍光ペン」「封筒」を抽出した例です。

仮に、この状態で4行目を非表示にすると、どうなるでしょうか?

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を使えば便利かを何となく知っておけば、実務で使えると思います。

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

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

今がアツい「キャンペーン情報」

【4月・5月限定】モッピー独占 3,000円 5,000円!!
大和コネクト証券(旧CONNECT)
(公式CP4,000円も行ける

過去最高&独占

  1. モッピー
    大和コネクト証券 新規口座開設【3,000円】【5,000円】

5/27(04:20)現在

まさかの更なる増量!
モッピー5,000PにUP!
(トータル9,000Pが楽勝)
※本記事の「3,000Pは5,000Pに読替」してください

  1. 公式側CP
    お友達招待プログラム【500円】
    • 招待コード:DDTFQGAC
  1. 公式側CP
    dアカウントまたはPonta IDの連携【1,000円】
    • ひな株(単元未満株)を選択。「後日売却」ですぐに現金化
    • もちろん、保有し続けてもOK

ブログ記事はコチラ/

【6/16まで】Amazon×P&G
「対象商品5,000円以上購入で即1,000円OFF」

  • 対象商品を5,000円分以上購入すると即1,000円OFF
    (注文確定時に自動適用)
  • クーポンがある場合は、ショッピングカートにて自分で適用する必要あり
    (その場合、クーポン適用前の価格が対象金額となる)
  • 期間中何度でもOK
  • 対象商品は不定期に変更される場合あり
    (キャンペーンページで要確認)

\対象商品をチェック/

【6/30まで】総額1,000万円
au PAYカード「抽選で1,000名に10,000P」

公式ページより
  • 要エントリー
  • 5,000円以上利用で条件達成
    (携帯料金・クレカ積立など対象外あり)
    AmazonギフトOK

\とりあえずエントリー/

条件「5,000円利用」と相性抜群

こちらもアリ

参考記事 1(コンボ可能

参考記事 2(コンボ可能

7/31まで
あおぞら銀行「デビット6%還元

公式ページより
  • 月2万円の利用まで6%還元
    AmazonギフトOKPayPay等スマホ決済OK

月2万円の利用で1,200円還元
6か月フルで参戦すれば、12万円の利用7,200円のキャッシュバック

\まずは詳細を確認/

私はコレで参戦「Amazonギフト」

参考記事(たぶんコンボ可能

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
目次表示
最初へ
目次
閉じる