エクセルで非表示の行を集計しない!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関数で求める