Excelでの文字列抽出

質問

Ｅｘｃｅｌで文字列の抽出とそれを並べる方法を教えてください。
関数だけを用いて実現できるのか、マクロを使わなければできないのかも分かりません。
この関数をこの様に使えばよいというのを教えていただけると幸いです。

やりたいことは、下記条件で、シート２のＢ列にシート１のＧ列～Ｐ列までに登録されている担当者名に該当するＢ列の店名を４店まで抽出したいのです。

分かりづらくて申し訳ございませんが、よろしくお願い致します。

－－－－－－－－－－－－－－－－－－－－－－－－

シートが２枚（シート１／シート２）あります。
シート１
　Ｂ列は４行毎に結合されており、店名が登録されています。
　Ｇ列～Ｐ列までは１行目は担当者名、２，３，４行目は補足情報が登録されています。　

シート２
　Ａ列に担当者名が入っています。（４行毎に結合しています）
　Ｂ列にＡ列の担当者名に該当する、シート１の店名を４店まで登録したい。

回答No.1

KURUMITO · ベストアンサー

シート１では１行目は空白の行として２行目から下方にお示しのようなデータが入力されているとします。
４行ずつが一つの店になっており、その行の最初の行のＧ列からＰ列までの間に担当者の名前が有るということですね。
そこで作業列を設けます。
Ｒ１セルから右横方向のセルに例えばＸＸ１セルまでに担当者名を入力します。
その後にＲ２セルには次の式を入力して横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR($B2="",R$1=""),"",IF(COUNTIF($G2:$P2,R$1),MAX(R$1:R1)+1,""))

シート２でも同様にＡ列の２行目から下方に１名につき４行ずつで担当者が入力されているとします。
Ｂ２セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet1!R$1:XX$1,INDIRECT("A"&ROUNDUP(ROW(A2)/4,0)*4-2))=0,"",IF(ISERROR(MATCH(MOD(ROW(A2)-1,4)+1,INDEX(Sheet1!R:XX,1,MATCH(INDIRECT("A"&ROUNDUP(ROW(A2)/4,0)*4-2),Sheet1!R$1:XX$1,0)):INDEX(Sheet1!R:XX,1000,MATCH(INDIRECT("A"&ROUNDUP(ROW(A2)/4,0)*4-2),Sheet1!R$1:XX$1,0)),0)),"", INDEX(Sheet1!$B:$B,MATCH(MOD(ROW(A2)-1,4)+1,INDEX(Sheet1!R:XX,1,MATCH(INDIRECT("A"&ROUNDUP(ROW(A2)/4,0)*4-2),Sheet1!R$1:XX$1,0)):INDEX(Sheet1!R:XX,1000,MATCH(INDIRECT("A"&ROUNDUP(ROW(A2)/4,0)*4-2),Sheet1!R$1:XX$1,0)),0))))

回答No.3

KURUMITO

回答Ｎｏ１です。
シート２のＢ２セルへの入力の式は次の式にしてください。入力後に下方にドラッグコピーします。

=IF(COUNTIF(Sheet1!R$1:XX$1,INDIRECT("A"&ROUNDUP(ROW(A1)/4,0)*4-2))=0,"",IF(ISERROR(MATCH(MOD(ROW(A1)-1,4)+1,INDEX(Sheet1!R:XX,1,MATCH(INDIRECT("A"&ROUNDUP(ROW(A1)/4,0)*4-2),Sheet1!R$1:XX$1,0)):INDEX(Sheet1!R:XX,1000,MATCH(INDIRECT("A"&ROUNDUP(ROW(A1)/4,0)*4-2),Sheet1!R$1:XX$1,0)),0)),"", INDEX(Sheet1!$B:$B,MATCH(MOD(ROW(A1)-1,4)+1,INDEX(Sheet1!R:XX,1,MATCH(INDIRECT("A"&ROUNDUP(ROW(A1)/4,0)*4-2),Sheet1!R$1:XX$1,0)):INDEX(Sheet1!R:XX,1000,MATCH(INDIRECT("A"&ROUNDUP(ROW(A1)/4,0)*4-2),Sheet1!R$1:XX$1,0)),0))))

前回の式はＲＯＷ（Ａ２）になっていましたね。

お礼

一度お礼を書き込んだつもりなのですが、見当たらないので再度、お礼を申し上げます。
早々にご回答いただき、本当にありがとうございました。

補足

ご教示いただいた式をExcel2003/2010の2種類で検証しましたが、残念ながら、エラーになります。
何処か間違いがでしょうか？

回答No.2

aokii

式を簡略化するために、まず、新たなシートとしてSheet3に以下の表を作ります。

Sheet3のA1セルに以下の式を入力する。
=Sheet2!A1

Sheet3のA2セルに以下の式を入力する。
=A1

Sheet3のA3セルに以下の式を入力する。
=A1

Sheet3のA4セルに以下の式を入力する。
=A1

Sheet3のSheet3のA1～A4を全て選んだ状態で、下にドラッグコピーすると、以下のように４行毎に担当者名が自動表示されるはずです。
Aさん
Aさん
Aさん
Aさん
Bさん
Bさん
Bさん
Bさん

Sheet3のB1に以下の式を入力する。
=INDIRECT("Sheet1!B"&MATCH($A1,Sheet1!G$1:G$16,0))

Sheet3のB1を選んだ状態で、右にK1までドラッグコピーする。

Sheet3のSheet3のB1～K1を全て選んだ状態で、下にドラッグコピーする。

Sheet2のB1に次の式を入力する。
=IF(ISNA(HLOOKUP(Sheet1!$B$1,Sheet3!$B1:$K1,1,FALSE)),"",Sheet1!$B$1)

Sheet2のB2に次の式を入力する。
=IF(ISNA(HLOOKUP(Sheet1!$B$5,Sheet3!$B2:$K2,1,FALSE)),"",Sheet1!$B$5)

Sheet2のB3に次の式を入力する。
=IF(ISNA(HLOOKUP(Sheet1!$B$9,Sheet3!$B3:$K3,1,FALSE)),"",Sheet1!$B$9)

Sheet2のB4に次の式を入力する。
=IF(ISNA(HLOOKUP(Sheet1!$B$13,Sheet3!$B4:$K4,1,FALSE)),"",Sheet1!$B$13)

Sheet2のB1～B4を全て選んだ状態で、下にドラッグコピーする。

お礼

一度お礼を書き込んだつもりなのですが、見当たらないので再度、お礼を申し上げます。
早々にご回答いただき、本当にありがとうございました。

補足

私の質問内容が不明確だったので申し訳ございません。
ご回答いただいた内容を実施したところ、２点問題がありました。
解決策をご教示いただけると幸いです。

問題１
　店は１０店（注）あります。しかし、上位の４店しか対象になりませんでした。
(注)下記に変更しています。
Sheet3のB1は=INDIRECT("Sheet1!B"&MATCH($A1,Sheet1!G$1:G$40,0))）

問題２
　同じ担当者が同じ列に複数あった場合でも、最上位の店しか対象になりませんでした。