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

解決済みの質問

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

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

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

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


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

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

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

     

投稿日時 - 2012-03-25 07:23:14

QNo.7382440

すぐに回答ほしいです

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

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

「等しい」の計算では,ワイルドカードを使うことはできません。
例えば
=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で入力してみます。

投稿日時 - 2012-03-28 11:53:22

お礼

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

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

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

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

投稿日時 - 2012-03-28 15:08:02

ANo.6

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

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

回答(6)

ANo.5

>簡単に

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



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



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

投稿日時 - 2012-03-26 14:08:14

お礼

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

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

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

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

投稿日時 - 2012-03-27 10:04:48

ANo.4

簡易にはA1に「クラス」,B1に「親」と(あるいは「名前」等でもいいですが)と記入して,回答2の式をCtrl+Shift+Enterで入力します。

投稿日時 - 2012-03-26 10:52:26

お礼

!!!出来ました~~~!!!(涙)

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

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

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

投稿日時 - 2012-03-26 13:09:51

ANo.3

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

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

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

投稿日時 - 2012-03-25 16:16:16

お礼

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

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

=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

です。

投稿日時 - 2012-03-26 06:47:33

ANo.2

たとえば
=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で入力してみるなどで。

投稿日時 - 2012-03-25 13:33:23

お礼

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

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

投稿日時 - 2012-03-26 06:50:24

ANo.1

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

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

投稿日時 - 2012-03-25 09:19:13

お礼

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

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

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

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

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

投稿日時 - 2012-03-25 12:32:09

あなたにオススメの質問