【EXCEL】フィルターで絞り込んだところだけ計算してよ!

スポンサーリンク

フィルターで絞り込んだところだけ計算してよ!

EXCELの一覧表をフィルターで絞込んで簡単に合計を出したりしたい時ないですか?

例えばですがこんな表で、営業担当毎に結局合計いくらなんだ?みたいな。ピボット組むまでもないし、チロッとみたいだけなんよね~って感じの時です。

まずは、ショートカットキーでスマートにフィルターをかけます^_^

Ctrl + Shift + L → フィルターかける(表の中でやる)

んで、必要なところだけ絞り込んで見ますか。

月 → 3月

営業担当 → 吉田

でいってみましょう。

こうなりますね。

合計したいんで、「SUM関数」でも使いましょうか。

「57,000円」出ましたね。ん?????ちがうやん!(T_T)なんやねんこれ。ってなりますね。

※これは、3月の営業担当全員の合計が出ちゃってます。

じゃあそのまま他の営業担当に変えてみましょう。営業担当のフィルターを金谷さんに変えてみましょう。

合計がどっか行った~!!みたいな感じで上手くいかないんですね。

SUBTOTAL関数

そんな時に大活躍するのが、「SUBTOTAL関数」です。この関数を使うことで、全てが上手くいきます。安心して下さい。

まずは関数の説明から。

SUBTOTAL(集計方法,範囲 1,[範囲 2],...)

次の表にある、集計方法の番号を指定することで、引数に指定した範囲の中を計算してくれる関数です。範囲はコンマで区切って複数指定出来るんですが、どこで使うんだろ?って感じです。とりあえずコレを使って、さっきの問題を解決していきましょう!

集計方法 集計機能 同等の関数
1または101 平均値を求める AVERAGE
2または102 数値の個数を求める COUNT
3または103 データの個数を求める COUNTA
4または104 最大値を求める MAX
5または105 最小値を求める MIN
6または106 積を求める PRODUCT
7または107 不偏標準偏差を求める STDEV.S
8または108 標本標準偏差を求める STDEV.P
9または109 合計値を求める SUM
10または110 不偏分散を求める VAR.S
11または111 標本分散を求める VAR.P

まず、最初にフィルターで絞り込む前に「SUBTOTAL関数」で合計を出しておきます。

売上額の一番最後のセルの下のセルに「=SUBTOTAL(9,E3:E35)」と入力します。

※「E3:E35」は範囲です。データの入っている一番上から下までを指定しています。

なんと簡単!あとはフィルターで好きに絞り込むだけ!

絞り込んだら、自動的に見えてるところだけの数字を合計してくれます!

集計方法の引数を変更するだけで、簡単に集計ができちゃいます!

ちなみに。

1~11と101~111の違いは、手動での非表示を入れるか入れないかとなっています。フィルターで消えた分は両方とも除外されるので今回の使い方ではどちらでも良いですね。

念の為、手動非表示では合計が変わるか変わらないかやっときましょう。

注意点は、フィルターかけた後、追加で非表示にすると、どちらでも除外されちゃうことです。

まずは、集計方法「9」でフィルターかかってるところに、1行非表示にするバージョン

除外されました。

次は、同じものを集計方法「109」でやってみます。

同じく除外されましたね。

それでは、集計方法「9」で、フィルターかけずに手動で非表示にしてみます。

除外されませんでした。

では、集計方法「109」で、フィルターかけずに手動で非表示にしてみます。

集計方法を「109」だと、手動での非表示が除外されることが分かりましたね。

まとめ

このように、「SUBTOTAL関数」を覚えておくと、ちょっとした確認などに使えます。少し挙動に癖があるので慣れが必要ですね^_^

失敗のところで出した、フィルターとSUM関数は、集計の間違いによくあるやつなので、注意して下さい!