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à

 

[ban1984b]

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 đ

Nó theo khu vực bạn nhé, cùng một trọng lượng nhưng khu vực khác nhau thì giá khác nhau; dịch vụ khác nhau thì giá khác nhau.
Khu vực thì bạn cứ hiểu như từ Hồ Chí Minh đi Bình Dương thì giá khác với từ Hồ Chí Minh đi Nha Trang.
Dịch vụ thì bình thường giá khác với dịch vụ Vip vậy

 

[Phan Thế Hiệp]

Nó theo khu vực bạn nhé, cùng một trọng lượng nhưng khu vực khác nhau thì giá khác nhau; dịch vụ khác nhau thì giá khác nhau.
Khu vực thì bạn cứ hiểu như từ Hồ Chí Minh đi Bình Dương thì giá khác với từ Hồ Chí Minh đi Nha Trang.
Dịch vụ thì bình thường giá khác với dịch vụ Vip vậy

Bạn hiểu nhầm ý của bạn @phuocam rồi.
Bảng giá của bạn trong cùng 1 khu vực, trọng lượng càng lớn thì tổng chi phí chuyển hàng sẽ thấp hơn!?

Thân

 

[ban1984b]

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

Mình chỉ biết cơ bản thôi ạ
Mình đang xài excel 2013

 

[Phuocam]

Nó theo khu vực bạn nhé, cùng một trọng lượng nhưng khu vực khác nhau thì giá khác nhau; dịch vụ khác nhau thì giá khác nhau.
Khu vực thì bạn cứ hiểu như từ Hồ Chí Minh đi Bình Dương thì giá khác với từ Hồ Chí Minh đi Nha Trang.
Dịch vụ thì bình thường giá khác với dịch vụ Vip vậy

Khác loại hay khác nơi thì nói làm gì, xem hình:

 

[ban1984b]

Bạn hiểu nhầm ý của bạn @phuocam rồi.
Bảng giá của bạn trong cùng 1 khu vực, trọng lượng càng lớn thì tổng chi phí chuyển hàng sẽ thấp hơn!?

Thân

Uh, đúng rồi bạn.
Gửi trọng lượng càng lớn thì giá càng giảm, những mức trọng lượng gần nhau ở 2 nấc khác nhau thì đôi thi trọng lượng cao hơn chút mà giá thấp hơn chút.

 

[Phan Thế Hiệp]

Uh, đúng rồi bạn.
Gửi trọng lượng càng lớn thì giá càng giảm, những mức trọng lượng gần nhau ở 2 nấc khác nhau thì đôi thi trọng lượng cao hơn chút mà giá thấp hơn chút.

Ồ! vậy có lợi cho người gửi hàng rồi.

Thân

 

[ban1984b]

Khác loại hay khác nơi thì nói làm gì, xem hình:
View attachment 219331

Vì 249kg thuộc nấc 101-250kg đơn giá 1kg tiếp theo trên 2kg là 2300, còn 280kg thuộc nấc 251-500kg đơn giá 1kg tiếp theo trên 2kg chỉ là 2000.
Do đó có sự chênh lệch đó bạn.

Cảm ơn các bạn nhiều.

 

[Phan Thế Hiệp]

Mình chỉ biết cơ bản thôi ạ
Mình đang xài excel 2013

Câu đó chỉ là sự trao đổi "kinh nghiệm" của anh em trong nhóm "quậy" tụi này mà thôi! bạn đừng lo và quan tâm đến.

Khà khà khà

 

[Phuocam]

Vì 249kg thuộc nấc 101-250kg đơn giá 1kg tiếp theo trên 2kg là 2300,
còn 280kg thuộc nấc 251-500kg đơn giá 1kg tiếp theo trên 2kg chỉ là 2000.
Do đó có sự chênh lệch đó bạn.

Cảm ơn các bạn nhiều.

Còn một cách tính khác là tính theo từng bậc thang như điện lực, nhưng tiền giảm dần, sẽ công bằng hơn.

 

[huuthang_bd]

Ồ! vậy có lợi cho người gửi hàng rồi.

Thân

Chả trách nhiều vụ bán ai phôn mà mở ra toàn gạch. Chắc là bỏ vô cho nặng để giảm cước vận chuyển đây mà )

 

[Phuocam]

