オートフィルターで絞り込んだデータのみ合計計算(SUM)する│Excel関数

Excelでデータ集計するときに多用するオートフィルター機能。

条件に合致したデータのみを絞り込むオートフィルター機能は、データを分析したり加工するのに欠かせない機能です。そして、オートフィルターと相性の良い関数というのも存在します。

たとえばオートフィルターで絞り込んだ件数のみを「カウント」する関数や、「合計計算」をする関数などです。

実は、ExcelのSUBTOTAL関数を使えば、それらを簡単に実現することができます。

オートフィルター結果のみを合計計算

今回の記事では、オートフィルターで絞り込んだデータのみ合計計算(SUM)する関数の使い方を紹介します。

目次

オートフィルターで絞り込んだデータのみを合計計算(SUM)

Excelのオートフィルターで絞り込んだデータのみを合計計算する場合、SUM関数は使いません。詳しくは後述しますが、SUM関数を使ってしまうとオートフィルターを無視して、カッコ内で記述したセル範囲の合計を計算してしまうのです。

オートフィルターで隠れたデータも計算してしまう・・

SUBTOTAL関数の概要

機能的にはSUMと同じく合計計算という動きをするのですが、SUBTOTALという関数を使えば、オートフィルターで絞り込んだデータのみを合計計算することができます。

さらにSUBTOTAL関数は万能型の関数で、引数を設定することでオートフィルター後の「合計の計算」、「データ個数のカウント」、「平均値」、「最大値」など、用途に応じた結果を算出することができます。

引数とは・・Excelの関数で使うカッコの中の記述文のことです。小数点を切り上げるROUNDUP関数を例にすると=ROUNDUP(A1,0)で、セルA1の値を小数点第一位で切り上げしますが、カッコ内の(範囲,桁数)がそれぞれ引数です。※この例では引数は2つ

SUBTOTAL関数

基本:=SUBTOTAL(集計方法,セル範囲)
合計:=SUBTOTAL(9,セル範囲)
個数:=SUBTOTAL(3,セル範囲)

引数として設定する[集計方法]は下記の11通りです。

集計方法引数
AVERAGE1
COUNT2
COUNTA3
MAX4
MIN5
PRODUCT6
STDEV7
STDEVP8
SUM9
VAR10
VARP11
SUBTOTAL関数の第一引数

当記事で実例紹介するのは、「3」と「9」です。

SUBTOTAL関数の実例

下記のイメージ図がSUBTOTALの実例です。1行目から8行目にかけてオートフィルターで絞り込んだデータのみが表示されています。

13・14行目では、COUNTA関数を使ってデータ個数を、SUM関数を使って合計値を計算していますが、オートフィルターで隠れているデータも計算の対象になっていることが分かります。

一方、16・17行目では、どちらもSUBTOTAL関数を使ってデータ個数や合計値を計算しています。計算結果は、オートフィルターで絞り込んだデータのみが対象になっていることが分かります。

なお、前述の通り、データ個数の場合は「3」、合計の場合は「9」の引数を設定します。

=SUBTOTAL(3,B2:B11)
=SUBTOTAL(9,B2:B11)
よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!
目次
閉じる