2026-10-03 · Power Query & Excel
Bảng chi phí tháng 6 của bốn bộ phận, 12 chứng từ, tổng 117.996.000 đồng. Tên bộ phận nằm trên một dòng riêng, gộp ngang cả bốn cột cho dễ nhìn. Người đọc thấy ngay khoản nào của bộ phận nào. Excel thì không thấy: bảng không có cột Bộ phận nên Pivot Table không chia được chi phí theo bộ phận, còn lệnh sắp xếp từ chối chạy vì vùng dữ liệu có ô gộp.
Chuẩn hóa dữ liệu Excel trong bài này là chuyện của bảng nhập liệu: bảng các bạn tự lập, ghi thêm mỗi ngày, rồi dùng làm nguồn cho Pivot Table, Power Query hay Power BI. Bốn quy tắc đầu lấy từ hướng dẫn của Microsoft về dữ liệu nguồn cho PivotTable, ba quy tắc sau là những thói quen lập bảng làm báo cáo phía sau sai lệch. Ví dụ lấy từ bộ file demo EzTech dùng trong loạt video Excel cơ bản. Nếu dữ liệu đã bẩn sẵn và cần dọn, xem thêm bài làm sạch dữ liệu trong Excel.
Bảng cho người đọc và bảng cho máy đọc
Trang hướng dẫn tạo PivotTable của Microsoft nêu yêu cầu với dữ liệu nguồn khá ngắn gọn:
- Dữ liệu tổ chức dạng bảng, không có dòng trống hay cột trống.
- Kiểu dữ liệu trong một cột phải giống nhau, ví dụ không trộn ngày với chữ trong cùng một cột.
- Mọi cột đều có tiêu đề, tiêu đề nằm trên một dòng duy nhất, mỗi cột một tên riêng, không bỏ trống. Tránh tiêu đề hai tầng và tránh ô gộp.
- Tốt nhất là định dạng vùng dữ liệu thành Excel Table.
Những thứ làm bảng dễ đọc với người (ô gộp, dòng tên nhóm, dòng cộng, tô màu) phần lớn đi ngược danh sách trên. Hai loại bảng phục vụ hai việc khác nhau. Rắc rối chỉ đến khi dùng bảng trình bày làm dữ liệu nguồn.
| Đặc điểm | Bảng trình bày | Bảng dữ liệu nguồn |
|---|---|---|
| Tiêu đề | Nhiều tầng, có ô gộp | Một dòng, mỗi cột một tên |
| Nhóm (bộ phận, chi nhánh) | Dòng tiêu đề nhóm chèn giữa bảng | Một cột riêng, ghi ở mọi dòng |
| Tổng cộng | Dòng cộng sau mỗi nhóm | Không có, để Pivot Table tự tính |
| Trạng thái | Tô màu | Một cột trạng thái |
| Kỳ báo cáo | Mỗi tháng một cột hoặc một sheet | Một cột Ngày hoặc Tháng |
7 quy tắc chuẩn hóa dữ liệu đầu vào trong Excel
1. Một dòng tiêu đề, mỗi cột một tên riêng
Tiêu đề hai tầng kiểu "Tháng 1" gộp phía trên hai cột "Số lượng", "Doanh thu" là cách trình bày quen thuộc của báo cáo. Làm nguồn thì Excel chỉ lấy một dòng làm tên cột: dòng trên có ô trống, dòng dưới có tên trùng nhau ở mỗi tháng. Gộp hai tầng thành một tên đủ nghĩa, như "Doanh thu T1", hoặc tốt hơn là đưa tháng xuống thành một cột (quy tắc 7).
2. Mỗi dòng là một nghiệp vụ
Bảng chi phí ở đầu bài có 16 dòng dưới tiêu đề nhưng chỉ 12 dòng là chứng từ. Bốn dòng còn lại là tên bộ phận, nằm ngay trong cột "Số CT". Cột này vì thế chứa hai loại thông tin: 12 mã chứng từ và 4 tên bộ phận. Lọc theo Số CT, sắp xếp theo Số tiền hay đếm số chứng từ đều phải trừ tay bốn dòng đó.
Dòng cộng chèn giữa bảng còn nguy hiểm hơn vì nó làm sai số mà không báo lỗi. Bảng chi tiền demo của EzTech có 19 phiếu chi của 4 chi nhánh, tổng 1.005.390.000 đồng. Lệnh Data → Subtotal chèn thêm một dòng cộng sau mỗi chi nhánh và một dòng tổng ở cuối. Các dòng này dùng hàm SUBTOTAL, và hàm SUBTOTAL bỏ qua các SUBTOTAL lồng bên trong để không cộng hai lần. Nhưng nếu sau đó ai cộng cả cột bằng SUM, hoặc lấy vùng này làm nguồn cho Pivot Table, mỗi đồng được tính ba lần: một lần ở dòng chi tiết, một lần ở dòng cộng chi nhánh, một lần ở dòng tổng.
Cách làm đúng: tên bộ phận, tên chi nhánh thành một cột ghi ở mọi dòng; tổng để Pivot Table hoặc dòng Total Row của Excel Table lo. Với bảng chi phí ở đầu bài, chỉ cần thêm cột Bộ phận là Pivot Table chia ra ngay: Kế toán 8.810.000, Kinh doanh 36.500.000, Kho vận 40.120.000, Nhân sự 32.566.000 đồng.
3. Không gộp ô trong vùng dữ liệu
Trang hỗ trợ của Microsoft ghi rõ Excel không sắp xếp dữ liệu trong cột có ô gộp. Sắp xếp một vùng có ô gộp như bảng chi phí ở đầu bài, Excel dừng lại với thông báo "To do this, all the merged cells need to be the same size." Lọc cũng lệch: vùng gộp chỉ giữ giá trị ở ô trên cùng bên trái, các ô còn lại là ô trống, nên lọc theo cột đó chỉ ra dòng đầu của mỗi vùng gộp.
Muốn chữ nằm giữa nhiều cột mà không gộp ô: chọn các ô, nhấn Ctrl + 1, thẻ Alignment, mục Horizontal chọn Center Across Selection. Nhìn giống hệt Merge & Center nhưng mỗi ô vẫn độc lập. Tùy chọn này có ở Excel trên máy tính.
Tìm ô gộp đang ẩn trong một bảng lớn theo hướng dẫn của Microsoft: Home → Find & Select → Find, bấm Options → Format, thẻ Alignment tích Merge cells, OK, rồi Find All. Excel liệt kê mọi ô gộp, bấm vào dòng nào thì nhảy tới ô đó.
4. Mỗi cột một loại thông tin, một kiểu dữ liệu
Cột Ngày của bảng chi phí ở đầu bài nhìn rất chuẩn: "05/06/2026", "08/06/2026". Cả 12/12 ô đều đang là chữ, không phải ngày. Lọc theo tháng, tính số ngày, nhóm theo tuần đều không làm được cho tới khi đổi về ngày thật. Cách đổi hàng loạt có ở bài sửa lỗi ngày tháng trong Power Query.
Cột số cũng vậy. Ô Số lượng ghi "5 cái" là chữ, và theo trang hướng dẫn của Microsoft, hàm SUM bỏ qua giá trị chữ, chỉ cộng các ô số. Tổng thiếu mà không ô nào báo lỗi.
5. Đơn vị và ghi chú nằm ở tiêu đề hoặc cột riêng
Một bảng công nợ demo của EzTech đặt tên cột là "Công nợ (đồng)", ô bên dưới chỉ chứa số. Cách này giữ được cả hai: người đọc biết đơn vị, Excel vẫn cộng được. Ghi chú kiểu "đã trả 2 lần", "chờ hóa đơn" đưa sang một cột Ghi chú, đừng viết chung vào ô số tiền.
6. Màu chỉ để nhìn, thông tin phải nằm trong ô
Tô vàng nghĩa là "đã thanh toán" chỉ có người lập bảng hiểu. Các hàm như COUNTIF, SUMIFS không có điều kiện theo màu, còn Power Query và Power BI đọc giá trị trong ô chứ không đọc màu. Thêm một cột Trạng thái, dùng Data → Data Validation tạo danh sách chọn ("Đã thanh toán", "Chưa thanh toán") để mọi người nhập cùng một cách viết. Muốn vẫn có màu thì dùng Conditional Formatting tô theo cột Trạng thái: màu sinh ra từ dữ liệu, không thay cho dữ liệu.
7. Kỳ báo cáo đi theo dòng, nhiều kỳ chung một bảng
Bảng ngang 12 cột cho 12 tháng dễ đọc nhưng khó phân tích: thêm tháng mới là thêm cột, mọi công thức và Pivot Table phía sau phải sửa theo. Bảng dọc với một cột Tháng và một cột Số tiền thì tháng mới chỉ là thêm dòng. Nếu bảng ngang đã có sẵn, Power Query đổi được trong vài bước, hướng dẫn ở bài Unpivot trong Power Query.
Tương tự với thói quen mỗi tháng một sheet. Ghi chung vào một bảng có cột Tháng là cách gọn nhất. Nếu buộc phải tách sheet, giữ cấu trúc cột y hệt nhau giữa các sheet để gộp lại bằng Power Query, như bài gộp nhiều sheet bằng Power Query.
Cài đặt nhỏ giữ bảng nhập liệu luôn chuẩn
- Định dạng thành Excel Table (chọn một ô trong bảng, nhấn Ctrl + T). Microsoft giải thích Table là nguồn tốt cho PivotTable vì dòng thêm vào bảng tự được tính khi refresh, cột mới tự hiện trong danh sách trường.
- Data Validation cho các cột danh mục như Bộ phận, Trạng thái, Mã khách hàng, để không ai gõ "Kế toán" ở dòng này và "KT" ở dòng kia.
- Tách sheet nhập liệu và sheet báo cáo. Sheet nhập liệu theo đủ 7 quy tắc. Sheet báo cáo dựng bằng Pivot Table hoặc công thức từ sheet nhập liệu, ở đó gộp ô, tô màu thoải mái.
Vị trí từng nút trên Ribbon (Format as Table, Data Validation, Merge & Center, Subtotal) có trong trang tra cứu công cụ Excel.
Khi bảng đến từ người khác hoặc từ phần mềm
Bảng do phần mềm xuất ra hay do đơn vị khác gửi thì không sửa được tận gốc. Khi đó Power Query làm phần chuẩn hóa mỗi lần refresh. Trong file báo cáo lãi lỗ tự cập nhật của EzTech, query đọc sổ nhật ký chung xuất từ phần mềm có một bước bỏ 3 dòng đầu (phần tiêu đề của báo cáo) và một bước lọc bỏ dòng "Tổng cộng". Hai query đọc danh sách khách hàng và danh mục khoản mục chi phí cũng phải bỏ 2 dòng đầu. Bảng nhập liệu tự lập theo 7 quy tắc trên thì không cần những bước đó. Cách xử lý file phần mềm kế toán xuất ra có ở bài xử lý dữ liệu MISA trong Excel.
Kiểm nhanh một bảng trước khi dùng làm nguồn
| Câu hỏi | Cách kiểm |
|---|---|
| Có ô gộp không? | Find & Select → Find → Options → Format → Alignment → Merge cells → Find All |
| Có dòng tên nhóm, dòng cộng lẫn trong dữ liệu? | Lọc cột mã chứng từ, tìm các ô có chữ "Tổng", "Cộng" hoặc tên nhóm |
| Cột số có ô nào đang là chữ? | Lấy =COUNTA(D2:D500)-COUNT(D2:D500) trên vùng dữ liệu, không lấy dòng tiêu đề: kết quả lớn hơn 0 là có ô không phải số |
| Cột ngày có ô nào là chữ? | Cùng phép tính trên cho cột ngày, vì ngày thật trong Excel là số |
| Thông tin nào đang nằm trong màu? | Hỏi người lập bảng màu đó nghĩa là gì, có nghĩa thì thêm cột |
Câu hỏi thường gặp
Chuẩn hóa dữ liệu có phải bỏ hết định dạng không?
Không. Tô màu, căn lề, định dạng số, cố định dòng tiêu đề vẫn dùng bình thường. Điều kiện duy nhất: không thông tin nào chỉ nằm trong định dạng, và không gộp ô trong vùng dữ liệu.
Báo cáo gửi sếp cần gộp ô, cần dòng cộng thì làm sao?
Làm ở sheet báo cáo, dựng từ sheet dữ liệu. Pivot Table tự có dòng tổng theo nhóm, và có thể đổi bố cục sang dạng có tiêu đề nhóm cho dễ đọc. Cách nối Power Query với Pivot Table để báo cáo chỉ cần Refresh có ở bài Power Query và Pivot Table.
Bảng đã lỡ gộp ô cả cột nhóm thì gỡ thế nào?
Chọn cột, bấm Merge & Center để bỏ gộp. Giá trị chỉ còn ở ô đầu mỗi nhóm, các ô dưới trống. Điền xuống bằng Go To Special → Blanks rồi Ctrl + Enter, các bước chi tiết ở mục "ô trống trong cột nhóm" của bài làm sạch dữ liệu.
Bắt đầu từ bảng nhập liệu
Các bạn có thể bắt đầu ngay với bảng đang dùng hằng ngày: chạy qua năm câu kiểm ở trên, sửa từng chỗ. Khóa DATA FINANCE của EzTech đi từ cấu trúc bảng dữ liệu, Power Query trong Excel đến báo cáo tài chính trên Power BI. Bạn mới bắt đầu với dữ liệu có thể xem khóa Đào tạo nghiệp vụ Data.
