AGGREGATE関数の使い方|SUBTOTALを拡張した集計関数

AGGREGATE関数の使い方
Excel関数

Excel(エクセル)のAGGREGATE関数の使い方|SUBTOTALを拡張した集計関数

使用関数:AGGREGATE

AGGREGATE(アグリゲート)関数とは?

AGGREGATEは「集計・合計」を意味する英語です。Excel2010から装備された関数で、降順・昇順の順位や中央値を求めたり、エラー値のセルを除外した集計もできます。

AGGREGATE関数とSUBTOTAL関数の違い

簡単に言えば、SUBTOTALは進化前で、AGGREGATEは進化後です。AGGREGATE関数はSUBTOTALの集計方法を拡張し、より柔軟な指定を可能にしました。

両者とも「集計方法」を対応する関数ごとに決まった番号で指定しますが、AGGREGATEは対応する関数が8種類増えています。

また、SUBTOTALでは非表示にした行データを除外して集計するのに3桁の番号を指定する必要がありましたが、AGGREGATEでは集計で除外する条件を引数オプションに0~7までの数字で指定する仕様に変わりました。このオプションには非表示・エラー値・小計の除外に関する様々な選択肢が含まれます。

SUBTOTAL関数の詳しい使い方です。

SUBTOTAL関数の使い方|集計方法の指定で様々な種類の集計を実行
SUBTOTAL関数の使い方を初心者にも分かりやすく解説。SUBTOTALが実行できる集計(合計・平均・分散・個数・標準偏差・最大値と最小値など)について、集計方法に指定する番号9、109、1、101、11、111の使い分けなどについて。

AGGREGATE関数の引数と書式

「数式」タブ「関数ライブラリ」の「数学/三角」からAGGREGATEをクリックします。
「関数ライブラリ」→「数学/三角」→ AGGREGATEをクリック
AGGREGATEのダイアログを開く際に2タイプの書式を選択する画面が現れます。
AGGREGATEの書式の選択画面

最初の書式の引数は「集計方法」「オプション」「配列」「順位」で、これはAGGREGATE関数で新たに加わった集計方法の内、14~19を対象にした書式です。

2番目の書式の引数は「集計方法」「オプション」「参照1」が必須で、「参照2」以下は任意です。これは1~13までの集計方法を対象にした書式です。

書式の構成はこうなります。
AGGREGATE関数の書式の構成

第一引数「集計方法」の指定

最初の引数の「集計方法」には以下に掲載した計算する種類番号で指定します。12~19はAGGREGATE関数で新たに加わった集計方法です。

集計の種類番号リスト

番号集計方法対応する関数
▼ 引数「集計方法・オプション・参照1・参照2・・・」を選択
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
▼ 引数「集計方法・オプション・配列・順位」を選択
14降順の順位LARGE
15昇順の順位SMALL
16百分位数PERCENTILE.INC
17四分位数QUARTILE.INC
180%と100%を除く百分位数PERCENTILE.EXC
190%と100%を除く四分位数QUARTILE.EXC

第二引数「オプション」の指定で非表示の行やエラー値の除外

AGGREGATE関数の引数「オプション」には以下に掲載した種類番号で除外する条件を指定します。

AGGREGATEの計算から除外する条件の種類番号リスト

番号除外する条件
0または省略ネストされたSUBTOTAL関数とAGGREGATE関数を無視する
1オプション番号「0」の条件に加えて非表示の行を無視する
2オプション番号「0」の条件に加えてエラー値を無視する
3オプション番号「0」の条件に加えて非表示の行とエラー値を無視する
4何も無視しない
5非表示の行を無視する
6エラー値を無視する
7非表示の行とエラー値を無視する

第三引数「配列」または「参照1」の指定

どちらも集計するデータ範囲を指定します。

「集計方法」の1~13に指定する「参照1にはセル及びセル範囲を「参照253」まで指定できます。
「集計方法」の14~19に指定する「配列には連続するセル範囲を指定します。配列形式で直接値を入力することもできます。

第四引数「順位」の指定

「集計方法」で14~19を指定した場合、この引数「順位」は省略できません。降順・昇順における順位、百分位数の率、四分位数の戻り値(0~4までの数字)を数値で指定します。

