ExcelでSUBTOTALとSUMIFS関数を組み合わせてフィルターをかけた集計を行う方法

Excelの関数を使って、フィルターをかけた範囲に対して集計を行う方法を解説します。特に、SUMIFS関数を使った集計に、SUBTOTAL関数を組み合わせる方法について説明します。

1. SUMIFS関数とSUBTOTAL関数の基本

まず、SUMIFS関数とは、指定した範囲の中で複数の条件を満たすセルの合計を求める関数です。一方、SUBTOTAL関数は、フィルターをかけた範囲に対して集計を行うことができます。

例えば、次のようにSUMIFS関数を使って、条件を満たす合計を求めることができます。

=SUMIFS(AE20:AE1048576, L20:L1048576, "自社")

この式は、L列が「自社」の場合のAE列の合計を求めます。

フィルターをかけると、非表示のデータは集計に含まれません。これを踏まえて、SUMIFS関数とSUBTOTAL関数を組み合わせることで、表示されているデータだけを集計できます。

SUBTOTAL関数のコード「9」を使用すると、合計を求めることができます。これを利用して、フィルター後の合計を求めるには、次のように式を作成します。

=SUMPRODUCT(SUBTOTAL(9, OFFSET(AE20:AE1048576, ROW(AE20:AE1048576)-MIN(ROW(AE20:AE1048576)), 0, 1)), --(L20:L1048576="自社"))

この式では、OFFSET関数を使って行を動的に指定し、SUBTOTAL関数でフィルター後の合計を求めます。SUMPRODUCT関数は、条件が一致する行に対してのみ合計を求めます。

この方法の利点は、フィルターをかけた状態で動的に集計ができる点です。これにより、条件を満たすデータが表示されている場合のみ集計を行うことができ、効率的にデータを集計できます。

この方法を使用する場合、フィルターが正しくかかっていることを確認してください。また、OFFSET関数を使用しているため、範囲指定が正しいかもチェックしておく必要があります。

SUMIFS関数とSUBTOTAL関数を組み合わせることで、フィルターをかけた範囲に対して効率的に集計を行うことができます。この方法を活用して、Excelでのデータ集計作業を効率化しましょう。