Hàm Lookup kết hợp hàm offset

kh0jy3n

Thành viên hoạt động
Tham gia ngày
21 Tháng tư 2012
Bài viết
148
Thích
41
Điểm
370
#1
Gửi các Bác

Mấy bác cho em hỏi em dùng hàm LOOKUP kết hợp với hàm OFFSET kết quả ở ô em biết thì đúng nhưng cứ kéo sang thì lại bị #N/A . còn kéo xuống thì kế quả trả về vẫn đúng.

Sheet : CHECK TĂNG CƯỜNG Ô em tô màu vàng thì thì kết quả trả về là đúng ạ.
với đoạn công thức Offset em đã kiểm tra bằng hàm SUM + Offset thì trả về kết quả đúng ạ

Bác nào rảnh kiểm tra hộ em với ạ. ngồi nghĩ mã mà không hiểu sao lại bị như thế ạ.:unknw::unknw:

Em cảm ơn!

Em Khởi
 

File đính kèm

vanthinh3101

Thành viên tích cực
Tham gia ngày
24 Tháng một 2015
Bài viết
813
Thích
872
Điểm
360
Tuổi
31
#3
Gửi các Bác

Mấy bác cho em hỏi em dùng hàm LOOKUP kết hợp với hàm OFFSET kết quả ở ô em biết thì đúng nhưng cứ kéo sang thì lại bị #N/A . còn kéo xuống thì kế quả trả về vẫn đúng.

Sheet : CHECK TĂNG CƯỜNG Ô em tô màu vàng thì thì kết quả trả về là đúng ạ.
với đoạn công thức Offset em đã kiểm tra bằng hàm SUM + Offset thì trả về kết quả đúng ạ

Bác nào rảnh kiểm tra hộ em với ạ. ngồi nghĩ mã mà không hiểu sao lại bị như thế ạ.:unknw::unknw:

Em cảm ơn!

Em Khởi
Nhìn công thức mà tôi thấy "lạnh" người, không luận ra được.
Bạn có thể nói rõ yêu cầu của kết quả không, biết đâu mọi người có thể dùng công thức khác hay hơn?
 

kh0jy3n

Thành viên hoạt động
Tham gia ngày
21 Tháng tư 2012
Bài viết
148
Thích
41
Điểm
370
#4
Nhìn công thức mà tôi thấy "lạnh" người, không luận ra được.
Bạn có thể nói rõ yêu cầu của kết quả không, biết đâu mọi người có thể dùng công thức khác hay hơn?
Dạ

Ở Sheet CHECK TĂNG CƯỜNG hàng tháng yêu cầu check từng mục tiêu số thời gian làm ca ngày và ca đêm. Nếu làm đủ là 12h/ ngày + thêm thời gian tăng ca. Dữ liệu được lấy từ sheet BẢNG CHẤM CÔNG.
P = Nghỉ Phép / TD= Nghỉ Tự Do.
Em Khởi
 

vanthinh3101

Thành viên tích cực
Tham gia ngày
24 Tháng một 2015
Bài viết
813
Thích
872
Điểm
360
Tuổi
31
#5
Dạ

Ở Sheet CHECK TĂNG CƯỜNG hàng tháng yêu cầu check từng mục tiêu số thời gian làm ca ngày và ca đêm. Nếu làm đủ là 12h/ ngày + thêm thời gian tăng ca. Dữ liệu được lấy từ sheet BẢNG CHẤM CÔNG.
P = Nghỉ Phép / TD= Nghỉ Tự Do.
Em Khởi
Tôi vẫn chưa rõ.
1. Ở cột Tăng ca ở Sheet Bảng chấm công, tôi thấy có giá trị từ 1-5 --> đây là số giờ làm việc thêm giờ đúng không?
2. Từ cột N trở đi ở Sheet Bảng chấm công, 1 ở đây là 1 ngày làm việc đúng không? Nếu đúng, 1 ngày làm việc này là 12h làm việc đúng không?
3. Tôi lấy 1 ví dụ cụ thể theo dữ liệu của bạn, bạn xem có đúng không?
- Mục tiêu REGINA A, ca làm việc đêm
- Có 3 người làm việc và tăng ca là 2, 3, 2
--> Kết quả là 3*12 + (2+3+2) = 43 đúng không?
 

kh0jy3n

Thành viên hoạt động
Tham gia ngày
21 Tháng tư 2012
Bài viết
148
Thích
41
Điểm
370
#6
Tôi vẫn chưa rõ.
1. Ở cột Tăng ca ở Sheet Bảng chấm công, tôi thấy có giá trị từ 1-5 --> đây là số giờ làm việc thêm giờ đúng không?
2. Từ cột N trở đi ở Sheet Bảng chấm công, 1 ở đây là 1 ngày làm việc đúng không? Nếu đúng, 1 ngày làm việc này là 12h làm việc đúng không?
3. Tôi lấy 1 ví dụ cụ thể theo dữ liệu của bạn, bạn xem có đúng không?
- Mục tiêu REGINA A, ca làm việc đêm
- Có 3 người làm việc và tăng ca là 2, 3, 2
--> Kết quả là 3*12 + (2+3+2) = 43 đúng không?
Gửi Bác

