memorandumsスマートフォン用の表示で見るmemorandums日々の作業ログです。office学生指導のため、学科に所属する学生の全成績データ(CSV)を事務部から入手できる仕組みがあります。調査分析のためのWebサービスがあればCSVも必要ないのでしょうけど、現状はそこまでありませんので、このデータを使っていろいろと分析しています。手始めにやったのが科目間の成績の相関分析です。以前、ここに書いたように、CSVデータは1テーブルのためデータを整理する必要があります。そのため、Googleスプレッドシートを利用します。各学生の履修科目の成績を一覧にします。以下は例で、Aさんは英語は70点で数学は80点という感じです。直交したところ(B2セル)に以下の式を書きました。項目名はわかりやすく日本語で書いていますが、実際には列名(A,B,C...)を書きます。B$1,"")ここで発見したのがこの関数に含まれるクエリー(上記のSELECT以降)にセル番号が含められる、ということです。動的にクエリーを書き換えられんですね。これで一覧表が完成します。受講していない科目も含まれていますので対応がまったくない科目もありますが、とりあえずCORREL関数を使って相関係数を求めます。このままGoogleスプレッドシートで進めてもよかったのですが計算時間がかかるのでExcelにコピーして続きの作業を進めました。上記の形式のデータを用いて科目間の相関係数を求めます。試行錯誤して最終的には以下のようにしました。説明が難しいのですが、上記の表の通り、各科目の点数は行方向に並んでいると思います。縦方向へのコピーだけ(ある特定の科目とその他の科目間の相関係数を)考えると以下のような式でいいと思います。ちなみに、以下のdはGoogleスプレッドシートからコピーしてきたデータが入っているワークシート名です。=CORREL(d!$B$2:$BW$2,d!$B2:$BW2)でも、やりたいことは全科目間の相関係数を求めたいので。基準となる科目は変わらなければなりません。いちいちセル番号を手入力で書き換えるなんて嫌です。ここで調べるとINDIRECT関数があることがわかりました。これが発見した2点目です。これは与えた文字列をセルの位置に置き換えて解釈してくれるというものです。Excelの使い方・操作方法ここでもう一度、Excelの最終結果を見ていただいたいのですが。1行目に数字が入っていますよね?これがこの基準となるセルの行番号なんですね。&C$1),d!$B2:$BW2)ちょっと複雑ですが。数式を横方向にコピーしても参照するセル位置を縦に移動したい場合なんかはこんなテクが使えるのではないかと思います。シンプルな別解がありそうですが。10:57相関係数の一覧表を作る過程で発見した2つのこと広告を非表示にする関連記事2019-07-12Googleスプレッドシートで独自ワークシート関数を作るGoogleスプレッドシートを使って、学内業務(時間割作成)用の...2019-07-06同一内容を含むデータ個数をカウントしたい(の答えです)そういえば忘れてた。同一内容を含むデータ個数をカウント...2018-05-24ターミナルの表示をExcelに貼り付けるときに気をつけたいこと(備忘録)MacのExcel2016です。Tableを使って簡易クリッカーアプリ...2017-09-02GoogleスプレッドシートでSQLを実行すると楽かも14日から後期授業が開始になります。»プロフィールid:ke_takahashiソフトウェア開発、工作、面白ガジェットに興味があります。(1)はてなブログをはじめよう!ke_takahashiさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。