Excel nhập dữ liệu từ trang web Với đăng nhập

Đối với các ví dụ của chúng tôi, chúng tôi sẽ sử dụng các công cụ Nhận & Chuyển đổi Dữ liệu được tìm thấy trên dải băng Dữ liệu của Excel

Những công cụ này từng được gọi là Phần bổ trợ Power Query. Kể từ Excel 2016, những công cụ này đã trở thành một tính năng tiêu chuẩn dành cho người dùng phiên bản đó và hơn thế nữa. Chúng tôi vẫn gọi các công cụ này là “Power Query” vì nó là thứ mà hầu hết người dùng đã quen dùng… hơn nữa, nó nghe có vẻ hay đấy

Power Query là một tính năng cực kỳ hữu ích cho phép chúng tôi

• kết nối với các nguồn dữ liệu khác nhau, như tệp văn bản, tệp Excel, cơ sở dữ liệu, trang web, v.v…
• chuyển đổi dữ liệu dựa trên các điều kiện tiên quyết của báo cáo
• lưu dữ liệu vào bảng Excel, mô hình dữ liệu hoặc chỉ cần kết nối với dữ liệu để tải sau

Phần tốt nhất là nếu dữ liệu nguồn thay đổi, bạn có thể cập nhật kết quả đích chỉ bằng một cú nhấp chuột;

Điều này tương tự với việc ghi và thực thi macro. Nhưng không giống như macro, việc tạo và thực thi mã back-end diễn ra tự động

Nếu bạn có thể nhấp vào các nút, bạn có thể tạo các giải pháp Power Query tự động

Ví dụ #1Nhập giá giao ngay cho xăng dầu từ trang web sang Excel

Bước đầu tiên là kết nối với nguồn dữ liệu. Đối với ví dụ này, chúng tôi sẽ kết nối với U. S. Quản lý thông tin năng lượng

https. //www. eia. chính phủ/dầu khí

Thông tin chúng tôi cần có trong một bảng là một phần của trang web tổng thể

“Ngày xưa”, chúng ta chuyển thông tin từ trang web sang Excel bằng cách bôi đen bảng trang web và sao chép/dán dữ liệu vào Excel

Nếu bạn đã từng làm điều này, bạn sẽ biết đây có thể là một đề xuất thành công hay thất bại. Đó là Quy tắc 50/50/90. nếu bạn có 50/50 cơ hội chiến thắng, bạn sẽ mất 90% thời gian

Ngay cả khi bạn chuyển thành công thông tin vào Excel, thông tin không được liên kết với trang web. Khi trang web thay đổi, bạn sẽ cần sao chép/dán lại (và có khả năng sửa chữa) thông tin đã cập nhật

1. Bắt đầu bằng cách sao chép URL từ trang web (giả sử bạn đang xem trước trang trong trình duyệt). Nếu không, bạn có thể nhập nó vào dấu nhắc URL của bước tiếp theo
2. Chọn Dữ liệu (tab) -> Nhận & Chuyển đổi (nhóm) -> Từ Web

1. Trong hộp thoại From Web, dán URL vào trường URL và nhấp vào OK

Cửa sổ Điều hướng hiển thị các thành phần của trang web trong bảng điều khiển bên trái

Nếu bạn muốn đảm bảo rằng bạn đang truy cập đúng trang web, hãy nhấp vào tab có nhãn Chế độ xem web để xem trước trang ở định dạng HTML truyền thống

Không chắc rằng các thành phần được liệt kê ở bên trái sẽ được trình bày với các tên rõ ràng như mục nào đi đến thành phần trang web nào. Bạn có thể cần nhấp từ mục này sang mục tiếp theo, xem trước từng mục trong bảng xem trước bên phải để xác định mục nào thuộc về bảng mong muốn

Nếu dữ liệu không cần chuyển đổi gì thêm, bạn có thể nhấp vào Load/Load To… để gửi dữ liệu trực tiếp sang Excel. Điều này sẽ cho phép bạn chọn đích của dữ liệu kết quả, chẳng hạn như bảng trên trang tính mới hoặc trang tính hiện có, Mô hình Dữ liệu hoặc tạo “Chỉ kết nối” cho dữ liệu nguồn

