Excelフィルタリング状態での計算概要

この記事では、excel に関する知識をお届けします。フィルタ後の連番加算、フィルタ後の乗算、フィルタ後の条件によるカウントの方法を中心に紹介しています。一緒に見ていきましょう。それはみんなを助けます。

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

1.

に示すように、フィルタリングした後にシリアル番号を追加します。下の図では、フィルタリングされた状態で連続したシリアル番号を維持するには、最初にフィルタをキャンセルし、セル D2 に次の数式を入力してプルダウンします:

=SUBTOTAL(3,E$1:E2) -1

SUBTOTAL 関数は、表示されているセルの内容のみをカウントします。

最初のパラメーターに 3 を使用すると、COUNTA 関数を実行するための計算ルール、つまり 2 番目のパラメーターの表示セルの数をカウントすることを示します。

2 番目のパラメータは、動的に拡張された範囲 E$1:E2 を使用します。数式がプルダウンされると、この範囲は E$1:E3、E$1:E4、E$1:E5、...

数式は常に、E 列の最初の行から数式が配置されている行までの領域内に表示される空でないセルの数を計算します。結果から 1 を減算すると、計算結果はシリアル番号と同じになり、フィルタリング後も連続したままになります。

この数式を =SUBTOTAL(3,E$2:E2) に変更した場合、つまり数式が存在する行から開始した場合、シリアル番号の結果は問題ありませんが、注意してください。最後の行は Excel によってフィルターされ、常に概要行として表示されます。

2. フィルタリング後の乗算

下図に示すように、E 列をフィルタリングした後、合計金額と単価を乗算する必要があります。

セル E2 の数式は次のとおりです:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

フィルタリングされた積を計算するには、問題の鍵はデータが表示されるかどうかを判断することです。

この目に見えるステータスをどのように判断すればよいでしょうか?

OFFSET 関数と SUBTOTAL 関数を組み合わせる必要があります。

まず、OFFSET 関数を使用してセル E3 を基点とし、行 1 ～ 13 を下方向にオフセットして、多次元参照を取得します。この多次元参照には 1 行 1 列の 13 個の参照領域が含まれており、E4 から E16 までの個々のセルがそれぞれ参照されます。

次に SUBTOTAL 関数を使用し、最初のパラメータとして 3 を使用します。つまり、E4 から E16 までの各セルに表示されているセルの数を順番に数えます。セルが表示状態の場合は、このセルを数えます。結果は 1 で、それ以外の場合は統計結果は 0 になります。次の効果を持つメモリ配列を取得します:

{1;0;1;1;1;1;0;0;1;1;0;1;0}

再度上記の結果に F 列の数量と G 列の単価を掛けます。セルが表示状態の場合は 1*数量*単価に相当し、それ以外の場合は 0*数量*単位に相当します。価格。

最後に SUMPRODUCT 関数を使用して積を合計します。

3.フィルタリング後の条件による集計

下図のように、E列の部門をフィルタリングした後、勤続3年以上の人数を計算する必要があります。

セル E2 の数式は次のとおりです:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

計算原理の前半は前の例と同じで、中心となるのはセルが表示されているかどうかを判断することです。

式の後半の統計条件(G4:G16>3)は、前半の判定結果に乗算され、両方の条件が同時に満たされることを示します。表示状態にある項目の数と列 G が 3 より大きい。

4. フィルタリング後にタイトルを自動修正する

下図に示すように、E 列の部門名をフィルタリングした後、セル D1 のタイトルを対応する部門に自動的に変更したいとします。式は次のとおりです:

=LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&”統計テーブル”

SUBTOTAL 関数と OFFSET 関数の組み合わせは、D 列のセルが表示されるかどうかを判断することを目的としています。 0 と 1 で構成されるメモリ配列を取得します:

{0;1;0;0;0;0;1;1;1;1;0;1;0;1;0}

メモリ配列 0/ を使用して、0 とエラー値で構成される新しいメモリ配列を取得します:

{#DIV/0!;0;#DIV/0!…;0;0;0 ; 0;#DIV/0!;0;#DIV/0!;0;#DIV/0!}

LOOKUP 関数は、クエリ値として 1 を使用して、上記の最後の 0 の位置を見つけます。メモリ配列 を取得し、対応する位置にある列 E の内容を返します。

最終的な目標は、フィルタリング後に最後に表示されたセルの内容を抽出することです。

抽出した内容を「統計表」と結び付け、自動更新される表タイトルにします。

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

以上がExcelフィルタリング状態での計算概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。