Đối chiếu hai danh sách (công nợ, sao kê)

Vấn đề bạn đang gặp

Bạn có sổ công nợ của mình: mỗi hóa đơn một hàng, với Số HĐ, khách hàng và số tiền phải thu. Khách hàng hoặc ngân hàng gửi sao kê những khoản đã trả. Hai danh sách không giống nhau: có hóa đơn chưa thấy khoản trả nào, có hóa đơn trả hai lần, có hóa đơn trả thiếu, và có hàng trong sao kê mà bạn không có hóa đơn nào khớp. Bạn cần biết đó là những hàng nào và lệch bao nhiêu tiền, thay vì dò từng hàng bằng mắt.

Đánh dấu khoản chưa có trong sao kê

Ghi Có hoặc Chưa có cho từng hóa đơn

1. 1

   Làm gì: Ở D2 gõ =IF(COUNTIF('Sao kê'!$A$2:$A$14,A2)>0,"Có","Chưa có").

   Vì sao: COUNTIF đếm số lần Số HĐ ở A2 xuất hiện trong cột Số HĐ của sao kê. Từ một lần trở lên là Có, không lần nào là Chưa có. Chữ trong công thức phải đặt trong dấu nháy kép.

2. 2Chỗ hay sai

   Làm gì: Giữ dấu $ trong vùng của trang tính Sao kê, nhấn Enter rồi sao chép D2 xuống hết danh sách.

   Vì sao: Vùng sao kê phải đứng yên khi sao chép. Nếu thiếu $, vùng trượt xuống từng hàng và các hóa đơn ở dưới bị báo Chưa có dù thật ra đã trả.

3. 3

   Làm gì: Với Excel 2021/365, một cách khác là =IFNA(XLOOKUP(A2,'Sao kê'!$A$2:$A$14,'Sao kê'!$B$2:$B$14),"Chưa có"): hiện số tiền của hàng khớp, hoặc Chưa có.

   Vì sao: XLOOKUP trả về #N/A khi không tìm thấy mã, và IFNA đổi đúng lỗi #N/A đó thành chữ bạn chọn. Nhưng cách này chỉ cho thấy hàng đầu tiên khớp, nên không dùng để cộng tiền.

4. 4

   Làm gì: Để thấy khoản có trong sao kê mà không có trong sổ, làm ngược lại: ở trang tính Sao kê, gõ =IF(COUNTIF('Bài tập'!$A$2:$A$13,A2)>0,"Có","Chưa có") ở một cột trống.

   Vì sao: Hai danh sách lệch theo hai chiều. Chiều thứ nhất tìm hóa đơn chưa thu được tiền, chiều thứ hai tìm khoản tiền chưa biết thuộc hóa đơn nào.

Tự hỏi mình

• Nếu một hóa đơn được trả hai lần, COUNTIF cho kết quả mấy? Kết quả đó có làm công thức IF sai không?
• Vì sao vùng sao kê cần $ còn Số HĐ ở A2 thì không?

Điền vào chỗ trống

=IF(COUNTIF('Sao kê'!$A$2:$A$14,A2)>0,"Có",)

Kiểm tra

Tính số đã thanh toán và chênh lệch

Cộng mọi hàng cùng Số HĐ rồi trừ

1. 1Chỗ hay sai

   Làm gì: Ở D2 (Đã thanh toán) gõ =SUMIF('Sao kê'!$A$2:$A$14,A2,'Sao kê'!$B$2:$B$14).

   Vì sao: SUMIF cộng các số ở vùng số tiền (đối số thứ ba) trên những hàng có Số HĐ bằng A2. Hóa đơn trả hai lần có hai hàng và cả hai được cộng. Trên lưới DeskLab, hóa đơn chưa có hàng nào cho kết quả 0.

2. 2

   Làm gì: Ở E2 (Chênh lệch) gõ =C2-D2.

   Vì sao: Chênh lệch là Phải thu trừ Đã thanh toán của cùng một hàng. Hàng bằng 0 là khớp, hàng lớn hơn 0 là còn phải thu.

3. 3

   Làm gì: Chọn D2:E2 và kéo xuống hàng 13. Lọc cột Chênh lệch khác 0 để thấy những hàng cần xem lại.

   Vì sao: Hai vùng của sao kê có $ nên đứng yên, còn A2 và C2 đổi theo từng hàng khi kéo xuống.

Tự hỏi mình

• Nếu bạn dùng XLOOKUP thay cho SUMIF ở D2, hóa đơn trả hai lần cho kết quả gì?

Tô màu các hàng thiếu (sẽ có sau)

Với danh sách dài, bạn muốn các hàng chưa khớp tự đổi màu. Việc này dùng định dạng có điều kiện, và DeskLab sẽ có bài riêng cho nó (bài L15-01); trong bài này chưa luyện được. Trước mắt, cột Có hoặc Chưa có và cột Chênh lệch đã đủ để bạn lọc ra các hàng cần xem.

Vì sao lỗi này xảy ra

Hai danh sách đến từ hai nguồn nên hiếm khi khớp hoàn toàn. Hóa đơn có thể chưa được trả, được trả nhiều lần, hoặc trả thiếu một khoản nhỏ; sao kê có thể chứa khoản bạn chưa ghi sổ. Đó là dữ liệu thật chứ không phải lỗi công thức, và việc của bạn là làm cho chúng hiện ra.

Có hai phép làm cần phân biệt. Hỏi "mã này có bên kia không" thì chỉ cần đếm số lần xuất hiện. Hỏi "bên kia tổng cộng bao nhiêu tiền cho mã này" thì cần cộng mọi hàng: hàm tìm kiếm chỉ lấy kết quả khớp đầu tiên nên bỏ sót hàng thứ hai của hóa đơn trả hai lần.

Nếu đối chiếu mà gần như mọi hàng ra "Chưa có" dù bạn thấy mã giống hệt, nguyên nhân thường là mã thừa dấu cách hoặc một bên là chữ, một bên là số. Xem bài VLOOKUP/XLOOKUP ra #N/A vì khoảng trắng, cách sửa dùng được cho cả COUNTIF.

• Gần như mọi hóa đơn ra Chưa có dù mã nhìn giống hệt.

  Nguyên nhân

  Mã bên này thừa dấu cách hoặc khác kiểu (chữ so với số) với mã bên kia, nên không khớp. Microsoft cảnh báo dấu cách thừa làm COUNTIF trả kết quả không mong đợi.

  Cách sửa

  Làm sạch mã bằng TRIM hoặc đổi cùng kiểu, như trong bài VLOOKUP/XLOOKUP ra #N/A.

• Hóa đơn trả hai lần chỉ hiện một nửa số tiền đã thanh toán.

  Nguyên nhân

  Dùng hàm tìm kiếm thay vì SUMIF: nó chỉ lấy hàng khớp đầu tiên.

  Cách sửa

  Dùng SUMIF để cộng mọi hàng cùng Số HĐ.

• Hóa đơn chưa trả ra #N/A thay vì 0 hoặc Chưa có.

  Nguyên nhân

  Hàm tìm kiếm báo #N/A khi không thấy mã, và công thức chưa bắt lỗi đó.

  Cách sửa

  Bọc bằng IFNA để thay #N/A bằng chữ bạn chọn, hoặc dùng COUNTIF và SUMIF (trên lưới DeskLab hai hàm này cho 0 khi không có hàng nào khớp).

• Chênh lệch có dấu ngược so với dự tính.

  Nguyên nhân

  Phép trừ viết ngược thứ tự: Đã thanh toán trừ Phải thu.

  Cách sửa

  Viết Phải thu trừ Đã thanh toán: =C2-D2.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Trên Excel thật

• Lưới DeskLab: COUNTIF, SUMIF, XLOOKUP và IFNA cho kết quả đúng trên lưới với tham chiếu sang trang tính khác, và hai bài tập kiểm tra điều đó. Tên trang tính có dấu cách, như Sao kê, cần đặt trong nháy đơn: 'Sao kê'!$A$2:$A$14.

• XLOOKUP: hàm này cần Excel 2021/365. Nếu máy bạn dùng Excel 2016 hoặc 2019, hãy dùng COUNTIF để đánh dấu có hay chưa có, hoặc VLOOKUP bọc bằng IFNA, và SUMIF để cộng tiền.

Câu hỏi thường gặp

Nên dùng COUNTIF hay XLOOKUP để kiểm tra một mã có trong danh sách kia không?

COUNTIF chạy trên mọi phiên bản Excel: đếm số lần mã xuất hiện, lớn hơn 0 là có. XLOOKUP cần Excel 2021/365, trả về giá trị tìm được, và có thể trả một chữ do bạn chọn khi không thấy (đối số if_not_found); IFNA cũng thay lỗi #N/A bằng giá trị bạn chọn.

Vì sao không dùng VLOOKUP hay XLOOKUP để lấy số tiền đã thanh toán?

XLOOKUP trả về giá trị của kết quả khớp đầu tiên nó tìm thấy. Khi một hóa đơn được trả hai lần, nó chỉ lấy hàng đầu. Để cộng đủ mọi hàng cùng mã, hãy dùng SUMIF.