Ví dụ tôi có 249 kg , nhét thêm 2 cục gạch 2 kg để gửi , giảm được 70.100 đ

 

[ban1984b]

Cảm ơn các bạn và các cao thủ Excel, có phần này rồi mình sẽ giảm thời gian rất nhiều, vì hàng ngày mình có gần 3000 phiếu gửi hàng kiểu này, nhập không đã phê lòi rồi.
Nhân đây cho mình hỏi luôn, các bạn và các cao thủ excel có tài liệu or sách về hàm, công thức excel dành cho người mới bắt đầu không chỉ mình với nhé.

 

[Phan Thế Hiệp]

Cảm ơn các bạn và các cao thủ Excel, có phần này rồi mình sẽ giảm thời gian rất nhiều, vì hàng ngày mình có gần 3000 phiếu gửi hàng kiểu này, nhập không đã phê lòi rồi.
Nhân đây cho mình hỏi luôn, các bạn và các cao thủ excel có tài liệu or sách về hàm, công thức excel dành cho người mới bắt đầu không chỉ mình với nhé.

"Đặc sản" của GPE đây, trong đó có cả bài tập dành cho người mới bắt đầu và nâng cao nữa.

2007Excel_Function_and_Formulas.rar

drive.google.com

Thân

 

[huuthang_bd]

Cảm ơn các bạn và các cao thủ Excel, có phần này rồi mình sẽ giảm thời gian rất nhiều, vì hàng ngày mình có gần 3000 phiếu gửi hàng kiểu này, nhập không đã phê lòi rồi.
Nhân đây cho mình hỏi luôn, các bạn và các cao thủ excel có tài liệu or sách về hàm, công thức excel dành cho người mới bắt đầu không chỉ mình với nhé.

Hướng dẫn sử dụng HÀM thì có, còn công thức thì tùy từng trường hợp cụ thể mà sử dụng kết hợp các HÀM phù hợp để có được kết quả.

 

[ban1984b]

@huuthang_bd Chào anh.
Hôm trước anh có giúp mình công thức tính trong bảng tính, Mình dùng ok rồi.
Nhưng hôm nay mình đổi qua một máy khác thì nó bị lỗi như thế này:
1. Lúc đầu mới copy công thức vào thì kết quả trả về vẫn đúng, các thao tác diễn ra bình thường.
2. Khi tắt file đi mở lại thì một số ô bị lỗi, mình kiểm tra thì thấy đoạn công thức này "=VLOOKUP(H6;CHOOSE({1;2};{0;1;2;3}" (báo sai #REF!) được đổi thành "=VLOOKUP(H6;CHOOSE({1\2};{0;1;2;3}" (kết quả vẫn đúng).
3. Máy này đang sử dụng bản WPS Office 2019.
4. Với công thức này: "=VLOOKUP(H6;CHOOSE({1;2};{0;1;2;3}*500;OFFSET(Gia!$B$3;MATCH(F6;Gia!$A$4:$A$93;MATCH(G6;Gia!$C$2:$H$2;4));2)+(ROUNDUP(H6/1000-2)*IFERROR(VLOOKUP(H6/1000;CHOOSE({1;2};{2;51;101;251;501};OFFSET(Gia!$B$3;MATCH(F6;Gia!$A$4:$A$93+4;MATCH(G6;Gia!$C$2:$H$2;5));2);0)" thì giã sử ở sheet "Gia" Mình thêm 2 cột là "PTT" (I) và "AIA" (J) thì mình chỉ cần sửa công thức lại như vậy thôi đúng không "=VLOOKUP(H6;CHOOSE({1;2};{0;1;2;3}*500;OFFSET(Gia!$B$3;MATCH(F6;Gia!$A$4:$A$93;MATCH(G6;Gia!$C$2:$J$2;4));2)+(ROUNDUP(H6/1000-2)*IFERROR(VLOOKUP(H6/1000;CHOOSE({1;2};{2;51;101;251;501};OFFSET(Gia!$B$3;MATCH(F6;Gia!$A$4:$A$93+4;MATCH(G6;Gia!$C$2:$J$2;5));2);0)".
Các bạn xem file đính kèm hộ mình, Cảm ơn.

 

[ban1984b]

Đã tim ra nguyên nhân rồi.
Hi hi