Ai cũng biết rằng World Wide Web chứa dữ liệu hữu ích khổng lồ. Tuy nhiên, chúng tôi phải nhập dữ liệu vào Microsoft Excel trước khi thực hiện bất kỳ loại phân tích nào. Có hai phương pháp mà bạn có thể sử dụng để hoàn thành loại nhiệm vụ này.
Lấy dữ liệu bên ngoài từ web theo cách thủ công
Giả sử bạn muốn tải xuống doanh thu về các bộ phim kiếm tiền hàng đầu từ trang web này, ở đây sẽ hướng dẫn bạn cách thực hiện dễ dàng.
Mở Microsoft Excel và nhấp vào Dữ liệu trong tab Lấy dữ liệu bên ngoài nhóm, nhấp vào Từ web . Sau Truy vấn web mới hộp thoại xuất hiện, sao chép địa chỉ web (https://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) vào Địa chỉ và nhấp vào Bắt đầu cái nút. Excel sẽ bắt đầu tải trang web như trong Hình 1.1. Chỉ cần nhấp vào Không nếu bạn gặp Lỗi tập lệnh hộp cảnh báo tương tự như trong hình dưới đây. Hộp sẽ biến mất và nó không ảnh hưởng đến quá trình nhập của bạn.
Hình 1.1
Có một mũi tên trong hộp màu vàng ở góc trên bên phải của hộp thoại Truy vấn Web Mới. Bằng cách nhấp vào nó, bạn có thể xác định xem có hiển thị các biểu tượng tương tự trước bảng hay không. Ví dụ, không có biểu tượng mũi tên bên cạnh bảng trong bảng điều khiển bên trái của Hình 1.2. Và sẽ có một biểu tượng (trong bảng bên phải) sau khi bạn nhấp vào nút mũi tên để hiển thị các biểu tượng.
Hình 1.2 [bấm vào ảnh để xem đầy đủ]
Nhấp vào biểu tượng mũi tên bên cạnh bảng bạn muốn chọn. Các biểu tượng và bảng sẽ thay đổi và tương tự như trong bảng điều khiển bên trái của Hình 1.3. Sau khi bạn nhấp vào Nhập; một Nhập dữ liệu hộp thoại sẽ được nhắc. Điền vào phạm vi (từ cột A đến cột H trong trường hợp của chúng tôi) nơi bạn muốn đặt dữ liệu và nhấp vào OK .
Hình 1.3 [click vào hình để xem đầy đủ]
Dữ liệu sẽ được nhập vào Excel sau khi bạn nhấp vào Ok . Nếu bạn nhấp chuột phải vào bất kỳ ô nào trong bảng và chọn Làm mới , Excel sẽ đi ra ngoài và lấy ra dữ liệu gần đây nhất từ một trang web.
Hình 1.4
Và bạn thậm chí có thể đặt dữ liệu truy vấn của mình được làm mới theo bất kỳ kiểu nào bạn muốn. Chỉ cần nhấp chuột phải vào bất kỳ ô nào trong bảng, chọn Thuộc tính dải dữ liệu . Trong Thuộc tính dải dữ liệu bên ngoài được nhắc hộp thoại, bạn có thể thay đổi Kiểm soát làm mới cài đặt để thay đổi kiểu. Ví dụ:bạn có thể chỉ định truy vấn được làm mới sau mỗi 60 phút hoặc khi tệp được mở.
Xử lý dữ liệu bằng lập trình VBA
Lập trình VBA có thể được sử dụng để thu thập dữ liệu từ một trang web. Nó hữu ích hơn nhiều trong khi khó so sánh với cách tiếp cận đầu tiên. Hơn nữa, bạn cần hiểu HTML là gì trước khi học cách quét dữ liệu bằng lập trình VBA. Tôi khuyên bạn nên tìm hiểu kiến thức cơ bản về HTML từ trang web này nếu bạn không biết gì hoặc ít về HTML. Và từ đây, bạn có thể tìm hiểu hầu hết mọi thứ liên quan đến web cạo bằng VBA. Bài viết này sẽ chỉ cho bạn thấy hai ví dụ.
Cóp nhặt dữ liệu từ một trang web
Giả sử rằng chúng ta muốn trích xuất tên công ty, địa chỉ email và tên liên hệ từ trang web này. Nếu bạn mở trang web này, bạn sẽ thấy rằng có một khối liên hệ ở dưới cùng. Hình 2.1 cho thấy khối liên lạc và mã nguồn tương ứng. Thông tin trong ô màu đỏ là những gì chúng ta muốn cần. Và những gì có gạch chân màu xanh lá cây là chính xác những gì chúng ta cần trích xuất.
Hình 2.1
Đoạn mã sau có thể giúp bạn trích xuất thông tin được yêu cầu ở trên và đưa chúng vào trang tính đầu tiên.
| Mã nguồn |    |
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 (“ khối chi tiết liên hệ tối ”)” có thể cho phép bạn nhận được tất cả các phần tử có tên lớp - khối chi tiết liên hệ tối. Trang web này liệt kê hầu hết tất cả các thuộc tính và phương pháp có thể được sử dụng trên các phần tử HTML. Bạn có thể chọn cái thích hợp cho vấn đề của riêng mình.
Thuộc tính innerHTML có thể cho phép bạn thiết lập hoặc trả về nội dung của một phần tử HTML. Trong trường hợp của chúng tôi, nó trả về nội dung của phần tử có tên lớp là "khối liên hệ chi tiết tối" và đặt nó thành một văn bản biến đổi. Nội dung (Hình 2.2) có thể được nhắc bởi ‘Msgbox htext’.
Hình 2.2
Bạn có thể thấy rằng văn bản được cấu trúc tốt. Đó là lý do tại sao chúng ta có thể sử dụng hàm SPLIT để trích xuất những thứ cần thiết. Ví dụ:nếu chúng ta lấy “
Tên Công ty:” làm dấu phân tách và công thức “Tách (văn bản htext,“
Tên Công ty:“) (1)” có thể trả về toàn bộ văn bản sau “
Tên Công ty:”. Đối với văn bản mới trả về này, chúng ta có thể lấy “
Sau khi bạn nhấp vào OK trong Hình 2.2, dữ liệu được yêu cầu có thể được nhập từ web vào trang tính Excel. Ví dụ:ô A1 chứa Tên Công ty trong khi ô A4 chứa địa chỉ trang web của công ty.
Hình 2.3
Bằng cách thêm mã bên dưới trước khi lưu sổ làm việc, bạn có thể thêm siêu kết nối vào ô A4.
Nếu bạn nhấp vào ô A4, bạn có thể truy cập lại trang web. Điều này đặc biệt hữu ích khi bạn phải truy xuất dữ liệu cho nhiều công ty. Vì bạn có thể nhấp vào bất kỳ siêu liên kết nào để truy cập lại trang web tương ứng và thêm hoặc cập nhật bất kỳ thông tin nào theo cách thủ công trong quá trình xem xét sau này.
Hình 2.4
Bài đọc tương tự
Ví dụ trên chỉ minh họa cách lấy dữ liệu từ một trang web tĩnh. Nhưng thường xuyên hơn, chúng tôi được yêu cầu trích xuất đã tương tác với các trang web để truy xuất một lượng lớn dữ liệu. Nhìn vào hình 3.1. Nó chỉ cho bạn cách truy cập trang web của ví dụ trên. Bạn có thể thấy rằng có rất nhiều ngành và đối với mỗi ngành, có rất nhiều công ty. Ví dụ, có 651 công ty trong ngành Kinh doanh nông nghiệp. Chúng ta nên làm gì nếu chúng ta muốn trích xuất thông tin liên hệ của tất cả các công ty từ tất cả các ngành?
Hình 3.1 [click vào hình để xem đầy đủ]
Sau khi nhập mã sau vào Visual Basic Editor và gửi mã, bạn sẽ thấy rằng IE của bạn đã được mở. Trang web đầu tiên xuất hiện, tiếp theo là trang web thứ hai. Tại đây, bạn sẽ tìm hiểu cách lấy phần tử danh sách thả xuống, chọn một tùy chọn và kích hoạt một sự kiện sau khi bạn chọn tùy chọn. “M =IE.document.getElementsByTagName (“ option ”). Độ dài - 1” sẽ cung cấp cho bạn tổng số tùy chọn. Điều này có thể được sử dụng cho vòng lặp tiếp theo.
Phần mã này có thể đưa bạn đến trang web được hiển thị trong Hình 2.1 sau khi Excel nhấp vào tên đầu tiên. Tên của tất cả các công ty được bao gồm trong phần tử có tên lớp là "Tên". Searchobj là một tập hợp và searchhobj (i) có thể trả về (i + 1)
th
sự vật. Ví dụ:searchhobj (1). Nhấp chuột có thể cho phép bạn truy cập trang web của RIDLEY CORPORATION (Melbourne).
Cuối cùng, đây là toàn bộ mã có thể cho bạn thấy quá trình mở IE, duyệt các trang web và trích xuất dữ liệu. Dữ liệu được trích xuất sẽ giống như trong Hình 2.4.
Trên thực tế, những gì chúng ta cần làm là trích xuất thông tin liên hệ của tất cả các công ty từ tất cả các ngành, do đó, chúng ta cần sử dụng câu lệnh lặp for để hoàn thành công việc này. Sau đây là mã hoàn chỉnh. Và bạn cũng có thể tìm thấy mã trong Truy xuất thông tin liên hệ cho tất cả các công ty.xlsm mà bạn có thể tải xuống ở cuối bài viết này.
Điểm duy nhất mà tôi cần giải thích được minh họa trong Hình 3.2. Một trang web chỉ có thể liệt kê tối đa 25 công ty. Khi tổng số công ty lớn hơn 25, sẽ có nhiều hơn một trang. Hình 3.2 cho thấy có một quy tắc để lấy địa chỉ của các trang sau trang đầu tiên. Đây là kết quả của địa chỉ nối của trang đầu tiên, “&pg =” và số trang thực tế. Và đối với tất cả các trang trước trang cuối cùng, tổng số đối tượng là 25. “IE.document.getElementsByClassName (“ SearchTotal ”) (0) .innerHTML” có thể trả về tổng số công ty trong một ngành. Trong trường hợp của chúng tôi, nó sẽ là 651. “Int (tot / 25) + 1” có thể cho bạn biết tổng số trang. Và “Max =(tot Mod 25) - 1” có thể trả về số lượng công ty tối đa trên trang cuối cùng. Tôi sẽ dừng lại ở đây và để bạn tìm ra cách áp dụng ý tưởng này vào mã. Đó là một cách tiếp cận tốt hơn nhiều để bạn nắm được mã. Bạn có thể để lại bình luận nếu bạn có thắc mắc.
Hình 3.2
Ở đây cho bạn thấy một phần của excel cuối cùng. Thông tin liên hệ của tất cả các công ty trong một ngành được tổng hợp trong một trang tính.
Hình 3.3 [click vào hình để xem đầy đủ]
Tải xuống tệp làm việc từ liên kết bên dưới.
Kéo-Dữ liệu-từ-Web-sang-Excel.rar
Mã nguồn 'Add hyperlink
ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
Tương tác với các trang web
Mã nguồn 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
Mã nguồn '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
Mã nguồn 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
Mã nguồn 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 
Tải xuống tệp làm việc
Các bài viết có liên quan