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

vi1nguoi12345 · 25/3/15

em có 1 file excel gồm 2 sheet là " Lì Xì " và " Doanh thu mua hàng "

bài toán yêu cầu tính tiền lì xì của cột "mã khách hàng " với các ĐK sau :

- nếu " số tiền chuyển " từ 50--> dưới 100tr đc lì xì 4% trên doanh thu mua hàng

- nếu " số tiền chuyển " từ 100--> dưới 300tr đc lì xì 4.5% trên doanh thu mua hàng

- nếu " số tiền chuyển >= 300tr đc lì xì 5% trên doanh thu mua hàng

- khách hàng chuyển tiền trước 14/02/2015 được lì xì thêm 1 % trên số tiền chuyển

vi1nguoi12345 · 25/3/15

có ai không giúp đỡ em với

vi1nguoi12345 · 25/3/15

có cao thủ nào có thể giúp em không . em cám ơn

phihndhsp · 25/3/15

vấn đề của bạn không phải là tính ra các %, mà vấn đề của bạn không rõ ràng doanh thu mua hàng, bạn phải làm rõ thì mọi người mới giúp bạn được

vi1nguoi12345 · 25/3/15

phihndhsp đã viết:
vấn đề của bạn không phải là tính ra các %, mà vấn đề của bạn không rõ ràng doanh thu mua hàng, bạn phải làm rõ thì mọi người mới giúp bạn được

em không hiểu bác cần làm rõ vấn đề gì ở sheet "doanh thu mua hàng"

em ví dụ như này nhé :

từ 09/2/2015 đến 09/03/2015 khách hàng chuyển tiền thanh toán mua hàng của Cty em là 500tr
- lấn 1 chuyển 200tr
- lần 2 chuyển 150tr
- lần 3 chuyển 150tr
trong khi hàng hóa bên em bán chỉ có trị giá 400tr . vậy để tính lì xì cho khách hàng này theo như ĐK ban đầu là :

- lần 1 : đc lì xì : 200tr *4.5%
- lần 2 : đc lì xì : 150tr*4.5%
- lần 3 :đc lì xì : 50tr *4.5% ( do doanh thu chỉ có 400tr nên lì xì đc tính trên doanh thu thực tế mua hàng )

n0thing1988 · 25/3/15

vi1nguoi12345 đã viết:
em không hiểu bác cần làm rõ vấn đề gì ở sheet "doanh thu mua hàng"

em ví dụ như này nhé :

từ 09/2/2015 đến 09/03/2015 khách hàng chuyển tiền thanh toán mua hàng của Cty em là 500tr
- lấn 1 chuyển 200tr
- lần 2 chuyển 150tr
- lần 3 chuyển 150tr
trong khi hàng hóa bên em bán chỉ có trị giá 400tr . vậy để tính lì xì cho khách hàng này theo như ĐK ban đầu là :

- lần 1 : đc lì xì : 200tr *4.5%
- lần 2 : đc lì xì : 150tr*4.5%
- lần 3 :đc lì xì : 50tr *4.5% ( do doanh thu chỉ có 400tr nên lì xì đc tính trên doanh thu thực tế mua hàng )

Thực ra cũng chưa hiểu cách tính tiền này lắm. Nhưng bạn hỏi hàm nào thì mình gợi ý dùng hàm IF lồng vào nhau là ra được

VD tại F9=IF(E9>=300000000,"5%",IF(E9>=100000000,"4.5%",IF(E9>=50000000,"4%",0)))

vi1nguoi12345 · 26/3/15

n0thing1988 đã viết:
Thực ra cũng chưa hiểu cách tính tiền này lắm. Nhưng bạn hỏi hàm nào thì mình gợi ý dùng hàm IF lồng vào nhau là ra được
VD tại F9=IF(E9>=300000000,"5%",IF(E9>=100000000,"4.5%",IF(E9>=50000000,"4%",0)))

cám ơn bạn . mình cũng hiểu là sử dụng hàm if . nhưng phải sử dụng như thế nào để ra kết quả mình thấy khó quá .

NguyenThien Nhan · 26/3/15

tại F9==IF(AND(COUNTIF($D$9:$D9,$D9)=1,LEN($D9)<=6),SUM(IF($E9>=300000000,5%,IF($E9>=100000000,4.5%,IF($E9>=50000000,4%,0)))*SUMIF('doanh thu mua hàng'!$A$3:$A$64,'Lì xì'!D9,'doanh thu mua hàng'!$B$3:$B$64),IF(DATE(2015,2,14)>$A9,1%*$E9,0)),0)

Thử xem có đúng không pạn

vi1nguoi12345 · 26/3/15

NguyenThien Nhan đã viết:
tại F9==IF(AND(COUNTIF($D$9:$D9,$D9)=1,LEN($D9)<=6),SUM(IF($E9>=300000000,5%,IF($E9>=100000000,4.5%,IF($E9>=50000000,4%,0)))*SUMIF('doanh thu mua hàng'!$A$3:$A$64,'Lì xì'!D9,'doanh thu mua hàng'!$B$3:$B$64),IF(DATE(2015,2,14)>$A9,1%*$E9,0)),0)

