＜未解決＞Excel（エクセル）でテストの結果をマクロで個人票にしたいんですが…

質問

エクセルのマクロを使って、下記のように個人票を作りたいです。いろいろ参考になりそうな同類の質問もあるのですが、私は初心者で、自分のして欲しいものと微妙に違うので書き換えられないので質問しました。詳細を書きます。分かりやすいように学校のテスト風に書きます。
＜元の物＞
│1学期中間テスト│
│氏名│国語│英語│数学│日史│・・・│合計│平均│国順│英順│数順│日順│・・・│総合│
│人Ａ│７０│８０│９０│８５│・・・│800 │88.9│3　 │5 　│　2 │ 3│・・・│1 │
│人Ｂ│６０│７０│８０│７５│・・・│790 │87.9│5 │7 　│ 4 │ 4│・・・│2 │
│・
│・
│・
│平均│５５│６０│７０│６４│・・・│515│578.2│
＜マクロ使用後＞
│1学期中間テスト│空白セル×２│人Ａ│
│　　│国語│英語│数学│日史│・・・│合計│平均│
│点数│７０│８０│９０│８５│・・・│800 │88.9│
│順位│3　 │5 　│　2 │ 3│・・・│1 │
│平均│５５│６０│７０│６４│・・・│515 │57.2│
│
│1学期中間テスト│空白セル×２│人Ｂ│
│　　│国語│英語│数学│日史│・・・│合計│平均│
│点数│６０│７０│８０│７５│・・・│790 │87.9│
│順位│5 │7 　│ 4 │ 4│・・・│2 │
│平均│５５│６０│７０│６４│・・・│515│578.2│
━以上━
(1)テスト名、科目名、人名等は毎回変わるのでそれに対応できるように。
(2)また、科目数、人数も変わるのでそれにも対応できるように。
(3)最初に、│テスト名│を入れ、２つ空白セルを作り、│名前│を入れる。
(4)次の行にセルを1つあけて、順に科目名、合計、平均
(5)次に「点数」といれて、その後に各人の点数
(6)次に「順位」といれて、その後に各人の順位
(7)最後に「平均」といれて、科目ごとの全体の平均
(8)人と人の間に空白の行１行
それぞれの合計、平均、科目ごとと総合の順位と科目ごとの平均はすでに算出されているものとしてつくっていただければ結構ですが、もしできれば、それも作ってくれるマクロだとなおさら嬉しいです。ちなみに│　│と「　」の違いは、前のセルを引用してくるか、その文字を新しく挿入するかの違いです。技術的に可能か不可能かも私にはわからないので、もし不可能ならばそのように教えてください。
お忙しいとは思いますが、よろしくお願いします。

回答No.5

mitarashi · ベストアンサー

#2です。見直したところ式に誤りがありましたので、照れ隠しに追加情報です。
<Sheet1>
|A||B|C|D|E|F|G|H|I|J|K
1||一学期中間テスト
2|氏名|国語|英語|数学|日史|合計|平均|国順|英順|数順|日順|総合
3|人A|70|80|90|85|325|81.25|3|5|2|3|1
4|人B|60|70|80|75|285|71.25|5|7|4|4|2
動的名前定義 Sheet1!はExcelが勝手につけるので省略可
項目、人数が変わっても名前の範囲が自動で変更されます
名前「一覧表」=OFFSET(Sheet1!$A$2,1,1,COUNTA(Sheet1!$A:$A)-2,COUNTA(Sheet1!$2:$2)-1)
名前「列見出し」=OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2)-1)
名前「行見出し」=OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-2,1)
注A列、2行目には余分な情報を加えないこと（項目数を誤認識してしまう）
http://office.microsoft.com/ja-jp/excel/HA011261151041.aspx

<Sheet2>
A||B|C|D|E|F|G|H|I|J|K
1|一学期中間テスト|||人A|1||
2||国語|英語|数学|日史|合計|平均
3|点数|70
4|順位|3
5|平均|
B3の式=INDEX(一覧表,$E$1,MATCH("国語",列見出し,0))
D1の式=INDEX(行見出し,E1,1)

<マクロの例>
Sub test()
Dim i As Long
For i = 1 To Sheets("Sheet1").Range("行見出し").Rows.Count
Sheets("Sheet2").Range("E1").Value = i
'ここに印刷するなり、他のシートに値複写するなり、処理を記述
'処理
Next i
End Sub

お礼

返事遅くなってすみません。
なるほど、これならできそうですね！
参考にさせていただきました。ありがとうございました。
やはり、自分でマクロを作成・修正できないのは難があるので、
ワードの差込印刷も含めてもう一度参考にさせていただきます。

回答No.6

DOUGLAS_

[回答番号：No.4] の DOUGLAS_ です。
＞参考にさせていただきましたが、どこかおかしいのか、きちんと表示されませんでした。
　何が「きちんと表示され」なかったのでしょうか？

