excelでテストの成績処理をしたいのですが

質問

私は塾の講師をしているのですが、毎日、生徒に５教科の小テストを行っています。
それで、毎日、５教科のそれぞれの点数と合計点数を打ち込んで、壁に張り出しています。
そのデータは、一つのブックに日付けごとでシートを分けて保存してあります。
今回、その日付別の表のデータから、毎回の成績を生徒別の表に変えて、プリントにして生徒一人一人に配ろうかと考えています。ですが回数と人数がとても多いので、一人一人「コピー→貼り付け」を繰り返していたのでは時間がかかりすぎます。
なので、マクロで自動的にやりたいと考えたまではいいんですが、実際どういうプログラムにすればいいのか皆目検討がつきません。
表の形はすべて、A列に順位、B列に名前、C列からG列までが各教科の点数、H列にSUM関数で合計点数。最後の行に各教科と合計の平均点を関数で出している。という表です。
表はすべて名簿の順にソートしてあります。
これらの表から一人一人の毎回の点数を取り出して、自分の毎回の点数が縦に並んでいる別の表を作成したいのです。
どういうやり方をすればいいのでしょうか？

回答No.5

grumpy_the_dwarf · ベストアンサー

> 順位の項を最後の列に付け加えようと思ったのですが、
vlookup関数ではB列で検索してA列を返すことは出来ません。そうい
う時はmatch関数で検索値を見つけた場所を返してもらい、そこからA
列の値を引っぱり出します。
=INDEX(INDIRECT("[日々の成績]"&TEXT($B6, "m月d日")&"!"&
"$a:$a"), MATCH($H$3,INDIRECT("[日々の成績]"&TEXT($B6, "m月d日
")&"!"&"$B:$B"), 0))
といった感じですね。あぁ、なんだか長くて嫌になってきた。A列あ
たりに非表示で
="[日々の成績]"&TEXT($B6,"m月d日")&"!"&""
とかやっといて、
=VLOOKUP($H$3,INDIRECT($A6&"$B:$E"), 4, FALSE)
=INDEX(INDIRECT($A6&"$a:$a"), MATCH($H$3,INDIRECT($A6&
"$B:$B"), 0))
ですませたい気持ち。

> なにぶんエクセルは全部独学で、
私も独学です。ズボラな性格なので本を買って読んで勉強するなんて
とんでもないです。

回答No.6

imogasi

＞簡単にうまくいきました。と言うことですので、今更言うべきことはないようですが、関数で解決できたから良かったものの、私にはそう簡単といえるかどうか？？？です。
エクセルはとっつきやすく、データが見えたまま処理できて良いのですが本件のように、レコードを組替えて（クラス別－＞個人別など）加工しようとするととたんに難しくなります。現状のファイルを利用しなければならない本件の解決には役立ちませんが、
（１）成績管理ソフトを探すことです。ソフトは作る時代でなく、使う時代です。各学校、各先生は必ず何かを使っているものとおもいます。
（２）自分で自由にやるなら、今後はアクセスなどの利用をお勧めします。
先生ならアカデミックパック（ソフト）が１２０００円で買えます。
一般に、関数の利用でレコードを検索し抜き出してくる解決は相当複雑になり、思いつくこと、理解することも難しいものになりがちです。
ある時のニーズでは、クラス別の成績表がピッタリですが
日付順にワークシートを作ったと言うのも他への変更を難しくしています。アクセスなら目的別にデータを固定してデータを持つので無く、後のことも考えて、汎用的に（緩い秩序で）データレコードを記録するでしょう。
そして（Ａ）必要なものを抜き出す。例えばテスト日とか
何回目テストとか、ある科目とか。（Ｂ）そして集計する。順位を振る、平均を出すなど加工をする（Ｃ）最後に印刷する、表示する。などを行います。データベースソフトは（Ａ）に適した機能があります。
今後はそういう方向にするにしても、今纏まって持っている情報を使って何とかしなければならないわけですが。
（３）そこでオフラインバッチ処理の方法を述べます。
これは必要な時に毎回時間をかけて処理するので、その後の変更は盛りこまれていないですが、ソートを多用する方法で、わかりやすく、データが多いとトータル処理時間が早いです。ワークシートのデータを１つのファイルデータにまとめます。その際見出しや集計行が邪魔ですので省きます。
一本化した後に、生徒番号順（かつ日付け順）にソートします。これでテストのレコード（エクセルで行に相当）は生徒別に固まります。
そして初めからそのファイルを読んで生徒番号が変るまで行（毎回テスト成績）を印字します。
生徒番号が変ると、前の生徒の合計を出したり、平均を出したりの処理をして印刷した紙を排出します。そして次ぎの生徒の紙シート印刷にかかります。
見出しや罫線などは、プログラムで印刷します。
またはエクセルの見だし付き罫線表の行のデータ用セルにデータをセットし、読んだレコードの生徒番号が変ると、前の生徒分をＰｒｉｎｔＯｕｔし、データ範囲をクリアします。差し替え印刷的な処理です。
シートの演算式は使えます。しかしＶＢＡの利用になります。

回答No.4

grumpy_the_dwarf

元になる成績表が「日々の成績」ブックに「8月5日」、「8月6日」の
ように日付をシート名として整理されているとします。

新しく「個人成績」ブックを作り、シート名を生徒の氏名にします。
もちろん、配付する時のためにどこかのセルに
=MID(CELL("filename", A1),FIND("]",CELL("filename", A1))+1,99)
のようにしてシート名を取り出します。例としてH3にしておきまし
た。

「個人成績」ブックのメインは日付と各教科成績の列ですね。日付は
手入力して下さい。日付をB6に書いたとしてシート名の生徒の該当す
る日の1教科めの成績は、
=VLOOKUP($H$3,INDIRECT("[日々の成績]"&TEXT($B6, "m月d日")&"!"&
"$B:$G"), 2, FALSE)
となります。以下第3パラメータを増やしていくと、2教科目以降も表
示されます。

1日分出来たらオートフィルを駆使して1人分完成させ、後はシートを
複製してシート名を書き換えれば全員分完成します。

補足

ありがとうございます。簡単にうまくいきました。
あとひとつ、順位の項を最後の列に付け加えようと思ったのですが、順位の項は前述したようにA列にあるので、数値を増やしていくだけでは表示されません。負の値を入れてみたら、「＃VALUE」と表示されます。
この場合どうすればいいのでしょうか？
なにぶんエクセルは全部独学で、ヘルプを頼りに今までやってきたのでそろそろちゃんと勉強したいと思っています。いろいろな関数やVBとか。お勧めの本などあったらお教え願えませんか？

回答No.3

mohenjo

No.1の方が言われる様に日付フィールドを設け、1枚の
シートで１行目を、日付、順位、名前、・・・・と見出し
にする場合（C列が名前）ノーマルなブック仕様（シート名）でSheet4以降に各自のデータが出る筈ですが！
Sub test()
Dim rngS As Range
Set rngS = ActiveSheet.UsedRange
Dim rngT As Range
Set rngT = Sheets(2).Range("A1:A2")
Dim rngI As Range
rngS.Columns("C").AdvancedFilter action:=xlFilterCopy, _
copytorange:=rngT.Range("A1"), unique:=True
For Each rngI In rngT.Worksheet.Range(rngT.Range("A2"), rngT.End(xlDown))
rngI.Copy rngT.Range("A2")
rngS.AdvancedFilter action:=xlFilterCopy, criteriarange:=rngT, _
copytorange:=Worksheets.Add(after:=Worksheets(Worksheets.Count)).Range("A1"), _
unique:=False
Next rngI

End Sub
　思惑違いならすいません。

回答No.2

lilact

もっとよい方法があるかもしれませんし、マクロではありませんが。

個人の表を作るシートを個人シートとして、日付ごとのある人のもとデータがＡ１から始まっているとしたら。

個人シートのあるセルに「=」と入力してからある日付のシートＡ１のセルをクリックしてエンターを押します。
そのセルを「＋」ポインター状態で右にドラッグしていけば一人分のある日の順位から平均までがコピーされます

あるセルの下のセルにも「=」と入力して、別の日のシートＡ１のセルをクリックしてエンター。それを右にドラッグ。これを日付分やります。

こうやって、一人分の複数のシートにあるデータをまとめます。
この一人分のデータを範囲選択して、下の空きスペースに人数分貼り付けます。もとデータの行番号が飛び飛びになりますので、あとで「置換」を使って一人ずつ修正します。
これで、個人ごとの別々のシートにあるデータが人数分の個人の表にまとまります。

回答No.1

papayuka

日付毎にシートを別けずに、日付フィールドを加えて同一シートにした方が処理しやすいと思います。

例えば
日付　順位　名前　国語　英語　算数　理科　社会　合計　平均

Accessとかでやった方が無難な気もしますし。。