【Excel】AGGREGATE関数－リストやデータベースの集計値を返す関数－

約～分
（約文字）

2020-10-13

【Excel】AGGREGATE関数－リストやデータベースの集計値を返す関数－

AGGREGATE関数とは

エクセルの「AGGREGATE関数」はリストやデータベースの集計値を返す関数です。なかでも、エラー値や非表示の行を無視して集計できるのが最大の特徴です。

たとえば、SUM関数やAVERAGE関数は、集計したい範囲に「#N/A」などのエラー値が1つでも入っていると、結果もエラーになってしまいます。AGGREGATE関数なら「エラー値を無視する」というオプションを指定できるため、エラーが混ざった表でも合計や平均をきちんと求められます。

• このページで分かること
• AGGREGATE関数でできることと基本の使いどころ
• 構文と引数の意味
• AGGREGATE関数の使用例

非表示の行やエラー値を無視するオプションを使い、リストやデータベースにさまざまな集計をすることが可能です。

構文と引数

• 365
• 2019
• 2016
• 2013
• 2010

AGGREGATE関数は、「合計」「平均」「最大値」など19種類の集計を1つの関数で切り替えられる関数です。「非表示の行」や「エラー値」を無視して集計できるのが大きな特徴です。引数の組み立て方は、集計方法の番号によって参照形式（1～13）と配列形式（14～19）の2つに分かれます。

=AGGREGATE(集計方法,オプション,範囲1,[範囲2],…)

参照形式（1～13）と配列形式（14～19）の違い

集計方法が1～13のときは、3つ目以降の引数に集計したい範囲を並べて指定します（参照形式）。集計方法が14～19のときは、3つ目の引数に「配列（範囲）」、4つ目の引数に「順位や率などの数値」を指定します（配列形式）。14～19では範囲を複数並べることはできません。

配列形式の4つ目の引数「k」は、集計方法によって意味が変わります。LARGE・SMALL（14・15）では「何番目か」、PERCENTILE（16・18）では「0～1の割合」、QUARTILE（17・19）では「四分位数の種類（0～4）」を指定します。下の表の「範囲2の指定が必要」列に書かれている括弧は、この4つ目の引数の内容を表しています。

集計方法を選ぶときの補足です。

• 14（LARGE）は「大きい方からk番目の値」、15（SMALL）は「小さい方からk番目の値」を返します。たとえば =AGGREGATE(14,6,B2:B10,1) はエラーを無視した最大値、=AGGREGATE(15,6,B2:B10,3) は小さい方から3番目の値になります。
• 16・18のkには0～1の割合（例：0.9なら上位10%の境目）、17・19のkには0～4の数字（0＝最小値、1＝第1四分位、2＝中央値、3＝第3四分位、4＝最大値）を指定します。
• 7と8、10と11は、母集団の一部を調べるのか（不偏）、全体を調べるのか（母集団）で使い分けます。迷ったら元になる関数名（STDEV.S／STDEV.P、VAR.S／VAR.P）を確認してください。

オプションの選び方

オプションは0～7の8種類ですが、「非表示の行を無視するか」「エラー値を無視するか」「入れ子（ネスト）の小計を無視するか」の3つの組み合わせで決まります。

実務でよく使うのは、エラーを無視する 6 と、非表示行とエラーの両方を無視する 7 です。たとえば =AGGREGATE(9,6,B2:B10) は、B2:B10にエラー値が混ざっていてもエラーにならず、数値だけの合計を返します。SUM関数ではエラーが1つあるだけで結果もエラーになるため、この点がAGGREGATE関数の便利なところです。

なお、「非表示の行を無視」できるのは行の非表示（フィルターで絞り込んだ行を含む）だけです。列を非表示にしても、集計からは除かれません。また、配列形式（14～19）でも同じオプションが使えます。

使用例

B2:B10に売上が入力されていて、一部にエラー値があり、一部の行を非表示にしている場合の例です。

集計方法には、引数ごとに番号を覚えなくても、数式を入力するときに候補が表示されます。慣れないうちは、上の表を見ながら「合計は9、平均は1、最大値は4、最小値は5」の4つを覚えておくと便利です。

