Đếm số ngày thứ 7 và chủ nhật trong 1 khoảng thời gian

[LuuGiaPhúc]

En nhờ các anh chị giúp em,
Em có 1 bảng chấm công như sau :



Hằng tháng, các văn phòng sẽ tổng hợp số buổi dạy ngoài giờ và gởi về cho em, nhiệm vụ của em là kiểm tra lại 1 lần nữa xem họ báo số liệu có đúng không, nếu đúng thì cột AJ để trống, nếu sai thì ghi số 1 vào ô tương ứng với hàng bị sai ở cột AJ.
Cách kiểm tra :
nhìn khoảng thời gian từ ngày ... đến ngày ... ở cột AD:AE : nếu có thứ 7 thì tính TỐI ĐA là 1 buổi , có CN thì TỐI ĐA là 2 buổi.
Ví dụ họ dạy từ ngày 11/02/2022 đến 14/02/2022 (có 1 ngày thứ 7 và 1 ngày CN) ==> TỐI ĐA là 3 buổi. Vậy họ có thể ghi 1 buổi, 2 buổi hoặc 3 buổi đều chấp nhận (vì họ sẽ ghi theo ngày thực tế họ có dạy). Nhưng nếu họ ghi 4 buổi là sai, lúc đó, mình phải note số 1 vào cột AJ tương ứng với hàng bị sai để trả kết quả về cho họ.

Đa số các trường hợp bị sai thường hay rơi vào tình huống :
Hàng thứ 10 : họ đăng ký lớp HAN48AR2205 vào ngày 01/02/2022 đến ngày 16/02/2022 : có 2 ngày Thứ 7 và 2 ngày CN : ==> tổng số buổi TỐI ĐA chấp nhận là 6 buổi : họ ghi 5 : ok, đúng.
Hàng thứ 11 : cũng giáo viên đó (nhìn theo cột U, trainer code), họ đăng ký lớp khác HAN48SBW2201 dạy từ 13/02/2022 đến ngày 13/02/2022 : là CN : TỐI ĐA là 2 buổi , họ ghi 2 buổi , mới nhìn tưởng đúng nhưng thật ra ngày 13/02/2022 rơi vào giai đoạn của hàng 10 (01/02/2022 đến 16/02/2022), mà tối đa giai đoạn này là 6 buổi, hàng thứ 10 họ đã ghi 5 buổi rồi nên giờ chỉ chấp nhận thêm 1 buổi thôi, họ ghi 2 buổi là sai. ==> ghi số 1 (sai) vào ô AJ11.

Ngoài ra, ở hàng thứ 19, giáo viên đăng ký lớp từ 08/02 đến 11/02 : giai đoạn này không có rơi vào T7 hay CN nên không được tính số buổi ngoài giờ ==> họ ghi 1 buổi là sai ==> kết quả là mình ghi số 1 (sai) vào ô AJ19

Hiện nay em đang phải dò tay từng hàng rất cực và cũng dễ bị sai, nhờ các anh chị giúp cách nào dò nhanh hơn và không bị sai nhé.
Cảm ơn các anh chị

 

[Hoàng Tuấn 868]

En nhờ các anh chị giúp em,

Bạn xem thử, mới tính tổng số buổi tối thực dạy trừ tổng số buổi tối đa theo lịch, chưa kiểm tra xem có dạy đúng T7 hay CN không.

 

[LuuGiaPhúc]

Bạn xem thử, mới tính tổng số buổi tối đa theo lịch trừ tổng số buổi thực dạy, chưa kiểm tra xem có dạy đúng T7 hay CN không.

Cảm ơn bạn Tuấn đã hỗ trợ dù đã khá khuya. Mình test thử thì phát hiện có trường hợp này chưa đúng :

12/02/2022 đến 16/02/2022 : có 3 buổi (1 buổi T7 và 2 buổi CN)
Sau đó đăng lý thêm lớp 20/02/2022 : 2 buổi CN
==> tổng cộng 5 buổi thôi.
Ô AK15 của bạn ra kết quả là 6 buổi là chưa đúng, bạn xem thêm giúp mình nhé


Mình cũng vừa tìm ra công thức xác định khoảng thời gian từ ngày... đến ngày ... có chứa ngày thứ 7 và ngày CN hay không
AM10=IF(($AE10-$AD10+1)-NETWORKDAYS($AD10,$AE10)>0,"x","")

 

[Hoàng Tuấn 868]

Cảm ơn bạn Tuấn đã hỗ trợ dù đã khá khuya. Mình test thử thì phát hiện có trường hợp này chưa đúng :

12/02/2022 đến 16/02/2022 : có 3 buổi (1 buổi T7 và 2 buổi CN)
Sau đó đăng lý thêm lớp 20/02/2022 : 2 buổi CN
==> tổng cộng 5 buổi thôi.
Ô AK15 của bạn ra kết quả là 6 buổi là chưa đúng,
View attachment 273935

