エクセルで非表示の行を集計しない!AGGREGATE関数

エクセルAGGREGATE関数は、集計方法を変えるだけ、色々な集計ができる関数ですが、AGGREGATE関数には、他の集計関数にはない特性があります。AGGREGATE関数は、オプションを設定することで、非表示の行を無視して集計できます。非表示の行を集計しません。その他、エラー値を無視して集計することができます。集計方法とオプションを色々変えることで、複雑な集計ができます。AGGREGATE関数は、2010エクセルから、集計関数に追加されました。1つの集計表で、集計方法を変えて色々な集計を求めたい。非表示の行を無視して集計したいなど、複雑な集計をしたいなら、AGGREGATE関数です。

 

 

 AGGREGATE関数

AGGREGATE関数では、非表示の行やエラー値を無視するオプションを使用して、さまざまな集計をすることができます。

AGGREGATE関数の書式と引数

セル範囲形式:集計方法が1~13

AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)

配列形式:集計方法が14~19

AGGREGATE(集計方法, オプション, 配列, [k])

集計方法1 ~ 19の数字で、集計方法を指定します。
オプション範囲内の無視する値を数値で指定します。
範囲 1集計する最初の範囲を指定します。

範囲 1、範囲 2,…集計する範囲を 253個まで指定できます。

集計方法が14~19は、配列形式になります。

配列を受け取る関数の場合、配列、配列数式、または集計値を求めるセル範囲の参照を範囲 1 に指定します。

範囲 2 は、特定の関数に必要な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関数の基本的使い方

エクセルAGGREGATE関数は、集計方法を変えることで、色々な集計ができます。その点では、SUBSTITUTE関数とよく似ています。

SUBSTITUTE関数との違いは、オプションを設定することで、エラー値や非表示の行、ネストされたAGGREGATE関数やSUBSTITUTE関数を無視することができます。

SUBSTITUTE関数との違いを交えながら、AGGREGATE関数の基本的な使い方を、説明していきましょう。

 非表示の行を無視して集計

SUBTOTAL関数で、集計してある表があります。

1.E14の式を見てみましょう。

 

E14 =SUBTOTAL(9,E2:E13)

E14には、合計「343,000」が返されています。

2.次は、「Z」で始まる「商品№」を、非表示してみましょう。「Z」で始まる行番号を選択して、右クリック→「非表示」をクリック。または「ホーム」タブ→「セル」グループ→「書式」→「表示設定」の「非表示/再表示」→「行を表示しない」をクリックでもできます。

3.E14に、式を入力します。

 

E14 =SUBTOTAL(9,E2:E13)

E14には、合計「343,000」が返されました!

非表示にした行は、SUBTOTAL関数の場合、式に含まれてしまいます。

3.今度は、AGGREGATE関数で、E14に、非表示の行を無視するオプションを指定して、集計する式を入力してみましょう。

 

E14 =AGGREGATE(9,5,E2:E13)

集計方法は「9」、オプションは「5」を指定します。

オプション「5」は、非表示の行だけを無視します。

E14には、非表示の行が無視されて、「293,000」が返されました!

 

 

エラー値を無視して集計

SUM関数やSUBSTITUTE関数の式で集計した場合、集計範囲にエラー値がでるとエラーが返されます。

1.E14に、SUBTOTALの式を入力してみましょう。

 

E14 =SUBTOTAL(9,E2:E13)

E14には、エラー値「#VALUE!」が返されます。

2.今度は、E14に、AGGREGATE関数の式を入力しましょう。AGGREGATE関数に、エラー値を無視するオプションを指定して、集計します。

 

E14 =AGGREGATE(9,6,E2:E13)

集計方法は「9」、オプションは「6」を指定します。

オプション「6」は、エラー値だけを無視します。

E14には、エラー値が無視されて、「279,000」が返されました!

 

 

 SUBTOTAL関数とAGGREGATE 関数を無視して集計

AGGREGATE関数は、ネストされたSUBTOTAL関数 、AGGREGATE関数を無視して集計します。

E17に、AGGREGATE関数の式を入力しましょう。

 

小計に、式を入力しましょう。

集計方法は「9」、オプションは省略します。または「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関数を無視する

エラー値、非表示の行、ストされたSUBTOTAL関数、AGGREGATE関数を無視したい場合は、AGGREGATE関数のオプションに、「3」を指定しましょう。

 

 

AGGREGATE関数で色々な集計方法で集計

集計方法を変えて集計

AGGREGATE関数の集計方法を変更するだけで、色々な集計ができます。

最大値を求める

金額の最大値を、求めてみましょう。

C7に、式を入力します。

 

C7 =AGGREGATE(4,3,C2:C6)

集計方法は「4」です。「金額」の最大値が求められます。

最小値を求める

金額の最小値を、求めてみましょう。

C7に、式を入力します。

 

C7 =AGGREGATE(5,3,C2:C6)

集計方法は「5」です。「金額」の最小値が求められます。

合計を求める

金額の合計を、求めてみましょう。

C7に、式を入力します。

 

C7 =AGGREGATE(9,3,C2:C6)

集計方法は「9」です。「金額」の合計が求められます。

非表示の行を無視して集計するAGGREGATE関数

エクセルAGGREGATE関数は、集計方法とオプションを変更することで、色々な集計をすることができる関数ですが、あまりよく知られていません。集計でよく使われている関数は、SUM関数、またはSUBSTITUTE関数です。AGGREGATE関数の最大の特徴は、非表示の行を無視して集計できることです。1つの集計表で、集計方法を変えて色々な集計をしたい、エラー値や非表示の行を無視して集計できるようにしたいときは、AGGREGATE関数を使いましょう。

こちらの記事もご覧ください。⇒さまざまな集計をエクセルSUBTOTAL関数で求める