複数の学生の複数の試験の得点を管理したいのですが、効率の良い方法があれば教えてください。
200名ほどの学生が合計13回試験を受験しました。これまで各試験結果を名前でソートして、名簿順に並べていましたが、全員が全部の試験を受験している訳ではなく、ソートした後に、目で確認しながら未受験の学生の部分に空白を入れて名簿に合わせていました。ただ学生数も試験回数も多いので非常に時間がかかり、間違いも発生し、非効率的です。
もっと正確で効率の良い方法があればご教示お願いします。
尚、EXCELでよく使うのは、合計、ソート、カウント程度です。
A 回答 (8件)
- 最新から表示
- 回答順に表示
No.8
- 回答日時:
No.7 の duffer_dog です。
長くなりますが、画像とコードを掲載します。エクセルシートのSheet1に添付ファイルの一番上のフォーマットでシートを作成します。氏名は全生徒の氏名になります。Sheet2以降に画像の真中のシートを追加していきます。氏名は試験結果のある生徒だけでかまいません。シートは何枚でも可能です。1列目から3列目まではこのフォーマットを守ってください。
次に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さま、
詳しくありがとうございます。すぐには時間がなくて出来ないのですが、明日試してみます。取り急ぎ御礼申し上げます。やりましたら再度ご報告させてください。取り急ぎ
No.7
- 回答日時:
VBAで組んでみたのですがご確認いただく方法がないのですね。
完成してから送る方法がないことがわかりました。ボタンひとつでまたたく間に転記完了するようにしたのですが残念です。
またの機会に。
No.6
- 回答日時:
マクロで処理する前提での補足要求です。
もし、マクロでの処理を望まない場合は、補足不要です。
1.試験結果は必ず試験1~試験13まで全てそろっていますか?
それとも、逐次、試験結果を追記していくのでしょうか。
2.その場合、1シートに1つの試験結果が表示されてますか。
3.名簿は、シート名:学生名簿 に添付図のレイアウトで作成されている前提で良いですか。
4.集計結果は、シート名:集計表 に添付図のレイアウトで作成しますが良いでしょうか。
(あなたが作成するのは空のシート:集計表です。1行目の見出しはあなたが作成してください。2行目以降はマクロが作成します)
5.試験結果はシート名:試験1~試験13で、レイアウトは添付図のように作成されている前提で良いですか。
(試験1~13の数字1~13は半角です。もし、シート名をこのように作成するのが大変な場合は、その旨補足してください)
添付画像が見にくい場合は、下記を参照ください。
https://gyazo.com/733cc5aa1bdb57d8e65d7ce2bb87033f
No.5
- 回答日時:
#3と#4で回答した者です。
失礼なことを申し上げてすみませんでした。#1さんへの図解を見て、意味が分かりました。
学生名簿と試験1、試験2、試験3を結合させるとできます。
VLOOKUPか、INDEX-MATCH関数を使えば可能です。
INDEX-MATCHは関数の組合せなので、慣れないとできません。
値を拾えるか、拾えない場合はエラーになるので、IFERROR関数も必要です。
各表の行列が分からないので、具体的な関数は書けませんが…。
=IFERROR(VLOOKUP(学生番号&試験区分,試験の範囲を絶対参照指定,3,0),"")
のイメージです。学生番号の番地は行は相対参照、列は絶対参照、試験区分番地は行は絶対参照、列は総体参照。
No.4
- 回答日時:
自身、学校現場で成績入力を長く関わっていますが、
質問者のストーリーが全く分かりません。
>試験に欠席したら、試験結果データにはその学生情報自体載っていません。
↑試験結果データは誰が入力するのですか?。一科目だけとして、仮に200人の学生がいて、10人欠席なら、190枚のペーパー答案があるはずです。ペーパーを見ながら、質問者さんが入力するのでしょうか?。
外部委託などで、入力されたエクセルの「試験結果データ」を質問者さんが受け取るのでしょうか。その場合は、190人分があり、10人分がないってことでしょうか。
>学生名簿と同じにはならないのです。
↑「学生名簿」のエクセルでお持ちなのですね。この学生名簿には、学生番号がないのでしょうか。学生名簿がペーパーのみで、エクセルで存在しないのならば、エクセル「学生名簿」を作らないと、整理するのが困難です。
そもそも試験を受ける際に、学生は試験に「学生番号」を書かないのでしょうか。
偉そうなことを言ってすみません。本当に言っていることが分かりません。
「試験結果データ」そのものが、外部委託などでエクセルデータになっているならば、欠席者にはデータを渡す必要もないので、委託業者に全面依頼する。その場合ならエクセルそのものを質問者さんは苦労して触ることもないです。
「試験結果データ」に入力するのが質問者さんならば、用意された「学生名簿」に点数を入力する。その際に、欠席者の点数は入力しない(空白のままにする)
大変申し訳ありません。長年エクセルを携わってきた者からの意見です。質問者さんの一つ一つは単語になっていますが、単語と単語の前後から、名簿う点数はペーパーのデータなのか、エクセルのデータなのか、自身は読み取ることは困難でした。(業務フローを提示いただきたいです。)
No.3
- 回答日時:
操作の全容が見えません。
ワークシートの図がないので正確なイメージが分かりません。
学生の名前でソート。
ソート範囲は各科目の点数。
シートは一つで、13回分の試験の点数があるのか。
13個のシートがあって各シートに13回分個々の点数があるのか。
試験欠席したら、点数は入力せずに空白ですね。
合ってますか?
質問者さんの求めている意図と同じかどうかわかりませんが、思ったことを書きます。
自身なら、学生の番号をつける。ソートキーは、名前でなく学生の番号を使う。
欠席か否かの項目を作る。
入力前にソートする。ソートキーは、①欠席、②学生番号。ソート範囲は点数も含める。欠席者と受験者が分かれてから順次入力
順番戻したいなら、学生番号でソートする。範囲は同じ。
質問者さんの正確な意図が分からないので、言葉足らずかもしれません。
No.2
- 回答日時:
200名の学生がいて、さらに13回の試験データがくるとなると、うまく工夫しないと「目で確認」っていうレベルの限界を超えていると思います。
昔、件数的には似たような感じのEXCELで管理したデータを半年以上かけても整理できないって言っている方がいました。
EXCEL VBAでプログラミングしてあげたら、一瞬で処理できたことがあります。
No.1さんの案にあるように、一工夫したほうがいいでしょうね。
No.1
- 回答日時:
合計とは学生毎の合計点という感じで、ソートは試験毎の学生の成績のようなイメージでしょうか?
考え方としては
1)縦(行)に学生の一覧を、横(列)に試験毎の点数を記載する一覧表シートを作る
2)各試験をシート毎に分離して管理する
でしょうかね・・・
で1ならば、一覧(元データ)シートはデータ入力専門にして、合計とか成績順のような加工データは、元データを参照した別シートで行う
こうすれば元のデータは常に一定の形式と並び順なので加工(参照)する場合もやりやすい
2の場合は、各試験を横並びに見るのは面倒だけど個々の試験に関するデータだけなのでシンプルに出来る感じかな
私なら1の方がデータベースっぽい使い方出来るから、融通は利きそうに思える
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL 下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 第二回模試の3科目の各得点と合 1 2023/04/25 18:02
- その他(ビジネス・キャリア) 資格取得の挑戦回数と合格率 2 2022/10/22 04:38
- 簿記検定・漢字検定・秘書検定 1週間後にネット試験で日商簿記3級を受験する商業高校生です。 1 2022/09/12 22:29
- 大学受験 京大特色入試の受験を検討しております。 4 2022/05/23 19:13
- その他(ニュース・社会制度・災害) 小室圭さん米国での司法試験。 初回は全然ダメでした。 2回目は「5点足りなかった(本人談)」 今回で 2 2022/10/21 14:21
- その他(職業・資格) 確率の問題?試験の合格率の算出について 1 2022/08/05 17:42
- 簿記検定・漢字検定・秘書検定 4月から商業高校の会計科に通う予定の中学生です。現在春休みで、簿記検定3級を取得しようと考えており、 1 2023/03/04 14:40
- 統計学 統計学の質問【帰無仮説】 高校の新学習指導要領では、統計的仮説検定の基本的な考え方が必修単元となった 5 2023/05/23 21:00
- 統計学 数学です 4 2022/07/20 23:06
- 大学・短大 Fラン大学の教育学部は、教員採用試験の一次試験には沢山合格します(割合でみると沢山というほど多くはあ 8 2022/08/05 03:03
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
真空遮断機の真空度試験方法に...
-
C言語の単体試験が分かりません
-
電通大の2次試験は整数の性質や...
-
EXCEL 就職試験実技
-
地盤改良セメントからの六価ク...
-
縮尺の1/100から1/250への変更...
-
自動車免許試験2回落ちました。...
-
100均のセリアに電工ペンチやギ...
-
中途採用で、筆記試験があるよ...
-
生命保険の一般課程試験について
-
屋根裏配線についてジョイント...
-
社労士試験勉強のやめどき
-
アンギラスという工具について
-
電極棒 満減水テストについて
-
電気工事士技能試験について、 ...
-
ソケットレンチをはずすのにイ...
-
コンデンサ用LBSによくトリップ...
-
車の免許の取得は簡単と言われ...
-
第二種電気工事試験について
-
免許センターの当日の試験問題...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
3相一括の充電電流の計算
-
私は職業訓練校に通っています...
-
真空遮断機の真空度試験方法に...
-
ある部品の試験サンプル数の決...
-
変圧器絶縁試験と加圧法と誘導...
-
スイッチング電源の耐電圧試験は
-
JIS 100洗80点?
-
どのくらい滑れれば、JSBAの2...
-
微生物学的力価試験の受託試験...
-
打音試験について
-
ハンターハンターのキメラアン...
-
地盤改良セメントからの六価ク...
-
C言語の単体試験が分かりません
-
FP2級 登録免許税と不動産取得税
-
Excel 効率的な名簿と得点の管...
-
ICND1とICND2の違いについて
-
大抵の試験監督って、試験が始...
-
数値制御フライス盤のペーパー...
-
ソフト開発における試験研究費...
-
EXCEL 就職試験実技
おすすめ情報
「これまで各試験結果を名前でソートして」と書きましたが、正確には『学生番号』でソートしています。
画像を添付しましたのでお参照いただけると助かります。よろしくお願いします。
>試験欠席したら、点数は入力せずに空白ですね。
合ってますか?
早くにご回答ありがとうございます。試験に欠席したら、試験結果データにはその学生情報自体載っていません。ですので、ソートしても、学生名簿と同じにはならないのです。ですので、目で確認しながら、未受験者の欄を追加して、名簿と同じにしています。その作業が辛いです。