Excelのフィルタ機能を使って、ある日付で特定の商品の販売回数をカウントするなど、表にフィルタをかけた後に別の条件でカウントしたいことがあります。
しかし、COUNTIF関数では対応できず、フィルタを考慮しない結果が返ってきてしまいます。したがって、別の関数を利用してカウントする必要があります。
この記事では、Excelでフィルタ後の結果を指定した条件でカウントする方法をご紹介します。
フィルタの結果にCOUNTIF関数を使った場合
次の販売の実績表を例にします。この表をフィルタ機能で絞り込んでカウントしたいとします。
9月の販売状況を確認したい場合、販売日でフィルタをかけ9月のデータのみを表示します。
上記のフィルタ後の表について、商品「A001」が販売された回数を調べたいと思います。
COUNTIFでの結果を確認してみましょう。本来は「3」と取得したいはずなのに、8月の分が含められて「4」とカウントされてしまっています。
このようにフィルタがかかった表に対してはCOUNTIF関数は正しくカウントすることができません。
フィルタ後の結果を条件でカウントする方法
フィルタ後の結果を条件でカウントするにはSUMPRODUCTとSUBTOTAL関数を用いて以下の式を利用します。
=SUMPRODUCT((C5:C13="A001")*(SUBTOTAL(3,INDIRECT("C"&ROW(C5:C13)))))
それぞれの数式の内容について分割して解説します。
- SUMPRODUCT()
- SUMPRODUCT関数は配列どうしの計算ができます。
- (C5:C13=”A001″)
- C5~C13セルの中で「A001」に一致するセルを探し、一致したら「1」を返します。
- SUMPRODUCT関数内での結果は、配列[1, 0, 0, 1, 1, 0, 0, 1, 0]が返されます。
- (SUBTOTAL(3,INDIRECT(“C”&ROW(C5:C13))))
- SUBTOTAL関数で、C5~C13セルの中でフィルタによって表示されているセルをカウントすることができます。1つめの引数に「3」を設定しているのでCOUNTA関数が使用されています。
- SUBTOTAL関数は、SUM関数やCOUNTIF関数とは異なり、フィルタによる非表示のセルを除いてセルのカウントや合計を計算することができます。
- SUMPRODUCT関数内での結果は、配列[0, 0, 1, 1, 1, 1, 1, 1, 1]が返されます。
数式の結果は、[1, 0, 0, 1, 1, 0, 0, 1, 0] と [0, 0, 1, 1, 1, 1, 1, 1, 1] を乗算して、結果 [0, 0, 0, 1, 1, 0, 0, 1, 0] すなわち「3」を返します。
上記の数式をつかうことで、フィルタ後の結果から検索値(例では”A001″)のデータをカウントすることができます。
以上、COUNTIF関数の使用には向かない、フィルタ後の結果を条件でカウントする方法でした。
コメント