Cách đối chiếu các giao dịch trong bảng tính (Không cần so khớp thủ công từng dòng)

Đối chiếu hai danh sách giao dịch nghĩa là tìm ra bốn loại chênh lệch. Các dòng bị thiếu ở một bên, các dòng bị trùng lặp ở bên kia, số tiền không khớp nhau, và cùng một khoản thanh toán nhưng lại rơi vào các kỳ khác nhau. Mọi việc khác chỉ là ghi chép sổ sách xoay quanh bốn loại này.

Bản năng của chúng ta là xếp hai tệp thẳng hàng và bắt đầu so khớp từng dòng. Cách này hoạt động tốt cho đến khi đạt khoảng hai trăm dòng, sau đó nó sẽ ngốn cả một buổi chiều của bạn chỉ để tạo ra một con số không ai có thể kiểm toán được.

Hướng dẫn này sẽ giải thích lý do tại sao các bảng tính lại gặp khó khăn với tác vụ cụ thể này, ba giải pháp tạm thời mà mọi người thường tìm đến, và giới hạn của từng giải pháp. Đây là một quy trình xử lý dữ liệu, không phải lời khuyên kế toán.

Tại sao việc đối chiếu trong bảng tính lại khó hơn vẻ bề ngoài

Bảng tính so sánh các ô. Đối chiếu so sánh các sự kiện, và cùng một sự kiện hiếm khi hiển thị giống hệt nhau ở cả hai bên.

Một khoản thanh toán bằng thẻ có thể xuất hiện một lần trong sổ cái của bạn nhưng lại xuất hiện hai lần trong tệp xuất của đơn vị xử lý thanh toán, được chia thành tiền gốc và phí. Một hóa đơn nhà cung cấp có thể mang mã tham chiếu INV-0042 ở hệ thống này nhưng lại là INV42 ở hệ thống kia. Một giao dịch chuyển khoản được khởi tạo vào ngày 31 có thể được tất toán vào ngày 1, làm lệch số liệu của cả tháng.

Không có điều nào trong số này là lỗi dữ liệu. Chúng là trạng thái bình thường khi hai hệ thống cùng ghi nhận một thực tế, và không một công thức tìm kiếm nào có thể tự giải quyết được chúng.

Ngoài ra còn có một cái bẫy làm tròn khiến mọi người mắc bẫy hàng tháng. Các giá trị tiền tệ được lưu trữ ở độ chính xác dấu phẩy động đầy đủ có thể khác nhau ở chữ số thập phân thứ tư, vì vậy hai số tiền hiển thị là 1,204.50 vẫn không vượt qua được phép thử bằng nhau tuyệt đối.

Quy mô dữ liệu cũng làm thay đổi bản chất của vấn đề. Với năm mươi dòng, một người có thể ghi nhớ cả hai danh sách trong đầu. Nhưng với năm nghìn dòng, nhiệm vụ sẽ trở thành việc tìm kiếm một vài ngoại lệ ẩn giấu trong một khối lượng lớn dữ liệu trùng khớp. Sự chú ý của con người rất khó đáp ứng được việc đó.

Những cái giá bạn phải trả

Sự dây dưa cuối tháng. Chín mươi phần trăm số dòng đầu tiên được khớp chỉ trong vài phút. Nhưng vài dòng còn lại lại mất hàng giờ, bởi vì mỗi dòng đều cần con người tìm hiểu xem nó thuộc loại nào trong bốn loại chênh lệch.

Kết quả không thể kiểm toán. Khi việc so khớp được thực hiện bằng mắt thường, quá trình xử lý sẽ biến mất ngay khi đóng tệp. Sáu tuần sau, không ai có thể tái dựng lại lý do tại sao hai dòng đó lại được coi là cùng một khoản thanh toán.

Những sai sót vẫn tồn tại. Một dòng trùng lặp bị khớp sai với một đối tác khác sẽ tự triệt tiêu trong tổng số và trông có vẻ như đã được đối chiếu sạch sẽ. Tổng số khớp nhau không phải là bằng chứng cho thấy các dòng khớp nhau.

Ba loại chi phí này cộng dồn lại. Sự dây dưa kéo dài gây ra mệt mỏi, mệt mỏi dẫn đến việc làm tắt, và làm tắt chính là cách một kết quả khớp sai được ghi nhận là đúng.

Những giải pháp tạm thời mọi người thường thử

Tùy chọn 1: So sánh tổng số trước khi so khớp bất kỳ thứ gì

Bắt đầu bằng cách so sánh tổng số nhóm thay vì từng dòng. Sử dụng SUMIFS để tính tổng mỗi bên theo tháng, theo tài khoản hoặc theo đối tác, sau đó đặt hai cột cạnh nhau.

Cách này giúp xác định vị trí chênh lệch trước khi bạn mất thời gian cho nó. Nếu mười một trong số mười hai tháng khớp nhau đến từng xu, bạn chỉ cần đối chiếu một tháng thay vì cả năm.

