ExcelのAGGREGATE関数の使い方｜エラーや非表示行を除外して集計

スポンサーリンク

「エラーが混じっている列の合計を出したいのに、数式がエラーになってしまう…」そんな場面はありませんか？

ExcelのSUM関数やSUBTOTAL関数は、範囲にエラー値が1つでもあると集計できません。#N/A や #VALUE! が混在するデータは意外と多いですよね。

そんなときに頼れるのがAGGREGATE関数です。エラー値や非表示行を除外しつつ、19種類の集計を使い分けられます。この記事では基本の書き方から実務の活用パターンまで紹介します。

ExcelのAGGREGATE関数とは？

AGGREGATE関数（読み方: アグリゲート関数）は、エラー値や非表示行を除外して集計できる関数です。

名前の「AGGREGATE」は英語で「集約する」を意味します。合計・平均・最大値など19種類の集計を1つの関数で切り替えられます。

最大の特長は エラー除外 です。SUBTOTAL関数はエラー値があると計算できません。AGGREGATE関数ならオプション番号の指定だけで、エラーをスキップして集計してくれますよ。

AGGREGATE関数にできることをまとめると、次のとおりです。

  • エラー値（#N/A #VALUE! #REF! など）を除外して集計する
  • フィルターや手動で非表示にした行を除外する
  • 19種類の集計方法を番号で切り替える（LARGE・SMALL・MEDIANなども対応）
  • ネストされたSUBTOTAL関数やAGGREGATE関数を自動で無視する

「エラーが混じるデータを安全に集計したい」ときに、まず検討したい関数です。

NOTE

AGGREGATE関数はExcel 2010以降で使えます。Excel 2007以前では使用できないので注意してください。

AGGREGATE関数の書き方（構文と引数）

基本構文

=AGGREGATE(集計方法, オプション, 参照1, [参照2], ...)

カッコの中に「何で集計するか」「何を除外するか」「どこを集計するか」を指定します。

引数の説明

引数必須/任意説明
集計方法必須集計に使用する関数を表す番号（1〜19）
オプション必須除外する対象を指定する番号（0〜7）
参照1必須集計の対象となるセル範囲
参照2, …任意LARGE・SMALL等で使うk値（順位）、または追加の範囲

SUBTOTAL関数との違いは「オプション」引数がある点です。この引数でエラー除外や非表示行の除外を細かく制御できます。

集計方法（第1引数）一覧

AGGREGATE関数は19種類の集計に対応しています。

番号対応する関数集計内容
1AVERAGE平均
2COUNT数値の個数
3COUNTA空白以外の個数
4MAX最大値
5MIN最小値
6PRODUCT積（すべて掛け合わせ）
7STDEV.S標本標準偏差（ばらつきの指標）
8STDEV.P母標準偏差
9SUM合計
10VAR.S標本分散
11VAR.P母分散
12MEDIAN中央値
13MODE.SNGL最頻値（最も多い値）
14LARGEk番目に大きい値
15SMALLk番目に小さい値
16PERCENTILE.INC百分位数（0〜1の範囲）
17QUARTILE.INC四分位数（0〜4の範囲）
18PERCENTILE.EXC百分位数（0と1を除く）
19QUARTILE.EXC四分位数（1〜3の範囲）

実務でよく使うのは 9（合計）・1（平均）・4（最大値）・5（最小値） の4つです。まずはこれだけ覚えておけば十分ですよ。

TIP

番号14（LARGE）と15（SMALL）は第4引数に「k」を指定します。たとえば =AGGREGATE(14, 6, A1:A10, 2) は「エラーを無視して2番目に大きい値」を返します。

オプション（第2引数）一覧

オプション番号で「何を除外するか」を指定します。

番号除外する対象
0ネストされたSUBTOTAL・AGGREGATE関数のみ
1非表示行 + ネスト関数
2エラー値 + ネスト関数
3非表示行 + エラー値 + ネスト関数
4何も除外しない
5非表示行のみ
6エラー値のみ
7非表示行 + エラー値

実務で最も使うのは 6（エラー値を除外） です。フィルター連動も必要なら 7（非表示行 + エラー値） を選びましょう。

TIP

覚え方のコツは「6 = エラー除外」です。これだけ覚えれば、ほとんどの場面に対応できますよ。

