Bạn muốn biết cách phân bổ tiền thu hồi cho hóa đơn trong Google Sheets? Sử dụng Vlookup trong Google Sheets, chúng ta có thể dễ dàng phân bổ các khoản thanh toán phù hợp với các hóa đơn tương ứng.
Giả sử có một hóa đơn (Số RA1564) trị giá $5,000.00 được gửi đến khách hàng A. Khi bạn nhận được khoản thanh toán cho hóa đơn này, bạn có thể dễ dàng khớp nó với số hóa đơn.
Việc phân bổ các khoản thanh toán phù hợp cũng không phức tạp nếu có nhiều khoản thanh toán phù hợp. Bằng cách kết hợp công thức Vlookup và Query, bạn có thể tổng hợp các khoản thanh toán đã nhận và sau đó khớp với hóa đơn.
Tuy nhiên, khi bạn muốn phân bổ khoản thu hồi cho hóa đơn trong Google Sheets mà không có số hóa đơn hoặc bất kỳ thông tin tham chiếu nào khác ngoài tên khách hàng, việc này không dễ dàng như vậy.
Giả sử bạn đã gửi hai hóa đơn cho khách hàng A với số hóa đơn RA-1564 trị giá $5,000.00 và RA-1565 trị giá $11,000.00.
Bạn đã nhận được một khoản thanh toán một lần duy nhất trị giá $8,000.00 cho những hóa đơn này.
Lần này không có số hóa đơn để khớp. Tuy nhiên, bạn cần phân bổ các khoản thanh toán đã nhận như sau:
| Debit | Credit | Balance |
|---|---|---|
Trong bài hướng dẫn Google Sheets này, chúng ta sẽ tìm hiểu cách phân bổ (phân phối) tiền thu hồi một cách chính xác cho các hóa đơn. Tôi sẽ giải thích tất cả các tình huống trên với các ví dụ dưới đây.
Chi tiết hóa đơn (Dữ liệu mẫu) để phân bổ
Sao chép và dán bảng tài khoản này vào một tệp Google Sheets mới (phạm vi là Sheet1!A1:F12).
Bảng #1:
| Customer | Invoice Date | Invoice # | Debit | Credit | Balance |
|---|
Trước khi phân bổ các khoản thanh toán trong cột “Credit” và tìm ra số dư chưa thanh toán trong cột “Balance”, hãy thử khớp các biên nhận.
Khớp các khoản thanh toán đơn lẻ bằng cách sử dụng Vlookup trong Google Sheets
Giả sử bạn đã nhận được các khoản thanh toán sau đây.
Bảng #2:
| Invoice # | Receipt Amount |
|---|
Làm thế nào để kết hợp cả hai báo cáo (Bảng #1 và Bảng #2)?
Trong ô Sheet1!E2, nghĩa là trong hàng đầu tiên trong cột “Credit”, chèn công thức sau đây:
=ArrayFormula(IFNA(vlookup(C2:C,Sheet2!A2:B,2,0)))
Công thức Vlookup trên tìm kiếm Bảng #2 cho các khóa tìm kiếm (số hóa đơn) từ Bảng #1 để tìm sự khớp.
Nếu tìm thấy sự khớp, nó sẽ trả về giá trị (số tiền thu) từ cột thứ hai của Bảng #2.
Bạn có thể tiếp tục thêm các biên nhận mới vào Bảng #2. Vlookup sẽ tự động khớp và chèn số tiền vào Bảng #1. Vì tôi đã bao gồm các hàng tương lai (phạm vi không giới hạn) trong công thức.
Trước khi bắt đầu cách phân bổ các khoản thu hồi cho hóa đơn, hãy đề cập đến một vấn đề khác mà việc khớp trên có thể gây ra.
Đó là vấn đề về các khoản thanh toán tùy ý. Mình nghĩ là một số khoản thu hồi tương ứng với cùng một hóa đơn cũng như một khoản thanh toán đơn.
Khớp các khoản thanh toán đa lần bằng cách kết hợp Vlookup và Query trong Google Sheets
Vì mục đích giải thích, mình đang sửa đổi Bảng #2 ở trên.
Bảng #2 (Sao chép):
| Invoice # | Receipt Amount |
|---|
Ở đây, công thức Vlookup trước đó sẽ không phân bổ đúng (nên nói là “khớp”) các khoản thanh toán đã nhận!
Đầu tiên, bạn cần tổng hợp các biên nhận, tức là Bảng #2 (Sao chép) ở trên, và sử dụng nó làm “phạm vi” (bảng tìm kiếm) trong Vlookup.
Công thức Query sau đây sẽ tạo bản tóm tắt của Bảng #2 (sao chép). Kết quả sẽ giống với Bảng #2 mà không có số hóa đơn lặp lại.
=query(Sheet2!A1:B,"Select A, Sum(B) Where A is not null Group by A",1)
Đơn giản thay thế Sheet2!A2:B bằng Truy vấn (Query) này trong công thức Vlookup.
Công thức để sử dụng trong Sheet1!E2:
=ArrayFormula(IFNA(vlookup(C2:C,query(Sheet2!A1:B,"Select A, Sum(B) Where A is not null Group by A",1),2,0)))
Phân bổ các khoản thu hồi cho hóa đơn trong Google Sheets
Giả sử bạn có sự kết hợp của hai ví dụ trên (các khoản thu hồi) và cũng có các khoản thu hồi tổng hợp. Vậy làm thế nào để có được số dư tài khoản của khách hàng đúng bằng cách phân bổ các khoản thanh toán thu như vậy.
Hãy đi vào chi tiết vấn đề đầu tiên.
Chúng ta đã nhận các khoản thanh toán từ các khách hàng khác nhau như sau.
Bảng #3:
| Customer | Receipt |
|---|
Chúng ta không có số hóa đơn nào để phân bổ các khoản thu như vậy. Ngoài ra, số tiền có thể khớp hoặc không khớp với một số số tiền thu như một số trong số chúng là số tiền tổng.
Vì chúng ta có tên khách hàng giống nhau trong Bảng #1 và Bảng #3, chúng ta có thể sử dụng thông tin đó để phân bổ các khoản thu như dư nợ/partial/on account đối với các hóa đơn.
Công thức phân bổ thanh toán cho Sheet1!E2 phải được sao chép vào dưới (không phải là một công thức mảng như các ví dụ trước).
=min(D2,sumif(Sheet2!$A$2:$A,$A$2:$A,Sheet2!$B$2:$B)-sumif($A$1:A1,A2,$E$1:E1))
Công thức trên chắc chắn rằng bạn không phân bổ một số tiền lớn hơn số tiền nợ (hóa đơn) trong trường hợp các khoản thu “on account”.
Ngoài ra, giá trị của biên nhận (lump_sum receipt) đã điều chỉnh trong mỗi hàng. Trong bất kỳ hàng nào, số tiền phân bổ (credit) nhỏ hơn số tiền nợ (hóa đơn), Min sẽ trả về số tiền phân bổ.
Công thức Google Sheets để tính số dư chưa thanh toán
Gõ công thức này vào ô F2 và quên nó đi!
=ArrayFormula(if(len(A2:A),D2:D-E2:E,))
Đó là tất cả những gì về việc phân bổ các khoản thu hồi trong Google Sheets.
