Hàm Lookup kết hợp hàm offset (4 người xem)

[kh0jy3n]

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ế ạ.

Em cảm ơn!

Em Khởi

 

[kh0jy3n]

Ngày mới tốt lành ạ .

 

[vanthinh3101]

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ế ạ.

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]

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]

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]

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]

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

=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

 

[vanthinh3101]

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

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: 13/2/19

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

=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ư ý

 

[Phan Thế Hiệp]

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

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.

 

[kh0jy3n]

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

=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]

@@ hnay em mới rảnh ngồi làm tiếp cái này :-s công thức dài quá @@

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,"")