excel グループごとにデータの種類数を数えたい

質問

こんばんは。お世話になります。
Excelで、データの種類数をかぞえたいのですが、いくつかのグループがあり、そのグループごとのデータの種類数をカウントしたいと考えています。
（データは数字のみ、空白もありますが、空白はデータ数として採用しない）
～データ～
　　A　　　　　　　　　B　　　
１　グループ　　　　　データ
２　　　a　　　　　　　　　　10
３ 　a　　　　　　　　　　　5
４ 　 a
５ 　 b　　　　　　　　　　　4
６ 　 b　　　　　　　　　　　4　
７ 　 b　　　　　　　　　　　
８ 　b　　　　　　　　　　　
９ 　c　　　　　　　　　　　・
１０　 c　　　　　　　　　　　・
・　 　 ・　　　　　　　　　　　・
・ 　 ・
・ 　 ・

～集計（求めたい結果）～
グループ　　　　データの種類数
a　　　　　　　　　　　2　　　　　　
b　　　　　　　　　　　1
c　　　　　　　　　　　・　
　・　　　　　　　　　　　・
　・　　　　　　　　　　　・

単純にB列のデータ種類を数えるには、
{=SUMPRODUCT(IF(B2:B・・・<>"",1/COUNTIF(B2:B・・・,B2:B・・・),0))}
でできるというところまでは分かったのですが、「グループ別に」というところでつまづいています。
なお、Excel2007です。

恐縮ですが、ご教示願います。

回答No.2

MackyNo1 · ベストアンサー

例示のレイアウトで、D2セル以下にグループ名が入力されているなら、E2セルに以下の式を入力して下方向にオートフィルします。

=SUMPRODUCT(($A$2:$A$100=D2)*($B$2:$B$100<>"")*(MATCH($A$2:$A$100&$B$2:$B$100,$A$2:$A$100&$B$2:$B$100,)=ROW($A$2:$A$100)-1))

お礼

ありがとうございます。そのものずばり求めることができました。
PCの性能のせいか・・・３０分くらいPCが考え込んでましたが（汗）
なんにしても求められたのでよかったです。

回答No.3

KURUMITO

分かりやすくしかもデータの数が多くなってもSUMPRODUCT関数などのように範囲を指定する必要もなく、また、計算に負担のかからない方法です。
作業列をＣ、Ｄ，Ｅ列に用意します。勿論目障りでしたら列を非表示にすればよいでしょう。
1行目には項目名があるとして2行目から下方にデータがあるとします。
Ｃ2セルには次の式を入力します。

=IF(B2="","",A2&"/"&B2)

Ｄ2セルには次の式を入力します。

=IF(C2="","",IF(COUNTIF(C$2:C2,C2)=1,1,0))

Ｅ2セルには次の式を入力します。

=IF(B2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(E$1:E1)+1,""))

Ｃ2セルからＥ2セルを選択してそれらの式を下方にオートフィルドラッグします。

最後にお求めのグループ名とデータの種類ですがＧ2セルとＨ２セルから下の行に表示させることにして、Ｇ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX($E:$E),"",IF(COLUMN(A1)=1,INDEX($A:$A,MATCH(ROW(A1),$E:$E,0)),IF(COLUMN(A1)=2,SUMIF($A:$A,$G2,$D:$D),"")))

お礼

作業用列をつかったほうがわかりやすい場合もありそうですね・・・。
今後の参考にさせていただきたいと思います。
ありがとうございました。

回答No.1

tom04

こんばんは！
↓の画像のように作業用の列を3列使っているので参考にならなかったら読み流してください。

作業列1のC2セルは
=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")
作業列2のD2セルは
=A2&B2
作業列3のE2セルは
=IF(COUNTBLANK(A2:B2),"",COUNTIF($D$2:D2,D2))
という数式を入れ、B2～E2セルを範囲指定し、E2セルのフィルハンドルで下へオートフィルでずぃ～～～！っとコピーしています。

そして、G2セルに
=IF(COUNT(C:C)<ROW(A1),"",INDEX(A:A,SMALL(C:C,ROW(A1))))
H2セルに
=IF(G2="","",SUMPRODUCT(($A$2:$A$1000=G2)*($E$2:$E$1000=1)))
という数式を入れ、G2・H2セルを範囲指定しH2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
この程度しか思いつきませんでした。
他に良い方法があればごめんなさいね。m(__)m

お礼

お礼が遅くなり申し訳ありません。
作業用の列とは気づきませんでした！
ありがとうございます。参考になりました。