Cách sử dụng hàm WEBSERVICE và FILTERXML để lấy dữ liệu trực tiếp từ web và tự động cập nhật trong Excel

Microsoft Excel rất tuyệt vời để sắp xếp dữ liệu. Nhưng nếu bạn cần thông tin thay đổi liên tục - chẳng hạn như giá cổ phiếu, cập nhật thời tiết hoặc tỷ giá tiền điện tử, bạn không cần phải sao chép số liệu từ các trang web mỗi khi chúng thay đổi.

May mắn thay, Excel có 2 hàm tích hợp sẵn, WEBSERVICE và FILTERXML, có thể lấy dữ liệu trực tiếp từ web và tự động cập nhật. Hai hàm này nằm trong danh sách các hàm Excel ít được chú ý, có thể giúp bạn tiết kiệm đáng kể thời gian. Vì vậy, bạn không cần bất kỳ add-in hay đăng ký đặc biệt nào - chỉ cần hai hàm này và hiểu biết cơ bản về cách các trang web cấu trúc dữ liệu của chúng.

Cách lấy dữ liệu thô từ một trang web bằng hàm WEBSERVICE

Cú pháp rất đơn giản nhưng kết quả lại thô

Hàm WEBSERVICE là công cụ tích hợp sẵn của Excel để lấy dữ liệu trực tiếp từ Internet. Hàm này gửi một yêu cầu đến một địa chỉ web (URL) và trả về bất kỳ thông tin nào mà trang đó chứa. Hàm trả về dữ liệu dưới dạng văn bản, thường ở định dạng XML, nhưng cũng có thể là JSON hoặc plain text. Tuy nhiên, FILTERXML, được sử dụng với WEBSERVICE, chỉ hoạt động với XML, do đó dữ liệu JSON không thể được phân tích cú pháp trực tiếp mà không cần chuyển đổi.

Hàm này sử dụng cú pháp sau:

=WEBSERVICE(url)

WEBSERVICE chỉ có một tham số - URL. Địa chỉ web đầy đủ mà bạn muốn lấy dữ liệu từ đó. Đây phải là một URL hợp lệ trả về dữ liệu, chứ không chỉ là một trang web thông thường. Hầu hết các API (Application Programming Interface) đều cung cấp những URL cụ thể để cung cấp dữ liệu ở các định dạng có cấu trúc như XML hoặc JSON. URL phải được đặt trong dấu ngoặc kép, hoặc bạn có thể tham chiếu đến một ô Excel có chứa URL.

Đây là một ví dụ đơn giản. Nếu muốn lấy dữ liệu XML từ một API cung cấp tỷ giá hối đoái, bạn có thể sử dụng cú pháp như sau:

=WEBSERVICE("https://api.example.com/exchange-rates")

Hàm này trả về tất cả những gì máy chủ gửi lại, thường là một chuỗi văn bản dài kèm theo tag, dấu ngoặc vuông hoặc các định dạng khác. Chuỗi văn bản này thô và chưa được lọc, đó là lý do tại sao bạn sẽ cần FILTERXML để hiểu được nó sau này.

Cách sử dụng hàm FILTERXML để làm cho dữ liệu web có thể sử dụng được

Truy vấn XPath cho phép bạn chọn chính xác những gì mình muốn

Đầu ra thô từ WEBSERVICE thường là một mớ hỗn độn các tag và văn bản mà Excel không thể tự diễn giải. Đó là lúc FILTERXML phát huy tác dụng. Nó trích xuất các phần thông tin cụ thể từ dữ liệu XML bằng những truy vấn XPath.

Cú pháp trông như sau:

=FILTERXML(xml, xpath)

Sau đây là chức năng của từng tham số:

• xml: Dữ liệu XML bạn muốn lọc. Đây thường là đầu ra từ hàm WEBSERVICE, nhưng cũng có thể là một tham chiếu ô chứa văn bản XML hoặc một chuỗi XML được nhập thủ công đặt trong dấu ngoặc kép.
• xpath: Một truy vấn XPath cho Excel biết chính xác phần nào của cấu trúc XML cần trích xuất. XPath sử dụng cú pháp dạng đường dẫn (tương tự như thư mục file) để điều hướng qua các tag và thuộc tính XML. Ví dụ: //price sẽ trích xuất tất cả các phần tử có tag "price" từ dữ liệu XML.

Giả sử hàm WEBSERVICE trong ô A1 trả về một file XML và bạn chỉ cần trích xuất tỷ giá hối đoái USD, hãy sử dụng:

=FILTERXML(A1, "//currency[@name='USD']")

Công thức này yêu cầu Excel tìm kiếm bên trong XML trong ô A1, tìm phần tử "currency" có thuộc tính "name" bằng "USD" và trả về giá trị của nó.

Nếu bạn muốn trích xuất nhiều giá trị cùng lúc, FILTERXML sẽ trả về chúng dưới dạng một mảng. Sử dụng //currency thay thế sẽ trích xuất tất cả tỷ giá hối đoái (nếu có trong XML), bạn có thể sử dụng chúng trên nhiều ô nếu đang sử dụng Excel 365 hoặc 2021.

Phần khó khăn là viết truy vấn XPath chính xác. Dữ liệu XML có cấu trúc phân cấp - tag cha chứa tag con - và XPath của bạn cần tuân theo cấu trúc đó. Nếu dữ liệu có namespaces (tiền tố như ns:currency), bạn cũng cần tính đến những namespaces này, mặc dù hầu hết các API đơn giản đều không sử dụng chúng.