Điền giá trị từ bảng tham chiếu vào cột theo nhiều điều kiện

[thaihung85]

Dạ kính nhờ anh chị hỗ trợ giúp em công thức ở CỘT H để lấy giá trị từ bảng tham chiếu ở Sheet BIỂU GIÁ theo các điều kiện ở CỘT C, F, G kèm theo 2 ngoại lệ sau:
1. Nếu ô nào ở CỘT B rỗng sẽ sử dụng "Đơn giá phụ" của bảng biểu giá
2. Nếu giá trị CỘT G từ 30.000 trở lên thì cũng sử dụng đơn giá của Số lượng in 30.000 trong bảng biểu giá.

Xin cảm ơn anh chị rất nhiều!

 

[Phuocam]

Dạ kính nhờ anh chị hỗ trợ giúp em công thức ở CỘT H để lấy giá trị từ bảng tham chiếu ở Sheet BIỂU GIÁ theo các điều kiện ở CỘT C, F, G kèm theo 2 ngoại lệ sau:
1. Nếu ô nào ở CỘT B rỗng sẽ sử dụng "Đơn giá phụ" của bảng biểu giá
2. Nếu giá trị CỘT G từ 30.000 trở lên thì cũng sử dụng đơn giá của Số lượng in 30.000 trong bảng biểu giá.

Xin cảm ơn anh chị rất nhiều!

Trong file không có kết quả mẫu, trường hợp số lượng in là 750 thì lấy giá là 500 hay 1000? Số lượng in là 250 thì lấy giá bao nhiêu?

 

[thaihung85]

Trong file không có kết quả mẫu, trường hợp số lượng in là 750 thì lấy giá là 500 hay 1000? Số lượng in là 250 thì lấy giá bao nhiêu?

Dạ xin lỗi em sót thông tin như sau:
- Số lượng in dưới 500 thì được tính giá như 500.
- Trường hợp số lượng in không trùng số lượng trong biểu giá được tính như sau: số lượng chính nhân với đơn giá chính, phần còn lại nhân với đơn giá in phụ (đơn giá in phụ chỉ áp dụng cho số lượng in từ 500 đến 30000). VD nếu số lượng in 750 thì 500 x đơn giá chính, còn lại 250 x đơn giá phụ)

Em có update lại file mới.

Em xin cảm ơn ạ.

 

[Phuocam]

- Trường hợp số lượng in không trùng số lượng trong biểu giá được tính như sau: số lượng chính nhân với đơn giá chính, phần còn lại nhân với đơn giá in phụ (đơn giá in phụ chỉ áp dụng cho số lượng in từ 500 đến 30000). VD nếu số lượng in 750 thì 500 x đơn giá chính, còn lại 250 x đơn giá phụ)

Có chắc chắn là như vậy không? Bạn hỏi lại người ra đề/ công ty/ cơ quan cho chắc.

 

[thaihung85]

Dạ theo văn bản dưới ạ. Số lượng dưới 500 và trên 30.000 sẽ tính theo 2 mốc đó.

Theo ví dụ trên thì có STT 5 và 6 thì số lượng phải tách ra để nhân 2 đơn giá.

 

[hoangtuaotrang_hp_vn]

Dạ theo văn bản dưới ạ. Số lượng dưới 500 và trên 30.000 sẽ tính theo 2 mốc đó.

Theo ví dụ trên thì có STT 5 và 6 thì số lượng phải tách ra để nhân 2 đơn giá.

Mình làm thêm nhiều cột phụ, nếu gấp bạn có thể sử dụng tạm

 

[cantl]

CỘT H

Dò lại xem sao nhé:
H4=INDEX(OFFSET('BIỂU GIÁ'!$A$1,3,SUM(--(F4='BIỂU GIÁ'!$A$1:$G$1)*COLUMN('BIỂU GIÁ'!$A$1:$G$1))-1,COUNTA('BIỂU GIÁ'!$A:$A)-2,3),SUM(--(G4>='BIỂU GIÁ'!$A$4:$A$35)),SUM(--(--C4>={1,2,4})))

 

[thaihung85]

Mình làm thêm nhiều cột phụ, nếu gấp bạn có thể sử dụng tạm

Cảm ơn bạn nhiều nhé

Bài đã được tự động gộp: Thứ tư lúc 14:27

Dò lại xem sao nhé:
H4=INDEX(OFFSET('BIỂU GIÁ'!$A$1,3,SUM(--(F4='BIỂU GIÁ'!$A$1:$G$1)*COLUMN('BIỂU GIÁ'!$A$1:$G$1))-1,COUNTA('BIỂU GIÁ'!$A:$A)-2,3),SUM(--(G4>='BIỂU GIÁ'!$A$4:$A$35)),SUM(--(--C4>={1,2,4})))

Dạ em add vô ra đúng rồi kết quả rồi

