Một trong những công việc quan trọng của kế toán viên là lập biên bảng đối chiếu công nợ. Trong quá trình lập biên bản, có thể các bạn sẽ tốn không ít thời gian điền dữ liệu theo cách thủ công. Để giúp các bạn giải quyết vấn đề này, hôm nay HTTL sẽ giới thiệu phương pháp lập biên bản đối chiếu công nợ tự động trên Microsoft Excel.
Bạn đang xem: Cách đối chiếu công nợ bằng excel
Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ
Nội dung của biên bản đối chiếu công nợ
Với mục đích ghi chép chi tiết việc đối chiếu công nợ của doanh nghiệp, một biên bản đối chiếu công nợ gồm 3 phần chính:
Qua những thông tin trong biên bản đối chiếu công nợ, kế toán viên có nhiệm vụ kiểm tra thực trạng mua bán giữa bên mua và bên bán, đồng thời kiểm soát tình hình nợ của doanh nghiệp với các bên cung ứng cũng như của khách hàng với doanh nghiệp.
Cấu trúc biên bản đối chiếu công nợ trong Excel
Từ 3 nội dung chính của một biên bản đối chiếu công nợ đã được đề cập ở phần trên, chúng ta xác định 3 sheet Excel tương ứng phải lập.
Danh sách hợp đồng của từng khách hàng: Mỗi hợp đồng đóng vai trò là một đối tượng theo dõi công nợ riêng biệt, nghĩa là trong trường hợp một khách hàng sở hữu nhiều hợp đồng khác nhau thì kế toán viên cũng cần theo dõi các hợp đồng ấy riêng biệt. Áp dụng các nguyên tắc bù trừ, tổng sau cùng của các hợp đồng đó sẽ là tổng số công nợ cần theo dõi.Mẫu biên bản đối chiếu: Để kế toán viên dễ dàng tổng hợp thông tin cần thiết của các đối tượng trong biên bản đối chiếu công nợ, mọi thông tin sẽ được hiển trị trong cùng một sheet.
Mẫu biên bản đối chiếu công nợ trong Excel
Sau đây là các công thức nhập liệu cần dùng để lập biên bản đối chiếu công nợ trong Excel cụ thể cho từng ô thông tin:
Thông tin khách hàng
Tại ô H1, chúng ta điền công thức hàm INDEX và MATCH như sau để Excel tự động hiển thị thông tin khách hàng:
Tên người đại diện thứ 1: =INDEX(KhachHang!$D$3:$D$32,MATCH(BB_CN!$H$1,KhachHang!$A$3:$A$32,0))Chức vụ ngườiđại diện thứ 1: =INDEX(KhachHang!$E$3:$E$32,MATCH(BB_CN!$F$1,KhachHang!$A$3:$A$32,0)
Số liệu trong bảng kê khai công nợ
Số tiền còn nợ được tính bằng công thức Số tiền – Đã thanh toán:=K3-L3
Số hợp đồng
Tại đây, chúng ta cần sử dụng tham chiếu theo điều kiện: kết hợp Tên khách hàng và Số thứ tự (STT) để xác định hợp đồng liên quan bằng hàm INDEX:
=INDEX(KhachHang!$I$3:$I$32,MATCH(BB_CN!$H$1&G18:A22,KhachHang!$A$3:$A$32&KhachHang!$H$3:$H$32,0))
Sau khi nhập xong công thức, chúng ta ấn tổ hợp phím Ctrl+Shift+Enter để Excel tiến hành trả kết quả.
Lưu ý: Nếu hiện lỗi #N/A khi tìm kết quả ở các vùng STT lớn, chúng ta có thể sử dụng thêm hàm IFERROR.
Các số liệu còn lại của hợp đồng
Chúng ta sử dụng hàm VLOOKUP để xác định các thông tin còn lại của hợp đồng, cụ thể như sau:
Ngày ký:=IF($A18=””,””,VLOOKUP($A17,KhachHang!$I$3:$M$32,2,0))Số tiền:=IF($A18=””,””,VLOOKUP($A17,KhachHang!$I$3:$M$32,3,0))Đã thanh toán:=IF($A18=””,””,VLOOKUP($A17,KhachHang!$I$3:$M$32,4,0))Còn nợ:=IF($A18=””,””,VLOOKUP($A17,KhachHang!$I$3:$M$32,5,0))
Lưu ý: Trong công thức trên, việc kết hợp thêm bẫy lỗi không có số hợpđồng sẽ trả về giá trị rỗng trước, sauđó mới dùng hàm VLOOKUP.
Số tiền dưới dạng chữ
Tham khảo chi tiết cách viết số tiền bằng chữ:Làm thế nào sử dụng công thức viết số tiền bằng chữ trong kế toán
Trên đây là hướng dẫn cách lập biên bản đối chiếu công nợ trên Excel. Việc tự động điền dữ liệu của hệ thống Excel sẽ tăng độ chính xác lên gấp nhiều lần so với việc điền dữ liệu thủ công. Nhờ vậy,chúng ta có thể trình bày một biên bản phức tạp một cách khoa học và nhanh chóng chỉ với những hàm cơ bản.Nếu các bạn còn chưa nắm rõ cách thức sử dụng các hàm IF, SUMIFS, COUNTIFS, VLOOKUPtrong bài, hãy tham khảo ngay các bài hướng dẫn của HTTL nhé.
Chúc các bạn kế toán viên áp dụng thủ thuật này thật thành công!
Bài viết tham khảo khác:
Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất
Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel
Hướng dẫn cách sắp xếp ngày trong Excel
Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao
HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ
Chuyên mục: Wiki
