Hướng dẫn làm sổ kế toán chi tiết thanh toán với khách hàng trong Excel
Trong bài viết này Học Excel Online sẽ hướng dẫn cách sử dụng các hàm Excel cơ bản để có thể lập được sổ kế toán chi tiết thanh toán với khách hàng (người mua) một cách tự động, và sổ hoàn toàn linh hoạt khi thay đổi khoảng thời gian cũng như chi tiết của từng khách hàng.
Xem nhanh
Mục đích và vai trò của sổ kế toán chi tiết công nợ phải thu
– Kế toán công nợ phải thu (thanh toán với khách hàng) là một phần hành kế toán khá quan trọng trong toàn bộ công tác kế toán của một doanh nghiệp.
– Căn cứ để ghi vào sổ này là cá phát sinh bên nợ và bên có của TK 131. Tài khoản 131 là một tài khoản lưỡng tính, tức là có thể có số dư bên Nợ, số dư bên Có.
– Sổ này dùng để phản ánh các khoản nợ phả thu, theo dõi chi tiết tình hình thanh toán các khoản nợ phải thu với khách hàng
– Sổ kế toán chi tiết thanh toán với người mua được mở chi tiết theo từng đối tượng.
Mẫu sổ kế toán chi tiết thanh toán với khách hàng (người mua)
Mẫu sổ bạn có thể có được từ nhiều nguồn khác nhau, trong bài viết này Học Excel Online sử dụng mẫu sổ như sau, bạn có thể Download file kèm ở cuối bài viết để tham khảo.
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:
Về cách ghi sổ thì bạn có thể tham khảo ở link sau:
Hướng dẫn ghi sổ chi tiết công nợ phải thu
Hướng dẫn làm sổ kế toán chi tiết thanh toán với khách hàng trong Excel
Trong phần này, Học Excel Online sẽ hướng dẫn lập sổ kế toán chi tiết thanh toán với người mua. Giả sử mình có DATA mẫu như sau:
Bảng DATA bạn sẽ nhập liệu hàng ngày
Và bảng DMKH (Danh mục khách hàng) mà đơn vị này có giao dịch, kèm theo số dư đầu kỳ của từng khách hàng.
Ở cuối bài viết sẽ có File đính kèm bài viết này để bạn có thể tham khảo.
Hàm Excel sử dụng để làm sổ kế toán chi tiết thanh toán với người mua
Cũng như các bài viết trước hướng dẫn về làm các sổ sách trong kế toán mà Học Excel Online đã giới thiệu, thì trong bài viết này để lập sổ kế toán chi tiết thanh toán với người mua ta vẫn sử dụng các hàm Excel cơ bản đó, và có sử dụng thêm một số hàm khác nữa, cụ thể như sau:
Để tính số dư đầu kỳ của mỗi khách hàng, ta dùng hàm SUMIF và hàm VLOOKUP:
Tương tự như các bài viết trước, Ta sử dụng kết hợp hàm IF, AND để lấy số liệu phát sinh trong kỳ:
Sử dụng hàm SUM để tính số cột tồn, và số tồn cuối kỳ:
Và trong bài này ta sẽ có thêm một số hàm cơ bản nữa như MAX, MIN:
Hướng dẫn hàm max so sánh hai hoặc nhiều giá trị trong excel
Cách sử dụng hàm min so sánh hai hoặc nhiều giá trị trong excel
Hướng dẫn cách thực hiện
Ta cần xác định như sau: Ta đã biết là sổ kế toán chi tiết phải thu khách hàng sẽ được chi tiết cho mỗi khách hàng, và ta sẽ lấy lên các thông tin liên quan tới mỗi khách hàng đó vào sổ chi tiết này căn cứ vào khoảng thời gian (từ ngày nào đến này nào). Vì thế, ta sẽ căn cứ vào 3 yếu tố chính là: Mã KH, từ ngày và đến ngày, để lập sổ này.
Điều đó sẽ giúp bạn linh hoạt hoàn toàn linh hoạt khi muốn xem được các giao dịch phát sinh liên quan tới khách hàng đó trong bất kỳ khoảng thời gian nào.
Lấy các thông tin về tên, địa chỉ,… của khách hàng
Để làm điều này thì bạn có thể dùng VLOOKUP như trong VD này mình áp dụng hoặc bạn có thể dùng INDEX + MATCH,…
Cụ thể như sau:
Ta sẽ căn cứ và Mã số khách hàng dò tìm trong danh mục để lấy ra tên khách hàng.
Xác định số dư đầu kỳ với hàm SUMIFS và hàm VLOOKUP
Tiếp theo, ta sẽ tính số dư đầu kỳ. Ta đã biết thì sổ kế toán chi tiết thanh toán với người mua sẽ theo dõi các phát sinh trên tài khoản 131 (phải thu khách hàng), tài khoản này có thể có số dư bên Nợ (số phải thu khách) và phát sinh bên Có (số khách hàng ứng trước cho đơn vị). Số dư ở đây là số dư tính tới thời điểm trước từ ngày (ô G8).
Ta phải đặt công thức như thế nào để khi ta thay đổi thời gian trong ô G8 thì số dư tính tới trước thời điểm đó sẽ luôn đúng (nó có thể là dư Nợ hoặc là dư Có và ta không biết trước được điều này). Cụ thể ta sẽ dùng công thức như sau:
Công thức ở ô I15 (số dư đầu kỳ bên Nợ):
=MAX(VLOOKUP(G10,DMKH!A3:H6,7,0) + SUMIFS(DATA!E3:E251,DATA!A3:A251,”<“&G8,DATA!H3:H251,G10)- VLOOKUP(G10,DMKH!A3:H6,8,0) – SUMIFS(DATA!E3:E251,DATA!A3:A251,”<“&G8,DATA!G3:G251,G10),0)
Công thức ở ô J15 (số dư đầu kỳ bên Có):
=-MIN(VLOOKUP(G10,DMKH!A3:H6,7,0) + SUMIFS(DATA!E3:E251,DATA!A3:A251,”<“&G8,DATA!H3:H251,G10)- VLOOKUP(G10,DMKH!A3:H6,8,0) – SUMIFS(DATA!E3:E251,DATA!A3:A251,”<“&G8,DATA!G3:G251,G10),0)
Với 2 công thức như trên ta sẽ giải quyết được vấn đề trên, ta sẽ luôn xác định được số dư đầu kỳ 1 cách chính xác tại bất kỳ thời điểm nào.
Thực ra thì bạn chỉ cần thay hàm MAX trong công thức thứ nhất thành – MIN là sẽ có công thức thứ 2, các phần còn lại thì 2 công thức này tương tự.
Lấy lên tình hình phát sinh trong kỳ
Khi đã tính được số dư đầu kỳ với công thức như trên, thì bây giờ ta sẽ lấy lên các phát sinh trong kỳ liên quan tới khách hàng. Và mình nghĩ công thức này bạn sẽ thấy nhẹ nhõm hơn với công thức tính số dư đầu kỳ , cụ thể như sau:
Tại ô D7 bạn gõ vào công thức như sau để láy lên ngày tháng chứng từ:
=IF(AND(DATA!A3>=$G$8, DATA!A3 <=$G$9,OR(DATA!H3=$G$10,DATA!I3=$G$10)),DATA!A3,””)
Ngày tháng chứng từ ở đây phải thoả mãn các điều kiện sau:
- Phải >= từ ngày (G8)
- Phải <= đến ngày (G9)
- Và hoặc là CT_NO hoặc là CT_CO = Mã số khách hàng (G10)
Với các trường thông tin còn lại: Số liệu chứng từ, diễn giải, TKDU, Số phát sinh Nợ, Số phát sinh Có bạn hoàn toàn có thể làm tương tự công thức trên, hoặc có cách nhanh hơn là ta sẽ căn cứ vào thông tin Ngày tháng chứng từ của công thức trên, cụ thể như sau:
Tính cột số dư
Về số dư cuối kỳ thì cũng sẽ kết hợp thêm hàm MAX, MIN cùng vơi hàm SUM. Tuy nhiên, nó dễ dàng hơn so với việc tính số dư đầu kỳ .
Công thức để xác định số dư Nợ như sau:
=MAX($I$15 + SUM($G$17:G17) -$J$15 – SUM($H$17:H17),0)
Và công thức để xác định số dư Có:
=-MIN($I$15 + SUM($G$17:G17) -$J$15 – SUM($H$17:H17),0)
Tuy là hai công thức như bạn chỉ cần thay đổi MAX trong công thức để tính dư Nợ thành -MIN trong công thức để tính dư Có.
Tới đây thì cơ bản là bạn đã hoàn thành sổ kế toán chi tiết thanh toán vói người mua rồi, giờ chỉ cần FillDown các công thức trên xuống cho các ô còn lại, và sau đó tạo 1 cột phụ với mục đích để lọc và cột phụ này sẽ có công thức đơn giản như sau:
=IF(D17=””,””,”x”)
Mỗi thay đổi mã khách hàng cũng như thời gian từ ngày đến này thì bạn chỉ cần lọc cột phụ này là bạn đã có ngay các thông tin cũng như tình hình phát sinh của khách hàng mà bạn chọn đó
Một vài lưu ý
Với cách làm khá đơn giản này, chúng ta chỉ cần sử dụng các hàm Excel cơ bản cùng với một số suy luận không quá phức tạp là bạn đã có thể lập được sổ kế toán chi tiết thanh toán với khách hàng một cách khá dễ dàng. Bên cạch đó ta cũng cần lưu ý một vài điểm như sau:
– Với cách làm này thì nêu số lượng DATA của bạn lớn thì File Excel của bạn sẽ khá nặng và sẽ chậm đi phần nào vì cách này đòi hỏi dữ liệu bên DATA của bạn có bao nhiêu dòng thì bên sổ kế toán chi tiết này cũng phải có từng ấy dòng.
– Để khắc phục được tính trạng trên thì sẽ có một số cách như sau: dùng công thức mảng hay nâng cao hơn thì VBA, SQL,… Tuy nhiên, do mục đích chính của bài viết này là hướng dẫn lập sổ kế toán chi tiết thanh toán với khách hàng bằng cách sử dụng các hàm Excel cơ bản mà phần đông chúng ta đều có thể tự thay thực hiện được. Nên với những kiến thức nâng cao hơn mình xin phép không đề cập trong bài viết này, thay vào đó Học Excel Online sẽ có các bài viết riêng để hướng dẫn cách làm áp dụng các kiến thức nâng cao hơn đó trong thời gian tới.
Kết luận
Tới đây Học Excel Online hy vọng bạn đã nắm được cách để có thể tự mình lập được sổ kế toán chi tiết thanh toán với khách hàng và sổ sẽ hoàn toàn linh hoạt với mỗi khách hàng cũng như là khoảng thời gian mà bạn muốn xem. Bạn có thể tự tay xây dựng DATA và sử dụng các hàm Excel như đã hướng dẫn ở trên để thực hiện và nếu trong quá trình làm chẳng may gặp vấn đề gì đó thì bạn có thể để lại Comment dưới bài viết để được hỗ trợ thêm. Goodluck!
Nếu bạn cần file để tham khảo thì nó nằm ngay đây: Hướng dẫn làm sổ kế toán chi tiết thanh toán với khách hàng trong Excel
Hướng dẫn cách làm sổ kế toán chi tiết vật tư, sản phẩm, hàng hoá trên excel
Hướng dẫn lập sổ nhật ký chung bằng hàm “heosql”
Hướng dẫn làm sổ quỹ tiền mặt, sổ kế toán chi tiết tiền mặt trên Excel
Hướng dẫn cách làm sổ kế toán chi tiết tiền gửi ngân hàng trên Excel
Hướng dẫn cách tạo phiếu thu, phiếu chi trên excel.
