Excel で sumproduct で subtotal を使用する方法

次の数式を使用して、Excel でSUBTOTAL関数とSUMPRODUCT関数を組み合わせることができます。

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

この特定の数式を使用すると、セル範囲が何らかの方法でフィルターされた後でも、範囲C2:C11と範囲D2:D11の値の積を合計することができます。

次の例は、この公式を実際に使用する方法を示しています。

例: Excel で SUMPRODUCT で SUBTOTAL を使用する方法

2 つの異なる食料品店でのさまざまな製品の販売に関する情報を含む次のデータセットがあるとします。

次に、データをフィルター処理して、Store 列の値がBである行のみを表示しましょう。

これを行うには、セル範囲A1:D11を強調表示します。次に、上部のリボンに沿って[データ]タブをクリックし、 [フィルター]ボタンをクリックします。

次に、 「Store」の横にあるドロップダウン矢印をクリックし、「 B」の横のボックスのみがチェックされていることを確認し、「 OK」をクリックします。

データは自動的にフィルタリングされ、 Store 列がBに等しい行のみを表示します。

SUMRPODUCT()関数を使用して Sales 列値と Price 列値の積を合計しようとすると、実際には元のデータセット内のこれら 2 つの列の積の合計が返されます。

代わりに、次の式を使用する必要があります。

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

次のスクリーンショットは、この式を実際に使用する方法を示しています。

この式は、 77.5の正しい合計を返します。

Sales 列と Price 列の間の値の積の合計を手動で計算することで、これが正しいことを確認できます。

売上と価格の値の積の合計: (3*2) + (12*2.5) + (5*3.5) + (8*3) = 77.5 。

追加リソース

次のチュートリアルでは、Excel で他の一般的な操作を実行する方法について説明します。

Excelでフィルターされた行を削除する方法
Excel でフィルターされた行をカウントする方法
Excelでフィルターされた行を合計する方法