POWER QUERY – CẬP NHẬT DỮ LIỆU Học Excel Online


Chúng ta đã được giới thiệu qua về Power Query ở các bài viết trước, nơi ta biết cách chuẩn hoá dữ liệu từ những file định dạng khác nhau và đưa chúng vào Excel. Trong bài viết lần này, Siêu Marketing sẽ chỉ dẫn bạn cách thức làm mới dữ liệu, đồng thời những chỉ dẫn này áp dụng được cho việc truy vấn đơn lẻ hoặc liên tục.

Cập nhật toàn bộ dữ liệu

Excel có những công cụ tính toán tự động thực hiện lại mỗi khi có sự thay đổi. Tuy nhiên, Power Query lại không hoàn toàn như vậy, nó yêu cầu lệnh rõ ràng mới tiến hành tính toán lại.

Để làm mới dữ liệu, bạn chỉ cần chọn Data → Refresh all.

Chúng tôi sẽ sử dụng Example 6 – Data Refresh 1.csvData Refresh 2.csv từ tài liệu đã tải để minh hoạ chi tiết hơn.

Kiến lập truy vấn cơ bản

Bắt đầu từ cửa sổ làm việc để tạo truy vấn mới từ file CSV: trên thanh công cụ chọn Data–> Get Data–> From File –> From text/CSV.

Màn hình Import data sẽ xuất hiện. Chọn file Example – Data Refresh 1.csv và tiến hành nhập vào với Import.

Sau khi Power Query mở ra cửa sổ mới với dữ liệu mẫu, bạn hãy chọn Transform Data.

Bạn cần thực hiện một số thao tác cơ bản để biến dữ liệu CSV thành thông tin hữu ích hơn. Bài này sẽ giới thiệu sơ qua một vài bước đổi mới, và sẽ được giải thích kỹ hơn ở những phần sau.

Lựa chọn cột Date và trên thanh công cụ vào Transform –> Date–>Month –>End of month để đổi cột Date thành ngày cuối cùng của tháng.

Kích vào tiêu đề cột Product, giữ phím Shift đồng thời kích tiêu đề cột Sold By. Với 2 cột này đã chọn, chọn lệnh Home –> Remove Columns để xoá bỏ.

Sau đó, hãy biến đổi dữ liệu ở cột Date. Chọn cột này và nhấn vào Transform –> Pivot Column trong thanh công cụ.

Bạn sẽ thấy cửa sổ Pivot Column hiện lên. Đổi cột Values Column sang Value. Chọn tùy chọn nâng cao và đặt Aggregate Value FunctionSum và chọn OK.

Chọn tuỳ chọn Home–> Close&Load. Excel sẽ tạo bảng mới và bảng đó chứa dữ liệu đã được chuyển đổi từ CSV, giống hình dưới đây:

Thay đổi dữ liệu nguồn

Để tiến hành việc cập nhật, chúng ta giả định rằng người dùng sẽ nhận file mới hàng ngày, hàng tuần hoặc hàng tháng. Để thực hiện, ta sẽ đổi tên hai file mẫu.

  • Example -Data Refresh 1.csv được đổi thành bất kỳ tên nào khác.
  • Example 6 – Data Refresh 2.csv đổi thành Example 6 – Data Refresh 1.csv.

Power Query sẽ tiếp tục chỉ đến file có tên là Example 6 – Data Refresh 1.csv với dữ liệu cập nhật mới.

Làm mới lại dữ liệu

Chọn nút Data –>Refresh all.

Excel sẽ xử lý dữ liệu qua Power Query, áp dụng chuyển đổi theo phương pháp cũ và sau đó cập nhật chúng vào bảng tính. Dữ liệu mới sẽ hiện diện ngay trên bảng tính một cách tự động.

Dữ liệu của tháng 3 sẽ được thể hiện ngay sau khi bạn thực hiện một cái nhấp chuột.

Nếu dữ liệu hiện tại có bất kỳ biến đổi nào, hoặc bạn nhận file mới, bạn chỉ cần lưu lại file với

Thêm đường dẫn file vào ô chỉ trong vài giây nhấn chuột,

Bài viết này sẽ hướng dẫn cách liên kết đường dẫn tệp với ô Excel mà không cần xoá đi file đã có sẵn.