Còn một chút nữa là làm cách nào để sử dụng đơn giá phụ khi số lượng in không trùng số lượng trong biểu giá chuẩn không ạ?
Cảm ơn anh!

 

[cantl]

Cảm ơn bạn nhiều nhé

Bài đã được tự động gộp: Thứ tư lúc 14:27

Dạ em add vô ra đúng rồi kết quả rồi

Còn một chút nữa là làm cách nào để sử dụng đơn giá phụ khi số lượng in không trùng số lượng trong biểu giá chuẩn không ạ?
Cảm ơn anh!

Úi zời, khó thế cơ à.

 

[thaihung85]

Úi zời, khó thế cơ à.

Dạ đúng rồi, em mò chỗ đó hoài không được.
VD nếu số lượng in 750 thì 500 x đơn giá chính, còn lại 250 x đơn giá phụ

 

[huuthang_bd]

Dò lại xem sao nhé:
H4=INDEX(OFFSET('BIỂU GIÁ'!$A$1,3,SUM(--(F4='BIỂU GIÁ'!$A$1:$G$1)*COLUMN('BIỂU GIÁ'!$A$1:$G$1))-1,COUNTA('BIỂU GIÁ'!$A:$A)-2,3),SUM(--(G4>='BIỂU GIÁ'!$A$4:$A$35)),SUM(--(--C4>={1,2,4})))

Làm gì mà loằng ngoằng vậy, đơn giá thì vầy thôi.

=VLOOKUP(MAX(500;G3);'BIỂU GIÁ'!$A$5:$G$36;2+(F3="ĐL")*3+LOG(C3;2))

--

nếu số lượng in 750 thì 500 x đơn giá chính, còn lại 250 x đơn giá phụ

Công thức tính ra thành tiền, nếu muốn thì bạn thêm cột đơn giá phụ và tách công thức ra.

=(
LOOKUP(MAX(500;G3);'BIỂU GIÁ'!$A$5:$A$36)*VLOOKUP(MAX(500;G3);'BIỂU GIÁ'!$A$5:$G$36;2+(F3="ĐL")*3+LOG(C3;2))+
IF(AND(G3>500;G3<30000);G3-LOOKUP(G3;'BIỂU GIÁ'!$A$5:$A$36);0)*INDEX('BIỂU GIÁ'!$B$37:$G$37;1+(F3="ĐL")*3+LOG(C3;2))
)*D3*E3

Ghi chú: Màu xanh tính đơn giá chính, màu đỏ tính đơn giá phụ, in nghiêng là số lượng, in đậm là đơn giá.

 

[Phuocam]

Dạ xin lỗi em sót thông tin như sau:
- Số lượng in dưới 500 thì được tính giá như 500.

Ví dụ in 300 cuốn thì giá như 500 x số lượng là 300 hay 500 ?

Một số trường hợp thấy bất hợp lý, ví dụ SST1:

1. Nếu số lượng in là 1000, thì số tiền phải trả là: 3321360, giá bình quân 1 tờ là: 46.13

2. Nếu số lượng in là 999, thì số tiền phải trả là: 3033655.36, giá bình quân 1 tờ là: 42.45

Thường thì số lượng in càng nhiều, giá bình quân càng rẻ mới đúng, ngoài trừ ngành điện.

 

[thaihung85]

Ví dụ in 300 cuốn thì giá như 500 x số lượng là 300 hay 500 ?

Một số trường hợp thấy bất hợp lý, ví dụ SST1:

1. Nếu số lượng in là 1000, thì số tiền phải trả là: 3321360, giá bình quân 1 tờ là: 46.13

2. Nếu số lượng in là 999, thì số tiền phải trả là: 3033655.36, giá bình quân 1 tờ là: 42.45

Thường thì số lượng in càng nhiều, giá bình quân càng rẻ mới đúng, ngoài trừ ngành điện.

Nếu in số lượng 300 thì tiền = 300 x đơn giá của 500 nha bạn

Bài đã được tự động gộp: Thứ tư lúc 20:40

Làm gì mà loằng ngoằng vậy, đơn giá thì vầy thôi.

=VLOOKUP(MAX(500;G3);'BIỂU GIÁ'!$A$5:$G$36;2+(F3="ĐL")*3+LOG(C3;2))

--

Công thức tính ra thành tiền, nếu muốn thì bạn thêm cột đơn giá phụ và tách công thức ra.

=(
LOOKUP(MAX(500;G3);'BIỂU GIÁ'!$A$5:$A$36)*VLOOKUP(MAX(500;G3);'BIỂU GIÁ'!$A$5:$G$36;2+(F3="ĐL")*3+LOG(C3;2))+
IF(AND(G3>500;G3<30000);G3-LOOKUP(G3;'BIỂU GIÁ'!$A$5:$A$36);0)*INDEX('BIỂU GIÁ'!$B$37:$G$37;1+(F3="ĐL")*3+LOG(C3;2))
)*D3*E3

