サラリーマン戦士が語る、SUMやAVERAGEでは絶対に到達できない安定集計テクニック

サラリーマンとして日々Excelと向き合っていると、「エラーが混ざっていてSUMが壊れる…」「フィルター後のデータだけ合計したい」「上位3件だけ合計したい」「SUBTOTALではできない集計をしたい」といった場面が必ず出てきます。

そんな“壊れない集計”を実現してくれるのが AGGREGATE（アグリゲート）関数 です。

AGGREGATEは、 エラー無視・非表示行無視・上位/下位取得など、19種類の集計を1つの関数で実行できる最強の集計関数 です。 SUM・AVERAGE・MAX・MIN の完全上位互換とも言える存在で、実務で安定した集計を作るなら必須の関数です。

この記事では、AGGREGATE関数の基本から応用まで、サラリーマン戦士としての実務経験を交えながら 徹底解説します。

🔍 AGGREGATE関数の書式

コード

=AGGREGATE(関数番号, オプション, 配列, [k])

✔ 引数の意味

関数番号：どの集計をするか（1〜19）
オプション：エラー無視・非表示無視など
配列：集計対象
k（任意）：上位・下位の順位など

📘 よく使う「関数番号」一覧（実務で使うものだけ）

関数番号	内容
1	AVERAGE（平均）
2	COUNT（件数）
3	COUNTA（空白以外の件数）
4	MAX
5	MIN
9	SUM（合計）
14	LARGE（上位）
15	SMALL（下位）

📘 よく使う「オプション」一覧（重要）

オプション	意味
0	何も無視しない
1	非表示の行を無視
2	エラー値を無視
3	非表示＋エラーを無視
5	SUBTOTALとAGGREGATEを無視
6	非表示＋SUBTOTAL＋AGGREGATEを無視

🎯 基本例：エラーを無視して平均（実務で最も使う）

コード

=AGGREGATE(1, 6, A2:A100)

1 → AVERAGE
6 → 非表示＋エラー無視
A2:A100 → 対象範囲

→ エラーがあっても壊れない平均が作れます。

🎯 エラーを無視して合計（SUMの上位互換）

コード

=AGGREGATE(9, 6, A2:A100)

→ SUMでは壊れる場面でも、AGGREGATEなら安心です。

🔥 フィルター後のデータだけ合計（SUBTOTALの上位互換）

コード

=AGGREGATE(9, 1, B2:B100)

1 → 非表示行を無視

→ フィルターで絞った結果だけ合計できます。

SUBTOTALより柔軟で、エラーにも強いのが魅力です。

🔥 上位3件の合計（ランキング集計）

コード

=SUM(AGGREGATE(14, 6, A2:A100, {1,2,3}))

14 → LARGE（上位）
{1,2,3} → 上位1〜3位

→ 上位3件の合計が一瞬で作れます。

🧩 SMALL（下位）も同じように使える

コード

=AGGREGATE(15, 6, A2:A100, 1)

→ 最小値（エラー無視）を取得できます。

📅 フィルター後の平均（SUBTOTALではできない）

コード

=AGGREGATE(1, 1, C2:C100)

→ フィルターで絞ったデータだけ平均できます。

🧠 AGGREGATEが強い理由

✔ エラーがあっても壊れない

#DIV/0! や #VALUE! が混ざっていても集計できます。

✔ フィルター後の集計ができる

SUBTOTALと同じく、非表示行を無視できます。

✔ 上位・下位の値を取得できる

LARGE・SMALL を内蔵しているため、ランキング集計が簡単です。

✔ SUM・AVERAGE・MAX・MINを1つの関数で実行

関数番号を変えるだけで集計方法を切り替えられます。

✔ SUBTOTALの完全上位互換

SUBTOTALではできない「エラー無視」が可能です。

🛠 よくあるミスと注意点

❗ 1. 関数番号を間違える

SUMは「9」、AVERAGEは「1」です。

❗ 2. オプションを忘れる

エラー無視は「6」です。

❗ 3. k を指定しないと LARGE/SMALL が動かない

上位・下位を使うときは必須です。

❗ 4. FILTERと併用するときは配列の形に注意

FILTER後の配列に対して使う場合、行列の形が変わることがあります。

📊 AGGREGATE と SUBTOTAL の違い

項目	AGGREGATE	SUBTOTAL
エラー無視	◎	×
上位・下位の取得	◎	×
フィルター後の集計	◎	◎
集計種類	19種類	11種類
実務向き	最強	中級

🧑‍💼 サラリーマン戦士が実際に使ってみた感想

サラリーマン戦士がAGGREGATE関数を使い始めたとき、「SUMやAVERAGEで壊れていた集計が全部解決した…」と本気で感動しました。

特にメリットは次のとおりです。

✔ エラーが混ざっていても壊れない

実務ではエラー値が混ざることが多く、AGGREGATEの強さを実感します。

✔ フィルター後の集計が正確

SUBTOTALより柔軟で、平均・最大値なども簡単に扱えます。

✔ 上位・下位の取得が神

ランキング集計が1式で完結します。

✔ 集計方法を関数番号で切り替えられる

SUM・AVERAGE・MAX・MIN を1つの関数で扱えるのは革命的です。

サラリーマン戦士として、「壊れない集計を作るならAGGREGATE一択」と断言できます。

📝 まとめ：AGGREGATEは“壊れない集計”の最強関数

エラーを無視して集計できます
フィルター後のデータだけ集計できます
上位・下位の値も取得できます
SUM・AVERAGE・MAX・MINの上位互換です
SUBTOTALより圧倒的に強力です

Excelで安定した集計を作るなら、 AGGREGATE関数は必ず覚えておきたい関数です。

🔍 AGGREGATE関数 の書式