Giúp mình công thức trong bảng tính (1 người xem)

[ban1984b]

Chào mọi người, mình có một bảng tính như file đính kèm, mình muốn dùng các hàm, công thức để tính thành tiền.
Mình xin mô tả như sau:
- Sheet RT: mình sẽ nhập liệu vào đây, chú ý 3 cột: Khu vực, Dịch vụ, trọng lượng.
Khu vực gồm có: VT1, VT2, BR1. BR2, TT1, TT2, LDN, DDO, CDC, XMC.
Dịch vụ gồm: CPN, KDM, HTC, COD, LHH, HNN.
Trọng lượng: từ 0 đến 999999999 gram (khi tính giá thì quy đổi thành kg và làm tròn thành 1kg để tính).
- Sheet Gia: bao gồm đơn giá cho từng khu vực tương ứng với nấc trọng lượng và dịch vụ.
VT1: giá từ nấc 0-500gr; trên 500 - 1000 gr; ... trên 500.000gr
Tương tự cho các khu vực khác.
Bài toán: ở ô H3 sẽ dùng hàm và công thức để tính tiền thỏa theo điều kiện là: Khu vực: VT1; Dịch vụ: CPN; Trọng lượng: 600 gr.
Nếu mình tính thủ công thì mình sẽ được 7000 vnđ (ô C4 của Sheet Gia).
Tương tự như các cột còn lại.
Yêu cầu: mình muốn các bạn có thể giúp mình làm một công thức hỗn hợp trong ô H3 sao cho các ô D3 (VT1, VT2, BR1. BR2, TT1, TT2, LDN, DDO, CDC, XMC); E3 (CPN, KDM, HTC, COD, LHH, HNN); F3 (từ 0 đến 999999999 gram) sẽ thay đổi các dữ liệu thì kết quả sẽ vẫn đúng.
Cảm ơn mọi người.

 

[Phuocam]

Yêu cầu: mình muốn các bạn có thể giúp mình làm một công thức hỗn hợp trong ô H3 sao cho các ô D3 (VT1, VT2, BR1. BR2, TT1, TT2, LDN, DDO, CDC, XMC); E3 (CPN, KDM, HTC, COD, LHH, HNN); F3 (từ 0 đến 999999999 gram) sẽ thay đổi các dữ liệu thì kết quả sẽ vẫn đúng.

1. Tiêu đề bài này chung chung, không nêu được ý muốn hỏi

2. Bảng dò không hợp lý, phải thiết kế lại mới sử dụng công thức được.

3. Bảng giá không hợp lý. Ví dụ: dịch vụ CPN, khu vực VT1

+ 2kg giá 10000

+3-50kg giá 2500

Dù giải thích giá phía dưới là giá 1 kg thì gửi 3kg rẻ hơn 2kg?

 

[dazkangel]

Làm đại, nhấn Ctrl+Shift+Enter:

=VLOOKUP(F3/1000,IF({1,0},{0;1;1.5;2;3;50;101;251;501},OFFSET(Gia!B$3,MATCH(D3,Gia!A$4:A$93,0),MATCH(E3,Gia!C$2:H$2,0),9)),2,1)*CEILING(F3/1000,1)

 

[ban1984b]

Cảm ơn bạn đã phản hồi.
bảng dò mình thử rồi mà ko biết làm cách nào cho hợp lý hơn ạ.
Còn bảng giá thì do mình mô tả chưa hết ạ.
Nếu trọng lượng lớn hơn 2kg thì cách tính như sau: đơn giá 2kg đầu + đơn giá 1kg tiếp theo*(số kg thực tế-2000)
Ví dụ: 3kg VT1, CPN giá sẽ là: 10000+2500 = 12500
90kg VT1, CPN: 10000+2400*((90000-2000)/1000)=221.200 vnđ
bạn có thể hướng dẫn mình làm hoặc giúp mình với nhé. cảm ơn bạn

 

[huuthang_bd]

Cảm ơn bạn đã phản hồi.
bảng dò mình thử rồi mà ko biết làm cách nào cho hợp lý hơn ạ.
Còn bảng giá thì do mình mô tả chưa hết ạ.
Nếu trọng lượng lớn hơn 2kg thì cách tính như sau: đơn giá 2kg đầu + đơn giá 1kg tiếp theo*(số kg thực tế-2000)
Ví dụ: 3kg VT1, CPN giá sẽ là: 10000+2500 = 12500
90kg VT1, CPN: 10000+2400*((90000-2000)/1000)=221.200 vnđ
bạn có thể hướng dẫn mình làm hoặc giúp mình với nhé. cảm ơn bạn

Nếu trọng lượng nhỏ hơn 2 kg nhưng không tròn 0,5 kg (ví dụ 1,7 kg) thì tính sao bạn?

 

[Phan Thế Hiệp]

