エクセルのデータ処理で困っています

質問

あまりエクセル関数に詳しくないので困っております。
2点ほど質問があるのですが…

1.　B1セルから横に

　　 ［A］, ［B］, ［C］ , ［D］ , ［E］ , ［F］
［1］ 　　 　 , mikan, ichigo , - , mikan , -
［2］ 　　 　 , - , ichigo , ichigo , ichigo, -　

　 と入っています。
(桁がずれて表示されてしまうので、セルの区切りにカンマを入れました。見えづらくてすみません。以下の表も同様です)
　　この時、各行毎に、入力されているデータ(ハイフンは除く)が同じものであるかどうかを判定し、同じであれば●を、違うものが混じっていれば×を、［A］列に入力したいのです。
　結果としては［A1］が×、［A2］が●となればよいのですが…
　if(B1=C1=D1=E1=F1,"●","×")と入れてみましたが、"-"をどう処理すればよいのかわからず、お手上げです。

2.　B1セルから横に

　 ［A］, ［B］, ［C］, ［D］
［1］ 　　　 Q05 , 　 mikan , orange
［2］ 　　　 P05 , 　 mikan , orange
［3］ 　　　 S12, 　 りんご, 　 apple
［4］ 　　　 R09, 　 ぶどう, 　 grape
［5］ 　　　 P10 , 　 ringo, apple
［6］ 　　　 S16, 　 りんご, 　 apple
［7］ 　　　 P30 , 　 みかん, 　orange
［8］ 　　　 T42 , ミカン, 　orange

と入っています。

　［A］列に重複の有る無しを入力したいのですが、条件があり、
　まず、［D］列が同じものであること、
　次に、［B］列の数字部分が等しくないものについて、
　上から順番に、重複1、重複2，…と［A］列にいれていきたいのです。
　［A］列にはすべて同じ式を入れますが、重複でないセルは"" になるように設定していただくと助かります。

結果として、
　 　　 ［A］, ［B］, ［C］, ［D］
［1］ 重複1, Q05 , mikan, orange
［2］ 　　,　 P05, mikan, orange
［3］ 重複2, S12, りんご, 　 apple
［4］ 　　, 　 R09, ぶどう, 　 grape
［5］ 重複2, P10 , ringo, apple
［6］ 重複2, S16, 　 りんご, 　 apple
［7］ 重複1, P30 , みかん, 　orange
［8］ 重複1, T42, ミカン, 　orange

　 このようになれば、と思っています。
　これについては、頭の中が混乱して、お手上げ状態です。

　　未熟者なので、関数でご教授いただければありがたいですが、もし無理なようであればVBA でも…
よろしくお願いいたします。

回答No.4

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

＃2です
質問1
不備があったのと、画像が張り付かなかったので再投稿
=IF(SUBSTITUTE(REPT(B1&C1&D1&E1&F1,2),"-","")
=SUBSTITUTE(B1&C1&C1&D1&D1&E1&E1&F1&F1&B1,"-",""),"●","×")

質問2
E1セル 判断材料
=MATCH(D1,$D$1:$D$8,0)*100+RIGHT(B1,2)
F1セル 重複の判断
=(COUNTIF(E$1:E1,E1)<=1)*(COUNTIF($E$1:$E$8,">"&FLOOR(E1,100))-COUNTIF($E$1:$E$8,">="&CEILING(E1,100)))
G1セル 重複の対象外は0
=IF(F1>1,INT(E1/100),0)
H1セル 種類数を出す
=IF(ROW(G1)=1,IF(G1=0,0,1),
IF(G1=0,INDEX(H:H,ROW(G1)-1),IF(MATCH(G1,G$1:G1,0)=ROW(G1),INDEX(H:H,ROW(G1)-1)+1,INDEX(H:H,ROW(G1)-1))))
A1セル 結果
=IF(G1=0,"","重複"&INDEX($H$1:$H$8,MATCH(G1,$G$1:$G$8,0)))

お礼

お礼が遅くなり、申し訳ありません。
２度も投稿していただき、ありがとうございます。
最初の投稿時のご指摘のとおり、質問一つずつ別なスレをたてるべきでした。ご指摘ありがとうございます。以後気をつけます。

