2026-09-25 · Power Query & Excel
Nhiều file báo cáo Pivot được làm lại theo cùng một vòng mỗi tháng: tải dữ liệu mới từ phần mềm, dán đè vào sheet Data, sửa vài cột bị sai kiểu, kéo lại vùng nguồn của Pivot, bấm Refresh, rồi kiểm tra xem dòng cuối có vào báo cáo không. Pivot Table làm tốt phần tổng hợp. Phần nạp và làm sạch dữ liệu thì vẫn bằng tay.
Ghép Power Query và Pivot Table lại thì phần tay đó biến mất: Power Query lo lấy dữ liệu và làm sạch, Pivot Table lo tổng hợp, và cả hai cùng chạy lại bằng một lần Refresh All. Bài này đi từng bước trên file bán hàng demo 9.998 dòng, kèm hai chỗ hay ra số sai: đếm số khách hàng và tính tỷ lệ lãi gộp.
File demo dùng trong bài
File Du lieu ban hang tong hop.xlsx có một sheet Data gồm 9.998 dòng bán hàng từ 01/01/2024 đến 31/12/2025, mỗi dòng một số hóa đơn, 16 cột: ngày bán, năm, tháng, số hóa đơn, khu vực, chi nhánh, kênh bán, nhân viên bán hàng, nhóm hàng, tên hàng, khách hàng, số lượng, đơn giá, doanh thu, giá vốn, lợi nhuận gộp. Tổng doanh thu hai năm là 323.799.434.000 đồng.
Trong bài, file này đóng vai file nguồn xuất từ phần mềm bán hàng. File báo cáo là một file Excel khác, trỏ vào file nguồn qua Power Query. Tách hai file như vậy thì tháng sau chỉ cần thay file nguồn, file báo cáo không phải mở ra sửa.
Vì sao Power Query và Pivot Table nên đi cùng nhau
Pivot Table dựng thẳng trên một vùng ô có ba điểm yếu:
- Nguồn là vùng cố định, dữ liệu dài thêm thì phần dôi ra nằm ngoài báo cáo. Bài Slicer trong Excel có một ví dụ cụ thể.
- Dữ liệu bẩn (ngày lưu dạng chữ, số có khoảng trắng, dòng tổng cộng lẫn trong bảng) đi thẳng vào báo cáo.
- Không đếm được giá trị không trùng, ví dụ số khách hàng khác nhau.
Power Query xử lý hai điểm đầu vì mỗi lần Refresh nó đọc lại toàn bộ file nguồn và chạy lại các bước làm sạch. Điểm thứ ba được xử lý khi nạp dữ liệu vào Data Model, phần bên dưới sẽ nói.
Bước 1: Kết nối tới file nguồn
- Mở file báo cáo, chọn Data → Get Data → From File → From Excel Workbook.
- Chọn file nguồn. Ở cửa sổ Navigator, tích sheet Data, bấm Transform Data (không bấm Load).
- Power Query Editor mở ra. Đổi tên query ở khung bên phải thành
BanHang.
Nếu dữ liệu nằm ngay trong file báo cáo thì dùng Data → From Table/Range, nhưng cách này giữ lại vấn đề dán đè hằng tháng. Máy chưa thấy menu Get Data thì xem bài Excel bản nào có Power Query.
Bước 2: Làm sạch trong Power Query
Với file demo, dữ liệu đã sạch nên chỉ cần kiểm tra kiểu dữ liệu: Ngày bán là Date, các cột tiền là Whole Number, các cột chữ là Text. Bấm vào biểu tượng kiểu ở đầu mỗi cột để đổi nếu Power Query đoán sai.
Với file xuất từ phần mềm thật, đây là chỗ đặt các bước như bỏ dòng tiêu đề thừa, lọc bỏ dòng tổng cộng, đổi cột ngày dạng chữ sang ngày, bỏ khoảng trắng thừa. Mỗi bước được ghi lại trong khung Applied Steps và chạy lại tự động ở mọi lần Refresh. Các lỗi hay gặp ở bước này có trong bài 7 lỗi Power Query thường gặp.
Một việc không nên làm ở bước này: thêm cột tỷ lệ lãi gộp tính theo từng dòng. Lý do nằm ở phần bẫy thứ hai.
Bước 3: Nạp vào Data Model
- Chọn Home → Close & Load → Close & Load To...
- Hộp thoại Import Data hiện ra. Chọn Only Create Connection và tích Add this data to the Data Model. Bấm OK.
Hộp thoại này có bốn lựa chọn. Table đổ 9.998 dòng ra một sheet, chỉ cần khi muốn nhìn dữ liệu. PivotTable Report tạo luôn một Pivot nối vào query, gọn nhưng không có Data Model. Only Create Connection kèm Data Model giữ dữ liệu trong bộ nhớ của file, không chiếm sheet nào, và mở được các tính năng như Distinct Count.
Bước 4: Dựng Pivot Table từ Data Model
- Chọn Insert → PivotTable → From Data Model, chọn nơi đặt bảng.
- Khung PivotTable Fields hiện bảng
BanHang. Kéo Nhóm hàng vào Rows, Năm vào Columns, Doanh thu vào Values.
Bảng cho ra doanh thu 2024 là 142.243.638.000 đồng, 2025 là 181.555.796.000 đồng. Muốn xem mức tăng: kéo Doanh thu vào Values lần thứ hai, bấm chuột phải → Show Values As → % Difference From, Base field chọn Năm, Base item chọn (previous). Cột mới cho thấy toàn công ty tăng 27,6%, trong đó Phụ kiện tăng mạnh nhất (35,6%) và Điện thoại thấp nhất (22,8%), dù Điện thoại vẫn là nhóm doanh thu lớn nhất.
Bẫy 1: đếm số khách hàng
Kéo cột Khách hàng vào Values, Pivot mặc định dùng Count và cho ra 9.998: đó là số dòng có ghi khách hàng, không phải số khách.
Cách đúng: bấm vào ô trong cột đó → Value Field Settings → Summarize value field by → Distinct Count. Lựa chọn này chỉ xuất hiện khi Pivot dựng từ Data Model, đó là lý do chọn Add this data to the Data Model ở bước 3. Kết quả là 26.
Con số 26 vẫn cần đọc kỹ. Trong đó có một giá trị là "Khách lẻ", chiếm 3.007 dòng và 30,0% doanh thu. Một cái tên đó gộp rất nhiều người mua khác nhau. Báo cáo nên ghi rõ "25 khách doanh nghiệp và nhóm khách lẻ" thay vì "26 khách hàng".
Bẫy 2: tính tỷ lệ lãi gộp
Cách hay gặp: thêm cột Tỷ lệ lãi gộp = Lợi nhuận gộp / Doanh thu cho từng dòng trong Power Query, đưa vào Pivot rồi chọn Average. Cách này lấy trung bình của 9.998 tỷ lệ, dòng bán 21 cái cáp sạc có trọng số bằng dòng bán 4 cái điện thoại. Tỷ lệ đúng là tổng lợi nhuận gộp chia tổng doanh thu.
Trên file demo, hai cách cho kết quả như sau:
| Nhóm hàng | Tổng LN / tổng DT | Average theo dòng |
|---|---|---|
| Âm thanh | 25,33% | 25,40% |
| Đồng hồ | 25,27% | 25,10% |
| Điện thoại | 25,11% | 25,16% |
| Phụ kiện | 24,94% | 25,03% |
| Gia dụng | 24,88% | 24,98% |
| Laptop | 24,63% | 24,65% |
| Toàn bộ | 24,98% | 25,07% |
Chênh lệch ở file demo nhỏ vì biên lợi nhuận các dòng khá đều nhau. Dù vậy thứ hạng đã đổi: tính đúng thì Đồng hồ đứng thứ hai, tính bằng Average thì Đồng hồ tụt xuống thứ ba, dưới Điện thoại. Với dữ liệu thật, nơi cùng một nhóm có đơn bán sỉ biên thấp và đơn bán lẻ biên cao, khoảng chênh sẽ lớn hơn nhiều.
Cách tính đúng trên Pivot dựng từ Data Model: bấm chuột phải vào tên bảng BanHang trong khung PivotTable Fields → Add Measure, đặt tên Tỷ lệ lãi gộp, gõ công thức:
=DIVIDE(SUM(BanHang[Lợi nhuận gộp]), SUM(BanHang[Doanh thu]))
Chọn định dạng Percentage rồi kéo measure này vào Values. Measure tính lại theo đúng từng ô của Pivot: theo nhóm hàng, theo năm, theo chi nhánh, luôn là tổng chia tổng. Với Pivot không dùng Data Model, cách tương đương là PivotTable Analyze → Fields, Items & Sets → Calculated Field.
Bước 5: Refresh và tự cập nhật khi mở file
Tháng sau, thay file nguồn bằng bản mới cùng tên, cùng vị trí, mở file báo cáo, chọn Data → Refresh All. Power Query đọc lại file nguồn, chạy lại các bước làm sạch, Data Model nạp dữ liệu mới, mọi Pivot cập nhật theo.
Muốn file tự làm việc đó khi mở: Data → Queries & Connections, bấm chuột phải query BanHang → Properties, tích Refresh data when opening the file.
Hai điều cần giữ để Refresh không vỡ: file nguồn giữ nguyên tên sheet và tên cột, và đường dẫn file nguồn không đổi. Đổi thư mục thì sửa lại đường dẫn ở bước Source trong Power Query. Muốn gộp nhiều file tháng vào cùng một nguồn thì xem bài tự động hóa báo cáo Excel cuối tháng.
Câu hỏi thường gặp
Không tích Add this data to the Data Model thì có sao không?
Vẫn dựng được Pivot, nhưng mất Distinct Count và measure. Data Model cũng không đổ dữ liệu ra sheet, nên file báo cáo không phải chứa thêm một bảng vài chục nghìn dòng.
Một query có dùng cho nhiều Pivot được không?
Được. Mọi Pivot dựng từ Data Model cùng dùng bảng BanHang, nên một Slicer nối được tất cả qua Report Connections.
Khi nào nên chuyển sang Power BI?
Khi cần nhiều trang báo cáo, nhiều người cùng xem trên web, hoặc dữ liệu lên tới hàng triệu dòng. Power Query và measure DAX học trong Excel dùng lại gần như nguyên vẹn trong Power BI. Có thể xem bố cục một báo cáo bán hàng hoàn chỉnh ở mẫu báo cáo phân tích bán hàng.
Bắt đầu từ đâu
Chọn một báo cáo Pivot đang làm lại hằng tháng, tách dữ liệu nguồn ra một file riêng, kết nối bằng Power Query và nạp vào Data Model theo năm bước trên. Rà lại các ô đang đếm khách hoặc tính tỷ lệ, xem chúng đang dùng Count, Average hay tổng chia tổng.
Khóa DATA FINANCE của EzTech đi từ Power Query chuyên sâu trong Excel đến báo cáo quản trị trên Power BI, dành cho người làm kế toán, tài chính. Cần dựng bộ báo cáo cho dữ liệu riêng của doanh nghiệp thì để lại thông tin ở trang liên hệ.
