Excel 効率的な名簿と得点の管理の仕方

複数の学生の複数の試験の得点を管理したいのですが、効率の良い方法があれば教えてください。

200名ほどの学生が合計13回試験を受験しました。これまで各試験結果を名前でソートして、名簿順に並べていましたが、全員が全部の試験を受験している訳ではなく、ソートした後に、目で確認しながら未受験の学生の部分に空白を入れて名簿に合わせていました。ただ学生数も試験回数も多いので非常に時間がかかり、間違いも発生し、非効率的です。
もっと正確で効率の良い方法があればご教示お願いします。
尚、EXCELでよく使うのは、合計、ソート、カウント程度です。

質問者からの補足コメント

• 

  「これまで各試験結果を名前でソートして」と書きましたが、正確には『学生番号』でソートしています。
  
  画像を添付しましたのでお参照いただけると助かります。よろしくお願いします。

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2022/08/07 10:31

• >試験欠席したら、点数は入力せずに空白ですね。
  合ってますか？
  
  早くにご回答ありがとうございます。試験に欠席したら、試験結果データにはその学生情報自体載っていません。ですので、ソートしても、学生名簿と同じにはならないのです。ですので、目で確認しながら、未受験者の欄を追加して、名簿と同じにしています。その作業が辛いです。

  No.3の回答に寄せられた補足コメントです。 補足日時：2022/08/07 10:35

No.8 回答者： duffer_dog 回答日時： 2022/08/08 13:22

No.7 の duffer_dog です。

長くなりますが、画像とコードを掲載します。エクセルシートのSheet1に添付ファイルの一番上のフォーマットでシートを作成します。氏名は全生徒の氏名になります。Sheet2以降に画像の真中のシートを追加していきます。氏名は試験結果のある生徒だけでかまいません。シートは何枚でも可能です。1列目から３列目まではこのフォーマットを守ってください。

次にVisual Basic Editorを起動します（Alt + F11 キー）。
左ペインの「thisworkbook」をダブルクリックして開いた右ペインに以下のコードを貼り付けてください。（画像の下を参照）

次にVBAを実行するボタンを作ります。メニューバーの「挿入」ー「図」ー「図形」の四角形から適当なものを選んでセル（1, 3）あたりに四角形を書く。「転記」などの名前をつけておく。図形を右クリックして「マクロの登録」を選ぶとマクロ名のダイアログボックスが出るので「Thisworkbook.sumup」を選ぶ。
以上で終わりです。

使い方は
・各試験シートの左上の色の付いているセルに試験名を入れます
・試験シートには「試験結果」シートと同じ生徒氏名とテストの結果を入力します
・「転記」ボタンを押します

各試験シートの試験名を変更すると下部の「シート名」と「試験結果」シートに転機されます。したがってシート名に使えない文字列は試験名に使えません。 「:」、「\」、「/」、「?」、「*」、「[」、「]」などがあるようですがこれらはすべて”_”に変換するようにしました。「9/1」などは「9_1」などになります。
試験シートは既存の試験シートのコピーなどで増やせます。その時試験名を再入力すれば「試験結果」シートに試験名が追加されます。

※点数の転記は生徒氏名を使っていますので「試験結果」の氏名を各試験点数のシートと同じでなければなりませんのでコピーしてください。空白を含めて違っていれば検索出来ません。
※使わないシートは削除されません。シートを手で削除して「試験結果」の該当試験名列を消去してください。
※「試験結果」の合計列の参照式は自動で増加します。

以下VBAコード：
''
'' 試験名を変更したときシート名の変更と集計項目の変更
'
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim i As Integer
Dim n As Integer
Dim nm As String
Dim lc As Integer
Dim lr As Integer

Dim m As Integer

Application.EnableEvents = False
If Target.Row = 1 And Target.Column = 1 Then
n = Sh.Index
nm = toSheetname(Sh.Cells(1, 1))
' テスト名が日付の場合の処理
Worksheets(n).name = nm
Worksheets(1).Cells(2, n + 2) = nm
'
With Worksheets(1)
lc = .Cells(2, 1).End(xlToRight).Column
lr = .Cells(2, 2).End(xlDown).Row
.Range(.Cells(3, 3), .Cells(lr, 3)).FormulaR1C1 = "=SUM(RC4:RC" & lc & ")"
End With
End If
Application.EnableEvents = True
End Sub