Mình tính tổng số ngày T7 và CN trong khoảng từ ngày nhỏ nhất đến ngày lớn nhất. Công thức này tính vậy là đúng, còn đếm cụ thể từng ngày trong nhiều khoảng như vậy bằng công thức thì mình chưa nghĩ ra bạn ạ.

 

[LuuGiaPhúc]

Mình tính tổng số ngày T7 và CN trong khoảng từ ngày nhỏ nhất đến ngày lớn nhất. Công thức này tính vậy là đúng, còn đếm cụ thể từng ngày trong nhiều khoảng như vậy bằng công thức thì mình chưa nghĩ ra bạn ạ.

Cảm ơn bạn, dù sao thì với 2 cột phụ như vậy cũng đã lọc ra gần như hơn 90% tình huống rồi, cũng nhẹ khá nhiều cho mình.
Cái quy tắc đếm này, mình đếm bằng tay nhiêu lúc muốn hoa mắt luôn, hy vọng có cách nào khác chuẩn được hơn nữa thì quá tốt, còn chưa có thì 2 cột phụ của bạn cũng đã giúp mình đỡ vất vả khá nhiều rồi đó.
Cảm ơn bạn.

 

[Hoàng Tuấn 868]

Cảm ơn bạn, dù sao thì với 2 cột phụ như vậy cũng đã lọc ra gần như hơn 90% tình huống rồi, cũng nhẹ khá nhiều cho mình.
Cái quy tắc đếm này, mình đếm bằng tay nhiêu lúc muốn hoa mắt luôn, hy vọng có cách nào khác chuẩn được hơn nữa thì quá tốt, còn chưa có thì 2 cột phụ của bạn cũng đã giúp mình đỡ vất vả khá nhiều rồi đó.
Cảm ơn bạn.

Để mình thử nghiên cứu thêm.

 

[SA_DQ]

Nếu đây sẽ là bảng chấm công từng tháng, thì nên chăng ta bố trí thành như bảng chấm công của các xí nghiệp;
& khi ý thay vì ghi 'X" ta ghi mã lớp; Nhưng tệ là mã lớp của bạn chủ bài đăng quá lê thê.

 

[LuuGiaPhúc]

Nếu đây sẽ là bảng chấm công từng tháng, thì nên chăng ta bố trí thành như bảng chấm công của các xí nghiệp;
& khi ý thay vì ghi 'X" ta ghi mã lớp; Nhưng tệ là mã lớp của bạn chủ bài đăng quá lê thê.

Dạ, chính xác thì đây không phải là 1 bảng chuyên về phần chấm công , mà nó là 1 bảng tổng hợp bao gồm rất nhiều thứ khác, do các cột không liên quan em đã xóa đi. Mã lớp được tạo ra theo quy ước : tên VP , tên loại lớp, năm mở lớp và thứ tự của lớp đó trong năm.
Em cần kiểm tra lại phần số buổi ngoài giờ để dựa vào đó tính thành chi phí phụ cấp ngoài giờ cho giáo viên toàn quốc.

 

[Phuocam]

Em cần kiểm tra lại phần số buổi ngoài giờ để dựa vào đó tính thành chi phí phụ cấp ngoài giờ cho giáo viên toàn quốc.

Công việc này dùng công thức rất khó, vì lịch có thể chồng lên nhau do dạy nhiều lớp hoặc ngắt quãng do nghỉ.

Có thể dùng hàm tự tạo hoặc sub.

.

 

[LuuGiaPhúc]

Công việc này dùng công thức rất khó, vì lịch có thể chồng lên nhau do dạy nhiều lớp hoặc ngắt quãng do nghỉ.

Có thể dùng hàm tự tạo hoặc sub.

.

Vâng, đúng vậy anh ơi, lịch ngày dạy trong khoảng từ ngày ... đến ngày của lớp này có thể nằm trong khoảng thời gian của lớp kia, hoặc nằm so le kiểu mấy ngày khoảng đầu của lớp này = mấy ngày khoảng cuối của lớp kia...
Ví dụ : lớp thứ nhất là từ ngày 02/02/2022 đến 17/02/2022
Lớp thứ 2 là từ ngày 07/02 đến 14/02 ==> nằm trong khoảng của lớp thứ 1

hoặc lớp thứ 2 từ ngày 15/02 đến ngày 20/02 ==> nằm so le

Mình phải so khoảng thời gian của cùng 1 trainer code để đếm được số lượng ngày thứ 7 và chủ nhật (bỏ trùng trong khoảng cùng thời gian) rồi mới nhân cho số buổi ngoài giờ (Thứ 7 tính 1 buổi, CN tính 2 buổi) rồi đối chiếu với kết quả mà họ báo về xem họ báo đúng hay không.

Thông thường tháng nào cũng có vài người báo sai, mình dò đối chiếu cũng rất cực vì dò bằng tay, thậm chí cả mình cũng có lúc sai vì dò 1 hồi là loạn mắt luôn

 

[Hoàng Tuấn 868]

