エラー値を含むデータ集計で役立つ関数AGGREGATE

今回は「関数AGGREGATE」の使い方を紹介していこう。AGGREGATE（アグリゲット、アグリゲイト）は「集計」を意味する英単語で、SUBTOTALの上位互換となるような機能を有する関数だ。ただし、関数AGGREGATEを効果的に活用できる場面は、関数SUBTOTALのそれとは異なる。エラー値を含むデータを集計するときに活用できるだろう。

関数AGGREGATEの記述方法

関数AGGREGATEは、（フィルター機能などにより）非表示にしたデータを無視して「合計」や「平均」などを算出する関数となる。基本的な機能は関数SUBTOTALとよく似ているが、指定できる集計方法が関数SUBTOTALよりも多い、無視するデータを選択できる、といった特長があり、SUBTOTALの上位互換ともいえる関数として位置づけられている。

なお、関数SUBTOTALの使い方をよく知らない人は、先に前回の連載を読んでから本連載を読み進めていただけると、より理解が深まるはずだ。

エラー値を含むデータも集計できる関数AGGREGATE

それでは、関数AGGREGATEの記述方法を紹介していこう。関数SUBTOTALと同様に、第1引数には「集計方法」を指定する。指定可能な値は1～19で、そのうち1～11は関数SUBTOTALと同じ集計方法になる。

◆関数AGGREGATEの書式
　=AGGREGATE(集計方法, オプション, 範囲1, [範囲2], ・・・)

・第1引数（集計方法）に指定できる値
　　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：0と1（0%と100%）を除いた百分位数（PERCENTILE.EXC）
　　19：0と1（0%と100%）を除いた四分位数（QUARTILE.EXC）

つまり、「12～19の集計方法」をSUBTOTALに追加した関数がAGGREGATEと考えられる。さらに、無視するデータ（計算の対象外にするデータ）を第2引数で指定することも可能となっている。こちらは、以下に示した0～7の数値で指定する仕組みになっている。

・第2引数（オプション）に指定できる値
　　0：SUBTOTALとAGGREGATEを無視（※1）
　　1：SUBTOTALとAGGREGATE、非表示の行を無視（※1）
　　2：SUBTOTALとAGGREGATE、エラー値を無視（※1）
　　3：SUBTOTALとAGGREGATE、非表示の行、エラー値を無視（※1）
　　4：何も無視しない
　　5：非表示の行を無視
　　6：エラー値を無視
　　7：非表示の行とエラー値を無視
　　（※1）関数がネストされている場合

「第2引数の値」と「無視するデータ」の関係を把握しやすいように、それぞれを表で示した図版も紹介しておこう。