集計

質問

お世話になります。
表の集計にCountifで、１の数をカウント又は２だったり３だったり変数をカウントしたのですが、困っているのは、オートフィルタをかけている表でCountif関数を使用しても非表示の数字もカウントしてしまう事です。表示されている部分だけを数えたいのですがどうやったらいいのでしょうか？お教え下さい。

回答No.3

noname#70958 · ベストアンサー

単に「表示されているセルを数える」のではなくて
「表示されているセルについて特定の数字を数える」のかと思ったのですが…。

もし私の解釈が正しければ、
例えば、データが２行目以下にあり、C列の値が5である行をカウントする場合。
----------------------------------------------
●甲案：　オーソドックスに作業列を使って。

　１．すべての行を表示した状態で、適当な列（仮にD列とします）の2行目を
　　　=SUBTOTAL(3,C2)
　　として下方にフィルしておきます。

　２．そのうえで、任意のセルを
　　=SUMPRODUCT((C2:C99=5)*D2:D99)
　　とすれば、オートフィルタをかけた際に、
　　「表示されている行で、かつ、C列が5である行」の数が得られます。
----------------------------------------------
●乙案：　ややトリッキーですが数式一発で。

　任意のセルを
　　=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2,ROW(C2:C99)-2,))*(C2:C99=5))
　とすれば、直接結果を得ることができます。

　※OFFSET(C2,ROW(C2:C99)-2,)　の　C2　の部分は範囲の先頭行を指定し、
　　ROW(C2:C99)-2　の　-2　の部分は先頭行の行番号を指定します。
----------------------------------------------
●丙案：ユーザー定義関数を使って。
　[Visual Basic]カテゴリでのご質問なので、やっつけですが一応。

Function Sample(ByVal myRng As Range, ByVal myKey As Variant) As Long
　myRst = 0
　For Each myCel In myRng
　　If Application.Subtotal(3, myCel) = 1 And myCel.Value = myKey Then
　　　myRst = myRst + 1
　　End If
　Next myCel
　Sample = myRst
End Function

任意のセルを
　=Sample(C2:C99,5)
とすれば、結果が得られます。
----------------------------------------------
いずれもExcel2003で動作確認済。以上ご参考まで。

お礼

ありがとうございました。
大変参考になりました。

回答No.2

onlyrom

もしかして、ＶＢＡですか？
なら、

Kensu = WorksheetFunction.Subtotal(3, Range("A2:A50"))

とか

Kensu = WorksheetFunction.Subtotal(3, Range("A1").CurrentRegion.Columns(1)) - 1

など。。。

お礼

ありがとうございました。
参考になりました。

回答No.1

onlyrom

そういうときは、SUBTOTAL関数を使います。

例えば、全て表示されてる時点で、データが、Ａ２～Ａ５０に入力されていたら

　　=SUBTOTAL(3,A2:A50)

としておくと、フィルターが掛かると表示されてる件数が計算されます。

それから、わざわざ数えなくてもフィルターがかかった時点で
画面下部のステータスバーの左に、抽出数が表示されるはずです。