AGGREGATE関数の基本的な使い方

エラー値を無視して合計する

実際にAGGREGATE関数を使ってみましょう。次のような売上データがあるとします。

 A列（商品名）B列（売上）
2行目商品A50,000
3行目商品B30,000
4行目商品C#N/A
5行目商品D80,000
6行目商品E25,000

B4セルには #N/A エラーが入っています。この列をSUM関数で合計すると、結果もエラーになります。

=SUM(B2:B6)        → #N/A（エラーに巻き込まれる）

AGGREGATE関数ならエラーを除外して集計できます。

=AGGREGATE(9, 6, B2:B6)   → 185,000
  • 集計方法 9 = 合計（SUM）
  • オプション 6 = エラー値を除外

エラーの行をスキップして、50,000 + 30,000 + 80,000 + 25,000 = 185,000 が返ります。

エラー値を無視して平均・最大値を求める

集計方法の番号を変えるだけで、さまざまな集計に対応できます。

=AGGREGATE(1, 6, B2:B6)   → 46,250（平均）
=AGGREGATE(4, 6, B2:B6)   → 80,000（最大値）
=AGGREGATE(5, 6, B2:B6)   → 25,000（最小値）
=AGGREGATE(2, 6, B2:B6)   → 4（数値の個数）

どの集計もエラー行を自動的にスキップしてくれます。個別の関数を書き換えなくても、AGGREGATE関数1つで対応できるのは便利ですよね。

AGGREGATE関数の実践的な使い方・応用例

基本がわかったところで、実務で役立つ活用パターンを紹介します。

パターン1: VLOOKUP結果のエラーを無視して集計

VLOOKUP関数で別テーブルから値を取得すると、一致しないデータは #N/A エラーになります。このエラーが混在する列の集計はAGGREGATE関数の得意分野です。

たとえば、受注一覧のB列にVLOOKUP関数で単価を取得しているとします。

B2: =VLOOKUP(A2, 単価表!A:B, 2, FALSE)  → 1,000
B3: =VLOOKUP(A3, 単価表!A:B, 2, FALSE)  → #N/A
B4: =VLOOKUP(A4, 単価表!A:B, 2, FALSE)  → 2,500
B5: =VLOOKUP(A5, 単価表!A:B, 2, FALSE)  → 1,800

AGGREGATE関数で合計すれば、エラー行を除外して計算できます。

=AGGREGATE(9, 6, B2:B5)   → 5,300

IFERROR関数でエラーを0に変換する方法もあります。ただ、データ量が多いと補助列の手間がかかりますよね。AGGREGATE関数なら元データを変更せずに集計できますよ。

パターン2: フィルター連動 + エラー除外の合わせ技

フィルター付きの表にエラーが混在しているケースです。オプション 7 を使えば、非表示行とエラー値の両方を除外できます。

=AGGREGATE(9, 7, C2:C100)    → 非表示行もエラーも除外して合計
=AGGREGATE(1, 7, C2:C100)    → 非表示行もエラーも除外して平均
=AGGREGATE(2, 7, C2:C100)    → 非表示行もエラーも除外して件数

フィルターを切り替えるたびに値が更新されます。エラー処理の数式を追加する必要がないので、シートがすっきりしますよ。

パターン3: エラーを除外してN番目に大きい値を取得

AGGREGATE関数ならではの使い方です。LARGE関数やSMALL関数はエラーが混在すると動きません。集計方法14（LARGE）や15（SMALL）を使えば解決します。

=AGGREGATE(14, 6, B2:B100, 1)   → エラーを除外して最大値
=AGGREGATE(14, 6, B2:B100, 2)   → エラーを除外して2番目に大きい値
=AGGREGATE(14, 6, B2:B100, 3)   → エラーを除外して3番目に大きい値
=AGGREGATE(15, 6, B2:B100, 1)   → エラーを除外して最小値

第4引数の「k」で何番目の値を取得するか指定します。売上ランキングや成績順位の算出に使えます。

パターン4: 小計行がある表での安全な総合計

小計行にSUBTOTAL関数やAGGREGATE関数を使っている表で、総合計を求めるケースです。

