見出し画像

№3　AGREEGATE関数

S.format

AGREEGATE関数　とは

AGREEGATE(アグリゲート)
できること:①〔集計方法〕に従ってさまざまな集計値を求める。
　　　　　 ②〔集計方法〕に従って順位や分位数を求める。
対応Ver：365　2021　2019　2016　2013　2010
引数:　①AGGREGATE（集計方法, オプション, 参照1, 参照2, ..., 参照253)
　　　 ②AGGREGATE（集計方法, オプション, 配列, 値）

使い方

【AGREEGATE関数】は、前の記事で紹介した【SUBTOTAL関数】の改良型ともいうべき関数です。この関数には構文が2種類あり、〔集計方法〕に加えて〔オプション〕も指定することができ、集計の幅が大きく広がりました。

少し複雑になってきましたが、安心してください。
機能がたくさんあるからといって全部覚える必要はなく、ご自身が使われるものだけ覚えてしまえば問題ありません。
特に②の構文は私も知識としては知っていますが使ったことがありません。

実用のことを考えて①の構文について細かく説明していきますね。
まずは、〔集計方法〕ですが、これは【SUBTOTAL関数】で学んだ時と同様に1～19の計算の種類を指定することができます。この中で使用されるのは同じく7つほどです。

次に〔オプション〕ですが、これは【SUBTOTAL関数】で非表示行を無視して集計するときには"9”ではなく"109”を指定したように
集計するときの条件を加えることができるようになりました。
その条件とは、

  • 非表示を無視する

  • エラー値を無視する

  • SUBTOTAL関数やAGREEGATE関数を使って集計されたセルを無視する

  • 無視しない

この4つです。

「ネストされた～」は小計でSUBTOTAL関数またはAGGREGATE関数を使用していると読み替えてください。

SUBTOTAL関数から追加された条件は「エラー値を無視する」ですね。
この４つの組み合わせが、0～7に振り分けられてその時にあったものを指定することができます。
"3"は、すべて無視することができるのでこちらを指定することがほとんどだと思います。

最後に〔参照〕ですが、こちらも【SUBTOTAL関数】と同様にセルもしくはセル範囲を指定します。
【SUM関数】のように直接構文に数値は入力できないので注意しましょう。

ちなみに「aggregate」は英語で「～を集まる、集計する」です。
語源のラテン語からすると「群れにして集める」といったニュアンスがを持ちます。

それでは、今回も【SUM関数】の用法を例にとって説明します。
新しく追加された「エラー値を無視する」が活躍するのは、下のような状況です。

C2セルがエラーになると小計(C6)と合計(C12）がエラー表示となってしまう

【SUM関数】や【SUBTOTAL関数】の対象が一つでもエラー値の場合、合計値もエラーとして表示されてしまいます。
これが【AGGREGATE関数】のオプションで「エラー値を無視する」という条件を付けているとそのエラー値以外の合計が表示されるようになります。

小計(H6)と合計(H12)では、H2セルのエラーを無視して集計している

場合によっては、エラーを起こしていることに気付けるきっかけとなりますが、やむを得ない理由（例えば、対象の数値が不明な時など）であえてエラーとしているのに合計値が表示されないのはかなり不便です。
のちに説明する【IFERROR関数】などで細かくエラーを起こさないように設定することもできますがわざわざデータセルに設定するのも面倒なので【AGGRREGATE関数】でエラー値を無視できるようにしておくほうが無難ですね。

また、【SUBTOTAL関数】を小計で使用している場合、合計を【AGGREGATE関数】で集計していても小計は重複されませんし、反対に
【AGGREGATE関数】を小計で使用している場合、合計を【SUBTOTAL関数】で集計していても小計は重複されません。
わざわざ小計と合計の関数を分ける理由もありませんが、豆知識として共有します。

SUBTOTAL関数とAGGREGATE関数は相互関係にある

使う場面

【AGGREGATE関数】は、小計を含む表を集計する時に使用される関数です。
この集計の対象となるのは、数値（もしくは文字列として入力された数字）に限定されます。
実際に使用される場面としては、複数拠点を含む全社の売上集計表、年間出勤日数の集計表などが挙げられます。特にデータ完全に揃っていない途中経過を把握する場合に役に立ちます。

合わせて覚えておきたい関数

先ほど紹介した通り、【AGGREGATE関数】はさまざまな集計方法で使用することができる関数です。
【SUBTOTAL関数】同様に、その集計方法である関数を知っていることが前提となってしまいますので少なくとも以下の関数は覚えておきたいですね。

  • AVERAGE関数　平均値を求める

  • COUNT関数　数値の個数を求める

  • COUNTA関数　データの個数を求める

  • MAX関数　最大値を求める

  • MIN関数　最小値を求める

  • PRODUCT関数　積（掛け算の結果）を求める

  • SUM関数　合計値を求める

他にもAGGREGATE関数で使える集計方法は12個あるのですが、特殊な環境下でない限り使用する場面はありませんので覚えてなくて大丈夫です。


この記事が参加している募集

仕事について話そう

108,833件

この記事が気に入ったらサポートをしてみませんか？