'
Sub sumup()
Dim test As String
Dim i As Integer
'
Application.EnableEvents = False
' 記入済み値を消去する
Worksheets(1).Cells(1, 1).CurrentRegion.Offset(2, 3).Clear
' 試験シートを順次転記処理
For i = 2 To Worksheets.Count
' 転記
copyToSumSheet Worksheets(i).name
Next
Application.EnableEvents = True
End Sub

' 転記作業
Sub copyToSumSheet(tn As String) ' t : sheet name
Dim l As Integer
Dim r As Integer
Dim c As Integer
Dim name As String
Dim i As Integer
'Dim sn As String
Dim sumsheet As Worksheet

Set sumsheet = Worksheets(1) ' sumup sheet
With Worksheets(tn)
c = sumsheet.Rows(2).Find(tn).Column
l = .Cells(1000, 2).End(xlUp).Row
For i = 3 To l
If .Cells(i, 2) <> "" Then
name = .Cells(i, 2)
r = sumsheet.Columns(2).Find(name).Row
sumsheet.Cells(r, c) = .Cells(i, 3)
End If

Next
End With
End Sub

' シート名で使用できないものを変換する
' 使用できないシート名「:」、「\」、「/」、「?」、「*」、「[」、「]」
'
Function toSheetname(strSheetname As Variant) As String
Dim chrNot As Variant
Dim c As Variant
Dim d As Integer
Dim strNewName As String

