CÔNG TY TNHH CÔNG NGHỆ TỰ ĐỘNG VASCOM
Trang chủ › Tin tức › Excel: dùng Power Query thay cho việc dán…

Excel: dùng Power Query thay cho việc dán tay mỗi tháng

Mỗi đầu tháng, nhiều doanh nghiệp lại xuất dữ liệu từ phần mềm, mở tệp Excel, xóa dòng thừa rồi…

Mỗi đầu tháng, nhiều doanh nghiệp lại xuất dữ liệu từ phần mềm, mở tệp Excel, xóa dòng thừa rồi dán vào báo cáo tổng hợp. Cách này không khó nếu chỉ làm một lần, nhưng dễ sai khi phải ghép nhiều tệp hoặc lặp lại các thao tác giống nhau. Power Query phù hợp với công việc đó: ghi lại các bước xử lý để lần sau chỉ cần cập nhật tệp nguồn và làm mới dữ liệu. Điểm cần chuẩn bị không phải là công thức phức tạp, mà là cách tổ chức tệp và quy ước cột ngay từ đầu.

Khi nào nên chuyển sang Power Query

Nên chuyển khi báo cáo có đầu vào lặp lại và các bước làm sạch tương đối ổn định. Ví dụ, mỗi tháng phần mềm xuất một tệp công nợ có cùng cấu trúc, hoặc mỗi chi nhánh gửi một tệp bán hàng cần ghép thành một bảng. Power Query cũng hữu ích khi dữ liệu phải lọc dòng tổng cộng, bỏ dòng trống, đổi kiểu ngày, chuẩn hóa mã khách hàng trước khi đưa vào PivotTable.

  • Nên dùng: tệp xuất định kỳ, nhiều tệp cùng mẫu, thao tác làm sạch lặp lại, báo cáo cần truy vết nguồn.
  • Chưa cần dùng: bảng nhỏ chỉ xử lý một lần, mẫu dữ liệu thay đổi liên tục, hoặc yêu cầu báo cáo chưa được thống nhất.
  • Cần xử lý trước: tệp có ô gộp, tiêu đề nhiều dòng, nhiều bảng nằm chồng trong một trang tính. Power Query có thể đọc được nhưng các bước xử lý sẽ khó bảo trì.

Không nên bắt đầu bằng báo cáo lớn nhất. Hãy chọn một bảng có nguồn rõ ràng, người phụ trách hiểu cách đối chiếu và kết quả đầu ra đang được sử dụng thường xuyên.

Bốn bước dựng truy vấn từ tệp xuất của phần mềm

Giữ lại vài tệp của những kỳ trước để thử, trong đó có một tệp phát sinh dòng trống hoặc dữ liệu khác thường. Không thử ngay trên tệp báo cáo đang dùng để nộp.

  1. Tách vùng tệp nguồn. Tạo một thư mục riêng cho tệp xuất, ví dụ theo từng loại báo cáo. Chỉ đặt các tệp cần ghép trong thư mục này; không để bản nháp, tệp tạm hoặc báo cáo kết quả chung một nơi. Nếu mỗi kỳ chỉ có một tệp, có thể kết nối trực tiếp tới tệp thay vì dùng thư mục.
  2. Kết nối và chọn đúng đối tượng dữ liệu. Trong Excel, vào Data > Get Data > From File, chọn From Folder khi cần ghép nhiều tệp, hoặc chọn From Workbook cho một tệp. Với tệp Excel, chọn đúng bảng hoặc trang tính chứa dữ liệu chi tiết, không chọn trang có tiêu đề báo cáo và dòng tổng hợp.
  3. Làm sạch trong truy vấn mẫu. Dùng dòng tiêu đề làm tên cột, bỏ dòng trống và dòng tổng cộng, lọc các bản ghi không thuộc phạm vi báo cáo. Đặt kiểu dữ liệu cho ngày, số và văn bản; kiểm tra định dạng ngày theo đúng tệp nguồn. Khi ghép từ thư mục, thực hiện các thao tác này trong Transform Sample File để Excel áp dụng cùng cách xử lý cho từng tệp.
  4. Nạp kết quả và lưu cách làm mới. Chọn Close & Load To. Nạp thành bảng nếu cần xem và chỉnh báo cáo trên trang tính; chọn chỉ tạo kết nối nếu dữ liệu chỉ dùng cho PivotTable hoặc mô hình dữ liệu. Những tháng sau, thêm tệp mới vào đúng thư mục rồi chọn Refresh All, không mở từng tệp để sao chép.

