Gộp nhiều file tháng thành bảng năm rồi đối chiếu không để lệch
Đầu bài
Mỗi tháng một file cùng mẫu, cần bảng tổng cả năm VÀ chắc chắn không sót file, không nạp trùng, tổng khớp với số đã chốt từng tháng.
Các bước làm
- 1
Gộp cả thư mục trong một bước
Data → From Folder → Combine & Transform. Đây là bước DUY NHẤT nhờ tới công cụ; xong là ra một bảng dọc, phần còn lại xử lý bằng hàm cho mình chủ động số.
- 2
Kiểm ngay số dòng vừa nạp
Ctrl+T đóng thành Table rồi Ctrl+Shift+↓ xem có đúng tổng số dòng 12 file cộng lại không — hụt là sót file, dư là lẫn file lạ.
- 3
Tách tháng từ tên file gốc
Cột Source.Name có sẵn: =TEXTBEFORE(ô,".") hoặc MID/FIND để rút ra cột Tháng, khỏi phải nhớ dòng nào của tháng nào.
- 4
Cộng kiểm từng tháng
=SUMIFS(số_tiền, cột_tháng, T) cho 12 tháng, khoá vùng bằng F4, rồi Alt+= tổng 12 con số đó lại.
- 5
Đối chiếu với số đã chốt tay
=IF(ABS(tổng_gộp - tổng_chốt)<1, "Khớp", "Lệch") — so bằng ABS<1 chứ đừng =, số thập phân trong máy hay lệch phần nghìn.
- 6
Bắt file bị nạp hai lần
=COUNTIFS(cột_khoá, khoá) > 1 là chứng từ trùng. Lọc ra soát ngay, một lần Refresh nhầm là nhân đôi doanh thu.
- 7
Tô đỏ dòng lệch để không bỏ sót
Conditional Formatting theo cột kiểm; tháng sau thả file mới vào thư mục rồi Alt+F5, mọi công thức kiểm chạy lại y nguyên.
Phím dùng trong bài
Bẫy hay dính
File tạm ~$ hay file năm ngoái lẫn trong thư mục là Power Query nuốt luôn mà không báo — đừng tin mắt, luôn để SUMIFS/COUNTIFS đối chiếu tổng làm chốt chặn.
Cùng nhóm gộp dữ liệu từ nhiều nguồn
- Tổng hợp 12 sheet cùng mẫu trong một fileFile có sheet T1 đến T12 cùng bố cục, cần một sheet tổng cộng dồn ô B5 của cả 12 sheet mà không gõ 12 lần.
- Năm chi nhánh gửi năm kiểu cột, phải ghép thành một bảngCùng một biểu mẫu nhưng nơi để cột Ngày lên đầu, nơi để cuối, nơi chèn thêm cột ghi chú. Copy dán tay là lệch cột mà cả tháng sau mới phát hiện.
- Ghép bảng bán hàng với danh mục mà số dòng tự nhiên phình raGhép thêm cột Nhóm hàng, Vùng, Nhân viên phụ trách từ mấy bảng danh mục. Ghép xong doanh thu tự dưng cao hơn trước, không ai chỉ ra được sai ở đâu.
- Tháng mới đổ thêm dữ liệu mà công thức với Pivot không phải sửa vùngMỗi tháng dán thêm mấy nghìn dòng vào cuối bảng. Lần nào cũng phải mở từng công thức sửa $A$2:$A$5000, sửa vùng nguồn Pivot; quên một chỗ là báo cáo thiếu hẳn một tháng mà không báo lỗi.