aggregate関数

Excel関数リファレンス

AGGREGATE 関数

適用対象: Microsoft Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses

 

リストまたはデータベースの集計値を返します。

AGGREGATE 関数を使用して、条件付き書式の制限に対応できます。計算範囲内にエラーがあると、データ バー、アイコン セット、およびカラー スケールに条件付き書式を表示できないという制限があります。これは、計算範囲内にエラーがある場合、MIN、MAX、および PERCENTILE の各関数が計算されないためです。また、LARGE、SMALL、および STDEVP の各関数も、同じ理由によっていくつかの条件付き書式ルールの該当する機能に影響を与えます。AGGREGATE 関数を使用するとエラーが無視されるため、これらの関数を実装することができます。さらに、AGGREGATE 関数では、非表示の行やエラー値を無視するオプションを使用して、リストやデータベースにさまざまな集計関数を適用することもできます。

書式

セル範囲形式

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

配列形式

AGGREGATE(集計方法, オプション, 配列, [k])

AGGREGATE 関数の書式には、次の引数があります。

• 集計方法     必ず指定します。使用する関数を指定する 1 ～ 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
15	SMALL
16	PERCENTILE.INC
17	QUARTILE.INC
18	PERCENTILE.EXC
19	QUARTILE.EXC

• オプション     必ず指定します。関数の検証範囲内の無視する値を指定する数値です。

オプション	動作
0 または省略	ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
1	非表示の行、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
2	エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
3	非表示の行、エラー値、ネストされた SUBTOTAL 関数と AGGREGATE 関数を無視します。
4	何も無視しません。
5	非表示の行を無視します。
6	エラー値を無視します。
7	非表示の行とエラー値を無視します。

• 参照 1     必ず指定します。集計値を求めるために複数の数値引数を受け取る関数の、最初の数値引数です。
• 参照 2、...     省略可能です。集計値を求めるための数値引数 2 ～ 253 を指定します。

配列を受け取る関数の場合、配列、配列数式、または集計値を求めるセル範囲の参照を参照 1 に指定します。参照 2 は、特定の関数に必要な 2 番目の引数を指定します。次の関数には引数の参照 2 が必要です。

関数
LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)

解説

集計方法

• ワークシートのセルに AGGREGATE 関数を入力するときに引数 function_num を入力するとすぐに、引数として使用できるすべての関数の一覧が表示されます。

エラー:

• 2 番目の参照引数を指定する必要があるときにこの引数が指定されていない場合、エラー値 #VALUE! が返されます。
• 1 つ以上の参照が 3-D 参照のとき、エラー値 #VALUE! が返されます。

ネストされた集計:

• 参照 1、参照 2、… の中に別の AGGREGATE 関数がある場合 (つまり、ネストされた AGGREGATE) がある場合、これらのネストされた AGGREGATE 関数は計算の重複を防ぐために無視されます。
• AGGREGATE 関数の参照引数に SUBTOTAL 関数が含まれている場合、これらの SUBTOTAL 関数は無視されます。
• AGGREGATE 関数が SUBTOTAL 関数内にある場合、これらの AGGREGATE 関数は無視されます。

範囲の種類:

• AGGREGATE 関数はデータ列つまり縦方向の範囲を対象としています。データ行つまり横方向の範囲には適していません。たとえば、AGGREGATE(1, 1, 参照 1) などのようにオプション 1 を使用して横方向の範囲を集計する場合、非表示の列は無視され、合計値に集計されません。一方、縦方向の範囲を集計する場合は非表示の行が集計されます。

使用例

使用例 1

使用例を新規のワークシートにコピーすると、計算結果を確認できます。

その方法は?

• この記事の使用例を選択してください。

 重要   行見出しまたは列見出しは選択しないでください。

ヘルプから使用例を選択する

• Ctrl キーを押しながら C キーを押します。
• Excel で、新しいブックまたはワークシートを作成します。
• ワークシートのセル A1 を選択し、Ctrl キーを押しながら V キーを押します。

 重要   使用例が正しく機能するには、ワークシートのセル A1 に貼り付ける必要があります。

• 計算結果と結果を返す数式の表示を切り替えるには、Ctrl キーと Shift キーを押しながら ` (アクサン グラーブ) キーを押すか、[数式] タブの [ワークシート分析] グループで [数式の表示] をクリックします。

使用例を空のワークシートにコピーした後、必要に応じて変更できます。

A B #DIV/0! 82 72 65 30 95 #NUM! 63 31 53 96 71 32 55 81 83 33 100 53 91 34 89 数式 説明 (計算結果) =AGGREGATE(4, 6, A1:A11) 指定したセル範囲内のエラー値を無視して最大値を求めます (96)。 =AGGREGATE(14, 6, A1:A11, 3) 指定したセル範囲内のエラー値を無視して 3 番目に大きい値を求めます (72)。 =AGGREGATE(15, 6, A1:A11) エラー値 #VALUE! を返します。これは、関数 (SMALL) に 2 番目の参照引数が必要で、AGGREGATE にその引数を指定する必要があるためです。 =AGGREGATE(12, 6, A1:A11, B1:B11) 指定したセル範囲内のエラー値を無視してメジアンを求めます (68)。 =MAX(A1:A2) 評価対象の範囲内にエラー値があるため、エラー値を返します (#DIV/0!)。

使用例 2

使用例を新規のワークシートにコピーすると、計算結果を確認できます。

その方法は?

• この記事の使用例を選択してください。

 重要   行見出しまたは列見出しは選択しないでください。

ヘルプから使用例を選択する

• Ctrl キーを押しながら C キーを押します。
• Excel で、新しいブックまたはワークシートを作成します。
• ワークシートのセル A1 を選択し、Ctrl キーを押しながら V キーを押します。

 重要   使用例が正しく機能するには、ワークシートのセル A1 に貼り付ける必要があります。

• 計算結果と結果を返す数式の表示を切り替えるには、Ctrl キーと Shift キーを押しながら ` (アクサン グラーブ) キーを押すか、[数式] タブの [ワークシート分析] グループで [数式の表示] をクリックします。

使用例を空のワークシートにコピーした後、必要に応じて変更できます。

A B x y 96 82 72 65 30 95 56 63 31 53 98 71 32 55 81 83 33 100 53 91 34 89 数式 説明 (計算結果) =AGGREGATE(4, 5, A1:A11) 指定したセル範囲内のエラー値を無視して最大値を求めます (98)。 =AGGREGATE(14, 5, A1:A11, 3) 指定したセル範囲内のエラー値を無視して 3 番目に大きい値を求めます (81)。 =AGGREGATE(18, 5, A1:A11) エラー値 #VALUE! を返します。これは、関数 (PERCENTILE) に 2 番目の参照引数が必要で、AGGREGATE にその引数を指定する必要があるためです。 =AGGREGATE(12, 5, A1:A11, B1:B11) 指定したセル範囲内のエラー値を無視してメジアンを求めます (68)。