ようやっと採点が終わった。100人前後のクラスのレポートを複数チェックするのは、正直けっこうたいへん。
採点でよく使う（あるいは、使えそうな）Excel関数などを下記にまとめておく。
さほど、高度なものではないが、よく使うものを自分用メモとしてまとめておく。
manaba+Rのデータ処理
manaba+Rでの複数回答データの採点処理。
# 回答1.1
1,2,14,19,21
1,2,4,5,6,14
いろいろやり方があるが、sumifで各番号ごとを1/0で、変換するセルをつくる。
2段階の処理が必要。
まず、第一にEmeditorか何かで、回答セルの最初と末尾の文字として、カンマ","を加える
# 回答1.1
,1,2,14,19,21,
,1,2,4,5,6,14,
その上で、仮にA1に、セルがあったとして、B1以後のセルに下記を貼り付ける。
COUNTIF(A1,"*,1,*") =COUNTIF(A1,"*,2,*") =COUNTIF(A1,"*,3,*") =COUNTIF(A1,"*,4,*") =COUNTIF(A1,"*,5,*") =COUNTIF(A1,"*,6,*") =COUNTIF(A1,"*,7,*") =COUNTIF(A1,"*,8,*") =COUNTIF(A1,"*,9,*") =COUNTIF(A1,"*,10,*") =COUNTIF(A1,"*,11,*") =COUNTIF(A1,"*,12,*") =COUNTIF(A1,"*,13,*") =COUNTIF(A1,"*,14,*") =COUNTIF(A1,"*,15,*") =COUNTIF(A1,"*,16,*") =COUNTIF(A1,"*,17,*") =COUNTIF(A1,"*,18,*") =COUNTIF(A1,"*,19,*") =COUNTIF(A1,"*,20,*") =COUNTIF(A1,"*,21,*") =COUNTIF(A1,"*,22,*") =COUNTIF(A1,"*,23,*") =COUNTIF(A1,"*,24,*") =COUNTIF(A1,"*,25,*") =COUNTIF(A1,"*,26,*") =COUNTIF(A1,"*,27,*") =COUNTIF(A1,"*,28,*") =COUNTIF(A1,"*,29,*") =COUNTIF(A1,"*,30,*") =COUNTIF(A1,"*,31,*") =COUNTIF(A1,"*,32,*") =COUNTIF(A1,"*,33,*") =COUNTIF(A1,"*,34,*") =COUNTIF(A1,"*,35,*") =COUNTIF(A1,"*,36,*") =COUNTIF(A1,"*,37,*") =COUNTIF(A1,"*,38,*") =COUNTIF(A1,"*,39,*") =COUNTIF(A1,"*,40,*") =COUNTIF(A1,"*,41,*") =COUNTIF(A1,"*,42,*") =COUNTIF(A1,"*,43,*") =COUNTIF(A1,"*,44,*") =COUNTIF(A1,"*,45,*") =COUNTIF(A1,"*,46,*") =COUNTIF(A1,"*,47,*") =COUNTIF(A1,"*,48,*") =COUNTIF(A1,"*,49,*") =COUNTIF(A1,"*,50,*") =COUNTIF(A1,"*,51,*") =COUNTIF(A1,"*,52,*") =COUNTIF(A1,"*,53,*") =COUNTIF(A1,"*,54,*") =COUNTIF(A1,"*,55,*") =COUNTIF(A1,"*,56,*") =COUNTIF(A1,"*,57,*") =COUNTIF(A1,"*,58,*") =COUNTIF(A1,"*,59,*") =COUNTIF(A1,"*,60,*") =COUNTIF(A1,"*,61,*") =COUNTIF(A1,"*,62,*") =COUNTIF(A1,"*,63,*") =COUNTIF(A1,"*,64,*") =COUNTIF(A1,"*,65,*") =COUNTIF(A1,"*,66,*") =COUNTIF(A1,"*,67,*") =COUNTIF(A1,"*,68,*") =COUNTIF(A1,"*,69,*") =COUNTIF(A1,"*,70,*") =COUNTIF(A1,"*,71,*") =COUNTIF(A1,"*,72,*") =COUNTIF(A1,"*,73,*") =COUNTIF(A1,"*,74,*") =COUNTIF(A1,"*,75,*") =COUNTIF(A1,"*,76,*") =COUNTIF(A1,"*,77,*") =COUNTIF(A1,"*,78,*") =COUNTIF(A1,"*,79,*") =COUNTIF(A1,"*,80,*") =COUNTIF(A1,"*,81,*") =COUNTIF(A1,"*,82,*") =COUNTIF(A1,"*,83,*") =COUNTIF(A1,"*,84,*") =COUNTIF(A1,"*,85,*") =COUNTIF(A1,"*,86,*") =COUNTIF(A1,"*,87,*") =COUNTIF(A1,"*,88,*") =COUNTIF(A1,"*,89,*") =COUNTIF(A1,"*,90,*") =COUNTIF(A1,"*,91,*") =COUNTIF(A1,"*,92,*") =COUNTIF(A1,"*,93,*") =COUNTIF(A1,"*,94,*") =COUNTIF(A1,"*,95,*") =COUNTIF(A1,"*,96,*") =COUNTIF(A1,"*,97,*") =COUNTIF(A1,"*,98,*") =COUNTIF(A1,"*,99,*") =COUNTIF(A1,"*,100,*") =COUNTIF(A1,"*,101,*") =COUNTIF(A1,"*,102,*") =COUNTIF(A1,"*,103,*") =COUNTIF(A1,"*,104,*") =COUNTIF(A1,"*,105,*") =COUNTIF(A1,"*,106,*") =COUNTIF(A1,"*,107,*") =COUNTIF(A1,"*,108,*") =COUNTIF(A1,"*,109,*") =COUNTIF(A1,"*,110,*") =COUNTIF(A1,"*,111,*") =COUNTIF(A1,"*,112,*") =COUNTIF(A1,"*,113,*") =COUNTIF(A1,"*,114,*") =COUNTIF(A1,"*,115,*") =COUNTIF(A1,"*,116,*") =COUNTIF(A1,"*,117,*") =COUNTIF(A1,"*,118,*") =COUNTIF(A1,"*,119,*") =COUNTIF(A1,"*,120,*") =COUNTIF(A1,"*,121,*") =COUNTIF(A1,"*,122,*") =COUNTIF(A1,"*,123,*") =COUNTIF(A1,"*,124,*") =COUNTIF(A1,"*,125,*") =COUNTIF(A1,"*,126,*") =COUNTIF(A1,"*,127,*") =COUNTIF(A1,"*,128,*") =COUNTIF(A1,"*,129,*") =COUNTIF(A1,"*,130,*") =COUNTIF(A1,"*,131,*") =COUNTIF(A1,"*,132,*") =COUNTIF(A1,"*,133,*") =COUNTIF(A1,"*,134,*") =COUNTIF(A1,"*,135,*") =COUNTIF(A1,"*,136,*") =COUNTIF(A1,"*,137,*") =COUNTIF(A1,"*,138,*") =COUNTIF(A1,"*,139,*") =COUNTIF(A1,"*,140,*") =COUNTIF(A1,"*,141,*") =COUNTIF(A1,"*,142,*") =COUNTIF(A1,"*,143,*") =COUNTIF(A1,"*,144,*") =COUNTIF(A1,"*,145,*") =COUNTIF(A1,"*,146,*") =COUNTIF(A1,"*,147,*") =COUNTIF(A1,"*,148,*") =COUNTIF(A1,"*,149,*") =COUNTIF(A1,"*,150,*") =COUNTIF(A1,"*,151,*") =COUNTIF(A1,"*,152,*") =COUNTIF(A1,"*,153,*") =COUNTIF(A1,"*,154,*") =COUNTIF(A1,"*,155,*") =COUNTIF(A1,"*,156,*") =COUNTIF(A1,"*,157,*") =COUNTIF(A1,"*,158,*") =COUNTIF(A1,"*,159,*") =COUNTIF(A1,"*,160,*") =COUNTIF(A1,"*,161,*") =COUNTIF(A1,"*,162,*") =COUNTIF(A1,"*,163,*") =COUNTIF(A1,"*,164,*") =COUNTIF(A1,"*,165,*") =COUNTIF(A1,"*,166,*") =COUNTIF(A1,"*,167,*") =COUNTIF(A1,"*,168,*") =COUNTIF(A1,"*,169,*") =COUNTIF(A1,"*,170,*") =COUNTIF(A1,"*,171,*") =COUNTIF(A1,"*,172,*") =COUNTIF(A1,"*,173,*") =COUNTIF(A1,"*,174,*") =COUNTIF(A1,"*,175,*") =COUNTIF(A1,"*,176,*") =COUNTIF(A1,"*,177,*") =COUNTIF(A1,"*,178,*") =COUNTIF(A1,"*,179,*") =COUNTIF(A1,"*,180,*") =COUNTIF(A1,"*,181,*") =COUNTIF(A1,"*,182,*") =COUNTIF(A1,"*,183,*") =COUNTIF(A1,"*,184,*") =COUNTIF(A1,"*,185,*") =COUNTIF(A1,"*,186,*") =COUNTIF(A1,"*,187,*") =COUNTIF(A1,"*,188,*") =COUNTIF(A1,"*,189,*") =COUNTIF(A1,"*,190,*") =COUNTIF(A1,"*,191,*") =COUNTIF(A1,"*,192,*") =COUNTIF(A1,"*,193,*") =COUNTIF(A1,"*,194,*") =COUNTIF(A1,"*,195,*") =COUNTIF(A1,"*,196,*") =COUNTIF(A1,"*,197,*") =COUNTIF(A1,"*,198,*") =COUNTIF(A1,"*,199,*") =COUNTIF(A1,"*,200,*") =COUNTIF(A1,"*,201,*") =COUNTIF(A1,"*,202,*") =COUNTIF(A1,"*,203,*") =COUNTIF(A1,"*,204,*") =COUNTIF(A1,"*,205,*") =COUNTIF(A1,"*,206,*") =COUNTIF(A1,"*,207,*") =COUNTIF(A1,"*,208,*") =COUNTIF(A1,"*,209,*") =COUNTIF(A1,"*,210,*") =COUNTIF(A1,"*,211,*") =COUNTIF(A1,"*,212,*") =COUNTIF(A1,"*,213,*") =COUNTIF(A1,"*,214,*") =COUNTIF(A1,"*,215,*") =COUNTIF(A1,"*,216,*") =COUNTIF(A1,"*,217,*") =COUNTIF(A1,"*,218,*") =COUNTIF(A1,"*,219,*") =COUNTIF(A1,"*,220,*") =COUNTIF(A1,"*,221,*") =COUNTIF(A1,"*,222,*") =COUNTIF(A1,"*,223,*") =COUNTIF(A1,"*,224,*") =COUNTIF(A1,"*,225,*") =COUNTIF(A1,"*,226,*") =COUNTIF(A1,"*,227,*") =COUNTIF(A1,"*,228,*") =COUNTIF(A1,"*,229,*") =COUNTIF(A1,"*,230,*") =COUNTIF(A1,"*,231,*") =COUNTIF(A1,"*,232,*") =COUNTIF(A1,"*,233,*") =COUNTIF(A1,"*,234,*") =COUNTIF(A1,"*,235,*") =COUNTIF(A1,"*,236,*") =COUNTIF(A1,"*,237,*") =COUNTIF(A1,"*,238,*") =COUNTIF(A1,"*,239,*") =COUNTIF(A1,"*,240,*") =COUNTIF(A1,"*,241,*") =COUNTIF(A1,"*,242,*") =COUNTIF(A1,"*,243,*") =COUNTIF(A1,"*,244,*") =COUNTIF(A1,"*,245,*") =COUNTIF(A1,"*,246,*") =COUNTIF(A1,"*,247,*") =COUNTIF(A1,"*,248,*") =COUNTIF(A1,"*,249,*") =COUNTIF(A1,"*,250,*") =COUNTIF(A1,"*,251,*") =COUNTIF(A1,"*,252,*") =COUNTIF(A1,"*,253,*") =COUNTIF(A1,"*,254,*") =COUNTIF(A1,"*,255,*") =COUNTIF(A1,"*,256,*") =COUNTIF(A1,"*,257,*") =COUNTIF(A1,"*,258,*") =COUNTIF(A1,"*,259,*") =COUNTIF(A1,"*,260,*") =COUNTIF(A1,"*,261,*") =COUNTIF(A1,"*,262,*") =COUNTIF(A1,"*,263,*") =COUNTIF(A1,"*,264,*") =COUNTIF(A1,"*,265,*") =COUNTIF(A1,"*,266,*") =COUNTIF(A1,"*,267,*") =COUNTIF(A1,"*,268,*") =COUNTIF(A1,"*,269,*") =COUNTIF(A1,"*,270,*") =COUNTIF(A1,"*,271,*") =COUNTIF(A1,"*,272,*") =COUNTIF(A1,"*,273,*") =COUNTIF(A1,"*,274,*") =COUNTIF(A1,"*,275,*") =COUNTIF(A1,"*,276,*") =COUNTIF(A1,"*,277,*") =COUNTIF(A1,"*,278,*") =COUNTIF(A1,"*,279,*") =COUNTIF(A1,"*,280,*") =COUNTIF(A1,"*,281,*") =COUNTIF(A1,"*,282,*") =COUNTIF(A1,"*,283,*") =COUNTIF(A1,"*,284,*") =COUNTIF(A1,"*,285,*") =COUNTIF(A1,"*,286,*") =COUNTIF(A1,"*,287,*") =COUNTIF(A1,"*,288,*") =COUNTIF(A1,"*,289,*") =COUNTIF(A1,"*,290,*") =COUNTIF(A1,"*,291,*") =COUNTIF(A1,"*,292,*") =COUNTIF(A1,"*,293,*") =COUNTIF(A1,"*,294,*") =COUNTIF(A1,"*,295,*") =COUNTIF(A1,"*,296,*") =COUNTIF(A1,"*,297,*") =COUNTIF(A1,"*,298,*") =COUNTIF(A1,"*,299,*") =COUNTIF(A1,"*,300,*") =
レポート自動判定系：Excel上にテキストデータが入っている場合の自動判定
■字数の判定
文字数にしたがった判定
=IF(LEN(A1)>1000,1,0)
※A1セルが、1000字以上かどうかだけを確認。1000字以上なら、1を返す。
=IF(A1<>"",LOOKUP(LEN(A1),{0,200,1000,2000,3000},{"F","C","B","A","A+"}),"")
※A1セルの字数に応じて、F～A+までの判定を返す。
＃字数についての関数
=LEN(A1)
※文字数の換算。普通は、これ。ただし、改行などが多いデータでうまくいかないことがある。
=LENB(A1)/2
※バイト数で計る場合。半角と、全角が混じっている場合、正確性が落ちるので、あまり使わないほうがよい。改行だけできれば、CLEAN関数や、SUBSTITUTE関数で改行を削除してから、LEN関数で測ったほうがよい。
■特定文字列の数値変換
=IF(E8<>"",LOOKUP(E8,{"未提出","期限後提出","提出済み"},{0,1,2}),"")
■指定の文字列が入っているかどうかの判定
=COUNTIF(A1,"*イノベーション*")
※A1に「イノベーション」の文字が入っていれば１を返す。
=(LEN(A1)-LEN(SUBSTITUTE(A1,"社会","")))/LEN("社会")
※A1に「社会」という言葉が使われた回数を確認する。
得点の処理
■得点をもとにした評定
=IF(A1<>"",LOOKUP(A1,{0,60,70,80,90},{"F","C","B","A","A+"}),"")
=IF(A1>=90,"A+",IF(A1>=80,"A",IF(A1>=70,"B",IF(A1>=60,"C","F"))))
※いずれも、A1のセルについて、90点以上ならA+、80点以上でA、70点以上でB、60点以上でC。それ以下は、F
■偏差値の確認
=(A1-average(A:A))/STDEV.S(A:A)*10+50
#A1のセルの偏差値の確認。A1のセルが数字だけだった場合。
=(A2-average(A$2:A$100))/STDEV.S(A$2:A$100)*10+50
#A2のセルの偏差値の確認。Aの列の2行目から100行目までが数字の場合。
関連研究
この分野での研究もあるらしい。ほとんど読んでないが、自然言語処理系とか、シカゴマニュアル的なフォーマットとかの研究者の領域だろうか。
https://scholar.google.com/scholar?cites=14362860401455473462&as_sdt=2005&sciodt=0,5&hl=ja
プログラミングの授業の採点補助システムなどは、だいぶ発展してそうではある。
