抽出した表でデータをカウントしたいのですが・・・

質問

タイトルにあるとおり、あるデータをオートフィルで抽出した状態で、その抽出結果からデータをカウントしたいのです。

以下のような表があったとします。

　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　　Ｆ　　Ｇ　　Ｈ　　Ｉ
１　　○　　　　　　　　　　　○　　○　　○
２　　　　　○　　○　　　　　○　　　　　　　　○
３　　　　　　　　○　　○　　○　　○
４　　○　　○　　　　　○　　　　　○
５　　　　　○　　　　　　　　　　　　○　　○　　　　○
６　　　　　○　　○　　　　　○　　　　　　　　○
７　　○　　　　　　　　○　　　　　　　　○　　　　　○
８　　　　　○　　○　　　　　○　　　　　　　　○
９　　○　　　　　　　　○　　○　　　　　○

この表では、仮に各行○が4つとして、ランダムに各列に○が振り分けられています。
Ａ列・4個、Ｂ列・5個、Ｃ列・4個、Ｄ列・4個、Ｅ列・6個、Ｆ列・4個、Ｇ列・4個、Ｈ列・3個、Ｉ列・2個
という状態です。

　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　　Ｆ　　Ｇ　　Ｈ　　Ｉ
１　　○　　　　　　　　　　　○　　○　　○
２　　　　　○　　○　　　　　○　　　　　　　　○
３　　　　　　　　○　　○　　○　　○
４　　○　　○　　　　　○　　　　　○
５　　　　　○　　　　　　　　　　　　○　　○　　　　○
８　　　　　○　　○　　　　　○　　　　　　　　○
９　　○　　　　　　　　○　　○　　　　　○

ある条件でオートフィルによって、抽出した結果が上の表です。
今回の例では6,7行が消えた状態だったとします。
それぞれ、Ａ列・3個、Ｂ列・4個、Ｃ列・3個、Ｄ列・3個、Ｅ列・5個、Ｆ列・4個、Ｇ列・3個、Ｈ列・2個、Ｉ列・1個
と、いう結果になりました。

このような状態になった表で最下段、この場合9から上に向かって3行分の○をカウントするような方法、または便利な関数はありますでしょうか？
今回の例ですと、最下段から、3行分、5、8、9のそれぞれの列の○をカウントするという感じです。
Ａ列・1個、Ｂ列・2個、Ｃ列・1個、Ｄ列・1個、Ｅ列・2個、Ｆ列・1個、Ｇ列・2個、Ｈ列・1個、Ｉ列・1個
これが今回の例における求めたい結果です。
抽出してない状態でしたら、ＯＦＦＳＥＴ関数でいけるのですが、抽出した状態からだと私のスキルでは解決できません。
どなたか、ご存知の方ご教示よろしくお願いします＾＾；

回答No.3

noname#204879

A　　B　　C　　D　　E　　F　　G　　H　　I　　J
1　 ZZ1　ZZ2　ZZ3　ZZ4　ZZ5　ZZ6　ZZ7　ZZ8　ZZ9　REF
2　 ○　　　　　　　　　○　 ○　 ○　　　　　　 X
3　　　　○　 ○　　　　○　　　　　　 ○　　　　X
4　　　　　　 ○　 ○　 ○　 ○　　　　　　　　　X
5　 ○　 ○　　　　○　　　　○　　　　　　　　　X
6　　　　○　　　　　　　　　○　 ○　　　　○
7　　　　○　 ○　　　　○　　　　　　 ○
8　 ○　　　　　　 ○　　　　　　 ○　　　　○　 X
9　　　　○　 ○　　　　○　　　　　　 ○　　　　X
10　○　　　　　　 ○　 ○　　　　○　　　　　　 X
11　　4　　5　　4　　4　　6　　4　　4　　3　　2

A11: =SUBTOTAL(3,A2:A10)

補足

mike_g 様　ご回答ありがとうございます＾＾

今回ご提示いただきました　A11: =SUBTOTAL(3,A2:A10)ですが　
最下段より3行のみをカウントする場合に使用できますでしょうか？

今回の例では、６、７行目以外をオートフィルによって、抽出しているわけなのですが、ケースによって抽出する内容が変わってきます。
例えば、9行目だけがフィルタによって削除された場合
6、7，8行目のみをカウントしたいわけです。
実際の表では9行目以降は毎回増えていきます。
ＳＵＢＴＯＴＡＬ関数＋最下段から任意の行数分カウント
と、いう感じでの関数を考えています＾＾；
わかりにくい説明で申し訳ないのですが、改めてよろしくお願いします

回答No.2

esupuresso

6.7行目は非表示の状態と言うことで宜しいでしょうか？

取り合えず非表示の行をカウントしない「SUBTOTAL」関数を使ってみます

セル【A10】に　「=SUBTOTAL(103,A5:A9)」を入れます
次にセル【A10】に入れたSUBTOTAL関数をセル【I10】までフィル機能を使い関数のコピーをして出来上がりです

尚、関数内の「103」は「COUNTA」を意味しますが他にも色々ありますので参考にしてみて下さい

行の再表示をされた場合は答えの数値も自動的に変わります

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/sum.htm#subtotal

補足

esupuresso 様　ご回答ありがとうございます＾＾

今回の例では６，７行目を非表示の状態です。
ご提示いただきました、「=SUBTOTAL(103,A5:A9)」
の場合ですと、Ａ５～Ａ９限定でのカウントになりそうですが
今回の例では、６、７行目以外をオートフィルによって、抽出しているわけなのですが、ケースによって抽出する内容が変わってきます。
例えば、9行目だけがフィルタによって削除された場合
6、7，8行目のみをカウントしたいわけです。
実際の表では9行目以降は毎回増えていきます。
ＳＵＢＴＯＴＡＬ関数＋最下段から任意の行数分カウント
と、いう感じでの関数を考えています＾＾；
わかりにくい説明で申し訳ないのですが、改めてよろしくお願いします

回答No.1

zap35

SUBTOTAL関数がその目的に合致します。SUBTOTAL関数はフィルタをかけた状態で「表示されているセル」だけを対象に集計することができます

=SUBTOTAL(3,A1:A9)
これは表示されているデータに対してCOUNTA(A1:A9)と等価の動きをします。

従って質問の例では
　フィルタをかけていないときは「4」
　フィルタをかけたときは「3」
を返します

詳しくはSUBTOTAL関数のHELPを見てください。

補足

zap35 様　ご回答ありがとうございます＾＾

ＳＵＢＴＯＴＡＬ関数は確かに便利ではあるのですが
今回の場合ですと、最下段より3行のみをカウントする場合に使用できますでしょうか？

今回の例では、６、７行目以外をオートフィルによって、抽出しているわけなのですが、ケースによって抽出する内容が変わってきます。
例えば、9行目だけがフィルタによって削除された場合
6、7，8行目のみをカウントしたいわけです。
実際の表では9行目以降は毎回増えていきます。
ＳＵＢＴＯＴＡＬ関数＋最下段から任意の行数分カウント
と、いう感じでの関数を考えています＾＾；
わかりにくい説明で申し訳ないのですが、改めてよろしくお願いします