Công việc này dùng công thức rất khó, vì lịch có thể chồng lên nhau do dạy nhiều lớp hoặc ngắt quãng do nghỉ.
Có thể dùng hàm tự tạo hoặc sub.

Em cũng đang lọ mọ nghĩ cách để viết, nhưng có vẻ sẽ hơi dài vì có đoạn trùng, có đoạn lại cách quãng.

 

[Phuocam]

Em cũng đang lọ mọ nghĩ cách để viết, nhưng có vẻ sẽ hơi dài vì có đoạn trùng, có đoạn lại cách quãng.

Có thể dùng Dictionary.

Tổng quan về Scripting.Dictionary

Với những ai yêu thích Excel nói chung và yêu thích VBA nói riêng thì chắc hẳn cũng đã biết qua các khái niệm về mảng, về công thức điều kiện, vòng lặp,..., những thứ rất quen thuộc mà gần như là sử dụng thường xuyên trong từng bài toán lập trình. Tuy nhiên, có lẽ sẽ rất ít người biết về...

www.giaiphapexcel.com

 

[Hoàng Tuấn 868]

Có thể dùng Dictionary.

Em cũng đang theo hướng đó, khả năng sẽ dùng đến 2 dic anh ạ.

 

[LƯƠNG TUẤN]

Ké Top 1 chút nhé
Em đang gặp khó khăn trong việc chia ca.
Yêu cầu là nhập cột A là ngày đầu tiên trong tháng đi ca gì, rồi sắp ca ý đến ngày thứ 7.
Chủ nhật nghỉ đổi ca - Thứ 2 tuần tiếp sẽ đổi ca còn lại.
En nhờ các anh chị giúp ạ.

 

[Phuocam]

Em cũng đang theo hướng đó, khả năng sẽ dùng đến 2 dic anh ạ.

Thử rút gọn, dùng 1 dic. Nếu dùng sub thì ghép mã với ngày.

.

 

[Hoàng Tuấn 868]

Thử rút gọn, dùng 1 dic. Nếu dùng sub thì ghép mã với ngày.

Em nghĩ mãi chưa ra, anh viết giúp bạn ấy Function được không anh.

 

[Phuocam]

Em nghĩ mãi chưa ra, anh viết giúp bạn ấy Function được không anh.

Bạn đưa code 2 dic lên, tôi sẽ rút gọn.

.

 

[Hoàng Tuấn 868]

Bạn đưa code 2 dic lên, tôi sẽ rút gọn.

Em làm theo ý đó nhưng thử mãi vẫn chưa được anh ạ.

 

[Phuocam]

Em làm theo ý đó nhưng thử mãi vẫn chưa được anh ạ.

Thử code sau:

Public Function T7CN(ByVal ID As String, ByVal rngID As Range, ByVal rngDate As Range) As Long
Dim arrID As Variant, arrDate As Variant
Dim Dic As Object
Dim i&, j&
Set Dic = CreateObject("Scripting.Dictionary")
arrID = rngID.Value
arrDate = rngDate.Value2

For i = 1 To UBound(arrID)
    If arrID(i, 1) = ID Then
        For j = arrDate(i, 1) To arrDate(i, 2)
            If Weekday(j, vbMonday) = 6 Then
                If Dic.Exists(j) = False Then
                    Dic.Item(j) = ""
                    T7CN = T7CN + 1
                End If
            ElseIf Weekday(j, vbMonday) = 7 Then
                If Dic.Exists(j) = False Then
                    Dic.Item(j) = ""
                    T7CN = T7CN + 2
                End If
            End If
        Next j
    End If
Next i
End Function

Trong cột "Số buổi tối đa theo lịch", nhập công thức:

=IF(AJ10="","",T7CN(U10,$U$10:$U$124,$AD$10:$AE$124))

.

 

[Hoàng Tuấn 868]

Thử code sau:

Public Function T7CN(ByVal ID As String, ByVal rngID As Range, ByVal rngDate As Range) As Long
Dim arrID As Variant, arrDate As Variant
Dim Dic As Object
Dim i&, j&
Set Dic = CreateObject("Scripting.Dictionary")
arrID = rngID.Value
arrDate = rngDate.Value2

For i = 1 To UBound(arrID)
    If arrID(i, 1) = ID Then
        For j = arrDate(i, 1) To arrDate(i, 2)
            If Weekday(j, vbMonday) = 6 Then
                If Dic.Exists(j) = False Then
                    Dic.Item(j) = ""
                    T7CN = T7CN + 1
                End If
            ElseIf Weekday(j, vbMonday) = 7 Then
                If Dic.Exists(j) = False Then
                    Dic.Item(j) = ""
                    T7CN = T7CN + 2
                End If
            End If
        Next j
    End If
Next i
End Function

Trong cột "Số buổi tối đa theo lịch", nhập công thức:

=IF(AJ10="","",T7CN(U10,$U$10:$U$124,$AD$10:$AE$124))

Tuyệt vời, xin cảm ơn anh nhiều, em loay hoay thử mãi mà không được.