- 更新日 : 2025年8月25日
Excelの隠れた万能選手！AGGREGATE関数でデータ集計を効率化
AGGREGATE関数は、Excelでデータ集計を行う際に便利な関数です。SUMなどの一部の関数では対応が難しいケースにおいても、エラー値を無視して計算したり、非表示行を除外して計算したりといった柔軟な集計ができます。
AGGREGATE関数を活用することで、煩雑なデータの中から必要な情報だけを正確に抽出し、計算ミスやエラーの影響を減らして作業の効率を高めることができます。
AGGREGATE関数の使い方：基本から応用まで
AGGREGATE関数は、以下の書式で使用します。
AGGREGATE(集計方法, オプション, 範囲, [k])
それぞれの引数について詳しく見ていきましょう。
1. 集計方法 (関数番号)
この引数で、どのような集計を行うかを指定します。Excelの様々な集計関数に対応した番号が割り振られています。
| 関数番号 | 集計方法 | 概要 |
|---|---|---|
| 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 | データセットでk番目に大きい値を返します。 |
| 15 | SMALL | データセットでk番目に小さい値を返します。 |
| 16 | PERCENTILE.INC | データセットのkパーセンタイルを返します（0から1の範囲）。 |
| 17 | QUARTILE.INC | データセットの四分位数を返します（0から4の範囲）。 |
| 18 | PERCENTILE.EXC | データセット内の与えられたパーセンタイル (k) に対応する値を返します。ただし、0%パーセンタイルと100%パーセンタイルは計算から除外されます。 |
| 19 | QUARTILE.EXC | データセット内の特定の四分位数に対応する値を返します。ただし、0番目の四分位数（最小値）と4番目の四分位数（最大値）は計算から除外されます。 |
例えば、合計値を計算したい場合は「9」、平均値を計算したい場合は「1」を指定します。
2. オプション (動作設定)
この引数では、計算から除外するデータの種類を指定します。AGGREGATE関数が他の関数と一線を画す最も重要なポイントの一つです。
| オプション番号 | 除外する対象 |
|---|---|
| 0 | ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視 |
| 1 | 非表示行、ネストされた SUBTOTAL 関数、および AGGREGATE 関数を無視 |
| 2 | エラー値、ネストされた SUBTOTAL 関数、および AGGREGATE 関数を無視 |
| 3 | 非表示行、エラー値、ネストされた SUBTOTAL 関数、および AGGREGATE 関数を無視 |
| 4 | 何も無視しない |
| 5 | 非表示行を無視 |
| 6 | エラー値を無視 |
| 7 | 非表示行とエラー値を無視 |
特に「2 (エラー値を無視)」や「5 (非表示行を無視)」、「7 (非表示行とエラー値を無視)」は、実務で非常に役立つオプションです。
3. 範囲 (配列)
集計対象となるセル範囲を指定します。例えば、A1:A10のように指定します。
4. k (省略可能)
これは、LARGE関数（関数番号14）やSMALL関数（関数番号15）など、特定の関数番号を選択した場合にのみ必要となる引数です。例えば、2番目に大きい値を取得したい場合は「2」を指定します。
AGGREGATE関数の使用例
いくつか具体的な例を見てみましょう。
例1：エラー値を無視して合計を出す
データの中に#DIV/0!のようなエラー値が含まれていても、それらを無視して数値の合計を計算したい場合です。
| 数値 |
|---|
| 10 |
| 20 |
| #DIV/0! |
| 30 |
通常のSUM関数ではエラーが返されますが、AGGREGATE関数を使えば、
=AGGREGATE(9, 6, A1:A4)
と入力することで、10 + 20 + 30 = 60という結果が得られます。ここで「9」は合計（SUM）、「6」はエラー値を無視するオプションです。
例2：非表示行を無視して平均を出す
データの中から特定の条件でフィルタリングし、表示されている行のみの平均値を計算したい場合です。
例えば、以下のようなデータがあり、一部の行を非表示にしたとします。
| 商品名 | 売上 |
|---|---|
| りんご | 100 |
| みかん | 150 |
| バナナ | 200 |
| いちご | 120 |
ここで「バナナ」の行を非表示にした状態で、表示されている商品の売上平均を計算するには、
=AGGREGATE(1, 5, B2:B5)
と入力します。「1」は平均（AVERAGE）、「5」は非表示行を無視するオプションです。これにより、表示されている「りんご」「みかん」「いちご」の平均値が計算されます。
AGGREGATE関数の利用シーン
AGGREGATE関数は、以下のような様々なシーンでその真価を発揮します。
1. データにエラー値が含まれる場合の集計
集計対象のデータに、計算式のエラー（#DIV/0!, #N/A, #VALUE!など）が混在していることはよくあります。これらのエラー値があると、通常のSUMやAVERAGEなどの関数はエラーを返してしまい、正確な集計ができません。
AGGREGATE関数であれば、オプションで「エラー値を無視する」を選択することで、適切な設定を行えばエラー値があってもスムーズに集計を行えます。これにより、一部のエラー値を除外する設定ができるため、前処理の手間を軽減しつつ、必要な情報を迅速に得られます。
2. フィルタリングされたデータの集計
Excelのオートフィルター機能を使ってデータを絞り込んだ際、表示されているデータのみを対象に集計したいケースは多々あります。SUBTOTAL関数もこの用途で使われますが、AGGREGATE関数は関数番号が19種類、除外オプションも8種類と、より柔軟な集計機能を備えています。
例えば、特定の顧客からの売上データのみを表示して、その合計や平均を計算するといった場合に非常に便利です。非表示行を無視するオプションを使うことで、フィルターをかけるだけで集計結果が自動的に更新されます。
3. 非表示の行や列を除外した集計
手動で非表示にした行や列を、集計対象から除外したい場合にもAGGREGATE関数が役立ちます。例えば、特定の時期のデータや、機密性の高い情報を含む行を一時的に非表示にしておき、その状態のまま残りのデータで集計を行いたい場合に利用できます。
4. トップNまたはワーストNの抽出
LARGE関数やSMALL関数をAGGREGATE関数内で利用することで、データの中から上位（または下位）の値を簡単に抽出できます。例えば、売上上位3位の商品を特定したり、試験の点数で下位5位の生徒を見つけ出したりする際に活用できます。
AGGREGATE関数の応用
AGGREGATE関数は、単独で使うだけでなく、他の関数と組み合わせることでさらに高度なデータ分析に活用できます。
1. 条件付き書式設定との連携
AGGREGATE関数を使って特定の条件を満たすデータを抽出し、その結果に基づいて条件付き書式を設定することが可能です。例えば、非表示行を除外した上で、特定の閾値を超える数値に色を付けるといった表現ができます。
2. 配列数式としての利用（ただし注意が必要）
AGGREGATE関数は、通常の引数として配列を受け取ることが可能ですが、複雑な条件指定を内部で行う場合は注意が必要です。多くの場合、SUMIFSやAVERAGEIFSなどの複数条件に対応した関数や、FILTER関数と組み合わせる方がシンプルに記述できます。
しかし、エラー値や非表示行の扱いを厳密に制御したい場合に、AGGREGATE関数とIF関数などを組み合わせた配列数式を検討することも可能です。この場合、CTRL + SHIFT + ENTERでの入力は不要です。
3. ダッシュボード作成への活用
動的なダッシュボードを作成する際、AGGREGATE関数は非表示やエラーを制御した集計ができるため便利です。例えば、ドロップダウンリストで選択した条件に基づいて、表示されるデータセットを集計し、その結果をリアルタイムでダッシュボードに反映させることができます。ユーザーがフィルターを適用したり、特定の項目を非表示にしたりしても、常に正確な集計結果を提供できます。
AGGREGATE関数のよくあるエラーと対策
AGGREGATE関数は非常に便利ですが、使い方を誤るとエラーが発生することもあります。ここでは、よくあるエラーとその対策について解説します。
1. #VALUE!エラー
このエラーは、主に以下のケースで発生します。
- 関数番号が正しくない場合：
1～19以外の数値を指定すると発生します。指定可能な関数番号を確認し、正しい数値を入力してください。 - オプション番号が正しくない場合:
0～7以外の数値を指定すると発生します。指定可能なオプション番号を確認し、正しい数値を入力してください。 - 引数の型が間違っている場合：
範囲に文字列など、集計できない型のデータが含まれていて、それが原因で計算できない場合があります。この場合は、データ型が適切であるかを確認するか、オプションを「エラー値を無視する（6または7）」に設定して対応します。 - k引数が不足している、または不要な場合：
LARGEやSMALLなどk引数が必要な関数番号を選択しているにもかかわらずkを省略した場合や、kが不要な関数番号でkを指定した場合に発生することがあります。関数番号に応じてkの要不要を確認しましょう。
2. #DIV/0!エラー
このエラーは、主に以下のケースで発生します。
- 集計対象のデータがない場合：
AVERAGEやCOUNTなど、データを元に計算する関数で、集計対象となる数値が一つもない場合に発生します。例えば、フィルターをかけた結果、表示される数値が全くない状態などが考えられます。 - 計算の分母がゼロになる場合：
平均値などを求める際に、計算の分母がゼロになった場合に発生します。
- 集計範囲にデータが正しく含まれているか確認してください。
- もし意図的にデータがない状態でのエラーを避けたい場合は、IFERROR関数と組み合わせてエラー時に代替値を表示させるなどの対策も有効です。
=IFERROR(AGGREGATE(1, 7, A1:A10), “データなし”)
上記のように記述することで、エラーが発生した場合に「データなし」と表示させることができます。
3. 期待と異なる結果が返される
エラーは発生しないものの、計算結果が期待と異なる場合があります。
- オプションの選択ミス：
例えば、非表示行を含めて計算してほしいのに「非表示行を無視する」オプション（1, 3, 5, 7）を選択している場合などです。集計の目的に合わせて最適なオプションを選択しているか再確認してください。 - 範囲の指定ミス：
集計対象としたい範囲が正しく指定されていない場合があります。数式バーで範囲をハイライト表示させ、意図した範囲が選択されているか確認しましょう。
AGGREGATE関数は、引数の選択肢が多いため、最初は戸惑うかもしれません。しかし、それぞれの引数の意味を理解し、目的と合わせて適切に選択することで、データ集計の幅が大きく広がります。
※ 掲載している情報は記事更新時点のものです。
※本サイトは、法律的またはその他のアドバイスの提供を目的としたものではありません。当社は本サイトの記載内容（テンプレートを含む）の正確性、妥当性の確保に努めておりますが、ご利用にあたっては、個別の事情を適宜専門家にご相談いただくなど、ご自身の判断でご利用ください。
関連記事
エクセルで数字を連続で入力する方法まとめ
エクセルはビジネスシーンにおいて、データ管理や集計に欠かせないツールですが、数字を連続して入力する手間に悩むことも多いでしょう。本記事では、エクセルで効率よく数字を連続で入力する方法をまとめました。オートフィル機能や関数を利用する方法など、…
詳しくみるエクセルで斜線を入れる方法・消す方法まとめ
エクセルを利用していると、時には斜線を使って情報を整理したい場合があります。斜線は、特定のデータを強調したり、セルの内容を分けたりするのに適した方法です。本記事では、エクセルで斜線を入れる方法や消す方法、さらには複数のセルに斜線を加えたり、…
詳しくみるXMATCH関数の使い方：高度な検索を簡単に実現する方法
XMATCH関数は、指定した値がリストや表の中で何番目にあるかを調べるExcelの検索関数です。従来のMATCH関数と似ていますが、部分一致やワイルドカード、後ろからの検索など、より柔軟な検索機能が追加されています。 大規模なデータでも正確…
詳しくみるSTDEV.S関数の使い方：標本データから母集団の標準偏差を推定する方法
STDEV.S関数は、標本データから母集団全体の標準偏差を推定する統計関数です。品質管理での製品のばらつき評価、投資リスクの測定、顧客満足度調査の分析、製造工程の安定性評価など、サンプルデータから全体の変動性を推定する場面で活用されます。例…
詳しくみるDSUM関数の使い方：条件に一致するデータの合計を計算する方法
DSUM関数は、データベース形式の表から指定した条件に一致するレコードの合計を計算する関数です。売上データの条件付き集計、在庫管理での特定商品の合計、経費精算での部門別集計など、複雑な条件での合計計算が必要な場面で活用されます。 例えば、特…
詳しくみるエクセルの相対参照・絶対参照・複合参照の使い分けをわかりやすく解説
エクセルを使いこなすためには、相対参照、絶対参照、複合参照といったセル参照の使い分けが重要です。それぞれの参照には明確な特徴があり、データを効率よく扱うための必須知識となります。相対参照はセルの位置に基づいて変化し、絶対参照は特定のセルを常…
詳しくみる