2026-09-24 · Power Query & Excel
Kiểm kê cuối năm xong, trong tay có hai file. Một file là báo cáo tồn kho xuất từ phần mềm, mỗi mã hàng một dòng. Một file là bảng kiểm đếm do thủ kho điền, mỗi vị trí kệ một dòng. Việc tiếp theo là đối chiếu: mã nào khớp, mã nào lệch, lệch bao nhiêu tiền.
Nhiều người mở hai file cạnh nhau rồi dùng VLOOKUP. Cách đó cho ra kết quả sai ngay từ dòng đầu tiên, vì một mã hàng thường nằm ở nhiều kệ, nên có nhiều dòng trong bảng kiểm đếm. VLOOKUP chỉ lấy dòng đầu tiên nó gặp, và mã nào nằm ở bốn kệ thì ba kệ còn lại bị bỏ qua.
Bài này đi qua cách đối chiếu tồn kho trong Excel bằng Power Query trên một bộ file demo gồm 160 mã hàng, chỉ ra hai chỗ hay làm sai, và cách dựng để kỳ kiểm kê sau chỉ cần thay file rồi bấm Refresh.
Bộ file dùng trong bài
Bộ file demo mô phỏng kho của một công ty thương mại, kiểm kê lúc 17 giờ ngày 31/12/2025:
- Báo cáo tồn kho (sổ sách): 160 mã hàng, mỗi mã một dòng, gồm mã hàng, tên hàng, đơn vị tính, số lượng tồn sổ, đơn giá và giá trị tồn. Tổng giá trị tồn sổ là 12.306.025.000 đồng.
- Bảng kiểm đếm thực tế: 365 dòng, mỗi dòng là một lần đếm tại một vị trí kệ, gồm khu vực, vị trí kệ, mã hàng, tên hàng, số lượng đếm và người đếm.
Cả hai file đều có bốn dòng tiêu đề phía trên bảng (tên báo cáo, tên công ty, ngày, một dòng trống), bảng thật bắt đầu từ dòng thứ năm. Đây là dạng file rất hay gặp khi xuất từ phần mềm kế toán.
Đếm theo mã hàng, bảng kiểm đếm có 158 mã khác nhau. Trong đó 119 mã nằm ở từ hai vị trí trở lên: 54 mã nằm ở hai kệ, 42 mã ở ba kệ, 23 mã ở bốn kệ. Chỉ 39 mã nằm gọn ở một chỗ.
Chỗ sai thứ nhất: Merge thẳng khi chưa Group By
Lấy mã SP007 làm ví dụ. Sổ sách ghi tồn 212 bộ. Bảng kiểm đếm có bốn dòng cho mã này, ở bốn vị trí: 2 bộ, 30 bộ, 98 bộ và 74 bộ.
Nếu Merge bảng sổ sách với bảng kiểm đếm ngay, Power Query trả về bốn dòng cho SP007, mỗi dòng đem 212 so với một con số lẻ. Cả bốn dòng đều báo lệch, trong khi con số cần so là tổng bốn kệ: 2 + 30 + 98 + 74 = 204. Mã này thiếu 8 bộ, không phải lệch bốn lần.
Trên cả bộ file, Merge thẳng (kiểu Left Outer, giữ mọi dòng của sổ sách) ra 362 dòng thay vì 160. Mọi mã nằm ở nhiều kệ đều bị nhân lên và báo lệch sai.
Cách sửa: trong truy vấn của bảng kiểm đếm, dùng Group By theo mã hàng trước, cộng số lượng đếm. Sau bước này mỗi mã chỉ còn một dòng, và phép Merge mới so đúng một đối một.
Chỗ sai thứ hai: chọn kiểu Merge làm mất một nhóm mã
Kiểu Merge mặc định trong Power Query là Left Outer: giữ toàn bộ bảng bên trái, bảng bên phải dòng nào khớp thì ghép vào. Đặt sổ sách bên trái thì các mã có trong kho nhưng không có trên sổ sẽ biến mất khỏi kết quả, không báo gì.
Trong bộ file demo có đúng tình huống này. Bốn mã SP901, SP902, SP903, SP904 xuất hiện ở 9 dòng kiểm đếm với tổng 109 cái, nhưng sổ sách không có mã nào như vậy. Đó có thể là hàng đã nhập mà chưa ghi sổ, hàng gửi của đơn vị khác, hoặc thủ kho ghi nhầm mã. Cả ba trường hợp đều cần người đi hỏi, và kết quả đối chiếu phải hiện ra được để có người đi hỏi.
Chiều ngược lại cũng vậy: đặt bảng kiểm đếm bên trái thì 6 mã có trên sổ nhưng không ai đếm thấy sẽ biến mất. Vì thế đối chiếu tồn kho nên dùng Full Outer: giữ mọi mã của cả hai bên.
Các bước đối chiếu tồn kho bằng Power Query
- Nạp hai file. Data → Get Data → From File → From Workbook, chọn sheet dữ liệu, bấm Transform Data để mở Power Query Editor. Làm lần lượt cho cả hai file.
- Bỏ dòng tiêu đề thừa. Ở mỗi truy vấn, Home → Remove Rows → Remove Top Rows, nhập 4. Sau đó Home → Use First Row as Headers.
- Chuẩn hóa mã hàng. Chọn cột Mã hàng, Transform → Format → Trim, rồi Format → UPPERCASE. Bộ file demo không có lỗi này, nhưng file thật hay có mã dính dấu cách ở cuối hoặc gõ chữ thường, và Merge sẽ coi "sp007 " với "SP007" là hai mã khác nhau.
- Group By bảng kiểm đếm. Home → Group By, chọn Advanced. Nhóm theo Mã hàng; thêm cột Tổng SL đếm với phép Sum trên cột SL đếm, và cột Số vị trí với phép Count Rows. Cột Số vị trí giúp tra lại khi cần đi đếm lại.
- Merge kiểu Full Outer. Chọn truy vấn sổ sách, Home → Merge Queries → Merge Queries as New. Chọn cột Mã hàng ở cả hai bảng, Join Kind chọn Full Outer. Mở rộng cột kết quả, chỉ lấy Mã hàng, Tổng SL đếm và Số vị trí, rồi đổi tên cột mã vừa mở rộng thành Mã hàng kiểm kê.
- Phân loại trước khi điền số 0. Thêm Conditional Column tên Trạng thái: Mã hàng bằng null thì là "Đếm có sổ không", Tổng SL đếm bằng null thì là "Có sổ không đếm", còn lại để trống. Bước này phải đứng trước bước thay null bằng 0, vì sau đó không còn phân biệt được "không đếm thấy" với "đếm được 0".
- Gộp cột mã. Mã ngoài sổ có ô Mã hàng trống. Thêm Custom Column:
if [Mã hàng] = null then [Mã hàng kiểm kê] else [Mã hàng]. - Tính chênh lệch. Thay null bằng 0 ở hai cột số lượng (Transform → Replace Values), thêm cột Chênh lệch SL = Tổng SL đếm − SL tồn sổ và cột Chênh lệch giá trị = Chênh lệch SL × Đơn giá. Những dòng Trạng thái còn trống thì điền Khớp nếu Chênh lệch SL bằng 0, Lệch nếu khác 0. Close & Load ra sheet.
Các bước Merge có hướng dẫn chi tiết hơn ở bài Merge Queries thay VLOOKUP.
Đọc kết quả đối chiếu
Với bộ file demo, bảng kết quả có 164 dòng: 160 mã trên sổ cộng 4 mã chỉ có trong kho.
| Trạng thái | Số mã | Việc cần làm |
|---|---|---|
| Khớp | 140 | Không cần làm gì |
| Lệch | 14 | 10 mã thiếu, 4 mã thừa: đếm lại những kệ của mã đó |
| Có sổ không đếm | 6 | Hỏi xem hàng có nằm ở khu vực chưa kiểm không |
| Đếm có sổ không | 4 | Kiểm tra phiếu nhập, hàng gửi, hoặc mã ghi nhầm |
Tính theo giá trị, tổng chênh lệch là thiếu 180.555.000 đồng, khoảng 1,47% giá trị tồn sổ. Con số này cần đọc kỹ trước khi báo cáo:
- 152.010.000 đồng trong đó đến từ 6 mã không đếm thấy. Đây là phần cần xác minh đầu tiên, vì rất có thể hàng vẫn nằm ở một khu chưa ai đi tới.
- Mã lệch lớn nhất là SP106, thiếu 12 cái, tương ứng 15.000.000 đồng. Mã này chỉ nằm ở một vị trí kệ, nên đếm lại rất nhanh.
- 4 mã ngoài sổ không có đơn giá trên sổ, nên phần chênh lệch giá trị của chúng chưa tính được. Tổng 180.555.000 đồng ở trên chưa gồm 109 cái hàng này.
Vì sao không so tổng số lượng
Một cách kiểm nhanh hay được dùng là so tổng: sổ ghi 28.776 đơn vị, kiểm đếm được 28.205 đơn vị, lệch 571. Cách này không cho biết gì để xử lý. Con số 571 là kết quả của 6 mã không đếm được, 14 mã lệch theo hai chiều, và 109 cái hàng ngoài sổ được cộng thêm vào, tất cả bù trừ lẫn nhau. Hơn nữa, cộng số lượng của mặt hàng tính bằng cái, bộ và tấm với nhau vốn đã không có nghĩa.
Đối chiếu phải làm theo từng mã. Đây cũng là nguyên tắc của bài đối chiếu công nợ bằng Power Query: so từng dòng theo khóa chung, không so tổng.
Dùng lại cho kỳ kiểm kê sau
Khi đã dựng xong, kỳ sau chỉ cần ghi đè hai file mới vào đúng chỗ cũ, giữ nguyên tên sheet và bố cục cột, rồi bấm Data → Refresh All. Nếu kho chia nhiều bảng kiểm đếm theo từng khu, có thể nạp cả thư mục bằng Get Data → From Folder thay vì từng file.
Bảng kết quả đối chiếu cũng là đầu vào tốt cho báo cáo tồn kho có cảnh báo. Mẫu báo cáo nhập xuất tồn trong kho báo cáo của EzTech mở xem trực tiếp được.
Câu hỏi thường gặp
Bảng kiểm đếm ghi tên hàng mà không có mã thì làm sao?
Merge theo tên hàng rất dễ trượt vì chỉ cần khác một dấu cách hoặc một chữ viết hoa. Nên in sẵn phiếu kiểm đếm có cột mã hàng lấy từ sổ, để người đếm chỉ điền số lượng. Nếu phải dùng tên, chuẩn hóa tên ở cả hai bảng (Trim, Clean, cùng kiểu chữ) rồi kiểm tra kỹ nhóm "Có sổ không đếm" và "Đếm có sổ không", vì lỗi khớp tên sẽ rơi vào đúng hai nhóm này.
Mã hàng có số lô hoặc hạn sử dụng thì Group By theo gì?
Group By theo đúng mức chi tiết mà sổ sách đang theo dõi. Sổ theo dõi đến từng lô thì nhóm theo cả mã hàng và số lô, và Merge cũng dùng cả hai cột đó làm khóa.
Có làm được việc này trong Google Sheets không?
Google Sheets không có Power Query. Có thể làm bằng QUERY và SUMIF, nhưng phần Full Outer phải tự ghép danh sách mã của hai bên, và mỗi kỳ phải kiểm lại vùng công thức. Với file kiểm kê vài trăm dòng trở lên, Power Query trong Excel gọn hơn.
Bắt đầu từ đâu
Lấy file kiểm kê gần nhất của kho, đếm xem có bao nhiêu mã nằm ở nhiều hơn một vị trí. Nếu con số đó lớn, mọi phép VLOOKUP trên file này từ trước tới nay đều cần xem lại.
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 quy trình đối chiếu cho dữ liệu kho cụ thể của doanh nghiệp thì để lại thông tin ở trang liên hệ.
