Excel の素晴らしい AGGREGATE 関数を学びましょう。1 つは 19 の価値があります。

前回の記事「実践的な Excel スキルの共有: 「検索と置換」を使用した日付データのフィルタリング 」では、「検索と置換」の実践的な操作をいくつか学びました。今日は、実際には 19 の価値がある魔法の Excel 統計関数について話します。これは単なる成果物です。急いで集めてください。

#今日紹介する関数は AGGREGATE と呼ばれます。 Excel2010から存在する関数ですが、AGGREGATE関数はSUM、AVERAGE、COUNT、LARGEなど19個の関数の機能を実装できるだけでなく、この関数を知っている人が少ないのが非常に残念です。また、非表示の行、エラー値、NULL 値などは無視できます。範囲にエラー値が含まれている場合、SUM などの関数はエラーを返します。この場合、AGGREGATE 関数は非常に便利です。

真似の練習はせず、ただ話して、AGGREGATEのスキルを見てみましょう。

AGGREGATE の基本的な使用法を説明するためにスコア テーブルを使用します。データ ソースは図に示すとおりです:

各生徒は 6 つのテストを受けます。結果によると、青い領域の 5 つの統計内容を取得するには、ほとんどの友人にとって、このフォームを完了するのは難しくないと思います。それは、いくつかの基本関数をマスターするだけです: AVERAGE (平均スコア)、SUM (合計スコア）、MAX（最高スコア）、MIN（最低スコア）、COUNT（実際の基準科目）を使用して、5 つのコンテンツをそれぞれカウントします。上記の 5 つの関数を理解していない初心者もいるかもしれないので、念のため、上記のデータの統計を実現する AGGREGATE 関数だけを学習する必要があります。

AGGREGATE の基本形式は: = AGGREGATE (統計関数、無視される値、データ領域)

例の 5 つの統計内容を完成させる方法を見てみましょう。

1. 統計的平均スコア

現在のテーブルの平均スコア統計式は次のとおりです: =AGGREGATE(1,,B2:G2) )

。セル H2 に数式を入力し、列全体に数式を入力して、各生徒の平均点を取得します。

# 説明: 統計関数が 1 の場合、関数は平均を計算する関数を実装します。この例では、統計を無視するデータを指定する必要がないため、2 番目のパラメータは省略できます (ここにカンマ 2 つが記述され、途中の 1 つのパラメータは省略されています)。最後のパラメータはデータ領域 B2:G2 です。関数の使い方 とても簡単ですが、結果は正しいでしょうか？ AVERAGE 関数を使用して検証することもできます。

結果がまったく同じであることがわかります。

次に、AGGREGATE 関数を使用して合計スコアをカウントする方法を見てみましょう。

2. 統計的な合計スコア

現在のテーブルの合計スコアの統計式は次のとおりです: =AGGREGATE(9,,B2:G2 ) 。セル I2 に数式を入力し、列全体に数式を入力して、各生徒の合計スコアを取得します。

9 は合計関数に対応するため、最初のパラメータを 9 に変更するだけです。

そういえば、最初のパラメータの 1 が平均、9 が合計を表しているのですが、この関数は全部で 19 個あるので覚えるのが大変だと心配している人もいるかもしれません。

実際には、これについてはまったく心配する必要はありません。Excel には非常にスマートなリマインダー関数が用意されています。関数を入力すると、パラメーター関数に対応するオプションがあります:

このプロンプトに従って、必要な機能を選択してください。

3. 最高スコアの統計

この関数を理解すると、最後の 3 つの統計項目を簡単に完了できます。 「Select 4」なので、セル J2 の数式は次のようになります: =AGGREGATE(4,,B2:G2)

# 4.統計最小スコア

最低スコアとして 5 を選択します。セル K2 の数式は次のとおりです: =AGGREGATE(5,,B2:G2)

##5. 統計実際の参考科目

実際の参照勘定科目は、統計データ領域内の数値の数です。COUNT 関数を使用して 2 を選択すると、式は次のようになります: =AGGREGATE(2,,B2:G2)

さて、上記の 5 つの例を通じて、友人は AGGREGATE の基本的な使い方を理解できるはずです。5 つの関数の作業を完了するのに使用される関数は 1 つだけですが、前のものと比較すると、 5 つの関数を使用してそれぞれの作業を完了すると、効率はある程度向上しますが、使用する前に各式を修正する必要があります。数式を使って右に引き下げることができれば素晴らしいでしょう。 (同じように感じている友達は記事の最後にメッセージを残してください)

6 種類と 5 種類の統計を 1 つのステップで実行できます

このアイデアを持っている友人には、褒めるべきです 結局のところ、私たちは仕事を完了するためだけでなく、効率を向上させるために Excel 関数の数式を学びます。それでは、数式を使用して右にプルダウンして、例の 5 つの統計を完成させることは可能でしょうか?答えは「はい」です。ただし、関数の組み合わせのペア、つまりchooseとcolumnが必要です。

