まとめへの近道
押すと、トップの該当箇所まで飛びます

富山県在住の30代、2児の父。
元地方公務員。新たなチャレンジのため、2024年4月に独立。
もともと趣味だったポイ活が高じて、このブログとXでお得情報を発信しています。
AIで文章や図解が誰でもすぐに作れる今だからこそ、自分の物差しで「これは良い」と思えたものだけを、公式の条件まで確かめて、丁寧にお届けしています。
お仕事のご相談は、XのDMからお気軽にどうぞ。
【ホームページ制作について】
このブログも、自分でつくって運営しています。
いつも読んでくださっている方で、お店や教室、事業をされている方がいらっしゃれば、ホームページのご相談もお受けしています。
夫婦でやっている別の事業「ツタエル富山」で、お店のホームページづくりは私が担当しています。全国どこからでもオンラインで進められます。
※ ご案内は、ツタエル富山のページが開きます
ご相談は、InstagramのDMへどうぞ
【スキル・特技・実績】

当ページは広告を含みます。

Excelでオートフィルターはよく使う。
抽出後データの合計をSUM関数で求めると、なぜか計算が合わない…
このような悩みはありませんか?
僕も以前、悩みました。でも大丈夫です。
オートフィルター使用時に合計を求める場合は
「SUBTOTAL(9,指定範囲)」か
「SUBTOTAL(109,指定範囲)」
を使いましょう。

なお、オートフィルターでSUBTOTAL関数を使う場合、第一引数は「9」でも「109」でも大勢に影響がないことがほとんどです。
でも本記事では具体例を用い、違いにもふれます。
「SUM(指定範囲)」は
オートフィルターや非表示で隠れたセルも計算します。
ポイントは上記2点ですが、本記事では簡単な例を用い解説します。
まずはじめに「SUBTOTAL(9,指定範囲)」 について、ざっくり次のようなイメージだけお持ちください。

この予測変換からもわかるように
「SUBTOTAL(9,指定範囲)」は
⇒SUMと「ほぼ」同じ意味を持つ
それでは、簡単な表で説明していきます。

この例では、C3からC7までの合計をSUBTOTAL関数とSUM関数のそれぞれで計算します。
計算結果はいずれも33,000。

結果が同じなんだから、SUMでも良さそうな気もします。
でも、オートフィルターでデータを抽出する可能性がある場合は、SUMではダメ。
次の例をご覧ください。

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

計算結果が変わりました。
正しいのはSUBTOTALで計算した1,000。
ここでSUBTOTAL関数の意味を説明します。
⇒SUBTOTAL(9,C3:C7) は SUM(C3:C7) と同じ計算となる。
ただし、SUMとの違いが1つあります。それは、
SUBTOTALは「オートフィルターで隠れたセルは計算しない」
ここが今回のポイントでした。
ということで、オートフィルターを使う場合はSUBTOTALをご活用ください。
違いは以下のとおりです。
(「9」と同様、「フィルター抽出後、表示されているセルのみを合計する」としても使用可能)
ややこしいので、実例でみてみます。
抽出前の表はこちら。

全ての項目を表示しているので、計算結果は当然「9」でも「109」でも同じ。
では、次にフィルターで交通費以外を抽出してみます。

結果は次のとおり。

この場合も、計算結果は同じ。
⇒フィルターを使い、抽出後に表示されているセルを合計する場合は「9」・「109」どちらでも大丈夫。
では、フィルターは一切使用せず、4行目(=蛍光ペン100円)を「非表示」にしてみます。

右クリック「非表示」でも同じ結果ですが、今回は見やすくするために
アウトライン⇒「グループ化」で非表示を行っています。

すると、計算結果が異なりました。正しいのは、「109」の方です。
⇒「非表示」を使い、表示されたセルだけを合計したい場合は「109」を使うのが正しい。
これが 「109」…「非表示」にした行は除外して合計する の意味です。
【参考】
フィルターで一部の項目を抽出し、その後、表示されている行を一部非表示にする場合は「9」でも「109」でも結果は同じになります。
もう一度、次の例をご覧ください。
フィルターで「コピー用紙」「蛍光ペン」「封筒」を抽出した例です。

仮に、この状態で4行目を非表示にすると、どうなるでしょうか?
「109」だけが正しい計算になりそうな気もしますが…

実は、答えは同じになります。
⇒フィルターで抽出し、表示されたセルを合計する場合は「9」・「109」どちらでも大丈夫。
SUBTOTAL関数は小計を計算するのに便利です。

というか、英単語の意味からすれば、こちらが本来の使い方かな…
次の画像の右の表をご覧ください。

SUBTOTAL関数は「範囲内のSUBTOTAL関数セルを計算しない」
その特徴を生かせば、小計の計算は楽勝です。
(なお、左の表のSUM関数は計算が誤っています。)
まずは、非表示にする前の状態です。

当然、計算結果は同じです。
では、9行目(値「5」)を非表示にしてみます。

「109」の方は正しい計算結果50に変わりました。「9」の方は55のまま。
先述のとおりですが、非表示セルを計算したくない場合は「109」をご活用ください。
仕事などでオートフィルターで項目を抽出するシーンは多々あると思います。
その際、SUBTOTAL関数を使えば、抽出後のデータに対して適切に計算ができます。
なお、「SUBTOTAL (9,指定範囲)」 や「SUBTOTAL(109,指定範囲)」は一字一句完璧に覚える必要はありません。

Excelには予測変換機能があります!
「=SU」まで入力すれば予測変換で「=SUBTOTAL」が候補に出てきます。

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

第一引数である「集計方法」の候補も出てきます。
どういうシーンでSUBTOTALを使えば便利かを何となく知っておけば、実務で使えると思います。
といったところで今回はこのあたりで。
最後までご覧いただきありがとうございました。
この記事が気に入ったら
フォローしてね!