欠席者・非選択者処理

便利ですが

マークシート読み取りソフト「マーくん」には欠席者や非選択者の概念はありません。

受験者の出席番号と得点、正答箇所一覧、選択した記号一覧が出力されるだけです。

なので手持ちの出席番号のデータと、テスト受験者の番号データを手動で合わせてやる必要があります。

いちいち確認しながら切り貼りするのは面倒ですので、ここでは一括してExcelで処理する方法を考えます。

出席番号の数値化

まず「クラスと出席番号」は4桁でマークさせた、と仮定します。1組1番なら「0101」です。

マーくんの分析データファイルからもって来た「0101」などのデータは、Excelででは文字列と認識されますので、数値に変換しておきましょう。

まず変換する文字列をドラッグして選択します。次にExcel上部メニューから「データ」を選択し、「区切り位置」をクリックします。マーくんのデータは区切り文字で区切られているのでそのまま「次へ」

「’」を選択し「次へ」をクリックします。

このままで「次へ」

数値に変換されました。

VLOOKUP(検索値,範囲,列番号,検索方法)

連続する文字列を入力して選択、セルの右下をクリックして引っ張ると連続したデータが入力されます。

次に関数VLOOKUP(検索値,範囲,列番号,検索方法)を使います。

検索値はA列。範囲つまり検索する表はF2からG9です。表の左上から右下の対角にあたるセルを指定します。

検索値A列と範囲の最も左つまりF列とを比べ、合致した場合データを取り出したいのは範囲の2列目G列です。

近似値では困るので検索方法は合致、FALSEとします。セルC2に「=VLOOKUP(A2,F2:G9,2,FALSE)」と記入します。「=」は数式や関数に用いる記号です。これをC列に適応するため、選択して右下を引っ張ります。

エラーが出てしまいました。理由は「検索値とともに参照する表も動いてしまっているから」です。確認してみましょう。

見事にズレています。ずらさないためには絶対記号「$」を付けます。

範囲の部分を「$F$2:$G$9」とするとズレません。

関数IFERROR(値,エラーの場合の値)

ところが困った事にエラーが出てしまっています。欠席者や非選択者がいる場合には一致するデータがないので関数VLOOKUPはエラー値を返します。

これは成績処理の邪魔なので、出来れば空白や「0」にしたいところです。

ここでは関数IFERROR(値,エラーの場合の値)を使います。関数VLOOKUPの値がエラーならば「””」つまり空白にしてみましょう。

=IFERROR(VLOOKUP(A2,$F$2:$G$9,2,FALSE),””)

「0」にしたい場合は「””」を「0」にかえればOKです。この右下をクリックして引っ張ります。

成功ですね、これで欠席者や非選択者データを自動的に除外できるようになりました。実際やってみると出席番号の記入ミスがあったりで、すんなりとはいきませんが手作業で確認するよりは効率がよいでしょう。

使った関数は

VLOOKUPとIFERRORです。

特にVLOOKUPは、点数を10段階評価などに変換する場合にも使える関数です。

成績付けでは多用できますよ。

Apache Open office

Microsoft Officeは商用ソフトで使いやすくて便利ですが、反面高くつきます。

今はとある事情で365を使えていますが、使えなくなったときに備えてフリーのOfficeソフト「Apache Open office」でも関数を使えるようにしておきます。

VLOOKUP(検索値;範囲;列番号;検索方法)

関数VLOOKUPの書式はほぼ変わりませんが、Excelの「, コンマ」が「; セミコロン」に代わります。

=VLOOKUP(A2;$F$2:$G$9;2;0)」検索方法は「0」になっていますが、完全一致「FALSE」と同じです。

Excelの書式でも「FALSE」を「0」に置き換えても大丈夫です。

関数iferrorの代わりに

Open Officeの関数には「IFERROR」は無いようです。

なので「IF」と「ISERROR」を組み合わせて使います。

関数ifは「=IF(論理式;TRUEの場合;FALSEの場合)」です。

関数iserrorは「=ISERROR(値)」で値がエラーの場合はTRUEを返す関数です。

よって式は「=IF(ISERROR(VLOOKUPの値);エラーの場合は“”;違う場合はVLOOKUPの値)」とします。

=IF(ISERROR(VLOOKUP(A2;$F$2:$G$9;2;0));””;VLOOKUP(A2;$F$2:$G$9;2;0))

これで、Excelと同じ事ができるようになります。

すこし手間ですね。