重複した項目のカウント(条件がもう一つあります)

質問

A　 　あ
B　 　あ
A　 　い
B　 　う
C　 　あ
B　 　い
B　 　あ
C　 　あ
A　 　い

↑のようなセルがあったとします。

これを、左側の項目ごとに、重複を無視して右側の項目の数を数えたいです。

例えば、
Aについては"あ"と"い"の2つだから2、
Bについては"あ"と"い"と"う"で3、
Cについては"あ"だけなので1

という具合です。

補助列を使ったりマクロで数えたりすればできるのですが、
関数のみを使ったやり方はないでしょうか。

※ある範囲内で重複を無視してカウントするだけなら
　=SUMPRODUCT(1/SUBSTITUTE(COUNTIF(範囲,範囲),0,0))
　でできました。

　これを使って、Aについて、Bについて、Cについての条件も
　加えようとしましたが、
　やり方がわかりません。

回答No.5

imogasi

例データ
A2:A12（第１行は空白とする）
１セルに「A　 　あ」のように値が入っているとする。A,B列に分かれて入っているのではないということ）
A　 　あ
B　 　あ
A　 　い
B　 　う
C　 　あ
B　 　い
B　 　あ
C　 　あ
A　 　い
A　 　う
B　 　え
Aの行について
=SUM((LEFT(A2:A12,1)="A")*(COUNTIF(OFFSET($A$2,0,0,ROW(A2:A12)-1),A2:A12)=1)*1)
と入れてSHIFT,CTRL,ENTERの３つのキーを同時押しする。
配列数式。
結果　３
ーー
Bの列については、同じく
=SUM((LEFT(A2:A12,1)="B")*(COUNTIF(OFFSET($A$2,0,0,ROW(A2:A12)-1),A2:A12)=1)*1)
と入れてSHIFT,CTRL,ENTERの３つのキーを同時押しする。
結果　　４
配列数式に関心があるものなので、やってみた。
配列数式はSUMPRODUCT関数と双対的のようなので、そちらで出来るかも。
多数例で正しいかチェックして無いのでよろしく。

回答No.4

maron--5

◆こんな方法はいかがでしょう
E1=COUNT(INDEX(1/(MATCH(D1&$B$1:$B$10,$A$1:$A$10&$B$1:$B$10,0)=ROW($1:$10)),0))
★下にコピー

★「A」限定ならば、
=COUNT(INDEX(1/(MATCH("A"&$B$1:$B$10,$A$1:$A$10&$B$1:$B$10,0)=ROW($1:$10)),0))

お礼

SUMPRODUCTを使わなくてもできるんですね！

大変参考になります。
ありがとうございます。

回答No.3

sige1701

こんな感じでは
=SUMPRODUCT((MATCH(A1:A9&B1:B9,INDEX(A1:A9&B1:B9,),0)=ROW(A1:A9))*(A1:A9="A"))

お礼

ありがとうございます。
まさに求めていた回答です。

ちょっとまだ私の知識ではその式の意味までは理解できないですが、
各関数について調べて理解しようと思います。

SEか何かをされているのでしょうか？

回答No.2

noname#204879

A　　 B　　 C　　 D　　E　 F　　　　 G
1　 fld1　fld2　fld3　fld4　　 fld1　　fld4
2　 A　　 あ　　A_あ　1　　　　A　　　　　　 2
3　 B　　 あ　　B_あ　0.5　　　B　　　　　　 3
4　 A　　 い　　A_い　0.5　　　C　　　　　　 1
5　 B　　 う　　B_う　1
6　 C　　 あ　　C_あ　0.5　　　合計/fld4
7　 B　　 い　　B_い　1　　　　fld1　　　 合計
8　 B　　 あ　　B_あ　0.5　　　A　　　　　　 2
9　 C　　 あ　　C_あ　0.5　　　B　　　　　　 3
10　A　　 い　　A_い　0.5　　　C　　　　　　 1

C2: =A2&"_"&B2
D2: =1/COUNTIF(C$2:C$10,C2)
G2: =SUMPRODUCT((A$2:A$10=F2)*(D$2:D$10))

参考までに、範囲 F6:G10 には範囲 A1:D10 を[データ範囲]とする[ピボットテーブルレポート]による結果を示しておきました。

お礼

補助列を使わないやり方はsige1701さんのやり方でできました。

mike_gさんのやり方もとても参考になりました。
ありがとうございました。

補足

ありがとうございます！

教えていただいた上記の方法でできました。

C2、D2の補助列を使わずに、
一気に一行だけで関数を組み合わせてやることは
可能でしょうか？

回答No.1

tom04

こんばんは！
参考になるかどうか判りませんが・・・

↓の画像のように表を作ってみました。

作業列としてA列を3回挿入しています。

A2セルは
=IF(COUNTIF($D$2:D2,D2)=1,ROW(A1),"")

B2セルは
=IF(C2="","",IF(COUNTIF($C$2:C2,C2)=1,ROW(A1),""))

C2セルは
=D2&E2

として、A2～C2セルを範囲指定した後に、オートフィルで下へコピーします。
（数式は1000行目まで対応できるようにしていますので、1000行くらいまでコピーしても構いません）

そして、G2セルに
=IF(COUNT($A$2:$A$1000)>=ROW(A1),INDEX($D$2:$D$1000,SMALL($A$2:$A$1000,ROW(A1))),"")

H2セルに
=IF(G2="","",SUMPRODUCT(($D$2:$D$1000=G2)*($B$2:$B$1000<>"")))

という数式を入れ、G2・H2セルを範囲指定の後、これもオートフィルで下へずぃ～～～！とコピーすると
画像のような感じになります。
（新しくデータが増えても対応できるようにしています）

作業列が多すぎて目障りであれば作業列を「表示しない」にしてもOKかと思います。

以上、参考になれば幸いですが、
少し手間がかかりますので
他に良い方法があれば読み流してくださいね。m(__)m

お礼

とても丁寧に答えていただきありがとうございました。

分かりやすかったです。