1. Trong hộp thoại Bộ điều hướng, chọn mũi tên bên cạnh Tải và nhấp vào Tải vào…

1. Trong hộp thoại Nhập dữ liệu, hãy chọn “Trang tính hiện có” và trỏ đến một ô trên trang tính đích mong muốn của bạn (như ô A1 trên “Trang tính 1”)

Kết quả là một bảng được kết nối với một truy vấn. Bảng Truy vấn & Kết nối (bên phải) liệt kê tất cả các truy vấn hiện có trong tệp này

Nếu bạn di chuột qua một truy vấn, một cửa sổ thông tin sẽ xuất hiện cung cấp cho bạn thông tin sau

• một bản xem trước của dữ liệu
• số cột đã nhập
• ngày/thời gian làm mới cuối cùng
• cách dữ liệu được tải hoặc kết nối với tệp Excel
• vị trí của nguồn dữ liệu

Mặc dù dữ liệu có vẻ chính xác nhưng chúng tôi có một số vấn đề về cấu trúc với kết quả sẽ gây ra sự cố khi phân tích thêm

Các ô trống trong cột Sản phẩm sẽ gây ra sự cố khi sắp xếp, lọc, lập biểu đồ hoặc xoay vòng dữ liệu

Chúng tôi cần thực hiện một số điều chỉnh đối với dữ liệu

1. Bấm đúp (hoặc bấm chuột phải và chọn Chỉnh sửa) truy vấn được liệt kê để kích hoạt Power Query Editor

Chúng tôi muốn điền các sản phẩm được liệt kê vào các ô trống phía dưới của cột Sản phẩm

1. Trong Power Query Editor, chọn cột Sản phẩm và bấm Chuyển đổi (tab) -> Cột bất kỳ (nhóm) -> Điền -> Xuống

Không có chuyện gì xảy ra

Lý do tên sản phẩm không thể lặp lại qua các ô trống là do Power Query không hiểu các ô là trống. Có thể có một số phần mềm từ trang web tồn tại trong ô mà chúng tôi không thể nhìn thấy

Chúng tôi sẽ thay thế tất cả các ô “trống giả” bằng các giá trị null. Điều này sẽ cho phép thao tác Điền xuống hoạt động như mong đợi

1. Chọn bước Loại đã thay đổi trong bảng Cài đặt truy vấn (phải)

1. Chọn cột Sản phẩm và nhấp vào Chuyển đổi (tab) -> Cột bất kỳ (nhóm) -> Thay thế giá trị

1. Cho Power Query biết rằng bạn muốn chèn bước mới này vào truy vấn hiện có bằng cách nhấp vào Chèn

1. Trong hộp thoại Thay thế giá trị, để trống trường “Giá trị cần tìm” và nhập “null” (không có dấu ngoặc kép) trong trường “Thay thế bằng”. Nhấn OK khi hoàn tất

Nếu bạn chọn bước “Điền xuống” đã tạo trước đó ở cuối bảng Cài đặt truy vấn, bạn sẽ thấy kết quả cập nhật của truy vấn

1. Cập nhật tên truy vấn thành “Giá giao ngay”

1. Nhấp vào phần trên cùng của nút Đóng và Tải ở phía bên trái của Trang chủ

Nếu chúng tôi vẽ biểu đồ dữ liệu và dữ liệu thay đổi, chúng tôi có thể làm mới biểu đồ của mình bằng cách nhấp vào Dữ liệu (tab) -> Truy vấn & Kết nối (nhóm) -> Làm mới tất cả hoặc nhấp chuột phải vào dữ liệu và chọn Làm mới

Tùy chọn truy vấn

Có một số tùy chọn có thể kiểm soát được bằng cách chọn Dữ liệu (tab) -> Truy vấn & Kết nối (nhóm) -> Làm mới tất cả -> Thuộc tính kết nối…

Một số tùy chọn phổ biến hơn bao gồm

• Làm mới dữ liệu sau mỗi N số phút
• Làm mới dữ liệu khi mở tệp
• Chọn tham gia trong quá trình làm mới tất cả

Ví dụ #2Nhập Dự báo thời tiết trong 10 ngày tới

