Cần trợ giúp hàm excel tính giá thuê nhà (1 người xem)

[nguyensatran]

Chào Anh/Chị
Nhờ Anh/Chị giúp em công thức tính Tiền thuê trong bảng " THANH TOÁN THEO THÁNG/ RENTAL PAYMENT BY MONTH" (file đính kèm). Em có ra kết quả nhưng chưa tối ưu, cụ thể như sau:
Chuỗi cửa hàng bên em bình thường sẽ thanh toán tiền thuê nhà mỗi 3 tháng 1 lần, đầu mỗi quý. Mùa dịch vừa qua công ty em được:
- Có CH giảm giá thuê 3 tháng, thanh toán 1 tháng 1 lần.
- Có CH giảm giá thuê 3 tháng, thanh toán 3 tháng 1 lần.
- Có CH giảm giá thuê 1 tháng, thanh toán 1 tháng 1 lần.
- Có CH giảm giá thuê 1 tháng, thanh toán 3 tháng 1 lần.
- Có CH giảm giá thuê 6 tháng, thanh toán 3 tháng 1 lần
- Có CH không giảm giá tiền thuê, thanh toán 1 tháng 1 lần
- Có CH vẫn giữ nguyên như cũ
- Có CH giảm giá đến khi nào NN chính thức ban hành cho học sinh, các cơ sở hoạt động lại bình thường.
....
Vì những thay đổi phức tạp trên mà việc tổng hợp SỐ TIỀN THANH TOÁN MỖI THÁNG khá rắc rối. Em có làm bảng "TIỀN THUÊ THEO THÁNG/ RENTAL BY MONTH" để tiện theo dõi, sau đó căn cứ vào cột I/J/K " Kỳ hạn thanh toán" để tính.
Công thức của em chỉ là ăn may thôi, vì ở cột AE và AF là thấy không hợp lý rồi nhưng em chưa biết tính công thức khác.
Mong nhận được giúp đỡ từ Anh/Chị

 

[Nhattanktnn]

Mình thấy thanh toán đều là "n tháng 1 lần" cho nên mình đã bỏ phần " tháng 1 lần" đi chỉ còn lại 1,3,12 cho dễ làm việc.
Bạn xem file đúng yêu cầu chưa nhé:

 

[nguyensatran]

Mình thấy thanh toán đều là "n tháng 1 lần" cho nên mình đã bỏ phần " tháng 1 lần" đi chỉ còn lại 1,3,12 cho dễ làm việc.
Bạn xem file đúng yêu cầu chưa nhé:

Bỏ phần " tháng 1 lần" đi chỉ còn lại 1,3,12 như vậy cũng được ạ, nhưng công thức em thấy cũng giống em ở chỗ:
- Công thức cho tháng Nov - 20 (AE4) : .....SUM(T4:V4) nếu như em xóa cột trống V thì kết quả sai rồi.
- Cột AF tương tự , nếu xóa cột trống W thì kết quả cũng sai.
Nên em mới nói cách làm của em hên là cột V và W trống nên ra kq chính xác ạ

 

[Nhattanktnn]

nếu như em xóa cột trống

Sao bạn gửi file lên như vậy giờ bạn bảo xóa cột U, V là sao nhỉ? Nếu xóa thì xóa từ đầu chứ?
File mình làm không có vấn đề gì nhé? vấn đề lỗi dòng 6 ở đây là do ô C6 bạn lấy dữ liệu tỷ giá theo ô V3, thì khi xóa cột V đi nó bị lỗi dòng 6, bạn cut dữ liệu tỷ giá qua ô khác đi rồi xóa V và W thì đâu có vấn đề?

Công thức cho tháng Nov - 20 (AE4) : .....SUM(T4:V4) nếu như em xóa cột trống V thì kết quả sai rồi.

AE4 sai chỗ nào sau khi xóa bạn gửi hình hoặc file mình xem nào?

 

[Phan Thế Hiệp]

Chào Anh/Chị
Nhờ Anh/Chị giúp em công thức tính Tiền thuê trong bảng " THANH TOÁN THEO THÁNG/ RENTAL PAYMENT BY MONTH" (file đính kèm). Em có ra kết quả nhưng chưa tối ưu, cụ thể như sau:
Chuỗi cửa hàng bên em bình thường sẽ thanh toán tiền thuê nhà mỗi 3 tháng 1 lần, đầu mỗi quý. Mùa dịch vừa qua công ty em được:
- Có CH giảm giá thuê 3 tháng, thanh toán 1 tháng 1 lần.
- Có CH giảm giá thuê 3 tháng, thanh toán 3 tháng 1 lần.
- Có CH giảm giá thuê 1 tháng, thanh toán 1 tháng 1 lần.
- Có CH giảm giá thuê 1 tháng, thanh toán 3 tháng 1 lần.
- Có CH giảm giá thuê 6 tháng, thanh toán 3 tháng 1 lần
- Có CH không giảm giá tiền thuê, thanh toán 1 tháng 1 lần
- Có CH vẫn giữ nguyên như cũ
- Có CH giảm giá đến khi nào NN chính thức ban hành cho học sinh, các cơ sở hoạt động lại bình thường.
....
Vì những thay đổi phức tạp trên mà việc tổng hợp SỐ TIỀN THANH TOÁN MỖI THÁNG khá rắc rối. Em có làm bảng "TIỀN THUÊ THEO THÁNG/ RENTAL BY MONTH" để tiện theo dõi, sau đó căn cứ vào cột I/J/K " Kỳ hạn thanh toán" để tính.
Công thức của em chỉ là ăn may thôi, vì ở cột AE và AF là thấy không hợp lý rồi nhưng em chưa biết tính công thức khác.
Mong nhận được giúp đỡ từ Anh/Chị

Theo như trường hợp CH mã số: 00010, kỳ thanh toán 12 tháng/lần, và theo thông lệ thì phải đóng tiền thuê ở đầu mỗi kỳ, do vậy, theo ý kiến riêng của tôi, nếu được giảm tiền thuê trong giữa kỳ, thì số giảm trừ phải trừ vào tiền thuê của kỳ kế tiếp. Cụ thể:

• Kỳ thuê từ 01/01/2020 đến hết 31/12/2020: --> 82,5 Trđ x 12 tháng = 990 Trđ
• Được giảm 3 tháng giữa kỳ từ: 01/04/2020 đến 30/06/2020: --> 24,75 Trđ x 3 tháng = 74,25 Trđ
• Do CH này đã đóng tiền thuê tại thời điểm đầu kỳ 01/01/2020, nên số tiền được giảm sẽ trừ vào đầu kỳ kế tiếp 01/01/2021 là:
  • 990 - 74,25 = 915,75 Trđ

Tương tự, luận cho các kỳ thanh toán: 3 tháng/lần, 1 tháng/lần.

Nếu bạn đồng ý với cách luận này, thì thử dùng công thức sau (không cần dùng bảng liệt kê "Tiền thuê theo tháng" cột M đến U)

X4=IF(MOD((MONTH(X$3)-1),$I4)=0,$C4*$I4-SUM(IF(IFERROR(MATCH(EDATE(X$3,ROW(INDIRECT("1:"&$I4))-$I4-1),$G4:$H4+{0,1}),)=1,$E4,)),)

Kết thúc bằng Ctrl+Shift+Enter, copy qua phải theo các cột tháng kế tiếp, rồi copy cả hàng xuống dưới.

Thân

 

[nguyensatran]

Theo như trường hợp CH mã số: 00010, kỳ thanh toán 12 tháng/lần, và theo thông lệ thì phải đóng tiền thuê ở đầu mỗi kỳ, do vậy, theo ý kiến riêng của tôi, nếu được giảm tiền thuê trong giữa kỳ, thì số giảm trừ phải trừ vào tiền thuê của kỳ kế tiếp. Cụ thể:

• Kỳ thuê từ 01/01/2020 đến hết 31/12/2020: --> 82,5 Trđ x 12 tháng = 990 Trđ
• Được giảm 3 tháng giữa kỳ từ: 01/04/2020 đến 30/06/2020: --> 24,75 Trđ x 3 tháng = 74,25 Trđ
• Do CH này đã đóng tiền thuê tại thời điểm đầu kỳ 01/01/2020, nên số tiền được giảm sẽ trừ vào đầu kỳ kế tiếp 01/01/2021 là:
  • 990 - 74,25 = 915,75 Trđ

Tương tự, luận cho các kỳ thanh toán: 3 tháng/lần, 1 tháng/lần.

Nếu bạn đồng ý với cách luận này, thì thử dùng công thức sau (không cần dùng bảng liệt kê "Tiền thuê theo tháng" cột M đến U)

X4=IF(MOD((MONTH(X$3)-1),$I4)=0,$C4*$I4-SUM(IF(IFERROR(MATCH(EDATE(X$3,ROW(INDIRECT("1:"&$I4))-$I4-1),$G4:$H4+{0,1}),)=1,$E4,)),)

Kết thúc bằng Ctrl+Shift+Enter, copy qua phải theo các cột tháng kế tiếp, rồi copy cả hàng xuống dưới.

Thân