VT1: giá từ nấc 0-500gr; trên 500 - 1000 gr; ... trên 500.000gr
..........................
Bài toán: ở ô H3 sẽ dùng hàm và công thức để tính tiền thỏa theo điều kiện là: Khu vực: VT1; Dịch vụ: CPN; Trọng lượng: 600 gr.
Nếu mình tính thủ công thì mình sẽ được 7000 vnđ (ô C4 của Sheet Gia).

Nếu theo nấc bạn ghi như trên thì trọng lượng 600gr phải ở nấc 2: từ 500-1000gr chứ, tức nó sẽ ra 8.000đ theo bảng của bạn, phải vậy không?
Nếu đúng, dùng:

H3=IFERROR(HLOOKUP($E3,Gia!$C$2:$H$93,MATCH($D3,Gia!$A$2:$A$93,)+MATCH($F3,{0,1,2,3,6,1,2,5,10}*5*10^{2,2,2,2,2,4,4,4,4}+1)-1,),)

Enter, fill xuống.

Thân

 

[huuthang_bd]

100,5 kg thì phần trên 2 kg tính giá nào bạn?

 

[ban1984b]

Nếu trọng lượng nhỏ hơn 2 kg nhưng không tròn 0,5 kg (ví dụ 1,7 kg) thì tính sao bạn?

Cảm ơn bạn.
Đơn giá theo nấc trọng lượng như sau:
0-500gr
trên 500-1000 gr
trên 1000-1500 gr
trên 1500-2000 gr
trên 2000-50000 gr
trên 50000-100000 gr
trên 100000-250000 gr
trên 250000-500000 gr
Trên 500000 gr
Ví dụ: 200 gr mình sẽ lấy đơn giá trong nấc 0-500gr
1800 gr sẽ lấy trong nấc 1500-2000 gr
300000 gr sẽ lấy trong nấc 250000-500000 gr
nếu trọng lượng trên 2000 gr thì phần lẻ sẽ được làm tròn thành 1kg để tính
Ví dụ 3200 gr sẽ được làm tròn thành 4000 gr bạn nhé.

Bài đã được tự động gộp: 17/6/19

100,5 kg thì phần trên 2 kg tính giá nào bạn?

Làm tròn 100500 gr = 101000 gr rồi tính bạn ạ.
Cách tỉnh thủ công mình vẫn tính như sau:
101000/1000 = 101 kg
101-2=99kg
2kg đầu = 10.000 vnđ
99kg tiếp theo: 99*2300=227.700 vnđ
Thành tiền là: 10000+227700 = 237.700 vnđ

Bài đã được tự động gộp: 17/6/19

Nếu theo nấc bạn ghi như trên thì trọng lượng 600gr phải ở nấc 2: từ 500-1000gr chứ, tức nó sẽ ra 8.000đ theo bảng của bạn, phải vậy không?
Thân

Đúng rồi bạn

Bài đã được tự động gộp: 17/6/19

Nếu theo nấc bạn ghi như trên thì trọng lượng 600gr phải ở nấc 2: từ 500-1000gr chứ, tức nó sẽ ra 8.000đ theo bảng của bạn, phải vậy không?
Nếu đúng, dùng:

H3=IFERROR(HLOOKUP($E3,Gia!$C$2:$H$93,MATCH($D3,Gia!$A$2:$A$93,)+MATCH($F3,{0,1,2,3,6,1,2,5,10}*5*10^{2,2,2,2,2,4,4,4,4}+1)-1,),)

Enter, fill xuống.

Thân

Những cái trọng lượng trên 2kg thì chưa đúng bạn ạ, bạn xem lại dùm mình.

 

[huuthang_bd]

Cụ thể VT1, CPN, 100,5 kg thì tính ra kết quả bao nhiêu bạn?

 

[ban1984b]

Cụ thể VT1, CPN, 100,5 kg thì tính ra kết quả bao nhiêu bạn?

Làm tròn 100500 gr = 101000 gr rồi tính bạn ạ.
Cách tỉnh thủ công mình vẫn tính như sau:
101000/1000 = 101 kg
101-2=99kg
2kg đầu = 10.000 vnđ
99kg tiếp theo: 99*2300=227.700 vnđ
Thành tiền là: 10000+227700 = 237.700 vnđ
Kết quả 237.700 vnđ

 

[Phan Thế Hiệp]

Làm tròn 100500 gr = 101000 gr rồi tính bạn ạ.
Cách tỉnh thủ công mình vẫn tính như sau:
101000/1000 = 101 kg
101-2=99kg
2kg đầu = 10.000 vnđ
99kg tiếp theo: 99*2300=227.700 vnđ
Thành tiền là: 10000+227700 = 237.700 vnđ
Kết quả 237.700 vnđ

Thử:

H3=IFERROR(SUM(HLOOKUP($E3,Gia!$C$2:$H$93,MATCH($D3,Gia!$A$2:$A$93,)+MMULT(N(CEILING(F3,10^((F3>2000)*3))>=CHOOSE({1;2},{0,0,1,2,4},{0.06,1,2,5,10})*5*10^{2;4}),{1;1;1;1;1})+{-2;3},)*TEXT(CHOOSE({1;2},1,CEILING((F3-2000)/1000,1)),"0;\0")),)

