[Excel2000]オートフィルタ後の種別件数

質問

お世話になります。
「オートフィルタ後」の「データ種別件数」取得についての質問です。

------
前置き
------

「データ構成」
・B列に地域（北or南）
・C列に店舗名（店舗A～Dで、地域「北にも南にも」存在）
・D列に商品
となっています。

各地域数は
=SUMPRODUCT((B10:B19<>"")/COUNTIF(B10:B19,B10:B19&""))
といったようにして、「2種類の地域が存在している」というのを認識できています。
・店舗数
・商品数
についても、関数の各列番号を変更した状態で、取得できています。

----
本題
----
上記の「各種別」を、オートフィルタと連動した抽出内容に沿った数を、Excel関数のみで実現できないでしょうか？
VBAを利用すると、容易に取得できるのはわかりますが、独自の関数を作成しないで実現したいのです。

（業務アプリで出力するExcelファイルのため、ファイル起動時にセキュリティの問合せが出てしまうような作りが不可なのです。）

また客先は古いOffice環境も存在しているため、Excel2000でも利用可能な標準組み込み関数のみで実現したいところではありますが、Excel2003以降でのみでの動作可能な案をいただけた場合でも、こちら側で動作できない部分をExcel2000でも動作可能に様にコンバートをするつもりです。

Excel関数にはかなり疎いため、そもそもVBAを利用しないで実現可能かどうかもわかりません。
（無理であれば、「無理」という事だけでも知りたいです。）

どなたかご教授いただけませんでしょうか？
宜しくお願いいたします。

回答No.2

MackyNo1 · ベストアンサー

例示のレイアウトなら、B3セルに以下の式を入力して、Ctrl+Shift+Enterで確定して、右方向にオートフィルしてください。

=COUNT(1/((MATCH(IF(SUBTOTAL(3,INDIRECT("b"&ROW(10:50))),B$10:B$50,"XXX"),IF(SUBTOTAL(3,INDIRECT("b"&ROW(10:50))),B$10:B$50,""),0)=ROW($B$10:$B$50)-9)))

２つ目の質問ですが、フィルタ結果が「１」の時とは、B,C,D列のすべての列のフィルタ結果が１種類という意味でしょうか？

お礼

MackyNo1さん、ご回答ありがとうございます。

正直「配列数式」と[Ctrl]+[Shift]+[Enter]のショートカットという存在をはじめて知りました。

私はWin95の頃からExcelに結構触れていて、多少の心得があると自負していたのですが、、、
「自分ってExcelの知識はまだまだなんだな。それにしても奥が深い・・・」
と率直に感じると同時に、出力された期待通りの結果に感動さえ覚えました。

本当にありがとうございます。

二つ目の質問についてですが、これは別にスレを立てようと思っていますので、そちらでご協力いただけたらと^^;;;

あらかじめお伝えしておきますと
質問時に貼り付けた画像のデータに、「商品=いちご」でフィルタを行うと
・Bが1件（北）
・Cが2件（店舗Bと店舗D）
・Dが1件（いちご）
となります。

このとき
・B5セル→「北」
・C5セル→《ブランク》
・D5セル→「いちご」
としたいのです。

・・・と余計な事も言ってしまいましたが、、、

何はともあれ、この度は本当にありがとうございました！

回答No.1

end-u

こんにちは。
取り敢えず叩き台として。

作業エリアを使えるなら比較的簡単にできそうです。
ただしカウント対象列全てに対して作業エリアが必要な案なので
大量データには向いてないかもしれません。

例えば
F10セルに =SUBTOTAL(3,B10)　'（表示フラグ的な使い方です）
G10セルに =IF($F10,B10,"")　'G10:I19セルへコピー
フィルタ抽出されていたらB:D列の各列データを引っ張ってくるようにしておけば
現状のSUMPRODUCT関数の参照先を変えるだけで良いかと思います。（作業列幅は非表示でも可）
=SUMPRODUCT((G10:G19<>"")/COUNTIF(G10:G19,G10:G19&""))

１件の時だけ該当データを表示させるなら、やはり表示フラグを見に行って
B5セルに =IF(B3=1,INDEX(B10:B19,MATCH(1,$F10:$F19,0)),"")　'など。

お礼

end-uさん、ご回答ありがとうございます。

>作業エリアを使えるなら
私も作業エリアを利用する事を検討しておりました。

#2のMackyNo1さんが、作業エリアを利用しない方法を教えてくださいましたので、今回はそちらの方法で行きたいと思います。

しかしながら「作業エリア利用場合」をいざ一人で考えて実現しようとしても、どのようにしたら実現可能なのかわからなかったので、end-uさんの細分化された手法は大変勉強になりました。

この度は本当にありがとうございました！