エクセルAGGREGATE関数は、集計方法を変えるだけ、色々な集計ができる関数ですが、AGGREGATE関数には、他の集計関数にはない特性があります。AGGREGATE関数は、オプションを設定することで、非表示の行を無視して集計できます。非表示の行を集計しません。その他、エラー値を無視して集計することができます。集計方法とオプションを色々変えることで、複雑な集計ができます。AGGREGATE関数は、2010エクセルから、集計関数に追加されました。１つの集計表で、集計方法を変えて色々な集計を求めたい。非表示の行を無視して集計したいなど、複雑な集計をしたいなら、AGGREGATE関数です。
目次
AGGREGATE関数
AGGREGATE関数では、非表示の行やエラー値を無視するオプションを使用して、さまざまな集計をすることができます。
AGGREGATE関数の書式と引数
セル範囲形式：集計方法が1～13
AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)
配列形式：集計方法が14～19
AGGREGATE(集計方法, オプション, 配列, [k])
| 集計方法 | 1 ～ 19の数字で、集計方法を指定します。 |
| オプション | 範囲内の無視する値を数値で指定します。 |
| 範囲 1 | 集計する最初の範囲を指定します。 |
範囲 1、範囲 ２,…集計する範囲を 253個まで指定できます。
集計方法が14～19は、配列形式になります。
配列を受け取る関数の場合、配列、配列数式、または集計値を求めるセル範囲の参照を範囲 1 に指定します。
範囲 2 は、特定の関数に必要な２番目の引数を指定します。
AGGREGATE関数の集計方法
| 集計方法 | 関数 | 説明 |
| 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 | 四分位数を求める |
| 18 | PERCENTILE.EXC | 百分位数を求める(0%と100%を除く) |
| 19 | QUARTILE.EXC | 四分位数を求める(0%と100%を除く) |
AGGREGATE関数のオプション
| オプション | 内容 |
| 0または省略 | ネストされたSUBTOTAL関数 AGGREGATE関数を無視します |
| 1 | 非表示の行、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します |
| 2 | エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します |
| 3 | 非表示の行、エラー値、ネストされたSUBTOTAL関数とAGGREGATE関数を無視します |
| 4 | 何も無視しません |
| 5 | 非表示の行を無視します |
| 6 | エラー値を無視します |
| 7 | 非表示の行とエラー値を無視します |
エクセルAGGREGATE関数は、集計方法を変えることで、色々な集計ができます。その点では、SUBSTITUTE関数とよく似ています。
SUBSTITUTE関数との違いは、オプションを設定することで、エラー値や非表示の行、ネストされたAGGREGATE関数やSUBSTITUTE関数を無視することができます。
SUBSTITUTE関数との違いを交えながら、AGGREGATE関数の基本的な使い方を、説明していきましょう。
非表示の行を無視して集計
SUBTOTAL関数で、集計してある表があります。
１．E14の式を見てみましょう。
E14 =SUBTOTAL(9,E2:E13)
E14には、合計「343,000」が返されています。
２．次は、「Z」で始まる「商品№」を、非表示してみましょう。「Z」で始まる行番号を選択して、右クリック→「非表示」をクリック。または「ホーム」タブ→「セル」グループ→「書式」→「表示設定」の「非表示/再表示」→「行を表示しない」をクリックでもできます。
３．E14に、式を入力します。
E14 =SUBTOTAL(9,E2:E13)
E14には、合計「343,000」が返されました！
非表示にした行は、SUBTOTAL関数の場合、式に含まれてしまいます。
３．今度は、AGGREGATE関数で、E14に、非表示の行を無視するオプションを指定して、集計する式を入力してみましょう。
E14 =AGGREGATE(9,5,E2:E13)
集計方法は「９」、オプションは「５」を指定します。
オプション「５」は、非表示の行だけを無視します。
E14には、非表示の行が無視されて、「293,000」が返されました！
エラー値を無視して集計
SUM関数やSUBSTITUTE関数の式で集計した場合、集計範囲にエラー値がでるとエラーが返されます。
１．E14に、SUBTOTALの式を入力してみましょう。
E14 =SUBTOTAL(9,E2:E13)
E14には、エラー値「#VALUE!」が返されます。
２．今度は、E14に、AGGREGATE関数の式を入力しましょう。AGGREGATE関数に、エラー値を無視するオプションを指定して、集計します。
E14 =AGGREGATE(9,6,E2:E13)
集計方法は「９」、オプションは「6」を指定します。
オプション「6」は、エラー値だけを無視します。
E14には、エラー値が無視されて、「279,000」が返されました！
SUBTOTAL関数とAGGREGATE 関数を無視して集計
AGGREGATE関数は、ネストされたSUBTOTAL関数 、AGGREGATE関数を無視して集計します。
E17に、AGGREGATE関数の式を入力しましょう。
小計に、式を入力しましょう。
集計方法は「９」、オプションは省略します。または「0」を入力します。
E6 =SUBTOTAL(9,D2:D5) または E6 =AGGREGATE(9,,E2:E5)
E10 =SUBTOTAL(9,D7:D9) または E10 =AGGREGATE(9,D7:D9)
E15 =SUBTOTAL(9,D11:D15) または E15 =AGGREGATE(9,D11:D15)
合計に、式を入力しましょう。
E17 =AGGREGATE(9,,E2:E16)
E17にはネストされたSUBTOTAL関数 、AGGREGATE関数が無視されて「343,000」が返されました！
エラー値、非表示の行、ストされたSUBTOTAL関数、AGGREGATE関数を無視したい場合は、AGGREGATE関数のオプションに、「3」を指定しましょう。
集計方法を変えて集計
AGGREGATE関数の集計方法を変更するだけで、色々な集計ができます。
最大値を求める
金額の最大値を、求めてみましょう。
C7に、式を入力します。
C7 =AGGREGATE(4,3,C2:C6)
集計方法は「４」です。「金額」の最大値が求められます。
最小値を求める
金額の最小値を、求めてみましょう。
C7に、式を入力します。
C7 =AGGREGATE(5,3,C2:C6)
集計方法は「５」です。「金額」の最小値が求められます。
合計を求める
金額の合計を、求めてみましょう。
C7に、式を入力します。
C7 =AGGREGATE(9,3,C2:C6)
集計方法は「９」です。「金額」の合計が求められます。
非表示の行を無視して集計するAGGREGATE関数
エクセルAGGREGATE関数は、集計方法とオプションを変更することで、色々な集計をすることができる関数ですが、あまりよく知られていません。集計でよく使われている関数は、SUM関数、またはSUBSTITUTE関数です。AGGREGATE関数の最大の特徴は、非表示の行を無視して集計できることです。１つの集計表で、集計方法を変えて色々な集計をしたい、エラー値や非表示の行を無視して集計できるようにしたいときは、AGGREGATE関数を使いましょう。
こちらの記事もご覧ください。⇒さまざまな集計をエクセルSUBTOTAL関数で求める
