memorandums

日々の作業ログです。

GoogleスプレッドシートでSQLを実行すると楽かも

14日から後期授業が開始になります。

もう夏休みも終わりです。。。あっという間です。

学科内委員の仕事の1つとして、成績不振者(公にはどう呼ぶのかわかりませんが、うちではこう呼んでいます。要は取得単位数が標準より少なく、最悪の場合、留年する可能性のある人です)を割り出してサポートする仕事があります。

まずは、成績不振者を抽出する必要があります。

成績データは大学のWebサイトで管理されているのですが、条件を入れて情報抽出するなどのマイニング機能はありません。

そこで、事務部より前期の成績締切後のCSVデータをもらい受け、それを処理します。

CSVデータをExcelで読み込み、以下の項目を抽出しました。

  • 科目名称
  • 科目単位数
  • 評価点
  • 学籍番号
  • 氏名
  • 学年

項目だけみてデータを想像するのは難しいかもしれませんが、1行あたり1科目なので1学生あたり複数行のデータがあります。ですので、現時点での総単位数を計算するためには学籍番号でグルーピングする必要があります。SQLならGROUP BYですよね。

でも、Excelではそういう機能はありません。いや、(真面目に調べていませんが)ないようです。以下のようにがんばった足跡もありますが、なんか大変そうです。

kojikoji75.hatenablog.com

で、最近、Googleのオフィス機能をよく使うので(プレゼンテーションとかですね)、スプレッドシートならできるかも?と思い調べてみるとビンゴでした。

先人がちゃんと記録し公開してくれています。ありがたく参考にさせていただきます。

qiita.com

まず上記の項目の全データをExcelからスプレッドシートにコピペします。

15000行くらいありましたので、少し時間がかかりましたが(Chromeが途中で停止しそうになりましたが)、待てばちゃんとアップロードできます。以下のようなイメージです。個人名はまずいので「ダミー」と書いていますが、これが15000行下に続いている感じになります。

f:id:ke_takahashi:20170902174203p:plain

で、シートを追加して、A1セルに以下を入力しました。

QUERY関数の第1引数は SQLが対象とするセルの範囲です。第2引数はご覧の通り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を勉強した方が役に立つケースが多いんじゃないかなぁ。。。とも思いますけどね。はい。