ExcelのAGGREGATE関数の使い方|リストやデータベースの集計値を返す

はじめに

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関数を使ってエラー値を省いて計算をしていきます。

AGGREGATE関数の利用

まずD11セルを選択し、数式バーに『=AGGREGATE(9,6,D3:D10)』を入力します。「D3:D10」とはD3からD10までの値を計算するという意味ですが、「9」と「6」はどのような意味があるのでしょうか。

集計方法

これは集計方法1~13の表です。この表の集計方法を利用するには、「集計方法(番号),オプション(番号),範囲1(範囲2,・・・)」の形式で入力します。ここでは足し算がしたいので、集計方法「9」を使用します。

オプション

これはオプションの0~7の表です。ここではエラー値を省きたいので、「6」を使用します。

AGGREGATE関数の結果

販売合計が算出されました。エラー値が省かれ、「31600+14200+7400+38250+39600=131050」が計算されました。

集計方法14を利用して指定した順位の点数をもとめる方法

筆記試験の成績表

筆記試験の成績表です。今からAGGREGATE関数を使って、B3からD7までの点数から上位5位までの順位をつけていきます。

AGGREGATE関数の入力

まず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」を使用します。

AGGREGATE関数の説明

G2に1位の結果が出た状態です。赤枠を見ると、B3からD7までの点数が括られていることがわかります。このセル範囲を絶対参照にしたいので、「$B$3:$D$7」と入力します。

※絶対参照とは、セルに入力された計算式をコピーする際、それぞれのセルの参照先を統一してしまうことをいいます。

この事例では、G2のセルの計算式をG3からG6にコピーしたいのですが、絶対参照を適用させてG2をふくむG6までのセルの参照先(B3からD7までのセル)を全部同じにすることができます。

順位の説明

「F2」のセルには1位の1の数字が表示されています。ここで、選択されているG2セルの右下にカーソルを合わせると十字キーに変化するので、そのまま下にドラッグします。

順位結果の表示

G2からG6に1位から5位までの点数の結果が出ました。

Windows10とExcel2016

本記事で使用したOSはWindows10、Excelのバージョンは2016です。

他の関数も合わせてチェック!

目的別に並べた便利なExcel関数一覧表(全451種)

目的別に並べた便利なExcel関数一覧表(全459種)

Excel Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

このページを見ている方におすすめの商品

Excel関数逆引き辞典パーフェクト 第3版

Excel関数逆引き辞典パーフェクト 第3版

きたみ あきこ-翔泳社

全832ページ

¥ 2,592

できるポケット Excel関数全事典 2013/2010/2007対応 (できるポケット全事典シリーズ)

できるポケット Excel関数全事典 2013/2010/2007対応 (できるポケット全事典シリーズ)

羽山 博-インプレス

全368ページ

¥ 1,490

このページをフォローする

関連記事