Excelの標準機能 – Power Query – でできる!ホームページからボタン一つでデータ収集する方法を徹底解説!

Power Query

ホームページから情報を抽出することを、Webスクレイピングと呼びます。実は、ExcelでもWebスクレイピングができます。

Power Queryという機能を使用することで、Webから情報を抽出できます。例:

Excelの「データ」>「Webから」を選び、URLを入力する
Table 0を選択すると、Excelに読み込めていることがわかる

しかし、抽出できるのページ内のテーブル要素だけです。
以下のページのように、欲しい情報がテーブルではなくリストに格納されている場合は抽出できません。

例えばyuturaの急上昇動画のページはLi要素で提供されています。
https://ytranking.net/trend/
Table 0 要素が表示されておらず、Excelに読み込めません。

そこで、リスト要素であっても情報を抽出する方法をご説明します。

処理の流れ

欲しい情報を抽出するため、ページを文字列として読み込み、サイトの構造に合わせて加工していきます。

1.文字列として読み込み

記事の最初のほうの手順と同じく、「Webから」を選び、URLを入力します。

https://ytranking.net/trend/?date=2022022010

文字列として読み込みたいので、「データの変換」をクリックし、処理を編集していきます。

Power Queryの編集画面が表示されます。右の「ナビゲーション」は不要なので×アイコンをクリックし削除しておきます。
テーブルではなく文字列として読み込むため、Web.Pageになっているところを、Lines.FromBinaryに書き換えます。
Lines.FromBinary
htmlソースがリストとして読み込まれます。

2.リスト要素<li>の開始箇所まで削除

htmlソースの抜き出したいデータの開始位置

読み込まれたhtmlソースをスクロールしていき、抜き出したいデータの開始位置を探します。151行目が<li>要素の開始位置でした。

先頭150行は不要なので削除します。

アイテムの削除 > 上位アイテムの削除
150を入力
<li id=”rank1″>が先頭になる

しかし、これではサイトに何か変更があり、<li id=”rank1″>が151行目で無くなったら処理がうまくいかなくなります。

そこでList.Skip(ソース,150)を書き換えていきます。

最初に<li id=”rank1″>がでてくるまで削除したいので、List.RemoveFirstN を使用します。

最初に残したい行の文字列をコピーします。

1行目をクリックして、下に表示の文字列をコピー
Power Queryの左のあたりで右クリック > 新しいクエリ > その他のソース > 空のクエリ をクリック
作成したクエリにコピーしておいた文字列を張り付け
名前を [ li開始位置 ] としておく
List.Skipを書き換え
List.RemoveFirstN(ソース, each _ <> li開始位置)

書き換えても結果が変わらない(1行目に<li id=”rank1″>があることを確認してください。これで、最初のliタグが151行目でなくても、望んだ結果がえられるようになりました。

3.リスト要素<li>の終了箇所まで保存

同じように、liの終了位置を探します。

403行目に最後の</li>がある
最後が562行目

後ろから159行削除します。

アイテムの削除 > 下位アイテムの削除
562 – 403 = 159を入力
403行目までになった

同じく、不要な行が159行でなくなると、後の処理がうまくいかなくなるため、開始位置と同様に改善します。

最後の行の文字列をコピー

同様に、新しいクエリを作成して「li終了位置」と名前を付けておきます。

li終了位置とクエリに名前を付けた状態
List.RemoveLastN(削除された最初の項目,159)を書き換え
List.RemoveLastN(削除された最初の項目,each _ <> li終了位置)

結果が変わらないことが確認できます。

4.<li>要素を行に分ける

今のリストは、htmlソースの元々の改行位置で行が分かれているので、一度すべての行を一つの文字列に結合してから、<li>で分割します。

削除された下の項目を右クリック > 後にステップの挿入
削除された下の項目を書き換える
Text.Combine( ) で囲むと文字列になる
カスタム1を右クリック > 後にステップの挿入
ステップを、Text.Split(カスタム1, “<li”)にする

1行目は不要なので、1行削除します。

アイテムの削除 > 上位アイテムを削除
<li>要素を1行に分けられた

5.記号で列に分ける

要素を行に分けられたので、要素の中身を列に分けます。分ける記号は「 ” , = , > , < 」の四つです。

まずはテーブルに変換

次に、それぞれの記号を一つの別の記号「 @ 」に置き換えます。

Column1の列名の値を右クリック > 値の置換…
検索する値に ” 、置換後 @ を入力
id=” が id=@になった

他3つの記号についても同じく置き換えます。

すべて置き換えた後

次に、「 @ 」で列に分割します。

ホーム > 列の分割
区切り記号をプルダウンから –カスタム– を選択、「 @ 」を入力
分割された。最後の自動で追加されたステップ「変更された型」は不要なのでx で削除しておく

6.欲しい列を残す

今回、抽出したい列は、「ランキング」、「タイトル」、(チャンネルの)「アイコン」、「チャンネル名」、「登録日」、「動画の長さ」、「再生回数」の7列です。分割後の列から、これら情報がある列を選択します。

Column1.10はランキングなので、列をクリックしておく
Column1.40 動画のタイトル。Ctrl + クリックでどんどんほしい列を選択していく
Column1.63 チャンネルのアイコン
Column1.68 チャンネル名
Column1.97 投稿日
Column1.113, Column1.115 再生回数
Column1.133 動画の長さ
最後の列を選択したところで、右クリック > 他の列の削除
選択した列が残る

再生回数だけ、万以上と以外で分かれているので、これを結合した列を作成します。

列の追加 > カスタム列を選択
手順をコードで記述

let countText = [Column1.115],
clipLeft = Text.Replace(countText, “万”, “”), // 万単位以外の数値の前後、万、回(+を消す
clipRight = Text.Replace(clipLeft, “回(+”, “”),
num = Number.From(clipRight), // 数値化
numMan = Number.From([Column1.113])
in num + numMan * 10000 // 万単位の数字を10000倍して足す

再生回数列が追加された

列名がまだColumnと内容を表せていないので、ここで名前を付けます。

列に名前を付ける

不要な列(再生回数の元の列)を削除します。

Column1.113と115を選択して右クリック > 列の削除
削除後の結果

ここで14行目が思った結果が取れていないことがわかったので、行を削除します。条件として、アイコン列がhttpsで始まっていないものとします。

アイコン列の右端の▼をクリックし、httpsで始まる項目の☑を外してOKを押す
[アイコン] <> “https:// ~ “という関数になるのでこれを書き換える
Text.StartsWith([アイコン], “https”) にすることで、httpsで始まる列のみ残るようになる
完成・・・の前に、ステップを読みやすくする
名前を付けておくことで、何か問題があった時に修正が簡単になる
閉じて読み込む
無事、Excelのシートに読み込まれた

いかがでしたか?マウスの操作と、少しのPower Queryの関数の知識で、htmlソースから、欲しい情報を取り出す方法を説明しました。

今後、URLが連番で変化するページや、ページの更新部分を読み込む方法などを説明していきます。

コメント

タイトルとURLをコピーしました