AGGREGATE関数の解説

• ・ワークシートのセルにAGGREGATE関数を入力するときに集計方法の引数を入力すると、引数として使用できるすべての関数の一覧が表示されます。
• ・2番目の参照引数を指定する必要があるときにこの引数が指定されていない場合、エラー値#VALUE!が返されます。
• ・1つ以上の参照が3-D参照のとき、エラー値#VALUE!が返されます。
• ・AGGREGATE関数では、列のデータ（縦方向の範囲）が集計されます。 行のデータ（横方向の範囲）を集計するための関数ではありません。例えば、AGGREGATE(1,1,範囲1)のようにオプション1を使用し横方向の範囲を集計する場合、いずれかの列を非表示にしても合計値は変わりません。縦方向の範囲を集計する場合は、いずれかの行を非表示にすると集計に影響します。

出典：Microsoft - Office サポート

上の注意点だけでは分かりにくいので、AGGREGATE関数でつまずきやすいポイントを、具体例つきで順番に整理します。

AGGREGATE関数の2つの書き方（参照形式と配列形式）

AGGREGATE関数には、集計方法によって2つの書き方があります。

• 参照形式：=AGGREGATE(集計方法, オプション, 参照1, 参照2, …)（集計方法 1〜13）
• 配列形式：=AGGREGATE(集計方法, オプション, 配列, k)（集計方法 14〜19）

集計方法の番号と、対応する関数は次のとおりです。

• 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　17：QUARTILE.INC　18：PERCENTILE.EXC　19：QUARTILE.EXC

オプション（第2引数）の意味

第2引数のオプションは、「どんなデータを無視して集計するか」を決める番号です。よく使うのは次の3つです。

• 4：何も無視しない
• 5：非表示の行を無視する
• 6：エラー値を無視する
• 7：非表示の行とエラー値の両方を無視する

0〜3は、上記に加えて「範囲の中にあるSUBTOTAL関数・AGGREGATE関数の結果を無視する」動作が加わります（0は入れ子のみ、1は非表示行＋入れ子、2はエラー＋入れ子、3は非表示行＋エラー＋入れ子）。小計行を含む表の総合計を二重計上せずに出したいときに役立ちます。

SUBTOTAL関数との違い

AGGREGATE関数は、SUBTOTAL関数の上位互換のような関数です。主な違いは次のとおりです。

• エラー値を無視できる：SUBTOTAL関数は範囲にエラーが1つでもあるとエラーになりますが、AGGREGATE関数はオプション6または7でエラーを飛ばして集計できます。
• 使える集計方法が多い：SUBTOTAL関数は11種類、AGGREGATE関数は19種類です（中央値、LARGE、SMALL、PERCENTILE、QUARTILEなどが追加で使えます）。
• 非表示の扱いを選べる：SUBTOTAL関数は「9」と「109」で切り替えますが、AGGREGATE関数はオプションで細かく指定できます。

たとえば、B2:B10に数値とエラー（#DIV/0!など）が混ざっているとします。

• =SUBTOTAL(9,B2:B10) → エラーが返される
• =AGGREGATE(9,6,B2:B10) → エラーを除いた合計が返される

なお、フィルターで絞り込んだ結果だけを合計したい場合は、SUBTOTAL関数でもAGGREGATE関数でも、非表示を無視する設定（SUBTOTALは109、AGGREGATEはオプション5または7）にすれば同じ結果になります。

集計方法14〜19は「配列形式」：範囲に配列を渡せる

集計方法14〜19（LARGE、SMALL、PERCENTILE、QUARTILEの仲間）は配列形式になり、第3引数に「セル範囲」だけでなく、計算式で作った配列も渡せます。ここがAGGREGATE関数の強力なところです。

例：A2:A10に売上、B2:B10に支店名があるとき、「東京」の売上だけで最大値を求める式です。

• =AGGREGATE(14,6,A2:A10/(B2:B10="東京"),1)

この式では、支店が「東京」の行は A2÷1 で元の売上のまま、それ以外の行は A2÷0 となり#DIV/0!エラーになります。オプション6で「エラーを無視」するため、東京の売上だけが残り、その中で1番大きい値（k=1）が返ります。IF関数を使った配列数式と違い、Ctrl+Shift+Enterを押さなくても計算できるのが利点です。

注意：集計方法1〜13（SUMやAVERAGEなど）の参照形式では、第3引数にこのような計算式の配列を渡すことはできません。配列を渡したいときは、集計方法14〜19を使います。

第4引数のkが必要な関数と、省略するとどうなるか

第4引数の「k」が必要なのは、集計方法14〜19だけです。kの意味は関数ごとに異なります。

• 14（LARGE）／15（SMALL）：k番目の順位。=AGGREGATE(14,6,A2:A10,2) は2番目に大きい値です。
• 16（PERCENTILE.INC）／18（PERCENTILE.EXC）：0〜1のパーセント位置。=AGGREGATE(16,6,A2:A10,0.9) は上位10％の境目の値です。
• 17（QUARTILE.INC）／19（QUARTILE.EXC）：四分位数の番号。=AGGREGATE(17,6,A2:A10,1) は第1四分位数です。

14〜19でkを省略すると、エラー値#VALUE!が返されます。逆に、集計方法1〜13ではkは不要なので、=AGGREGATE(9,6,A2:A10) のように参照だけを指定します。

よくあるエラーと対処

• 集計方法やオプションが範囲外の番号（例：集計方法20、オプション8）：#VALUE!になります。番号を確認してください。
• 集計方法1〜13で複数の範囲を指定したら別シートにまたがる3-D参照になっていた：#VALUE!になります。範囲は同じシート内で指定してください。
• 横方向の範囲で、列を非表示にしても結果が変わらない：AGGREGATE関数が無視するのは「非表示の行」だけで、非表示の列は対象外だからです。横方向のデータには向きません。
• 範囲内の全データがエラーや非表示で無視された：集計できるデータが残らないため、エラーになることがあります。IFERROR関数で囲んで空白や0を表示する方法も使えます。

迷ったときは、「エラーを飛ばして合計したい」なら =AGGREGATE(9,6,範囲)、「非表示行とエラーを両方飛ばしたい」なら =AGGREGATE(9,7,範囲) を基本の形として覚えておくと便利です。

AGGREGATE関数の使用例

上のデータに対する、エラー値を無視して最大値を求めます。

=AGGREGATE(4, 6, A1:A11)

=96

上のデータに対する、エラー値を無視して 3 番目に大きい値を求めます。

=AGGREGATE(14, 6, A1:A11, 3)

=72

上のデータに対する、エラー値を無視してメジアンを求めます。

=AGGREGATE(12, 6, A1:A11, B1:B11)

=68

関連する関数

• SUBTOTAL関数：リストやデータベースの集計値を返す関数
• SUMIF関数：条件に一致する数値を合計する関数
• AVERAGEIF関数：検索条件に一致する平均値（算術平均）を返す関数

よくある質問

Q. AGGREGATE関数は何をするときに使いますか？

AGGREGATE関数は、リストやデータベースの集計値を返す関数です。 とくに SUBTOTAL関数 など近い関数との違いを合わせて押さえると、使い分けしやすくなります。

Q. AGGREGATE関数がうまく動かないときは何を確認すればよいですか？

まず 集計方法、オプション の指定方法を見直し、数値の単位、丸め方、引数の範囲 を確認してください。

よくある失敗例・エラー対処

AGGREGATE関数で期待通りの結果にならないときは、次の点を確認してください。

• 集計方法、オプション の指定が関数の仕様どおりか確認する
• 丸めや桁数の指定が意図どおりか確認する
• 近い関数との違いを取り違えていないか確認する

まとめ

このページでは、AGGREGATE関数について以下を確認しました。

• ・AGGREGATE関数でできることと基本の使いどころ
• ・構文と引数の意味
• ・AGGREGATE関数の使用例

前の記事

【Excel関数】Excel 関数 一覧表（アルファベット別）

次の記事

Excel 関数 一覧表（機能別）