- 公開日：
- 更新日：
ExcelのAGGREGATE関数の使い方｜リストやDBの集計値を返す
AGGREGATE関数は「集計方法」と呼ばれる関数や「オプション」と呼ばれる除外条件を組み合わせて、計19種類の集計を行うことができる関数です。
この関数を使えば、エラー表示や非表示を無視して計算を行ったり、指定した順位の値をもとめたりすることができます。
AGGREGATE関数は、大きく2種類に分かれます。
- 「集計方法,オプション,範囲1（範囲2,・・・）」の形式で入力する集計方法1~13の場合
- 「集計方法,オプション,配列,集計方法の第2引数」の形式で入力する集計方法14~19の場合
以下より、よく利用される2つの事例を取り上げてご紹介していきます。1つ目は集計方法9を利用してエラー値を省く事例、2つ目は集計方法14を利用して指定した順位を降順にする事例です。
集計方法
AGGREGATE関数では、下の表から数値を1つ選択して集計の関数を使うことができます。
| 集計方法 | 関数 | 集計内容 |
|---|---|---|
| 1 | AVERAGE | 平均 |
| 2 | COUNT | 数値の個数 |
| 3 | COUNTA | 空白以外の個数 |
| 4 | MAX | 最大値 | 5 | MIN | 最小値 |
| 6 | PRODUCT | 掛け算 |
| 7 | STDEV.S | 標本標準偏差 |
| 8 | STDEV.P | 標準偏差 |
| 9 | SUM | 合計 |
| 10 | VAR. | 不偏分散 |
| 11 | VAR.P | 分散 |
| 12 | MEDIA | 中央値 |
| 13 | MODE.SNG | 最頻値 |
| 14 | LARGE | 降順の値 |
| 15 | SMALL | 昇順の値 |
| 16 | PERCENTILE.INC | 百分位数 |
| 17 | QUARTILE.INC | 四分位数 |
| 18 | PERCENTILE.EXC | 10～90の百分位数 |
| 19 | QUARTILE.EXC | 第2、第3の四分位数 |
オプション
オプションとは、エラー値などの不要なデータを無視して集計することをいいます。AGGREGATE関数では、下の表から数値を1つ決めて除外条件を適用させることができます。
| オプション | 内容 |
|---|---|
| 0 | 指定する範囲内にSUBTOTAL関数やAGGREGATE関数 がある場合は、これらの集計値を無視します。 |
| 1 | オプション「0」のほか、非表示行を無視します。 |
| 2 | オプション「0」のほか、エラー値を無視します。 |
| 3 | オプション「0」、「1」、「2」のすべてを含みます。 |
| 4 | 何も無視しません。 | 5 | 非表示の行を無視します。 |
| 6 | エラー値を無視します。 |
| 7 | 非表示の行とエラー値を無視します。 |
書式/使用例
19種類の集計を行います。
=AGGREGATE（集計方法,オプション,範囲1（範囲2,・・・））【集計方法1~13の場合】
=AGGREGATE（集計方法,オプション,配列,集計方法の第2引数）【集計方法14~19の場合】
| 集計方法 | A1セル | A2セル | A3セル | 関数式 | 結果 |
|---|---|---|---|---|---|
| 1 | 50 | エラー | 100 | =AGGREGATE(1,6,A1:A3) | 75 |
| 2 | 50 | エラー | 100 | =AGGREGATE(2,6,A1:A3) | 2 |
| 3 | 50 | 文字列 | 100 | =AGGREGATE(3,6,A1:A3) | 3 |
| 4 | 50 | エラー | 100 | =AGGREGATE(4,6,A1:A3) | 100 |
| 5 | 50 | エラー | 100 | =AGGREGATE(5,6,A1:A3) | 50 |
| 6 | 50 | エラー | 100 | =AGGREGATE(6,6,A1:A3) | 5000 |
| 7 | 50 | エラー | 100 | =AGGREGATE(7,6,A1:A3) | 35.3... | 8 | 50 | エラー | 100 | =AGGREGATE(8,6,A1:A3) | 25 |
| 9 | 50 | エラー | 100 | =AGGREGATE(9,6,A1:A3) | 150 |
| 10 | 50 | エラー | 100 | =AGGREGATE(10,6,A1:A3) | 1250 |
| 11 | 50 | エラー | 100 | =AGGREGATE(11,6,A1:A3) | 625 |
| 12 | 50 | エラー | 100 | =AGGREGATE(12,6,A1:A3) | 75 |
| 13 | 50 | 100 | 100 | =AGGREGATE(13,6,A1:A3) | 100 |
| 14 | 50 | 100 | 1 | =AGGREGATE(14,3,$A$1:$A$2,A3) | 100 |
| 15 | 50 | 100 | 1 | =AGGREGATE(15,3,$A$1:$A$2,A3) | 50 |
| 16 | 50 | 100 | 1 | =AGGREGATE(16,3,$A$1:$A$2,A3) | 100 |
| 17 | 50 | 100 | 1 | =AGGREGATE(17,3,$A$1:$A$2,A3) | 62.5 |
| 18 | 50 | 100 | 1 | =AGGREGATE(18,3,$A$1:$A$2,A3) | エラー |
| 19 | 50 | 100 | 1 | =AGGREGATE(19,3,$A$1:$A$2,A3) | エラー |
集計方法9を利用してエラー値を省いて集計する方法
野菜の入荷状況です。表の中にはエラー値がいくつか含まれていますが、今からAGGREGATE関数を使ってエラー値を省いて計算をしていきます。
まずD11セルを選択し、数式バーに『=AGGREGATE(9,6,D3:D10)』を入力します。「D3:D10」とはD3からD10までの値を計算するという意味ですが、「9」と「6」はどのような意味があるのでしょうか。
これは集計方法1~13の表です。この表の集計方法を利用するには、「集計方法（番号）,オプション（番号）,範囲1（範囲2,・・・）」の形式で入力します。ここでは足し算がしたいので、集計方法「9」を使用します。
これはオプションの0~7の表です。ここではエラー値を省きたいので、「6」を使用します。
販売合計が算出されました。エラー値が省かれ、「31600+14200+7400+38250+39600=131050」が計算されました。
集計方法14を利用して指定した順位の点数をもとめる方法
筆記試験の成績表です。今からAGGREGATE関数を使って、B3からD7までの点数から上位5位までの順位をつけていきます。
まずG2セルを選択し、数式バーに『=AGGREGATE(14,3,$B$3:$D$7,F2)』を入力します。その後、Enterを押します。
さて、ここで「14」、「3」、「$B$3:$D$7」、「F2」はどのような意味があるのでしょうか。
これは集計方法14~19の表です。ここでは、順位を1,2,3,4,5の順にして点数を表示させたいので、降順の「14」を使用します。
これはオプションの0~7の表です。ここではオプション0、1、2を無視したいので、「3」を使用します。
G2に1位の結果が出た状態です。赤枠を見ると、B3からD7までの点数が括られていることがわかります。このセル範囲を絶対参照にしたいので、「$B$3:$D$7」と入力します。
※絶対参照とは、セルに入力された計算式をコピーする際、それぞれのセルの参照先を統一してしまうことをいいます。
この事例では、G2のセルの計算式をG3からG6にコピーしたいのですが、絶対参照を適用させてG2をふくむG6までのセルの参照先（B3からD7までのセル）を全部同じにすることができます。
「F2」のセルには1位の1の数字が表示されています。ここで、選択されているG2セルの右下にカーソルを合わせると十字キーに変化するので、そのまま下にドラッグします。
G2からG6に1位から5位までの点数の結果が出ました。
他の関数も合わせてチェック！
当サイトでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。