【Excel初心者向け】エラーや非表示を無視して集計。AGGREGATE関数の使い方を分かりやすく解説
はじめに
Excelの集計作業で、「#N/A」などのエラーが出て合計できない」、「フィルターで非表示にした行を除外して計算したい」といった悩みはありませんか？
この記事では、Excel初心者の方でも理解できるように、AGGREGATE関数の基本的な使い方から応用例まで、わかりやすく解説します。この関数をマスターすれば、エラーや非表示行に悩まされず、日々の事務作業を効率化できます。
AGGREGATE関数とは：エラーや非表示に強い集計関数
AGGREGATE関数は、Excel 2010以降で使えるようになった比較的新しい関数です。一言でいえば、SUM関数やAVERAGE関数など複数の集計方法を一つにまとめ、さらにエラー処理や非表示セルの無視といった高度なオプションを指定できる、多機能な集計関数です。 この関数の最大の特徴は、計算範囲に含まれるエラー値（例: #N/A, #DIV/0!）や、フィルターなどで非表示になっている行を無視して集計できる点です。 例えば、VLOOKUP関数でエラーが出ているセルを含んだまま合計を出したい、フィルターで絞り込んだ結果だけを平均したい、といった場合に非常に役立ちます。
AGGREGATE関数の基本的な使い方（構文とオプション）
AGGREGATE関数には、引数の数が異なる2つの形式があります。
形式1：基本的な集計（SUM, AVERAGE, MAX, MINなど）
=AGGREGATE(関数番号, オプション, 範囲1, [範囲2], ...)形式2：特定の順位の値を取得（LARGE, SMALL）
=AGGREGATE(関数番号(14か15), オプション, 範囲, 順位)各引数の解説:
- 関数番号: どの集計方法を使うかを指定する1から19の番号です。（例: 9はSUM、1はAVERAGE）
- オプション: エラー値や非表示セルなどをどのように扱うかを指定する0から7の番号です。（詳細は後述）
- 範囲1, [範囲2], ...: 集計したいデータのセル範囲です。形式1の場合、複数指定できます。
- 順位: 関数番号が14 (LARGE) または 15 (SMALL) の場合にのみ指定します。何番目に大きい（小さい）値を取得するかを数値で指定します（例: 1, 2, 3...）。配列 {1,2,3} のように指定すると、複数の順位の値を一度に取得できます（スピル機能）。
関数番号(第1引数):
| 関数番号 | 意味 | 同等の関数 |
|---|---|---|
| 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 | 上位n番目の値 | LARGE |
| 15 | 下位n番目の値 | SMALL |
| 16 | パーセンタイル(0-100) | PERCENTILE.INC |
| 17 | 四分位数(0-100) | QUARTILE.INC |
| 18 | パーセンタイル(1-99) | PERCENTILE.EXC |
| 19 | 四分位数(1-99) | QUARTILE.EXC |
オプション(第2引数):
| オプション | 非表示の行 | エラー値 | ネストされた SUBTOTAL関数と AGGREGATE関数 |
|---|---|---|---|
| 0か省略 | 集計対象 | 集計対象 | 無視 |
| 1 | 無視 | 集計対象 | 無視 |
| 2 | 集計対象 | 無視 | 無視 |
| 3 | 無視 | 無視 | 無視 |
| 4 | 集計対象 | 集計対象 | 集計対象 |
| 5 | 無視 | 集計対象 | 集計対象 |
| 6 | 集計対象 | 無視 | 集計対象 |
| 7 | 無視 | 無視 | 集計対象 |
使用頻度が高いのは、6（エラー値のみ無視）、7（非表示行とエラー値を無視）です。
- 「ネストされたSUBTOTAL関数とAGGREGATE関数」とは: 集計範囲内にSUBTOTAL関数やAGGREGATE関数が含まれる場合に、その結果を集計に含めるか無視するかを指定します。
- よく使うオプション: 6（エラー値のみ無視）、7（非表示行とエラー値を無視）がよく使われます。
範囲1, [範囲2], ... （集計対象のデータ範囲）(第3～引数):
この引数には、合計、平均、最大値などを計算したい数値データが含まれるセル範囲を指定します
- 最低1つの範囲（範囲1）を指定する必要があります。
- 必要に応じて、2つ目以降の範囲（[範囲2], [範囲3], ...）をカンマ（,）で区切って複数指定できます。これにより、離れた場所にある複数の範囲を一度に集計できます。
- 例1（単一範囲）: =AGGREGATE(9, 5, C2:C6) → C2からC6までの範囲を集計します。（下記画像の数式はC2:C3ですが、文脈に合わせて調整ください）
- 例2（複数範囲）: =AGGREGATE(9, 5, C2:C3, C4:C5, C6) → C2:C3、C4:C5、C6という複数の範囲をまとめて集計します。（下記画像参照）
※関数番号14（LARGE）と15（SMALL）の場合は、集計対象となる範囲を1つだけ、第3引数として指定します。複数の範囲を指定することはできません。この範囲の指定の後に、第4引数として 順位 を指定します。
順位(第4引数): ※関数番号14（LARGE）と15（SMALL）のみ
関数番号14（LARGE）と15（SMALL）は4つ目の引数に順位を指定します。
順位には何番目の値を取得するかを指定します。
上から2番目の値を求める例。
=AGGREGATE(14,5,C2:C6,2)
下から2番目の値を求める例。
=AGGREGATE(15,5,C2:C6,2)
順位を配列で指定すると複数の順位を取得します。
配列は{}で囲み,で区切ります。
上から1と2と5番目の値を求める例。
=AGGREGATE(14,5,C2:C6,{1,2,5})結果はスピルになり、右のセルに拡張されます。
AGGREGATE関数と他の集計関数（SUM, SUBTOTAL）との違い
SUM関数などとの比較
エラー値を無視して集計する
SUM関数などは範囲内に一つでもエラー値があると結果がエラーになりますが、AGGREGATE関数はオプション6や7などを指定すればエラー値を無視して計算できます。
例えばセルA1からA10に数値があり、その中にエラー値（#DIV/0!など）が含まれている場合：
=AGGREGATE(9, 6, A1:A10)この式の、「9」は合計（SUM）を意味し、「6」はエラー値を無視するオプションです。
もしSUM関数でエラーを無視したい場合はIFERROR関数が必要です。
=SUM(IFERROR(B1:B10,0))使用する関数が少ない分、AGGREGATE関数は数式がシンプルになるメリットがあります。
非表示行を含めずに計算する
SUM関数などは非表示行も常に計算対象としますが、AGGREGATE関数はオプション5や7などを指定すれば非表示行（フィルターや手動での非表示）を無視して計算できます。
非表示にしたデータも含めずに計算したい場合の例：
=AGGREGATE(9, 5, B6:B11)この式の、「9」は合計（SUM）を意味し、「5」は非表示行を無視するオプションです。
この機能により簡素な集計表であれば、ピボットテーブルより効率的に集計値を確認することができます。フィルターでなく、列の高さを0にした場合も無視します。
使い分け
エラー値や非表示行を無視して集計したい場合は、AGGREGATE関数を使いましょう。数式が簡略になるため有効です。
そうでない場合は元の集計関数を利用しましょう。簡略で理解しやすい数式になります。
SUBTOTAL関数との違い
Excelには似た機能を持つSUBTOTAL関数がありますが、AGGREGATE関数はSUBTOTAL関数の改良版と位置づけられます。互換性の問題がない限り、AGGREGATE関数の使用を推奨します。
- 対応する集計方法の種類: SUBTOTALは11種類ですが、AGGREGATEは19種類（LARGE, SMALL, MEDIANなど高度な統計関数を含む）に対応しています。
- エラー処理: SUBTOTALはエラー値を無視できませんが、AGGREGATEはオプションで無視できます。
- 非表示行の扱い: SUBTOTALはフィルターによる非表示行を常に無視しますが（手動非表示は無視しない場合がある）、AGGREGATEはオプションで無視するかどうかを柔軟に選択できます。
- ネストされた関数の扱い: AGGREGATEには、範囲内のSUBTOTAL関数やAGGREGATE関数の結果を無視するオプションがありますが、SUBTOTALにはありません。
| 相違点 | AGGREGATE | SUBTOTAL |
|---|---|---|
| 対応する集計方法の種類 | 19種類(多い) | 11種類(少ない) |
| エラー処理 | オプションで無視できる | 無視できない |
| 非表示行の扱い | オプションで無視するかどうかを柔軟に選択可能 | フィルターによる非表示行を常に無視（手動非表示は無視しない場合がある） |
| ネストされた関数(SUBTOTAL関数やAGGREGATE関数)の扱い | オプションで無視できる | 無視できない |
実践例で学ぶAGGREGATE関数
実際の業務でどのようにAGGREGATE関数を使うと便利なのか、いくつかの実践例を見ていきましょう。
例1: こんな時に便利 - エラー値を含む売上データを集計したい
以下のような売上データがあり、一部のセルに「#DIV/0!」や「#VALUE!」などのエラーが含まれている場合を考えてみましょう。
| 日付 | 商品 | 売上 | 利益率 | 利益額 |
|---|---|---|---|---|
| 4/1 | A商品 | 1,000 | 25% | 250 |
| 4/2 | B商品 | 1,500 | #DIV/0! | #DIV/0! |
| 4/3 | C商品 | #N/A | 30% | #N/A |
| 4/4 | A商品 | 2,000 | 25% | 500 |
売上データに「#DIV/0!」や「#N/A」エラーが含まれていても、オプション6（エラー値を無視）を使えば正常な値だけで合計できます。
- 売上合計: =AGGREGATE(9, 6, C2:C5)
- 利益額合計: =AGGREGATE(9, 6, E2:E5)
数式の C2:C5はC列の売上範囲、E2:E5はE列の利益額範囲です。
この表の「売上」と「利益額」列を合計した結果。
例2 :こんな時に便利 - 部門別の成績上位者（エラー無視）の平均点を出したい
試験の成績表があり、部門別に上位3名の平均点を計算したいとします。
FILTER関数と組み合わせることで、部門ごとのデータを取り出し、オプション6（エラー値を無視）を指定して関数番号14（LARGE）で上位3名の成績を取得し、その平均点をAVERAGE関数で求めます。
=AVERAGE(AGGREGATE(14,6,FILTER($C$2:$C$16,$A$2:$A$16=$E2),{1,2,3}) )
数式の中の 14はLARGE関数、6はエラー値を無視するオプション、$C$2:$C$16は成績データ範囲、$A$2:$A$16=$E2は営業部の条件、{1,2,3}は1位から3位までを取得する指定です。
この式は、C2:C16のセル範囲から部門別に上位3つの値の平均を計算します。
数式の解説
この数式は最初に条件に一致する行を取得するFILTER関数で部門別の成績を取得しています。
=FILTER($C$2:$C$16,$A$2:$A$16=$E2)E列には部門名が入ります。E2は営業部のため、営業部のみの成績リストが取得されます。
次にAGGREGATE関数でエラーを無視しつつ、成績上位3名を取得しています。
=AGGREGATE(14,6,FILTER($C$2:$C$16,$A$2:$A$16=$E2),{1,2,3})
そして最後にAVERAGE関数で平均を計算します。
例3: こんな時に便利 - フィルターや手動で非表示にした行を除外して最小在庫数を知りたい
在庫リストでフィルターをかけたり、行を手動で非表示にしたりした場合でも、オプション7（非表示行とエラー値を無視）と関数番号5（MIN）を使えば、表示されている商品だけの最小在庫数を計算できます。
=AGGREGATE(5,7,D4:D18) D4:D18はD列の在庫数範囲です。
関数番号5（MIN）、オプション7（非表示行とエラーを無視）で、表示されている商品の中の最小在庫数を返します。
下の例では最小の7が数式(D1セル)の結果になります。
フィルターでカテゴリを文房具に絞ると、文房具の中の最小である67が結果になります。
よくある質問
Q1: AGGREGATE関数はどのバージョンのExcelから使えますか？
A1: AGGREGATE関数はExcel 2010以降のバージョンで利用可能です。それ以前のバージョンでは使用できません。古いバージョンをお使いの方は、代わりにSUBTOTAL関数や、通常の集計関数（SUM関数など）とIFERROR関数を組み合わせた方法を検討しましょう。
Q2: AGGREGATE関数で無視されたエラーはどうやって確認できますか？
A2: オプションを変更して（例: オプション4や5）エラーが表示されるか確認する、 条件付き書式でエラーセルを強調表示するなどの方法があります。
Q3: AGGREGATE関数の計算速度は通常の関数と比べてどうですか？
A3: 内部で複雑な処理を行うため、大量のデータを扱う場合、単純なSUM関数などより計算に時間がかかることがあります。 通常規模のデータであれば、速度の違いはほとんど気になりません。
Q4: AGGREGATE関数を使う上での注意点は？
A4: 引数の指定（特に関数番号とオプション）を間違えないように注意が必要です。また、Excel 2010より前のバージョンでは使用できないため、ファイルを共有する相手の環境も考慮すると良いでしょう。配列数式として扱われる場合があるため、意図しない結果にならないか確認することも大切です。
まとめ：AGGREGATE関数でエラーと非表示に強い集計を
AGGREGATE関数は、Excel初心者の方にとって最初は少し複雑に感じるかもしれませんが、エラー値や非表示行を自在に扱えるため、使いこなせると日々の業務効率が大幅に向上します。
AGGREGATE関数のメリット
- エラー値を無視して集計できる
- 非表示セルやフィルター済みの値を柔軟に扱える
- 19種類もの集計方法を1つの関数で実現できる
- 複雑なデータ分析もシンプルな式で可能になる
使いこなすコツ
- よく使う関数番号（9:SUM, 1:AVERAGE, 4:MAX, 5:MIN, 14:LARGE, 15:SMALLなど）を覚える
- オプション番号は6（エラー値のみ無視）、7（非表示行とエラー値を無視）の挙動を理解する
- 実際のデータで試しながら使い方をマスターする
- エラー処理や非表示行の扱いに困ったらAGGREGATE関数を試してみる
この記事を通じて、AGGREGATE関数の基本的な使い方と利便性を理解いただけたでしょうか。エラーによる集計中断や、フィルター適用後の再計算の手間から解放されれば、 事務作業の効率は格段に上がります。ぜひ、明日からの業務でAGGREGATE関数を活用してみてください。
Excel 関数の仕様と使い方の記事一覧
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。