今回はAGGREGATE関数のすごいところをまとめて解説します。同じ列にある小計も非表示もエラーも全部無視して集計する方法です。すべての要素が関係しなくても、この指定をしておけば大丈夫という便利な方法のご紹介です。さすが”集計”を意味するAGGREGATE関数はSUBTOTAL関数の上を行きます。
AGGREGATE関数の構文や引数の指定については、以下の関連記事で書いています。
エラーが出ているセルを無視して集計できる！これがAGGREGATE関数の特徴の一つです。SUBTOTAL関数では、参照範囲内にエラーがあると集計結果もエラーになってしまいます。SUM関数も同様です。この記事では、それぞれの関数を比較しながら[…]
小計も非表示もエラーも無視して集計する方法
構文
- =AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)
結論から言えば、以下の通りの指定になります。
- 第一引数：9
- 第二引数：3
- 第三引数：範囲指定
この「9,3,範囲指定」は非常に便利です。
SUM関数やSUBTOTAL関数との比較で見ていきましょう。
SUM関数やSUBTOTAL関数の場合
日付と売上、月ごとの小計を含めた集計表を使って説明します。
SUM関数は参照範囲にあるすべての値を集計します。そのため、小計も含めてすべてを合計していることが画像から分かります。この状況を避けるためにSUBTOTAL関数をよく使いますね。
SUBTOTAL関数は、集計する時に小計を無視して集計してくれます。参照範囲にSUBTOTAL関数がある場合、その値は集計から除外されるわけです。
では、手動で特定の行を非表示にした場合はどうなるでしょうか。
非表示
画像のように、7行目の「5月10日」を非表示にしました。SUM関数もSUBTOTAL関数も、手動で非表示にしたセルの値を含めて集計しているので、合計は非表示前と変わらないことが分かります。でも非表示の行は集計から除きたい時がありますよね。
では、特定のセルにエラーが出ているとどうなるでしょうか。
エラー
画像の通り、「5月30日」の値にエラーが出ています。この場合、SUM関数もSUBTOTAL関数も小計と合計ともにエラーを返しています。一つのセルのエラーが集計全体に影響するということです。でもエラーを無視して集計したい時もあるのではないでしょうか。
では、AGGREGATE関数ではどうなるでしょうか。
AGGREGATE関数の場合
先程の表の右側に同じ値をコピーし、AGGREGATE関数で集計してみます。「9,3,範囲指定」でそれぞれ小計、合計を集計しています。SUBTOTAL関数と同様、小計は無視して集計していることが分かりますね。
第二引数の「オプション」で設定できる条件は8通りです。画像のように「3」は、「非表示の行、エラー値、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視します」。今回の場合、「入れ子になっているSUBTOTAL関数およびAGGREGATE関数」は「小計」にあたる部分です。そのため、この指定で非表示、エラー、小計をすべて無視して集計できるということになります。いわゆる“全部のせ”のような条件ですね。
そのため、手動で特定の行を非表示にしたり、特定のセルにエラーが出ていても、それらを無視して集計してくれます。
非表示
7行目の「5月10日」を手動で非表示にしています。非表示のセル（売上30,000）を無視して集計されていることが分かります。
エラー
「5月30日」のセルにエラーが出ています。AGGREGATE関数の場合、エラー値を無視して集計しているのを確認できます。「小計」(D10)、「合計」(D15)ではエラー値(D9)を除外した集計がされていますね。
まとめ
いかがでしたか。今回はAGGREGATE関数を使って、小計も非表示もエラーもすべて無視して集計する方法でした。こうして考えてみると、AGGREGATE関数 ≧ SUBTOTAL関数 ということになりますね！