エラー値を含む計算はAGGREGATEを使え！｜エクセル関数

複数のセルに数値が入っていて、その合計を求めたいときはSUM関数を使いますよね。

でも、その集計対象の中にエラー値が入っていたらどうなるでしょうか？

例えば、こんな状態です。

合計対象の中に「#N/A」というエラー値が含まれていますよね。

エラー値を含むセル範囲に対してSUMを実行すると、

はい。エラーが返ってきます。

では、合計対象のセル範囲にエラー値が含めまれている時、そのエラーを無視して合計を求めるにはどうすればいいのでしょうか？

ビジネスエリートの「これはすごい！」を集めた 外資系投資銀行のエクセル仕事術???数字力が一気に高まる基本スキル

その答えが、このエントリのタイトルにあるAGGREGATE関数です。

では早速、AGGREGA関数を使って計算してみます。

エラー値を含む範囲を指定した状態で計算を実行すると、

はい。見事に計算できました。

。。。
と、AGGREGATE関数の使い方、特に引数について一切の説明を飛ばしてしまってますので、改めて説明すると、、、

まず、第一引数(上図では9となっているところ)では集計方法を指定しています。

集計方法は下表の19パターンが用意されています。

集計方法	関数
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
19	QUARTILE.EXC

第一引数の9はSUMすることを表していたのです。

次に、第二引数(上図では7となっていたところ)は集計時のオプションを指定します。
ここがエラー値を除いて計算する際のポイントです。

用意されているオプションは下表のとおり。

オプション	動作
0 または省略	ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
1	非表示の行、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
2	エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
3	非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
4	何も無視しません。
5	非表示の行を無視します。
6	エラー値を無視します。
7	非表示の行とエラー値を無視します。

上の例では7を指定して、非表示の行とエラー値を無視して計算するようにしていました。

そして、第三引数で集計対象のセルを指定しています。

今回の例ではセル範囲を合計していましたが、第一引数の表のとおり、計算方法は合計(SUM)だけではなくAVERAGE等、普段使うことの多い集計関数が網羅されています。

そして第二引数の表のとおり集計オプションが用意されていますので、それを使ってエラー値を無視して計算することが可能になるわけです。

第二引数でおおよそ使うことになるのは、5・6・7の3パターンになると思います。
(4を指定するならAGGREGATE関数使わずに、従来からある関数使いますよね。)

外資系投資銀行がやっている 最速のExcel

ということで、エラー値を含むセル範囲の計算にはAGGREGATE関数を使うのがベストチョイス。
さらに非表示行を集計対象に含めいないようにすれば、例えばオートフィルタを掛けたときに、表示されているセルだけを集計する、といったことにも対応できるわけです。

一昔前であれば、エラー値を集計対象から除くために「=SUMIF(集計範囲, "<>#N/A")」なんてやっていました。
これだとエラーの種類が「#N/A」の時だけ集計対象から除くことになるので、そのほかのエラー値も除きたいとなると非常にややこしいことになっていました。

それがAGGREGATE関数であれば一発で解決できるワケです。

非常に便利、この上ないですねー。