ポイ活 お道具箱

Amazon 便利まとめ

5/9まで
Audible「2か月無料」

公式ページより
  • Audible(オーディブル)・2か月無料
  • 限りある時間の有効活用に
パパ

過去に無料トライアルを利用した私も対象でした

\3,000円相当がタダ!/

スマイルSALE GW:4/19(金)9:00~4/22(月)23:59/

ご自由にご活用ください!(私も嬉しい)

スクロールできます

ポイントサイト

銀行・証券・クレカ・プリカ

  • 第一生命NEOBANK
    ↓招待コード
    oFv6PT9
  • みんなの銀行
    ↓招待コード
    QJBqSpey
  • 大和コネクト証券
    ↓招待コード
    DDTFQGAC
  • エポスカード
    ↓ご紹介番号
    23071456281
  • IDARE
    ↓招待コード
    qyj51t
  • B/43
    ↓招待コード
    9K804P
  • Olive
    ↓招待コード
    SF00149-0048801
  • 楽天銀行
    ↓招待コード
    P30004498
  • 楽天銀行 第一生命支店
    ↓招待コード
    D25933370

競輪

その他

  • Amazonプライム会員
    招待リンク
  • カウシェ
    ↓招待コード
    IINBXQ
  • mineo(招待リンク
  • 楽天Car車検
    ↓招待コード(2026年3月12日まで有効)
    BM79LOW9
  • 楽天フリマ
    ↓招待コード
    EBvaU
【5/31まで】楽天モバイル/再契約もOK:大規模バラマキ中14,000P

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

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

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

今アツいキャンペーン一覧をチェック

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

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