Phương pháp này thực sự hữu ích và giúp khoanh vùng sớm. Tuy nhiên, tổng số nhóm chỉ cho bạn biết nơi xảy ra chênh lệch chứ không bao giờ chỉ ra dòng nào gây ra lỗi đó, và hai lỗi tự triệt tiêu lẫn nhau trong cùng một nhóm sẽ biến mất một cách vô hình.

Tùy chọn 2: Tạo khóa khớp lệnh và tìm kiếm

Ghép các trường xác định một sự kiện thành một khóa duy nhất, thường là ngày cộng với số tiền cộng với mã tham chiếu đã được làm sạch. Sau đó sử dụng XLOOKUP theo cả hai chiều để tìm các dòng có ở bên này nhưng thiếu ở bên kia.

Thêm COUNTIFS cho cùng một khóa đó để phát hiện các dòng trùng lặp, vì hàm tìm kiếm chỉ trả về kết quả khớp đầu tiên và âm thầm bỏ qua kết quả thứ hai. Đặt số tiền vào hàm ROUND để làm tròn đến hai chữ số thập phân trước khi đưa vào khóa, điều này giúp loại bỏ sự không khớp do dấu phẩy động đã mô tả ở trên.

Đây là phương pháp chủ lực và nó giải quyết được hầu hết các tháng. Giới hạn của nó nằm ở mặt cấu trúc: nó cần một khóa có cùng ý nghĩa ở cả hai bên. Định dạng tham chiếu khác nhau, hoặc một khoản phí bị chia thành hai dòng, sẽ ngay lập tức vô hiệu hóa phương pháp này.

Tùy chọn 3: Xử lý chủ động bốn loại chênh lệch

Thay vì một lượt so khớp chung, hãy chạy bốn lượt kiểm tra hẹp hơn. Các dòng bị thiếu được tìm ra từ việc tìm kiếm hai chiều. Các dòng trùng lặp được tìm ra từ việc đếm trên khóa. Số tiền không khớp được tìm ra bằng cách chỉ so khớp trên mã tham chiếu, sau đó so sánh các giá trị. Chênh lệch thời gian được tìm ra bằng cách so khớp trong một khoảng thời gian thay vì một ngày chính xác.

Nếu được thực hiện đúng cách, đây là phương pháp trung thực nhất, vì mỗi dòng không khớp sẽ được phân vào một danh mục cụ thể thay vì bị bỏ lại trong một đống hỗn độn.

Đây cũng là phương pháp tốn nhiều công sức nhất. Bốn lượt kiểm tra nghĩa là cần bốn cột phụ cho mỗi bên, và toàn bộ hệ thống phải được dựng lại khi một trong hai tệp xuất thay đổi thứ tự cột. Hướng dẫn của chúng tôi về làm sạch và loại bỏ dữ liệu trùng lặp sẽ đề cập đến bước chuẩn bị mà phương pháp này phụ thuộc vào.

Giới hạn chung. Cả ba phương pháp đều giả định một dòng ở bên này tương ứng với một dòng ở bên kia. Một lần tất toán duy nhất có thể bao gồm bốn mươi giao dịch. Một khoản thanh toán có thể được ghi nhận dưới dạng tiền gốc cộng với phí cộng với hoàn tiền. Trong cả hai trường hợp, việc so khớp bằng khóa đều không có điểm tựa. Đó là lý do tại sao bạn mất cả buổi chiều.

Cách đối chiếu giao dịch bằng Powerdrill Bloom

Bước 1: Tải lên cả hai tệp

Tải lên tệp xuất sổ cái và bảng sao kê đối tác cùng lúc. Powerdrill Bloom sẽ phân tích cả hai, nhờ đó các tên cột không khớp, định dạng ngày khác nhau và kiểu tham chiếu không nhất quán sẽ hiển thị rõ ràng trước khi bắt đầu so khớp.

Bước 2: Mô tả việc đối chiếu bằng ngôn ngữ tự nhiên

Yêu cầu cụ thể bốn danh mục này theo tên. Yêu cầu các dòng có trong tệp này nhưng thiếu trong tệp kia, cùng với các mã tham chiếu bị trùng lặp. Sau đó, yêu cầu tìm các khoản chênh lệch số tiền vượt quá mức sai số cho phép đã đặt ra, và các bút toán có ngày chênh lệch nhau vài ngày.

Sau đó, hãy đặt câu hỏi để giải quyết các trường hợp khó. Hỏi xem nhóm dòng nào ở một bên có tổng bằng một dòng duy nhất ở bên kia. Đó là trường hợp nhiều-đối-một mà việc so khớp bằng khóa không thể chạm tới.

Bước 3: Xuất biểu đồ, báo cáo hoặc bản trình bày

Trích xuất danh sách ngoại lệ, bảng tóm tắt giá trị không khớp theo danh mục, hoặc một ghi chú ngắn bằng văn bản cho tệp chốt sổ.

Tại sao cách này vượt trội hơn việc dựng lại quy trình so khớp hàng tháng

	Phương pháp thủ công	Powerdrill Bloom
