Hỗ trợ code VBA tính lãi chậm thanh toán

strongman4789 · 27/9/21

Tôi mày mò viết code VBA để tính lãi chậm thanh toán của các khách hàng (ví dụ như tính lãi cho việc bán các căn hộ) với các dữ liệu như sau:
- Sheet “DL_SP”: Là danh mục các sản phẩm
- Sheet “DL_Han_TT”: Là danh mục các kỳ hạn than toán của các sản phẩm
- Sheet “DL_TT”: Là dữ liệu các đợt thanh toán thực tế của các khách hàng
Yêu cầu tính lãi quá hạn: Nếu khách hàng chậm thanh toán kỳ thanh toán bất kỳ thi sẽ phải chịu lãi phạt đối =Số tiền quá hạn thực tế * Số ngày quá hạn thực tế*Lãi suất quá hạn/Số ngày của năm.
Khách hàng thanh toán trước hạn thì không được hưởng lãi.

Cách đặt công thức hàm của tôi:
=Tinh_lai_phat(Ma_SP,DL_Han_TT,DL_TT,LS_QH,Ngay_Tinh_lai,Ngay_1_Nam)
Trong đó:
- Ma_SP: Mã của sản phẩm tính lãi
DL_Han_TT: Vùng dữ liệu các kỳ hạn thanh toán của các sản phẩm (gồm 3 cột theo đúng thứ tự như trong sheet)

