Excelの集計関数
excelのAGGREGATE関数は、指定されたデータテーブルまたはデータリストの集計を返します。この関数には、関数番号として最初の引数もあり、追加の引数はデータセットの範囲に対するものです。関数番号は、使用する関数を知るために覚えておく必要があります。
構文
AGGREGATE式には2つの構文があります。
- 参照構文
= AGGREGATE（function_num、options、ref1、ref2、ref [3]、…）
- 配列構文
= AGGREGATE（function_num、options、array、[k]）
Function_numは、使用する特定の関数を示す数値であり、1〜19の数値です。
オプション： 0から7の範囲の数値でもあり、計算中に無視する値を決定します
Ref1、ref2、ref [3]： は参照構文を使用する際の引数であり、計算を実行する1つまたは複数の数値です。少なくとも2つの引数が必要です。残りの引数はオプションです。
配列：操作を実行する値の配列であり、ExcelのAGGREGATE関数の配列構文で使用されます。
K：はオプションの引数であり、数値です。ExcelでLARGE、SMALL、PERCENTILE.EXC、QUARTILE.INC、PERCENTILE.INC、QUARTILE.EXCなどの関数を使用する場合に使用されます。
例
このAGGREGATE関数Excelテンプレートはここからダウンロードできます–AGGREGATE関数Excelテンプレート例–＃1
数値のリストがあり、値を含むセルの数である平均、カウント、空ではないセルのカウント、最大、最小、積、および指定された数値の合計を計算するとします。以下の表に値を示します。
最初に、指定されたすべての値について、行9の平均を計算しましょう。平均して、function_numは
列Cでは、すべての値が指定されており、値を無視する必要がないため、オプション4を選択します（何も無視しない）
そして、値の範囲C1：C8を数値の配列として選択します
' k'はオプションの引数であり、ExcelのLARGE、SMALL、PERCENTILE.EXC、QUARTILE.INC、PERCENTILE.INC、またはQUARTILE.EXCなどの関数が使用される場合に使用されますが、この場合、平均を計算しているため、 kの値は省略されます。
したがって、平均値は
同様に、範囲D1：D8についても、オプション4を選択します。
範囲E1：E8の場合、セルE6にはエラー値が含まれます。同じAGGREGATE数式を使用するとエラーが発生しますが、適切なオプションを使用すると、ExcelのAGGREGATEは、エラーを無視して残りの値の平均を返します。 E6の値。
エラー値を無視するために、オプション6があります。
同様に、範囲G1：G8の場合、オプション6を使用します（エラー値は無視します）
ここで、範囲H3の場合、値64を入力し、3番目の行を非表示にしてオプション5を使用すると、非表示の行を無視します。ExcelのAGGREGATEは、表示されている数値の平均値のみを示します。
行3を非表示にしない出力
行3を非表示にした後の出力
AGGREGATE式を他の操作に適用すると、次のようになります。
例–＃2
以下に示すように、さまざまなチャネルからさまざまな日付に生成された収益のテーブルがあるとします。
ここで、さまざまなチャネルで生成された収益を確認します。したがって、合計関数を適用すると、生成された総収益が得られますが、オーガニックチャネルや直接チャネルなどの生成された収益を確認する場合、Excelでフィルターを適用すると、合計関数は常に合計を与える
チャネルをフィルタリングするときに、表示されている値の合計を取得する必要があるため、SUM関数を使用する代わりに、AGGREGATE関数を使用して、フィルターが表示されているときに表示される値の合計を取得します。適用されます。
したがって、SUM式をオプションコード5のAGGREGATE関数に置き換えると（非表示の行と値は無視されます）、
ここで、さまざまなチャネルにフィルターを適用すると、残りの行が非表示になったときにのみ、そのチャネルの収益が表示されます。
ダイレクトチャネルで生成された総収益：
オーガニックチャネルで生み出された総収入：
有料チャネルで生成された総収益：
したがって、AGGREGATE関数は、フィルタリングされた後、さまざまなチャネルに対して生成された収益のさまざまな合計値を計算することがわかります。したがって、AGGREGATE関数を動的に使用して、条件式を使用せずに、さまざまな条件のさまざまな関数を置き換えることができます。
同じテーブルチャネルと収益について、収益値の一部にエラーが含まれていると仮定します。エラーを無視する必要があります。同時に、フィルターを適用する場合、AGGREGATE関数は非表示の行の値も無視する必要があります。
オプション5を使用すると、総収益のSUMのエラーが発生します。エラーを無視するには、オプション6を使用する必要があります。
オプション6を使用すると、エラー値を無視して合計が得られますが、たとえば、チャネル値Directでフィルターを適用すると、エラーを無視して同じ合計が得られますが、同時に非表示の値も無視する必要があります。
したがって、この場合、エラー値を無視すると同時に非表示の行を無視するオプション7を使用します。
覚えておくべきこと
- AGGREGATE関数は、19より大きいまたは1より小さい関数_ num値を認識しません。同様に、オプション番号については、7より大きく1より小さい値を認識しません。他の値を指定すると、＃VALUEが返されます。 ！！エラー
- 常に数値を受け入れ、常に数値を出力として返します
- ExcelのAGGREGATEには制限があります。非表示の行を無視するだけで、非表示の列は無視しません。