エラー値を含むデータ集計で役立つ関数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引数の値」と「無視するデータ」の関係を把握しやすいように、それぞれを表で示した図版も紹介しておこう。

第2引数の指定により無視されるデータ

続いて、第3引数に「集計するセル範囲」を指定すると、関数の入力が完了する。以上が関数AGGREGATEの記述方法となる。文章で説明しても理解しづらいと思われるので、以降に具体的な例を紹介していこう。

関数AGGREGATEを使った表示データだけの集計

関数SUBTOTALとの違いを把握しやすいように、前回の連載と同じデータ表を使って具体的な使用例を紹介していこう。このデータ表には、「公演日」や「区分」、「席」などに分類した形で各チケットの「販売数」と「金額」がまとめられている。

「チケットの売上状況」をまとめた表

また、表の下には「販売数」の「金額」の合計を求める関数SUMが入力されている。

「販売数」と「金額」の合計

これらの合計を関数AGGREGATEで求めるときの記述例を紹介していこう。適当なセルを選択して「=AGGREGATE(」まで関数の入力を進めると、第1引数（集計方法）に指定可能な数値の一覧が表示される。

「販売数」の合計を求める関数AGGREGATEの入力（1）

このあたりの動作は関数SUBTOTALと同じだ。関数AGGREGATEを使用する場合も、第1引数に指定する数値を暗記しておく必要はない。関数の入力時に表示される一覧を参考に数値を指定することが可能だ。今回の例のように「合計」を求めたい場合は、第1引数に「9」を指定すればよい。

続いて、「,」（カンマ）を入力すると、第2引数（オプション）に指定可能な数値の一覧が表示される。

「販売数」の合計を求める関数AGGREGATEの入力（2）

よって、第2引数に指定する数値も暗記しておく必要はない。今回の例では第2引数に「3」を指定するので、「SUBTOTALとAGGREGATE」、「非表示の行」、「エラー値」のすべてを無視することになる。

最後に「集計するセル範囲」を指定すると、関数の入力が完了する。

「販売数」の合計を求める関数AGGREGATEの入力（3）

これで「販売数」の合計を算出できる。同様の手順で「金額」の合計を算出する関数AGGREGATEも入力していこう。「集計するセル範囲」（第3引数）が異なるだけで、基本的な記述方法は先ほどと同じだ。

「金額」の合計を求める関数AGGREGATEの入力

「Enter」キーを押して関数を実行すると、以下の図のような結果が表示された。現時点では全データが画面に表示されているので、この結果は関数SUMで計算した「合計」と同じ値になる。

算出された合計値

それでは、関数AGGREGATEならではの特長を確認していこう。まずは、フィルター機能を使って「前売り」のデータだけを表示した例だ。この場合、「非表示の行」のデータが無視されるため、「前売り」のチケットについてのみ「販売数」と「金額」の合計を求めることが可能となる。

フィルター機能で「前売り」のデータだけを抽出した場合

さらに、手作業で「非表示の行」を指定した例も紹介しておこう。以下の図のように「7/16」のデータを非表示にすると、「7/15」と「7/17」の「前売り」のチケットについてのみ「販売数」と「金額」の合計を求められる。

行を非表示にする操作

「7/16」のデータを非表示にした場合

このように、画面に表示されているデータについてのみ、「合計」や「平均」などの指標を算出できるのが関数AGGREGATEと特長となる。ただし、同様の機能は関数SUBTOTALでも実現できるので、あえて関数AGGREGATEを使用する理由は特に見当たらない。

中央値（MEDIAN）や最頻値（MODE.SNGL）など、関数SUBTOTALに用意されていない集計方法を指定できるのは関数AGGREGATEならではの特長といえるが、これらの指標を実際に使用する機会はあまり多くないため、それほど大きなメリットにはならない。むしろ、「関数名を覚えにくい」、「指定する引数の数が多くなる」といったデメリットの方が大きいかもしれない。

「無視するデータ」を第2引数で指定できることも関数AGGREGATEならではの特長といえるが、「非表示の行」だけを無視して「SUBTOTALやAGGREGATEの計算結果」は無視しない、などのオプションを指定するケースは状況は非常に稀なので、こちらも特に大きなメリットとはいえないだろう。

エラーを含む場合の集計

では、どのような場合に関数AGGREGATEを活用できるのだろうか？　その一例となるのが、エラー値を含む表で「合計」や「平均」などを算出したい場合だ。「関数SUM」や「関数SUBTOTAL」はエラー値を無視できないため、この部分が関数AGGREGATEならではの最大の特長となる。

今度は、以下に示した図を使って説明していこう。

計算エラーを含む表

上図は、あるレストランにおける「1週間の予約状況」をまとめたものだ。予約されたコースの（単価）×（人数）を計算して「金額」を求めているが、「特別にアレンジしたコースをお願いしたい・・・」とか、「まだ人数が確定していない・・・」といった予約も含まれているため、一部のデータが“文字データ”として入力されている（背景が黄色のセル）。

このような場合、普通に関数SUMで「金額」の合計を算出すると、エラーが発生してしまう。