Hướng dẫn get data from web to excel - lấy dữ liệu từ web sang excel

Dễ dàng nhập dữ liệu từ web sang excel

[2 ví dụ thực tế]

Bài đăng này sẽ trình bày cách nhập dữ liệu từ web vào Excel. & NBSP; Có nhiều kỹ thuật có sẵn cho nhiệm vụ này; Một số tốt hơn những người khác, tùy thuộc vào tính chất động/thay đổi của dữ liệu web.

Đố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ụ dữ liệu GET & Biến đổi được tìm thấy trên ruy băng dữ liệu của Excel.Get & Transform Data tools found on the Data ribbon of Excel.

Các công cụ này được gọi là bổ trợ truy vấn nguồn. & NBSP; Kể từ Excel 2016, các công cụ này đã trở thành một tính năng tiêu chuẩn có sẵn cho người dùng phiên bản đó và hơn thế nữa. & NBSP; Chúng tôi vẫn đề cập đến các công cụ là truy vấn sức mạnh của người Hồi giáo vì nó là những gì mà hầu hết người dùng đã quen với cộng với cộng với, nghe có vẻ rất tuyệt.Power Query Add-In.  Since Excel 2016, these tools have become a standard feature available to users of that version and beyond.  We still refer to the tools as “Power Query” since it’s what most users are used to… plus, it sounds cool.

Truy vấn sức mạnh 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 báo cáo.
• Lưu dữ liệu vào bảng Excel, mô hình dữ liệu hoặc đơn giản là 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; Một cái gì đó là lý tưởng cho dữ liệu thay đổi thường xuyên.

Điều này tương tự như ghi và thực hiện một macro. & Nbsp; Nhưng không giống như một macro, việc tạo và thực hiện mã back-end sẽ tự động xảy ra.

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 truy vấn năng lượng tự động.

Ví dụ 1

Nhập giá tại chỗ cho dầu mỏ

Từ một trang web đến Excel

Bước đầu tiên là kết nối với nguồn dữ liệu. & NBSP; Ví dụ này, chúng tôi sẽ kết nối với Cơ quan Thông tin Năng lượng Hoa Kỳ.

//www.eia.gov/petroleum

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

Trong những ngày cũ, chúng tôi sẽ chuyển thông tin từ trang web sang Excel bằng cách làm nổi bật 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 một đề xuất hay bỏ lỡ nào có thể là gì. & NBSP; Nó có quy tắc 50/50/90: Nếu bạn có cơ hội chiến thắng 50/50, bạn sẽ mất 90% thời gian.50/50/90 Rule: if you have a 50/50 chance of winning, you’ll lose 90% of the time.

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. & NBSP; Khi trang web thay đổi, bạn sẽ cần tái hiện/dán [và có khả năng sửa] thông tin được 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 bước tiếp theo.
2. Chọn Dữ liệu [Tab] -> Get & Transform [Nhóm] -> Từ Web.Data [tab] -> Get & Transform [group] -> From Web.

1. Trong hộp thoại From Web, dán URL vào trường URL và nhấp vào OK.From Web dialog box, paste the URL into the URL field and click 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.Navigator window displays the components of the webpage in the left panel.

Nếu bạn muốn đảm bảo bạn đang ở trên trang web chính xác, nhấp vào Tab được dán nhãn Chế độ xem web để có được bản xem trước của trang ở định dạng HTML truyền thống.Web View to get a preview of the page in a traditional HTML format.

Không có khả nă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 về 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 cái nào thuộc về bảng mong muốn.

Nếu dữ liệu không yêu cầu bất kỳ phép biến đổi nào nữa, bạn có thể nhấp vào Tải/Tải đến để gửi dữ liệu trực tiếp đến Excel. & NBSP; Đ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 bảng tính mới hoặc hiện có, mô hình dữ liệu hoặc tạo kết nối chỉ có một kết nối với dữ liệu nguồn.Load/Load To… to send the data directly to Excel.  This will allow you to select the destination of the results data, such as a table on a new or existing worksheet, the Data Model, or create a “Connection Only” to the source data.

1. Trong hộp thoại Điều hướng, chọn mũi tên bên cạnh tải và nhấp vào Tải vàoNavigator dialog box, select the arrow next to Load and click Load To…

1. Trong hộp thoại Nhập dữ liệu, chọn bảng tính hiện tại của Cameron và trỏ đến một ô trên bảng điểm đích mong muốn của bạn [như ô A1 trên trên tờ Sheet Sheet1].Import Data dialog box, select “Existing worksheet” and point to a cell on your desired destination worksheet [like cell A1 on “Sheet1”].

Kết quả là một bảng được kết nối với truy vấn. & NBSP; Bảng truy vấn & kết nối [phải] liệt kê tất cả các truy vấn hiện có trong tệp này.Queries & Connections panel [right] lists all existing queries in this file.

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:

