概要
集計したい範囲に #DIV/0! や #N/A などのエラーが混ざっている と、
SUM・AVERAGE・MAX などの通常関数は 1つでもエラーがあるだけで全体がエラー になります。
そんなときに便利なのが AGGREGATE 関数です。
AGGREGATE は
「どの集計をするか」＋「何を無視するか」
を指定できるため、
エラーセルを完全に無視して集計 できます。
初心者でもすぐ使えるように、テンプレートと例題を交えて丁寧に解説します。
AGGREGATE の基本
書式（範囲に対する集計）
=AGGREGATE(集計方法番号, オプション番号, 範囲)
集計方法番号（よく使うもの）
1 … AVERAGE（平均）
4 … MAX（最大値）
5 … MIN（最小値）
9 … SUM（合計）
14 … LARGE（n番目に大きい値）
15 … SMALL（n番目に小さい値）
オプション番号（重要）
6 … エラー値を無視
7 … 非表示行とエラー値を無視
エラーセルを無視したいときは 6 を使います。
エラーセルを無視して合計する
SUM の代わりに AGGREGATE を使う
A2:A10 に数値とエラーが混在しているとします。
普通の SUM だとエラーがあると全体がエラーになります。
=SUM(A2:A10)
AGGREGATE ならエラーを無視して合計できます。
=AGGREGATE(9, 6, A2:A10)
動き
- 数値 → 合計に含める
- エラー → 完全に無視
エラーセルを無視して平均を出す
AVERAGE の代わりに AGGREGATE
=AGGREGATE(1, 6, A2:A10)
- 数値だけで平均
- エラーセルは存在しないものとして扱う
AVERAGEIF では対応しきれないケースでも、AGGREGATE なら一発です。
エラーセルを無視して最大値・最小値を求める
最大値（MAX）
=AGGREGATE(4, 6, A2:A10)
最小値（MIN）
=AGGREGATE(5, 6, A2:A10)
MAX/MIN はエラーが混ざるとすぐ壊れるので、
AGGREGATE の恩恵が大きい場面です。
エラーセルを無視して「n番目の大きさ」を求める
LARGE / SMALL の代わりに AGGREGATE
n番目に大きい値（LARGE 相当）
=AGGREGATE(14, 6, A2:A10, n)
n番目に小さい値（SMALL 相当）
=AGGREGATE(15, 6, A2:A10, n)
例：エラーを無視して 2番目に大きい値を求める
=AGGREGATE(14, 6, A2:A10, 2)
AGGREGATE を使うときのコツ
まずは普通の関数で書いて、エラーが出たら AGGREGATE に置き換える
SUM → AGGREGATE(9,6,範囲)
AVERAGE → AGGREGATE(1,6,範囲)
MAX → AGGREGATE(4,6,範囲)
MIN → AGGREGATE(5,6,範囲)
この置き換えパターンを覚えておくと便利です。
IFERROR(…,0) との違い
IFERROR
→ エラーを「0として扱う」
AGGREGATE
→ エラーを「完全に無視する」
どちらが正しいかは目的次第です。
例題
問題1
A2:A10 に数値とエラーが混在しています。
エラーを無視して合計を求める式を書いてください。
=AGGREGATE(9, 6, A2:A10)
問題2
A2:A10 の平均を求めたいが、エラーセルを無視したい。
AGGREGATE を使った式を書いてください。
=AGGREGATE(1, 6, A2:A10)
問題3
A2:A10 の最大値を、エラーを無視して求める式を書いてください。
=AGGREGATE(4, 6, A2:A10)
問題4
A2:A10 の中から、エラーを無視して「2番目に大きい値」を求める式を書いてください。
=AGGREGATE(14, 6, A2:A10, 2)
問題5
A2:A10 の中にエラーと非表示行が混ざっています。
エラーと非表示行の両方を無視して合計を求める式を書いてください。
=AGGREGATE(9, 7, A2:A10)
まとめ
AGGREGATE は、
「エラーセルを無視して集計したい」
という Excel の定番トラブルを一発で解決してくれる強力な関数です。
まずはこの2つを覚えておけば十分です。
=AGGREGATE(9, 6, 範囲) ' エラーを無視した合計
=AGGREGATE(1, 6, 範囲) ' エラーを無視した平均
あなたの表の中で「エラーが混ざって集計が壊れている」場所があれば、
ぜひ AGGREGATE に置き換えてみてください。
表が一気に安定して、扱いやすくなります。