Dạ đúng rồi ạ

3 người làm x 12h / người + thời gian tăng ca của cả 3 người ạ.

Em cảm ơn !
 

Phan Thế Hiệp

Thành viên gắn bó
Tham gia ngày
10 Tháng mười 2017
Bài viết
1,773
Thích
4,872
Điểm
360
Tuổi
47
#7
Gửi Bác

Dạ đúng rồi ạ

3 người làm x 12h / người + thời gian tăng ca của cả 3 người ạ.

Em cảm ơn !
Phải vầy không!?
1/ Sheet "Bảng Chấm Công": điều chỉnh công thức cột L: thêm DATE($B$1,$B$2+1,0)
2/ Sheet "Check tăng cường": dùng công thức mảng
Mã:
=SUM(IFERROR(1/(1/OFFSET('BẢNG CHẤM CÔNG'!$N$6,,MATCH(D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5)-1,995,)*($B5='BẢNG CHẤM CÔNG'!$A$6:$A$1000)*($C5='BẢNG CHẤM CÔNG'!$H$6:$H$1000))+12,))
Kết thúc bằng Ctrl+Shift+Enter.

Thân
 

File đính kèm

vanthinh3101

Thành viên tích cực
Tham gia ngày
24 Tháng một 2015
Bài viết
813
Thích
872
Điểm
360
Tuổi
31
#8
Gửi Bác

Dạ đúng rồi ạ

3 người làm x 12h / người + thời gian tăng ca của cả 3 người ạ.

Em cảm ơn !
Bạn thử công thức này xem sao.
Làm theo đúng ý tưởng bên trên.
Công thức cho Sheet CHECK TĂNG CƯỜNG
PHP:
D5=SUMPRODUCT(('BẢNG CHẤM CÔNG'!$A$6:$A$20='CHECK TĂNG CƯỜNG '!$B5)*('BẢNG CHẤM CÔNG'!$H$6:$H$20='CHECK TĂNG CƯỜNG '!$C5),OFFSET('BẢNG CHẤM CÔNG'!$M$6:$M$20,,MATCH('CHECK TĂNG CƯỜNG '!D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5,0)))*12+SUMIFS('BẢNG CHẤM CÔNG'!$K$6:$K$20,'BẢNG CHẤM CÔNG'!$A$6:$A$20,'CHECK TĂNG CƯỜNG '!B$6,'BẢNG CHẤM CÔNG'!$H$6:$H$20,'CHECK TĂNG CƯỜNG '!$C5)
Enter, Fill sang phải, Fill xuống.

P/s: Bạn xem lại nhé, làm sao mà thời gian tăng ca ngày nào cũng như nhau được --> cách tính có vấn đề.
Bài đã được tự động gộp:

Phải vầy không!?
1/ Sheet "Bảng Chấm Công": điều chỉnh công thức cột L: thêm DATE($B$1,$B$2+1,0)
2/ Sheet "Check tăng cường": dùng công thức mảng
Mã:
=SUM(IFERROR(1/(1/OFFSET('BẢNG CHẤM CÔNG'!$N$6,,MATCH(D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5)-1,995,)*($B5='BẢNG CHẤM CÔNG'!$A$6:$A$1000)*($C5='BẢNG CHẤM CÔNG'!$H$6:$H$1000))+12,))
Kết thúc bằng Ctrl+Shift+Enter.

Thân
Anh ơi, anh giải thích cho em về công thức này với.
Tết xong làm em "lú lẫn" luôn, luận mãi không ra.

Em chúc anh và gia đình năm mới dồi dào sức khỏe, vạn sự như ý ;)
 
Lần chỉnh sửa cuối:

Phan Thế Hiệp

Thành viên gắn bó
Tham gia ngày
10 Tháng mười 2017
Bài viết
1,773
Thích
4,872
Điểm
360
Tuổi
47
#9
Bạn thử công thức này xem sao.
Làm theo đúng ý tưởng bên trên.
Công thức cho Sheet CHECK TĂNG CƯỜNG
PHP:
D5=SUMPRODUCT(('BẢNG CHẤM CÔNG'!$A$6:$A$20='CHECK TĂNG CƯỜNG '!$B5)*('BẢNG CHẤM CÔNG'!$H$6:$H$20='CHECK TĂNG CƯỜNG '!$C5),OFFSET('BẢNG CHẤM CÔNG'!$M$6:$M$20,,MATCH('CHECK TĂNG CƯỜNG '!D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5,0)))*12+SUMIFS('BẢNG CHẤM CÔNG'!$K$6:$K$20,'BẢNG CHẤM CÔNG'!$A$6:$A$20,'CHECK TĂNG CƯỜNG '!B$6,'BẢNG CHẤM CÔNG'!$H$6:$H$20,'CHECK TĂNG CƯỜNG '!$C5)
Enter, Fill sang phải, Fill xuống.