• Xem trước dữ liệu
• số lượng cột nhập khẩu
• Ngày/giờ 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 dữ liệu nguồn

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

Các ô trống trong cột sản phẩm sẽ gây ra vấn đề khi sắp xếp, lọc, biểu đồ hoặc xoay dữ liệu.Product column will cause problems when sorting, filtering, charting, or pivoting the data.

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

1. Nhấp đúp vào [hoặc nhấp chuột phải và chọn Chỉnh sửa] Truy vấn được liệt kê để kích hoạt Trình chỉnh sửa truy vấn Power.Edit] the listed query to activate the Power Query Editor.

Chúng tôi muốn lấp đầy các sản phẩm được liệt kê vào các ô trống, thấp hơn của cột sản phẩm.Product column.

1. Trong trình chỉnh sửa Truy vấn Power, chọn cột sản phẩm và nhấp vào Transform [tab] -> bất kỳ cột nào [nhóm] -> Fill -> Down.Product column and click Transform [tab] -> Any Column [group] -> Fill -> Down.

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

Lý do tên sản phẩm không lặp lại thông qua các ô trống là truy vấn nguồn không diễn giải các ô trống. & NBSP; Có thể có một số tạo tác từ trang web tồn tại trong ô mà chúng ta có thể nhìn thấy.

Chúng tôi sẽ thay thế tất cả các ô trống rỗng giả bằng các giá trị null. & NBSP; Điều này sẽ cho phép hoạt động lấp đầy hoạt động như mong đợi.Fill Down operation to work as expected.

1. Chọn bước loại đã thay đổi trong bảng Cài đặt truy vấn [phải].Changed Type step in the Query Settings panel [right].

1. Chọn cột sản phẩm và nhấp vào Transform [Tab] -> Bất kỳ cột [Nhóm] -> Thay thế các giá trị.Product column and click Transform [tab] -> Any Column [group] -> Replace Values.

1. Nói với Power Truy vấn 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.Insert.

1. Trong hộp thoại thay thế giá trị, hãy để lại giá trị của người dùng để tìm trường trống và gõ vào NULL, không có dấu ngoặc kép] trong trường Thay thế với trường. Bấm OK khi hoàn thành.Replace Values dialog box, leave the “Value To Find” field empty and type “null” [no quotes] in the “Replace With” field. Click OK when finished.

Nếu bạn chọn bước đã được tạo trước đó, bước xuống trên cùng của 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 lên giá giao ngay.

1. Nhấp vào phần trên cùng của nút Đóng & Tải ở phía bên trái của ngôi nhàClose & Load button at the far-left of the Home

Nếu chúng tôi vẽ đồ thị dữ liệu và dữ liệu sẽ 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.Data [tab] -> Queries & Connections [group] -> Refresh All or right-click on the data and select Refresh.

Tùy chọn truy vấn

Có một số tùy chọn có thể kiểm soát có sẵn 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ốiData [tab] -> Queries & Connections [group] -> Refresh All -> Connection Properties…

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

• Làm mới dữ liệu mỗi n số phút
• Làm mới dữ liệu khi mở tệp
• Lựa chọn tham gia trong khi làm mới tất cả các hoạt động

Ví dụ #2

Nhập dự báo thời tiết

trong 10 ngày tiếp theo

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. & NBSP; Là một dịch vụ khách hàng, bạn muốn cung cấp cho khách của mình một bản in dự báo thời tiết trong 10 ngày tới.

Đây là một cái gì đó cần được in mỗi ngày, nơi mỗi ngày liên tiếp nhìn vào 10 ngày tới.

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 Weather.com.

1. Bây giờ chúng tôi có liên kết web đến dự báo 10 ngày cho Thành phố New York, chúng tôi sẽ sao chép và dán nó vào truy vấn web trong Excel [Data [Tab] -> Get & Transform [Nhóm] -> từ Web].Data [tab] -> Get & Transform [group] -> From 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 về phía 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ổ Navigator, chọn Chuyển đổi dữ liệu để tải dự báo vào truy vấn nguồn.Transform Data to load the forecast into 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 Day Day và chọn Xóa.Remove.

1. Chọn 3 cột cuối cùng [Gió Gió, Độ ẩm và Cột Cột7] và cũng loại bỏ chúng.

1. Đổi tên 3 cột còn lại.
   • Mô tả -> Ngày
   • Cao / Thấp -> Mô tả
   • Kết tủa -> cao/thấp

1. Đổi tên truy vấn của Seattle Seattleweather.
2. Trên tab Trang chủ, chọn phần dưới của nút Đóng & Tải và nhấp vào Đóng & Tải vào LỚN và chọn bảng tính hiện có từ hộp thoại Nhập dữ liệu. Bấm OK khi hoàn thành.Home tab, select the lower-part of the Close & Load button and click Close & Load To… and select Existing Worksheet from the Import Data dialog box. Click OK when complete.

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 tôi chỉ cần nhấp chuột phải vào bảng để làm mới thông tin.

