こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

こんばんは。お世話になります。
Excelで、データの種類数をかぞえたいのですが、いくつかのグループがあり、そのグループごとのデータの種類数をカウントしたいと考えています。
(データは数字のみ、空白もありますが、空白はデータ数として採用しない)
~データ~
  A         B   
1 グループ     データ
2   a          10
3  a           5
4   a
5   b           4
6   b           4 
7   b           
8  b           
9  c           ・
10  c           ・
・    ・           ・
・   ・
・   ・


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


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

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

投稿日時 - 2010-12-06 22:38:40

QNo.6367676

すぐに回答ほしいです

質問者が選んだベストアンサー

例示のレイアウトで、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))

投稿日時 - 2010-12-07 00:32:55

お礼

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

投稿日時 - 2010-12-20 21:12:05

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

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

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

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

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

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

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

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

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

=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),"")))

投稿日時 - 2010-12-07 07:13:29

お礼

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

投稿日時 - 2010-12-20 21:13:12

ANo.1

こんばんは!
↓の画像のように作業用の列を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

投稿日時 - 2010-12-06 22:59:09

お礼

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

投稿日時 - 2010-12-20 21:10:38

あなたにオススメの質問