P/s: Bạn xem lại nhé, làm sao mà thời gian tăng ca ngày nào cũng như nhau được --> cách tính có vấn đề.
Cẩn thận em trai! :)

Công thức của em: Lấy cột giờ tăng ca x 12h, rồi cộng tổng số giờ tăng ca thì số tăng ca bự lắm đó.
Ví dụ:
  • người thứ 1: làm 2h x 12h=24h, rồi cộng thêm: 2h => 26h chỉ trong ngày 01 tây
  • người thứ 2: làm 1.5h x 12h=18h, rồi cộng thêm: 1.5h => 19.5h trong ngày chỉ trong ngày 01 tây
  • người thứ 3: làm 1h x 12h=12h, rồi cộng thêm: 1h => 13h trong ngày chỉ trong ngày 01 tây
  • Tổng cộng 3 người làm sẽ là: 26+19.5+13=58.5h lận.
Chủ thớt đã ghi: "3 người làm x 12h / người + thời gian tăng ca của cả 3 người ạ", theo anh nghĩ có lẽ là người nào có giờ tăng ca (tức tăng ca>0) thì cộng thêm 12h, rồi tổng cộng các người đó theo nhóm và buổi "N/D" trong ngày hôm đó. Do vậy công thức của anh là:
  • người thứ 1: làm 2h + 12h=14h chỉ trong ngày 01 tây
  • người thứ 2: làm 1.5h + 12h=13.5h chỉ trong ngày 01 tây
  • người thứ 3: làm 1h + 12h=13hchỉ trong ngày 01 tây
  • Tổng cộng 3 người làm sẽ là: 14+13.5+13= chỉ 40.5h.
  • Hay= (3 x12h) + (2+1.5+1) = 40.5h như cách tính của chủ thớt.
Còn mấy người không cùng nhóm, hoặc có các ký tự không là số như "P" hay "TD", hoặc có số giờ tăng ca =0 thì bị loại trừ bởi bẫy:
Iferror( 1/(1/...) , 0) rồi, nên không cần ghi thêm điều kiện đó ra nữa.

Chúc em ngày vui.
/-*+//-*+//-*+/
 
Lần chỉnh sửa cuối:

kh0jy3n

Thành viên hoạt động
Tham gia ngày
21 Tháng tư 2012
Bài viết
148
Thích
41
Điểm
370
#10
Phải vầy không!?
1/ Sheet "Bảng Chấm Công": điều chỉnh công thức cột L: thêm DATE($B$1,$B$2+1,0)
2/ Sheet "Check tăng cường": dùng công thức mảng
Mã:
=SUM(IFERROR(1/(1/OFFSET('BẢNG CHẤM CÔNG'!$N$6,,MATCH(D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5)-1,995,)*($B5='BẢNG CHẤM CÔNG'!$A$6:$A$1000)*($C5='BẢNG CHẤM CÔNG'!$H$6:$H$1000))+12,))
Kết thúc bằng Ctrl+Shift+Enter.

Thân
Thời gian tăng ca là khác nhau,ở cột đó em dang vd cho mấy dòng ạ.
Em cảm ơn ạ .
 

kh0jy3n

Thành viên hoạt động
Tham gia ngày
21 Tháng tư 2012
Bài viết
148
Thích
41
Điểm
370
#11
@@ hnay em mới rảnh ngồi làm tiếp cái này :-s công thức dài quá @@
Mã:
IFERROR(IFERROR(SUMIFS(OFFSET('BẢNG CHẤM CÔNG'!$N$6,0,MATCH(D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5)-1,995,),'BẢNG CHẤM CÔNG'!$A$6:$A$1000,'CHECK TĂNG CƯỜNG '!$B5,'BẢNG CHẤM CÔNG'!$H$6:$H$1000,'CHECK TĂNG CƯỜNG '!$C5),"")+SUM(COUNTIFS('BẢNG CHẤM CÔNG'!$A$6:$A$1000,'CHECK TĂNG CƯỜNG '!$B5,'BẢNG CHẤM CÔNG'!$H$6:$H$1000,'CHECK TĂNG CƯỜNG '!$C5,OFFSET('BẢNG CHẤM CÔNG'!$N$6,0,MATCH(D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5)-1,995,),"")+COUNTIFS('BẢNG CHẤM CÔNG'!$A$6:$A$1000,'CHECK TĂNG CƯỜNG '!$B5,'BẢNG CHẤM CÔNG'!$H$6:$H$1000,'CHECK TĂNG CƯỜNG '!$C5,OFFSET('BẢNG CHẤM CÔNG'!$N$6,0,MATCH(D$4,'BẢNG CHẤM CÔNG'!$N$5:$NN$5)-1,995,),"<12"))*12,"")
 
Top