Trích xuất dữ liệu từ web trong Excel: Power Query, VBA và các phương pháp API

let
    Endpoint = "https://api.scrapeless.com",
    TargetUrl = Excel.CurrentWorkbook(){[Name="AuthorizedTargetUrl"]}[Content]{0}[Column1],
    Payload = Json.FromValue([
        actor = "unlocker.webunlocker",
        proxy = [country = "ANY"],
        input = [
            url = TargetUrl,
            jsRender = [
                enabled = true,
                response = [type = "html", options = []]
            ]
        ]
    ]),
    Raw = Web.Contents(
        Endpoint,
        [
            RelativePath = "api/v2/unlocker/request",
            Headers = [
                #"Content-Type" = "application/json"
            ],
            Content = Payload,
            ApiKeyName = "x-api-token"
        ]
    ),
    Envelope = Json.Document(Raw),
    Checked = if Envelope[code] = 200 and Envelope[data] <> null
        then Envelope[data]
        else error "The acquisition response did not contain page data",
    Output = #table(
        {"source_url", "html"},
        {{TargetUrl, Checked}}
    )
in
    Output

Option Explicit

Public Sub ImportCsv()
    Dim endpoint As String
    Dim target As Worksheet
    Dim query As QueryTable

    endpoint = ThisWorkbook.Names("CsvEndpoint").RefersToRange.Value
    Set target = ThisWorkbook.Worksheets("ImportedData")

    target.Cells.ClearContents

    Set query = target.QueryTables.Add( _
        Connection:="TEXT;" & endpoint, _
        Destination:=target.Range("A1"))

    With query
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFilePlatform = 65001
        .RefreshStyle = xlOverwriteCells
        .Refresh BackgroundQuery:=False
    End With
End Sub

2. **Chuyển đổi và phân tích trong Excel:** định hình phản hồi thành các cột, kết hợp các bảng tra cứu, tạo bảng tổng hợp và công bố biểu đồ.

