AGGREGATE関数で集計値(19の方法)を求める方法とSUBTOTALとの違いや0無視についてわかりやすく解説

AGGREGATE関数について

AGGREGATEの概要

どのような時に使用するか

• 飛び飛びのデータを集計したい
• 連続していないデータの集計
• 大量のデータのの中から、5番目に大きい数値を見つけ出したい
• 全体の数値において60%の位置にある数値を知りたい

AGGREGATE(集計方法が1～13)の例として合計値を求める

AGGREGATE(集計方法が14～19)の例として指定順位の値を求める

AGGREGATEとSUBTOTALの違い

• SUBTOTALにできてAGGREGATEにできない処理はないため、AGGREGATEが推奨されます
  

  AGGREGATE	SUBTOTAL
  19種類の集計方法 エラーなどの無視処理の指定ができる	11種類の集計方法 エラーなどの無視処理の指定ができない

AGGREGATEの引数指定型(14～19)で離れたセルの値を取得したい

• 以下のA列、C列の最大値を求めたいときに=AGGREGATE( 14 , 6 , A1:A5 , C2:C5 , 1 )と、=AGGREGATE( 14 , 6 , A1:C5 , 1 )またはすることはできない。前者はエラー、後者はB列も含まれてしまう。
• そこで、MAXを用いるか、範囲毎に答えを求めてAGGREGATEを適用するかの方法を取ります。
• D1セルを=AGGREGATE( D2 , 6 , A1:A5 , E2 )、E1セルを=AGGREGATE( D2 , 6 , C1:C5 , E2 )とします。その上で、以下のようにします。

AGGREGATEで0を無視する？

• 例えば以下の数値の中で0を無視して平均値を取得します。0を無視しなければ60です。オプションにおける0指定とは関数を無視するという意味であって、2や3の「0に加えて」の0は数値の0を無視するという意味ではありません。
• 0を無視したいということであれば、別途、0を空白セルとするセルを用意してその列について計算を行う方法などがあります。

AGGREGATE関数のよくある質問

Q: AGGREGATE関数の基本的な使い方は？

A: AGGREGATE関数は、リストやデータ範囲に対して特定の統計を計算しつつ、エラー値や非表示行を無視することができます。構文は=AGGREGATE(関数番号, オプション, 範囲1, [範囲2], ...)です。例えば、=AGGREGATE(9, 7, A1:A10)は、A1:A10の合計を計算し、エラー値や非表示行を無視します。

Q: AGGREGATE関数の「LARGE」や「SMALL」の使い方は？

A: AGGREGATE関数でLARGEやSMALLを使う場合、順位を追加の引数として指定します。例えば、=AGGREGATE(14, 6, A1:A10, 3)は、A1:A10の中で3番目に大きい値を計算し、エラーや非表示行を無視します。

Q: AGGREGATE関数がSUBTOTAL関数と異なる点は？

A: SUBTOTAL関数は単一の統計計算に限定されますが、AGGREGATE関数はLARGEやSMALLなど、より広範な統計計算が可能です。また、エラー値を無視する機能も備えています。

Q: AGGREGATE関数でエラー値が無視されない場合の原因は？

A: オプション引数でエラー値を無視する設定が正しくされていない可能性があります。エラーを無視するには、オプション引数に3または7を指定してください。

Q: AGGREGATE関数で複数条件を使用する方法は？

A: AGGREGATE関数は複数条件を直接サポートしていませんが、条件付きで計算したい場合は、条件を補助列で計算するか、配列数式を組み合わせる必要があります。

Q: AGGREGATE関数で配列計算を使用する際の注意点は？

A: 配列計算を使用する場合、エラー値や非表示行を正しく無視するために適切なオプションを指定してください。また、配列のサイズが一致していることを確認してください。

Excelの関数を検索する