Đặt tên truy vấn theo nội dung, chẳng hạn “CongNo_ChiTiet”, thay vì giữ tên mặc định. Các bước trong khung Applied Steps cũng nên đổi tên khi thao tác không dễ nhận ra, nhất là bước lọc và bước đổi tên cột.

Giữ truy vấn chạy được khi tên cột thay đổi

Lỗi thường gặp là phần mềm đổi “Mã KH” thành “Mã khách hàng”, trong khi các bước sau vẫn tìm tên cũ. Bước Changed Type do Excel tự tạo cũng có thể chứa một danh sách dài tên cột, nên chỉ một thay đổi nhỏ đã làm truy vấn báo lỗi. Cách giảm phụ thuộc là chuẩn hóa tên ngay sau khi đọc dữ liệu, rồi để toàn bộ phần xử lý phía sau dùng tên chuẩn.

  1. Tạo một bảng ánh xạ gồm hai cột: Tên trong tệp và Tên chuẩn. Ví dụ, cả “Mã KH” và “Mã khách hàng” đều quy về “MaKhachHang”; mỗi tệp chỉ được có một tên nguồn tương ứng với một tên chuẩn.
  2. Đưa bảng ánh xạ vào Power Query và dùng nó cho bước đổi tên. Với người đã quen chỉnh M, có thể dùng Table.RenameColumns cùng MissingField.Ignore để bỏ qua những tên cũ không xuất hiện trong tệp hiện tại.
  3. Chỉ đặt kiểu dữ liệu và viết phép tính sau bước đổi tên. Không tham chiếu cột bằng vị trí như “cột thứ ba”, vì thứ tự cột trong tệp xuất có thể thay đổi.
  4. Lập danh sách cột bắt buộc và kiểm tra với Table.ColumnNames trước khi xử lý. Nếu thiếu cột, truy vấn nên báo rõ tên cột thiếu thay vì âm thầm tạo một bảng thiếu dữ liệu.

Khi có tệp dùng tên cột mới, thử làm mới trên một bản sao trước. Nếu chỉ cần bổ sung ánh xạ thì cập nhật bảng tên cột; nếu ý nghĩa dữ liệu đã thay đổi, phải xem lại logic báo cáo thay vì chỉ sửa tên cho truy vấn chạy được.

Kiểm tra sau mỗi lần làm mới

Power Query tự lặp lại thao tác, nhưng không tự xác nhận kết quả đã đúng về nghiệp vụ. Sau Refresh All, cần đối chiếu với tệp nguồn trước khi phát hành báo cáo.

  • Kiểm tra số dòng sau khi lọc và giải thích các dòng bị loại.
  • Đối chiếu một vài chỉ tiêu tổng hợp, như tổng phát sinh hoặc số dư cuối kỳ, với báo cáo gốc.
  • Mở thông báo lỗi của từng truy vấn; không dùng bảng kết quả cũ nếu lần làm mới chưa thành công.
  • Lưu lại tệp nguồn theo kỳ để truy vết khi số liệu được hỏi lại.

Việc nên làm ngay

  1. Chọn một báo cáo đang dán tay định kỳ và lưu lại các tệp nguồn mẫu.
  2. Dựng truy vấn theo bốn bước, rồi đối chiếu kết quả với báo cáo hiện tại.
  3. Lập bảng tên cột chuẩn và ghi rõ cách kiểm tra sau mỗi lần làm mới.

Một truy vấn dễ bảo trì quan trọng hơn một truy vấn có nhiều bước xử lý khéo. Khi cần rà soát luồng dữ liệu giữa tệp xuất và báo cáo quản trị, VASCOM có thể cùng doanh nghiệp chuẩn hóa từ phần đầu vào trước khi tự động hóa.

Thủ thuật Trao đổi với VASCOM
Tin khác

Đọc tiếp

Zalo ☎
VASCOM
Gọi 0822.577.579 Đăng ký tư vấn