Excel での AGGREGATE 関数の 8 つの使用例の概要

この記事では、excel に関する関連知識を提供します。主に AGGREGATE 関数に関する関連内容を紹介します。この関数の使用方法は SUBTOTAL 関数に似ていますが、SUBTOTAL 関数よりも機能的です。 . 強力ですので、見てみましょう。皆さんのお役に立てれば幸いです。

関連する学習の推奨事項: excel チュートリアル

AGGREGATE 関数の使用法は SUBTOTAL 関数と似ていますが、さらに複雑です。 SUBTOTAL関数よりも強力で、SUM、AVERAGE、COUNT、LARGE、MAXなど19個の関数の機能を実現できるだけでなく、非表示の行、エラー値、NULL値などを無視でき、定数もサポートします。配列。

この関数の最初のパラメータは 1 ～ 19 の数値で、使用する集計方法を指定するために使用されます。

2 番目のパラメータは計算領域で無視する値のタイプを指定する、0 から 7 までの数値:

次に、この関数の典型的な使用法について説明します。

1. 複数の不連続な領域はエラー値を無視し、それらを直接合計します

この関数の強みは、2 番目のパラメーターを指定してエラー値を無視し、直接カウントできることです

次の図のように、青い領域にはさまざまなエラー値が含まれています。ここで、これらの不連続な領域を合計する必要があります。

式は次のとおりです:

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

2. フィルタリング状態のエラー値を無視する

#以下に示すように、フィルタリングされたデータ領域にはエラー値が含まれています。

式は次のとおりです:

=AGGREGATE(9,7,B6:B18)

##最初のパラメータを使用します合計には 9 を使用し、非表示の行とエラー値を無視するには 2 番目のパラメーターに 7 を使用します。

3. 1 つの数式で複数の統計効果を解決します

以下に示すように、フィルター処理されたデータはセル領域 A3:B14 にあり、表示領域とすべてのデータの最大値と最大値最小値、平均値、合計、個数、中央値をそれぞれカウントする必要があります。

式は 1 つだけで十分です:

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

これは領域配列式であることに注意してください。最初に c17:d22 領域を選択し、次に編集バーに式を書き込み、最後に Ctrl + SHIFT Enter を押して入力します。それ。

4. フィルタリングされていても、上向きに合計する方法は皆さん知っていますが、下向きについてはどうでしょうか?

=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)

下向きの合計方向に加えて、隠れた値やエラー値も存在します。これは小計 sum(if) では達成できない統計効果です。

(入力方法は、合計する領域を選択することです) Null 値を配置し、編集バーに書き込みます 式を完成させます Ctrl + Enter バッチ充填)

5. 開始が重要なポイントです - 条件付き極値統計

この関数は統計を実現しています5 年前の 2016 年にのみ利用可能になった maxifs 関数と minifs 関数を組み合わせたもので、効果的であり、3 つのキーは必要ありません。

以下に示すように、1 つのワークショップに対応する最小値を計算するための式は次のとおりです:

=AGGREGATE(15,6,B4:B15/(A4:A15=" 1 ワークショップ" ),1)

式の最初のパラメーターは 15 を使用します。これは SMALL 関数を使用することを意味し、2 番目のパラメーターは 6 を使用します。これは意味します。エラー値は無視されます。カウントする領域は、B4:B15/(A4:A15="1 作業場")

A4:A15="1 作業場" セクションです。まず、A 列の作業場が指定された作業場と等しいかどうかを比較します。条件。セル範囲 A4:A15 が「1 ワークショップ」に等しい場合、論理値 TRUE が返され、それ以外の場合、論理値 FALSE が返されます。次に、このメモリ配列を B4:B15 で除算すると、結果は次のようになります:

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/ 0! ;#DIV/0!;19;47}

最後に、AGGREGATE 関数は内部のエラー値を無視し、最初の最小値を取得します。

ワークショップ 1 に対応する 3 番目の最小値を計算したい場合は、最後の 1 を 3 に変更するだけです。

ワークショップ 1 に対応する最大値を計算したい場合は、最初のパラメーターを変更して、k 番目の最大値である 14 を使用できます。

6. 1 対多のクエリ

1 対多のクエリが必要な場合、多くの人は INDEX SAMLL IF 関数の 3 つのキーの組み合わせを思い浮かべます。実は、集計関数に置き換えることも可能です。

以下に示すように、2 番目のワークショップのすべての作業番号を抽出するには、次の式を使用できます。

=IFERROR(INDEX(B:B,AGGREGATE(15, 6,ROW( $3:$12)/(A$3:A$12=D$3),ROW(A1))),"")

この式の考え方は基本的に5番目の式と同じです。

7. 同一セルの最大値をカウントする

以下のように、B列の複数人の評価状況が同一セルに記載されており、その最大値をカウントする必要があります。式は次のとおりです:

=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)

数式の MID(B4,ROW($1:$50),COLUMN(A:AZ)) 部分については、1 文字目から 50 文字目まで MID 関数を使用し、長さ 1 ～ 50 の文字列をそれぞれ抽出すると、巨大なメモリ配列が生成されます。さらに 2 つの負符号を使用して、メモリ配列内のテキストをエラー値に変換します。この値は依然として独自の値です。

最後に、AGGREGATE 関数を使用してメモリ配列内のエラー値を無視し、それらの中で最初の最小値を計算します。

8. 指定した条件の最大値と最小値を同時にカウントする

下図のように、1ワークショップに対応する最大値と最小値をカウントする必要があります同時に。

まずセル F4:G4 を同時に選択し、編集バーに次の数式を入力し、Ctrl Shift キーを押して Enter キーを押します。

#=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

##AGGREGATE 最初のパラメータは定数配列 {16,15} を使用し、それぞれ最大値と最小値を使用する計算規則を示します。

最終結果もメモリ配列であるため、入力として 2 つのセルを同時に選択する必要があります。

この関数の特徴は、第一引数が 14~19 の場合、第四引数を使用できることです。このとき、第四引数は配列をサポートしているので、直接 SMALL を置き換えてさまざまなアプリケーションを実行できます。 、LARGE、およびエラー値を無視するその他の関数。

関連する学習の推奨事項:

Excel チュートリアル

以上がExcel での AGGREGATE 関数の 8 つの使用例の概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。