Hãy tưởng tượng bạn làm việc tại quầy lễ tân của một khách sạn nổi tiếng ở thành phố New York. Là một dịch vụ khách hàng, bạn muốn cung cấp cho khách của mình bản in dự báo thời tiết trong 10 ngày tới

Đây là thứ cần được in hàng ngày trong đó mỗi ngày liên tiếp nhìn vào 10 ngày tiếp theo của nó

1. Chúng tôi bắt đầu bằng cách tìm kiếm một trang web có thể cung cấp dự báo 10 ngày cho Seattle

1. Chúng tôi nhận ưu đãi đầu tiên trong kết quả tìm kiếm đưa chúng tôi đến thời tiết. com

1. Bây giờ, chúng ta có liên kết web đến dự báo 10 ngày cho Thành phố New York, chúng ta sẽ sao chép và dán liên kết đó vào truy vấn web trong Excel (Dữ liệu (tab) -> Nhận & Chuyển đổi (nhóm) -> Từ Web)
2. Chọn bảng từ phía bên trái của cửa sổ Điều hướng

Chúng ta có thể thấy từ bản xem trước rằng có một số cột ở bên phải mà chúng ta không quan tâm và các tiêu đề cột đã thay đổi

1. Trong cửa sổ Bộ điều hướng, hãy chọn Chuyển đổi dữ liệu để tải dự báo vào Power Query
2. Bắt đầu chỉnh sửa dữ liệu bằng cách nhấp chuột phải vào tiêu đề cho cột “Ngày” và chọn Xóa

1. Chọn 3 cột cuối cùng (“Gió”, “Độ ẩm” và “Cột7”) và xóa chúng đi

1. Đổi tên 3 cột còn lại
   • Mô tả -> Ngày
   • Cao / Thấp -> Mô tả
   • Lượng mưa -> Cao/Thấp

1. Đổi tên truy vấn “SeattleWeather”
2. Trên tab Trang chủ, chọn phần dưới của nút Đóng & Tải, rồi nhấp vào Đóng & Tải vào… và chọn Trang tính Hiện có từ hộp thoại Nhập Dữ liệu. Nhấn OK khi hoàn thành

Bây giờ chúng tôi có dự báo thời tiết trong 10 ngày tới

Mỗi ngày, chúng ta chỉ cần nhấp chuột phải vào bảng để cập nhật thông tin

Gây ấn tượng với sếp

Chúng tôi thực sự thích ý tưởng nhìn thấy trên thời tiết ban đầu. trang web com hiển thị biểu tượng cảm xúc đám mây mưa cho những ngày sắp có mưa

Để mang lại một chút thú vị cho báo cáo của chúng tôi, chúng tôi sẽ có Excel hiển thị biểu tượng cảm xúc ô cho bất kỳ ngày nào dự báo mưa hoặc mưa rào

GHI CHÚ. Thủ thuật Excel sáng tạo này do những người bạn tốt của chúng tôi là Frédéric Le Guen và Oz du Soleil mang đến cho chúng tôi. Bạn có thể tìm thấy các liên kết đến blog và video chi tiết về các cách sử dụng khác nhau của thủ thuật này ở cuối bài đăng này

Bước đầu tiên là thêm biểu tượng cảm xúc ô. Chúng tôi sẽ truy cập thư viện biểu tượng cảm xúc Windows tích hợp

1. Trên bàn phím, nhấn phím Windows và dấu chấm để hiển thị thư viện biểu tượng cảm xúc

1. Nhập từ “mưa” để lọc thư viện biểu tượng cảm xúc thành các biểu tượng cảm xúc liên quan đến mưa
2. Chọn ô có hạt mưa

1. Đánh dấu biểu tượng cảm xúc ô trong Thanh công thức và nhấn Sao chép (CTRL-C) rồi Enter
2. Bấm đúp vào truy vấn “SeattleWeather” để khởi chạy Power Query Editor

Bước tiếp theo là tạo một cột mới có thêm biểu tượng cảm xúc ô cho bất kỳ hàng nào có chứa từ “mưa” hoặc “vòi hoa sen” trong cột Mô tả

Làm cách nào để trích xuất dữ liệu từ trang web được bảo vệ bằng mật khẩu sang Excel?

Excel có thể tự động lấy dữ liệu từ một trang web không?