Office
 Computer >> コンピューター >  >> ソフトウェア >> Office

Web サイトから Excel にデータをインポートする方法

World Wide Web には膨大な有用なデータが含まれていることはよく知られています。ただし、何らかの分析を行う前に、データを Microsoft Excel にインポートする必要があります。この種のタスクを完了するために使用できる方法が 2 つあります。

Web から外部データを手動で取得

この Web ページからトップの金儲け映画の収益をダウンロードしたいとします。ここでは、簡単に行う方法を示します。

Microsoft Excel を開き、[データ] をクリックします。 タブの 外部データの取得 グループで、[ウェブから] をクリックします . 新しい Web クエリの後 ダイアログ ボックスが表示されたら、ウェブ アドレス (https://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) を アドレス にコピーします。 フィールドに移動して Go をクリックします ボタン。図 1.1 に示すように、Excel は Web ページのダウンロードを開始します。 [いいえ] をクリックするだけです スクリプト エラー が表示される場合 下図のような警告ボックス。ボックスは消え、インポート プロセスには影響しません。

Web サイトから Excel にデータをインポートする方法

図 1.1

[新しい Web クエリ] ダイアログ ボックスの右上隅にある黄色のボックスに矢印があります。それをクリックすると、テーブルの前に同様のアイコンを表示するかどうかを決定できます。たとえば、図 1.2 の左側のパネルでは、テーブルの横に矢印アイコンがありません。また、矢印ボタンをクリックしてアイコンを表示すると、(右側のパネルに) アイコンが表示されます。

Web サイトから Excel にデータをインポートする方法

図 1.2 [画像をクリックして全体を表示]

選択するテーブルの横にある矢印アイコンをクリックします。アイコンとテーブルが変更され、図 1.3 の左側のパネルに示されているものと同様になります。 [インポート] をクリックした後。 データのインポート ダイアログボックスが表示されます。データを配置する範囲 (この例では A 列から H 列まで) を入力し、[OK] をクリックします。 . Web サイトから Excel にデータをインポートする方法

図 1.3 [画像をクリックして全体を表示]

[OK] をクリックすると、データが Excel にインポートされます。 .テーブル内の任意のセルを右クリックして [更新] を選択した場合 、Excel が出て、Web ページから最新のデータを引き出します。 Web サイトから Excel にデータをインポートする方法

図 1.4

また、クエリ データを任意の方法で更新するように設定することもできます。テーブル内の任意のセルを右クリックして、[データ範囲のプロパティ] を選択します。 .プロンプトが表示された External Data Range Properties で ダイアログ ボックスで、リフレッシュ コントロールを変更できます スタイルを変更するための設定。たとえば、クエリを 60 分ごとに更新するか、ファイルを開いたときに更新するように指定できます。

Web サイトから Excel にデータをインポートする方法

VBA プログラミングを使用してデータをスクレイピングする

VBA プログラミングを使用して、Web ページからデータをスクレイピングできます。最初のアプローチと比較すると、はるかに便利ですが、困難です。さらに、VBA プログラミングを使用してデータをスクレイピングする方法を学ぶ前に、HTML とは何かを理解する必要があります。 HTML について何も知らない、またはほとんど知らない場合は、この Web サイトから HTML の基礎知識を学ぶことをお勧めします。ここから、VBA を使用した Web スクレイピングに関するほとんどすべてを学ぶことができます。この記事では 2 つの例のみを示します。

1 つの Web ページからデータをスクレイピングする

この Web ページから会社名、電子メール アドレス、および連絡先名を抽出するとします。この Web ページを開くと、下部に連絡先ブロックがあることがわかります。図 2.1 に、接点ブロックと対応するソース コードを示します。赤いボックス内の情報は、私たちが必要としているものです。緑色の下線が付いているものは、まさに抽出する必要があるものです。

Web サイトから Excel にデータをインポートする方法

図 2.1

次のコードは、上記の要求された情報を抽出して最初のワークシートに入れるのに役立ちます。

ソースコード Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 
Sub Retrieve_Click()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's not see the browser window

IE.Visible = False
 
'Open the web page

IE.Navigate "https://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736"
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
MsgBox htext
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
ThisWorkbook.Save
 
Set IE = Nothing
 
Set contactobj = Nothing
 
End Sub

「IE.document.getElementsByClassName(“contact-details block dark”)」を使用すると、クラス名が contact-details block dark のすべての要素を取得できます。この Web ページには、HTML 要素で使用できるほぼすべてのプロパティとメソッドがリストされています。自分の問題に適したものを選択できます。

innerHTML プロパティを使用すると、HTML 要素のコンテンツを設定または返すことができます。私たちの場合、「contact-details block dark」というクラス名を持つ要素のコンテンツを返し、それを変数 htext に設定しました。コンテンツ (図 2.2) は、「Msgbox htext」で表示できます。

Web サイトから Excel にデータをインポートする方法

図 2.2

文章がきちんと構成されていることがわかります。そのため、必要なものを抽出するために SPLIT 関数を使用できます。たとえば、「

Company Name:」を区切り文字として使用し、式「Split(htext, "

Company Name:")(1)" を使用すると、「

Company Name:」の後のテキスト全体を返すことができます。 」。この新しく返されたテキストでは、区切り文字として「

図 2.2 で [OK] をクリックすると、要求されたデータを Web から Excel ワークシートにインポートできます。たとえば、セル A1 には会社名が含まれ、セル A4 には会社の Web ページのアドレスが含まれています。

Web サイトから Excel にデータをインポートする方法

図 2.3

ワークブックを保存する前に以下のコードを追加すると、セル A4 にハイパーリンクを追加できます。

ソースコード Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 
'Add hyperlink

ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)

セル A4 をクリックすると、Web ページに再度アクセスできます。これは、多数の企業のデータを取得する必要がある場合に特に便利です。ハイパーリンクをクリックして対応する Web ページに再度アクセスし、後で確認するときに手動で情報を追加または更新できるため.

Web サイトから Excel にデータをインポートする方法

図 2.4

類似の読み物

  • 別のワークブックを開いて Excel VBA でデータをコピーする方法
  • [Fixed!] Object Workbook のメソッド Open の失敗 (4 つの解決策)
  • 配列にセル値を入力する Excel VBA (4 つの適切な例)
  • VBA を使用してワークブックを開いてマクロを実行する方法 (4 つの例)

ウェブページを操作する

上記の例は、1 つの静的 Web ページからデータを取得する方法を示しています。しかし、多くの場合、大量のデータを取得するために Web ページとやり取りしたデータを抽出する必要があります。図 3.1 を見てください。上記の例の Web ページにアクセスする方法を示します。多くの業種があり、業種ごとに多くの企業があることがわかります。たとえば、アグリビジネス業界には 651 社あります。すべての業界のすべての会社の連絡先情報を抽出したい場合、どうすればよいでしょうか?

Web サイトから Excel にデータをインポートする方法

図 3.1 [画像をクリックして全体を表示]

重要なポイントは、VBA を手動で行うように Web ページと対話させる方法です。 S.W.I.Sアドバンテージを例に挙げてみましょう。通常、Excel が Agribusiness (図 3.1 の上部パネル) をクリックすると、IE がトリガーされて 2 番目の Web ページが表示されることを期待しています。 2 番目の Web ページ (図 3.1 の下部のパネル) で、Excel は S.W.I.S Advantage をクリックできます。IE は図 2.1 に示すようなページに誘導し、S.W.I.S Advantage の連絡先情報を取得できるようにします。

次のコードを Visual Basic Editor に入力してコードを送信すると、IE が開いていることがわかります。最初の Web ページが表示され、続いて 2 番目の Web ページが表示されます。ここでは、ドロップダウン リスト要素を取得する方法、オプションを選択する方法、およびオプションを選択した後にイベントをトリガーする方法を学習します。 「m =IE.document.getElementsByTagName(“option”).Length – 1」は、オプションの総数を示します。これは次のループに使用できます。

ソースコード Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 
Sub retrieve()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = True
 
'Open the web page

IE.Navigate "https://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
End Sub

コードのこの部分は、Excel が最初の名前をクリックすると、図 2.1 に示す Web ページに移動します。 「Name」というクラス名を持つ要素には、すべての企業名が含まれています。 Searchobj はコレクションであり、searchobj(i) は (i+1) th を返すことができます 物体。たとえば、searchobj(1).Click を使用すると、RIDLEY CORPORATION (メルボルン) の Web ページにアクセスできます。

ソースコード Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 
'Part 2 - Select company Name

Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop

最後に、IE を開き、Web ページを閲覧し、データを抽出するプロセスを示すコード全体を示します。抽出されたデータは、図 2.4 と同じになります。

ソースコード Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 
Sub Retrieve()
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = True
 
'Open the web page

IE.Navigate "https://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 2 - Select company Name

Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
'Add hyperlink

ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
 
End Sub

実際、すべての業界のすべての企業の連絡先情報を抽出する必要があるため、このタスクを完了するには for ループ ステートメントを使用する必要があります。以下は完全なコードです。また、コードは、この記事の最後にダウンロードできるすべての会社の連絡先情報を取得する.xlsm にもあります。

ソースコード Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 Web サイトから Excel にデータをインポートする方法 
Sub Retrieve()
 
For idex = 2 To 18
 
'Create InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window

IE.Visible = False
 
'Open the web page

IE.Navigate "https://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading

Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
idexn = idex - 1
 
'Part 1 - Select dropdown

Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = idexn
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
wurl = IE.LocationURL
 
tot = IE.document.getElementsByClassName("SearchTotal")(0).innerHTML
 
pg = Int(tot / 25) + 1
 
Max = (tot Mod 25) - 1
 
'Part 2 - Select Class = "Name"

a = 2
 
For j = 1 To pg
 
If j = 1 Then
 
IE.Navigate (wurl)
 
Else
 
IE.Navigate (wurl & "&pg=" & j)
 
End If
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
If j <> pg Then
 
For i = 1 To 24
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
Else
 
For i = 0 To Max
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading

Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information

Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
ThisWorkbook.Worksheets(idex).Hyperlinks.Add ThisWorkbook.Worksheets(idex).Cells(a, 6), ThisWorkbook.Worksheets(idex).Cells(a, 6)
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
End If
 
ThisWorkbook.Save
 
Next j
 
Set IE = Nothing
 
Set contactobj = Nothing
 
Next idex
 
End Sub

説明する必要がある唯一のポイントは、図 3.2 に示されています。 1 つの Web に掲載できる企業は 25 社までです。合計企業数が 25 を超える場合、複数のページが表示されます。図 3.2 は、最初のページ以降のページのアドレスを取得する規則があることを示しています。これは、最初のページのアドレス「&pg=」と実際のページ番号を連結した結果です。また、最終ページより前のすべてのページでは、合計オブジェクト数は 25 です。「IE.document.getElementsByClassName(“SearchTotal”)(0).innerHTML」は、業界内の企業の合計数を返すことができます。この場合は 651 になります。「Int(tot / 25) + 1」で総ページ数を取得できます。また、「Max =(tot Mod 25) – 1」は、最後のページにある企業の最大数を返すことができます。ここでやめて、このアイデアをコードに適用する方法を理解してもらいます。コードを把握するためのはるかに優れたアプローチです。質問がある場合は、コメントを残すことができます。

Web サイトから Excel にデータをインポートする方法

図 3.2

ここに、最終的な Excel の一部を示します。 1 つの業界内のすべての企業の連絡先情報が 1 つのワークシートにまとめられています。

Web サイトから Excel にデータをインポートする方法

図 3.3 [画像をクリックして全体を表示]

作業ファイルをダウンロード

以下のリンクから作業ファイルをダウンロードしてください。

Pull-Data-from-Web-to-Excel.rar

関連記事

  • ウェブサイトから Excel にデータを自動的に抽出する方法
  • Word から Excel へのデータ (文、段落、表、コメント) のインポート
  • Excel VBA プログラミングのベスト 6 冊 (初心者および上級ユーザー向け)
  • Excel VBA プログラミングとマクロを学ぶ (無料チュートリアル – ステップバイステップ)
  • Excel VBA コーディングのヒント
  • VBA でできること
  • VBA マクロの概要


  1. アンケートの質的データを Excel で分析する方法

    この記事では、アンケートの質的データを Excel で分析する方法を学びます。 .多くの場合、Excel ユーザーは、アンケートから定性的なデータを分析する必要があります。アンケートには、質問のリストが含まれています。 定性データを分析するため 、いくつかの特定の手順に従う必要があります。今日は、これらの簡単な手順を実演し、Excel で質的データを分析する方法を紹介します。それでは、遅滞なく、議論を始めましょう。 練習帳をダウンロード ここからワークブックをダウンロードして練習できます。 アンケートの質的データを Excel で分析するための段階的な手順 ステップバイステップの手順を説明

  2. Excel でデータ モデルからデータを取得する方法 (2 つの簡単な方法)

    Excel でデータ モデルからデータを取得するための特別なトリックを探している場合は、適切な場所に来ています。データ モデルからデータを取得するには、さまざまな方法があります。この記事では、データ モデルからデータを取得するこれらのメソッドの詳細について説明します。このすべてを学ぶために完全なガイドに従ってみましょう. データ モデルとは データモデルは、データ分析に不可欠です。データ モデルを使用して、データ (テーブルなど) を Excel のメモリに読み込むことができます。データを接続するには、共通の列を使用するように Excel に指示できます。データモデルでは、「モデル」という言