備忘録：Power Query を使って Web ページのデータを Excel に取り込む方法

・csv ファイルや xls ・ xlsx ファイルをダウンロードするだけでなく、表や文字列も。
・Power Query は Excel 2016 以降に標準搭載。Excel 2010・2013 はアドイン追加インストールで利用可能。
・以下は我流であるため、さらに便利な方法がまだあるかも知れない。

自動認識でまず読み込む

1.　Office 365 の Excel だと、入り口は「メニュー － データ － データの取得と変換 － Web から」

2.　「Webから」ボタンをクリックすると URL を入力する画面が表示される。(ここから Power Query の範疇)
「基本」のまま、ここに目的の Web ページの URL をまず入力する。

3.　Power Query (以下、"PQ" と略す) で自動認識出来るのは、表形式。

4.　ひとまず「読み込み先」でワークシートへの読み込み先をセルで指定して

5.　読み込めた テーブル「Table 0」と クエリ「Table 0」

・Power Queryでデータを取得してワークシートに読み込むと「テーブル」形式になる。
・テーブル形式は展開／縮小が自在なため、元データの行数が増減してもそれに応じて伸縮する。
・取り込んだテーブルの右列を追加して、Excel 関数による作業列を作ることも可能。クエリーを更新するたびに、作業列も自動計算される。 (左列に追加はダメそう・・・)
・逆に、テーブルとして認識させたくない情報 ( SUBTOTAL関数を使っての合計など) は、上下縦横とも最低1行1列は離す。
・「クエリと接続」画面は「メニュー － データ － クエリと接続 －クエリと接続」からいつでもアクセスできる。

※ このテーブルは元の Web ページのデザイン上、「地区 － 停電軒数」で1セットの横2段組のため、このままでは扱いづらい。
※ クエリーの列名は重複できないので、自動認識した横2段組の2段目の列名は「地区2」「停電軒数2」に自動的に変更されている。
※ このテーブルを元にさらに1段目、2段目を分けるクエリーを作成・結合させて1段組の表に作り直す。
※ 3Dマップなどのグラフ化には停電軒数を数値化する必要がある。このためには作業列を作って、使い慣れたExcel 文字列関数などにより数値化する。(詳細説明は今回は省略)

中身の観察

6.　「クエリと接続」画面で クエリ "Table 0" を「編集」で開いてみると・・・

7.　"Power Query エディター" が開く。
「適用したステップ」は、読み込みで適用された各マクロのようなもの。

1)　「ソース」の中身。
URL と読み込むファイル形式が「HTML ページ」に指定されていた。

2)　「ナビゲーション」の中身。
Web ページのコンテンツのうち "Table 0" が指定された。

3)　「変更された型」の中身。
1列目を選択して右クリックすると、「テキスト」形式が選択されていることが分かる。

4)　この3つのステップは「詳細エディター」で見ると以下のように記述されている。
(「表示オプション － ワードラップを有効にする」で表示した場合)
後で列の構成や列名などを変更すると、このクエリー内の次のステップやこのクエリーテーブルを参照した他のクエリーでエラーを生じたりする。
その場合はこの「詳細エディター」で直接修正し、整合性を取る。

自動認識できないデータの取得方法

8.　PQ で自動認識できないデータも取得したい場合は、html をテキストで読み込む。

・まず、2. ～7. で作成されたクエリー名を "Q_Table_0" とでも名前変更した上で、
・同じ内容のクエリーを自動認識で作成して別ワークシートに読み込む。
(クエリー名は "Q_html" に変更した。)

9.　これを「編集」で開いて

・「適用したステップ」で「ソース」以外を削除し、
・「ソース」の「設定の編集」で「Webから」画面が開き、「形式を指定してファイルを開く」が選択できるようになっているので、「HTMLページ」 → 「テキスト ファイル」に変更する。

10.　まだ文字化けしているので・・・

11.　再度「ソース」の「設定の編集」を開くと、さらに「元のファイル」が選択できるようになっている。
これはエンコードに相当するようなので、適当なものを選ぶ。
(このケースでは 「932: 日本語 (シフト JIS)」 だった。)

12.　目的の行を特定するため、行番号の列を追加する。
「列の追加」タブから「インデックス列 － 1から」を選択して、インデックス列を追加。

13.　見やすいように、インデックス列を最左にドラッグして並べ替える。
列名も変更しておく。

14.　出来上がった "Q_html" テーブルを「行番号：40以上60以下」のフィルターで絞って表示したところ。
赤○が付いている行に必要な情報があったので、このテーブルを元に、さらに別クエリーを作成して Excel 文字列関数などを使って取り出す。
(詳細説明は今回は省略)

追記

追記1.　クエリーで出来上がったテーブルはデフォルトでは更新のたびに列幅が自動調整されてしまうので、「列の幅を調整する」のチェックをはずしておく。
なお、クエリーで作成されたテーブル名はクエリー名と同じになる。

追記2.　1つのブック (Excel ファイル) 内に複数のクエリーがあって、これらを順番に計算させたいときは、
1)　「メニュー － データ － クエリと接続」で必要な順番に上から並び替え、
 ( ワークシートのタブも左から順番に並べ替えた方が良さそうっぽい・・・)
2)　各クエリーについて「メニュー － データ － クエリと接続 － すべての更新 － 接続のプロパティ」から、

3)　デフォルトの「バックグラウンドで更新する」のチェックをはずす。
「すべて更新」ボタンで上から順番に更新・再計算される。
4)　「すべて更新」ボタンでは更新させたくない場合は、「すべての更新でこの接続を更新する」のチェックをはずす。

5)　4) の場合、個別にクエリーを更新させたい時は、
a.　各クエリーテーブルをアクティブにして「メニュー クエリ － 更新」ボタン

b.　「クエリと接続」画面で、各クエリーについて右クリックメニューで「最新の情報に更新」

のいずれかで行う。

以上、ここまで。