Option Explicit
Function CongTG(LookUpRange As Range, Optional Nghi As Boolean = False) As Integer
Dim Clls As Range, Thu As Byte
For Each Clls In LookUpRange
Thu = Weekday(Cells(9, Clls.Column).Value)
If Nghi And (Thu = 1 Or Thu = 7) Then
CongTG = CongTG + Clls.Value
ElseIf Not Nghi And (Thu <> 1 And Thu <> 7) Then
CongTG = CongTG + Clls.Value
End If
Next Clls
End FunctionOption Explicit
Function CongThG(lookupRange As Range, Optional LoaiCong As String = "C") As Integer
If LoaiCong = "" Then LoaiCong = "C"
If InStr("CLT", LoaiCong) < 1 Then
CongThG = 999: Exit Function
End If
Dim Clls As Range, Rng As Range, Thu As Byte
For Each Clls In lookupRange
Set Rng = Cells(9, Clls.Column)
Thu = Weekday(Rng.Value)
If LoaiCong = "C" And (Thu = 1 Or Thu = 7) Then
CongThG = CongThG + Clls.Value
ElseIf LoaiCong = "T" And (Thu <> 1 And Thu <> 7) Then
CongThG = CongThG + Clls.Value
ElseIf LoaiCong = "L" And Rng.Interior.ColorIndex > 2 Then
CongThG = CongThG + Clls.Value
End If
Next Clls
End FunctionCó khi mình dùng hàm trong excel cò dỡ hơn bạn nữa là đằng khác. Hãy chờ các nhà siêu công thức ấy, họ ra tay cho bạn;
Option Explicit
Function CongThG(lookupRange As Range, Optional LoaiCong As String = "C") As Integer
If LoaiCong = "" Then LoaiCong = "C"
If InStr("CLT", LoaiCong) < 1 Then
CongThG = 2999: Exit Function
End If
Dim Clls As Range, Rng As Range
Dim MyColor As Boolean, Thu As Byte
For Each Clls In lookupRange
Set Rng = Cells(9, Clls.Column)
Thu = Weekday(Rng.Value):
MyColor = Rng.Interior.ColorIndex > 2
If LoaiCong = "C" And (Thu = 1 Or Thu = 7) And Not MyColor Then
CongThG = CongThG + Clls.Value
ElseIf LoaiCong = "T" And (Thu <> 1 And Thu <> 7) And Not MyColor Then
CongThG = CongThG + Clls.Value
ElseIf LoaiCong = "L" And MyColor Then
CongThG = CongThG + Clls.Value
End If
Next Clls
End Function
Theo mình thì làm thêm một hàng nữa để điền ngày lễ!
Bạn xem file đính kèm để tham khảo thêm, mong rằng giúp bạn được điều gì đó có ích!
để khắc phục như yêu cầu trong file thì cũng đơn giản thôi mà bạn. Xem file nhé
thanks bác nhé, e phải học hỏi ở bác rất nhiều
thanks bác, em đã mail tả trong file đính kèm, bác tư vấn giải pháp hộ em nhé, có thể phân quyền kiểu tạo form đăng nhập ko ạ ? admin là quyền cao nhất dc phép sửa xóa số giờ làm thêm của bác sỹ nhập vào. Còn bác sỹ chi dc phép nhập số giờ làm thêm của mình theo các ngày tháng, ko dc phép sửa, xóa , để đảm bảo tính chính xác, tránh gian lận
Bạn chịu khó sửa bài lại, có bỏ dấu tiếng Việt, nếu không vi phạm nội quy:
Ngoài câu hỏi của anh Hoang2013, bổ sung thêm:
Đã hiểu việc trên, nhưng bạn phải giải thích lấy mốc của hai ca cho đúng, và như thế nào là trễ
Tôi thấy trong bảng chấm giờ công của bạn cũng có rất nhiều trường hợp đi trễ >10h45, như:
AS3=SUMPRODUCT(($D3:$AG3>$AB$1)*($D3:$AG3<$AC$1-TIME(1,,))*($D3:$AG3-$AB$1-($D3:$AG3>TIME(12,,))*30/60/24))+SUMPRODUCT(($D3:$AG3>$AF$1)*($D3:$AG3<$AS$1)*($D3:$AG3-$AF$1))Tôi gửi lại cho bạn file mới cập nhật thêm phần kiểm tra, bạn có thể click Mũi tên lên xuống có tại ô B51: thể hiện stt của nhân viên nào bạn muốn kiểm tra.
CT này của anh chưa chuẩn chỗ ($D3:$AG3>TIME(12,,))*30/60/24, hình như anh lấy mốc 12h là nghỉ trưa và thời gian nghỉ là 30 phút, em ví dụ lỡ là 12:19:00 thì nó thỏa đk của CT này, tức là bị trừ đi 30p còn là 12:19:00-8:30:00-0:30:00=3:19:00, nhưng thực tế là trễ tới 12:00:00-8:30:00=3:30:00 (vì rơi vào thời gian nghỉ trưa nên 12:19:00 với 12:00:00 là như nhau)!!!
Chính xác! lo tính mấy vụ kia quên khuấy mất khoảng nghỉ trưa này.
AS3=SUM(($D3:$AG3>$AB$1)*($D3:$AG3<$AC$1-TIME(1,,))*(CHOOSE(MATCH($D3:$AG3*24,{0,8.5,12,13},1),0,$D3:$AG3,TIME(12,,),$D3:$AG3-TIME(,30,))-$AB$1)+($D3:$AG3>$AF$1)*($D3:$AG3<$AS$1)*($D3:$AG3-$AF$1))
Còn trường hợp ca 2 anh không xét nghỉ giữa ca hả!!!Chính xác! lo tính mấy vụ kia quên khuấy mất khoảng nghỉ trưa này.
Cảm ơn em.
Vậy, phải chỉnh lại chút bằng việc dùng công thức mảng rồi:
Ctrl+Shift+Enter, rồi fill xuốngMã:AS3=SUM(($D3:$AG3>$AB$1)*($D3:$AG3<$AC$1-TIME(1,,))*(CHOOSE(MATCH($D3:$AG3*24,{0,8.5,12,13},1),0,$D3:$AG3,TIME(12,,),$D3:$AG3-TIME(,30,))-$AB$1)+($D3:$AG3>$AF$1)*($D3:$AG3<$AS$1)*($D3:$AG3-$AF$1))
Chúc em ngày thiệt vui.
!!!
Khuya quá ai còn vô em, lúc đó nhiều "ma" lắm nên bạn chủ thớt mới nói trễ lắm là 18h30 là hết mức rồi.Còn trường hợp ca 2 anh không xét nghỉ giữa ca hả!!!
Name=Sheet1!$D3:$AG3-{8.5;17}/24
Name2=CHOOSE(MATCH(name,{-100,3.5,4}/24),name,3.5/24,name-0.5/24)AT3=SUMPRODUCT(((name2>0))*((name2<={6;6}/24)*name2))
!!!
Thứ dữ!Hi, em tính không làm bài này rồi vì CT cũng tương đối dài, thôi cũng đưa ra cách của em ,em xét luôn thời gian nghỉ của 2 ca, mốc thời gian nghỉ là 12->12h30, và 20h30->21h, dùng mảng 2 chiều để tính cả 2 ca một lần:
Tại AT đặt các name như sau:
Sau đó dùng CT này ở At3:Mã:Name=Sheet1!$D3:$AG3-{8.5;17}/24 Name2=CHOOSE(MATCH(name,{-100,3.5,4}/24),name,3.5/24,name-0.5/24)
Fill xuống!!Mã:AT3=SUMPRODUCT(((name2>0))*((name2<={6;6}/24)*name2))
Note: Chỗ {6;6} tại sao em không để là 6 luôn vì chỗ này em quy định thời gian tối đa được phép vào trễ (đã trừ thời gian nghĩ 30p), nếu theo đúng để bài của bạn chủ topic là thì thời gian tối đa được phép vào trễ là 10:45 là 18:30 thì có thể chỉnh lại chỗ đó là {2.25,1.5}. số 3.5 trong CT của name là thời gian làm buổi đầu của 1 ca (ví dụ 8:30>12h là 3.5 giờ cộng thêm 30p nghỉ là 4h) có thể chỉnh lại tùy theo số làm việc từng buổi của ca!!!
Có chênh lệch với anh do em quy định mốc thời gian là {6;6}!!!
Lâu lắm rồi mới dùng name do công thức cũng tương đối!!!
Chờ anh Hieucd viết code cho bài này nữa là đủ bộ!!!
Haha, hên thôi anh, lâu lâu gặp một vài bài để thí nghiệm thành công thì lấy sài luôn, không thì thôi, macth xử lý được mảng 2 chiều khi đưa vào lookup_value, còn đưa vào lookup_array thì chịu.Thứ dữ!
Lần đầu tiên anh mới thấy hàm MATCH() xử mảng 2 chiều, rồi SUMPRODUCT() cũng chơi lúc 2 chiều để tính ra ca 1, ca 2.
Cái này anh bái hai tay, chịu thua. Khà khà khà.
Đáng học hỏi thiệt.
Cảm ơn em.
!!!!
