Nhờ giúp đỡ sử dụng hàm , công thức gì trong bài toán này

[bebo021999]

Theo tôi, với mỗi phát sinh nhận tiền từ khách hàng A, vấn đề cần quan tâm là:
1) KH A đó có phát sinh doanh thu hay không? nếu không, lì xì =0; nếu có, tiếp bước 2
2) Công lũy kế thanh toán của KH A, so sánh với doanh thu, nếu vượt quá, phần vượt lì xì =0, nếu chưa vượt, tính tiếp bước 3
3) Số tiền là bao nhiêu? quy ra theo bảng % mà tính tiền

 

[bebo021999]

Tôi làm thế này bạn xem có đúng không:

Xét trường hợp bất kỳ, VD: ô F18:

1) Đặt các name :
* Doanh thu:
D_T =VLOOKUP(D18,'doanh thu mua hàng'!$A$3:$B$64,2,0)
* Lũy kế thanh toán:
L_KE = SUMIF($D$918,D18,$E$9:E18)

2) Xác định =0 tại các dòng có doanh thu < 50tr hoặc tại các dòng Total:
IF(OR(D_T<50*10^6,RIGHT(D18,5)="Total"),0,....Tính tiếp...)

3) Tính tiếp:
* Xác định số tiền (ST) để tính lì xì:
Nếu luỹ kế TT chưa vượt quá doanh thu, trả về "số tiền chuyển", nếu vượt, lấy một phần "số tiền chuyển" cho đủ với doanh thu:
VD: TT 50tr, trong khi Luỹ kế = 200 tr, DThu =180 tr, tức là vượt 20 tr, do đó trong 50 tr chỉ lấy 30 tr thôi để tính lì xì
ST =IF(L_KE<=D_T,E18,(E18-L_KE+D_T)*(E18-L_KE+D_T>0))
* Xác định tỷ lệ lì xì
=LOOKUP(ST,{50,100,300}*10^6,{4,4.5,5}/100)
thay ST:
=LOOKUP(IF(L_KE<=D_T,$E18,($E18-L_KE+D_T)*($E18-L_KE+D_T>0)),{50,100,300}*10^6,{4,4.5,5}/100)
*ST*Tỉ lệ:
LOOKUP(IF(L_KE<=D_T,$E18,($E18-L_KE+D_T)*($E18-L_KE+D_T>0)),{50,100,300}*10^6,{4,4.5,5}/100)*IF(L_KE<=D_T,$E18,($E18-L_KE+D_T)*($E18-L_KE+D_T>0))
*Cộng thêm 1% nếu ngày TT <=14/2/2015
(A18<=DATE(2015,2,14))*0.01*ST)
thay ST:
(A18<=DATE(2015,2,14))*0.01*IF(L_KE<=D_T,E18,(E18-L_KE+D_T)*(E18-L_KE+D_T>0))

Như vậy,
Tính tiếp = ST*Tỉ lệ + Cộng thêm 1%

Công thức tổng quát:

=IF(OR(D_T<50*10^6,RIGHT(D18,5)="Total"),0,LOOKUP(IF(L_KE<=D_T,$E18,($E18-L_KE+D_T)*($E18-L_KE+D_T>0)),{50,100,300}*10^6,{4,4.5,5}/100)*IF(L_KE<=D_T,$E18,($E18-L_KE+D_T)*($E18-L_KE+D_T>0))+(A18<=DATE(2015,2,14))*0.01*IF(L_KE<=D_T,E18,(E18-L_KE+D_T)*(E18-L_KE+D_T>0)))


Lồng vô bẫy lỗi những KH không có DT:


Tại F18, copy cho các ô còn lại:

=IFERROR(IF(OR(D_T<50*10^6,RIGHT(D18,5)="Total"),0,
LOOKUP(IF(L_KE<=D_T,$E18,($E18-L_KE+D_T)*($E18-L_KE+D_T>0)),{50,100,300}*10^6,{4,4.5,5}/100)
*IF(L_KE<=D_T,$E18,($E18-L_KE+D_T)*($E18-L_KE+D_T>0))
+(A18<=DATE(2015,2,14))*0.01*IF(L_KE<=D_T,E18,(E18-L_KE+D_T)*(E18-L_KE+D_T>0))),0)

Bạn kiểm tra xem đúng chưa nhé.