【Excel関数】AGGREGATE関数~非表示の行やエラー値を無視して集計

テーブルやデータベースを集計する際、非表示の行やエラー値を無視して集計する関数です。集計には19種類の関数を使用することができます。SUBTOTAL関数との違いについても解説しています。

具体的な使い道は?
  • テーブルに対し、フィルタで抽出したデータを集計する(=非表示行は集計しない)
  • エラー値が含まれた表で、エラー値を無視してデータを集計する、等
目次

構文

集計で使用する関数によって構文が違います。関数の引数が範囲のみの場合はセル範囲形式、範囲以外に引数が必要な関数は配列形式の構文を使用します。

セル範囲形式

=AGGREGATE(引数1:集計方法, 引数2:オプション, 引数3:参照 1, [引数4:参照 2], …)

引数1:集計方法

省略不可。使用する関数を番号で指定します。

  • 1…AVERAGE
  • 2…COUNT
  • 3…COUNTA
  • 4…MAX
  • 5…MIN
  • 6…PRODUCT
  • 7…STDEV.S
  • 8…STDEV.P
  • 9…SUM(この後サンプルあり)
  • 10…VAR.S
  • 11…VAR.P
  • 12…MEDIAN
  • 13…MODE.SNGL
引数2:オプション

省略可。引数3で指定した範囲内で無視する値を番号で指定します。

  1. 0または省略…ネストされたSUBTOTAL関数とAGGREGATE関数を無視します。
  2. 1…非表示の行、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します。
  3. 2…エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します。
  4. 3…非表示の行、エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します。
  5. 4…何も無視しません。
  6. 5…非表示の行を無視します(この後サンプルあり)。
  7. 6…エラー値を無視します。
  8. 7…非表示の行とエラー値を無視します(この後サンプルあり)。
引数3:参照1

省略不可。

引数4:参照2

省略可。最大253個まで指定できます。

配列形式

=AGGREGATE(引数1:集計方法, 引数2:オプション, 引数3:配列, 引数4:)

引数1:集計方法

省略不可。使用する関数を番号で指定します。

  • 14…LARGE(引数4:順位 この後サンプルあり)
  • 15…SMALL(引数4:順位
  • 16…PERCENTILE.INC(引数4:
  • 17…QUARTILE.INC(引数4:戻り値
  • 18…PERCENTILE.EXC(引数4:
  • 19…QUARTILE.EXC(引数4:戻り値
引数2:オプション

省略可。引数3で指定した範囲内で無視する値を番号で指定します(セル範囲形式と同じ)。

引数3:配列

省略不可。

引数4:

省略不可。各関数に必要な引数を指定します。

集計で使用する関数によって構文は若干変わりますが、関数による引数の違いがあるだけで構文の基本は変わりません。

  • 集計に使用する関数を指定する
  • 集計の条件(何を無視するか)を指定する
  • 集計の範囲を指定する
  • 関数によっては追加の引数を指定する

AGGREGATE関数でフィルタ抽出した行を集計する

テーブル内の年棒を合計するサンプルです。

=AGGREGATE(9,5,N2:N76)集計方法,オプション,参照1

集計方法はSUM関数、オプションは非表示行を無視、参照1年棒列(N列)です。

フィルタで抽出した値で年棒の合計が算出されます(非表示行は集計されない)。

AGGREGATE関数でエラー値を無視して集計する

年棒を背番号で割り、LARGE関数で一位の値を抽出するサンプルです(意味は特にありません)。2行目と3行目は背番号により#DIV/0!が返されますが、条件で非表示の行とエラー値を無視しているので値が返ります。

=AGGREGATE(14,7,O2:O76,1)集計方法,オプション,配列,順位

集計方法はLARGE関数、オプションは非表示の行とエラー値を無視する、配列年棒/背番号列(O列)、順位1です。

もちろんですが、この計算をLARGE関数のみで行うと#DIV/0!が返ります。

SUM関数であれば、=SUMIF(範囲,”<>#N/A”,合計範囲)でエラー値を除いて集計することは可能です。

エラーについてまとめた記事もあります。気になる点がある人はこちらの記事からお願いします。

SUBTOTAL関数との違い

AGGREGATE関数はExcel2010で実装された関数です。それまではSUBTOTAL関数を使用していました。違いは4つです。

  • AGGREGATE関数はエラー値を無視できる
  • SUBTOTAL関数はフィルタによる非表示行しか無視できない(AGGREGATE関数は右クリックメニューの非表示も無視する)
  • 集計方法(使用できる関数)がSUBTOTAL関数は11種類、AGGREGATE関数は19種類
  • 引数の指定方法が若干違う

後発のAGGREGATE関数は当然、SUBTOTAL関数の上位互換です。Excel2010以降のバージョンを使用しているのであれば、SUBTOTAL関数を使う必要はありません

まとめ

  • エラー値を無視したいときや非表示行を集計したくないときはAGGREGATE関数を使用する。
  • 集計方法(使用する関数)によって構文が変わってくるので注意する。
  • SUBTOTAL関数は旧バージョンの関数なので積極的に使用する必要はない。

集計方法やオプションは入力補助でリストが出るので覚える必要はありません。構文間違いさえ気を付ければ大丈夫です。

よかったらブックマークとシェアをお願いします。最後までご高覧いただきありがとうございました。

よかったらシェアしてください
URLをコピーする
URLをコピーしました!
目次へ
トップへ
目次
閉じる