R28：ジャッジテスト採点の匿名化
今回はジャッジテストの採点を匿名でできるようなシートを作ったので、そのシェアと解説をしたいと思います。
もっと効率のいい関数の使い方とかありそうですし、所々変な表現や間違った言い方があるかもしれませんが、そこはご容赦ください💦
また、少なくとも１人はどのJTが誰のものかを知ることができてはしまうのでそこも何とかできたらいいのになあとは思ってますが、今の所いい案が無い感じです。
【追記】
これTabの方が行ったとしたらACのメンバーは誰もJT提出者の名前を知らずに済むので、採点が終わるまではその方が良いかもですね？
以下の様な設定で進めていきます
〇参加ジャッジ
A,D→Osaka所属のジャッジ
B,E→Kobe所属のジャッジ
C,F→Kyoto所属のジャッジ
〇AC（JTを採点する人々）
X→Kobe
Y→Osaka
Z→Kyoto
（今回は同大学の人のジャッジテストは採点しない、と言う体でやります。ですがジャッジテストをどうせ匿名にするから同大学の人のジャッジテストを採点してもええやろ、としてしまってもまあ問題はないかなあと思います。ただその場合でもほとんど手順は変わりません。）
〇用意するもの （URL載せておきますので使いたい方は自身のPC等にコピー保存してからご自由にお使いください）
・JTを提出する人の一覧表Googleスプレッドシート（JT anonymize score 1）
https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU/edit?usp=sharing
・JTを採点する用のGoogleスプレッドシート（JT anonymize score 2）
https://docs.google.com/spreadsheets/d/1iprbmgUQLJXHmm8_x2N1wYM4eM-5CnZMF5WAwrzbGaA/edit?usp=sharing
・JT提出用のGoogleフォーム（JT anonymize score 3）
https://forms.gle/e3GtrKgCrD7BadBR8
・フォーム回答まとめのGoogleスプレッドシート（JT anonymize score 3 （回答））
https://docs.google.com/spreadsheets/d/193ePceCmzGrDb5t-VhZlh2BWGsXpTVyqGxaTbK7_0qk/edit?usp=sharing
〇使う主要な機能・関数
RAND関数
VLOOKUP関数
IMPORTRANGE関数
オートフィル（単なる時短）
〇流れの概要
どのACがどのJTを採点するかを決める（ランダムにしたかったら後述のRAND関数とか使ってもいいかも）
→ACのうち１人がJT提出者にランダムな整理番号を振って匿名化
→整理番号とJT回答を一致させた２のファイルを作る
→２のファイルをAC全員に共有して匿名のまま採点
→採点結果をTabに共有（必要なら採点終了後にACにも共有してもよさそう）
〇詳細の説明
１．どのJTをどのACが担当するかを決める
大体の場合１つのJTを２人くらいが採点してその平均をとることが多いと思います。
まあこの担当の振り分けは大学気にしたりダイバーシティ気にしたり色々な決め方があると思うのでACで話し合って決めましょう。
２．JT提出者に整理番号を振って匿名化する
１）RAND関数を用いて各ジャッジに０～１の間のランダムな数字を当てる
シートのセル内に「=RAND()」と打つと、そのセル内に０～１のランダムな数字が発生します。これを全部のジャッジにやると、全てのジャッジが０～１の間の何らかの数字を持っていることになります。
２）RAND関数で出た数字を大きさ順にジャッジを並び替え、上から番号を振っていく
ランダム生成された数字の大きい／小さい順に並び替えると、ジャッジの順番もランダムになります。その状態で上から順番に整理番号を振ってあげれば、各ジャッジがランダムな整数を持っていることになり、その整数を使うことで匿名化ができるようになります。
※並べ替えのやり方
並べ替えたい範囲を選択し、「範囲を並び替え」を選びます。
するとどの列の値を基準に並び替えるかを選択できるので、RAND関数を入れた列（今回ならF）を選んで並び替えると、その列だけでなくNameやAffiliation・担当など全部がまとめて並び替えられます。
ちなみにRAND関数はEnter押したり何かやる毎に数字がランダム生成されるので、いきなり数字が変わっても気にせず並び替えましょう(笑)
なお、このファイルは最後までJT提出者一覧表として使っていくので、整理番号を振った後はA列の名前でアルファベット順に並び替えておくと最後に採点結果を整理するのに楽です。
３．整理番号とJTの解答を一致させる
１）名前・回答と整理番号を一致させる
３（回答）のスプレッドシートを見ると、「フォームの回答」みたいなフォームの回答がまんま載っているシートがあると思います。（タイムスタンプ、とかとか）
まずはこれを分かりやすくAnswerとかのシンプルな英語の名前に変えておく方が良いかと思います（後程関数とかの中で参照するため）
そしてシートを１つ追加し、また適当な英語の名前を付けます。（今回はSummaryとしています）
このSummaryのシートのなかでJT提出者の名前・回答と整理番号を一致させます。
一番左のNameのセル、「＝Answer!B1」とあると思います。
これは同じスプレッドシートのファイル内の「Answer」というシートの中の「B1」のセルの中身をそのまま持ってきます、と言う意味のコマンドです。
これをオートフィルで下まで引っ張ると、ジャッジテストを出した人の名前がそのまま出てきます。
（フォームの回答Answerが提出順になっているので、ここの名前も提出順になっています。つまり出てきたものから上に並んでいくので、全部の提出を待たなくでもこの作業とか採点ができます）
次に名前の横に整理番号を持ってきたいので、以下のような関数を入れます。
=VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU","List!A2:E7"),3, FALSE)
これは
VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])
という関数に
IMPORTRANGE(スプレッドシートのURL, 範囲の文字列)
という関数が入った入れ子状態になっています。
VLOOKUP関数とは、「検索キー」の部分にある値が「範囲」の中のどこの行にあるかを探し、その行の左から「指数」番目のセルにある値をこのセルに表示する、と言うものです。
例えばこの場合だと、
検索キー（A1の値：大西）がどこの行にあるかを範囲（A3~B6の四角）から探します。
すると大西がA6にあることが分かります。
そしてそこから数えて右に２番目のセルにある値を返します。
注意点としてA6が１番目と数えられるので、80歳のセルの値を返すためには指数の所は1ではなく2にする必要があります。
見つけたマスの２つ右のセルの値を知りたいときは、この様に指数を３にします。
（範囲内の値しか返してくれないので、この時に範囲がA3:B6のままだと体重の列が含まれておらず返ってきません）
ちなみに関数内の４つ目のFALSEってやつは何も考えずこれでいいみたいです(笑)
話を図５と下の関数に戻します。
=VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU","List!A2:E7"),3, FALSE)
この関数では、「A２」の値が「IMPORTRANGE…」の範囲の中のどの行にあるかを探し、そこから３番目の値を返す、という命令が成されています。
IMPORTRANGE関数とは、別のスプレッドシートのシートやファイルの中の範囲を参照するときに使う関数です。
IMPORTRANGE(スプレッドシートのURL, 範囲の文字列)
１つめの部分に”シートのURL”を入れます。今回はJT anonymize score 1のURLを入れます。
注意点として普通にスプレッドシートを開くと
https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU/edit#gid=0
みたいなURLになってますが、この後ろの部分「/edit#gid=0」を入れずに入力しないといけません。
２つ目の部分には「どのシート」の「どの範囲」なのかを入れます。
上のものだと「List!A2:E7」となっていますが、これはListというシートの中のA2~E7の四角い範囲を指しています。
（URLは頭のhttps://が入ってないので注意）
=VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU","List!A2:E7"),3, FALSE)
なのでこの関数は、
図５におけるA2の値（JT提出者の名前、今回はA）がJT anonymize score 1のファイルのListというシートのA2~E7の中（図８赤線内）のどこにあるかを探し、
そこから数えて右に3番目のセルの中にある値（今回なら６）を返す
という意味を持っています。
こうするとAさんの整理番号がJT anonymize score 3 (回答)の所に現れます。
【追記】
名前を使った検索に関してですが、大文字・小文字や半角スペースなどが一致してないとヒットしないと思われます。
なので、そもそもJTフォームの名前を記入する欄は自由記入じゃなくてこちらで用意した一覧からの選択式にしてもいいかもしれないですね。
JT提出者の名前の横に整理番号を出せました。今度はその横にJTの回答を出してきます。
これはIMPORTRANGE関数を使うとまとめてできます。
このScore 3のURLを使い、Answerシート内の回答がある範囲を全選択するとそのまんまこっちのシートにも出てきます。
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/193ePceCmzGrDb5t-VhZlh2BWGsXpTVyqGxaTbK7_0qk","Answer!F1:R7")
こうすることでSummaryシート内でJT提出者の名前・回答・整理番号が全部そろいます。
２）匿名のまま採点できるファイル２を作る
ファイル２に載せる情報は
・JT提出者の整理番号
・その回答（Ranking, Speaker score, RFD）
・採点の担当者
・その採点者が点数をつける欄
・採点者の点数の平均
です。提出者の名前を外さないといけないので、フォームの回答やJT anonymize score 3をそのまま使えないんですよね。
ちなみにファイル３内の別シートではなく別にファイル２を作る理由は、ファイル内の特定のシートのみをアクセスするということが多分できないからです（知らないだけだったらすみません）
なのでこの匿名化の作業をしている人以外が提出者の名前を知れないように、別ファイルでまとめる必要があります。
ファイル３のSummaryシートから、JT提出者の整理番号を持ってきます。（範囲はB1~B26）
この時整理番号を持ってきている大元のデータはフォームの回答なので、整理番号はJT回答順（＝フォームのデータ順）になりますし、まだJTを出していない人がいればその人の整理番号は表示されません。
で、B~E列にそれぞれ「採点担当１」「その点数」「採点担当２」「その点数」を入れ、F列にその平均を入れます。
平均の所はAverage関数使ってもいいですし、直接「=(C2+E2)/2」みたいにしてもいいです。
JTの回答はSummaryシートに移した時みたいに、IMPORTRANGE関数を使います。
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/193ePceCmzGrDb5t-VhZlh2BWGsXpTVyqGxaTbK7_0qk", "Summary!C1:O7")
で、JTの整理番号に対応した採点者の名前をB列とD列に載せないといけません。
ここでまたVLOOKUP関数とIMPORTRANGE関数の組み合わせが出てきます。
=VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU","List!C2:E7"),2,FALSE)
今回はJT採点者の割り振りはファイル１でやっているので、そこを参照して担当者を引っ張ってきます。
上の関数だと
ファイル２のシート（Score）におけるA2（JT提出者の整理番号）を、ファイル１のC2~E7の範囲のどこの行にあるかを探し、そこから数えて右に2番目（担当１）のとこにある値を呼び出します。
図１２のBの列では担当者１を呼び出すのでIMPORTRANGE関数の指数（何個右に行くか）は2ですが、D列で担当者２を呼び出すときは3になります（図１３で見て担当２は担当１のもう1つ右）
で、これをオートフィルでやると提出されたJTに対し整理番号が振られ、その回答と採点担当者がそろった状態になります。
ここまで来たらJT anonymize score 2のファイルのリンクをAC内で共有して、採点してもらえばOKです。
４．採点結果をまとめる
採点結果をまとめるファイルは他のACがアクセスできない場所でしたらどこに作ってもいいのですが、今回はJT anonymize score 3のファイルに3つ目のシート（ScoreSum）を作ってやることにします。
これはJT anonymize score 1のシートにまとめてあるやつを、IMPORTRANGE関数で引っ張ってこれます。
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU","List!A1:B7")
=VLOOKUP(VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zjv6HcBxdHn4YrJK-c-mtMzgVCJhXgFxu4k5EeInIKU", "List!A2:C7"), 3, FALSE), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1iprbmgUQLJXHmm8_x2N1wYM4eM-5CnZMF5WAwrzbGaA", "Score!A2:F7"),6,FALSE)
まあここで整理番号も載せて採点結果呼び出してもよかったんですが、せっかくなので最後まで匿名性を貫いてみました。
この関数では、
１．A列にあるJT提出者の名前を使って、JT anonymize score 1のListのシートから整理番号を呼び出す
２．その整理番号を使って、JT anonymize score 2のScoreシートから採点結果を呼び出す
という作業を行っています。
JT anonymize score 2のScoreシートではA列が整理番号、F列が採点結果となっており、右に５個ずれるのでIMPORTRANGE関数の指数は6になります。
以上で、このシートを作る作業をする人以外は匿名性を保ったままJTの採点とその集計ができるようになります。
最後に。
こんなくそ長いブログをここまで読んでいただけた方は「なんでJTの採点に匿名性が必要なの？」とは今更は思わないとは思いますが、
やっぱり人間はおバカなので所属や個人の名前・知名度などで偏見を持ってしまうことは避けられません。
なのでこうやって匿名性を担保することで公平な採点になるのではないでしょうか。
