Slackを用いた授業外ライティング活動の便利ワザ[Google Spreadsheet編②]

はじめに

下記の前回の記事の続きです。

Slackを用いた授業外ライティング活動の便利ワザ [Google Spreadsheet編①]

前回の記事では,Google Apps Script (GAS)を利用して,Slackに書き込まれた内容をGoogle Spreadsheetに記録し,その記録をもとに語数を数えるという話でした。この記事では,それをもう少し便利にするために,「ある特定の期間で区切って語数をカウントする」というお話です。例えば,1日ごと,1週間ごとみたいな。月ごとの場合は,前回の記事で紹介した方法は月ごとに新しいspreadsheetのファイルができるので楽なんですが,「同じファイル内のシートの中で一定の期間の間ごとに区切りたい」となると,少しだけ工夫がいります。といっても,spreadsheet内で関数を書くだけなのでそんなに大変なことではありません。一度雛形ができればあとはコピペして少し書き換えるだけですみます。

前回のおさらい

少しだけ前回の記事に書いた内容に触れておきます。GASを使ってspreadsheetに取り込まれた状態は,下記画像のようになっています。

Screen Shot 2018-09-24 at 22.50.58

A列が日付,B列が名前,C列が書き込まれたテキスト,D列がもとのJSON形式の情報です。このシートが,チャンネルの数だけ一つのファイルにある状態です。前回は,GASで更新されるこのシートの情報を以下のような手順に沿って使いました。

  1. importrange関数を使って別のファイルにエクスポート
  2. そのシートに語数をカウントするE列を挿入
  3. sumproduct関数で名前列を参照してE列の合計を求める

すると,下記画像のようなまとめシートができます。

sumproduct-example

今回は,そこに「日付の情報も参照する」という情報も付け加えていくことになります。

同じ月内で1週間ごとに語数を数える

では,実際に私がやっている実践に即して,1週間ごとに語数を数えるという作業をやる方法です。先程説明したように,前提は一ヶ月で一つのファイルができ,そのファイルの中にチャンネルの数だけシートがあるという状態です。同じ月内であれば一つのシートの情報で,以下のような作業をspreadsheetにやってもらいます(注1)。

  1. まとめシートのA列にあるセルの名前に当てはまるものだけをフィルタリング
  2. 参照元シートのB列にある日付の情報を参照し,指定した1週間のものだけをフィルタリング
  3. 1と2で絞り込まれたデータのE列の語数を合計して計算する

注意点は,参照するデータのシートでは名前がB列,日付がA列なのに,まとめシートでは名前はA列になっているという点です。どの列にどのデータがあり,どこの列を参照するのかを考えないと混乱するかもしれません。さて,前回はsumproduct関数使ったんですが,今回使うsumifs関数でも同じことが可能だと思うので,全部sumifs関数でやってもいいかもしれません。

先にあとで説明する作業が完了した状態のシートの画像を見てください。1つ前の画像の状態から,下記の画像の状態にすることが今回の目標です(注2)。

Screen Shot 2018-10-12 at 14.12.11

私がこのSlackライティング活動を採用しているのは金曜日の授業で,金曜日から次の週の木曜日までを1週間としています。日付で区切っていて時間で区切っているわけではないので,今回は日付で絞り込みにしています(午後5時までとかそういう切り方だと時間も指定する必要があります)。

では,上の画像のB2の列にはどのような式が入力されているのか見てみましょう。

Screen Shot 2018-10-12 at 14.12.38

画像中の式で最後に”-5″しているのは,最初は必ず”@XXX has joined the channel. “という5語の文が記録されているので,それを排除するためです。

sumifs関数の基本的な引数はsumifs(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)のようになっています。合計範囲とは,合計を計算したい範囲のことですので,今回の場合はE列の語数の列を選択します。”Friday2_201809″というのが,金曜2限クラスの9月分のデータがエクスポートされたシートになります。このまとめシートはimportrangeで自動的にデータがエクスポートされるように設定しているspreadsheetファイルの中に作っているので,”Friday2_201809!$E$1:$E$1000″で,「9月分のデータのE列(語数列)」を合計範囲として指定していることになります。コピーしたときに範囲が変わることを防ぐために,絶対参照にしています。

2つ目の引数は,同じ”Friday2_201809″のA列を1つ目の条件範囲にしています。A列は日付の入っている列ですから,日付列を条件範囲としていることになります。そして,3つめの引数で日付の条件指定を行います。最初の1週間は,もともとデータ記録の一番最初(2018年9月21日)が1週間の1日目でしたので,7日目(終わりの日)だけを指定しています。これには等号・不等号の使い方でいろいろなやり方が考えられますので,この例は一例です。

“<“&”2018-09-28”

等号・不等号は必ずそれのみをクォーテーションマークでくくり,クォーテーションマークでくくった日付と&でつなぐようにします。上の式が意味しているのは,「2018年9月28日より前(つまり27日以前)」です。これで,9月28日0時00分以降の書き込みが排除されます。