Định dạng tham chiếu khác nhau	Tự làm sạch cả hai bên bằng tay trước	Mô tả sự khác biệt và đặt câu hỏi
Nhiều dòng khớp với một dòng	Gom nhóm thủ công	Hỏi xem những dòng nào có tổng bằng dòng đối ứng
Trùng lặp	Thêm cột đếm phụ cho mỗi bên	Được bao gồm trong danh sách ngoại lệ
Tháng tiếp theo	Dựng lại mọi cột phụ	Chỉ cần tải lên các tệp xuất mới

Dòng đầu tiên là nơi tiêu tốn phần lớn thời gian. Làm sạch các mã tham chiếu để hai hệ thống khớp nhau là công việc chuẩn bị vốn dĩ tự nó không tạo ra kết quả gì. Nó cũng phải được làm lại bất cứ khi nào định dạng tệp xuất thay đổi.

Các sai lầm thường gặp

Coi tổng số khớp nhau là đã hoàn thành đối chiếu. Hai lỗi có cùng độ lớn nhưng ngược chiều nhau sẽ tạo ra một tổng số hoàn hảo. Hãy kiểm tra số lượng dòng và giá trị không khớp, chứ không chỉ kiểm tra tổng số.

Chỉ so khớp dựa trên số tiền. Trong bất kỳ sổ cái thực tế nào, nhiều giao dịch có thể có cùng một giá trị. Việc tìm kiếm theo số tiền sẽ ghép cặp sai các giao dịch và trông có vẻ rất thuyết phục khi làm điều đó.

Bỏ qua chênh lệch làm tròn. Các giá trị hiển thị giống hệt nhau vẫn có thể không vượt qua được phép thử bằng nhau. Hãy làm tròn cả hai bên về cùng một độ chính xác trước khi so sánh chúng.

Quên chiều kiểm tra. Phép tìm kiếm một chiều chỉ tìm thấy các dòng bị thiếu ở tệp thứ hai chứ không bao giờ tìm thấy các dòng bị thiếu ở tệp thứ nhất. Hãy luôn chạy theo cả hai chiều.

Xóa các dòng đã khớp trong quá trình làm. Việc này tạo cảm giác hiệu quả nhưng lại phá hủy dấu vết kiểm toán. Thay vào đó, hãy đánh dấu các dòng bằng một cột trạng thái và giữ nguyên dữ liệu gốc.

Đối chiếu trước khi kỳ kế toán đóng lại. Các bút toán đến muộn sẽ tạo ra các chênh lệch thời gian tự giải quyết được sau đó. Việc cố gắng xử lý chúng giữa kỳ chỉ làm lãng phí công sức.

Kết luận

Đối chiếu là một bài toán phân loại, không phải bài toán so khớp. Hãy phân loại mọi dòng không khớp vào nhóm bị thiếu, trùng lặp, sai số tiền hoặc sai kỳ, và phần việc còn lại sẽ rất ít và dễ giải thích.

Điều khiến công việc này trở nên tốn kém là việc phải dựng lại hệ thống mỗi tháng, đặc biệt là khi các mã tham chiếu không khớp hoặc một khoản thanh toán tương ứng với nhiều dòng. Nếu đó là nơi tiêu tốn thời gian chốt sổ của bạn, hãy thử Powerdrill Bloom trên cả hai tệp xuất. Xem thêm các hướng dẫn của chúng tôi về gộp hai tệp Excel không cần VLOOKUP và xây dựng báo cáo ngân sách so với thực tế. Các trang báo cáo chi phí và phân tích dòng tiền sẽ đề cập đến các quy trình làm việc liên quan.

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

Đối chiếu giao dịch nghĩa là gì?

Nghĩa là xác nhận rằng hai bản ghi của cùng một hoạt động khớp nhau, và giải thích mọi chênh lệch còn lại. Các lời giải thích được chia thành bốn nhóm: dòng bị thiếu, trùng lặp, không khớp số tiền và chênh lệch thời gian.

Excel có thể tự động đối chiếu hai danh sách không?

Bản thân Excel thì không thể. Excel cung cấp cho bạn các thành phần, chủ yếu là các hàm tìm kiếm, đếm và tính tổng có điều kiện, nhưng bạn phải tự xây dựng logic so khớp và dựng lại nó khi một trong hai tệp xuất thay đổi.

Tại sao hai số tiền trông giống nhau lại không khớp?

Thường là do độ chính xác được lưu trữ. Một giá trị hiển thị dưới dạng hai chữ số thập phân có thể chứa nhiều chữ số hơn ở phía sau, vì vậy phép so sánh chính xác sẽ thất bại. Việc làm tròn cả hai bên về cùng một độ chính xác sẽ khắc phục được điều này.

Làm thế nào để xử lý một khoản thanh toán xuất hiện dưới dạng nhiều dòng?

Gom nhóm các dòng nhỏ hơn và so sánh tổng nhóm với dòng đối ứng duy nhất. Việc so khớp bằng khóa ở cấp độ dòng không thể thể hiện trường hợp này, đó là lý do tại sao đây là nguyên nhân phổ biến nhất của công việc thủ công.

Có nên xóa các dòng không khớp không?

Không. Hãy giữ lại chúng và thêm một cột trạng thái để ghi lại danh mục và lý do. Việc xóa đi sẽ làm mất dấu vết kiểm toán giúp việc đối chiếu có thể giải trình được sau này.