CH 10 anh suy luận như vậy là đúng rồi ạ, nhưng suy luận cho CH 3 tháng/lần, 1 tháng/lần thì chưa đúng, các CH này đều là chưa thanh toán, em đang nghiên cứu áp dụng công thức của anh cho linh hoạt mà chưa ra, khó quá ạ, em chưa đủ trình độ.

 

[Phan Thế Hiệp]

CH 10 anh suy luận như vậy là đúng rồi ạ, nhưng suy luận cho CH 3 tháng/lần, 1 tháng/lần thì chưa đúng, các CH này đều là chưa thanh toán, em đang nghiên cứu áp dụng công thức của anh cho linh hoạt mà chưa ra, khó quá ạ, em chưa đủ trình độ.

Cũng tương tự như cách luận 12 tháng/lần, tức đến kỳ "phải" thanh toán thì xem và tổng hợp lại các khoản miễn/giảm các tháng trong kỳ trước.

Ví dụ: Cho kỳ thanh toán 3 tháng

• Kỳ "Phải" thanh toán của quý 3, tức các tháng 7-8-9, nếu được giảm trong các tháng: 4 -> 8, thì số tiền sẽ được tính như sau:
  • Ghi vào tháng 7 = Tiền thuê (tháng 7-8-9) - Tiền giảm (tháng 4-5-6)
• Kỳ "Phải" thanh toán của quý 4, tức các tháng 10-11-12, thì số tiền sẽ được tính như sau:
  • Ghi vào tháng 10 = Tiền thuê (tháng 10-11-12) - Tiền giảm (tháng 7-8)

Thân

 

[nguyensatran]

Sao bạn gửi file lên như vậy giờ bạn bảo xóa cột U, V là sao nhỉ? Nếu xóa thì xóa từ đầu chứ?
File mình làm không có vấn đề gì nhé? vấn đề lỗi dòng 6 ở đây là do ô C6 bạn lấy dữ liệu tỷ giá theo ô V3, thì khi xóa cột V đi nó bị lỗi dòng 6, bạn cut dữ liệu tỷ giá qua ô khác đi rồi xóa V và W thì đâu có vấn đề?

AE4 sai chỗ nào sau khi xóa bạn gửi hình hoặc file mình xem nào?

Xin lỗi anh
Em đã xem lại nhiều lần và thấy là cột V và W xóa đi thì công thức không sai, em bị sai chỗ này.
Nhưng theo công thức của anh CH thanh toán 1 tháng 1 lần thường bị sai vì không phải sẽ được thanh toán 1 tháng 1 lần suốt cả năm 2020 mà chỉ trong khoảng thời gian được ghi rõ ở cột J và K thôi.
Em có bôi vàng 1 các tháng sau ưu đãi lại trở về thanh toán như bình thường là 3 tháng/lần

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

Cũng tương tự như cách luận 12 tháng/lần, tức đến kỳ "phải" thanh toán thì xem và tổng hợp lại các khoản miễn/giảm các tháng trong kỳ trước.

Ví dụ: Cho kỳ thanh toán 3 tháng

• Kỳ "Phải" thanh toán của quý 3, tức các tháng 7-8-9, nếu được giảm trong các tháng: 4 -> 8, thì số tiền sẽ được tính như sau:
  • Ghi vào tháng 7 = Tiền thuê (tháng 7-8-9) - Tiền giảm (tháng 4-5-6)
• Kỳ "Phải" thanh toán của quý 4, tức các tháng 10-11-12, thì số tiền sẽ được tính như sau:
  • Ghi vào tháng 10 = Tiền thuê (tháng 10-11-12) - Tiền giảm (tháng 7-8)

Thân

Xin bỏ qua CH 10 ngoại lệ, các CH khác sẽ tính như sau:
Cty em xin chủ nhà trước đó và từ tháng 4 trở đi sẽ thanh toán theo ưu đãi mới:
Ví dụ: Cho kỳ thanh toán 3 tháng
- Nếu được giảm giá từ tháng 4 - 8 thì:
Tháng 4 = Tiền thuê ( tháng 4 - 5 -6) - Tiền giảm (tháng 4 - 5 -6)
Tháng 7 = Tiền thuê (tháng 7 - 8) - Tiền giảm (tháng 7 - 8) + Tiền thuê (tháng 9)
Chứ không trừ ngược như vậy ạ

 

[Phan Thế Hiệp]

Xin bỏ qua CH 10 ngoại lệ, các CH khác sẽ tính như sau:
Cty em xin chủ nhà trước đó và từ tháng 4 trở đi sẽ thanh toán theo ưu đãi mới:
Ví dụ: Cho kỳ thanh toán 3 tháng
- Nếu được giảm giá từ tháng 4 - 8 thì:
Tháng 4 = Tiền thuê ( tháng 4 - 5 -6) - Tiền giảm (tháng 4 - 5 -6)
Tháng 7 = Tiền thuê (tháng 7 - 8) - Tiền giảm (tháng 7 - 8) + Tiền thuê (tháng 9)
Chứ không trừ ngược như vậy ạ

