[[20220607200122]]　『可視セル内で一意の値の種類をカウントする』（Ma）　ページの最後に飛ぶ

『可視セル内で一意の値の種類をカウントする』（Ma）

A列には「大分類」、B列には「小分類」が記載されています。

「大分類」列を特定の値でフィルタしたとき、「小分類」列に表示される項目数をカウントしたいのですが、
「小分類」列の中には重複する項目が含まれているため、一意の値のみ種類をカウントしたいです（"重複の削除"した後のデータの個数を数えるイメージ）。

検索するとSUBTOTALやSUMPRODUCTを使用する方法が見つかるのですが、
自分の場合に当てはめられず・・
良い導き方はないでしょうか。

以下、例です。
　

     A            B
1 (空セル) （求めたい関数）
2 大分類　　　　小分類
3 やさい　　　 だいこん
4 やさい　　　 にんじん
5 やさい　　　 だいこん
6 やさい　　　 　なす
7 くだもの　　　いちご
8 くだもの　　　りんご
9 くだもの　　　いちご

2行目を「やさい」でフィルタしたときはB1セルに"3"（だいこん、にんじん、なす）、
同じく「くだもの」でフィルタしたときは"2"（いちご、りんご）
を返す関数をB1セルに入力したいです。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 _|___A____|___B____|___C____|___D____|___E____
 1|        |       5|        |        |        
 2|大分類  |小分類  |        |        |        
 3|やさい  |だいこん|       1|だいこん|       1 
 4|やさい  |にんじん|       1|にんじん|       1
 5|やさい  |だいこん|       1|だいこん|       2
 6|やさい  |なす    |       1|なす    |       1
 7|くだもの|いちご  |       1|いちご  |       1
 8|くだもの|りんご  |       1|りんご  |       1
 9|くだもの|いちご  |       1|いちご  |       2

 C3 =SUBTOTAL(3,B3)
 D3 =IF(C3,B3)
 E3 =IF(C3,COUNTIF(D$3:D3,D3))

 B3 =COUNTIF(E:E,1)

 これをC:E列を使わずイッパツで実現する方法...　って事ですか。。。

（白茶） 2022/06/07(火) 21:10

 =SUM((MATCH(IF(SUBTOTAL(3,INDIRECT("A"&ROW(3:9))),B3:B9,""),IF(SUBTOTAL(3,INDIRECT("A"&ROW(3:9))),B3:B9,""),0)=ROW(3:9)-2)*SUBTOTAL(3,INDIRECT("A"&ROW(3:9))))
 と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定ではどうだろうか？
（ねむねむ） 2022/06/07(火) 21:20

 作業列を使ってもいいのなら
 例）C列

 C3 =IF(SUBTOTAL(3,A3),IF(COUNTIFS($A$3:A3,A3,$B$3:B3,B3)=1,1,""),"")
 下コピー

 B1 =COUNT(C:C)

 実際、どんな値なのか知りませんけど
 小分類の項目が複数の大分類に含まれることがないのなら
 例）だいこんは「やさい」だけで「くだもの」にはない
 　　
 C3 =IF(SUBTOTAL(3,A3),IF(COUNTIF($B$3:B3,B3)=1,1,""),"")

 B1 は同じ

 ■フィルターオプション（フィルターの詳細設定）
 「重複するレコードは無視する」で抽出して SUBTOTAL でカウントでもいいかも

 以上
（笑） 2022/06/07(火) 21:27

フィルタオプションで「重複するレコードを無視」で絞り込み、
=SUBTOTAL(103,対象列）のようにカウントすればよいのでは？
（見出し数(1?)を控除する必要ありますが）
それでは満足できないということでしたか。

#　かぶりましたが、そのまま。

（γ） 2022/06/07(火) 21:48

103でなくても3でよかったのでした。
（γ） 2022/06/07(火) 21:49

皆様ありがとうございます。初めて投稿したのですがこれだけの回答を頂き感動してます、、

作業列を入れて大丈夫でしたので、一番最初に回答下さった方のやり方で解決しました。

わたしの知見が足りず、皆さまの回答を理解できていないところもあるのですが;;
大変助かりました、ありがとうございました。
（Ma） 2022/06/08(水) 20:54

コメント返信：

[ 一覧(最新更新順) ]

[[20220607200122]] 『可視セル内で一意の値の種類をカウントする』（Ma） ページの最後に飛ぶ

[[20220607200122]]　『可視セル内で一意の値の種類をカウントする』（Ma）　ページの最後に飛ぶ