Trợ giúp sử dụng hàm để tính giá (1 người xem)

[dannd]

Mình có biểu giá như sheet "bieugia" trong file đính kèm. Các bạn giúp mình lập công thức tính cột thành tiền ở sheet " thucte" với.
nhiều điều kiện quá mình loạn cả lên.

 

[Phan Thế Hiệp]

Mình có biểu giá như sheet "bieugia" trong file đính kèm. Các bạn giúp mình lập công thức tính cột thành tiền ở sheet " thucte" với.
nhiều điều kiện quá mình loạn cả lên.

Do điều kiện khung thời gian của bạn rắc rối nên làm phức tạp vấn đề.

Ví dụ: khung giờ 0530-0800, tức ý là từ 05h30 đến 08h00, phải vậy không!!? đem so giả sử với 0615 (06h15), thì nó phù hợp khung giờ này. Vậy hỏi bạn nếu giờ thực tế là 1015 thì nó nằm ở khung giờ nào? vì dòm trong cả bảng chả thấy khung giờ phù hợp.

Tuy nhiên, tôi vẫn đưa giải pháp theo những gì trong file mà bạn đã gửi lên, cụ thể:

1. Tạo Name "DongThoa": để lấy dòng thỏa điều kiện theo từng dòng của dữ liệu thực tế (xem công thức minh họa tại H3: M11 file đính kèm)

   =OFFSET(Banggia!$D$2:$J$2,LOOKUP(2,1/(Banggia!$A$3:$A$11=Thucte!$B3)/(Banggia!$B$3:$B$11=Thucte!$C3)/(MMULT(N(MID(SUBSTITUTE(Banggia!$C$3:$C$11,"-",REPT(" ",100)),{1,101},100)*{-1,1}>=Thucte!$D3*{-1,1}),{1;1})=2),ROW(Thucte!$1:$100)),)*CHOOSE({1,2,3,4,5,6},1,1,Thucte!E3,Thucte!E3,Thucte!E3,Thucte!E3)

2. Công thức tính Thành tiền:

   F3=INDEX(DongThoa,SUMPRODUCT(N(E3>CHOOSE({1,2,3,4,5},0,INDEX(DongThoa,1),45,100,300)))+1)

   Enter, fill xuống.

Xem file kèm.

Thân

 

[saobekhonglac]

Do điều kiện khung thời gian của bạn rắc rối nên làm phức tạp vấn đề.

Ví dụ: khung giờ 0530-0800, tức ý là từ 05h30 đến 08h00, phải vậy không!!? đem so giả sử với 0615 (06h15), thì nó phù hợp khung giờ này. Vậy hỏi bạn nếu giờ thực tế là 1015 thì nó nằm ở khung giờ nào? vì dòm trong cả bảng chả thấy khung giờ phù hợp.

Tuy nhiên, tôi vẫn đưa giải pháp theo những gì trong file mà bạn đã gửi lên, cụ thể:

1. Tạo Name "DongThoa": để lấy dòng thỏa điều kiện theo từng dòng của dữ liệu thực tế (xem công thức minh họa tại H3: M11 file đính kèm)

   =OFFSET(Banggia!$D$2:$J$2,LOOKUP(2,1/(Banggia!$A$3:$A$11=Thucte!$B3)/(Banggia!$B$3:$B$11=Thucte!$C3)/(MMULT(N(MID(SUBSTITUTE(Banggia!$C$3:$C$11,"-",REPT(" ",100)),{1,101},100)*{-1,1}>=Thucte!$D3*{-1,1}),{1;1})=2),ROW(Thucte!$1:$100)),)*CHOOSE({1,2,3,4,5,6},1,1,Thucte!E3,Thucte!E3,Thucte!E3,Thucte!E3)

2. Công thức tính Thành tiền:

   F3=INDEX(DongThoa,SUMPRODUCT(N(E3>CHOOSE({1,2,3,4,5},0,INDEX(DongThoa,1),45,100,300)))+1)

   Enter, fill xuống.

