Excelでセル文字列に応じて他シートからコピペ

質問

Excelで、
シート1には「ある値の羅列」
シート２には「特定の値についての詳細説明が複数セルにわたって記載されている」
という構成になっているとき、
シート１のセル内の部分的な値と一致する、シート２の特定のセル
（詳細説明の中で必要なセルのみ）をシート1の値の横のセルから右方向に
コピーアンドペーストしたいと考えています。

具体的には
シート1の、A列のセル内に以下のように「：」で区切った情報の羅列が数千行分
入力されています。

　　　　A　　　　　　　　　　　　　　　　　　B
１　商品名：リンゴ：渋谷店：在庫有り　　（空白セル）
２　商品名：みかん：新宿店：在庫無し　　（空白セル）
３　商品名：メロン：池袋店：在庫有り　　（空白セル）
　　　・
　　　・

シート２には
　　　　　A　　　　　B　　　　　C　　　　　D　　　　　E　　　　　
１　イチゴ　　　　　　赤　　　甘酸っぱい　　春　　　　　高価　　　・・・
２　みかん　　　　　　橙　　　甘酸っぱい　　冬　　　　　安価　　　・・・
３　リンゴ　　　　　　赤　　　　甘い　　　　冬　　　　　普通　　　・・・
４　メロン　　　　　　緑　　　　甘い　　　　夏　　　　　高価　　　・・・
５　バナナ　　　　　　黄色　　　甘い　　　　？　　　　　普通
　　　・
　　　・
　　　・

という風に、ある値に対する詳細説明が列方向に記載されています

このとき、シート１のA１のある値（リンゴ、みかん　等）に対応した
シート２の説明行のうち、ある一定の部分（D列より右がわ）を、
シート1のB列より右に一括でコピーアンドペーストできないでしょうか？

出来上がりの希望は以下の通りです。

　　　　A　　　　　　　　　　　　　　　　　　B　　　　　　C　　　・・・
１　商品名：リンゴ：渋谷店：在庫有り　　　　　冬　　　　　普通　　・・・
２　商品名：みかん：新宿店：在庫無し　　　　　冬　　　　　安価　　・・・
３　商品名：メロン：池袋店：在庫有り　　　　　夏　　　　　高価　　・・・
　　　・
　　　・
　　　・

従来ははいちいちシート１のA列の値をコピーし、シート２で検索、
該当する情報をドラッグしてコピー、シート1の戻ってB列にペースト、
という作業を行っていました。

しかし今回、シート1の内容が数千行もあるため、できればマクロや関数でなんとかできれば、
と思っています。

この作業の自動化に付き、ご存知の方、お教えいただきたくお願いいたします。

回答No.5

bunjii

B1セルに次の数式を入力して右と下へオートフィルコピーすれば良いでしょう。
=VLOOKUP(MID($A1,FIND("：",$A1)+1,FIND("：",$A1,FIND("：",$A1)+1)-FIND("：",$A1)-1),Sheet2!$A:D,COLUMN(Sheet2!D1),FALSE)
但し、A列の未入力やVLOOKUP関数で検索値の該当が無いときの処理は含んでいません。
必要なときはどうしたいかを補足してください。
基本的には回答No.2と同様ですが範囲の指定方法を見直して負荷軽減の配慮をしました。

回答No.4

kagakusuki

御質問の件の場合、検索するセル範囲を指定する際に65536行まで指定せずとも、列番号のみの範囲を指定するだけで事足ります。
　まず、Sheet1のB1セルに次の関数を入力して下さい。

=IF(COUNTIF($A1,"商品名：*?：*"),IF(ISERROR(1/(VLOOKUP(REPLACE(LEFT($A1,FIND("：",$A1,FIND("商品名：",$A1)+LEN("商品名："))-1),1,FIND("商品名：",$A1)+LEN("商品名"),),Sheet2!$A:$E,COLUMNS(Sheet2!$A:D),FALSE)<>"")),"",VLOOKUP(REPLACE(LEFT($A1,FIND("：",$A1,FIND("商品名：",$A1)+LEN("商品名："))-1),1,FIND("商品名：",$A1)+LEN("商品名"),),Sheet2!$A:$E,COLUMNS(Sheet2!$A:D),FALSE)),"")

