Nguyễn Ngọc Diệp
Nguyễn Ngọc Diệp
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 1609 lượt xem

Hướng dẫn lập biên bản đối chiếu công nợ trên Excel

Mar 12 2021

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 Gitiho 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

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:

  • Bên gửi bản đối chiếu (thường là bên thu nợ)
  • Bên nhận bản đối chiếu (thường là bên phải trả nợ)
  • Các thông tin liên quan: Thời gian đối chiếu, lịch sử thông tin phát sinh và thanh toán nợ, số nợ còn lại cần thanh toán,...

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.
  • Thông tin thanh toán: Các thông tin về thanh toán trong mỗi hợp đồng bao gồm thời gian thanh toán, số tiền thanh toán, số lần thanh toán, cũng phải được theo dõi chi tiết.
  • 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 đã thanh toán được tính bằng hàm SUMIFS: =SUMIFS(ThanhToan!$C$3:$C$25,ThanhToan!$B$3:$B$25,I3,ThanhToan!$A$3:$A$25,”<=”&BB_CN!$H$2)
  • Số tiền còn nợ được tính bằng công thức Số tiền - Đã thanh toán: =K3-L3
  • Số thứ tự của hợp đồng được xác định bằng hàm COUNTIF=COUNTIF($A$3:A3,A3)

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.

Xem thêm: Giải đáp lỗi #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, #NULL! trong Excel và cách sửa

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, VLOOKUP trong bài, hãy tham khảo ngay các bài hướng dẫn của Gitiho 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












Cùng tham gia cộng đồng hỏi đáp về chủ đề Tin học văn phòng

Thảo luận 0 câu trả lời
Lượt xem 1609 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội