Đối chiếu hai danh sách: bên nào thiếu, bên nào lệch tiền
Đầu bài
Sao kê ngân hàng một bên, sổ kế toán một bên. Cần biết chứng từ nào chỉ có ở một bên, và chứng từ nào có cả hai bên nhưng lệch số tiền.
Các bước làm
- 1
Chuẩn hoá khoá đối chiếu trước
Cả hai bên bọc =TRIM(chuỗi) và thống nhất kiểu dữ liệu. Khoá lệch một dấu cách là sai hết phần sau.
- 2
Đánh dấu cái chỉ có một bên
=IF(COUNTIF(danh_sách_kia, khoá)=0, "Chỉ có bên này", "Có cả hai") — làm cho cả hai bảng, mỗi bên một cột.
- 3
So số tiền của phần có cả hai
=SUMIF(khoá_kia, khoá, tiền_kia) - tiền_bên_này. Khác 0 là lệch.
- 4
Bọc sai số làm tròn
Đừng so bằng =0. Dùng =ABS(chênh)<0.01 vì số thập phân trong máy tính hay lệch phần nghìn, nhìn bằng mắt thì thấy bằng nhau.
- 5
Tô màu phần lệch
Home → Conditional Formatting → New Rule → Use a formula, trỏ vào cột chênh lệch.
- 6
Cách gọn hơn nếu dữ liệu lớn
Đưa cả hai bảng vào Power Query rồi Merge Queries kiểu Full Outer — ra thẳng bảng đối chiếu và Refresh được kỳ sau.
Phím dùng trong bài
Bẫy hay dính
COUNTIF coi mã dài trên 15 chữ số là bằng nhau ở 15 số đầu — mã chứng từ dài phải nối thêm ký tự chữ: COUNTIF(vùng, khoá&"*") hoặc dùng SUMPRODUCT(EXACT()).
Cùng nhóm dò tìm, đối chiếu & bắt lệch
- VLOOKUP báo #N/A dù hai bên nhìn y hệt nhauDò mã khách từ bảng này sang bảng kia, mắt nhìn hai ô giống hệt nhau nhưng công thức vẫn trả về #N/A. Đây là câu hỏi số một của học viên mới.
- Dò theo hai điều kiện trở lên không cần cột phụCần lấy giá theo cả mã hàng VÀ khu vực, hoặc doanh số theo nhân viên VÀ tháng. VLOOKUP thường chỉ dò được một khóa.
- Đếm giá trị không trùng và truy ra bản ghi trùngCần biết có bao nhiêu khách hàng KHÁC NHAU (không phải bao nhiêu dòng), và chỉ ra chứng từ nào bị nhập trùng theo nhiều khóa cùng lúc.
- Dò đơn giá theo NGÀY HIỆU LỰC khi bảng giá đổi mấy lần trong nămCùng một mã hàng có nhiều dòng trong bảng giá, mỗi dòng một ngày áp dụng. Hoá đơn ngày nào phải ăn đúng giá có hiệu lực tại ngày đó — VLOOKUP thường chỉ lấy dòng nó gặp trước.