[API Scraping Universal](https://www.scrapeless.com/vi/product/universal-scraping-api?utm_source=website&utm_medium=blog&utm_campaign=universalscrapingapi&utm_term=web-scraping-in-excel) được xây dựng cho giai đoạn đầu tiên. Power Query vẫn là kết nối giao diện cho bảng tính. Ranh giới này cho phép các nhóm thay đổi phương pháp thu thập mà không cần xây dựng lại mọi công thức và biểu đồ.

Sử dụng [trang giá Scrapeless](https://www.scrapeless.com/vi/pricing?utm_source=website&utm_medium=blog&utm_campaign=universalscrapingapi&utm_term=web-scraping-in-excel) để so sánh chi phí dịch vụ với thời gian kỹ thuật cần thiết để hoạt động hạ tầng trình duyệt. Để có cái nhìn tổng quát hơn về các định dạng phản hồi phù hợp với quy trình Excel, hãy xem [cập nhật định dạng phản hồi API Scraping Universal](https://www.scrapeless.com/vi/blog/response-formats-update?utm_source=website&utm_medium=blog&utm_campaign=universalscrapingapi&utm_term=web-scraping-in-excel).

## Các vấn đề và cách khắc phục phổ biến

### Trình duyệt không hiển thị bảng

Trang có thể không tiết lộ bảng HTML ổn định trong phản hồi ban đầu. Tìm nguồn JSON hoặc CSV được phép. Nếu giá trị chỉ xuất hiện sau JavaScript, sử dụng một lớp thu thập trả về nội dung hoàn chỉnh.

### Làm mới trả về lỗi cấp độ riêng tư

Power Query cách ly các nguồn dữ liệu theo cài đặt riêng tư. Xem xét cấu hình nguồn của bảng tính và tránh kết hợp dữ liệu tổ chức riêng tư với nguồn công khai trừ khi chính sách cho phép.

### Một truy vấn hoạt động trên một máy tính nhưng không hoạt động trên máy tính khác

Kiểm tra phiên bản Excel, cài đặt xác thực, vùng tên, chính sách macro và yêu cầu cổng. Thông tin xác thực là cụ thể cho môi trường và không nên di chuyển bên trong tệp bảng tính.

### Bảng tính tải một trang thách thức

Thêm xác thực cấp nội dung trước khi chuyển đổi. yêu cầu trường JSON, tiêu đề CSV hoặc dấu hiệu trang mong đợi. Từ chối bất kỳ phản hồi nào không đáp ứng hợp đồng đó.

### Các loại cột thay đổi sau khi làm mới

Áp dụng các loại dữ liệu rõ ràng trong Power Query sau bước nguồn. Giữ lại các ký hiệu tiền tệ, phân tách theo vùng và giá trị thiếu ra khỏi các cột số.

### Làm mới mất quá nhiều thời gian

Giảm dữ liệu trước khi nó đến Excel. Lọc theo ngày hoặc định danh tại nguồn, yêu cầu chỉ những trường cần thiết và tải các truy vấn trung gian chỉ dưới dạng kết nối khi bảng tính không cần chúng.

## Từ prototype bảng tính đến nguồn sản xuất

Một quá trình hữu ích là:

1. Chứng minh một URL và một hàng thủ công.
2. Tạo một Power Query với các tên và loại cột rõ ràng.
3. Thêm xác thực nội dung và một bảng lỗi.
4. Chuyển rendering và phân tích HTML vào dịch vụ thu thập.
5. Công bố JSON hoặc CSV với một lược đồ phiên bản.
6. Lên lịch thu thập bên ngoài Excel và giữ lại làm mới bảng tính tập trung vào phân tích.

Điều này giữ cho bảng tính hữu ích mà không biến nó thành một trình duyệt web tự động. Bảng tính trở thành người tiêu thụ dữ liệu sạch, một vai trò mà Excel đảm nhận tốt.

## Kết luận: để Excel phân tích, không bắt chước một trình duyệt

Power Query là lựa chọn mặc định cho các bảng HTML có thể làm mới và các API JSON. VBA là rất quý giá cho các nhập khẩu do người dùng kích hoạt có kiểm soát. Khi một nguồn được phép phụ thuộc vào JavaScript hoặc xác thực lưu lượng, sử dụng một API thu thập và cung cấp cho Excel một hợp đồng dữ liệu ổn định.

Để xây dựng ranh giới đó, [tạo một tài khoản Scrapeless](https://app.scrapeless.com/passport/login?utm_source=website&utm_medium=blog&utm_campaign=universalscrapingapi&utm_term=web-scraping-in-excel), thử nghiệm API Scraping Universal với một URL công khai, xác định các trường mà bảng tính cần, và làm cho những trường đó trở thành hợp đồng làm mới.

## Các Câu hỏi thường gặp

### Excel có thể scraping một trang web mà không cần mã không?

Có. Kết nối From Web của Power Query có thể nhập nhiều bảng HTML thấy được thông qua một quy trình đồ họa. Nó phù hợp nhất với các trang công khai ổn định mà hiển thị bảng trong HTML ban đầu của chúng.

### Tại sao Power Query hiển thị một trang trống?

Các giá trị có thể được render bằng JavaScript sau phản hồi ban đầu, tải từ một điểm cuối riêng biệt, hoặc bị thay thế bởi một trang xác thực lưu lượng. Kiểm tra hành vi nguồn và chọn phương pháp thu thập phù hợp.

### Power Query có tốt hơn VBA cho việc scraping web không?

Power Query thường tốt hơn cho việc chuyển đổi dữ liệu có thể làm mới và quản lý thông tin xác thực. VBA hữu ích cho các nút trên bảng tính và việc nhập tệp có kiểm soát, nhưng đó là một nơi dễ bị hỏng để duy trì phân tích HTML.

### Power Query có thể gọi một API JSON không?

Có. `Web.Contents` có thể lấy một phản hồi, và `Json.Document` có thể phân tích nó thành các bản ghi, danh sách, và bảng. Sử dụng hộp thoại thông tin xác thực thay vì lưu trữ bí mật API trong mã M.

### Bảng tính có thể xử lý các trang được render bằng JavaScript như thế nào?

Sử dụng một dịch vụ thu thập có khả năng render để trả về HTML, JSON hoặc CSV, sau đó kết nối Power Query với đầu ra đó. Điều này giữ cho hành vi của trình duyệt bên ngoài bảng tính.

### Bao lâu thì Excel nên làm mới dữ liệu đã scraping?
Khớp tần suất làm mới với nhu cầu kinh doanh, nguồn quyền, và khả năng dịch vụ. Đối với việc thu thập theo lịch trình hoặc khối lượng lớn, hãy thực hiện việc thu thập bên ngoài Excel và để workbook làm mới từ một tập dữ liệu đã chuẩn bị.