Xem file kèm.

Thân

Em mò từ sáng giờ mà chưa có cách nào ngoài tạo cột phụ

 

[Phan Thế Hiệp]

Em mò từ sáng giờ mà chưa có cách nào ngoài tạo cột phụ

Thì Name "Dongthoa" cũng là 1 hình thức dùng cột phụ. Đến lúc cần để xử lý thì không nên ngần ngại dùng cột phụ.

Chúc anh em ngày vui.

 

[dannd]

Cảm ơn các bạn đã giúp đỡ. Mình đang cố gắng lần mò để hiếu hết các công thức các bạn đã cung cấp. File mình gửi các bạn là file đã đơn giản hóa bớt vì mình làm thủ công một số bước. Thực tế thì file phức tạp hơn, như 2 file mình đính kèm để các bạn tham khảo. Trong đó, ở file thực tế nếu mã hàng là trống thì đó là hàng thông thường (ở trong bảng giá)

Bài đã được tự động gộp: 27/9/18

"KL định mức" trong file mình gửi hôm trước hay "Moc" trong file bạn Phan Thế Hiệp đã gửi chính bằng M/N (M chia N, là các giá trị tương ứng trong cột M, N của file BangGia mình vừa gửi.
Cảm ơn các bạn rất nhiều.

 

[dannd]

Chào bạn Phan Thế Hiệp.
Mình thử lấy dữ liệu thực tế trong file bảng tính và đưa vào file test mẫu của bạn, nhưng nhiều chỗ báo lỗi "#N/A". Mình không hiểu nguyên nhân vì sau. Bạn kiểm tra và giúp mình với.

 

[Phan Thế Hiệp]

Chào bạn Phan Thế Hiệp.
Mình thử lấy dữ liệu thực tế trong file bảng tính và đưa vào file test mẫu của bạn, nhưng nhiều chỗ báo lỗi "#N/A". Mình không hiểu nguyên nhân vì sau. Bạn kiểm tra và giúp mình với.

Tôi đang xem xét file thực tế bạn gửi, sơ bộ hoàn toàn khác và phức tạp gấp bội lần file mẫu bạn gửi.
Tôi đang xử lý phần tính phí ngày "Thứ 2 & CN" nữa là xong.
Bạn chịu khó chờ, chắc đến chiều tối sẽ xong.

Thân

 

[dannd]

Cảm ơn bạn. Mình thao tác theo kiểu filter để lọc dữ liệu rồi copy và paste vào file test của bạn thì lỗi N/A, mình thấy các lỗi này là do khoảng thời gian nằm ngoài khoảng thời gian trong mẫu mình gửi trước đấy.
Nếu có thể bạn làm luôn trên file gốc mình gửi thì mình sẽ đỡ công copy và paste nhé. Minh vẫn chưa mò tìm và hiểu được công thức bạn đã sử dụng!
dù sao cũng cảm ơn bạn rất nhiều.

 

[dannd]

Chào bạn Hiệp, có lẽ bạ không cần xử lý phần tính phí theo Thứ Bảy và Chủ nhật đâu. Vì thực tế có nhiều hành trình lại các ngày khác, vì vậy bạn giúp mình theo khoảng thời gian trong ngày là đủ. Phần thứ bảy chủ nhật thì mình lọc theo filter rồi copy và paste thôi.

 

[Phan Thế Hiệp]

Chào bạn Hiệp, có lẽ bạ không cần xử lý phần tính phí theo Thứ Bảy và Chủ nhật đâu. Vì thực tế có nhiều hành trình lại các ngày khác, vì vậy bạn giúp mình theo khoảng thời gian trong ngày là đủ. Phần thứ bảy chủ nhật thì mình lọc theo filter rồi copy và paste thôi.

Đã thực hiện xong, nhưng do vấn đề phức tạp nên xử lý công thức và phải dùng thêm cột phụ tăng lên:
Ví dụ:

• "Hành trình" của 2 sheet thậm chí trong cùng một sheet như "Thucte" cũng khác nhau, ba hồi HANSGN, ba hồi SGNHAN!? Buộc lòng phải dùng cột phụ để đổi về.
• Sheet khung bảng giá lại dùng "Merge Cell" tại các cột quan trọng cần so khớp, vì vậy để giảm thiểu công thức cũng buộc lòng phải dùng cột phụ.
• Trọng lượng tối thiểu trước trong file mẫu thì có, trong bảng thực tế thì không nên phải tạo thêm cột trong bảng giá này (cột E)
• Một số mục như Gia cầm, bưu phẩm bưu kiện, theo khung trọng lượng tại mức: 45,100,300 không có giá, nên khi tính toán nếu trọng lượng thực tế tại các mức này sẽ bằng 0.
• Name: "DongThoa" điều chỉnh:

  =OFFSET('15apr-further'!$E$5:$K$5,LOOKUP(2,1/($P2='15apr-further'!$P$6:$P$46)/($H2>='15apr-further'!$M$6:$M$46)/($H2<='15apr-further'!$N$6:$N$46)/('15apr-further'!$O$6:$O$46*1=IF(AND(P2="HANSGN",WEEKDAY(H2)<3,MATCH(H2,'15apr-further'!$W$2:$AA$2)>2),2,MOD(MATCH(K2,OFFSET('15apr-further'!$R$2:$V$2,MATCH($P2,'15apr-further'!$Q$2:$Q$3,)-1,)),2)))/(TRIM($L2)='15apr-further'!$C$6:$C$46),ROW($1:$500)),)*CHOOSE({1,2,3,4,5,6},1,1,$F2,$F2,$F2,$F2)

• Công thức "Thành tiền" như cũ:

  M2=INDEX(DongThoa,SUMPRODUCT(N(F2>CHOOSE({1,2,3,4,5},0,INDEX(DongThoa,1),45,100,300)))+1)

  Enter, fill xuống.
• Một số lưu ý có ghi thêm trong file để bạn thực hiện đúng.

Thân

 

[saobekhonglac]

Đã thực hiện xong, nhưng do vấn đề phức tạp nên xử lý công thức và phải dùng thêm cột phụ tăng lên:
Ví dụ:

• "Hành trình" của 2 sheet thậm chí trong cùng một sheet như "Thucte" cũng khác nhau, ba hồi HANSGN, ba hồi SGNHAN!? Buộc lòng phải dùng cột phụ để đổi về.
• Sheet khung bảng giá lại dùng "Merge Cell" tại các cột quan trọng cần so khớp, vì vậy để giảm thiểu công thức cũng buộc lòng phải dùng cột phụ.
• Trọng lượng tối thiểu trước trong file mẫu thì có, trong bảng thực tế thì không nên phải tạo thêm cột trong bảng giá này (cột E)
• Một số mục như Gia cầm, bưu phẩm bưu kiện, theo khung trọng lượng tại mức: 45,100,300 không có giá, nên khi tính toán nếu trọng lượng thực tế tại các mức này sẽ bằng 0.
• Name: "DongThoa" điều chỉnh:

  =OFFSET('15apr-further'!$E$5:$K$5,LOOKUP(2,1/($P2='15apr-further'!$P$6:$P$46)/($H2>='15apr-further'!$M$6:$M$46)/($H2<='15apr-further'!$N$6:$N$46)/('15apr-further'!$O$6:$O$46*1=IF(AND(P2="HANSGN",WEEKDAY(H2)<3,MATCH(H2,'15apr-further'!$W$2:$AA$2)>2),2,MOD(MATCH(K2,OFFSET('15apr-further'!$R$2:$V$2,MATCH($P2,'15apr-further'!$Q$2:$Q$3,)-1,)),2)))/(TRIM($L2)='15apr-further'!$C$6:$C$46),ROW($1:$500)),)*CHOOSE({1,2,3,4,5,6},1,1,$F2,$F2,$F2,$F2)

• Công thức "Thành tiền" như cũ:

  M2=INDEX(DongThoa,SUMPRODUCT(N(F2>CHOOSE({1,2,3,4,5},0,INDEX(DongThoa,1),45,100,300)))+1)

  Enter, fill xuống.
• Một số lưu ý có ghi thêm trong file để bạn thực hiện đúng.

Thân

Chịu khó tìm hiểu lắm nhưng cũng không hiểu nỗi,

 

[dannd]

Quên mất thông tin, đối với các mã hàng mà phía trước có giá (VD mức 100, 300 có giá, 500 không có giá) thì mức giá phía sau lấy theo giá trước.
Bạn làm giúp cho mình như file test thôi, bo việc lựa chọn thứ bảy chủ nhật, hoặc theo khoảng thời gian đi vì thực tế giá thay đổi liên tục, theo ngày, tuần, tháng, ... chỉ cần theo tham số giờ trong ngày thôi. Các tham số thứ, tuần, hoặc khoảng thời gian mình dùng filter rồi copy và paste là OK. Bạn làm trên file gốc mình gửi thì tốt cho mình.

Ths.

 

[Loc Uyen]

Chịu khó tìm hiểu lắm nhưng cũng không hiểu nỗi,

Em cũng vậy, căng não ra luôn, hichic

 

[Phan Thế Hiệp]

Quên mất thông tin, đối với các mã hàng mà phía trước có giá (VD mức 100, 300 có giá, 500 không có giá) thì mức giá phía sau lấy theo giá trước.
Bạn làm giúp cho mình như file test thôi, bo việc lựa chọn thứ bảy chủ nhật, hoặc theo khoảng thời gian đi vì thực tế giá thay đổi liên tục, theo ngày, tuần, tháng, ... chỉ cần theo tham số giờ trong ngày thôi. Các tham số thứ, tuần, hoặc khoảng thời gian mình dùng filter rồi copy và paste là OK. Bạn làm trên file gốc mình gửi thì tốt cho mình.
Ths.

"File gốc" mà bạn đã upload lên và muốn điền công thức vào đó, thì nó đang tại bài số mấy ở trên đây?

Thân

Chịu khó tìm hiểu lắm nhưng cũng không hiểu nỗi,

Em cũng vậy, căng não ra luôn, hichic

Nếu thực sự muốn, huỡn chút anh sẽ ghi giải thuật gợi ý.

Thân

 

[Loc Uyen]

Nếu thực sự muốn, huỡn chút anh sẽ ghi giải thuật gợi ý.

 

[saobekhonglac]

"File gốc" mà bạn đã upload lên và muốn điền công thức vào đó, thì nó đang tại bài số mấy ở trên đây?

Thân


Nếu thực sự muốn, huỡn chút anh sẽ ghi giải thuật gợi ý.

Thân

Cám ơn anh trước

 

[dannd]

Bạn tính cột Thành tiền trong Sheet "Thucte" giúp mình ở file đính kèm. Các xử lý liên quan Thứ, khoảng thời gian mình sẽ tự lọc theo filter để đỡ phức tạp vì bảng giá nhiều lắm.
KL Định mức trong File test chính bằng giá trị cột M/cột N.
Hàng thông thường không có mã hàng.
Nếu giá trong cột Q45, Q100, Q300, Q500 không có thì giá bàng giá tương ứng trước đó (cột N). Tương tự nếu giá trong cột Q100, Q300, Q500 thì giá bằng giá tương ứng trong cột Q45, ...

 

[Phan Thế Hiệp]

Bạn tính cột Thành tiền trong Sheet "Thucte" giúp mình ở file đính kèm. Các xử lý liên quan Thứ, khoảng thời gian mình sẽ tự lọc theo filter để đỡ phức tạp vì bảng giá nhiều lắm.
KL Định mức trong File test chính bằng giá trị cột M/cột N.
Hàng thông thường không có mã hàng.
Nếu giá trong cột Q45, Q100, Q300, Q500 không có thì giá bàng giá tương ứng trước đó (cột N). Tương tự nếu giá trong cột Q100, Q300, Q500 thì giá bằng giá tương ứng trong cột Q45, ...

Xem file đính kèm.

Thân

 

[dannd]

Cảm ơn bạn rất nhiều.

 

[Phan Thế Hiệp]

Cám ơn anh trước

Gợi ý cho file bài giải đính kèm bài #10:
Công thức:
Name "Dong thoa"=OFFSET('15apr-further'!$E$5:$K$5, LOOKUP(2,1/ ($P2='15apr-further'!$P$6:$P$46)

/ ($H2>='15apr-further'!$M$6:$M$46)​

/ ($H2<='15apr-further'!$N$6:$N$46)​

/ ('15apr-further'!$O$6:$O$46*1=IF(AND(P2="HANSGN",WEEKDAY(H2)<3,MATCH(H2,'15apr-further'!$W$2:$AA$2)>2),2,MOD(MATCH(K2,OFFSET('15apr-further'!$R$2:$V$2,MATCH($P2,'15apr-further'!$Q$2:$Q$3,)-1,)),2))) ​

/ (TRIM($L2)='15apr-further'!$C$6:$C$46), ROW($1:$500)) ,)​

• Là bài toán "Truy tìm theo nhiều điều kiện" nên chắc chắn phải nhờ đến LOOKUP(2,1/...), để trả về số dòng chứa khung giá phù hợp
• Dùng OFFSET() đến dòng vừa tìm được (nhờ Lookup()), để mang cả hàng khung giá (tức cột E đến cột K), gồm cả: "Khối lượng tối thiểu"; "Đơn giá tối thiểu"; Mức 1: mức tối thiểu"; "Mức 2: 45"; "Mức 3: 100"; "Mức 4: 300"; "Mức 5: 500".
• Điều kiện 1: ($P2='15apr-further'!$P$6:$P$46) hay điều kiện cuối: (TRIM($L2)='15apr-further'!$C$6:$C$46) thì đã biết nên không giải thích thêm.
• Điều kiện 2 và 3: dùng lọc những dòng có ngày thực tế so khớp trong khoảng ngày hiệu lực áp dụng bảng giá phù hợp.
• Điều kiện 4: dùng lọc "dạng giờ" có phân ra ngày thứ 3 đến thứ 7, và ngày thứ 2 & Chủ nhật:
  • '15apr-further'!$O$6:$O$46*1, cột O này được phân bằng tay, xem thêm chú thích tại: R4,R5,R6 của Sheet '15apr-further'.
  • IF(AND(P2="HANSGN",WEEKDAY(H2)<3,MATCH(H2,'15apr-further'!$W$2:$AA$2)>2),2,MOD(MATCH(K2,OFFSET('15apr-further'!$R$2:$V$2,MATCH($P2,'15apr-further'!$Q$2:$Q$3,)-1,)),2))
    • AND(P2="HANSGN",WEEKDAY(H2)<3,MATCH(H2,'15apr-further'!$W$2:$AA$2)>2): là điều kiện lọc "hành trình: HANSGN", và chỉ chọn ngày thứ 2 & CN, và Ngày hiệu lực từ 28/04/2018. Nếu thỏa điều kiện này thì "dạng giờ là 2.
    • Nếu không thỏa, tức các dạng giờ còn lại, thì thực hiện: MOD(MATCH(K2,OFFSET('15apr-further'!$R$2:$V$2,MATCH($P2,'15apr-further'!$Q$2:$Q$3,)-1,)),2), tức quy đổi "Giờ VC" tại cột K sheet "Thucte", thành các mốc giờ: 1-2-3-4-5, dùng MOD( 'Mốc giờ' ,2) để đổi các mốc giờ lẻ thành 1, các mốc giờ chẳn thành 0.

Thân