注目キーワード
  1. USCPA
  2. RPA
  3. Tableau

【エクセル】Webの表からデータを取得する方法【パワークエリ】

ecoslymeです。

 

Web上にある表テーブルの情報を取ってきて、エクセルにコピペしてから何らかの作業を行ったことのある人は多いのではないでしょうか?

 

Webから情報を取ってくるのが一度だけならよいですが、毎日その作業をしていると結構面倒ですよね。

 

困っている人
毎日株価や為替のデータをWebから取ってこないといけないから、会社休めない。。。
困っている人
Webから表を取ってくるマクロを作りたいけど、エクセル初心者だからそんな難しいこと出来ない。。。

 

Web上にある表からデータを取得してくる方法は「Webスクレイピング」と呼ばれていますが、マクロを使いこなせないとそのやり方が分かりません。

 

また、後輩に仕事を引き継ぐときにハードルが高くなってしまう可能性があります。

 

そこでこの記事では、エクセルのPower Query(パワークエリ)という機能を利用して、Webから表を取得してくる方法をご紹介します。

 

めちゃくちゃ便利な機能なので、是非覚えて下さい。

 

また、マクロでWebスクレイピングを行う方法もご紹介しています。

 

※記事の最後の方でエクセルマクロのダウンロードができます

ベクター 航海

元データと結果

(Web上の為替相場表)

(パワークエリ実行後のエクセル)

 

Power Query(パワークエリ)でWebスクレイピングが出来る環境

ベクター 講習

まずは、このPower Query(パワークエリ)でWebスクレイピングが出来る環境についてご紹介します。

 

以下の環境以外の方は、利用できない可能性があります。

 

  • パワークエリでWebスクレイピングが利用できる環境

・Windows(Macではできない)

・Office 365、Excel 2019、Excel 2016、Excel 2010以上(アドイン必要)

 

※利用環境については別の記事でまとめる予定です

 

Macではパワークエリは利用出来ましたが、Webスクレイピングが利用できませんでした。

 

Office 365は利用可能なことを確認しましたが、他は未確認です。

 

Web上のデータ(表)をエクセルに取得してくる方法

それでは、Web上のデータ(表)をエクセルに取得してくる方法についてご説明します。

 

Web上のデータ(表)を読み込んで、エクセルに落としてくることを「Webスクレイピング」と言います。

 

例として、伊予銀行の外国為替相場の推移表を利用してみます。

2021年の主要外国為替相場推移(過去の仲値推移)

 

 

Webからデータ取得

エクセルで、以下の要領で「Webから」を選択して下さい。

 

・データ ⇒ データの取得 ⇒ その他のデータソースから ⇒ Webから

 

 

URL入力

URLの欄に、Web上の欲しいデータ(表)があるURLを入力します。

 

その後、OKをクリックします。

 

ナビゲーターからデータの変換

ナビゲータの画面に行きますので、欲しいデータをクリックしながら見ていきましょう。

 

例の場合、「Table 0」が欲しいデータ(表)になっていました。

 

データ選択後、データの変換をクリックします。

 

データ修正後、データを閉じて読み込む

データを修正できるエディターが表示されます。

 

このエディターでデータの修正が出来ます。

 

イメージとしては、ACCESSでの処理と似たようなものです。

 

データを扱うスキルの差が生じてくる部分かと想定しています。

 

今回は「閉じて読み込む」をいきなりクリックして、編集を終了します。

 

名前の修正

クエリで名前の修正を行いたい場合は、該当する項目名をダブルクリックして名前を編集しましょう。

 

Web上で、以下のように改行して表示されている名前(イギリスポンド、スイスフラン)は、名前の部分が少しおかしく表示されてしまいます。

 

名前から無駄なスペースを削除しておきましょう。

⇒(項目名から無駄なスペースを削除)

 

Webスクレイピングしてきたデータのエクセルへの表示

Webスクレイピングしてきたデータが、エクセルに表示されました。

 

 

クエリの編集を行いたい場合

クエリの編集を行いたい場合、下図のように画面右側に表示される「クエリと接続」の該当するクエリをダブルクリックすれば、エディター画面が再度表示されます。

 

表示形式の微修正

表示形式の微修正はわざわざクエリの方で行わずとも、エクセルの方で微修正行うことが出来ます。

 

今回の例の場合、A列の「月」の部分の表示がおかしいので、A列を選択後に「Ctrl + 1」と押して表示形式の修正を行います。

 

ユーザー定義で「mm”月”」と入力しておけば良いでしょう。

 

 

表示形式について詳しく知りたい方は、以下の記事をご覧ください。

関連記事

