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

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

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

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

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

2/13まで
Amazon「JCB 5,000円利用→ 1,000P」
【上限:3,000P】

公式ページより
  • 要エントリー
  • JCBカード5,000円の利用で1,000P
    (Amazonギフト購入等は対象外)
    (付与上限:3,000P)
    • 5,000円→1,000P
    • 10,000円→2,000P
    • 15,000円→3,000P

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

2/28まで
ファミペイ バーチャルカード
「3,000円利用→500円相当獲得

公式ページより
  • 3,000円の利用で500円相当のファミペイボーナス
  • ネットでの決済が対象
    AmazonギフトOK
    Google PayやQUICPay+による実店舗での支払は対象外)

\まずは詳細を確認/

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

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

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

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

\まずは詳細を確認/

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

2/15まで
prime video対象のキッズCH「最初の2か月:月額10円」

公式ページより
  • 最初の2か月:月額99円
    「自動更新しない」設定にすると「1か月99円で利用OK」
    2か月目に入り「自動更新しない」設定にすると「2か月198円で利用OK」
「自動更新しない」設定

\対象かどうか確認/

2/28まで
Amazon Audible「2か月無料」

公式ページより
  • Audible・2か月無料
    (通常3,000円
  • 即退会だと、無料期間は初めの1か月分だけ
  • 1か月経過後に退会」で無料期間は2か月分
    (Amazon Music UnlimitedやKindle Unlimitedとは対応が異なるので要注意

\対象かどうか確認/

【終了日不明】
kindle unlimited「2か月99円」

公式ページより
  • Kindle Unlimited・2か月99円
    (通常1,960円
  • 即「自動更新しない設定」でも2か月利用OK
    (設定はコチラ

\対象か確認(コスパ良好)/

2/28まで
Aamzon×P&G「5,000円以上で1,000円引」

  • 対象商品を5,000円分以上購入すると即1,000円OFF
    (注文確定時に自動適用)
  • 期間中何度でもOK
パパ

キャンペーンページが消えたり、また出現したり…
(2023年2月8日現在では復活)

\対象商品をチェック/

【2/19まで】
「テレボート」地域特産品が1万名に当たる

公式ページより
  • 2/1~2/19
  • 地域特産品が1万名に当たる
  • テレボート「送付物を希望する」と登録
  • 期間中にキャンペーンサイトから応募
  • 期間中合計5,000円以上の購入が必要
    • 必ずしも5,000円の入金が必要とは限らない
      (的中すれば再投票できるので)
  • 「お友だち紹介キャンペーン」と併用OK
    • 招介コード:0246040053

詳細記事はコチラ

\「1万人」ってすごい数/

3/31まで
「テレボート」お友だち紹介CP

公式ページより
  • 1/1~3/31(現在第3クール)
  • かなりの期待値
    • 1等:20,000円(300名)
    • 2等:10,000円(900名)
    • 3等:5,000円(1,800名)
  • 被紹介者は1,000円以上の購入が必要
    • 初心者の方は「1号艇・複勝1.0倍」への投票がオススメ

招介コード:0246040053

\3か月で総額2,400万円の大盤振舞い/

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

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