青森県総合学校教育センター コンピュータ利用技術研究講座報告［2008.3］ Ｆ４－０３
校務の情報化
Microsoft Excel および VBA を利用した成績処理システムの構築
青森県立大湊高等学校 教諭 成 田 健 要 旨
現在，本校で行われている成績処理は Microsoft Excel と VBA を利用している。年度初めに， 成績入力ファイルを講座数だけ作成しているが，この作業には多くの時間と労力を必要とする。 また，成績入力のミスも毎回発生している。そこで，本研究では，Microsoft Excel をベースに， VBA のユーザーフォームや CSV ファイルを利用することで，教務部担当者の負担を軽減し，成績 入力ミスが発生しない，効率の良いシステムの構築を目指した。 キーワード：校務の情報化 成績処理 Excel VBA Ⅰ はじめに 現在，高等学校の成績処理は，外部業者が作成したシステムか，または，校内で自作したシステムで運用 されている。外部業者が作成したシステムは，学校の現状にあわせカスタマイズすることができ，出欠管理 から成績処理や調査書の発行等データの一元管理が可能である。また，トラブルが発生した際にも，迅速な サポートを受けることができる。しかし，購入予算や年間保守予算の捻出，生徒の個人情報保護の問題など クリアしなければならない問題も多く，すべての学校で外部業者が作成したシステムを利用できるわけでは ない。
本校では，Microsoft Excel (以下 Excel と記す)を利用し，校内で自作したシステムで成績処理を行って いる。Microsoft Access 等のデータベースを利用する方法もあるが，クライアントにあたる校内のすべての パソコンにインストールされているわけではなく，利用するためには新たに購入する必要があるため，実用 的ではないと考えた。 そこで，本研究では，購入時にほとんどのパソコンにインストールされており，教職員にも馴染み深い Excel をベースに成績処理システムを構築した。 Ⅱ 研究のねらい 平成１７年度まで本校で運用されていた成績処理システムは，一覧表形式の Excel ファイルをネットワー ク上で共有し，授業担当者が自分の担当している生徒のセルに，点数や欠課時数等を入力する方法をとって いた。クラス全員が履修している科目は問題ないのだが，総合学科では選択科目が多いため，入力するセル は不連続である。そのため，入力時の科目（縦列）の選択ミスや，生徒（横列）の選択ミスが多かった。さ らに，ＨＲ担任は，教務部から出される成績一覧表と教科担任から提出される手書きの成績伝票とを照合し， 入力ミス等のチェックをする作業があり，成績一覧表が完成するまでには多くの時間を必要とし，効率的で はなかった。 平成１８年度からは，講座(科目を授業担当者毎に区別したもの)毎の入力ファイルをネットワーク上に置 き，それに授業担当者が入力する方法に変更したところ，入力するセルは連続したものになったため，以前 のような入力ミスはなくなった。また，教務部から個人票を作成し，素点や欠課時数を生徒に確認させるこ とで，確認作業がスムーズになり，ＨＲ担任の負担も減った。 しかし，教務部担当者にとって，年度初めに講座毎のファイルを準備することは，非常に負担が大きいも のであった。また，最近は，教科担当者それぞれが Excel で成績データを管理する場合も多いが，round 関 数等を利用して整数値に直さないまま，コピー＆ペーストで成績入力を済ませてしまうミスも発生するよう になった。
そこで，本研究では，Excel をベースに，VBA を利用してシステムを開発し，ユーザーフォームや CSV フ ァイルの活用を研究することで，入力ミスがなく，効率の良い成績処理システムを構築することをねらいと した。 Ⅲ 研究の実際とその考察 １ 全体構造の設計 研究の構想段階で，成績データを Excel のワークシート上に入力する方法も考えたが，この場合，［選択 者名簿のコピー＆ペースト］⇒［名前を付けて保存］という手順を，講座の数だけ繰り返し行う必要があっ た。 本校は，今年度２７５の講座を開講しており，この入力ファイルを作成する作業は教務部担当者にとって 負担が大きいものである。そのため，成績処理の流れを以下のように設計し，できるだけ負担を軽減するこ とを目指した。（表１，図１）その際，データは CSV 形式で保存し，入力や処理操作にはユーザーフォーム の活用を考えた。 表１ 成績処理の流れ ２ 成績処理システムの構築 (1) 年度初めに準備するファイルの作成 前年度の１１月に，次年度履修する科目が本登録され，それ以降の履修科目の変更はできないことにな っている。Excel シートの列には開設されているすべての科目名，行には生徒名が記載されており，生徒 が履修した科目のセルには半角数字の１が入力されている。（図２） 選択一覧.csv は，このデータを VBA で処理して作成している。 図１ 成績処理の全体構造 科目選択 （前年度１１月） 選択一覧.csv （４月に作成） 科目.csv 教員.csv （４月に作成） クラス.csv （４月に作成） 教科.csv 講座毎名列作成 ファイル.xls 講座毎名列作成 ファイル.xls 名列表.csv 成績入力.xls 成績入力.xls [講座コード]（講座数分）.csv 成績処理.xls 成績処理.xls 成績一覧表.xls 成績一覧表.xls 個人票 （印刷結果） 個人票 （印刷結果） 通知票 （印刷結果） 通知票 （印刷結果） 図２ 科目選択調査データ ① 年度初めに５つの CSV ファイルを準備 ② ５つのファイルを関連付け，名列表.csv を作成 ③ 成績入力時に，名列表.csv から必要なデータを読み込み・表示 ④ 講座毎に成績データを CSV ファイルで保存 ⑤ 統計処理を行い，成績一覧表を作成，個人票と通知票の印刷
教員.csv，クラス.csv はどちらも，年度毎に変更しなければならない。 この作業はそれほど手間がかからないため，手作業で行うことにした。 教科.csv と科目.csv は，教育課程が変わらない限り変更する必要はない ものである。これら５つのテキストファイルを関連づけて名列表.csv を 作成するためにユーザーフォームを利用した。（図３） ユーザーフォームを開く際に，上記５つの CSV ファイルをシーケンシ ャル入力モードで読み込んでいる。 コンボボックスで教科を選択したときに，科目 Code と教員 Code の左 ２桁が教科 Code と等しいものだけを絞り込み，科目名と教員名をそれぞ れのコンボボックスに表示させている。さらに，科目名から生徒名を絞 り込んで中央のリストボックスに表示させている。 リストボックスでは，MultiSelect プロパティを，fmMultiSelectExtended に設定し，対象となる生徒 をシフトキーや Ctrl キーを利用して複数選択できるようにした。保存ボタンを押すと，選択されたデー タがシーケンシャル追記モードを利用して名列表.csv に追記される。なお，追記モードでは，名列表.csv が指定フォルダに存在しないときは，新規作成される仕組みになっている。 また，追記された生徒名はリストボックスから削除し，同じ生徒が同じ科目に重複して登録されないよ うに配慮した。 コンボボックスの項目を絞り込んで表示させる方法は，Excel では実現できないと考えていたが，CSV ファイルを２次元配列に読み込ませて処理することで容易にできることがわかった。CSV ファイルの入出 力モードと，コードの例を以下に示す。（表２，表３） ファイルを開くときのモード キーワード 追記モード Append バイナリモード Binary 入力モード Input 出力モード Output ランダムアクセスモード Random 図３ 名列表作成フォーム 表２ CSV ファイルの入出力モード 表３ 教科.csv をシーケンシャル入力モードで読み込むコード Private Sub UserForm_Initialize()
Dim myTxtFile As String Dim i As Long, j As Integer Dim Kyoka(50, 3) As String Dim myBuf(30, 3) As String Open myTxtFile For Input As #1 i = 1: j = 1
Do Until EOF(1)
Input #1, myBuf(i, 1), myBuf(i, 2), myBuf(i, 3) If myBuf(i, 3) = 1 Then Kyoka(j, 1) = myBuf(i, 1) Kyoka(j, 2) = myBuf(i, 2) 教科名.AddItem (Kyoka(j, 2)) j = j + 1 End If i = i + 1 Loop Close #1 End Sub ←Open ステートメントで CSV ファイルを開く ←EOF 関数が True を返すまでループする ←Input ステートメントで データを読み込む ←Close ステートメントでファイルを閉じる
(2) 成績入力.xls の作成 成績入力は図４の画面で行っている。フォームを開く際は，名列 表.csv を読み込み，教科，教員，科目で対象生徒を絞り込んで表示 させている。生徒番号と生徒氏名は，事前にラベルをそれぞれ４５ 個配置しておき，対象生徒数と同じ数だけ表示させ，残りは非表示 にすることにした。素点・評点・総欠・公欠も同様に，４５個のテ キストボックスを配置し生徒数と同じ数だけ表示させている。 成績データをテキストボックスに入力することで，正確なデータ 入力が可能となった。また，データのチェックも比較的容易にでき るようになった。 保存時には，シーケンシャル出力モードを利用し，講座毎に CSV 形式で指定フォルダに保存させた。（表４）その結果，コンボボックスで講座を選択し生徒名等を表示さ せる場合は，保存済みの CSV ファイルが存在すれば訂正入力，保存済みの CSV ファイルが存在しなければ 新規入力と自動で判断することができた。訂正入力の場合は，保存済みの CSV ファイルを読み込みデータ を表示させている。 (3) 成績処理.xls の作成 図５は教務部担当者が成績 処理を行うフォームである。 入力ファイル回収ボタンを クリックすると，指定フォル ダに保存されている成績入力 済みの CSV ファイルを，Dir() 関数を利用して，シーケンシ ャル入力モードで１つずつ開 き，追記モードで，１つの seiseki.csv にデータをまと めている。 図４ 成績入力フォーム 図５ 成績処理フォーム
openPath = Worksheets("Sheet1").Range("B1").Value & "¥" openFname = Dir(openPath & "*.csv")
savePath = Worksheets("Sheet1").Range("B2").Value & "¥" saveTxtFile = savePath & "seiseki.csv"
Open saveTxtFile For Output As #2 Do While openFname <> ""
openTxtFile = openPath & openFname Open openTxtFile For Input As #1 Do Until EOF(1)
Input #1, myBuf(0), myBuf(1), myBuf(2), myBuf(3), myBuf(4), _ myBuf(5), myBuf(6), myBuf(7), myBuf(8), myBuf(9)
Write #2, myBuf(0), myBuf(1), myBuf(2), myBuf(3), myBuf(4), _ myBuf(5), myBuf(6), myBuf(7), myBuf(8), myBuf(9)
Loop Close #1 openFname = Dir() Loop Close #2 表４ CSV ファイルをシーケンシャル入力モードで開き，追記モードで書き込むコード 図６ 講座の未入力があった 場合の画面
この処理でも，CSV ファイルを利用したことで，ワークシート上 のデータの結合に比べ，非常に高速に処理を完了することができた。 また，入力ファイルチェックボタンでは，未入力の講座がないか チェックしている。（図６） 一覧表作成ボタンのクリックで，素点，評点，評定，欠課の４つ のワークシート上で統計処理が実行される。それぞれのシートに作 成された成績一覧表のデータは，ひな形（図７）にコピーされ，名 前を付けて成績入力フォルダに保存される。（図８） 個人票は，成績処理.xls の素点と欠課のワークシートから，成績 データをひな形に１９名分ずつ転記・印刷する処理を繰り返し行う ような仕組みをとった。（図９）通知票も同様に，転記・印刷の繰 り返し処理を利用している。（図１０） 成績一覧表，個人票，通知票ともに，書式設定済みのひな形を準 備しておき，実行時にそれらを呼び出してデータの転記処理を行うことによって，処理時間を早めること ができた。 Ⅳ 研究のまとめ 今回の研究では，VBA に対する知識と理解を深めることができた。特に，ユーザーフォームを利用したシ ステムの構築は初めての経験であり，試行錯誤しながらの研究であった。結果として，コンボボックス，リ ストボックスやテキストボックスを活用し，ユーザーが視覚的に迷うことなく操作できるものを作成するこ 図８ 成績一覧表 図９ 個人票 図１０ 通知票 図７ 成績一覧表のひな型
とができたと思う。特に，コンボボックスの項目を絞り込んで表示させる方法は，今まで Excel では実現で きないと思っていた。この技術は，今後も様々な場面で活用できるものである。 また，研究前は，ワークシート上のデータを処理する VBA しか扱ったことがなかったが，CSV 形式で保存 されたデータを活用することで，処理時間を大幅に短縮させることができた。 CSV ファイルの利点は，ファイルのダブルクリックで Excel を介して読み取ることができ，Excel の関数 を利用して容易に編集することも可能なことである。また，ワークシート上のデータと異なり，シーケンシ ャルモードを利用すれば，データを読み込むためにファイルをダブルクリックで開く必要がない。保存時に は，ファイルサイズも小さいことから，非常に扱いやすい保存形式である。これをシステムに活用できたこ とは非常に大きな成果であった。 Ⅴ 本研究における課題 本研究では，現在本校で発行される帳票を効率よく出力できる成績処理システムを構築することができた。 今後は，このシステムを改良し，観点別評価に対応したシステムを作り上げたい。また，生徒の出欠管理や 調査書の発行までスムーズに行えるシステムの構築に向けて，さらに研究を重ねていきたい。 また，今回の成績処理システムは，Excel をベースに作成したため，特別な準備をすることなく校内のほ とんどのパソコンから利用することが可能である。しかし，ユーザーフォームや一覧表作成等の処理には VBA を利用しているため，教務部担当者が VBA の知識を持っていない場合，予期しないエラーやトラブルに対処 することが難しい。この課題の解決に向けて，成績処理担当者を教務部内の複数人で組織し，協力して成績 処理をしながらシステムの構成やプログラムを引き継ぐことができる体制を整えることが必要であると考え る。 ＜参考文献＞ 教職員研修講座テキスト 2007 Excel VBA プログラミング講座 青森県総合学校教育センター 大村あつし著 2004 「かんたんプログラミング Excel 2003 VBA 応用編」 技術評論社 大村あつし著 2004 「かんたんプログラミング Excel 2003 VBA コントロール・関数編」 技術評論社 ＜参考ＵＲＬ＞ 「モーグ」 http://www.moug.net/index.htm
「Excel でお仕事！」 http://www.asahi-net.or.jp/～ef2o-inue/top01.html
・Microsoft○R Office Excel ,Microsoft○R office Access は米国 Microsoft Corporation の米国および
その他の国における登録商標です。