　次に、Sheet1のB1セルをコピーして、Sheet1のC1セルに貼り付けて下さい。
　そして、Sheet1のB1～C1の範囲をコピーして、Sheet1のB列～C列の2行目以下に貼り付けて下さい。

　尚、上記の関数では、Sheet2のA列に該当するデータが存在している行において、D列かE列のセルが空欄となっている箇所がある場合には、0が表示される様な事は無く、正しく空欄の表示となる様になっております。
　後それから、元データの形式が

商品名：○○：□□：××

などの様に「商品名：」が先頭に来ているのではなく、

△△：商品名：○○：□□：××

などの様に「商品名」が「：」に挟まれた形で途中に存在している様な場合には、次の様な関数にして下さい。

=IF(COUNTIF($A1,"*：商品名：*?：*"),IF(ISERROR(1/(VLOOKUP(REPLACE(LEFT($A1,FIND("：",$A1,FIND("：商品名：",$A1)+LEN("：商品名："))-1),1,FIND("：商品名：",$A1)+LEN("：商品名"),),Sheet2!$A:$E,COLUMNS(Sheet2!$A:D),FALSE)<>"")),"",VLOOKUP(REPLACE(LEFT($A1,FIND("：",$A1,FIND("：商品名：",$A1)+LEN("：商品名："))-1),1,FIND("：商品名：",$A1)+LEN("：商品名"),),Sheet2!$A:$E,COLUMNS(Sheet2!$A:D),FALSE)),"")

回答No.3

chie65536（@chie65535）

蛇足な追記。

VLOOKUPは、検索に時間がかかるので、シート２の最大行数が決まっている（つまり、後から増えたとしても、これ以上は増えないと思われる、最大の行数がある）なら「65536」の部分を、その値に変えて下さい。

例えば「後から増えたとしても、最大で7500行にしかならない」と言うのであれば「Sheet2!$1:$65536」を「Sheet2!$1:$7500」にして下さい。

なお「最初は7500にしてしまったけど、後から7500を越えてしまった」って場合は、シート１のB列から右を範囲指定して（つまり、A列以外を範囲指定して）「置換」で「7500を8000に、すべて置換」などで、検索する表の範囲を広げて下さい。

お礼

補足説明まで丁寧にいただき、本当にありがとうございます。
Sheet2の内容は基本的には手を加えませんので、行数指定のTipsは
本当にありがたいです。最初いただいたアドバイスの内容とともに
現在確認してみています。取り急ぎ、改めてお礼申し上げたく書き込ませていただきました。ありがとうございます。

回答No.2

chie65536（@chie65535）

シート２のシート名は「Sheet2」だと仮定します。

シート１のB1セルに
=VLOOKUP(MID($A1,FIND("：",$A1)+1,FIND("：",MID($A1,FIND("：",$A1)+1,LEN($A1)))-1),Sheet2!$1:$65536,COLUMN(Sheet2!D1),FALSE)
と言う式を入れます。

式を入れたら、B1セルをCtrl＋Cでコピー。

B1セルが点滅したら、貼り付けたい範囲（例えば、B1からK7000まで）を範囲指定して、Ctrl＋VまたはEnterキーで貼り付け。

もし、どこかで「#N/A」というのが出たら「シート２に説明が無い」ので、説明を追加して下さい。

お礼

早速お教えいただき、ありがとうございます。
現在、教えていただいた関数を使って見ています。
取り急ぎ、お礼申し上げたく書き込みさせていただきました。
また結果に付き、追記させていただこうと思います。

回答No.1

kuma56

>できればマクロや関数でなんとかできれば、と思っています。
はい、マクロや関数でできると思いますよ。

>この作業の自動化に付き、ご存知の方、お教えいただきたくお願いいたします。
えっ？
だから、マクロや関数で何とかしたいと思っているんじゃないんですか？？
だったら、マクロや関数について質問者のスキルがよく判らないと、アドバイスも中途半端いなるかもよ？？
それとも、丸投げですかな？？
エクセルの、マクロや関数について解説している様なwebサイトは結構沢山あるので、まずそれらを検索してみて、自身で方法を検討してみて、それで判らないところがあったら、そこにポイントを絞って設問したほうが、より良いアドバイスが貰いやすいと思うよ。
とりあえずは、関数の　vlookup　なぞを調べてみては？？？