条件で絞込み重複データをカウント：エクセル

質問

同じような質問があるのですが、どーーーーーしても理解できないので再度質問させてください。

下記のデータで、”赤組”の親の数を数えたいのですが、どうしたらいいのでしょうか。
（つまり双子がいる場合は、親は”１”にしたいのです）　　

答えは、山田さん、鈴木さん、中村さんの３人。

　　　クラス　　　　　　　親
　　　　赤　　　　　　　山田
　　　　白　　　　　　　佐藤
　　　　赤　　　　　　　鈴木
　　　　白　　　　　　　佐藤
　　　　赤　　　　　　　山田
　　　　赤　　　　　　　中村
　　　　白　　　　　　　田中

作業列は使用したくなく、関数だけで解決できますでしょうか？
また、エクセルは２００３でも使えるものでお願いしたいのですが・・・

どうぞよろしくお願いいたします。

回答No.6

keithin · ベストアンサー

＞単純に”赤”となっているところを”*赤*”とすればいいのかと思いましたが、上手く行きませんでした

「等しい」の計算では，ワイルドカードを使うことはできません。
例えば
=IF(A1="*赤*","含む","含まない")
のような計算は通りません。

他の関数を使って計算します。単一のセルに対しても色々な関数を使って工夫できますが，その中から例えば「ISNUMBER(FIND())」を使って
=COUNT(0/FREQUENCY(MATCH(IF(ISNUMBER(FIND("赤",A1:A10)),B1:B10&"",B1),B1:B10&"",0),MATCH(IF(ISNUMBER(FIND("赤",A1:A10)),B1:B10&"",B1),B1:B10&"",0)))-1
をCtrl+Shift+Enterで入力してみます。

お礼

本当に本当に何度もありがとうございました。

お答えをいただくチョット前に”赤”のところに”チーム赤””赤””赤組”など具体名を入れたセルを別に作ってしまい”C1:C3”のような形でちょっとズルして解決しました。

更なる関数で対応してくださり、”本当に本当に本当にすごいですね！！！”としか申し上げようがありません。。。
一度レクチャーを受けたいくらいです。。。

お忙しい中、何度もお答えいただきまして本当にありがとうございました。

回答No.5

keithin

＞簡単に

MATCH関数で（赤組の）「佐藤さん」や「山田さん」が「何行目」に出てくるかを計算します。
山田さんが複数人出てきた場合，最初に見つかった山田さんの行番号の数字がダブりになって計算されます。
COUNT(0/FREQUENCY)という計算を使い，ダブった数字列から何種類の数字があるか計算します。

もし，簡単にではなくきちんと理解したい時は，必ず「数式の検証」の機能を使い，関数の１ステップ毎に計算がどのように進んでいくのか検証しながら理解を深めてください。
今回使った計算のテクニックについては，下記の資料を見ながら実際に練習用に数式を組んでみて，やはり数式の検証で計算を追いかけてみてください。
http://support.microsoft.com/kb/268001/ja

＃参考
「数式の検証」は，Excel2003ではツールメニューのワークシート分析にあります。
数式の検証を使って確認すると，もうお一方から寄せられた数式では例えば「赤組の山田さん」と「白組の山田さん」がいらっしゃる時に0.5が出てくるといったコトも確認できます。

お礼

本当に本当にありがとうごさいます。

数式の検証機能、知りませんでした。
面白いですね。とっても為になります。
今後も使ってみようと思います。
教えてくださったサイトも・・・
エクセルの奥深さを感じました。。。

それにしてもkeithinさんのエクセルスキル、タダモノではありませんね。。。
すごすぎます。
今回、人間の脳には運動能力同様”限界”があるような気がしました。。。
私には、苦しかったです。。。

蛇足のご質問なのですが、上記例題においてクラスが”赤”だけでなく、”赤”のつくクラス（例えば、チーム赤、とか赤組とか）も含める場合は可能なのでしょうか？
単純に”赤”となっているところを”*赤*”とすればいいのかと思いましたが、上手く行きませんでした。
もうお答えになりたくなければ、ここまでお聞きしたので十分なのですが、もしよろしかったら最後にお教えいただけると嬉しいです。。。

回答No.4

keithin

簡易にはＡ１に「クラス」，Ｂ１に「親」と（あるいは「名前」等でもいいですが）と記入して，回答２の式をCtrl+Shift+Enterで入力します。

お礼

！！！出来ました～～～！！！（涙）

１行目に”項目名”が必要だったのですね・・・

式の意味は全くわからないのですが・・・
もしよかったら簡単で結構ですので、ご説明くださると嬉しいです。

どうぞよろしくお願いいたします。

回答No.3

KURUMITO

回答No1です。
小数点がついて.5のようになるのはA列が赤でB列が空白のある場合ですね。次の式がよりよいでしょう。

=SUMPRODUCT((A1:A100="赤")*(B1:B100>""))-(SUMPRODUCT((A1:A100="赤")*(COUNTIF(B1:B100,B1:B100)=2)))/2

－の後の式はA列が赤でB列の親の名前がダブっているケースを求め、ダブっているケースを2分の1にすることで、ダブりのない数、ダブりのある数としています。
前の式ではダブりを含めた数でマイナス以降はそのうちのダブりのある数となっています。

お礼

赤 山田
さくら 山田
赤 斎藤
赤 森
赤 森
すみれ 阿部
すみれ 太田
すみれ 鈴木

度々ありがとうございます。
使っているデータは上記なのですが（１行目と１０行目はブランク）、答えは２．５と出てしまいます。
コピペで行数そ１０にして使っているだけなのですが、何がいけないのでしょうか。
ちなみに使っている式は

=SUMPRODUCT(($A$1:$A$10="赤")*($B$1:$B$10>""))-(SUMPRODUCT(($A$1:$A$10="赤")*(COUNTIF($B$1:$B$10,$B$1:$B$10)>=2)))/2

です。

回答No.2

keithin

たとえば
=COUNT(0/FREQUENCY(MATCH(IF(A1:A10="赤",B1:B10&"",B1),B1:B10&"",0),MATCH(IF(A1:A10="赤",B1:B10&"",B1),B1:B10&"",0)))-1
と記入して，必ずコントロールキーとシフトキーを押しながらEnterで入力してみるなどで。

お礼

お返事ありがとうございます。

No.3のところに書きましたデーターで試してみると－１になってしまいます。。。
ちゃんと配列数式にしているのですが、、、

回答No.1

KURUMITO

A列にクラスB列に親の名前が有るとして

=SUMPRODUCT((A1:A100="赤")*(B1:B100>""))-(SUMPRODUCT((A1:A100="赤")*(COUNTIF(B1:B100,B1:B100)<>1)))/2

お礼

ご回答ありがとうございます。

すごいですーーーー！！！
できましたーーーー！！！

・・・なのですが、大変恐縮ではあるのですが、マイナス以下後半部分の式を
ご説明いただけませんでしょうか。。。

・・・と、申しますのも、この例題ではちゃんと教えていただいた式で出来たのですが、実際の表でやってみたら、どうしても小数点になってしまうので、私がこの式を理解しないと出来ないと思いまして。。。

お手数ですがよろしくお願いいたします。