Dọn file sổ chi tiết bán hàng xuất từ phần mềm kế toán bằng Power Query, tháng sau chỉ bấm Refresh

Power QueryCơ bản Video 1:56

Bài toán

Sếp hỏi tháng này bán cho khách nào nhiều nhất, bạn xuất sổ chi tiết bán hàng từ phần mềm ra: hơn trăm cột, ba dòng tiêu đề, cuối còn dòng Tổng cộng, tháng nào cũng xoá tay lại từ đầu.

Làm xong được gì

Bảng doanh thu sạch chỉ giữ cột cần, Pivot doanh thu theo khách; tháng sau chép đè file xuất mới, Refresh là có số.

Dùng trong bài

From Excel WorkbookRemove Top RowsRemove EmptyChoose ColumnsPivotTable

File thực hành gồm

KeToan\BanHang\So chi tiet ban hang.xlsx
File xuất tháng 9 từ phần mềm, sheet Sổ chi tiết bán hàng: 3 dòng tiêu đề gộp ô, dòng 4 là tên cột, 132 cột, 500 dòng giao dịch và dòng Tổng cộng ở dòng 505.
KeToan\BanHang T10\So chi tiet ban hang.xlsx
File xuất tháng 10, cùng tên, dùng ở bước cuối.
KeToan\BaoCao_BanHang.xlsx
Bản đã dựng sẵn: query và Pivot doanh số theo khách. Mở ra bấm Data › Refresh All là chạy, dùng để so kết quả.

Giải nén gói file vào ổ C:\ để ra đúng thư mục C:\KeToan. Query trong BaoCao_BanHang.xlsx trỏ vào C:\KeToan\BanHang\So chi tiet ban hang.xlsx.

Các bước làm

1
Lấy dữ liệu từ file xuất
Mở một file Excel trắng. Vào Data › Get Data › From File › From Excel Workbook, chọn C:\KeToan\BanHang\So chi tiet ban hang.xlsx rồi bấm Import. Ở hộp Navigator bấm sheet Sổ chi tiết bán hàng, rồi bấm Transform Data.
2
Bỏ 3 dòng tiêu đề
Trong Power Query Editor, vào Home › Remove Rows › Remove Top Rows. Ô Number of rows gõ 3 rồi OK.
3
Đưa dòng tên cột lên làm tiêu đề
Bấm Home › Use First Row as Headers. Excel tự thêm bước Changed Type ngay sau đó, cột Ngày hạch toán được đổi sang kiểu ngày.
4
Bỏ dòng Tổng cộng
Bấm mũi tên lọc ở tiêu đề cột Tên khách hàng, chọn Remove Empty. Dòng Tổng cộng không có tên khách nên bị loại, bảng từ 501 dòng còn 500. Đừng lọc ở cột Ngày hạch toán: sau khi đổi kiểu, chữ Tổng cộng ở cột đó đã thành Error, lọc không ra.
5
Giữ sáu cột cần dùng
Vào Home › Choose Columns. Bỏ tích Select All Columns, rồi tích Ngày hạch toán, Số chứng từ, Tên khách hàng, Tên hàng, Số lượng bán, Doanh số bán. OK. Bảng từ 132 cột còn 6.
6
Nạp thẳng vào PivotTable
Bấm mũi tên ở nút Close & Load, chọn Close & Load To. Ở hộp Import Data chọn PivotTable Report rồi OK. Excel tạo sheet mới có một Pivot trống.
7
Dựng Pivot doanh số theo khách
Ở khung PivotTable Fields tích Tên khách hàng và Doanh số bán. Bấm ô giá trị đầu tiên của Pivot, vào tab Data, bấm nút xếp Z → A để xếp từ lớn đến nhỏ. Khách đứng đầu tháng 9 là Cơ điện Lũng Tùng Xanh, 900.892.300. Grand Total 4.344.741.400, khớp dòng Tổng cộng của file xuất, không có dòng (blank).
8
Tháng sau chép đè file và Refresh
Chép file trong thư mục BanHang T10 đè lên file cùng tên trong thư mục BanHang. Về Excel bấm Data › Refresh All. Pivot chuyển sang số tháng 10, khách đứng đầu là Xây lắp điện Gành Sỏi Bạc, 709.713.700.

Lưu ý khi làm với file thật

Máy nào menu ghi From Workbook thì bấm mục đó, cùng một lệnh với From Excel Workbook.
Quên bỏ dòng Tổng cộng thì Pivot có thêm một dòng (blank) bằng doanh số cả tháng, Grand Total ra gấp đôi.

Excel 2019, 2021, 365 trên Windows đều làm được như trong bài. Excel 2016 cũng có Power Query nhưng menu hơi khác.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học