関数初心者がとあるニコ生主の配信履歴表を作ってみた
この記事はSpreadsheets/Excel Advent Calendar 2018 2日目の記事になります。
僕は沖縄でとあるIT会社のデバッガーをしてます。
専門用語が飛び交う毎日に
「たのむ！日本語で喋ってくれ！日本語で！！」
と絶叫する毎日に終止符を打つために、最近から関数やらPHPやらJavaScriptやらRubyやらGoogle Apps Scriptやらに手を出し始めた右も左もわからない初心者中の初心者です。
さて、関数初心者である私がSpreadsheetの関数を使ってとあるニコ生主の配信履歴表を作ってみたことをかきます！
～きっかけ～
今の仕事をするまでSpreadsheetを使ったことがなく、仕事以外でも使ってみたいなと思っていた矢先・・・
個人的に応援しているニコ生主の方がいまして、
その名も、「七原くん」 といいます。（ググってみればすぐに出てきます）
七原くんのコミュニティを閲覧していた際、
「そういえば七原くんっていつから配信していたっけ・・？」
「いちいちコニュニティへアクセスして確認するの大変だな・・・」
と感じて ふと考えたんです。
このコミュってHTMLだったな・・・
↓
そーいえば同僚が
同僚「Spreadsheetの関数でHTMLの構造から指定した一部を抜き出すことができるお。ｽｺﾞｽｷﾞｨｨｨｨ!!!FFのパッチが～～～ｺﾞﾆｮｺﾞﾆｮ～～」
って言ってたな
↓
やってみよう！
ってな感じでつくるきっかけになりました。
（Excelは税理士事務所で働いていたときに使ってましたが、関数は使ってなかったです。そもそも関数という存在があったことを知りませんでした。）
さっそく、つくるにあたり私が欲しい情報を簡単に整理します。
・過去配信した日時。
・直近の配信動画がタイムシフト視聴可能かどうか。
・できたら配信した年・月・曜日で簡単なランキングを表示させたい。
では初めていきましょう。
★まずは対象となるサイトを準備。
★スプレッドシートを準備
今回のメインとなる関数を確認しましょう。
IMPORTHTML関数
と
IF関数
になります。
詳しい説明は苦手なので上記リンクをみていただければ幸いです。。。
★過去配信した日時を探す
サイトから確認してみると「生放送」と表示された箇所があります。
開いてみると・・・・
直近の配信履歴が30件表示されていますね。
こちらをコンソール画面で確認します。
IMPORTHTML関数のlistが使えそうですね。
一番古いと思われるページを探してみると、
ページが21ページまであることがわかりました。（記事を書いた時点）
配信した日時がしっかりと記載されていますので、こちらページから日時などの情報を使うことにします。
★直近の配信動画がタイムシフト視聴可能かどうかを探す
「生放送」ページの右側に「タイムシフト視聴はできません」とありますね。
配信後数日は「タイムシフト視聴可」みたいな感じで表示されるのですが、今回はタイミングが悪かったようで表示されていません。
ですが「タイムシフト視聴はできません」と表示があるので、一旦タイムシフト視聴可能フラグはこちらの使います。
★関数でシートにひっぱっる
Spreadsheetの出番です。
ここからの流れてしては、
・IMPORTHTMLで欲しい情報を書き出す
・書き出した情報を年/月/日/曜日 に分ける
・カレンダーみたいなものに、配信があった日に色をつける
の流れで作っていきます。
Spreadsheetに IMPORTHTML関数を使って過去配信した履歴を表示させます。
=IMPORTHTML("https://com.nicovideo.jp/live_archives/co3313757?com_header=1","list",7)
このように表示されます。
このままだと、1ページ分の履歴しか抜き出していないので、2~21ページ分を抜き出します。（ここは地味な作業です。ごめんなさい。。。）
次に抜き出した情報から、日付を抜き出します。
私はLEFT関数を使いました。
=IF(LEFT(B2,10)<>"",LEFT(B2,10),"")
こんな感じで、対象のセル内の左10文字を抜き出します。
いい感じで日付を抜き出せました。
この調子で時間まで一気に抜き出してみます。
時間のみを抜き出してみます。
時間を抜き出す時はRIGHT関数を使いました。
=IF(E2<>"",RIGHT(E2,5),"")
対象のセル内の右から5文字を抜き出します。。
こちらもいい感じで時間が抜き出せてます。
抜き出した日付を曜日に変換をします。
=IF(D2<>"",TEXT(D2,"ddd"),"")
バッチリ曜日変換してます。
各 年/月/日/曜日 を判断させるために簡単にわけます。
ここまでつくって一旦名前をつけておきます。
とりあえず「関数元ネタ」としときます。
簡単にカレンダーを作ります。
上から、月・日付・曜日・配信があった場合のフラグ欄になります。
フラグ欄に先ほど作った「関数元ネタ」から2018/01/01に配信したか関数で調べます。
=IF(COUNTIF('関数元ネタ'!D2:D,B6),COUNTIF('関数元ネタ'!D2:D,B6),"")
本当に配信が2回行われたか確認してみます。
2回配信しているようですね！
この調子で12月分まで作ります。（かなり地道な作業になります。ごめんなさい。。。）
作っていくうちにフラグ欄が数字だらけになり、見辛くなるので条件付き書式で「空白でないセル」を色付けし目に負担を減らします。
見やすくなりましたw
こんな感じで2016年・2017年分も作成します。
最近行われた生放送や予約スケジュールなども簡単に見れるようにしてみます。
最近行われた生放送
=IMPORTHTML("https://com.nicovideo.jp/community/co3313757","list","9")
予約スケジュール
=IMPORTHTML("https://com.nicovideo.jp/community/co3313757","list",10)
タイムシフトカウント（件の箇所です）
=IF(COUNTIF('関数元ネタ'!D2:D,"タイムシフト視聴可 "),COUNTIF('関数元ネタ'!D2:D,"タイムシフト視聴可 "),"")&"件"
2018年の月・曜日での配信回数をランキングを出してみます。
ついで2016〜2018年にかけての曜日ごとの配信グラフ&ランキングも作ってみます。
D列のグラフは下記に関数を使っています。
=SPARKLINE(C1,{"charttype","bar";"max",100})
かなり雑な説明でしたがこれで出来上がりです。
↓↓こちらが僕が作ってみたシートになります。↓↓
感想
関数に対して苦手意識があったのですが、過去配信表をつくってみたことで関数への苦手意識がなくなり好きになりました。
今回の記事の内容は初心者なりの書いてみたのですが、記事の書き方やもっと使いやすい関数があるよ！などのご助言をいただければ幸いです。
このような機会を作ってくれた @minemura_coffee さん本当にありがとうございます。
来年はもっと技術をつけてまた参加したいと思います。
明日は・・・・記事書く方がいない・・・だと・・・
この記事を閲覧された方12/3の記事書き書きよろしくお願いします！
この記事を読んでいただき本当にありがとうございました！！！
