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.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300214.png)
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.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300211.png)
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 .![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300238.png)
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.![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300272.png)
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.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300325.png)
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’.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300381.png)
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.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300381.png)
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.
Mã nguồn | ![]() ![]() ![]() |
'Add hyperlink ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
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.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300355.png)
Hình 2.4
Bài đọc tương tự
- Cách mở một sổ làm việc khác và sao chép dữ liệu bằng Excel VBA
- [Đã sửa lỗi!] Phương pháp Mở Sổ làm việc Đối tượng Không thành công (4 Giải pháp)
- Excel VBA để điền mảng với giá trị ô (4 ví dụ phù hợp)
- Cách Mở Workbook và Chạy Macro bằng VBA (4 Ví dụ)
Tương tác với các trang web
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?
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300377.png)
Hình 3.1 [click vào hình để xem đầy đủ]
Chà, điểm mấu chốt là làm thế nào để VBA tương tác với các trang web giống như những gì chúng ta làm thủ công. Hãy lấy S.W.I.S Advantage làm ví dụ. Nói chung, chúng tôi hy vọng rằng Excel có thể nhấp vào Doanh nghiệp nông nghiệp (bảng trên cùng của Hình 3.1) và do đó kích hoạt IE để chuyển hướng chúng tôi đến trang web thứ hai. Trên trang web thứ hai (bảng điều khiển phía dưới của Hình 3.1), Excel có thể nhấp vào S.W.I.S Advantage, sau đó IE hướng chúng ta đến trang như trong Hình 2.1 để chúng ta có thể truy xuất thông tin liên hệ của S.W.I.S Advantage.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.
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
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).
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
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.
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
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.
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
Đ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.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300370.png)
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.
![Cách nhập dữ liệu từ trang web sang Excel?](/article/uploadfiles/202210/2022103116300349.png)
Hình 3.3 [click vào hình để xem đầy đủ]
Tải xuống tệp làm việc
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
Các bài viết có liên quan
- Làm cách nào để tự động trích xuất dữ liệu từ trang web sang Excel?
- Nhập dữ liệu (Câu, Đoạn, Bảng, Nhận xét) từ Word sang Excel
- 6 Sách Lập trình VBA Excel Hay nhất (Dành cho Người mới bắt đầu &Người dùng Nâng cao)
- Học Lập trình Excel VBA &Macro (Hướng dẫn Miễn phí - Từng bước)
- Mẹo viết mã VBA trong Excel
- Những gì bạn có thể làm với VBA
- Giới thiệu về Macro VBA