' 使用できないシート名「:」、「\」、「/」、「?」、「*」、「[」、「]」
chrNot = Array(":", "\", "/", "?", "*", "[", "]")
strNewName = Format(strSheetname, "mm/dd")

For Each c In chrNot
d = InStr(strNewName, c)
If d > 0 Then
strNewName = Replace(strNewName, c, "_")
End If
Next c

toSheetname = strNewName
End Function

動作確認は充分しましたがエラーが出るときはご連絡ください。

この回答へのお礼

duffer_dogさま、
詳しくありがとうございます。すぐには時間がなくて出来ないのですが、明日試してみます。取り急ぎ御礼申し上げます。やりましたら再度ご報告させてください。取り急ぎ

お礼日時：2022/08/09 07:43

No.7 回答者： duffer_dog 回答日時： 2022/08/07 23:33

VBAで組んでみたのですがご確認いただく方法がないのですね。

完成してから送る方法がないことがわかりました。
ボタンひとつでまたたく間に転記完了するようにしたのですが残念です。
またの機会に。

No.6 回答者： tatsumaru77 回答日時： 2022/08/07 14:55

マクロで処理する前提での補足要求です。

もし、マクロでの処理を望まない場合は、補足不要です。
１．試験結果は必ず試験１～試験１３まで全てそろっていますか？
それとも、逐次、試験結果を追記していくのでしょうか。
２．その場合、１シートに１つの試験結果が表示されてますか。
３．名簿は、シート名：学生名簿　に添付図のレイアウトで作成されている前提で良いですか。
４．集計結果は、シート名：集計表　に添付図のレイアウトで作成しますが良いでしょうか。
（あなたが作成するのは空のシート：集計表です。１行目の見出しはあなたが作成してください。２行目以降はマクロが作成します）
５．試験結果はシート名：試験1～試験13で、レイアウトは添付図のように作成されている前提で良いですか。
（試験1～13の数字1～13は半角です。もし、シート名をこのように作成するのが大変な場合は、その旨補足してください）

添付画像が見にくい場合は、下記を参照ください。
https://gyazo.com/733cc5aa1bdb57d8e65d7ce2bb87033f

No.5 回答者： kamejiro 回答日時： 2022/08/07 14:25

#3と#4で回答した者です。

失礼なことを申し上げてすみませんでした。
#1さんへの図解を見て、意味が分かりました。
学生名簿と試験１、試験２、試験３を結合させるとできます。
VLOOKUPか、INDEX-MATCH関数を使えば可能です。
INDEX-MATCHは関数の組合せなので、慣れないとできません。

値を拾えるか、拾えない場合はエラーになるので、IFERROR関数も必要です。
各表の行列が分からないので、具体的な関数は書けませんが…。
=IFERROR(VLOOKUP(学生番号&試験区分,試験の範囲を絶対参照指定,3,0),"")

のイメージです。学生番号の番地は行は相対参照、列は絶対参照、試験区分番地は行は絶対参照、列は総体参照。

No.4 回答者： kamejiro 回答日時： 2022/08/07 14:08

自身、学校現場で成績入力を長く関わっていますが、

質問者のストーリーが全く分かりません。

＞試験に欠席したら、試験結果データにはその学生情報自体載っていません。
↑試験結果データは誰が入力するのですか？。一科目だけとして、仮に200人の学生がいて、10人欠席なら、190枚のペーパー答案があるはずです。ペーパーを見ながら、質問者さんが入力するのでしょうか？。
外部委託などで、入力されたエクセルの「試験結果データ」を質問者さんが受け取るのでしょうか。その場合は、190人分があり、10人分がないってことでしょうか。

＞学生名簿と同じにはならないのです。
↑「学生名簿」のエクセルでお持ちなのですね。この学生名簿には、学生番号がないのでしょうか。学生名簿がペーパーのみで、エクセルで存在しないのならば、エクセル「学生名簿」を作らないと、整理するのが困難です。
そもそも試験を受ける際に、学生は試験に「学生番号」を書かないのでしょうか。

偉そうなことを言ってすみません。本当に言っていることが分かりません。
「試験結果データ」そのものが、外部委託などでエクセルデータになっているならば、欠席者にはデータを渡す必要もないので、委託業者に全面依頼する。その場合ならエクセルそのものを質問者さんは苦労して触ることもないです。
「試験結果データ」に入力するのが質問者さんならば、用意された「学生名簿」に点数を入力する。その際に、欠席者の点数は入力しない（空白のままにする）

大変申し訳ありません。長年エクセルを携わってきた者からの意見です。質問者さんの一つ一つは単語になっていますが、単語と単語の前後から、名簿う点数はペーパーのデータなのか、エクセルのデータなのか、自身は読み取ることは困難でした。（業務フローを提示いただきたいです。）

No.3 回答者： kamejiro 回答日時： 2022/08/07 09:03

操作の全容が見えません。

ワークシートの図がないので正確なイメージが分かりません。
学生の名前でソート。
ソート範囲は各科目の点数。
シートは一つで、13回分の試験の点数があるのか。
13個のシートがあって各シートに13回分個々の点数があるのか。
試験欠席したら、点数は入力せずに空白ですね。
合ってますか？

質問者さんの求めている意図と同じかどうかわかりませんが、思ったことを書きます。
自身なら、学生の番号をつける。ソートキーは、名前でなく学生の番号を使う。
欠席か否かの項目を作る。
入力前にソートする。ソートキーは、①欠席、②学生番号。ソート範囲は点数も含める。欠席者と受験者が分かれてから順次入力
順番戻したいなら、学生番号でソートする。範囲は同じ。
質問者さんの正確な意図が分からないので、言葉足らずかもしれません。

No.2 回答者： lv4u 回答日時： 2022/08/07 08:41

２００名の学生がいて、さらに１３回の試験データがくるとなると、うまく工夫しないと「目で確認」っていうレベルの限界を超えていると思います。

昔、件数的には似たような感じのEXCELで管理したデータを半年以上かけても整理できないって言っている方がいました。
EXCEL VBAでプログラミングしてあげたら、一瞬で処理できたことがあります。

No.1さんの案にあるように、一工夫したほうがいいでしょうね。

No.1 回答者： trajaa 回答日時： 2022/08/07 08:34

合計とは学生毎の合計点という感じで、ソートは試験毎の学生の成績のようなイメージでしょうか？

考え方としては
1）縦(行)に学生の一覧を、横(列)に試験毎の点数を記載する一覧表シートを作る
2）各試験をシート毎に分離して管理する
でしょうかね・・・

で1ならば、一覧(元データ)シートはデータ入力専門にして、合計とか成績順のような加工データは、元データを参照した別シートで行う
こうすれば元のデータは常に一定の形式と並び順なので加工(参照)する場合もやりやすい

2の場合は、各試験を横並びに見るのは面倒だけど個々の試験に関するデータだけなのでシンプルに出来る感じかな

私なら1の方がデータベースっぽい使い方出来るから、融通は利きそうに思える