オートフィルタ使用時にCOUNTIFをSUBTOTALのように使いたい。

質問

エクセルのシートでデータ管理をしようと思います。
その時便利なのが、オートフィルタですが、

　　　SUBTOTAL関数「例：=SUBTOTAL(9,A1:A100)」

を設定した場合、フィルタを操作すると、必要な数値が返ってきます。ところがというか当然というか、

　　　COUNTIF関数「例=COUNTIF(B1:B100,"○")

ではだめですよね。どうにかして関連づける方法はないでしょうか。
または、その他に同様な効果が得られる方法はありませんか。

回答No.2

nishi6 · ベストアンサー

回答がありませんね。
質問の意味は、
　　例えば、101行目にSubtotalを置いていればフィルタをかけたときに選択されたものだけが集計される。
　　しかしCountifのように条件付きの集計はできないので代替手段は？
ということでしょうか。

多分、Subtotalの集計方法はフィルタで絞られたものを対象にするので、さらにそれを絞り込むことは難しいと思います。

＞その他に同様な効果が得られる方法はありませんか
ということなので、機能が無ければ作ればいいという事で下記のユーザー定義関数を書いてみました。
フィルタで絞る時は、該当しない行は自動的に非表示になるのでそれを利用しています。

使い方は、COUNTIF(B1:B100,"○") と同じようにsubtotal_countif(B1:B100,"○") とします。

標準モジュールに貼り付けます。
Public Function subtotal_countif(rgSelect As Range, moji As String)
　　Dim rg As Range 'セル
　　Dim cot As Long 'カウンタ

　　For Each rg In rgSelect
　　　　If Rows(rg.Row).Hidden = False Then '表示されている行だけ対象にする
　　　　　　If rg = moji Then
　　　　　　　　cot = cot + 1
　　　　　　End If
　　　　End If
　　Next
　　subtotal_countif = cot
End Function

お礼

ご丁寧かつ専門的な回答ありがとうございました。質問の内容が陳腐（？）で誰も相手にしてくれないのかと心配していました。(;_;)

エクセルを自己流で使っておりましたので、エクセルをドラエモンの４次元ポケットのように思いこんでいて、ついそのような質問を…。
しかし、nishi6さんの回答を見るともしかしてエクセルは…。

ところで、標準モジュールっていうことは、VBAですよね(!?)。参考書片手に頑張ってみます。結果はまた後で連絡します。

＞質問の意味は、
　　例えば、101行目にSubtotalを置いていればフィルタをかけたときに選択されたものだけが集計される。
　　しかしCountifのように条件付きの集計はできないので代替手段は？
ということでしょうか。

その通りです。分かってもらってうれしいです。

回答No.3

nishi6

質問を理解するほうに気が行って、回答に手を抜いてしまいました。すいませんでした。

(1)シートのメニューから、ツール→マクロ→Visual Basic Editor でVBE画面に移ります。
(2)VBEメニューで、挿入→標準モジュールで標準モジュールを挿入します。
(3)そのコードウインドウ（現れた広い場所です）に下記コードをコピーし貼り付けます。
(4)ワークシートに戻り、COUNTIFを表示したかったセルに
　　=subtotal_countif(B1:B100,"○")
と入力します。

＞エクセルをドラエモンの４次元ポケットのように思いこんで・・・・

ワークシートの機能だけでもいろんなことが可能と思います。ただ、関数を組み合わせていったりすると、行入力のため分かりにくさが問題になります。それに、手順を複雑にしてしまうと、手順復元が困難になったり、’あの人しかできない処理’になったりしてしまいます。そのためにVBAを使ったりもしています。

『４次元ポケット的』という意味を『シート上のデータやExcelが扱えるのものに対して』すれば、VBAを使えば、どらえもんになれると思っています。自分は何をしたいかを、はっきりつかむことが大事でしょう。本もたくさんありますし、がんばって下さい。

お礼

手順まで示していただき大変ありがとうございます。
前回の答に基づきやってみました。(;￣Д￣) …で、できました!!
意味はよく分かりませんが、確かに私の意図したとおり関数が働きます。ありがとうございました。

回答No.1

ahsblue

Ｂ列に入る値が、○と空欄だけでしたら
SUBTOTAL(3,B1:B100)
で問題なしです。

Ｂ列に○以外にも文字が入る場合は、隣の列（例としてＣ列）などに非表示列を作って○だけ抜き出してはいかがでしょうか？
そうすれば、上記と同様にSUBTOTAL(3,C1:C100)で対処できます。

非表示列を作るのが嫌な場合はマクロかな～

お礼

ありがとうございました。質問の内容が陳腐（？）で誰も相手にしてくれないのかと心配していました。(;_;)
早速試してみます。結果はまた後で連絡します。