質問1ですが、求める結果は得られたのですが、恥ずかしながら、どのような考え方で作られた式なのかがよくわからないので、関数事典とにらめっこで勉強します。
質問2ですが、こちらも求める結果を得られました。各式の説明をありがとうございます。この式も私の使ったことのない関数が多数あり、勉強不足を痛感しております。

お二人とも大変お忙しいところ、貴重なお時間を割いていただきまして、ありがとうございました。

回答No.3

kagakusuki

１．A1セルに次の数式を入力してから、A1セルをコピーして、A2以下に貼り付けると良いと思います。

=IF(COUNTIF($B1:$F1,$B1)*($B1<>"-")+COUNTIF($B1:$F1,$C1)*($C1<>"-")+COUNTIF($B1:$F1,$D1)*($D1<>"-")+COUNTIF($B1:$F1,$E1)*($E1<>"-")+COUNTIF($B1:$F1,$F1)*($F1<>"-")=(COLUMNS($B1:$F1)-COUNTIF($B1:$F1,"=")-COUNTIF($B1:$F1,"-"))^2,"●","×")

２．適当な列（ここでは仮にE列とします）を作業列として使用します。
　まず、E2セルに次の数式を入力して下さい。

=IF(ROW($B1)>MATCH("゜",$B:$B,-1),"",IF(AND(SUMPRODUCT((OFFSET($D$1,,,MATCH("゜",$B:$B,-1))=$D1)*(RIGHT(OFFSET($B$1,,,MATCH("゜",$B:$B,-1)),LEN(OFFSET($B$1,,,MATCH("゜",$B:$B,-1)))-1)<>RIGHT($B1,LEN($B1)-1)))>1,COUNTIF($D$1:$D1,$D1)=1),COUNT(E$1:E1,""))

　次に、E2セルをコピーして、E3以下に貼り付けて下さい。
　次に、A1セルに次の数式を入力して下さい。

=IF(OR($B1="",$D1=""),"",IF(SUMPRODUCT(((OFFSET($D$1,,,MATCH("゜",$B:$B,-1))=$D1)*(RIGHT(OFFSET($B$1,,,MATCH("゜",$B:$B,-1)),LEN(OFFSET($B$1,,,MATCH("゜",$B:$B,-1)))-1)<>RIGHT($B1,LEN($B1)-1))+(ROW(OFFSET($E$2,,,MATCH("゜",$B:$B,-1)))-1=ROW(A1)))*ISNUMBER(OFFSET($E$2,,,MATCH("゜",$B:$B,-1))))>0,"重複"&INDEX($E:$E,SUMPRODUCT(((OFFSET($D$1,,,MATCH("゜",$B:$B,-1))=$D1)*(RIGHT(OFFSET($B$1,,,MATCH("゜",$B:$B,-1)),LEN(OFFSET($B$1,,,MATCH("゜",$B:$B,-1)))-1)<>RIGHT($B1,LEN($B1)-1))+(ROW(OFFSET($E$2,,,MATCH("゜",$B:$B,-1)))-1=ROW(A1))>0)*ISNUMBER(OFFSET($E$2,,,MATCH("゜",$B:$B,-1)))*ROW(OFFSET($E$2,,,MATCH("゜",$B:$B,-1))))),""))

　次に、A1セルをコピーして、A2以下に貼り付けて下さい。

以上です。

お礼

ご回答ありがとうございます。
お礼が遅くなり申し訳ありません。

1.ですが、未熟者の私にも考え方のわかりやすい関数で、求める結果が得ることができました。
2.ですが、E2に貼付けた式にエラーがあるとのメッセージがでてしまい、思うような結果が得られませんでした。
どこをどう直せば、うまく動くのかわからず、とても残念です。

勉強させていただきます。
ありがとうございました。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

システム上、関連性がないなら質問1つにつき1つのスレを立ち上げてほしい。
で、時間がないので 質問1のみ
=IF(SUBSTITUTE(B1&C1&D1&E1&F1,"-","")=SUBSTITUTE(C1&D1&E1&F1&B1,"-",""),"●","×")
ないとは思うが、問題は7行目の場合

回答No.1

aokii

りんごとappleが同じかどうかはパソコンには解りませんので、まずは同じ言葉に変換しておくのはいかがでしょう。

お礼

ありがとうございます。参考にさせていただきます。