World Wide Web에는 방대한 유용한 데이터가 포함되어 있다는 것은 잘 알려져 있습니다. 그러나 어떤 종류의 분석을 수행하기 전에 데이터를 Microsoft Excel로 가져와야 합니다. 이러한 종류의 작업을 완료하는 데 사용할 수 있는 두 가지 방법이 있습니다.
웹에서 수동으로 외부 데이터 가져오기
이 웹 페이지에서 최고의 수익 창출 영화에 대한 수익을 다운로드하려는 경우 여기에서 쉽게 수행하는 방법을 보여줍니다.
Microsoft Excel을 열고 데이터를 클릭합니다. 탭의 외부 데이터 가져오기 그룹에서 웹에서를 클릭합니다. . 새 웹 쿼리 이후 대화 상자가 나타나면 웹 주소(https://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office)를 주소에 복사합니다. 필드를 클릭하고 이동을 클릭합니다. 단추. Excel은 그림 1.1과 같이 웹 페이지 다운로드를 시작합니다. 아니요를 클릭하세요. 스크립트 오류 가 발생하는 경우 아래 그림과 유사한 경고 상자. 상자가 사라지고 가져오기 프로세스에 영향을 주지 않습니다.
그림 1.1
새 웹 쿼리 대화 상자의 오른쪽 상단에 있는 노란색 상자에 화살표가 있습니다. 클릭하면 표 앞에 유사한 아이콘을 표시할지 여부를 결정할 수 있습니다. 예를 들어, 그림 1.2의 왼쪽 패널에 있는 테이블 옆에는 화살표 아이콘이 없습니다. 화살표 버튼을 클릭하면 아이콘이 표시됩니다(오른쪽 패널에).
그림 1.2 [전체보기를 보려면 이미지를 클릭하십시오]
선택하려는 테이블 옆에 있는 화살표 아이콘을 클릭합니다. 아이콘과 표는 그림 1.3의 왼쪽 패널에 표시된 것과 유사하게 변경됩니다. 가져오기를 클릭한 후; 데이터 가져오기 대화 상자가 표시됩니다. 데이터를 넣을 범위(여기서는 A열부터 H열까지)를 채우고 확인을 클릭합니다. .
그림 1.3 [전체보기를 보려면 이미지를 클릭하십시오]
확인을 클릭하면 데이터를 Excel로 가져옵니다. . 표 내의 셀을 마우스 오른쪽 버튼으로 클릭하고 새로고침을 선택하면 , Excel이 나가서 웹 페이지에서 가장 최근 데이터를 가져옵니다.
그림 1.4
또한 쿼리 데이터를 원하는 방식으로 새로 고칠 수도 있습니다. 표 내의 아무 셀이나 마우스 오른쪽 버튼으로 클릭하고 데이터 범위 속성을 선택합니다. . 프롬프트된 외부 데이터 범위 속성에서 대화 상자에서 새로 고침 제어를 변경할 수 있습니다. 스타일을 변경하는 설정. 예를 들어, 60분마다 또는 파일이 열릴 때 새로고침되도록 쿼리를 지정할 수 있습니다.
VBA 프로그래밍을 사용하여 데이터 스크랩
VBA 프로그래밍은 웹 페이지에서 데이터를 스크랩하는 데 사용할 수 있습니다. 첫 번째 접근 방식과 비교할 때 어렵지만 훨씬 더 유용합니다. 또한 VBA 프로그래밍을 사용하여 데이터를 스크랩하는 방법을 배우기 전에 HTML이 무엇인지 이해해야 합니다. HTML에 대해 전혀 모르거나 거의 모르는 경우 이 웹사이트에서 HTML에 대한 기본 지식을 배우는 것이 좋습니다. 그리고 여기에서 VBA를 사용하여 웹 스크래핑과 관련된 거의 모든 것을 배울 수 있습니다. 이 기사에서는 두 가지 예만 보여줍니다.
한 웹페이지에서 데이터 스크랩
이 웹 페이지에서 회사 이름, 이메일 주소 및 연락처 이름을 추출하려고 한다고 가정합니다. 이 웹 페이지를 열면 하단에 연락처 블록이 있음을 알 수 있습니다. 그림 2.1은 접점 블록과 해당 소스 코드를 보여줍니다. 빨간색 상자에 있는 정보가 우리가 필요로 하는 것입니다. 그리고 녹색 밑줄이 있는 것들은 정확히 우리가 추출해야 하는 것입니다.
그림 2.1
다음 코드는 위에서 요청한 정보를 추출하여 첫 번째 워크시트에 넣는 데 도움이 됩니다.
| 소스 코드 |    |
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인 모든 요소를 가져올 수 있습니다. 이 웹 페이지는 HTML 요소에 사용할 수 있는 거의 모든 속성과 메서드를 나열합니다. 자신의 문제에 적합한 것을 선택할 수 있습니다.
innerHTML 속성을 사용하면 HTML 요소의 내용을 설정하거나 반환할 수 있습니다. 우리의 경우 "contact-details block dark"라는 클래스 이름을 가진 요소의 내용을 반환하고 변수 htext로 설정했습니다. 내용(그림 2.2)은 'Msgbox htext'로 확인할 수 있습니다.
그림 2.2
텍스트가 잘 구성되어 있음을 알 수 있습니다. 그래서 SPLIT 함수를 사용하여 필요한 것을 추출할 수 있습니다. 예를 들어 "
Company Name:"을 구분 기호로 사용하고 "Split(htext, "
Company Name:")(1)" 수식은 "
Company Name:". 새로 반환된 이 텍스트의 경우 "
그림 2.2에서 확인을 클릭하면 요청된 데이터를 웹에서 Excel 워크시트로 가져올 수 있습니다. 예를 들어 A1 셀에는 회사 이름이 포함되어 있고 A4 셀에는 회사 웹 페이지 주소가 포함되어 있습니다.
그림 2.3
통합 문서를 저장하기 전에 아래 코드를 추가하면 A4 셀에 하이퍼링크를 추가할 수 있습니다.
A4 셀을 클릭하면 웹 페이지를 다시 방문할 수 있습니다. 이것은 많은 회사의 데이터를 검색해야 할 때 특히 유용합니다. 하이퍼링크를 클릭하여 해당 웹 페이지를 다시 방문하고 나중에 검토하는 동안 수동으로 정보를 추가하거나 업데이트할 수 있기 때문입니다.
그림 2.4
유사한 수치
위의 예는 하나의 정적 웹 페이지에서 데이터를 검색하는 방법을 보여줍니다. 그러나 많은 양의 데이터를 검색하기 위해 웹 페이지와 상호 작용한 것을 추출해야 하는 경우가 많습니다. 그림 3.1을 보십시오. 위 예제의 웹 페이지에 액세스하는 방법을 보여줍니다. 업종도 많고 업종별로 회사도 많다는 것을 알 수 있습니다. 예를 들어, 농업 관련 산업에는 651개의 회사가 있습니다. 모든 산업에서 모든 회사의 연락처 정보를 추출하려면 어떻게 해야 합니까?
그림 3.1 [전체보기를 보려면 이미지를 클릭하십시오]
Visual Basic Editor에 다음 코드를 입력하고 코드를 제출하면 IE가 열리는 것을 볼 수 있습니다. 첫 번째 웹 페이지가 나타난 다음 두 번째 웹 페이지가 나타납니다. 여기에서 드롭다운 목록 요소를 가져오고, 옵션을 선택하고, 옵션을 선택한 후 이벤트를 트리거하는 방법을 배웁니다. "m =IE.document.getElementsByTagName("option").Length – 1"은 총 옵션 수를 제공합니다. 다음 루프에 사용할 수 있습니다.
코드의 이 부분은 Excel에서 이름을 클릭한 후 그림 2.1에 표시된 웹 페이지로 안내할 수 있습니다. 클래스 이름이 "Name"인 요소에는 모든 회사의 이름이 포함됩니다. Searchobj는 컬렉션이고 searchobj(i)는 (i+1)
번째
를 반환할 수 있습니다. 물체. 예를 들어 searchobj(1).Click을 사용하면 RIDLEY CORPORATION(Melbourne)의 웹 페이지를 방문할 수 있습니다.
마지막으로 IE를 열고 웹 페이지를 탐색하고 데이터를 추출하는 과정을 보여주는 전체 코드입니다. 추출된 데이터는 그림 2.4와 동일합니다.
사실, 우리가 해야 할 일은 모든 산업의 모든 회사에 대한 연락처 정보를 추출하는 것이므로 이 작업을 완료하려면 for 루프 문을 사용해야 합니다. 다음은 전체 코드입니다. 또한 이 문서의 끝에서 다운로드할 수 있는 모든 회사에 대한 연락처 정보 검색.xlsm에서 코드를 찾을 수 있습니다.
내가 설명해야 할 유일한 요점은 그림 3.2에 나와 있습니다. 하나의 웹에는 최대 25개의 회사만 나열할 수 있습니다. 회사의 총 수가 25개보다 크면 한 페이지 이상이 됩니다. 그림 3.2는 첫 번째 페이지 이후에 페이지 주소를 얻는 규칙이 있음을 보여줍니다. 첫 번째 페이지의 주소인 "&pg="와 실제 페이지 번호를 연결한 결과입니다. 그리고 마지막 페이지 이전의 모든 페이지에 대해 총 개체 수는 25입니다. "IE.document.getElementsByClassName("SearchTotal")(0).innerHTML"은 산업 내 기업의 총 수를 반환할 수 있습니다. 우리의 경우 651이 됩니다. "Int(tot / 25) + 1"은 총 페이지 수를 얻을 수 있습니다. 그리고 "Max =(tot Mod 25) – 1"은 마지막 페이지의 최대 회사 수를 반환할 수 있습니다. 여기에서 멈추고 이 아이디어를 코드에 적용하는 방법을 알아내도록 할게요. 코드를 파악하는 것이 훨씬 더 나은 접근 방식입니다. 질문이 있으면 댓글을 남길 수 있습니다.
그림 3.2
다음은 최종 Excel의 일부를 보여줍니다. 한 산업에 속한 모든 회사의 연락처 정보가 하나의 워크시트에 정리되어 있습니다.
그림 3.3 [전체보기를 보려면 이미지를 클릭하십시오]
아래 링크에서 작업 파일을 다운로드하세요.
웹에서 Excel로 데이터 가져오기.rar 소스 코드 'Add hyperlink
ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
웹 페이지와 상호작용
소스 코드 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 소스 코드 '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 소스 코드 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 소스 코드 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 
작업 파일 다운로드
관련 기사