4つ目の引数は条件範囲2です。”Friday2_201809″のB列を参照しています。B列は名前の入った列ですので,名前の絞り込みをしようというわけです。最後の引数は$A2です。「条件範囲2(元データの名前列)の中で,まとめシートのA2セルの名前に当てはまるものだけをフィルタリングしてね」ということです。指定する順番自体は前後していますが,上にも挙げた以下の3つの作業が1つの関数で実現されたことになります。

  1. まとめシートのA列にあるセルの名前に当てはまるものだけをフィルタリング
  2. 元ファイルのB列にある日付の情報を参照し,指定した1週間のものだけをフィルタリング
  3. 1と2で絞り込まれたデータのE列の語数を合計して計算する

これであとは,列を横に足して,別の日付指定をすれば,あとはその条件に当てはまるものだけが自動的に記録されていくことになります。合計範囲指定は固定ですが、条件範囲と条件の指定は名前が先で日付が後でも構いません。

1週間のはじめとおわりを指定する場合

さて,上のやり方は,1週間の終わりだけの指定でしたが,それが使えるのは最初の1度だけで,次からは1週間のはじめの日も指定する必要があります。「○月○日から○月○日まで」としたいわけです。これをやるには,sumifs関数の条件範囲と条件を1つずつ追加すればいいだけです。画像上では順番が前後しますが,下の画像のD列(10/5-10/11)の1週間を指定した場合を見てみましょう。

Screen Shot 2018-10-12 at 14.13.34

1つ目の引数は同じですが,2番目と3番目の引数で始まりの日付の指定,4番目と5番目の引数で終わりの日付の範囲の指定を行っています。

“>=”&”2018-10-05”

という指定は,「2018年10月5日以降」という指定になります。1週間の始まりですね。

“>”&”2018-10-04”

としても同じです。続いて,終わりの日付は,同じように日付列を条件範囲とし,

“<“&”2018-10-12”

を条件にしています。つまり,「2018年10月12日より前」ですので,2018年10月11日の23時59分までのデータが条件に当てはまることになります。

“<=”&”2018-10-11”

でも同じです(むしろこっちのほうがわかりやすいかも)。これで,始まりの日付から終わりの日付までの間の合計語数が計算されます。ここを任意の幅に設定すれば,1週間ではなくとも3日でも4日でも10日でも同じようにできます。

月をまたいだ1週間の語数

同じ月内でのやり方は上の2つのやり方の組み合わせで対応できます。では,月をまたいでしまうときはどうすればよいでしょう。上述したように,月ごとにシートが異なるわけなので,別々のシートに記録された情報を統合する必要が出てきます。ただ,難しいことはなく単純に足し算すればよいだけです。

Screen Shot 2018-10-12 at 14.13.15

この例では,Friday2_201809というシートに9月分,Friday2_201810というシートに,importrange関数でデータを同期させています。9月分のデータで,9月の終わりの数日間(この例では9月28日~9月30日),10月のデータで10月1日からの数日間(この例では10月1日~10月4日)の語数を計算し,合算するという作業です。

つまり,それぞれの月でsumifs関数を使った式を作り,2つのsumifs関数式を+記号でつないであげれば,月をまたいだ場合の語数が計算できます。不等号のみと,不等号+等号の意味の違いは,上で説明したとおりです。

おわりに

ということで,前回の記事で紹介したGASでデータを引っ張る作業,importrange関数でデータを別ファイルにエクスポートする作業と,今回の記事で紹介したsumifs関数で日付指定する3つのパターンを使えば,一定期間の間の語数記録は簡単にできてしまいます。

エクセルが得意な方はすでにお気づきかもしれませんが,実は,1行目に指定する日付を終わりの日付にし,そのセルを日付の範囲指定に利用することもできます。つまり,上の画像で言えば,B1セルに”2018-09-27″,C1セルに”2018-10-04″,D1セルに”2018-10-11″のようにするということです。ただ,見たときに1週間の範囲がわかるほうがいいかなという理由で,そういうやり方はしていません。

ということで,下処理問題は解決されていませんが,だいたいの語数を記録して,学生がいつでも見れるようにするということについては,前回と今回の記事の内容でだいたいカバーできるのではないかなと思います。今の所第三段は予定していませんが,今後もしも「こういう事が必要だなぁ」という事が出てきたら更新するかもしれません。

なにをゆう たむらゆう。

おしまい。

注1. おそらくExcelのピボットテーブルなら,同じ列に当てはまる複数の条件でのフィルタリング可能だと思うので,Excelならピボットテーブルだけでいけると思います。

注2. 画像で一目瞭然ですが,毎週書き続けられている学生と,すでに脱落してしまっている学生が分かれてしまっているのは問題で,これについては何かしらの介入が必要だと思っています。

広告

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中