Gây ấn tượng với ông chủ

Chúng tôi thực sự thích ý tưởng được thấy trên trang web ban đầu của Weather.com hiển thị biểu tượng cảm xúc mưa trong nhiều ngày đang mong đợi mưa.

Để mang lại một chút niềm vui cho báo cáo của chúng tôi, chúng tôi sẽ có màn hình 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.

LƯU Ý: Một chút mánh khóe sáng tạo này được mang đến cho chúng tôi bởi những người bạn tốt của chúng tôi Frédéric Le Guen và Oz du Soleil. & NBSP; Liên kết đến blog và chi tiết video của họ, nhiều ứng dụng khác nhau của thủ thuật này có thể được tìm thấy ở cuối bài này.Frédéric Le Guen and Oz du Soleil.  Links to their blog and video detail various uses of this trick can be found at the end of this post.

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

1. Trên bàn phím, nhấn phím Windows và khoảng thời gian để hiển thị thư viện biểu tượng cảm xúc.Windows key and the period to display the emoji library.

1. Nhập từ Rain Rain Rain để lọc thư viện biểu tượng cảm xúc đến biểu tượng cảm xúc liên quan đến mưa.
2. Chọn ô với những hạt mưa.umbrella with raindrops.

1. Làm nổi bật biểu tượng cảm xúc ô trong thanh công thức và nhấn bản sao [CTRL-C] sau đó nhập.Formula Bar and press Copy [CTRL-C] then Enter.
2. Nhấp đúp vào truy vấn của Seattle Seattleweather để khởi chạy trình soạn thảo Power Truy vấn.Power Query Editor.

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

1. Chọn Thêm cột [tab] -> Chung [Nhóm] -> Cột có điều kiện.Add Column [tab] -> General [group] -> Conditional Column.

1. Tên của cột mới là được trang bị, và logic là nếu cột có tên ‘Mô tả, chứa từ‘ Rain, sau đó hiển thị biểu tượng cảm xúc ô. [Dán biểu tượng cảm xúc ô vào đầu ra]Paste the umbrella emoji into the Output]
2. Nhấp vào nút Thêm mệnh đề để tạo điều kiện thứ hai.Add Clause button to create a second condition.
3. Mô tả thứ hai logic logic là khác nếu cột có tên ‘Mô tả, chứa từ‘ vòi hoa sen, sau đó hiển thị biểu tượng cảm xúc ô. [Dán biểu tượng cảm xúc ô vào đầu ra]Paste the umbrella emoji into the Output]

1. Nhấn OK để thêm cột có điều kiện mới.OK to add the new conditional column.

1. Kéo tiêu đề cho các trò chơi được trang bị, vì vậy, cột mới nằm giữa các cột mô tả trên mạng và các cột cao/thấp.

1. Đóng và tải truy vấn được cập nhật trở lại Excel. the updated query back into Excel.

Ngày mai, bạn chỉ cần nhấp chuột phải vào bảng để tải dự báo thời tiết 10 ngày được cập nhật.

Liên kết thú vị

Thực hành sổ làm việc

Hãy tải xuống sổ làm việc ở đây.

Học Excel từ Microsoft MVP

Kiểm tra các khóa học Excel bán chạy nhất của tôi

Tìm hiểu bất cứ lúc nào phù hợp với lịch trình của bạn.

Tải tập tin. Thực tiễn. Ứng dụng.

3 bình luận

1. David Cerac ngày 17 tháng 12 năm 2019 lúc 12:42 sáng December 17, 2019 at 12:42 am

   پاس Tiếng

   بار

   Nhưng, phần này của đào tạo ngu ngốc.👉
   Dumb.👉

   Trên bàn phím, nhấn phím Windows Windows và thời gian trên mạng để hiển thị thư viện biểu tượng cảm xúc. Điều này quá tệ
   This is too Bad

   Bạn tốt hơn là chỉ vào các nút bàn phím. Tuy nhiên, đơn đặt hàng của bạn không rõ ràng. Nhưng đừng quên bạn trong một thế giới giáo dục. Điều này có nghĩa là tất cả mọi người hiện diện với từng cấp độ kiến ​​thức.

   Các nút để hiển thị Windows Emoji là 👉 Windows Windows +

   • Chris ngày 11 tháng 1 năm 2020 lúc 4:06 chiều January 11, 2020 at 4:06 pm

     Cảm ơn phản hôi của bạn.

2. Emad ngày 20 tháng 12 năm 2019 lúc 12:32 sáng December 20, 2019 at 12:32 am

   Bạn cho chúng tôi những cách tốt để trở nên tốt hơn và lớn lên cảm ơn bạn rất nhiều Chúa phù hộ cho bạn

Nhận xét đã đóng cửa.