ecoslymeです。日々の業務の中で、スケジュール表を毎月更新もしくは毎年更新することってありませんか?昨年度のエクセル(Excel)のスケジュール表を使い回したはいいけれども、年度が違えば曜日が変わっていて、その曜日を修[…]

 

クエリを更新する方法

パワークエリは更新しなければ最新のデータにはなりません。

 

自動更新する方法(エクセルを開くとデータが勝手に自動で更新されている方法)は別の記事でご紹介させて頂く予定です。

 

今回はパワークエリを手動で更新する方法をご紹介します。

 

パワークエリのデータを更新する方法は至って単純です。

 

・クエリ ⇒ 更新 をクリック

 

これでデータが更新されます。

 

Webスクレイピングをマクロで行う方法

上の説明では、パワークエリでWebスクレイピングを行いましたが、今度はエクセルのマクロでWebスクレイピングを行う方法をご紹介します。

 

上でご紹介した例と同じ例でWebスクレイピングのマクロを実施しましたが、データが少しおかしくなってしまいました。

 

Webスクレイピングは、マクロよりもパワークエリで行った方が安定性が高いのかと思います。

 

A列の「03月」のセルはWeb上では一つしか書かれていないので、おかしなデータの読み取り方をしてしまったのでしょう。

 

例えば3~11行目は「03月」が表示されていないので、1列分左へズレてしまっています。

 

(参考:Web上のデータ)

 

今回の例ではうまくできませんでしたが、他のWeb上のデータだとうまくいく可能性もありますので、マクロは何も修正せずにしています。

 

Webスクレイピング マクロのコード

参考に、Webスクレイピング マクロのコードを載せておきます。

 

  • Webスクレイピング マクロのコード

Sub WebScraping()

Application.ScreenUpdating = False

‘Web(IE)の起動
Dim objIE As Object

Set objIE = GetObject(“”, “InternetExplorer.Application”)

objIE.Visible = True

objIE.navigate “https://www.iyobank.co.jp/kinri-gaikokukawasesoba-market/gaikokukawasesoba/nakane-rireki.html” ‘ URLを指定する

‘ ページの表示完了待ち。
While objIE.readyState <> 4 Or objIE.Busy = True

DoEvents

Wend

On Error Resume Next ‘エラーを回避する

i = 1 ‘開始行を指定
K = 0
J = objIE.document.all.Length ‘要素の数を知る

Cells(i, 1).Value = “No”

Dim A As Object

For Each A In objIE.document.getElementsByTagName(“tr”)

For K = 0 To 60

Cells(i, K + 1) = A.Children(K).innerText

Next

i = i + 1

Next

On Error GoTo 0

Cells.WrapText = False

Application.ScreenUpdating = True
Application.StatusBar = False

objIE.Quit

End Sub

 

Webスクレイピング マクロのダウンロード

Webスクレイピング マクロダウンロードをしたい方は、以下のリンクをクリックしてください。

 

エクセルでダウンロードが可能です。

 

<マクロ>

【ダウンロード】Web-Scraping-macro

 

ダウンロード後にマクロファイルを開いたら、「マクロを有効にする」をクリックすること。

 

最後に

ベクター 散歩

いかがでしたでしょうか。

 

もしかしたら、エクセルではエクセル間もしくはそのエクセル内にあるデータからしかデータの編集ができない、と思っていた人も多いのではないでしょうか。

 

Webからデータを取得できるという機能は非常に便利で、今後無視できない存在になってくると思います。

 

RPA(WinActorなど)を利用している人にとっては、相性が良ければWebからのデータ取得が安定し、更にロボット稼働時間も短縮するかもしれません。

 

固定観念にとらわれずに、幅広い視点を持って業務の効率化に挑戦してみましょう。

世界最大級のオンライン学習サイトUdemy

エクセルスキルアップ!!

Udemyは、受講者数4,000万人の世界最大級オンライン学習プラットフォームです。

 

<Udemy内 講義の一例>

【初心者から上級者まで】1日で学べるエクセルの教科書 マスターコース

https://www.udemy.com/course/excel-start/

Excel VBA[第1弾](超入門)エクセルが自動で仕事する!マクロの魔法 文系・非IT職もできるプログラミング

https://www.udemy.com/course/excel23vba/

✔︎ まわりに内緒でエクセル技術力を大幅アップ!

  ⇨無駄な作業を早く終わらせて残業せずに帰れる!!

✔︎ E-Learningでいつでも、どこでも学べる!

  ⇨在宅ワーク中などに閲覧し、行動格差を広げよう!!

✔︎ 人気コース、評価が一目で分かる!

  ⇨コスパの高いコースがすぐ選べる!!

 

※「検索」で学びたいコースを入力してください