Kết thúc bằng Ctrl+Shift+Enter. Fill xuống.

Thân

 

[huuthang_bd]

Làm tròn 100500 gr = 101000 gr rồi tính bạn ạ.
Cách tỉnh thủ công mình vẫn tính như sau:
101000/1000 = 101 kg
101-2=99kg
2kg đầu = 10.000 vnđ
99kg tiếp theo: 99*2300=227.700 vnđ
Thành tiền là: 10000+227700 = 237.700 vnđ
Kết quả 237.700 vnđ

Bài đã được tự động gộp: 17/6/19

 

[ban1984b]

Thử:

H3=IFERROR(SUM(HLOOKUP($E3,Gia!$C$2:$H$93,MATCH($D3,Gia!$A$2:$A$93,)+MMULT(N(CEILING(F3,10^((F3>2000)*3))>=CHOOSE({1;2},{0,0,1,2,4},{0.06,1,2,5,10})*5*10^{2;4}),{1;1;1;1;1})+{-2;3},)*TEXT(CHOOSE({1;2},1,CEILING((F3-2000)/1000,1)),"0;\0")),)

Kết thúc bằng Ctrl+Shift+Enter. Fill xuống.

Thân

Came ơn bạn nhiều

Bài đã được tự động gộp: 17/6/19

Bài đã được tự động gộp: 17/6/19

Cảm ơn bạn
Chính xác rồi bạn ạ.

 

[huuthang_bd]

Thử:

H3=IFERROR(SUM(HLOOKUP($E3,Gia!$C$2:$H$93,MATCH($D3,Gia!$A$2:$A$93,)+MMULT(N(CEILING(F3,10^((F3>2000)*3))>=CHOOSE({1;2},{0,0,1,2,4},{0.06,1,2,5,10})*5*10^{2;4}),{1;1;1;1;1})+{-2;3},)*TEXT(CHOOSE({1;2},1,CEILING((F3-2000)/1000,1)),"0;\0")),)

Kết thúc bằng Ctrl+Shift+Enter. Fill xuống.

Thân

Hình như IFERROR phải trong SUM chứ bạn

 

[Phan Thế Hiệp]

Hình như IFERROR phải trong SUM chứ bạn

IFERROR(SUM(), 0 ): chỉ dành đối phó với mấy dòng trống nếu chép xuống nữa.
Nếu chủ thớt biết "dừng bước giang hồ" đến "biên" dòng 78, thì không cần IFERROR() cũng được, giữ SUM() lại thôi.

Thân

 

[huuthang_bd]

IFERROR(SUM(), 0 ): chỉ dành đối phó với mấy dòng trống nếu chép xuống nữa.
Nếu chủ thớt biết "dừng bước giang hồ" đến "biên" dòng 78, thì không cần IFERROR() cũng được, giữ SUM() lại thôi.

Thân

Chắc do xài đồ cổ nên nó không hiểu =))

 

[Phan Thế Hiệp]

Chắc do xài đồ cổ nên nó không hiểu =))

Lạ quá!
Tại sao Excel XP2007 nó không hiểu TEXT(...,"0;\0")!?

 

[Phuocam]

Dịch vụ này tính cũng vui:

Người gửi 249kg tính 578.100 đ

Người gửi 280kg chỉ tính 566.000 đ

 

[huuthang_bd]

Lạ quá!
Tại sao Excel XP2007 nó không hiểu TEXT(...,"0;\0")!?

Không phải do hàm TEXT đâu bạn. Excel 2007 thì nó vầy, không biết các phiên bản cao hơn có thay đổi gì không.

 

[Phan Thế Hiệp]

Không phải do hàm TEXT đâu bạn. Excel 2007 thì nó vầy, không biết các phiên bản cao hơn có thay đổi gì không.

Chính xác là "em" nó rồi!
CEILING() phiên bản cao hơn đã không còn bị vậy:

CEILING (Hàm CEILING)

Trả về số được làm tròn lên, xa số không, đến bội số có nghĩa gần nhất. Ví dụ, nếu bạn muốn tránh dùng đồng penny trong giá và sản phẩm của bạn có giá là $4.42, hãy dùng công thức =CEILING(4.42,0.05) để làm tròn lên đến đồng nickel gần nhất. Nếu đối số không phải dạng số, CEILING trả về giá...

support.office.com

---------------------------------------------------------------------------------------------------------------------------------------------------

Dịch vụ này tính cũng vui:

Người gửi 249kg tính 578.100 đ

Người gửi 280kg chỉ tính 566.000 đ

Muốn công bằng thì từ mức >2Kg, chơi lũy tiến giống tính mức điện bậc thang lũy tiến vậy.

Khà khà khà