フィルタを掛けた結果のみを計算する

数式 / 関数 , ,

40-1.png
Excelにはフィルタ機能というのがあります。指定したデータのみを抽出し表示する機能なんですが、その抽出されなかったデータというのは非表示扱いで隠れてしまいます。
が、非表示されるだけでデータ自体は存在しますので、たとえば合計を出すSUM関数を使うと、非表示部分も合計されてしまいます。
フィルタに掛かった結果のみ、表示されているデータのみを合計したい場合はどうしたら良いんでしょうか。

フィルタリングする

40-2.png
まずは上のデータをフィルタリングしてみます。消耗品費・修繕費のみを抽出したいと思います。

①A3からC10までを選択します ②フィルタボタンを押します ③科目フィルタの消耗・修繕以外チェックを外します。
40-3.png 40-4.png 40-5.png

するとこのような結果になります。
しかしです!金額欄を見てください。6,500+120,000+800の合計は127,300にならないといけません。
40-6.png
これがSUM関数。隠れている厚生費、交通費、旅費も合計してしまうんです。

ではどうすればいいの?

ここで使うのがSUBTOTAL関数です。
あまり聞き慣れない関数ですが、合計(SUM)だけでなく、COUNTやAVERAGE、最大値(MAX)、最小値(MIN)もこの関数一つで扱えます。
今回のパターンだと以下の式になります。
40-7.png
SUBTOTAL関数の引数は2つ。
=SUBTOTAL( 集計方法 , 範囲 ) です。
この集計方法というのは予め決められた番号があり、番号毎に集計方法が決まっています。SUMだと9です。下記の表を参考にしてください。

番号 集計方法
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

他の例を出すと、
=COUNT(C4:C10) の結果は 7 ですが
=SUBTOTAL(2,C4:C10) の結果だと 3 になります。

Comment

  1. seiko より:

    フィルターをかけた区間で、表示された数字の数をカウントするには、どうすればよろしいでしょうか?
    例えば、1から100の区間で、2の数字が何回表示か、1から50の区間では、2の数字が何回表示されてるか?
    のような場合の、集計のやり方がわかりません。
    よろしくお願いいたします。

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

バーコード ユーザー定義関数 VLOOKUP 変換 テンプレート グラフ フロー図 作業効率UP DATE TODAY 印刷 置換 日付 コピペ SUMIF ワイルドカード 検索 SUM 偏差値 HLOOKUP NA() セル選択 図形 COLUMN 無料 条件付き書式 XLSTART 目標 COUNTIF STDEV
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