エラーが出ているセルを無視して集計できる！これがAGGREGATE関数の特徴の一つです。SUBTOTAL関数では、参照範囲内にエラーがあると集計結果もエラーになってしまいます。SUM関数も同様です。この記事では、それぞれの関数を比較しながら、AGGREGATE関数でエラーを無視して集計する方法を画像付きで解説します。
AGGREGATE関数について
Microsoftの公式サポートページには、AGGREGATE関数について次のような説明があります。
リストまたはデータベースの集計値を返します。 AGGREGATE 関数では、非表示の行やエラー値を無視するオプションを使用して、リストやデータベースにさまざまな集計関数を適用することもできます。
「リストまたはデータベースの集計値を返す」という点はSUBTOTAL関数も同じです。ただし、オプションの指定でエラーを無視できる点が特徴的です。
構文
セル範囲形式の場合、以下の構文です。
- =AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)
集計方法
第1引数で「集計方法」を指定する点はSUBTOTAL関数と同じですが、集計方法の種類は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％を除く） |
SUBTOTAL関数と比較すると12～19までの集計方法が加わっており、合計19種類あります。
オプション
第2引数の「オプション」では、以下の条件を指定できます。
| オプション | オプションの説明 |
|---|---|
| 0（または省略） | 入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 1 | 非表示の行、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 2 | エラー値、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 3 | 非表示の行、エラー値、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視 |
| 4 | すべてを検索対象とする |
| 5 | 非表示の行を無視する |
| 6 | エラー値を無視する |
| 7 | 非表示の行とエラー値を無視する |
0～7の合計8種類です。
このように、AGGREGATE関数はSUBTOTAL関数よりも集計方法の種類が多く、オプションで無視する値を指定して集計できます。こうしたことからSUBTOTAL関数の“上位互換”関数と呼ばれることもあります。
EXCEL2010から使えるようになりましたので、そんなに新しい関数ではありません。SUBTOTALから乗り換えてもよいのですが、まだまだ認知度が低い関数でもあります。現場で共同編集する場合など、認知度の低さはマイナス要因ですね。ただ、SUBTOTAL関数でできることはAGGREGATE関数でもできますし、より多くのことを行える”上位互換”なので、使い方を覚えたいものですね。
エラーを無視して集計する方法
結論から言えば、以下の通りの数式を指定すればOKです。
分かりやすくするために、SUM関数、SUBTOTAL関数との比較で見てみましょう。
SUM関数、SUBTOTAL関数との比較
「日付」と「売上」だけの表ですが、SUM関数とSUBTOTAL関数で集計しています。
特定の行でエラーが出ている場合は、集計にどんな影響が出るのでしょうか。
「5月10日」の売上のセルにエラー（#N/A）が出ています。この場合、SUM関数、SUBTOTAL関数ともに「合計」の集計値もエラーが返されています。
同じ表をコピーして、AGGREGATE関数を使ってみます。
「合計」（D13）に「=ag」 まで入力すれば、候補に「AGGREGATE」が出てきます。「TAB」キーを押して入力します。
第1引数（集計方法）で「9 – SUM」を選択します。カンマで区切り、次はオプションです。
第2引数（オプション）では「6 – エラー値を無視します」を選択します。あとは範囲選択です。
カンマで区切り、集計したい範囲を指定しましょう。画像ではD4:D12までを指定し、4月～6月までの売上の値を選択しています。「5月10日」のセルにエラーが出ていますが、それを含めて選択されています。集計結果はどうなるでしょうか。
分かりやすくするためにSUM関数の列ではエラーを消しています。AGGREGATE関数の場合、エラーを返したセルを無視して集計されているのが分かると思います。D13のセルです。
このように、SUM関数とSUBUTOTAL関数の場合は指定した範囲にエラーがある時、「合計」（集計値）にもエラーが出てしまいます。しかし、AGGREGATE関数では、オプションで「エラーを無視」を指定することで、エラーの出ているセルを無視（除外）して集計してくれるのです。
おまけ情報
便利機能
特定のセルに入力されている数式を確認するには、そのセルを選択して上部の表示バーを見れば分かります。しかし、複数のセルに設定されている数式を同時に見たい場合はどうしたらよいでしょうか。
FORMULATEXTを使う
数式を表示させたいセルで、「=fo」と入力すると、候補に「FORMULATEXT」が出てきます。画像の通り「数式を文字列として返す」関数です。
上の画像のように、D13に入力された数式を知りたい場合は、参照としてD13を指定します。
赤囲み部分のように、数式が表示されます。B15、C15も同様にFORMULA関数で表示させています。
まとめ
いかがでしたか。今回は、AGGREGATE関数でエラーを無視して集計する方法でした。AGGREGATE関数の場合、オプションでエラーを無視する指定をすれば、参照範囲内のエラーを無視した集計結果が表示されます。
次回は引き続きAGGREGATE関数で、非表示の行を無視したり、小計を除外して集計値を出す方法をご紹介します。次回もお楽しみに！