エラー値や非表示の行を無視して集計できるAGGREGATEの使い方［Excelシート付］

SUM関数で集計する際、集計するセル範囲に［#DIV/0!］や［#N/A］などのエラー値があり、結果がエラー値になってしまったり、合計したくない非表示の行の数値を含めて合計されてしまったりして、困ったことはありませんか。

この記事では、そんな悩みを解決できる、AGGREGATE関数を紹介します。
AGGREGATE関数は、SUBTOTAL関数の上位互換関数で、Excel2010以降であれば使用できます。

SUBTOTAL関数を使える方も、そうでない方も、AGGREGATE関数をぜひ使えるようになってください。

→記事で紹介した例題をそのまま掲載した［練習用Excelシート］

AGGREGATE関数とは

AGGREGATE（アグリゲイト）関数は、第1引数に「集計方法」を、第2引数に「非表示」や「小計」、「エラー値」を無視するかどうかを、第3引数に集計範囲などを指定して使います。

「集計方法」には、合計や個数のカウント、平均、最大、最小、中央値などがありますが、合計が使えるようになれば十分です。

似たような関数にSUBTOTAL関数がありますが、SUBTOTAL関数に比べ、エラー値が無視できるほか、使用できる関数が19種類と8種類多いのが特徴です。

書式	AGGREGATE( 集計方法, オプション, 参照1, ... )
	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	百分位数を求める（0%と100%を除く）	PERCENTILE.EXC
19	四分位数を求める（0%と100%を除く）	QUARTILE.EXC

オプション

オプション	週目
0または省略	セル範囲内にあるSUBTOTAL関数とAGGREGATE関数を無視
1	非表示の行、セル範囲内にあるSUBTOTAL関数とAGGREGATE関数を無視
2	エラー値、セル範囲内にあるSUBTOTAL関数とAGGREGATE関数を無視
3	非表示の行、エラー値、セル範囲内にあるSUBTOTAL関数とAGGREGATE関数を無視
4	何も無視しない
5	非表示の行を無視
6	エラー値を無視
7	非表示の行とエラー値を無視

エラー値を無視する

今、次のような製品名別の売り上げの表があり、B7セルに売り上げの合計を出したい場合には、ほとんどの方がSUM関数を使うと思います。

では、次のように、［#DIV/0!］や［#N/A］などのエラー値が含まれている場合はどうでしょうか。

［#DIV/0!］や［#N/A］などのエラー値が含まれている場合に、SUM関数で合計するとエラー値になってしまいます。

このように、エラー値が含まれている場合にAGGREGATE関数を使って合計すると、エラーが発生しなくなります。

B7セルに（日本語入力オフの状態で）「=ag」と入力すると、AGGREGATE関数が表示されるので、AGGREGATE関数を選び、［Tabキー］を押して選択します。

第1引数には、SUM関数を意味する「9」を、第2引数には、［非表示の行、エラー値、セル範囲内にあるSUBTOTAL関数とAGGREGATE関数を無視］するオプション「3」を、第3引数に合計範囲である「B2:B6」を指定します。

B7セルの数式は、=AGGREGATE(9,3,B2:B6) となります。エラー値を無視して合計を求めることができます。

非表示行を無視する

さきほどと同じ製品名別の売り上げの表があったとします。B7セルには、=SUM(B2:B6) というSUM関数が入っています。

このとき、4行目の製品Cを非表示にしてみます。4行目の製品Cを非表示にしても、合計は非表示にする前と同じです。

非表示にした行を合計したくない場合にも、AGGREGATE関数を使って合計します。

B7セルに、=AGGREGATE(9,3,B2:B6) というAGGREGATE関数を入力します。すると、非表示にしたセルを値を含まない合計を求めることができます。

小計を無視する

今、次のような小計を含んだ製品名別の売り上げの表があった場合に、SUM関数で売り上げの合計を出すと、小計を追加・削除した場合などに、参照範囲を設定し直すなどメンテナンスが面倒です。
このような場合にも、AGGREGATE関数が便利です。

小計をAGGREGATE関数にする

ポイントは、小計も合計もAGGREGATE関数を使用することです。
これは、AGGREGATE関数の合計範囲内に、AGGREGATE関数がある場合にはそのセルを無視してくれるため、合計が重複しないからです。

AGGREGATE関数のコツ

集計するセル範囲に［#DIV/0!］や［#N/A］などのエラー値がある場合、非表示の行を合計したくない場合、小計がある場合の合計には、AGGREGATE関数を使ってください。

そのとき、AGGREGATE関数の第1引数には「9」を、第2引数には「3」を指定するのがコツです。

個人的には、AG関数などと呼ばれて、SUM関数より「日常的に使われるようになったらいいのに」と思っているくらいです。

そこで、SUM関数より使われるにはどうしたらいいかを考えました。

AGGREGATE関数（勝手な略称、AG関数）は、「=AGGREGATE(9,3,合計範囲)」が鉄板的なパターンです。ですから、これを単語登録します（ワンクリックで入力されるようなボタンが開発されることを希望しています）。つまり、「単語」を「=AGGREGATE(9,3,」とし、「読み」を「＝あｇ」として単語登録します。

これで、「＝あｇ」と入力し、スペースなどの変換キーを押すと、「=AGGREGATE(9,3,」と入力されますので、あとは「合計範囲」を選んで確定するだけです（最後の閉じカッコ［)］は入力しなくても、Excelが勝手に入力してくれます）。

いかがですか？SUM関数の使い勝手に近づいたと思いませんか。いずれにしても、AGGREGATE関数は、便利な関数なのでぜひ使えるようになってください。

参考文献

Excel のヘルプとラーニング