Cập nhật các truy vấn nhất định

Khi có quá nhiều truy vấn mở, việc làm mới chúng có thể tốn nhiều thời gian. Làm sao để chỉ cập nhật những truy vấn cần thiết? Dưới đây là vài phương pháp:

Nút Refresh

Để làm mới truy vấn trong bảng, chỉ cần vào Data–> chọn từ menu thả xuống và chọn Refresh.

Chọn Refresh sẽ giúp cập nhật truy vấn đã chọn.

Khi dữ liệu từ Power Query được nhập vào Excel, Queries and Connections sẽ hiện ra, liệt kê tất cả truy vấn trong workbook.

Mở menu này bằng cách chọn Data -> Queries and Connections nếu nó chưa hiển thị.

Trong Queries and Connections, mỗi truy vấn đều có icon refresh riêng, chỉ việc click vào đó để cập nhật.

Hoặc bạn có thể click chuột phải vào truy vấn và chọn Refresh từ menu xuất hiện.

Cập nhật qua VBA

Phần này không được giải thích trong loạt bài, tuy nhiên VBA có thể được dùng để làm mới các truy vấn cụ thể. Điều này có ích khi bạn:

  • Muốn tạo một giao diện thân thiện với người dùng,
  • Mong muốn kiểm soát trình tự cập nhật của các truy vấn.

Tuỳ chọn cập nhật nâng cao

Khi workbook có nhiều truy vấn, việc cập nhật từng cái một có thể chậm và tốn kém thời gian. May mắn là Excel cung cấp các tuỳ chọn tự động cập nhật dữ liệu.

Chọn ô từ bảng truy vấn, sau đó vào Data -> Refresh All (Drop Down) -> Connection Properties.

Sau đó cửa sổ Query Properties sẽ xuất hiện:

Tuỳ chọn cập nhật có trong cửa sổ này bao gồm:

Background refresh: giúp bạn tiếp tục công việc trong Excel trong khi dữ liệu đang cập nhật ở nền chương trình. Mặc định, tuỳ chọn này được bật. Nếu tắt tuỳ chọn, bạn sẽ không thể tiếp tục làm việc cho tới khi cập nhật hoàn tất. Nếu bật, quá trình cập nhật sẽ lâu hơn nhưng công việc không bị gián đoạn.

Làm mới cứ mỗi x phút: Workbook cần mở để tuỳ chọn Refresh every x minutes hoạt động. Cực kỳ có ích cho dữ liệu thường xuyên thay đổi. Cần kết hợp với Background refresh để tránh gây gián đoạn cho người dùng.

Làm mới khi mở file: Tính năng tự động cập nhật dữ liệu mỗi khi file được mở rất tiện lợi vì dữ liệu luôn mới nhất mỗi lần mở file.

Làm mới kết nối này khi Refresh All: đối với truy vấn chứa dữ liệu không đổi, việc cập nhật nhiều lần không cần thiết. Bỏ dấu tích trong hộp Refresh this connection on Refresh All để loại bỏ họ khỏi quá trình làm mới chung.

Điều cần lưu ý khi làm mới dữ liệu

Dưới đây là một số vấn đề bạn có thể gặp:

Mở và đóng file

Nguồn dữ liệu lưu trữ ngoài như CSV, file văn bản hay Excel workbook cực kỳ quan trọng trong quá trình cập nhật.

Nếu nguồn dữ liệu lưu ở ngoài và file đang mở, mọi thay đổi sẽ không được Power Query cập nhật cho tới khi chúng được lưu lại.

Khi truy vấn và dữ liệu nằm trong cùng một workbook, mọi thay đổi đều tức thì ngay cả khi chưa lưu file.

Truy vấn dựa trên truy vấn khác

Bài viết sau sẽ giải thích cách tạo truy vấn từ một truy vấn khác. Nhưng cần chú ý nếu chỉ làm mới truy vấn đơn lẻ thì các truy vấn tiếp theo không được cập nhật tự động.

Làm mới nền

Background refresh là tính năng tiện lợi cho phép làm việc khác trong Excel. Tuy nhiên, các PivotTables và công thức có thể không chính xác nếu chưa hoàn thành việc làm mới nền. Trong một số trường hợp, tuỳ chọn này có thể không cần thiết.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop