１４日から後期授業が開始になります。
もう夏休みも終わりです。。。あっという間です。
学科内委員の仕事の１つとして、成績不振者（公にはどう呼ぶのかわかりませんが、うちではこう呼んでいます。要は取得単位数が標準より少なく、最悪の場合、留年する可能性のある人です）を割り出してサポートする仕事があります。
まずは、成績不振者を抽出する必要があります。
成績データは大学のWebサイトで管理されているのですが、条件を入れて情報抽出するなどのマイニング機能はありません。
そこで、事務部より前期の成績締切後のCSVデータをもらい受け、それを処理します。
CSVデータをExcelで読み込み、以下の項目を抽出しました。
- 科目名称
- 科目単位数
- 評価点
- 学籍番号
- 氏名
- 学年
項目だけみてデータを想像するのは難しいかもしれませんが、１行あたり１科目なので１学生あたり複数行のデータがあります。ですので、現時点での総単位数を計算するためには学籍番号でグルーピングする必要があります。SQLならGROUP BYですよね。
でも、Excelではそういう機能はありません。いや、（真面目に調べていませんが）ないようです。以下のようにがんばった足跡もありますが、なんか大変そうです。
で、最近、Googleのオフィス機能をよく使うので（プレゼンテーションとかですね）、スプレッドシートならできるかも？と思い調べてみるとビンゴでした。
先人がちゃんと記録し公開してくれています。ありがたく参考にさせていただきます。
まず上記の項目の全データをExcelからスプレッドシートにコピペします。
１５０００行くらいありましたので、少し時間がかかりましたが（Chromeが途中で停止しそうになりましたが）、待てばちゃんとアップロードできます。以下のようなイメージです。個人名はまずいので「ダミー」と書いていますが、これが１５０００行下に続いている感じになります。
で、シートを追加して、A1セルに以下を入力しました。
QUERY関数の第１引数は SQLが対象とするセルの範囲です。第２引数はご覧の通りSQLです。FROM句がないのでちょっと変ですが、だいたい同じように書けました。
以下のSQLでは、評価点であるC列の値が60点以上を対象として、単位数であるB列のSUMを求めます。学籍番号と氏名のD、E列でグルーピングして、学籍番号順に昇順に表示します。
=QUERY('シート1'!A2:F15350,"SELECT D, E, SUM(B) WHERE C >= 60 GROUP BY D, E ORDER BY D")
完成したらこのデータをExcelに戻していろいろと処理すれば楽チンです。
Accessを使ったりExceシートをデータオブジェクトとして扱うとかも解法として考えられそうですが、これは楽ですね。
プログラミング教育が話題ですが、実務的にはSQLを勉強した方が役に立つケースが多いんじゃないかなぁ。。。とも思いますけどね。はい。
