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

Liên hệ QC

kh0jy3n

Thành viên thường trực
Tham gia
21/4/12
Bài viết
345
Được thích
115
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

  • BẢNG CHÂM CÔNG CHO BẢO Vệ.xlsm
    1,011.2 KB · Đọc: 33
Ngày mới tốt lành ạ .
 
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?
 
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
 
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?
 
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 !
 
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

  • BẢNG CHÂM CÔNG CHO BẢO Vệ.xlsm
    1,009.8 KB · Đọc: 20
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:
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:
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 ạ .
 
@@ 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,"")
 
Web KT
Back
Top Bottom