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

アイキャッチ

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

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

AGGREGATE関数の使い方

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

セル範囲形式

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

省略不可。

引数4:参照2

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

配列形式

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

引数1:集計方法

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

  1. LARGE(引数4:順位 この後サンプルあり
  2. …SMALL(引数4:順位
  3. …PERCENTILE.INC(引数4:
  4. …QUARTILE.INC(引数4:戻り値
  5. …PERCENTILE.EXC(引数4:
  6. …QUARTILE.EXC(引数4:戻り値
引数2:オプション

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

引数3:配列

省略不可。

引数4:

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

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

  • 引数1…集計に使用する関数を指定する
  • 引数2…集計の条件(何を無視するか)を指定する
  • 引数3…集計の範囲を指定する
  • 引数4…関数によっては追加の引数を指定する(配列形式のみ)

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

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

AGGREGATE関数用のサンプルシート画像。テーブルが用意されている。

=AGGREGATE(9,5,N2:N76)

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

AGGREGATE関数用のサンプルシート画像。C列が「外野手」で抽出されている。
  • フィルタで抽出した値で年棒の合計が算出されます(非表示行は集計されない)。

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

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

AGGREGATE関数用のサンプルシート画像。テーブルデータにエラー値が含まれている。

=AGGREGATE(14,7,O2:O76,1)

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

  • O2O3のエラー値が無視され、範囲内の最大値である75,000,000を返します。
  • もちろんですが、この計算を普通にLARGE関数 のみで行うと#DIV/0!が返ります。
  • 加算であれば、=SUMIF(範囲,“<>#DIV/0”,合計範囲)でもエラー値を除いて集計できます。

SUBTOTAL関数との違い

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

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

後発のAGGREGATE関数 は当然、SUBTOTAL関数 の上位互換です。

Excel2010以降のバージョンを使用しているのであれば、SUBTOTAL関数 を使う必要はありません。

非表示の行やエラー値を無視して集計するときはAGGREGATE関数を使おう

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

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

エクセルの独学は動画学習がおすすめです。書籍を読み解くより圧倒的に楽です。

趣味でブログを開設したい人向けに手順を紹介しています。

よかったらシェアしてね!
  • URLをコピーしました!
目次