教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

Excel  >> データベース

Excel2010-2016: フィルタされたデータをCountIfで数えたい

 

 

 

 

 


 

 

 

「フィルタ結果にCOUNTIFを使いたい」というご質問をいただくことがありますので、関数で返す方法をご紹介します。

※ あまり大きなデータには向きません(動作が重くなることがあります)

※ なるべく複数フィルタ、ピボットテーブル、Sumproduct関数、Countifs等を使いましょう。どうしてもフィルタ結果にCountifしたい、といった場合の方法です。


さて、フィルタ結果に対しCountIfで数える方法です。例えば…

 

◆ 20歳以上を数える → 4

=Countif(C:C,">=20") … 4

更に男のみフィルタで抽出 → 4のままで表示される。3を返したい

=Countif(C:C,">=20") → 4

 

式の対象をフィルタで抽出されているデータのみとしたい

VBAで記述する方法等もありますが、ここでは関数のみで簡単な方法をご紹介します。

 

 

◎ フィルタしても常に20歳以上をカウントできるようにする

=SUMPRODUCT((C2:C12>=20)*(SUBTOTAL(3,INDIRECT("A"&ROW(A2:A12)))))

★Subtotal(3) → CountA

 

 -解説-

Sumproduct((C2:C12>=20) →  20以上ならTrue1 → 1,1,1

SUBTOTAL(3,INDIRECT("A"&ROW(A2:A12))) → 表示されているセルを1つずつカウント → 1,1,1,1,1,1,1,1

 

結果として合致した1をカウントする→3
表示されているセル   20以上 結果
1 × 1 1
1 × 0 0
1 × 0 0
1 × 1 1
1 × 0 0
1 × 1 1
1 × 0 0
1 × 0 0

★ SUBTOTAL(3,範囲)では表示セル全てをカウントしてしまいます。

★ SUBTOTAL(3,INDIRECT(範囲)も表示セル全てをカウントしてしまいます。

★ SUBTOTAL(3,INDIRECT("A"&ROW(A2:A12))のように指定すると、1セルずつを表示されている範囲として数えるので全てに1,1,1,1が返され、結果的に合致したセルのみカウントできます。

      

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows