投票型ランキング動画制作における超効率的集計方法
閉じる
閉じる

新しい記事を投稿しました。シェアして読者に伝えましょう

×

投票型ランキング動画制作における超効率的集計方法

2017-01-29 00:30

    目次に戻る


    この方法はゆっくり大投票祭


    開催にあたって開発、利用した方法です。投票動画のルールは以下に記載されています。
    ゆっくり大投票祭(実験) 企画説明

    投票のポイントは
    ①作品IDのみのコメントをしてもらうこと
    ②投票してもらう区間はそれ以外のコメントをしないでもらうこと
    ③投票コメントは別の方法で募集すること
    ④コメント最大数を考慮すること


    といった感じです。以下の集計方法を見た方が、何故こんな手法になっているのか理解できると思います。改良できそうな点は改良して使ってください。

    1.投票動画のコメントを全てコピーする

    コメント欄からのコピーでできないことも無いでしょうが、今回はNicofinderを使います。
    Nicofinder
    http://www.nicofinder.net/

     検索窓に投票動画のタイトルを入力します。ゆっくり大投票祭投票動画も公開状態で残してあるので、練習に使ってください。

     再生できないかもしれませんが、今は問題ありません。右上の「コメント解析」をクリックしてください。

     右下のExcelをクリックしてください、このコメント一覧をExcelファイルでダウンロードできます。

     インターネットで入手したExcelファイルは編集を有効にするか訊かれます。有効にしてください。
     投票の集計で使うのはE列のコメントのみです。それ以外の行列は全て削除してください。また投票コメント以外も全て行ごと削除します。投票ミスと考えられるID以外の文字が入ったコメントは、量に応じて訂正もしくは削除します。

     この状態になります。B1に次のように入力します。

    =COUNTIF(A:A,A1)

    これはA列内にA1と同じ値がいくつあるかを返す関数です。
    セル右下の小さな四角(フィルハンドル)をダブルクリックし、下までオートフィルします。

     B列をコピーしてC列に値のみを貼り付けます。その後B列を削除します。
     A、B列を選択して「データ」タブ内の「重複を削除」を行います。

     B列を基準に降順で並び替えます。

     これでA列に投票数の多い順でIDが並びました。B列はその作品の票数です。
    A列に列を挿入し、A1に

    =RANK.EQ(C1,C:C)

    と入力
    して下までオートフィルすれば順位も表示されます。(Excel2007以前はRANK関数)


    2.IDから作品情報を取得する
     これで集計自体は終わっていますが、ニコニコが提供するAPIを参照し、作品IDから動画タイトル、投稿者名、再生数、コメント数、マイリスト数を取得して手間を省きます。

    まずはD1に以下の関数を入力し、下までオートフィルします。

    =FILTERXML(WEBSERVICE("https://ext.nicovideo.jp/api/getthumbinfo/"&B1),"/nicovideo_thumb_response/thumb/title")

    解説:WEBSERVICE関数でニコニコから提供されている、動画情報のAPIを参照しています。これはXML形式で記述されているので、その中から

    /nicovideo_thumb_response/thumb/title
    に書かれた動画タイトルをFILTERXML関数で返します。

     D列に動画タイトルが表示されました。#VALVE!となっている動画は投稿者が削除したため見れなくなっている動画です。
     このままでは編集できないので、D列をコピーし、E列に値のみ貼り付けてD列を削除します。

     このままでは第一回のタイトルになっているので、発表する順位の作品まで手作業でシリーズ名に書き換えます。(このブロマガでは省略)また投票ルールを理解しておらず、第一回以外に投票しているコメントもあります。なのでタイトルを見て票数を合わせ、順位を正しく並べ直す作業も任意で行います。ただし参加人数が多い場合、元々ルールを守っていない票なので無視してもいいと思います。

     次の各セルに以下の関数を入力します。

    E1
    =FILTERXML(WEBSERVICE("https://ext.nicovideo.jp/api/getthumbinfo/"&B1),"/nicovideo_thumb_response/thumb/user_nickname")&"さん"

    解説:API内の投稿者名を参照し、投稿者名の後に「さん」を付けて返します。

    F1
    ="再生:"&FILTERXML(WEBSERVICE("https://ext.nicovideo.jp/api/getthumbinfo/"&B1),"/nicovideo_thumb_response/thumb/view_counter")&CHAR(10)&"コメント:"&FILTERXML(WEBSERVICE("https://ext.nicovideo.jp/api/getthumbinfo/"&B1),"/nicovideo_thumb_response/thumb/comment_num")&CHAR(10)&"マイリスト:"&FILTERXML(WEBSERVICE("https://ext.nicovideo.jp/api/getthumbinfo/"&B1),"/nicovideo_thumb_response/thumb/mylist_counter")

    解説:API内の再生数、コメント数、マイリスト数についてそれぞれ参照し、
    再生:〇〇
    コメント:〇〇
    マイリスト:〇〇
    と返します。CHAR(10)はエクセルの関数内で記述すると改行として扱われます。

    各々オートフィルします。少し時間がかかります。

     D列に動画タイトル、E列に投稿者名、F列に再生コメマイリス数が表示されました。再生コメマイリス数はここままだとコピペできないので、一旦F列をコピーしてG列に値のみ貼り付け、F列を削除してください。 

     再生コメマイリスをコピペして使う際は任意のF列のセルを選択し、数式バーの中身をコピーしてペーストします。

     これで投票されたIDから作品の投票数と順位、更にタイトル、投稿者、再生コメマイリス数を出せます。
     

    目次に戻る


    広告
    コメントを書く
    コメントをするには、
    ログインして下さい。