学生指導のため、学科に所属する学生の全成績データ（CSV）を事務部から入手できる仕組みがあります。
調査分析のためのWebサービスがあればCSVも必要ないのでしょうけど、現状はそこまでありませんので、このデータを使っていろいろと分析しています。
手始めにやったのが科目間の成績の相関分析です。
以前、ここに書いたように、CSVデータは１テーブルのためデータを整理する必要があります。そのため、Googleスプレッドシートを利用します。
まず、各学生の履修科目の成績を一覧にします。以下は例で、Aさんは英語は70点で数学は80点という感じです。
氏名を１行目、科目名を A列に並べます。そして直交したところ（B2セル）に以下の式を書きました。項目名はわかりやすく日本語で書いていますが、実際には列名（A,B,C...）を書きます。
=QUERY('CSVデータ抜粋'!$A:$F,"SELECT C WHERE 科目名='" & $A2 & "' AND 氏名=" & B$1,"")ここで発見したのがこの関数に含まれるクエリー（上記のSELECT以降）にセル番号が含められる、ということです。動的にクエリーを書き換えられんですね。。。便利です。
これで一覧表が完成します。受講していない科目も含まれていますので対応がまったくない科目もありますが、とりあえずCORREL関数を使って相関係数を求めます。
このままGoogleスプレッドシートで進めてもよかったのですが計算時間がかかるのでExcelにコピーして続きの作業を進めました。
上記の形式のデータを用いて科目間の相関係数を求めます。試行錯誤して最終的には以下のようにしました。
ちょっと説明が難しいのですが、上記の表の通り、各科目の点数は行方向に並んでいると思います。ですので、縦方向へのコピーだけ（ある特定の科目とその他の科目間の相関係数を）考えると以下のような式でいいと思います。ちなみに、以下のdはGoogleスプレッドシートからコピーしてきたデータが入っているワークシート名です。
=CORREL(d!$B$2:$BW$2,d!$B2:$BW2)
でも、やりたいことは全科目間の相関係数を求めたいので。。。基準となる科目は変わらなければなりません。もちろん、いちいちセル番号を手入力で書き換えるなんて嫌です。
ここで調べるとINDIRECT関数があることがわかりました。これが発見した２点目です。これは与えた文字列をセルの位置に置き換えて解釈してくれるというものです。
参照するセルの位置を変数で指定する (計算結果により参照するセルの位置を変更する) - Excelの使い方・操作方法
ここでもう一度、Excelの最終結果を見ていただいたいのですが。。。１行目に数字が入っていますよね？これがこの基準となるセルの行番号なんですね。
=CORREL(INDIRECT("d!$B$" & C$1 & ":$BW$" &C$1),d!$B2:$BW2)ちょっと複雑ですが。。。数式を横方向にコピーしても参照するセル位置を縦に移動したい場合なんかはこんなテクが使えるのではないかと思います。
もっとシンプルな別解がありそうですが。
とりあえずメモとして。