式を明らかにする前に、問題の簡単な分析を行ってみましょう。式では、AGGREGATE を使用して 5 つのデータ統計を完成させますが、統計手法である最初のパラメーターのみが次の順序で変更されます。 、9、4、5、2。右にプルダウンする数式を使用したい場合は、右にプルダウンするときに数式の最初のパラメータをこの順序で変更する必要があります (統計手法が同じであるため、プルダウンする場合は変更する必要はありません) ）。

通常、数式を使用して右にプルして連続的に変化するデータを取得する必要がある場合は、列関数を使用します。

列の関数関数はパラメータの対応関係を取得する関数です。列番号、たとえば列 (a1) はセル a1 の列番号、つまり 1 を取得します。右に引っ張ると、a1 は b1、c1... になります。計算式の結果は1、2、3...の順になります。

この例では、取得する必要があるのは、非常に規則的なシーケンスではなく、1、9、4、5、2 の順序のないシーケンスです。このとき、choose 関数を使用する必要があります。達成:

Choose 関数の基本形式は次のとおりです: =choose(select Index, value 1, value 2, value 3...)

Choose 関数は、最初のパラメーターの番号に基づいてパラメーター リストの値を返します。たとえば、上の図では、最初のパラメータが 1 の場合、パラメータ リストの最初の値「1」が返され、最初のパラメータが 2 の場合、パラメータ リストの 2 番目の値「9」が返されます。 、choose の最初のパラメータとして column を使用すると、指定されたシーケンスを返すことができます。

上記はchooseとcolumnの関数の組み合わせの説明です。問題に戻りますが、右プルダウンに使用できる式は次のとおりです。 =AGGREGATE(CHOOSE(COLUMN(A1 ), 1,9,4,5,2),,$B2:$G2)

初心者の中にはまだめまいを感じる人もいるかもしれませんが、これは正常です、私は継続的に学習することで、この公式を自由に使用できるようになると信じています。

7. 最初のパラメータ関数コレクション

上記の紹介を通じて、AGGREGATE 関数を合理的に使用すると、作業効率が向上することがわかります。倍増します。この関数の最初のパラメータの 19 個の関数は何ですか? 次の比較表を見れば、それがよくわかります:

実際、一般的に使用される関数はほんのわずかです。 。

8. 第 2 パラメータ関数集

次に、第 2 パラメータとは何か、または比較表を通して見てみましょう。 :

1. null 値を無視する

2 つの例を通して 2 番目のパラメーターの使用方法を見てみましょう。無視するコンテンツを選択します: =AGGREGATE(9,1,B2:B15)

最初のパラメータでは、合計を表す 9 が選択され、 2 番目のパラメーター 1 を選択すると、非表示の行が無視されます。すべてのデータが表示されると、AGGREGATE 関数を使用して合計した結果は、SUM 関数を使用した結果と一致します (行 16 の合計スコアは SUM 関数を使用して合計されます)。一部の行を非表示にした場合 データを読み取ると、違いがわかります:

行 4、8、および 11 を非表示にした後、式 =AGGREGATE(9,1,B2:B15) は現在表示されているデータのみを要約します。

そういえば、SUBTOTAL関数を学習した生徒さんは、SUBTOTALにもそんな関数があったときっと思うはずです。ただし、今回登場した AGGREGATE 関数は、SUBTOTAL 関数よりも強力です。SUBTOTAL はエラー値やネストされた集計を処理できませんが、AGGREGATE は引き続き処理できるためです。

#2. エラー値を無視する

今日の最後の例では、エラー値が発生したときに何が起こるかを見てみましょう:

上の図に示すように、各生徒の中国語のスコアは、vlookup 関数を使用してスコア テーブルから取得されます (この関数については以前にチュートリアルで説明しました。まだ知らない人は、それについて学ぶためのリンク: リンクを挿入)。 Li Si や Zhang San のように、スコア表に名前がない場合はエラー値が得られ、SUM 関数や SUBTOTAL 関数を使用しても、正しい合計スコアを取得することはできません。中国語スコア。エラー値を無視できるのは AGGREGATE だけです。正しい結果を得ることができます。もちろん、iferror や他の関数を使用して処理し、その後 SUM を使用して合計することもできますが、これは AGGREGATE の能力をカバーするものではありません。

19種類の統計関数と7種類の無視項目を組み合わせた、通常の関数とは比べものにならない驚異の積分関数です。 AGGREGATE は統計関数の王様です。今すぐ集めてください。

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

Excel チュートリアル

以上がExcel の素晴らしい AGGREGATE 関数を学びましょう。1 つは 19 の価値があります。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。