Thử xem có đúng không pạn

cám ơn bạn . nhưng vẫn chưa đúng bạn ạ

NguyenThien Nhan · 26/3/15

vậy pạn đánh thử một vài kết quả taị cột F thử xem. khi đó mới biết mà giúp tiếp

vi1nguoi12345 · 26/3/15

NguyenThien Nhan đã viết:
vậy pạn đánh thử một vài kết quả taị cột F thử xem. khi đó mới biết mà giúp tiếp

bảng này do mình đã subtotal nên tính lì xì cho các mã khách hàng như sau

- mã DL009 vì không có doanh thu mua hàng nên không đc lì xì
- mã DL043 : DT mua hàng là 30 tr nên tiền lì xì : 30tr *4%
- mã DL045 : DT mua hàng là 120tr > tiền chuyển là 100tr nên tiền lì xì = 50tr*4%+ 50tr*4%
- mã DL047 : DT mua hàng là 300tr < tiền chuyển là 400 tr nên lì xì = 100tr*4.5%+100tr*4.5%+100tr*4.5%

giola · 26/3/15

vi1nguoi12345 đã viết:
bảng này do mình đã subtotal nên tính lì xì cho các mã khách hàng như sau

- mã DL009 vì không có doanh thu mua hàng nên không đc lì xì
- mã DL043 : DT mua hàng là 30 tr nên tiền lì xì : 30tr *4%
- mã DL045 : DT mua hàng là 120tr > tiền chuyển là 100tr nên tiền lì xì = 50tr*4%+ 50tr*4%
- mã DL047 : DT mua hàng là 300tr < tiền chuyển là 400 tr nên lì xì = 100tr*4.5%+100tr*4.5%+100tr*4.5%

Sau hồi đọc hết các bài trên cũng hiểu sơ sơ,

Giản đơn, bạn làm như sau

1) Tại sheet "doanh thu mua hang"

+ ô C2 gõ %
+ ô C3 nhập công thức:

PHP:

=IF(B3<50*10^6,0%,IF(B3<100*10^6,4%,IF(B3<300*10^6,4.5%,5%)))

_______copy công thức này cho toàn cột C: C3:C64

2) tại sheet "Lì xì"

+ Tạm bỏ Remove Outline Subtotal đi đã
+ô F8 dùng công thức sau

PHP:

=E9*IFERROR(VLOOKUP(D9,'doanh thu mua hàng'!$A$3:$C$64,3,0),0)

_______Copy toàn công thức cho cột F

+ Thực hiện Outline Subtotal (lại như cũ)

vi1nguoi12345 · 26/3/15

giola đã viết:
Sau hồi đọc hết các bài trên cũng hiểu sơ sơ,

Giản đơn, bạn làm như sau

1) Tại sheet "doanh thu mua hang"

+ ô C2 gõ %
+ ô C3 nhập công thức:

PHP:

=IF(B3<50*10^6,0%,IF(B3<100*10^6,4%,IF(B3<300*10^6,4.5%,5%)))

_______copy công thức này cho toàn cột C: C3:C64

2) tại sheet "Lì xì"

+ Tạm bỏ Remove Outline Subtotal đi đã
+ô F8 dùng công thức sau

PHP:

=E9*IFERROR(VLOOKUP(D9,'doanh thu mua hàng'!$A$3:$C$64,3,0),0)

_______Copy toàn công thức cho cột F

+ Thực hiện Outline Subtotal (lại như cũ)

cám ơn bạn nhưng mình thấy hình như không đúng

% lì xì đc tính dựa trên cột số tiền chuyển mà

giola · 26/3/15

Hoặc phương án khác không cần sử dụng cột C bên sheet "doanh thu mua hang", thì như sau

tại sheet "Lì xì"

+ Tạm bỏ Remove Outline Subtotal đi đã
+ô F8 dùng công thức sau

PHP:

=E9*IFERROR(LOOKUP(VLOOKUP(D9,'doanh thu mua hàng'!$A$3:$B$64,2,0)/10^6,{0,50,100,300},{0,0.04,0.045,0.05}),0)

_______Copy toàn công thức cho cột F

+ Thực hiện Outline Subtotal (lại như cũ)

Thì kết quả vẫn đẹp mà không cần cột % C ở bên Doanh thu

giola · 26/3/15

vi1nguoi12345 đã viết:
cám ơn bạn nhưng mình thấy hình như không đúng

% lì xì đc tính dựa trên cột số tiền chuyển mà

Bạn có nhầm lẫn không, khi chính bạn đã viết

vi1nguoi12345 đã viết:
bảng này do mình đã subtotal nên tính lì xì cho các mã khách hàng như sau

