Excelでifでsubtotalを使用するにはどうすればよいですか?

この公式はまさに魔法です。

Excel で IF (条件) を使用して SUBTOTAL 式を作成する場合は、いくつかの関数を使用する必要があります。しかし、その前に、この例のデータを理解しましょう。

上の例には 3 つの列があります。

また、[年齢スラブ] 列からスラブをフィルターすると、セル F1 に女性の数が表示されます。つまり、条件付きでフィルターされた値の数を表示する数式があるということになります。

式は次のとおりです。

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

SUBTOTAL IF 式を理解する

この数式では、SUMPRODUCT、SUBTOTAL、OFFSET、ROW、MAX の 5 つの関数を使用します。したがって、この式を理解するには、それをいくつかの部分に分割する必要があります。

式のこの部分では、MIN 関数と ROW 関数を使用します。

この部分には、行番号の配列を返す ROW 関数のみがあります。

OFFSET 関数が追加されました。これは、セル参照を開始点として使用して範囲への参照を作成するのに役立ちます。参照の引数では、性別の範囲が始まる最初のセルであるセル C2 を参照しました。

行の引数には、最初の 2 つの部分で説明した式のこの部分が含まれています。その後、cols 引数に 0 を使用しました。これで、OFFSET は「Sex」列のすべての値の配列を返します。

SUBTOTAL の OFFSET によって返された配列を使用しました。そして、function_num では 3 を使用しました。これは、SUBTOTAL に計算に COUNTA 関数を使用するように指示します。

「年齢スラブ」列でフィルターを使用すると、数式の小計部分は 0 と 1 を含む配列を返します。

この表では、フィルターを適用した値に相当する値が 1 になっています。以下の例を参照してください。

数式のこの部分は、条件をテストして配列を返します。この条件では、範囲値が「女性」かどうかをテストし、配列で TRUE と FALSE を返します。

このテーブルでは、「女性」の値が TRUE、その他の値が FALSE になっています。

最終的に、SUMPRODUCT には 2 つのテーブルができます。また、これらの配列の間にはアスタリスク演算子もあります。

2 つのテーブルを掛け合わせると、0 と 1 を含む 1 つのテーブルが得られます。このテーブルでは、1 は性別の値「女性」を表し、21 ～ 30 は「年齢スラブ」を表します。

最終的に、SUMPRODUCT はこの配列を使用して合計を返します。この合計は、[年齢範囲] 列を 21 ～ 30 でフィルターした場合の、性別列の値が「女性」であるセルの数です。