　もし、＜元の物＞ のシート名が「Sheet1」でないような場合でしたら、[回答番号：No.4] と同様に、[回答番号：No.3] の操作をすべて行なった後で、[置換ダイアログ] を開き、[検索する文字列(N)] を
Sheet1
に、[置換後の文字列(E)] を
＜元の物＞のシート名
にして、[すべて置換(A)] してください。

　あるいは、[回答番号：No.3] の冒頭に
＞Sheet1 A1 に「1学期中間テスト」、A2 に「氏名」とします。
と書いてありますが、「1学期中間テスト」・「氏名」が、それぞれ＜元の物＞のシート の A1・A2 ではなかった場合は、さっぱりです！！

　また、
＞Sheet2 において、
と書いておりますように、[回答番号：No.3] の計算式は、＜元の物＞のシートとは別のシートの
A1:
などの書いてある通りのセルにコピペしてくださいね。

回答No.4

DOUGLAS_

[回答番号：No.3] の DOUGLAS_ です。
　ちょっと間違いがありました。

　[回答番号：No.3] の操作をすべて行なった後で、[置換ダイアログ] を開き、[検索する文字列(N)] を
$A$1:$A$14
に、[置換後の文字列(E)] を
$A:$A
にして、[すべて置換(A)] してください。

＞この私の回答に対してのご質問にはお答えいたしかねます
とは書いたものの、他にも間違いがあるかも知れませんので、不具合等についてのみお受けいたします。　　<(_ _)>

お礼

回答ありがとうございます。
参考にさせていただきましたが、どこかおかしいのか、きちんと表示されませんでした。

回答No.3

DOUGLAS_

Sheet1 A1 に「1学期中間テスト」、A2 に「氏名」とします。

　Sheet2 において、

A1:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,Sheet1!$A$1,"")

D1:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,INDIRECT("Sheet1!A" & INT(ROW()/6)+3),"")

A3:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,"点数","")

A4:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,"順位","")

A5:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,"平均","")

B2:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,IF(COLUMN()<=MATCH("平均",Sheet1!$2:$2,0),Sheet1!B$2,""),"")

B3:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,IF(COLUMN()<=MATCH("平均",Sheet1!$2:$2,0),OFFSET(Sheet1!$A$1,INT(ROW()/6)+2,COLUMN()-1),""),"")

B4:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,IF(COLUMN()<=MATCH("平均",Sheet1!$2:$2,0),OFFSET(Sheet1!$A$1,INT(ROW()/6)+2,MATCH("平均",Sheet1!$2:$2,0)+COLUMN()-2),""),"")

B5:
=IF(MATCH("平均",Sheet1!$A$1:$A$14,0)>INT(ROW()/6)+3,IF(COLUMN()<=MATCH("平均",Sheet1!$2:$2,0),OFFSET(Sheet1!B$1,MATCH("平均",Sheet1!$A$1:$A$14,0)-1,),""),"")

　B2:B5 を右方向に適当にコピー（２０列ぐらい？）

　1:6 を下方向に適当にコピー（人数分以上）

＞私は初心者で、自分のして欲しいものと微妙に違うので書き換えられないので質問しました。
とのことですが、この私の回答に対してのご質問にはお答えいたしかねますので、不明な点がございましたら、誠に恐縮ながら、ご自分でお考えください。

　エクセルで普通に使う関数しか使用しておりませんので、ヘルプなりＷＥＢ検索なりで解説をお求めください。

回答No.2

mitarashi

親切な方がコードを書いてくれるかもしれませんが、メンテナンスはどうするんでしょうね．．．
初心の内は、エクセルの機能をなるべく生かし、マクロは簡単なものにするのが吉だと思います。
|A||B|C|D|E|F|G|H|I|J|K
1||一学期中間テスト
2|氏名|国語|英語|数学|日史|合計|平均|国順|英順|数順|日順|総合
3|人A|70|80|90|85|325|81.25|3|5|2|3|1
4|人B|60|70|80|75|285|71.25|5|7|4|4|2
というデータがあったとして、
=INDEX(A2:L4,MATCH("人A",A2:A4,0),MATCH("英語",B2:L2,0))
は70を与えます。必要な情報について同様に繰り返せば、個人票が出来ます。検索範囲を元表の拡大・縮小に応じて動的に変化させる事も可能です。
"人A"をセルから与える様に変更して、マクロでその値を名簿から参照してくる様なループを組めば、全生徒に対して処理できます。これならマクロも至極簡単です。
ご参考まで。

お礼

回答ありがとうございます。
なるほどそのような方法もあるのですね。
参考にさせていただきます。

回答No.1

n-jun

＞│氏名│国語│英語│数学│日史│・・・│合計│平均│国順│英順│数順│日順│・・・│総合│
この”・・・”ってデータ列数はどの位を意味しているのでしょう？

どこにデータがあって、どこにどのように表示（振り分け？）したいのか
回答者側で判断し提示すれば、あとは修正してもらえるのかな？とちょっと疑問でした。

回答ではなくすいません。

補足

ご回答ありがとうございます。
おそらく科目としては５～１０列ほどになると思います。