| A列   | B列    |
|-------|--------|
| 営業部 | 100    |
| 営業部 | 200    |
| 小計   | =SUBTOTAL(9, B2:B3)  → 300 |
| 総務部 | 150    |
| 小計   | =SUBTOTAL(9, B5:B5)  → 150 |
| 総合計 | =AGGREGATE(9, 0, B2:B6) → 450 |

オプション 0 を指定すると、ネストされたSUBTOTAL関数やAGGREGATE関数を無視します。小計を二重カウントせずに正しい総合計が出せますよ。

SUBTOTAL関数との違い・使い分け

AGGREGATE関数とSUBTOTAL関数はどちらも「条件付き集計」ができる関数です。違いを比較してみましょう。

比較項目SUBTOTALAGGREGATE
集計方法の数11種類19種類（MEDIAN・LARGE・SMALLなど追加）
エラー値の除外できないできる（オプション6）
非表示行の除外できるできる
ネスト関数の無視できるできる
対応バージョンExcel 2003〜Excel 2010〜
構文のシンプルさシンプルやや複雑（オプション引数あり）

使い分けの判断基準は明確です。

  • エラーが混じらないデータの集計 → SUBTOTAL関数がシンプルでおすすめ
  • エラーが混じる可能性があるデータの集計 → AGGREGATE関数（オプション6 or 7）
  • LARGE・SMALL・MEDIANをエラー除外で使いたい → AGGREGATE関数一択
  • Excel 2007以前の環境で使う可能性がある → SUBTOTAL関数

迷ったら「エラーがあればAGGREGATE、なければSUBTOTAL」と覚えるのが一番シンプルですよ。

GROUPBY関数やPIVOTBY関数と組み合わせれば、グループ集計でもエラー除外が可能です。Microsoft 365の方はあわせてチェックしてみてください。

よくあるエラーと対処法

AGGREGATE関数で困ったときの原因と対策をまとめました。

エラー/症状原因対処法
#VALUE!集計方法番号が範囲外（0や20以上）1〜19の範囲で指定する
#VALUE!オプション番号が範囲外（8以上）0〜7の範囲で指定する
結果が0範囲に数値がない（文字列型の数値）VALUE関数やデータクリーンアップで数値に変換する
#NUM!LARGE・SMALLでk値がデータ数を超えているk値をデータ数以下に設定する
非表示行が除外されないオプションが0または4になっているオプション1・3・5・7のいずれかを使う

集計方法番号・オプション番号の入力ミス

最も多いミスが番号の入力間違いです。

=AGGREGATE(0, 6, B2:B10)    → #VALUE!（集計方法は1〜19）
=AGGREGATE(9, 8, B2:B10)    → #VALUE!（オプションは0〜7）

AGGREGATE関数は引数が多い分、番号の指定ミスが起きやすくなります。入力時にExcelが表示するヒントを活用しましょう。

LARGE・SMALLのk値エラー

集計方法14（LARGE）や15（SMALL）を使うとき、第4引数のk値に注意が必要です。

=AGGREGATE(14, 6, B2:B5, 10)   → #NUM!（データが4件しかない）

k値がデータの件数を超えると #NUM! エラーになります。エラー除外後の有効データ数を確認してからk値を指定してください。

配列引数での制限

AGGREGATE関数には1つ注意点があります。参照1に計算式を含む配列を渡す場合、非表示行の除外が効かないことがあります。

=AGGREGATE(9, 5, A2:A10*B2:B10)   → 非表示行が除外されない場合がある

この制限はMicrosoft公式ドキュメントにも記載されています。配列計算と非表示行除外を同時に使いたい場合は、補助列を活用するのが確実ですよ。

まとめ

ExcelのAGGREGATE関数のポイントをおさらいしましょう。

項目内容
読み方アグリゲート関数
役割エラー値・非表示行を除外した集計（19種類対応）
構文=AGGREGATE(集計方法, オプション, 参照1, [参照2])
よく使う組み合わせ=AGGREGATE(9, 6, 範囲) （エラー除外して合計）
SUBTOTALとの違いエラー除外の可否、集計方法の種類数
対応バージョンExcel 2010以降

まずは =AGGREGATE(9, 6, 範囲) でエラーを除外した合計から試してみてください。エラーが混在するデータでもスムーズに集計できるのを実感できますよ。

この記事で紹介した関数・関連記事

スポンサーリンク
タイトルとURLをコピーしました