-DL_TT: Vùng dữ liệu các đợt thanh toán thực tế của các khách hàng (gồm 3 cột theo đúng thứ tự như trong sheet (gồm 3 cột theo đúng thứ tự như trong sheet)
(02 sheet trên đều phải sắp xếp theo cột “Ngày thanh toán” hoặc “Ngày” của kỳ hạn theo thứ tự tăng dần (nếu không hàm sẽ tính sai)
- LS_QH: Lãi suất quá hạn (/năm)
- Ngay_Tinh_lai: Ngày tính lãi quá hạn
- Ngay_1_Nam: Số ngày của 1 năm (mặc định 360 ngày)

Tôi gửi dữ liệu test và code trong file gửi kèm và mong các bạn giúp đỡ:
1. Code của tôi viết để tính lãi theo yêu cầu trên đã đúng chưa?
2. Tôi thấy thuật toán của tôi là chưa tối ưu và dẫn tới tính chậm.
Như trong dữ liệu test của tôi có 230 sản phẩm với tổng số 1.150 kỳ hạn thanh toán và 1.791 giao dịch thanh toán thực tế mà máy phải tính mất 6~7 giây.
Trong đó tôi thấy:
2.1. Với mỗi sản phẩm, code của tôi vẫn phải quét tất cả các kỳ hạn thanh toán của các sản phẩm (ở đây là 1.150 kỳ hạn) để lấy dữ liệu các kỳ hạn thanh toán của sản phẩm đó (trong khi 1 sản phầm chỉ 5 kỳ hạn thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các kỳ hạn thanh toán của riêng sản phẩm đó.
2.2. Với mỗi kỳ hạn thanh toán của 1 sản phẩm, code của tôi vẫn phải quét tất cả các giao dịch thanh toán của tất cả các sản phẩm (ở đây là 1.791 giao dịch) để lấy dữ liệu các giao dịch thanh toán của sản phẩm đó (trong khi 1 sản phẩm thường chỉ 5~10 giao dịch thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các giao dịch thanh toán của riêng sản phẩm đó.
2.3. Thuật toán đang phải xử lý trên dữ liệu đã được sắp xếp ngày tháng theo thứ tự tăng dần, nếu không sẽ tính sai. Tôi chưa tìm ra cách sắp xếp nhanh dữ liệu trong hàm để không phụ thuộc vào việc dữ liệu có được sắp xếp hay chưa.

Trong đó, về vấn đề nêu tại 2.1 và 2.2, tôi có ý tưởng là: Với mỗi sản phẩm, đầu tiên sẽ copy dữ liệu các kỳ hạn thanh toán và dữ liệu các đợt thanh toán của riêng sản phẩm đó ra 2 mảng con riêng, sau đó chỉ xử lý trên 2 mảng con đó (nhưng tôi chưa tìm ra cách nào để copy nhanh dữ liệu này). Nếu xử lý nhanh được cái này thì tốc độ tính toán sẽ được cải thiện đáng kể.

Mong nhận được hỗ trợ của các ACE.

Phuocam · 27/9/21

strongman4789 đã viết:
Tôi mày mò viết code VBA để tính lãi chậm thanh toán của các khách hàng (ví dụ như tính lãi cho việc bán các căn hộ) với các dữ liệu như sau:
- Sheet “DL_SP”: Là danh mục các sản phẩm
- Sheet “DL_Han_TT”: Là danh mục các kỳ hạn than toán của các sản phẩm
- Sheet “DL_TT”: Là dữ liệu các đợt thanh toán thực tế của các khách hàng
Yêu cầu tính lãi quá hạn: Nếu khách hàng chậm thanh toán kỳ thanh toán bất kỳ thi sẽ phải chịu lãi phạt đối =Số tiền quá hạn thực tế * Số ngày quá hạn thực tế*Lãi suất quá hạn/Số ngày của năm.
Khách hàng thanh toán trước hạn thì không được hưởng lãi.

Cách đặt công thức hàm của tôi:
=Tinh_lai_phat(Ma_SP,DL_Han_TT,DL_TT,LS_QH,Ngay_Tinh_lai,Ngay_1_Nam)
Trong đó:
- Ma_SP: Mã của sản phẩm tính lãi
DL_Han_TT: Vùng dữ liệu các kỳ hạn thanh toán của các sản phẩm (gồm 3 cột theo đúng thứ tự như trong sheet)

-DL_TT: Vùng dữ liệu các đợt thanh toán thực tế của các khách hàng (gồm 3 cột theo đúng thứ tự như trong sheet (gồm 3 cột theo đúng thứ tự như trong sheet)
(02 sheet trên đều phải sắp xếp theo cột “Ngày thanh toán” hoặc “Ngày” của kỳ hạn theo thứ tự tăng dần (nếu không hàm sẽ tính sai)
- LS_QH: Lãi suất quá hạn (/năm)
- Ngay_Tinh_lai: Ngày tính lãi quá hạn
- Ngay_1_Nam: Số ngày của 1 năm (mặc định 360 ngày)

Tôi gửi dữ liệu test và code trong file gửi kèm và mong các bạn giúp đỡ:
1. Code của tôi viết để tính lãi theo yêu cầu trên đã đúng chưa?
2. Tôi thấy thuật toán của tôi là chưa tối ưu và dẫn tới tính chậm.
Như trong dữ liệu test của tôi có 230 sản phẩm với tổng số 1.150 kỳ hạn thanh toán và 1.791 giao dịch thanh toán thực tế mà máy phải tính mất 6~7 giây.
Trong đó tôi thấy:
2.1. Với mỗi sản phẩm, code của tôi vẫn phải quét tất cả các kỳ hạn thanh toán của các sản phẩm (ở đây là 1.150 kỳ hạn) để lấy dữ liệu các kỳ hạn thanh toán của sản phẩm đó (trong khi 1 sản phầm chỉ 5 kỳ hạn thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các kỳ hạn thanh toán của riêng sản phẩm đó.
2.2. Với mỗi kỳ hạn thanh toán của 1 sản phẩm, code của tôi vẫn phải quét tất cả các giao dịch thanh toán của tất cả các sản phẩm (ở đây là 1.791 giao dịch) để lấy dữ liệu các giao dịch thanh toán của sản phẩm đó (trong khi 1 sản phẩm thường chỉ 5~10 giao dịch thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các giao dịch thanh toán của riêng sản phẩm đó.
2.3. Thuật toán đang phải xử lý trên dữ liệu đã được sắp xếp ngày tháng theo thứ tự tăng dần, nếu không sẽ tính sai. Tôi chưa tìm ra cách sắp xếp nhanh dữ liệu trong hàm để không phụ thuộc vào việc dữ liệu có được sắp xếp hay chưa.

Trong đó, về vấn đề nêu tại 2.1 và 2.2, tôi có ý tưởng là: Với mỗi sản phẩm, đầu tiên sẽ copy dữ liệu các kỳ hạn thanh toán và dữ liệu các đợt thanh toán của riêng sản phẩm đó ra 2 mảng con riêng, sau đó chỉ xử lý trên 2 mảng con đó (nhưng tôi chưa tìm ra cách nào để copy nhanh dữ liệu này). Nếu xử lý nhanh được cái này thì tốc độ tính toán sẽ được cải thiện đáng kể.

Mong nhận được hỗ trợ của các ACE.

Nếu dữ liệu sort theo sản phẩm trước, rồi đến ngày tháng từ nhỏ đến lớn => viết lại => tính nhanh được một chút.

Cách hợp lý là chuyển qua dùng sub.

.

VetMini · 27/9/21

6-7 giây thì chết ai? Chú trọng vào tốc độ là sai mục đích.

Loại vấn đề này chỉ nên chú trọng vào quy trình:
Bảng Hạn thanh toán, và quá trình thanh toán đáng lẽ phải được sắp xếp theo mã sản phẩm, và ngày.
Chưa kể đến thuật toán. Chỉ bảng đã sắp xếp kiểu này mới giúp được kiểm soát dữ liệu.

strongman4789 · 7/10/21

VetMini đã viết:
6-7 giây thì chết ai? Chú trọng vào tốc độ là sai mục đích.

Loại vấn đề này chỉ nên chú trọng vào quy trình:
Bảng Hạn thanh toán, và quá trình thanh toán đáng lẽ phải được sắp xếp theo mã sản phẩm, và ngày.
Chưa kể đến thuật toán. Chỉ bảng đã sắp xếp kiểu này mới giúp được kiểm soát dữ liệu.

Thuật toán mình kiểm tra, đối chiếu với cách tính thủ công thấy đúng.
Bạn góp ý sắp xếp lại dữ liệu mình thấy không cần thiết vì việc này cũng không cải thiện được tốc độ tính toán và mình không muốn thay đổi dữ liệu gốc (dữ liệu gốc là nhập theo trình tự thời gian)

Bài đã được tự động gộp: 7/10/21

strongman4789 đã viết:
Thuật toán mình kiểm tra, đối chiếu với cách tính thủ công thấy đúng.
Bạn góp ý sắp xếp lại dữ liệu mình thấy không cần thiết vì việc này cũng không cải thiện được tốc độ tính toán và mình không muốn thay đổi dữ liệu gốc (dữ liệu gốc là nhập theo trình tự thời gian)

May mắn và có duyên lại được bạn góp ý, trân trọng cảm ơn bạn vì những góp ý, hỗ trợ chân thành.

HieuCD · 7/10/21

strongman4789 đã viết:
Tôi mày mò viết code VBA để tính lãi chậm thanh toán của các khách hàng (ví dụ như tính lãi cho việc bán các căn hộ) với các dữ liệu như sau:
- Sheet “DL_SP”: Là danh mục các sản phẩm
- Sheet “DL_Han_TT”: Là danh mục các kỳ hạn than toán của các sản phẩm
- Sheet “DL_TT”: Là dữ liệu các đợt thanh toán thực tế của các khách hàng
Yêu cầu tính lãi quá hạn: Nếu khách hàng chậm thanh toán kỳ thanh toán bất kỳ thi sẽ phải chịu lãi phạt đối =Số tiền quá hạn thực tế * Số ngày quá hạn thực tế*Lãi suất quá hạn/Số ngày của năm.
Khách hàng thanh toán trước hạn thì không được hưởng lãi.

Cách đặt công thức hàm của tôi:
=Tinh_lai_phat(Ma_SP,DL_Han_TT,DL_TT,LS_QH,Ngay_Tinh_lai,Ngay_1_Nam)
Trong đó:
- Ma_SP: Mã của sản phẩm tính lãi
DL_Han_TT: Vùng dữ liệu các kỳ hạn thanh toán của các sản phẩm (gồm 3 cột theo đúng thứ tự như trong sheet)

-DL_TT: Vùng dữ liệu các đợt thanh toán thực tế của các khách hàng (gồm 3 cột theo đúng thứ tự như trong sheet (gồm 3 cột theo đúng thứ tự như trong sheet)
(02 sheet trên đều phải sắp xếp theo cột “Ngày thanh toán” hoặc “Ngày” của kỳ hạn theo thứ tự tăng dần (nếu không hàm sẽ tính sai)
- LS_QH: Lãi suất quá hạn (/năm)
- Ngay_Tinh_lai: Ngày tính lãi quá hạn
- Ngay_1_Nam: Số ngày của 1 năm (mặc định 360 ngày)

Tôi gửi dữ liệu test và code trong file gửi kèm và mong các bạn giúp đỡ:
1. Code của tôi viết để tính lãi theo yêu cầu trên đã đúng chưa?
2. Tôi thấy thuật toán của tôi là chưa tối ưu và dẫn tới tính chậm.
Như trong dữ liệu test của tôi có 230 sản phẩm với tổng số 1.150 kỳ hạn thanh toán và 1.791 giao dịch thanh toán thực tế mà máy phải tính mất 6~7 giây.
Trong đó tôi thấy:
2.1. Với mỗi sản phẩm, code của tôi vẫn phải quét tất cả các kỳ hạn thanh toán của các sản phẩm (ở đây là 1.150 kỳ hạn) để lấy dữ liệu các kỳ hạn thanh toán của sản phẩm đó (trong khi 1 sản phầm chỉ 5 kỳ hạn thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các kỳ hạn thanh toán của riêng sản phẩm đó.
2.2. Với mỗi kỳ hạn thanh toán của 1 sản phẩm, code của tôi vẫn phải quét tất cả các giao dịch thanh toán của tất cả các sản phẩm (ở đây là 1.791 giao dịch) để lấy dữ liệu các giao dịch thanh toán của sản phẩm đó (trong khi 1 sản phẩm thường chỉ 5~10 giao dịch thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các giao dịch thanh toán của riêng sản phẩm đó.
2.3. Thuật toán đang phải xử lý trên dữ liệu đã được sắp xếp ngày tháng theo thứ tự tăng dần, nếu không sẽ tính sai. Tôi chưa tìm ra cách sắp xếp nhanh dữ liệu trong hàm để không phụ thuộc vào việc dữ liệu có được sắp xếp hay chưa.

Trong đó, về vấn đề nêu tại 2.1 và 2.2, tôi có ý tưởng là: Với mỗi sản phẩm, đầu tiên sẽ copy dữ liệu các kỳ hạn thanh toán và dữ liệu các đợt thanh toán của riêng sản phẩm đó ra 2 mảng con riêng, sau đó chỉ xử lý trên 2 mảng con đó (nhưng tôi chưa tìm ra cách nào để copy nhanh dữ liệu này). Nếu xử lý nhanh được cái này thì tốc độ tính toán sẽ được cải thiện đáng kể.

Mong nhận được hỗ trợ của các ACE.

1. Code của tôi viết để tính lãi theo yêu cầu trên đã đúng chưa? Cách tính ra kết quả sai
2. Muốn nhanh, copy vào vùng tạm, sort theo ngày và sản phẩm

strongman4789 · 7/10/21

HieuCD đã viết:
1. Code của tôi viết để tính lãi theo yêu cầu trên đã đúng chưa? Cách tính ra kết quả sai
2. Muốn nhanh, copy vào vùng tạm, sort theo ngày và sản phẩm

Mình đã kiểm tra đối chiếu với kết quả tính thủ công và thấy đúng, chưa phát hiện ra chỗ nào tính sai. Bạn thấy chỗ sai chỉ giúp mình nhé

HieuCD · 7/10/21

strongman4789 đã viết:
Mình đã kiểm tra đối chiếu với kết quả tính thủ công và thấy đúng, chưa phát hiện ra chỗ nào tính sai. Bạn thấy chỗ sai chỉ giúp mình nhé

12% là lãi năm quy định 360 ngày, theo function trả quá hạn đúng 1 năm lãi suất >12%
Dữ liệu nhiều và phức tạp khó kiểm tra, nên bắt đầu với dữ liệu đơn giản

strongman4789 · 7/10/21

HieuCD đã viết:
12% là lãi năm quy định 360 ngày, theo function trả quá hạn đúng 1 năm lãi suất >12%
Dữ liệu nhiều và phức tạp khó kiểm tra, nên bắt đầu với dữ liệu đơn giản

Cảm ơn bạn, mình sẽ kiểm tra lại trên file bạn gửi. Nhưng nếu quy định tính lãi suất 1 năm có 360 ngày (nhiều ngân hàng tính như vậy, do thoả thuận) thì nếu mình chậm thực tế 1 năm thì mình sẽ phải chịu lãi nhiều hơn 12% (12% x 365/360)

HieuCD · 8/10/21

strongman4789 đã viết:
Cảm ơn bạn, mình sẽ kiểm tra lại trên file bạn gửi. Nhưng nếu quy định tính lãi suất 1 năm có 360 ngày (nhiều ngân hàng tính như vậy, do thoả thuận) thì nếu mình chậm thực tế 1 năm thì mình sẽ phải chịu lãi nhiều hơn 12% (12% x 365/360)

Mình không biết thực tế tính như thế nào, theo mình tròn năm 365 ngày lãi suất là 12% hợp lý hơn

strongman4789 · 8/10/21

HieuCD đã viết:
Mình không biết thực tế tính như thế nào, theo mình tròn năm 365 ngày lãi suất là 12% hợp lý hơn

Trong hàm của mình có tham số này mà, mình đang để mặc định (nếu ko đưa tham số) là 360, nếu đưa tham số thì hàm sẽ tính theo tham số đó

HieuCD · 8/10/21

strongman4789 đã viết:
Trong hàm của mình có tham số này mà, mình đang để mặc định (nếu ko đưa tham số) là 360, nếu đưa tham số thì hàm sẽ tính theo tham số đó

Ý mình vẫn mặc định 1 năm có 360 ngày, nhưng trong 1 năm thực tế (365 ngày) lãi suất không được vượt quá 12%

strongman4789 · 8/10/21

HieuCD đã viết:
Ý mình vẫn mặc định 1 năm có 360 ngày, nhưng trong 1 năm thực tế (365 ngày) lãi suất không được vượt quá 12%

Sửa code theo ý bạn thì được nhưng thực tế nó sẽ ko thế. Nếu lãi suất 12%/năm và ngân hàng quy ước 1 năm 360 ngày thì lãi suất ngày sẽ là 12%/360, và ls thực tế 1 năm có 365 ngày sẽ là 12%*365/360. Cái này là quy tắc chung rồi, chỉ khác nhau thoả thuận là 360 hay 365 thôi.

strongman4789 · 8/10/21

strongman4789 đã viết:
Cảm ơn bạn, mình sẽ kiểm tra lại trên file bạn gửi. Nhưng nếu quy định tính lãi suất 1 năm có 360 ngày (nhiều ngân hàng tính như vậy, do thoả thuận) thì nếu mình chậm thực tế 1 năm thì mình sẽ phải chịu lãi nhiều hơn 12% (12% x 365/360)

HieuCD đã viết:
12% là lãi năm quy định 360 ngày, theo function trả quá hạn đúng 1 năm lãi suất >12%
Dữ liệu nhiều và phức tạp khó kiểm tra, nên bắt đầu với dữ liệu đơn giản

Mình đã kiểm tra lại và thấy kết quả đúng. Tiền lãi sẽ thay đổi nếu số ngày quy ước/năm thay đổi (file mình tắt chức năng tự động tính do nó tính hơi lâu nên khi thay đổi tham số phải cho tính lại để nó cập nhật).

HieuCD · 8/10/21

strongman4789 đã viết:
Mình đã kiểm tra lại và thấy kết quả đúng. Tiền lãi sẽ thay đổi nếu số ngày quy ước/năm thay đổi (file mình tắt chức năng tự động tính do nó tính hơi lâu nên khi thay đổi tham số phải cho tính lại để nó cập nhật).

Sai ở vùng tô màu cam

HieuCD · 9/10/21

strongman4789 đã viết:
Tôi mày mò viết code VBA để tính lãi chậm thanh toán của các khách hàng (ví dụ như tính lãi cho việc bán các căn hộ) với các dữ liệu như sau:
- Sheet “DL_SP”: Là danh mục các sản phẩm
- Sheet “DL_Han_TT”: Là danh mục các kỳ hạn than toán của các sản phẩm
- Sheet “DL_TT”: Là dữ liệu các đợt thanh toán thực tế của các khách hàng
Yêu cầu tính lãi quá hạn: Nếu khách hàng chậm thanh toán kỳ thanh toán bất kỳ thi sẽ phải chịu lãi phạt đối =Số tiền quá hạn thực tế * Số ngày quá hạn thực tế*Lãi suất quá hạn/Số ngày của năm.
Khách hàng thanh toán trước hạn thì không được hưởng lãi.

Cách đặt công thức hàm của tôi:
=Tinh_lai_phat(Ma_SP,DL_Han_TT,DL_TT,LS_QH,Ngay_Tinh_lai,Ngay_1_Nam)
Trong đó:
- Ma_SP: Mã của sản phẩm tính lãi
DL_Han_TT: Vùng dữ liệu các kỳ hạn thanh toán của các sản phẩm (gồm 3 cột theo đúng thứ tự như trong sheet)

-DL_TT: Vùng dữ liệu các đợt thanh toán thực tế của các khách hàng (gồm 3 cột theo đúng thứ tự như trong sheet (gồm 3 cột theo đúng thứ tự như trong sheet)
(02 sheet trên đều phải sắp xếp theo cột “Ngày thanh toán” hoặc “Ngày” của kỳ hạn theo thứ tự tăng dần (nếu không hàm sẽ tính sai)
- LS_QH: Lãi suất quá hạn (/năm)
- Ngay_Tinh_lai: Ngày tính lãi quá hạn
- Ngay_1_Nam: Số ngày của 1 năm (mặc định 360 ngày)

Tôi gửi dữ liệu test và code trong file gửi kèm và mong các bạn giúp đỡ:
1. Code của tôi viết để tính lãi theo yêu cầu trên đã đúng chưa?
2. Tôi thấy thuật toán của tôi là chưa tối ưu và dẫn tới tính chậm.
Như trong dữ liệu test của tôi có 230 sản phẩm với tổng số 1.150 kỳ hạn thanh toán và 1.791 giao dịch thanh toán thực tế mà máy phải tính mất 6~7 giây.
Trong đó tôi thấy:
2.1. Với mỗi sản phẩm, code của tôi vẫn phải quét tất cả các kỳ hạn thanh toán của các sản phẩm (ở đây là 1.150 kỳ hạn) để lấy dữ liệu các kỳ hạn thanh toán của sản phẩm đó (trong khi 1 sản phầm chỉ 5 kỳ hạn thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các kỳ hạn thanh toán của riêng sản phẩm đó.
2.2. Với mỗi kỳ hạn thanh toán của 1 sản phẩm, code của tôi vẫn phải quét tất cả các giao dịch thanh toán của tất cả các sản phẩm (ở đây là 1.791 giao dịch) để lấy dữ liệu các giao dịch thanh toán của sản phẩm đó (trong khi 1 sản phẩm thường chỉ 5~10 giao dịch thanh toán). Tôi chưa tìm ra cách trích xuất nhanh các giao dịch thanh toán của riêng sản phẩm đó.
2.3. Thuật toán đang phải xử lý trên dữ liệu đã được sắp xếp ngày tháng theo thứ tự tăng dần, nếu không sẽ tính sai. Tôi chưa tìm ra cách sắp xếp nhanh dữ liệu trong hàm để không phụ thuộc vào việc dữ liệu có được sắp xếp hay chưa.

Trong đó, về vấn đề nêu tại 2.1 và 2.2, tôi có ý tưởng là: Với mỗi sản phẩm, đầu tiên sẽ copy dữ liệu các kỳ hạn thanh toán và dữ liệu các đợt thanh toán của riêng sản phẩm đó ra 2 mảng con riêng, sau đó chỉ xử lý trên 2 mảng con đó (nhưng tôi chưa tìm ra cách nào để copy nhanh dữ liệu này). Nếu xử lý nhanh được cái này thì tốc độ tính toán sẽ được cải thiện đáng kể.

Mong nhận được hỗ trợ của các ACE.

Đang rảnh

Mã:

  Option Explicit
  Dim dic As Object, addHan$, addTT$

Function LaiPhat(sp$, rngHan As Range, rngTT As Range, ls#, NgayTL As Date, Optional NgayNam& = 360) As Double
  If addHan <> rngHan.Parent.Name & "!" & rngHan.Address(0, 0) Or _
                    addTT <> rngTT.Parent.Name & "!" & rngTT.Address(0, 0) Then
    Call CreateDic(rngHan, rngTT, ls, NgayTL, NgayNam)
    addHan = rngHan.Parent.Name & "!" & rngHan.Address(0, 0)
    addTT = rngTT.Parent.Name & "!" & rngTT.Address(0, 0)
  End If
  If dic.exists(sp) Then LaiPhat = dic.Item(sp)
End Function

Private Sub CreateDic(rngHan, rngTT, ls, NgayTL, NgayNam)
  Dim arr(), aHan(), aTT(), aNgay(), tmp(), sp
  Dim ngay As Date, lsNgay#, TienLai#
  Dim sRow&, fR&, i&, k&, ik&
 
  Set dic = CreateObject("scripting.dictionary")
  aHan = rngHan.Value ' Han TT
  aTT = rngTT.Value ' Thanh toan
  If aHan(1, 2) > aTT(1, 2) Then ngay = aTT(1, 2) Else ngay = aHan(1, 2) 'Ngay dau tien
  fR = ngay - 1 'Dong luu so dong du lieu
  ReDim aNgay(fR To NgayTL, 1 To 2)
  sRow = UBound(aHan)
  For i = 1 To sRow
    If aHan(i, 2) < NgayTL Then
      If dic.exists(aHan(i, 1)) = False Then
        k = k + 1
        dic.Add aHan(i, 1), k
        ReDim Preserve arr(1 To k)
        arr(k) = aNgay
      End If
      ik = dic.Item(aHan(i, 1))
      arr(ik)(fR, 1) = arr(ik)(fR, 1) + 1
      arr(ik)(aHan(i, 2), 1) = arr(ik)(aHan(i, 2), 1) + aHan(i, 3)
    End If
  Next i
  sRow = UBound(aTT)
  For i = 1 To sRow
    If aTT(i, 2) < NgayTL Then
      If dic.exists(aTT(i, 1)) = True Then
        ik = dic.Item(aTT(i, 1))
        If arr(ik)(aTT(i, 2), 1) = Empty Then arr(ik)(fR, 1) = arr(ik)(fR, 1) + 1
        arr(ik)(aTT(i, 2), 2) = arr(ik)(aTT(i, 2), 2) + aTT(i, 3)
      End If
    End If
  Next i
  lsNgay = ls / NgayNam
  For Each sp In dic.keys
    ik = dic.Item(sp)
    ReDim tmp(1 To arr(ik)(fR, 1), 1 To 3)
    k = 0
    For i = ngay To NgayTL
      If arr(ik)(i, 1) > 0 Or arr(ik)(i, 2) > 0 Then
        k = k + 1
        tmp(k, 1) = arr(ik)(i, 1)
        tmp(k, 2) = arr(ik)(i, 2)
        tmp(k, 3) = i
      End If
    Next i
    dic.Item(sp) = CreateLaiPhat(tmp, lsNgay, NgayTL) 'Tien lai phat
  Next sp
End Sub

Private Function CreateLaiPhat(arr, lsNgay, NgayTL) As Double
  Dim no#, co#, TL#, i&

  For i = 1 To UBound(arr)
    If arr(i, 2) > 0 Then
      If no = 0 Then
        co = co + arr(i, 2)
      ElseIf no > 0 Then
        If no >= arr(i, 2) Then
          TL = TL - arr(i, 2) * lsNgay * (NgayTL - arr(i, 3))
          no = no - arr(i, 2)
        Else
          TL = TL - no * lsNgay * (NgayTL - arr(i, 3))
          co = arr(i, 2) - no
          no = 0
        End If
      End If
    End If
    If arr(i, 1) > 0 Then
      If co = 0 Then
        TL = TL + arr(i, 1) * lsNgay * (NgayTL - arr(i, 3))
        no = no + arr(i, 1)
      ElseIf co > 0 Then
        If co >= arr(i, 1) Then
          co = co - arr(i, 1)
        Else
          no = arr(i, 1) - co
          TL = TL + no * lsNgay * (NgayTL - arr(i, 3))
          co = 0
        End If
      End If
    End If
  Next i
  CreateLaiPhat = TL
End Function

vanthinh3101 · 11/10/21

strongman4789 đã viết:
Mình đã kiểm tra lại và thấy kết quả đúng. Tiền lãi sẽ thay đổi nếu số ngày quy ước/năm thay đổi (file mình tắt chức năng tự động tính do nó tính hơi lâu nên khi thay đổi tham số phải cho tính lại để nó cập nhật).

Thực tế về cơ sở tính lãi thì chia ra làm 2 giai đoạn:
- Các khoản tiền gửi/tiền vay phát sinh trước ngày 01/01/2018: cơ sở tính lãi 360 hay 365 đều có thể áp dụng, tùy thuộc vào thỏa thuận
- Các khoản tiền gửi/tiền vay phát sinh từ ngày 01/01/2018: cơ sở tính lãi là 1 năm có 365 ngày, khi tính toán lãi vay, các TCTD thường tính theo công thức:
Lãi phải trả = (số tiền gốc x số ngày thực tế x lãi suất/năm)/365
--> Dân banker vẫn gọi đây là cách tính 366/365 (có tính đến năm nhuận tháng 02 có 29 ngày)

strongman4789 · 15/10/21

HieuCD đã viết:

Đang rảnh

Mã:

  Option Explicit
  Dim dic As Object, addHan$, addTT$

Function LaiPhat(sp$, rngHan As Range, rngTT As Range, ls#, NgayTL As Date, Optional NgayNam& = 360) As Double
  If addHan <> rngHan.Parent.Name & "!" & rngHan.Address(0, 0) Or _
                    addTT <> rngTT.Parent.Name & "!" & rngTT.Address(0, 0) Then
    Call CreateDic(rngHan, rngTT, ls, NgayTL, NgayNam)
    addHan = rngHan.Parent.Name & "!" & rngHan.Address(0, 0)
    addTT = rngTT.Parent.Name & "!" & rngTT.Address(0, 0)
  End If
  If dic.exists(sp) Then LaiPhat = dic.Item(sp)
End Function

Private Sub CreateDic(rngHan, rngTT, ls, NgayTL, NgayNam)
  Dim arr(), aHan(), aTT(), aNgay(), tmp(), sp
  Dim ngay As Date, lsNgay#, TienLai#
  Dim sRow&, fR&, i&, k&, ik&
 
  Set dic = CreateObject("scripting.dictionary")
  aHan = rngHan.Value ' Han TT
  aTT = rngTT.Value ' Thanh toan
  If aHan(1, 2) > aTT(1, 2) Then ngay = aTT(1, 2) Else ngay = aHan(1, 2) 'Ngay dau tien
  fR = ngay - 1 'Dong luu so dong du lieu
  ReDim aNgay(fR To NgayTL, 1 To 2)
  sRow = UBound(aHan)
  For i = 1 To sRow
    If aHan(i, 2) < NgayTL Then
      If dic.exists(aHan(i, 1)) = False Then
        k = k + 1
        dic.Add aHan(i, 1), k
        ReDim Preserve arr(1 To k)
        arr(k) = aNgay
      End If
      ik = dic.Item(aHan(i, 1))
      arr(ik)(fR, 1) = arr(ik)(fR, 1) + 1
      arr(ik)(aHan(i, 2), 1) = arr(ik)(aHan(i, 2), 1) + aHan(i, 3)
    End If
  Next i
  sRow = UBound(aTT)
  For i = 1 To sRow
    If aTT(i, 2) < NgayTL Then
      If dic.exists(aTT(i, 1)) = True Then
        ik = dic.Item(aTT(i, 1))
        If arr(ik)(aTT(i, 2), 1) = Empty Then arr(ik)(fR, 1) = arr(ik)(fR, 1) + 1
        arr(ik)(aTT(i, 2), 2) = arr(ik)(aTT(i, 2), 2) + aTT(i, 3)
      End If
    End If
  Next i
  lsNgay = ls / NgayNam
  For Each sp In dic.keys
    ik = dic.Item(sp)
    ReDim tmp(1 To arr(ik)(fR, 1), 1 To 3)
    k = 0
    For i = ngay To NgayTL
      If arr(ik)(i, 1) > 0 Or arr(ik)(i, 2) > 0 Then
        k = k + 1
        tmp(k, 1) = arr(ik)(i, 1)
        tmp(k, 2) = arr(ik)(i, 2)
        tmp(k, 3) = i
      End If
    Next i
    dic.Item(sp) = CreateLaiPhat(tmp, lsNgay, NgayTL) 'Tien lai phat
  Next sp
End Sub

Private Function CreateLaiPhat(arr, lsNgay, NgayTL) As Double
  Dim no#, co#, TL#, i&

  For i = 1 To UBound(arr)
    If arr(i, 2) > 0 Then
      If no = 0 Then
        co = co + arr(i, 2)
      ElseIf no > 0 Then
        If no >= arr(i, 2) Then
          TL = TL - arr(i, 2) * lsNgay * (NgayTL - arr(i, 3))
          no = no - arr(i, 2)
        Else
          TL = TL - no * lsNgay * (NgayTL - arr(i, 3))
          co = arr(i, 2) - no
          no = 0
        End If
      End If
    End If
    If arr(i, 1) > 0 Then
      If co = 0 Then
        TL = TL + arr(i, 1) * lsNgay * (NgayTL - arr(i, 3))
        no = no + arr(i, 1)
      ElseIf co > 0 Then
        If co >= arr(i, 1) Then
          co = co - arr(i, 1)
        Else
          no = arr(i, 1) - co
          TL = TL + no * lsNgay * (NgayTL - arr(i, 3))
          co = 0
        End If
      End If
    End If
  Next i
  CreateLaiPhat = TL
End Function

Cảm ơn bạn có chút "Đang rảnh" đã giúp đưa ra cách làm rất hay.
- Mình đã kiểm tra code của mình và phát hiện ra chỗ sai của mình (số ngày tính lãi cho giai đoạn cuối cùng của 1 số trường hợp), sửa lại và đã khớp với kết quả theo cách tính của bạn.
- Bạn đưa cách giải rất hay là tạo Dictionary, tính toán lãi cho tất cả các sản phẩm và sau đó các sản phẩm chỉ cần tham chiếu đến Dic này mà không cần tính toán lại, khi đó việc tính toán cho hàng loạt sản phẩm sẽ rất nhanh.
Tuy nhiên, có 1 vấn đề là khi thay đổi dữ liệu chi tiết của sản phẩm (ví dụ thay đổi số tiền yêu cầu thanh toán của 1 kỳ hạn, hoặc số tiền thanh toán thực tế của 1 kỳ nào đó) thì thuật toán không tạo lại mà vẫn tham chiếu đến Dic cũ dẫn đến sai.

HieuCD · 15/10/21

strongman4789 đã viết:
Cảm ơn bạn có chút "Đang rảnh" đã giúp đưa ra cách làm rất hay.
- Mình đã kiểm tra code của mình và phát hiện ra chỗ sai của mình (số ngày tính lãi cho giai đoạn cuối cùng của 1 số trường hợp), sửa lại và đã khớp với kết quả theo cách tính của bạn.
- Bạn đưa cách giải rất hay là tạo Dictionary, tính toán lãi cho tất cả các sản phẩm và sau đó các sản phẩm chỉ cần tham chiếu đến Dic này mà không cần tính toán lại, khi đó việc tính toán cho hàng loạt sản phẩm sẽ rất nhanh.
Tuy nhiên, có 1 vấn đề là khi thay đổi dữ liệu chi tiết của sản phẩm (ví dụ thay đổi số tiền yêu cầu thanh toán của 1 kỳ hạn, hoặc số tiền thanh toán thực tế của 1 kỳ nào đó) thì thuật toán không tạo lại mà vẫn tham chiếu đến Dic cũ dẫn đến sai.

Dữ liệu thanh toán rất quan trọng không được tự ý thay đổi, do đó code không xét tình huống nầy, nếu muốn thay đổi lung tung
Chép sub vào sheet DL_SP

Mã:

Private Sub Worksheet_Activate()
  addHan = Empty
End Sub

Khai báo lại biến toàn cục trong module chứa function

Mã:

  Option Explicit
  Public addHan$
  Dim dic As Object, addTT$

Hỗ trợ code VBA tính lãi chậm thanh toán

Thành viên mới

File đính kèm

Thành viên mới

Ăn cùng góc phố

Thành viên mới

Chuyên gia GPE

Thành viên mới

Chuyên gia GPE

File đính kèm

Thành viên mới

Chuyên gia GPE

Thành viên mới

Chuyên gia GPE

Thành viên mới

Thành viên mới

File đính kèm

Chuyên gia GPE

File đính kèm

Chuyên gia GPE

File đính kèm

Thành viên tích cực

Thành viên mới

Chuyên gia GPE

File đính kèm

Facebook

Group