Ghi chú: Màu xanh tính đơn giá chính, màu đỏ tính đơn giá phụ, in nghiêng là số lượng, in đậm là đơn giá.

Công thức bạn viết gọn quá mà sao mình add vào chưa được ạ

 

[thaihung85]

Mình làm thêm nhiều cột phụ, nếu gấp bạn có thể sử dụng tạm

Mình có làm thử cách này nhưng điều kiện đơn giá phụ chỉ áp dụng với số lượng in từ 500 đến 30.000 chưa thỏa ạ

 

[huuthang_bd]

Công thức bạn viết gọn quá mà sao mình add vào chưa được ạ

Chưa được cụ thể là như thế nào bạn phải nói rõ. Là nhập công thức bị báo lỗi, công thức trả về kết quả lỗi, kết quả sai hay thế nào.

 

[thaihung85]

Chưa được cụ thể là như thế nào bạn phải nói rõ. Là nhập công thức bị báo lỗi, công thức trả về kết quả lỗi, kết quả sai hay thế nào.

mình nhập công thức vào file trên báo lỗi bạn ạ. Mình đang dùng Excel 2016, bạn xem lại giúp mình với nhé

 

[huuthang_bd]

mình nhập công thức vào file trên báo lỗi bạn ạ. Mình đang dùng Excel 2016, bạn xem lại giúp mình với nhé

Khả năng là do thiết lập dấu phân cách của máy khác với công thức, cái này ai dùng excel cũng phải biết.

=VLOOKUP(MAX(500,G3),'BIỂU GIÁ'!$A$5:$G$36,2+(F3="ĐL")*3+LOG(C3,2))

=(
LOOKUP(MAX(500,G3),'BIỂU GIÁ'!$A$5:$A$36)*VLOOKUP(MAX(500,G3),'BIỂU GIÁ'!$A$5:$G$36,2+(F3="ĐL")*3+LOG(C3,2))+
IF(AND(G3>500,G3<30000),G3-LOOKUP(G3,'BIỂU GIÁ'!$A$5:$A$36),0)*INDEX('BIỂU GIÁ'!$B$37:$G$37,1+(F3="ĐL")*3+LOG(C3,2))
)*D3*E3

 

[hoangtuaotrang_hp_vn]

Mình có làm thử cách này nhưng điều kiện đơn giá phụ chỉ áp dụng với số lượng in từ 500 đến 30.000 chưa thỏa ạ

Bạn tải lại đi, mình update file mới nhất rồi. Nếu chưa thỏa chỗ nào thì bạn chi chú lại giúp mình nhé

 

[thaihung85]

Khả năng là do thiết lập dấu phân cách của máy khác với công thức, cái này ai dùng excel cũng phải biết.

=VLOOKUP(MAX(500,G3),'BIỂU GIÁ'!$A$5:$G$36,2+(F3="ĐL")*3+LOG(C3,2))

=(
LOOKUP(MAX(500,G3),'BIỂU GIÁ'!$A$5:$A$36)*VLOOKUP(MAX(500,G3),'BIỂU GIÁ'!$A$5:$G$36,2+(F3="ĐL")*3+LOG(C3,2))+
IF(AND(G3>500,G3<30000),G3-LOOKUP(G3,'BIỂU GIÁ'!$A$5:$A$36),0)*INDEX('BIỂU GIÁ'!$B$37:$G$37,1+(F3="ĐL")*3+LOG(C3,2))
)*D3*E3

Mình đưa công thức vào ra đúng kết quả rồi bạn

Còn chỗ này chưa thỏa, đơn giá phụ chỉ áp dụng cho khung số lượng in từ 500 đến 30.000

Theo ví dụ trên có ô G3, G15, G19 số lượng ngoài khung này. Số lượng in trên 30.000 bản được tính tiền công in như 30.000 bản

Cảm ơn bạn nhiều lắm!

 

[thaihung85]

Bạn tải lại đi, mình update file mới nhất rồi. Nếu chưa thỏa chỗ nào thì bạn chi chú lại giúp mình nhé

Hình như bạn chưa up file mới. Mình có sửa lại công thức ở cột thành tiền chút thì ra đúng yêu cầu.
Cảm ơn bạn nhé.

Bài đã được tự động gộp: Thứ sáu lúc 00:31

Mình đưa công thức vào ra đúng kết quả rồi bạn

Còn chỗ này chưa thỏa, đơn giá phụ chỉ áp dụng cho khung số lượng in từ 500 đến 30.000

Theo ví dụ trên có ô G3, G15, G19 số lượng ngoài khung này. Số lượng in trên 30.000 bản được tính tiền công in như 30.000 bản

Cảm ơn bạn nhiều lắm!
View attachment 303916

huuthang_bd​

Có thời gian xem lại giúp mình chút nữa nhé. Gần hoàn chỉnh hết rồi bạn
Mình xin cảm ơn