例えば、降順で大きい方から3番目の値を求めたいなら「3」、百分位数で小さい方から10%の位置にある値を求めたいなら「0.1」と入力します。

AGGREGATE関数の使い方|集計方法1~13までの書式

集計方法「1」から「13」までは引数「集計方法・オプション・参照1・参照2・・・」の書式を選択して計算します。
この中で「最大値」と「最小値」を求める集計をしてみましょう。

会員名簿のデータ表です。会員資格をクリアしていない数名の行が非表示になっており、入力ミスによるエラー値もあります。この中から「最年長」と「最年少」の会員の年齢をAGGREGATE関数で取得してみます。
AGGREGATE関数で最大値と最小値を集計するデータ表

  • 集計方法」には最大値を求める「4」を指定します。
    AGGREGATE関数の集計方法に最大値を求める4を指定
  • オプション」には非表示の行とエラー値を無視する「7」を指定します。
    AGGREGATE関数のオプションに非表示の行とエラー値を無視する7を指定
  • 参照1」には「年齢」配下のデータ範囲を指定します。
    AGGREGATE関数の参照1に「年齢」配下のデータ範囲を指定

ダイアログでの指定は以下のようになります。
AGGREGATE関数の引数ダイアログでの指定

「最年長」の数式を「最年少」のセルにコピーして、「集計方法」の数値を最大値を求める「4」から最小値を求める「5」に修正します。
AGGREGATE関数の「集計方法」の数値を最大値を求める「4」から最小値を求める「5」に修正

「最年長」と「最年少」の年齢が取得できました。
AGGREGATE関数で最大値と最小値を計算し、「最年長」と「最年少」の年齢を取得

最年長の数式: =AGGREGATE(4,7,$D$2:$D$21)
最年少の数式: =AGGREGATE(5,7,$D$2:$D$21)

AGGREGATE関数の使い方|集計方法14~19までの書式

集計方法「14」から「19」までは引数「集計方法・オプション・配列・順位」の書式を選択して計算します。
この中から「降順の順位」を求める集計をしてみましょう。

ランダムに並べた成績表があり、これを点数の高い順に並べ替えます。欠席者の行が非表示になっており、「氏名」の欄には点数が並べ替えられたら氏名も自動で表示されるように「=IFERROR(VLOOKUP(E2,$A$2:$B$16,2,0),””)」の数式が入っています。
AGGREGATE関数で成績順に並べ替えるデータ表

  • 集計方法」には降順の順位を求める「14」を指定します。
    AGGREGATE関数の集計方法に降順の順位を求める14を指定
  • オプション」には非表示の行を無視する「5」を指定します。
    AGGREGATE関数のオプションに非表示の行を無視する5を指定
  • 配列」には「総合点」配下のデータ範囲を指定します。
    AGGREGATE関数の配列に「総合点」配下のデータ範囲を指定
  • 順位」には「1」をセル参照で指定します。
    AGGREGATE関数の順位にセル参照で1を指定

ダイアログでの指定は以下のようになります。
AGGREGATE関数の引数ダイアログでの指定

この数式を下までオートフィルコピーして完了です。成績順位で表が並べ替えられました。
AGGREGATE関数の数式をオートフィルコピー

数式: =AGGREGATE(14,5,$A$2:$A$16,D2)

SUBTOTAL関数のまとめ
  • AGGREGATE関数は様々な種類の集計方法(合計・平均・分散・個数・標準偏差・最大値と最小値・降順の順位・百分位数など)を実行できる便利な関数です
  • AGGREGATEはSUBTOTAL関数を拡張した機能を持つ関数で、Excel2010から数学/三角のライブラリに組み込まれました
  • AGGREGATE関数はオプションの指定によって小計・エラー値・非表示データを除外して集計できます
  • 引数「集計方法」には対応する1~19の番号で指定します
  • 集計方法の番号(1~13)は引数「集計方法・オプション・参照1・参照2・・・」の書式で実装します
  • 集計方法の番号(14~19)は引数「集計方法・オプション・配列・順位」の書式で実装します
タイトルとURLをコピーしました