Nhập Dữ Liệu Từ Trang Web Vào Excel Dễ Dàng: Hướng Dẫn Bước Để Bước

Ai cũng biết World Wide Web chứa lượng lớn dữ liệu hữu ích. Tuy nhiên, chúng ta phải nhập dữ liệu vào Microsoft Excel trước khi làm bất kỳ phân tích nào. Có hai phương pháp bạn có thể dùng để hoàn thành loại tác vụ này.

Lấy Dữ Liệu Bên Ngoài Từ Web Thủ Công

Giả sử bạn muốn tải doanh thu về các bộ phim kiếm tiền nhất từ trang web này, đây cho bạn thấy cách làm dễ dàng.

Mở Microsoft Excel và nhấp vào tab Data, trong nhóm Get External Data, nhấp From Web. Sau khi hộp thoại New Web Query xuất hiện, sao chép địa chỉ web (https://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) vào trường Address và nhấp vào nút Go. Excel sẽ bắt đầu tải trang web như Hình 1.1. Chỉ cần nhấp No nếu bạn nhận được hộp cảnh báo Script Error tương tự như hình bên dưới. Hộp sẽ biến mất và không ảnh hưởng đến quá trình nhập của bạn.

Nhập Dữ Liệu Từ Trang Web Vào Excel Dễ Dàng: Hướng Dẫn Bước Để Bước

Hình 1.1

Có một mũi tên trong hộp vàng ở góc trên bên phải của hộp thoại New Web Query. Bằng cách nhấp vào nó, bạn có thể quyết định có hiển thị biểu tượng tương tự trước cá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ó biểu tượng (trong bảng điều khiển bên phải) sau khi bạn nhấp vào nút mũi tên để hiển thị biểu tượng.

Hình 1.2 [nhấp vào hình ảnh để xem toàn bộ]

Nhấp vào biểu tượng mũi tên bên cạnh các 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ư bảng điều khiển bên trái của Hình 1.3. Sau khi bạn nhấp vào Import; hộp thoại Import Data sẽ được nhắc. Điền phạm vi (từ cột A đến cột H trong trường hợp của chúng ta) nơi bạn muốn đặt dữ liệu và nhấp vào OK. Nhập Dữ Liệu Từ Trang Web Vào Excel Dễ Dàng: Hướng Dẫn Bước Để Bước

Hình 1.3 [nhấp vào hình ảnh để xem toàn bộ]

Dữ liệu sẽ được nhập vào Excel sau khi bạn nhấp vào Ok. Nếu bạn chuột phải vào bất kỳ ô nào trong bảng và chọn Refresh, Excel sẽ ra ngoài và kéo dữ liệu mới nhất từ trang web. Nhập Dữ Liệu Từ Trang Web Vào Excel Dễ Dàng: Hướng Dẫn Bước Để Bước

Hình 1.4

Và bạn thậm chí có thể đặt dữ liệu truy vấn của bạn được làm mới theo bất kỳ cách nào bạn muốn. Chỉ cần chuột phải vào bất kỳ ô nào trong bảng, chọn Data Range Properties. Trong hộp thoại External Data Range Properties được nhắc, bạn có thể thay đổi cài đặt Refresh Control để thay đổi kiểu. Ví dụ, bạn có thể chỉ định truy vấn được làm mới mỗi 60 phút hoặc khi file được mở.

Nhập Dữ Liệu Từ Trang Web Vào Excel Dễ Dàng: Hướng Dẫn Bước Để Bước

Cào Dữ Liệu Sử Dụng Lập Trình VBA

Lập trình VBA có thể dùng để cào dữ liệu từ trang web. Nó hữu ích hơn nhiều so với cách đầu tiên nhưng cũng khó khăn hơn. Hơn nữa, bạn cần hiểu HTML là gì trước khi học cách cào dữ liệu dùng VBA. Tôi khuyên bạn nên học kiến thức cơ bản về HTML từ trang web này nếu bạn không biết hoặc biết ít về HTML. Và từ đây, bạn có thể học gần như mọi thứ liên quan đến cào web dùng VBA. Bài viết này chỉ chỉ cho bạn hai ví dụ.

Cào Dữ Liệu Từ Một Trang Web

Giả sử 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 có một khối liên hệ ở dưới cùng. Hình 2.1 cho thấy khối liên hệ và mã nguồn tương ứng. Thông tin trong hộp đỏ là những gì chúng ta cần. Và những cái có gạch dưới xanh lá là chính xác những gì chúng ta cần trích xuất.

Hình 2.1

Mã sau có thể giúp bạn trích xuất thông tin yêu cầu ở trên và đặt chúng vào trang tính đầu tiên.

Mã nguồn

Sub Retrieve_Click()
  'Tạo InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
  'Đừng để thấy cửa sổ trình duyệt
IE.Visible = False
  'Mở trang web
IE.Navigate "https://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736"
  'Đợi IE tải xong
Do While IE.readyState <> 4 Or IE.Busy = True
  DoEvents
  Loop
  'Lấy tên công ty, email & thông tin liên hệ
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")" cho phép bạn lấy tất cả phần tử với tên lớp – contact-details block dark. Trang web này liệt kê hầu như tất cả thuộc tính và phương pháp có thể dùng trên phần tử HTML. Bạn có thể chọn cái phù hợp cho bài toán của mình.

Thuộc tính innerHTML cho phép bạn đặt hoặc trả về nội dung của một phần tử HTML. Trong trường hợp của chúng ta, nó trả về nội dung của phần tử với tên lớp "contact-details block dark" và gán cho biến htext. Nội dung (Hình 2.2) có thể được nhắc bằng 'Msgbox htext'.

Hình 2.2

Bạn thấy văn bản được cấu trúc tốt. Đó là lý do chúng ta có thể dùng hàm SPLIT để trích xuất thứ cần. Ví dụ, nếu lấy "<p>Company Name:" làm dấu phân tách và công thức "Split(htext, "<p>Company Name: ")(1)" có thể trả về toàn bộ văn bản sau "<p>Company Name:". Đối với văn bản mới trả về này, chúng ta có thể lấy "<br" làm dấu phân tách và lấy văn bản trước "<br" đầu tiên và đó là tên công ty. Tóm lại, hàm SPLIT là công cụ linh hoạt bạn có thể dùng để trích xuất gần như mọi thứ. Các hàm hữu ích khác bao gồm LEN, INSTR, LEFT, RIGHT, MID, và REPLACE. Tôi sẽ không bàn chi tiết ở đây.

Sau khi bạn nhấp OK ở Hình 2.2, dữ liệu 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ông ty.

Hình 2.3

Bằng cách thêm mã dưới đây trước khi lưu sổ làm việc, bạn có thể thêm siêu liên kết vào ô A4.

Mã nguồn

'Thêm siêu liên kết
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 hữu ích đặc biệt 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 thủ công trong quá trình xem xét sau.

Hình 2.4

Các Bài Đọc Tương Tự

Cách Mở Sổ Làm Việc Khác Và Sao Chép Dữ Liệu Với Excel VBA
[Fixed!] Method Open of Object Workbooks Failed (4 Giải Pháp)
Excel VBA Để Điền Mảng Với Giá Trị Ô (4 Ví Dụ Phù Hợp)
Cách Mở Sổ Làm Việc Và Chạy Macro Sử Dụng VBA (4 Ví Dụ)

Tương Tác Với Trang Web

Ví dụ trên chỉ minh họa cách truy xuất dữ liệu từ một trang web tĩnh. Nhưng thường thường hơn, chúng ta được yêu cầu trích xuất dữ liệu đã tương tác với trang web để lấy lượng lớn dữ liệu. Xem Hình 3.1. Nó cho thấy bạn cách đến trang web của ví dụ trên. Bạn thấy có rất nhiều ngành và cho mỗi ngành, có rất nhiều công ty. Ví dụ, có 651 công ty trong ngành Nông nghiệp. Chúng ta nên làm gì nếu muốn trích xuất thông tin liên hệ của tất cả công ty từ tất cả ngành?

Hình 3.1 [nhấp vào hình ảnh để xem toàn bộ]

Điểm mấu chốt là làm thế nào để VBA tương tác với trang web như chúng ta làm thủ công. Hãy lấy S.W.I.S Advantage làm ví dụ. Chung quy lại, chúng ta hy vọng Excel có thể nhấp vào Nông nghiệp (bảng điều khiển trên cùng của Hình 3.1) và do đó kích hoạt IE để dẫn chúng ta đến trang web thứ hai. Trên trang web thứ hai (bảng điều khiển dưới cùng của Hình 3.1), Excel có thể nhấp vào S.W.I.S Advantage, và sau đó IE dẫn chúng ta đến trang như 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 IE được mở. Trang web đầu tiên xuất hiện theo sau là trang web thứ hai. Ở đây bạn sẽ học 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 sự kiện sau khi chọn tùy chọn. "m = IE.document.getElementsByTagName("option").Length – 1" sẽ cho bạn tổng số tùy chọn. Điều này có thể dùng cho vòng lặp tiếp theo.

Mã nguồn

Sub retrieve()
  'Tạo InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
  'Hãy xem cửa sổ trình duyệt
IE.Visible = True
  'Mở trang web
IE.Navigate "https://www.austrade.gov.au/international/buy#"
  'Đợi IE tải
Do While IE.Busy
  Application.Wait DateAdd("s", 1, Now)
  Loop
  Application.Wait (Now + TimeValue("00:00:10"))
  'Phần 1 - Chọn danh sách thả xuống và kích hoạt sự kiện sau khi chọn tùy chọn
Set selectobj = IE.document.getElementsByTagName("select")
  m = IE.document.getElementsByTagName("option").Length - 1
  selectobj(0).selectedIndex = 1
  selectobj(0).FireEvent ("onchange")
  'Đợi IE tải
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ể dẫn bạn đến trang web như Hình 2.1 sau khi Excel nhấp vào tên đầu tiên. Tất cả tên công ty đều nằm trong phần tử với tên lớp "Name". Searchobj là một tập hợp và searchobj(i) có thể trả về đối tượng (i+1)^th. Ví dụ, searchobj(1).Click cho phép bạn truy cập trang web cho RIDLEY CORPORATION (Melbourne).

Mã nguồn

'Phần 2 - Chọn tên công ty
Set searchobj = IE.document.getElementsByClassName("Name")
  searchobj(0).Click
  'Đợi IE tải
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 quy trình mở IE, duyệt trang web, và trích xuất dữ liệu. Dữ liệu trích xuất sẽ giống như ở Hình 2.4.

Mã nguồn

Sub Retrieve()
  'Tạo InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
  'Hãy xem cửa sổ trình duyệt
IE.Visible = True
  'Mở trang web
IE.Navigate "https://www.austrade.gov.au/international/buy#"
  'Đợi IE tải
Do While IE.Busy
  Application.Wait DateAdd("s", 1, Now)
  Loop
  Application.Wait (Now + TimeValue("00:00:10"))
  'Phần 1 - Chọn danh sách thả xuống và kích hoạt sự kiện sau khi chọn tùy chọn
Set selectobj = IE.document.getElementsByTagName("select")
  m = IE.document.getElementsByTagName("option").Length - 1
  selectobj(0).selectedIndex = 1
  selectobj(0).FireEvent ("onchange")
  'Đợi IE tải
Do While IE.readyState <> 4 Or IE.Busy = True
  Application.Wait DateAdd("s", 1, Now)
  Loop
  Application.Wait (Now + TimeValue("00:00:10"))
  'Phần 2 - Chọn tên công ty
Set searchobj = IE.document.getElementsByClassName("Name")
  searchobj(0).Click
  'Đợi IE tải
Do While IE.readyState <> 4 Or IE.Busy = True
  DoEvents
  Loop
  'Phần 3 - Truy xuất tên công ty, email & thông tin liên hệ
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
  'Thêm siêu liên kết
ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
  End Sub

Thực tế, những gì chúng ta cần làm là trích xuất thông tin liên hệ cho tất cả công ty từ tất cả ngành, do đó, chúng ta cần dùng câu lệnh vòng lặp for để hoàn thành tác vụ. Dưới đây là mã hoàn chỉnh. Và bạn cũng có thể tìm mã trong Retrieve contact information for all companies.xlsm mà bạn có thể tải ở cuối bài viết.

Mã nguồn

Sub Retrieve()
  For idex = 2 To 18
  'Tạo InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
  'Đừng để thấy cửa sổ trình duyệt
IE.Visible = False
  'Mở trang web
IE.Navigate "https://www.austrade.gov.au/international/buy#"
  'Đợi IE tải
Do While IE.Busy
  Application.Wait DateAdd("s", 1, Now)
  Loop
  Application.Wait (Now + TimeValue("00:00:10"))
  idexn = idex - 1
  'Phần 1 - Chọn danh sách thả xuống
Set selectobj = IE.document.getElementsByTagName("select")
  m = IE.document.getElementsByTagName("option").Length - 1
  selectobj(0).selectedIndex = idexn
  selectobj(0).FireEvent ("onchange")
  'Đợi IE tải
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
  'Phần 2 - Chọn 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
  'Đợi IE tải
Do While IE.readyState <> 4 Or IE.Busy = True
  DoEvents
  Loop
  'Phần 3 - Truy xuất tên công ty, email & thông tin liên hệ
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
  'Đợi IE tải
Do While IE.readyState <> 4 Or IE.Busy = True
  DoEvents
  Loop
  'Phần 3 - Truy xuất tên công ty, email & thông tin liên hệ
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 tôi cần giải thích được minh họa ở Hình 3.2. Một trang web chỉ 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ó quy tắc để lấy địa chỉ các trang sau trang đầu. Đó là kết quả của việc nối địa chỉ trang đầu, "&pg=" và số trang thực tế. Và đối với tất cả các trang trước trang cuối, 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 ta, đó sẽ là 651. "Int(tot / 25) + 1" có thể cho bạn 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. Tôi dừng lại ở đây và để bạn tự tìm hiểu cách áp dụng ý tưởng này vào mã. Đó là cách tiếp cận tốt hơn nhiều để bạn nắm mã. Bạn có thể để lại bình luận nếu có câu hỏi.

Hình 3.2

Ở đây cho bạn thấy một phần của file Excel cuối cùng. Thông tin liên hệ cho tất cả công ty trong một ngành được gom lại trong một trang tính.

Hình 3.3 [nhấp vào hình ảnh để xem toàn bộ]

Tải file làm việc

Tải tệp làm việc từ liên kết bên dưới.

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

Các Bài Viết Liên Quan

Cách trích xuất dữ liệu từ trang web vào Excel tự động?
Nhập Dữ Liệu (Câu, Đoạn, Bảng, Bình Luận) Từ Word Sang Excel
6 Cuốn Sách Lập Trình Excel VBA Tốt Nhất (Cho Người Mới Bắt Đầu & Người Nâng Cao)
Học Lập Trình Excel VBA & Macro (Hướng Dẫn Miễn Phí – Bước Để Bước)
Mẹo Lập Trình Excel VBA
Những Gì Bạn Có Thể Làm Với VBA
Giới Thiệu Về VBA Macros