12 file lương mỗi file một kiểu cột: gộp số liệu quyết toán thuế TNCN bằng Power Query

Power QueryNâng cao Video 3:07

Bài toán

Tới mùa quyết toán thuế TNCN, mở mười hai file lương ra cộng cho từng người: tháng thêm cột phụ cấp, tháng đổi tên cột thuế, copy dán lệch một cột là sai cả năm.

Làm xong được gì

Một bảng cả năm theo từng người: tổng thu nhập chịu thuế, tổng thuế đã khấu trừ, đủ cả người nghỉ giữa năm; thả file lương tháng 12 (thêm cột thưởng) vào thư mục, Refresh là cộng tiếp.

Dùng trong bài

From FolderTransform Sample FileUnpivot Other ColumnsReplace ValuesPivot Column

File thực hành gồm

KeToan\Luong2026\Bang luong T01-2026.xlsx … Bang luong T11-2026.xlsx
Mười một bảng lương tháng 1 tới tháng 11 của một công ty 18 người, sheet Luong: 4 dòng tiêu đề, dòng 5 là tên cột, cuối có dòng Cộng và chỗ ký. Khuôn cột đổi theo tháng: tháng 4 thêm cột Phụ cấp xăng xe, từ tháng 7 đổi mẫu, cột Thuế TNCN đổi tên thành TNCN khấu trừ.
KeToan\LuongT12\Bang luong T12-2026.xlsx
Bảng lương tháng 12, có thêm cột Thưởng cuối năm. Dùng ở bước cuối.
KeToan\Quyet toan TNCN 2026.xlsx
Bản đã gộp sẵn, mỗi người một dòng. 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 Quyet toan TNCN 2026.xlsx trỏ vào C:\KeToan\Luong2026.

Các bước làm

1
Lấy dữ liệu từ thư mục lương
Mở một file Excel trắng. Vào Data › Get Data › From File › From Folder, dán C:\KeToan\Luong2026 rồi bấm Open. Hộp xem trước liệt kê các file lương trong thư mục. Bấm mũi tên ở nút Combine, chọn Combine & Transform Data. Ở hộp Combine Files bấm sheet Luong rồi OK.
2
Dọn tiêu đề ngay trên file mẫu
Trong Power Query Editor, ở khung Queries bên trái bấm Transform Sample File. Sửa ở đây thì file nào cũng được dọn cùng một cách. Vào Home › Remove Rows › Remove Top Rows, gõ 3 rồi OK: lúc mở Excel đã tự đưa dòng tên công ty lên làm tiêu đề, còn lại 3 dòng nằm trên dòng tên cột. Sau đó bấm Home › Use First Row as Headers.
3
Xoá bước Changed Type ở file mẫu
Ở khung Applied Steps, Excel vừa tự thêm bước Changed Type. Bước này ghi cứng tên cột của tháng 1, tháng nào đổi tên cột là báo lỗi. Bấm dấu X cạnh tên bước để xoá.
4
Bỏ dòng Cộng và phần chữ ký
Bấm mũi tên lọc ở tiêu đề cột Mã NV, chọn Remove Empty. Dòng Cộng và các dòng chữ ký không có mã nhân viên nên bị loại.
5
Unpivot Other Columns
Bấm tiêu đề cột Mã NV, giữ Ctrl bấm tiêu đề Họ và tên, chuột phải rồi chọn Unpivot Other Columns. Mọi cột còn lại xoay thành dòng. Bảng còn 4 cột: Mã NV, Họ và tên, Attribute ghi tên cột cũ, Value ghi giá trị. File thừa hay thiếu cột nào cũng về đúng bốn cột này.
6
Sang bảng gộp, xoá Changed Type lần nữa
Ở khung Queries bấm query Luong2026. Bảng báo lỗi ở bước Changed Type vì bước này còn nhớ khuôn cột lúc Combine. Bấm dấu X để xoá bước đó.
7
Gom tên cột thuế về một tên
Ở cột Attribute, chuột phải một ô ghi Thuế TNCN, chọn Replace Values. Ô Value To Find đã điền sẵn Thuế TNCN, ô Replace With gõ TNCN khấu trừ, OK.
8
Chỉ giữ hai khoản cần quyết toán
Bấm mũi tên lọc cột Attribute, bỏ tích Select All, tích Thu nhập chịu thuế và TNCN khấu trừ, OK. Sau đó bấm tiêu đề cột Source.Name rồi bấm Delete. Để lại cột tên file thì mỗi tháng thành một dòng riêng.
9
Pivot Column, mỗi người một dòng
Bấm tiêu đề cột Attribute, vào Transform › Pivot Column. Ô Values Column đổi sang Value, phần Advanced options để nguyên Sum, OK. Bảng còn 18 dòng, 4 cột: Mã NV, Họ và tên, Thu nhập chịu thuế, TNCN khấu trừ. Bấm Home › Close & Load. Về Excel, dò dòng NV009: người này nghỉ từ tháng 6, năm tháng lương 20.000.000 ra thu nhập chịu thuế 100.000.000, thuế đã khấu trừ 600.000. Cả 18 người cộng lại là 4.056.307.692 thu nhập chịu thuế và 42.369.924 tiền thuế.
10
Tháng 12 chỉ thả file và Refresh
Kéo file Bang luong T12-2026.xlsx từ thư mục LuongT12 sang thư mục Luong2026. Về Excel bấm Data › Refresh All. File tháng 12 có thêm cột Thưởng cuối năm vẫn gộp được. Tổng thu nhập chịu thuế lên 4.796.384.615, tổng thuế đã khấu trừ lên 79.283.597.

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

Bài chỉ cộng số thu nhập chịu thuế và số thuế đã khấu trừ ghi trên từng bảng lương, không tính lại thuế.
Bước Changed Type do Excel tự thêm ghi cứng tên cột của file đầu tiên. Thư mục có file khác khuôn cột thì phải xoá bước này ở cả file mẫu lẫn bảng gộp.

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