- mã DL009 vì không có doanh thu mua hàng nên không đc lì xì
- mã DL043 : DT mua hàng là 30 tr nên tiền lì xì : 30tr *4%
- mã DL045 : DT mua hàng là 120tr > tiền chuyển là 100tr nên tiền lì xì = 50tr*4%+ 50tr*4%
- mã DL047 : DT mua hàng là 300tr < tiền chuyển là 400 tr nên lì xì = 100tr*4.5%+100tr*4.5%+100tr*4.5%

vi1nguoi12345 · 26/3/15

giola đã viết:
Bạn có nhầm lẫn không, khi chính bạn đã viết

không bạn ạ .vì đề bài lúc đầu đã ghi rất rõ .
% thì đc tính trên số tiền chuyển . nhưng khi tính tiền lì xì thì phải so sánh giữa số tiền chuyển và DT mua hàng

- nếu số tiền chuyển > DT mua hàng thì tiền lì xì = DT mua hàng * %
- nếu số tiền chuyển <= DT mua hàng thì tiền lì xì = số tiền chuyển * %

giola · 26/3/15

vi1nguoi12345 đã viết:
không bạn ạ .vì đề bài lúc đầu đã ghi rất rõ .
% thì đc tính trên số tiền chuyển . nhưng khi tính tiền lì xì thì phải so sánh giữa số tiền chuyển và DT mua hàng

- nếu số tiền chuyển > DT mua hàng thì tiền lì xì = DT mua hàng * %
- nếu số tiền chuyển <= DT mua hàng thì tiền lì xì = số tiền chuyển * %

+ Nhưng %: 4%, 4.5%, 5% là theo tiền chuyển?

+ và dưới 50 triệu có %? có phải 0%? vì bạn chỉ viết

- nếu " số tiền chuyển " từ 50--> dưới 100tr đc lì xì 4% trên doanh thu mua hàng

- nếu " số tiền chuyển " từ 100--> dưới 300tr đc lì xì 4.5% trên doanh thu mua hàng

- nếu " số tiền chuyển >= 300tr đc lì xì 5% trên doanh thu mua hàng

vi1nguoi12345 · 26/3/15

giola đã viết:
+ Nhưng %: 4%, 4.5%, 5% là theo tiền chuyển?

+ và dưới 50 triệu có %? có phải 0%? vì bạn chỉ viết

không có trường hợp nào là dưới 50tr cả bạn ạ

giola · 26/3/15

chưa trả lời câu hỏi này

+ Nhưng %: 4%, 4.5%, 5% là theo tiền chuyển?

phần trăm dựa vào cái gì? với các trường hợp

- nếu số tiền chuyển > DT mua hàng thì tiền lì xì = DT mua hàng * % _____________(ký hiệu % ở đây là A)
- nếu số tiền chuyển <= DT mua hàng thì tiền lì xì = số tiền chuyển * % ____________(ký hiệu % ở đây là B)

thì A, B tính theo???

giola · 26/3/15

và nữa

trường hợp này có mâu thuẫn

- mã DL047 : DT mua hàng là 300tr < tiền chuyển là 400 tr nên lì xì = 100tr*4.5%+100tr*4.5%+100tr*4.5%

đây là 4.5% hay 5%??????????? vì từ:

- nếu " số tiền chuyển >= 300tr đc lì xì 5% trên doanh thu mua hàng

và nữa
bạn nói "không có trường hợp nào là dưới 50tr cả bạn ạ" NHƯNG lại đưa ví dụ ở đây (có 30 triệu<50trieu)

- mã DL043 : DT mua hàng là 30 tr nên tiền lì xì : 30tr *4%

trả lời các câu hỏi trên đầy đủ thì may ra mới tìm ra đúng ??? nên diễn giải chi tiết rõ ràng

bebo021999 · 26/3/15

Theo tôi, với mỗi phát sinh nhận tiền từ khách hàng A, vấn đề cần quan tâm là:
1) KH A đó có phát sinh doanh thu hay không? nếu không, lì xì =0; nếu có, tiếp bước 2
2) Công lũy kế thanh toán của KH A, so sánh với doanh thu, nếu vượt quá, phần vượt lì xì =0, nếu chưa vượt, tính tiếp bước 3
3) Số tiền là bao nhiêu? quy ra theo bảng % mà tính tiền

bebo021999 · 27/3/15

Tôi làm thế này bạn xem có đúng không:

Xét trường hợp bất kỳ, VD: ô F18:

1) Đặt các name :
* Doanh thu:
D_T =VLOOKUP(D18,'doanh thu mua hàng'!$A$3:$B$64,2,0)
* Lũy kế thanh toán:
L_KE = SUMIF($D$9

18,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:

Mã:

=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é.

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

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

Thành viên mới

Thành viên mới

Thành viên gạo cội

Thành viên mới

Mù VBA

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

theo chiều Gió

Thành viên mới

theo chiều Gió

theo chiều Gió

Thành viên mới

theo chiều Gió

Thành viên mới

theo chiều Gió

theo chiều Gió

Thành viên gạo cội

Thành viên gạo cội

Thời gian đếm ngược.