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

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

  • URLをコピーしました!

<景品表示法に基づく表記>本サイトの情報には、商品・サービスPRを含む場合があります。

困り人
困り人

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

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

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

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

12/1まで
Amazonギフト「チャージタイプ 0.5%還元」

公式ページより
  • 要エントリー
  • 期間中 合計5,000円以上 のチャージが対象
    (クレジットカードOK
  • 還元上限10,000P
ママ

いろいろとコンボできそう

\今、チャージがお得。忘れずエントリー/

【10%還元】1/3まで
Amazonギフト「Eメール・配送タイプ」
5,000円購入で500P獲得

  • 要エントリー
  • 1回5,000円以上の購入→500P付与
    (1人1回まで)
    (クレジットカードOK
  • Eメールタイプ・配送タイプが対象
    チャージタイプは対象外

「配送タイプ:5,000円」なら、プライムスタンプラリー(11/17~12/15)の条件「プライム配送特典の対象商品を1回2,000円以上注文」も同時にクリア

\私は、配送タイプ!忘れずエントリー/

参考記事

12/1まで
kindle unlimited「3か月 99円」

公式ページより
パパ

私も現在「3か月99円」加入中。良いです

\コスパ抜群(スキマ時間の有効活用にも)/

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

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

\対象かどうか確認/

【12/31まで】Amazon×P&G
「5,000円以上購入で即1,000円OFF」

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

\対象商品をチェック/

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

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