Nếu bạn muốn lấy mốc tháng 04/2020 làm mốc đầu tiên (áp dụng cho cả trường hợp 12 tháng, coi như bạn thuê nhà bắt đầu từ tháng 04/2020), và kể như đã biết tiền miễn/giảm trước kỳ "Phải" thanh toán rồi, thì điều chỉnh công thức như sau (không cần dùng bảng liệt kê "Tiền thuê theo tháng" cột M đến U) :

X4=IF(MOD((MONTH(X$3)-4),$I4),,$C4*$I4-SUM((IFERROR(MATCH(EDATE(X$3,ROW(INDIRECT("1:"&$I4))-1),$G4:$H4+{0,1}),)=1)*$E4))

Kết thúc bằng Ctrl+Shift+Enter, copy qua phải theo các cột tháng kế tiếp, rồi copy cả hàng xuống dưới.

Thân

 

[nguyensatran]

Nếu bạn muốn lấy mốc tháng 04/2020 làm mốc đầu tiên (áp dụng cho cả trường hợp 12 tháng, coi như bạn thuê nhà bắt đầu từ tháng 04/2020), và kể như đã biết tiền miễn/giảm trước kỳ "Phải" thanh toán rồi, thì điều chỉnh công thức như sau (không cần dùng bảng liệt kê "Tiền thuê theo tháng" cột M đến U) :

X4=IF(MOD((MONTH(X$3)-4),$I4),,$C4*$I4-SUM((IFERROR(MATCH(EDATE(X$3,ROW(INDIRECT("1:"&$I4))-1),$G4:$H4+{0,1}),)=1)*$E4))

Kết thúc bằng Ctrl+Shift+Enter, copy qua phải theo các cột tháng kế tiếp, rồi copy cả hàng xuống dưới.

Thân

Xin phiền anh nữa ạ
Lỗi do em không nói rõ là các kỳ hạn thanh toán ưu đãi sẽ chỉ được áp dụng trong 1 khoảng thời gian được ghi ở cột J và K thôi. Cho nên sau khoảng thời gian này lại trở về thanh toán 3 tháng 1 lần, em có tô vàng trong file đính kèm ạ.
Em cám ơn

 

[Phan Thế Hiệp]

Xin phiền anh nữa ạ
Lỗi do em không nói rõ là các kỳ hạn thanh toán ưu đãi sẽ chỉ được áp dụng trong 1 khoảng thời gian được ghi ở cột J và K thôi. Cho nên sau khoảng thời gian này lại trở về thanh toán 3 tháng 1 lần, em có tô vàng trong file đính kèm ạ.
Em cám ơn

Nếu muốn các kỳ hạn thanh toán đang là "1 tháng/lần" đổi thành kỳ hạn "3 tháng/lần" căn cứ vào ngày hết hạn ưu đãi của cột K, thì chỉ cần bổ sung thêm 1 ít điều kiện, như sau:

X4=IF(MOD((MONTH(X$3)-4),(X$3>$K4)*($I4=1)*2+$I4),,$C4*((X$3>$K4)*($I4=1)*2+$I4)-SUM((IFERROR(MATCH(EDATE(X$3,ROW(INDIRECT("1:"&((X$3>$K4)*($I4=1)*2+$I4)))-1),$G4:$H4+{0,1}),)=1)*$E4))

Kết thúc bằng Ctrl+Shift+Enter, copy qua phải theo các cột tháng kế tiếp, rồi copy cả hàng xuống dưới.

Thân

 

[nguyensatran]

Nếu muốn các kỳ hạn thanh toán đang là "1 tháng/lần" đổi thành kỳ hạn "3 tháng/lần" căn cứ vào ngày hết hạn ưu đãi của cột K, thì chỉ cần bổ sung thêm 1 ít điều kiện, như sau:

X4=IF(MOD((MONTH(X$3)-4),(X$3>$K4)*($I4=1)*2+$I4),,$C4*((X$3>$K4)*($I4=1)*2+$I4)-SUM((IFERROR(MATCH(EDATE(X$3,ROW(INDIRECT("1:"&((X$3>$K4)*($I4=1)*2+$I4)))-1),$G4:$H4+{0,1}),)=1)*$E4))

Kết thúc bằng Ctrl+Shift+Enter, copy qua phải theo các cột tháng kế tiếp, rồi copy cả hàng xuống dưới.

Thân

Em rất cám ơn
Em đã làm được rồi !
Chúc anh ngày mới tốt lành