EXCEL データの抽出と転記

質問

1.添付ファイル（JPEG)では、Sheet 10とSheet 5を両方表示しています。同じブッ　ク内ですが、当然分離しています。
2.やりたいことはSheet 10からデータを抽出してSheet 5に転記することです。　　　Sheet 5の9組に、その例を表示しました。
3.Sheet 5には25名分を表記しています。組はランダムです。
4.Sheet 10では、実際の組の数は全部で9つあり、No.は188あります。氏名と組には　ところどころ空白があります。（不在者です）　氏名は昇順とは限りません。
　それに対応できる「計算式」をお願いします。
5.私はEXCELに詳しくありません。マクロが使えません。Sheet 5の組がランダムに　表記されているので、どう抽出したらよいのか困っています。「〇〇関数を使えば　よい」の回答は不要です。具体的な計算式での回答をお願いします。
　尚、私はINDEX関数やMATCH関数は使いますが、VLOOKUP関数は使いません。
7.過日一度質問を投稿したのですが、私の不手際で「プライバシーの侵害につながる恐れのある内容が含まれていた」と判断されてしまいました。そして添付ファイルを削除され、質問の内容を編集されました。結果、質問の内容が回答者には伝わらなくなってしまいました。今回は質問の仕方を変えて再投稿しています。今度の添付ファイルは問題がないと思っています。
説明が長くなってしまいました。
よろしくご指導下さい。

回答No.3

nishi6 · ベストアンサー

少し修正です。
Sheet10のNo.が188あることと、氏名に空白があることに対応しました。組の空白は、どの組にも抽出されません。コピー手順は変わりません。

A3:=IFERROR(MATCH(A$1,Sheet10!$C$2:$C$189,0),"")
A4:=IF(A3<25,IFERROR(A3+MATCH(A$1,INDIRECT("Sheet10!C$"&(A3+2)&":$C$189"),0),""),"")
B3:=IFERROR(IF(INDEX(Sheet10!$B$2:$B$189,A3,1)<>"",INDEX(Sheet10!$B$2:$B$189,A3,1),""),"")

回答No.4

msMike

Sheet10 において、
１．範囲 A1:C26 を選択 ⇒ Alt+MC ⇒ “上端行”のみにチェック入れ ⇒ [OK]

Sheet5 において、
２．次式を入力したセル A3 を右隣のセル B4 にオートフィル
￣￣ =IFERROR(INDEX(INDIRECT(A$2),SMALL(IF(組=A$1,ROW(組)),ROW(A1))-1),"")
￣￣【お断り】上式は必ず配列数式として入力のこと
３．オートフィルされた式中の「組=B$1」を「組=A$1」に変更して
￣￣次式に変更
￣￣ =IFERROR(INDEX(INDIRECT(B$2),SMALL(IF(組=A$1,ROW(組)),ROW(B1))-1),"")
￣￣【お断り】上式は必ず配列数式として入力のこと
４．範囲 A3:B3 を下方にズズーッと(26行目まで)オートフィル
５．範囲 A3:B26 を右方にズズーッと(J列まで)オートフィル

言わずもがなのことですが、Sheet5 の１行目は
G/標準"組"
に書式設定して、数値 9、10、13、14、15 を入力しています。

お礼

回答ありがとうございました。
今回は、私のレベルで何とか理解できた別の方の回答を採用させていただきました。

回答No.2

bunjii

>Sheet 5の9組に、その例を表示しました。
A1とB1セルが結合され、値が「9組」となっていますがB1セルの値は未入力であることをご存知ですよね？
また、A1セルの「9組」は文字列ですか？、それとも数値の「9」に表示形式で「組」を付加したものですか？
Sheet10のC列の値は数値のようですからSheet5のA1、C1、E1セルの値と比較するには値の種類を統一する必要があります。
更に、A1、C1、E1セルの組番号は左から右へ順次大きくなっていますか？
ランダムの場合は組毎に数式を組む必要があります。
条件が曖昧なので数式の提示が困難です。

Sheet5の1行目に組番号が数値で入力されていて左から順次大きい値になる状態であれば次の数式で対応できるでしょう。
Sheet5のA3に下記の数式を入力し、右および下へ必要数コピーします。
=IFERROR(INDEX(Sheet10!$A$1:$B$189,LARGE(INDEX((Sheet10!$C$2:$C$189=MAX($A$1:A$1))*ROW(Sheet10!C$2:C$189),0),COUNTIF(Sheet10!$C$2:$C$189,MAX($A$1:A$1))-ROWS(A$3:A3)+1),MOD(COLUMNS($A3:A3)-1,2)+1),"")

添付画像はExcel 2013で検証したSheet5の結果です。
元データのSheet10は質問に添付の画像を参照しました。

お礼

回答ありがとうございました。
計算式が長く、私のレベルでは難解でした。

回答No.1

nishi6

Sheet5の
　セルA3に、=IFERROR(MATCH(A$1,Sheet10!$C$2:$C$26,0),"")
　セルA4に、=IF(A3<25,IFERROR(A3+MATCH(A$1,INDIRECT("Sheet10!C$"&(A3+2)&":$C$26"),0),""),"")
　　　　これをA5～A26にコピー
　セルB3に、=IFERROR(INDEX(Sheet10!$B$2:$B$26,A3,1),"")
　　　　これをB4～B26にコピー
　セル9組のA3～B26を各組に貼り付けます。

ただし、セルA1とセルB1は結合されており、数値が入力されており、表示形式が「G/標準"組"」とします。