EXCEL/関数 特定の範囲を条件付で行方向に整形して表示したい

質問

回答者の皆様　宜しくお願いします。

以下のようなEXCELシートがあります。
EXCELで開きやすいようカンマ区切りにしてあります。御了承ください
ユニークな生徒コードがあり、受験した科目1-5と対応する点数1-5があります。

生徒コード,科目1,点数1,科目2,点数2,科目3,点数3,科目4,点数4,科目5,点数5
2001,4,9,1,4,5,7
2002,5,5,2,7,3,5,1,9,4,2
2003,1,4,3,7,

＊科目コードは、生徒ごとに行方向に昇順ではありません
＊科目1-5のうち、1-5全てを受験した生徒も居れば、そうでない生徒も居ます。
＊生徒ごとに、受験した科目と結果のみが左詰で入力されています。
＊点数は10点満点です

これを、別シートに

生徒コード,科目1,点数1,科目2,点数2,科目3,点数3,科目4,点数4,科目5,点数5
2001,1,4,,,,,4,9,5,7
2002,1,9,2,7,3,5,4,2,5,5
2003,1,4,,,3,7,,,,

＊生徒ごとに、行方向に科目コード昇順（対応する点数も昇順）
＊受験していない科目は空欄（見出しに対応する位置に科目と点数が来る）

ように表示したいのですが、関数を組み合わせて可能でしょうか。

大変厄介なことに点数が10点満点のため、検索系の関数でヒットした値が
『「科目」なのか「点数」なのか区別がつけられない』ということです。

VLOOKUP,HLOOKUP,MATCH,IFなど組み合わせて頭を捻っていたのですが
どうしても解決方法が思い浮かびませんでした。
よいアイデアありましたら御教授宜しくお願いします。

検索キー　EXCEL 文字列 検索 奇数行のみ 偶数行のみ 関数

回答No.4

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

No.1、No.2のCoalTarです
別に配列数式にこだわったわけではないですが、
頭が回らなかったのが本当のところです。
ということで別案

条件1. 作業用のセルを設けてよい
条件2. 生徒コードの並びは「元の表」と「結果の表」が一緒
ということなら
1. 科目番号の取り出し
B7セル =INDEX($B2:$K2,B$6*2-1)
右へ下へオートフィル

2. 点数の取り出し
G7セル =INDEX($B2:$K2,G$6*2)
右へ下へオートフィル

3. 科目位置とその点数の取り出し
B12セル =MATCH(B$11,$B7:$F7,0)
C12セル =INDEX($G7:$K7,B12)
B12:C12セルを右へ下へオートフィル

4. 結果の表の作成
B17セル =IF(ISNA(B12),"",B$11)
C17セル =IF(ISNA(C12),"",C12)
B17:C17セルを右へ下へオートフィル

回答No.3

imogasi

このタイプは関数では複雑だな、と思って既出回答を見ると案の定、配列数式を使い、かつ式は長い。
＞関数を組み合わせて可能でしょうか
このタイプの課題は、生徒コード番号でソートし、同じ生徒の分は近接行に集め、VBAで科目コードー点数の２列のデータを組にして考え、科目コード列（＝コード＋１の列）のセルに点数を代入する、というロジックでやるのが素直なやり方です。（この場合同一生徒で同一科目が２度以上出てくると困りますが。）
VBAの経験が無いため、関数でということだが、この関数を考え付く人はそう多くない。結局どちらも丸写しになる。
データ作成の段階から、生徒番号ー科目コードー点数で１行というデータの寄せ集めにでもしておくのが望ましい。
＞『「科目」なのか「点数」なのか区別がつけられない
難しいのはそんなことでなく、生徒番号が連続して無いだろうし、科目コードの出現数が不定で、現れる位置が不定で有ることが、関数では難しい原因と思います。
科目コードと点数は、生徒番号の後に、ペアになっているから、VBAなら区別はつくと思います。
参考までにVBAの例　L列より右に科目点数を所定列に並べます。
ーーー
例データ　A-K列（下記はA-E列の例）
11 1 44 2 55
11 4 33
12 3 56
14 1 76 4 36
コード
Sub test01()
d = Range("A65536").End(xlUp).Row
k = 1
For i = 1 To d
If Cells(i, "A") <> m Then
k = k + 1
m = Cells(i, "A")
End If
Cells(k, "L") = Range("A" & i)
r = Range("K" & i).End(xlToLeft).Column
MsgBox r
For j = 2 To r Step 2
c = Cells(i, j)
Cells(k, c + 12) = Cells(i, j + 1)
Next j
Next i
End Sub
結果　L-P列
コード　科目１ 科目２　科目３　科目４
11 44 55 ー 33
12 ー ー 56
14 76 ー ー 36

回答No.2

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

ちょっと修正(わかりやすくしようとしたら反って悪くした
=IF(OR(VLOOKUP($A7,$A$2:$K$4,{2,4,6,8,10},FALSE)=RIGHT(C$6,1)*1),
INDEX($B$2:$K$4,MATCH($A7,$A$2:$A$4,0),
MATCH(RIGHT(C$6,1)*1,VLOOKUP($A7,$A$2:$K$4,{2,4,6,8,10},FALSE),0)*2),"")
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)
m(_ _)m

回答No.1

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

B7セル =IF(C7="","",RIGHT(B$6,1)*1)
C7セル
=IF(OR(VLOOKUP($A7,$A$2:$K$4,{2,4,6,8,10},FALSE)=RIGHT(C$6,1)*1),
INDEX($B$2:$K$4,MATCH($A7,$A$2:$A$4,0),
MATCH(RIGHT(C$6,1)*1,VLOOKUP($A7,$A$2:$K$4,{2,4,6,8,10},0),FALSE)*2),"")
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)
B7:C7セルを右へ下へオートフィル

根本的にデータの入力方法を見直したほうが良いと思います
生徒コード,点数1,点数2,点数3,点数4,点数5
2001,4,,,9,7
2002,9,7,5,2,5
2003,4,,7,,