[[20081125004522]]　『オートフィルタとcountの組み合わせで種類数を出ａx（Yuya）　ページの最後に飛ぶ

『オートフィルタとcountの組み合わせで種類数を出したい』（Yuya）

 初めて質問させていただきます。

  　　Ａ　　　　Ｂ
 １　１００　　ＥＥ
 ２　２００　　ＥＡ
 ３　１００　　ＥＳ
 ４　２００　　ＥＥ
 ５　１００　　ＥＡ
 ６　　２　　　　３

上のようにデータがあり、Ａ６、Ｂ６には、種類数をカウントできるよう、

 =sumproduct(1/countif(A1:A1,A1:A1))のように式が入れてあります。
 これで種類数は出せたのですが、オートフィルタにかけたときに、
 表示行のみの種類数を出すときにはsubtotalと連動させないとと思うのですが、
 自分で組み立てができません。

 　　　Ａ　　　　Ｂ
 １　１００　　　ＥＥ
 ３　１００　　　ＥＳ
 ５　１００　　　ＥＡ
 ６　　１　　　　　３

 このように、フィルタを１００でかけたときに、きちんと種類数が出るようにしたい
 のです。 Sumproductで、100*EE などで出すやり方はわかりますが、
 Ｃ列以降にもたくさんのデータがあるため、どうしてもＢ列はＢ列での種類数を出したいのです。
　 表示行のみで種類数をカウントするやり方をご存知の方、
　是非教えてください！

 こんにちは～♪

 時間が無いので。。

 A列　=IF(COUNT(A2:A6)<>SUBTOTAL(2,A2:A6),1,SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6)))

 B列 =COUNT(IF(SUBTOTAL(5,A2:A6)&B2:B6=A2:A6&B2:B6,ROW(1:5)))

 こちらは配列数式です。。。

 ふたたび～です。。。♪

 B列の式は、違ってましたネ！！

 いよいよ時間が無いので。。
 他の方の回答をお待ちください。。。

 。。。Ms.Rin～♪♪

 こんにちは～♪

 やっと、時間が空きました～！！。。。♪

 回答が付かなかった様ですので。。。

 B列の式は、こんな式になりました。。
 A列のフィルターをかけない時は、空白にしてあります。

 =IF(COUNT(A2:A6)<>SUBTOTAL(2,A2:A6),COUNT(0/(IF(A2:A6=SUBTOTAL(5,A2:A6),
MATCH(SUBTOTAL(5,A2:A6)&B2:B6,A2:A6&B2:B6,0)=ROW(1:5),))),"")

 配列数式になります。。

 Ctlr＋Shift＋Enterキーで式を確定して下さい。。

 A列の式もB列の式の場合も、1行目が見出し行で
 2行目～6行目のデータ範囲になっています。

 ★それから、２つの式はA列をオートフィルタにかけた場合の式です。

 ご参考にどうぞ。。。

 。。。Ms.Rin～♪♪

 Ms.Rinさん、お忙しい中、ご回答ありがとうございます。
 上の、ご解答いただいたものは、Ａ列をフィルタにかけた場合ということですが、
 Ｃ列以降にもたくさんデータがあるため、フィルタを５～６列同時にかけることが
 あります。そのため、Ａ列、Ｃ列、Ｄ列、Ｆ列、Ｈ列でフィルタで絞ったときの
 Ｂ列の種類数は？というように、列ごとに、表示してある行の種類数を出したい
 のですが、関数で可能なのでしょうか？もしお分かりになれば教えてください！

　Yuya

 おはようございま～す。。。♪

 チョットカン違いしてたので。。。

 この式でいかがでしょう。。。？

 A15セルへ
 =COUNT(0/(MATCH(IF(SUBTOTAL(3,INDIRECT("A"&ROW(2:7))),A2:A7,),
IF(SUBTOTAL(3,INDIRECT("A"&ROW($2:$7))),A2:A7,""),0)=ROW(1:6)))

 配列数式です。。
 Ctlr＋Shift＋Enterキーで式を確定して下さい。。
 式が{  }で囲まれます。

 ★この式をA15セルあたりに入れて、H15セルまで右にコピーします。。

 ★この式は、データ行が2行目～7行目になってます。

 ★それぞれの列の数える種類が、数値か文字列かわかりませんので
 　SUBTOTALは、COUNTAを使いました。

 これでA列～H列までどの列でフィルタを掛けても
 それぞれの列の種類をカウント出来ると思います。

 (注)
  A15セルへ式を入れたのは
  Excel2003ですと、式を入れる行を最終データ行から8行位空けないと
  フィルタを掛けた時、結果が見えなくなってしまいましたので。
  見出し行を2行目以下にして、その上の行に式を入れてもいいですネ！！。。

 。。。Ms.Rin～♪♪

検索していてここへたどり着きました。
質問させてください。

 =COUNT(0/(MATCH(IF(SUBTOTAL(3,INDIRECT("A"&ROW(2:7))),A2:A7,),
IF(SUBTOTAL(3,INDIRECT("A"&ROW($2:$7))),A2:A7,""),0)=ROW(1:6)))
は、データ行が2～7行ということで確かに抽出時でも列のデータ種類を計算できました。

データ行を8～200とする場合どのようにすれば良いのでしょうか？
ちなみにD列を自分なりに加工してみたのですがどうもうまくいきません。
=COUNT(0/(MATCH(IF(SUBTOTAL(3,INDIRECT("A"&ROW(8:200))),D8:D200,),
IF(SUBTOTAL(3,INDIRECT("A"&ROW($8:$200))),D8:D200,""),0)=ROW(1:201)))

 D列なら、この様に変更するのではないでしょうか？

 =COUNT(0/(MATCH(IF(SUBTOTAL(3,INDIRECT("D"&ROW(8:200))),D8:D200,), IF(SUBTOTAL(3,INDIRECT("D"&ROW(8:200))),D8:D200,""),0)=ROW(1:193)))
 　Ctlr＋Shift＋Enterキーで式を確定

 この案で解決しない場合は新規質問にしてください。
 その際は、ここを読んでからにしてください。（データサンプルも添えてください）
 　　　　　　↓
 　　　初めての方へ
https://www.excel.studio-kazu.jp/wiki/excelboard/

 (半平太) 2009/07/20 22:59

すみません。
解決致しました。Rowの範囲が間違ってました。
次回からはちゃんとルールに従います。
ありがとうございました。
maya

ほぼ初心者です。
検索でこちらに辿りつきました。

=COUNT(0/(MATCH(IF(SUBTOTAL(3,INDIRECT("D"&ROW(8:200))),D8:D200,), IF(SUBTOTAL(3,INDIRECT("D"&ROW(8:200))),D8:D200,""),0)=ROW(1:193))) 　Ctlr＋Shift＋Enterキーで式を確定

フィルタ後の種別カウントを上記式で結果を得ることができました。
ありがとうございます。

でも、空白セルまで種別カウントしてしまいます。
COUNTA　でも上手くいきません。。

どなたかご教授ください。
よろしくお願いします。

さとし。

 空白セルが、本当に空白セルなのか疑問ですが
 レスもつかない様ですので
 新しく質問をされた方が良いと思います。

 ↓こちらからどうぞ。
https://www.excel.studio-kazu.jp/cgi-bin/kazuwiki2.cgi?mycmd=new

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20081125004522]] 『オートフィルタとcountの組み合わせで種類数を出ａx（Yuya） ページの最後に飛ぶ

[[20081125004522]]　『オートフィルタとcountの組み合わせで種類数を出ａx（Yuya）　ページの最後に飛ぶ