Excelフィルタリング状態での計算概要-excel-php.cn

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

WBOY

リリース： 2022-06-08 11:34:25

転載

4639 人が閲覧しました

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

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

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

1.

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

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

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

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)

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

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

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

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 関数を使用して積を合計します。