Aさん 小計のある集計表を関数で作成する場合、小計・総計の数だけSUMをセットしないといけませんが、もっと楽にできないですかね？
森田 その場合は、関数の「AGGREGATE」を活用すると総計部分の数式はもっとシンプルにできますよ！
では、AGGREGATEの使い方について解説していきますね。
はじめに
この記事はSUBTOTALの使い方を把握していることが前提です。
SUBTOTALの詳細は以下の記事をご参照ください。
はじめに この記事は関数の概要とSUMの使い方を把握していることが前提です。 参考記事 関数の概要とSUMの使 …
小計がある集計表の「総計」の数式を楽に設定したい場合は「AGGREGATE」が有効
小計がある集計表を関数で作成する場合、総計/小計はSUMを複数セットすることが一般的です。
この場合、総計は離れた小計のセルを、小計は連続するセル範囲をそれぞれ指定します。
ただし、上記のように小計のセルが少なければ良いですが、小計のセルが多い場合、総計のSUMのセル選択が煩雑となり、選択ミスが起きる可能性も高まります。
こんな場合、関数の「AGGREGATE」を使うと、総計の集計が楽になります。
AGGREGATEは「アグリゲート」と読む。
AGGREGATEを使うことで、小計を含んだセル範囲を指定しても、小計を除外して総計を集計することが可能になります。
【参考】SUBTOTALとの違い
小計を除外して総計を集計すること自体はSUBTOTALでも対応できますが、AGGREGATEはExcel2010から登場したSUBTOTALの上位機能にあたる関数です。
主な違いは以下の通りです。
| AGGREGATE | SUBTOTAL | |
|---|---|---|
| 集計方法 | 19種類 ※以下「集計方法」の12~19が追加 |
11種類 |
| 集計対象 | 以下を無視するか選択可能
|
以下を無視するか選択可能
※入れ子にしたAGGREGATE・SUBTOTAL、フィルター操作による非表示の行は必ず無視 |
このように、AGGREGATEの方がより多くの集計方法に対応し、集計対象をより細かく設定できるように強化されました。
よって、AGGREGATEでのみ対応している集計方法を使いたい、あるいは小計がエラー値になる可能性がある場合はAGGREGATEを使うと良いでしょう。
一方、SUBTOTALは非表示の行の扱いのみAGGREGATEより細かいため、手動の非表示は無視せず、フィルターの可視セルだけで集計したいといった場合に使うと良いです。
それ以外は、AGGREGATE・SUBTOTALのどちらも同じ結果になるケースはお好みの方を使いましょう。
AGGREGATEの構文
AGGREGATEの構文は以下の通りです。
=AGGREGATE(集計方法,オプション,参照1,[参照2],…)
リストまたはデータベースの集計値を返します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 集計方法 | ◯ | 数値 | 任意の集計方法を番号（1~19）で指定します。 |
| オプション | ◯ | 数値 | 「参照n」から無視する範囲を番号（0~7）で指定します。 |
| 参照1 | ◯ | 参照 | 計算対象の値や単一セル、セル範囲を指定します。 |
| 参照2 ※参照3以降も同様 |
－ | 参照 | 計算対象の値や単一セル、セル範囲を指定します。 |
引数「参照n」は最大253まで設定可能。
引数「参照n」に1つ以上の3-D参照（複数シートで同じセルまたは範囲を参照すること）が含まれる場合、エラー値「#VALUE!」が表示。
【参考】引数「集計方法」のパターン
引数「集計方法」は任意の集計方法を選択しましょう。
AGGREGATEの関数名をセットすると、数式上でサジェストされるため、任意の集計方法を選択し「Tab」キーで確定しましょう。
各番号がどの集計方法を示すかは以下の表をご覧ください。
| 集計方法 | 関数 | 計算の種類 |
|---|---|---|
| 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 | n番目に大きい値 |
| 15 | SMALL | n番目に小さい値 |
| 16 | PERCENTILE.INC | 上位k%（0以上1以下）にあたる値 |
| 17 | QUARTILE.INC | 四分位数にあたる値（0以上1以下） |
| 18 | PERCENTILE.EXC | 上位k%（0より大きく1より小さい）にあたる値 |
| 19 | QUARTILE.EXC | 四分位数にあたる値（0より大きく1より小さい） |
【参考】引数「オプション」のパターン
引数「オプション」は任意の集計対象（引数「参照n」から無視する範囲）を選択しましょう。
その際、引数「集計方法」の後にコンマ（,）を入力すると、数式上でサジェストされるため、任意のオプションを選択し「Tab」キーで確定しましょう。
各番号がどのオプションを示すかは以下の表をご覧ください。
| オプション | 動作 |
|---|---|
| 0または省略 | 入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視します |
| 1 | 非表示の行、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視します |
| 2 | エラー値、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視します |
| 3 | 非表示の行、エラー値、入れ子になっているSUBTOTAL関数およびAGGREGATE関数を無視します |
| 4 | すべてを検索対象とします |
| 5 | 非表示の行を無視します |
| 6 | エラー値を無視します |
| 7 | 非表示の行とエラー値を無視します |
状況に合わせて使い分けましょう。
AGGREGATEは縦方向の集計を想定した仕様のため、横方向に使用した場合、引数「オプション」を「1」・「3」・「5」・「7」を選択しても非表示の列は無視されない（=集計される）。
【参考】AGGREGATEは2種類の形式がある
AGGREGATEは「配列形式」と「セル範囲形式」の2つの構文が用意されています。
先述の構文はセル範囲形式のもので、配列形式の場合の構文は以下の通りです。
=AGGREGATE(集計方法,オプション,配列,順位)
この2種類の構文は、引数「集計方法」に応じて使い分けるイメージです。
具体的には、次の6つの集計方法の場合は配列形式の構文となり、第4引数までセットしないといけません（第3引数は連続するセル範囲のみ指定可）。
| 集計方法 | 関数 | 構文（AGGREGATEの第3・4引数に該当） |
|---|---|---|
| 14 | LARGE | LARGE(配列,順位) |
| 15 | SMALL | SMALL(配列,順位) |
| 16 | PERCENTILE.INC | PERCENTILE.INC(配列,率) |
| 17 | QUARTILE.INC | QUARTILE.INC(配列,戻り値) |
| 18 | PERCENTILE.EXC | PERCENTILE.EXC(配列,率) |
| 19 | QUARTILE.EXC | QUARTILE.EXC(配列,戻り値) |
上記集計方法の選択時、第4引数を省略した場合、エラー値「#VALUE!」が表示。
【参考】AGGREGATEは「数学/三角関数」
あくまで参考情報となりますが、AGGREGATEはリボン「数式」タブの関数ライブラリの「数学/三角」に分類されています。
実際にAGGREGATEを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
AGGREGATEの使用結果イメージ
AGGREGATEを使い、小計を除外して総計を集計するイメージは以下の通りです。
今回はC4~C22セルの合計を、C14・C22セルの小計の値を除外した上で集計しました。
C14・C22セルはAGGREGATEで合計を集計。
今回は引数「オプション」を「0」（入れ子したSUBTOTAL・AGGREGATEのみ無視）にしていますが、他にも非表示の行やエラー値が発生する可能性があれば、他のオプション番号を設定しましょう。
AGGREGATEの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はC23セル - 「=ag」等と入力
- サジェストから「AGGREGATE」を選択し、「Tab」キーで確定
- サジェストから任意の集計方法を選択
※今回は「9」 - コンマ（,）を入力
- サジェストから任意のオプションを選択
※今回は「0」 - コンマ（,）を入力
- 集計したいセル範囲を選択
※今回はC4~C22セル - 「Enter」キーで確定
手順②の際にIMEを半角英数モードにすること。
【注意】集計軸が変動する場合はピボットテーブルの方が便利
今回は、小計がある集計表を関数で作成する方法を解説していますが、小計の内訳となる集計軸（縦軸の見出し）が増減する場合は、ピボットテーブルの方がおすすめです。
関数の場合、集計表へ手作業で行の挿入や削除、それに対応するように小計や総計の数式修正といったメンテナンスが大変になってしまいます。
一方、ピボットテーブルであれば、「更新」するのみで上記対応が済みますし、後々「小計」や「総計」を表示⇔非表示をコマンドから簡単に変更できて便利です。
ピボットテーブルの「小計」・「総計」コマンドの詳細は以下の記事をご参照ください。
解説動画：【ピボットテーブル#5】書式設定の基本テクニック9選 - レポートを読み手に見やすく分かりやすく体裁を整える この記事の内容は下記の動画でも解説しています。 コメント欄の「小計」・「総計」の時間の部分をクリック …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_AGGREGATE.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はC23セル - 「=ag」等と入力
- サジェストから「AGGREGATE」を選択し、「Tab」キーで確定
- サジェストから任意の集計方法を選択
※今回は「9」 - コンマ（,）を入力
- サジェストから任意のオプションを選択
※今回は「0」 - コンマ（,）を入力
- 集計したいセル範囲を選択
※今回はC4~C22セル - 「Enter」キーで確定
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
AGGREGATEは集計方法や集計対象を細かく設定できる関数です。
関数で小計のある集計表を作成する場面があるなら、覚えておくと便利ですね。
なお、AGGREGATE以外にもExcelでのデータ集計の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 AGGREGATEを使う場面がなければ、無理に覚えることはないです。
ただし、AGGREGATEの集計方法1~9、12~15に対応する個別の関数は単独で使う機会があるため、それぞれ覚えておくことをおすすめします。
