Excel 重複値を検索し全て表示させる関数

質問

Sheet1の１行目には見出しがあり、A列とB列の２行目から下にデータが入っています。（約２００行）
A列　　　　　B列
田中　　　　13
山本　　　　 8
谷口　　　　11
鈴木　　　　6
田中　　　　10
佐々木　　　　9
奥村　　　　 15
佐々木　　　15
佐々木　　　20
・
・
・

Sheet2のA列１行目から下には別のデータが入っています。（約６００行）
A列
太田川
山村
田中
多賀先
鈴木
奥村
幸田
・
・
・

Sheet2のA列のデータと完全一致するデータ（名前）をSheet1のA列から探して、同じ名前があれば、その隣のB列にある数値をSheet2のB列に貼り付ける(B列が重複の場合、下に張り付ける。但し、A列の名前は1行目は表示、2行目からは表示、空欄でも構いません)。
見つからない場合はSheet2のB列は空欄のままです。
Sheet2
A列　　　　　　B列
太田川　　　　　
山村
田中　　　　13
　　　　　　 10
多賀先
鈴木　　　　 6
奥村　　　　15
佐々木　　　9
　　　　　　15
　　　　　　20
幸田
・
・
・
何卒、ご指導の程宜しくお願い致します。

回答No.5

kagakusuki · ベストアンサー

回答No.1様の仰る通り、Excelのワークシート関数では既に値が入力されている行の間に新たな行を挿入する事は出来ません。
　ですから、Sheet1とSheet2のデータを基にして、Sheet3のA列～B列に結果が表示させる様にされては如何でしょうか?

　まず、Sheet2のA1セルに項目名等の 何らかの値を入力して下さい。
　次に、Sheet3のA1セルに次の関数を入力して下さい。

=Sheet2!$A$1

　次に、Sheet3のA2セルに次の関数を入力して下さい。

=IF(A1="","",IF(COUNTIF(A$1:A1,A1)<COUNTIF(Sheet1!$A:$A,A1),A1,INDEX(Sheet2!$A:$A,MATCH(A1,Sheet2!$A:$A,0)+1)&""))

　次に、Sheet3のB2セルに次の関数を入力して下さい。

=IF($A2="","",IF(COUNTIF(Sheet1!$A:$A,$A2),IF(ISERROR(1/(INDEX(Sheet1!$B:$B,SUMPRODUCT(ISNUMBER(ROW(Sheet1!$A$1:$A$999))*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$999)-ROW(Sheet1!$A$1)+1),$A2)<COUNTIF($A$1:$A2,$A2)))+1)<>"")),"",INDEX(Sheet1!$B:$B,SUMPRODUCT(ISNUMBER(ROW(Sheet1!$A$1:$A$999))*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$999)-ROW(Sheet1!$A$1)+1),$A2)<COUNTIF($A$1:$A2,$A2)))+1)),""))

　次に、Sheet3のA2～B2セルをコピーして、Sheet3のA列～B列の3行目以下に貼り付けて下さい。

　以上です。

お礼

すごーいですね。今、目が点になっています。・・・・・・
有難うございました。物凄い物を見させて頂きました。感謝の言葉を忘れて呆然としています。結果についても文句は全くありませんし、何とお礼したらいいか分かりません。どうしたらkagakusukiさんみたいにこの短時間の間にこれ程の関数が出来上がるのでしょうか。
ここでは文字でしかあらわせませんが、本当に感謝、感謝の気持ちでいっぱいです。有難うございました。

回答No.4

imogasi

よくある質問の再出なので考え方だけ。
＞指導の程宜しくお願い致しますのご指導にあたり、回答は別ですが、知っておいたらよいと思って。
いままで下記のことを知らずに、のんきに関数での答えを求めている人が圧倒的に多い。
エクセル関数やその本質を、勉強不足が原因で、つかんでいない場合が多い。
（１）エクセルは式を入れたセルにしか答えが出せない。
ＶＢＡだと任意のセルにセットできる。
（２）式の繰り返しができない。
ＶＢＡだとできる。
（３）一時記憶しておく変数が使えない。ＶＢＡでは当たり前に使う。
そのため関数（ＶＢＡでのユーザー定義関数のことは除外）だと知恵を絞る必要がある。
ーーー
エクセル関数では（条件により）抜出問題は苦手な個所だと思う。
（１）条件を付けての抜出し問題はＶＢＡ向けです。将来的にビジネスで使いこなそうと思えば、ＶＢＡをよく勉強しする必要がある。この件ではＦｉｎｄメソッドという者１つが中心。Ａｃｃｅｓｓなどならもっと簡単だろう。
またエクセル関数は、該当データの整理（見えかた、フォーマット）に注文を付けると
複雑化する。
（２）設計的に、複数該当がある場合は、関数が、難しく複雑になる
（３）（２）について作業列を使わない方式だと式が複雑になる。
ーー＞だから初心者はできるだけ作業列を活用しろ。
（４）ふつうは、該当が元データの飛び飛び行に現れるが、これを検出するのは、ＣＯＵＮＴＩＦ（ＣｏｕｎｔｉｆＳ）関数などで簡単だが、これを行的に（ある列に）「詰めて！」表示したいのだろうが、これがむつかしい。
どの関数をどう組み合わせるかの方法が、天下り的に教えてもらい、覚えていくほかない。
（普通は、何年考えても、独力では、作り出す域にはならないと思うような技巧的な式。）回答に出た式のまる写しで、「よし」とするか。
ーーー
Ｇｏｏｇｌｅで「small index 抜き出し」で照会し読んでみたら、この手の質問の概略は判るだろう。あるいは「エクセル　関数　抜出し」などで検索。

お礼

色々とご指摘頂き有難うございます。私もその中の一人かも知れませんね。
ご指摘頂いた点も含め、もう一度トライしてみます。
気づかせて頂き有難うございました。

回答No.3

msMike

よりスマート(だと思うけどなぁ～)な添付図でよろしければ、・・・
Sheet2!B1: =IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$12=Sheet1!$D1,ROW($A$1:$A$12),""),COLUMN(A1))),"")
【お断り】上式は必ず配列数式として入力のこと

お礼

失礼をしたうえ、ご回答まで頂きまして有難うございました。
早速、教えられた通り式を入れてみます。
重ね重ね、有難うございました。

回答No.2

msMike

「Sheet2のA列１行目から下には別のデータが入っています」の直下の表には「奥村」と「田中」の間に「佐々木」は存在しないのに、
「見つからない場合はSheet2のB列は空欄のままです」の直下の表にノウノウと「佐々木」が登場しているのはなぜ？！
シッカリしてね！

お礼

今後はこのようなミスをする用が無い様に致します。ご指摘有難うございました。

補足

失礼いたしました。ご指摘の様に矛盾があります。先を急ぐあまり順番に気を付けづに質問してしまいました。申し訳ございません。

回答No.1

f272

田中や佐々木のように複数見つかった場合には，行を挿入することになるが，それをエクセルの関数で行うのは不可能です。VBAなら出来る。
行を挿入するのではなく，C列，D列...を使って表示するというのなら関数を使っても可能です。

お礼

参考になりました。有難うございます。宜しければ、補足コメントにあるようにVBAを教えて頂ければ幸いです。

補足

早速のお返事有難うございます。VBAで出来るのであればご指導いただけないでしょうか？何卒、宜しくお願い致します。