Tính số ngày nghỉ liên tiếp lớn nhất theo nhiều điều kiện

minhle166 · 15/7/20

Kính gửi anh/ chị thành viên GPE!

Em có 1 bài toán về tính số ngày nghỉ liên tiếp lớn nhất trên bảng chấm công mà nghĩ hoài không ra cách làm. Giải thích trên đây thì hơi dài dòng mà cũng khó hình dung ạ, các điều kiện & mô tả chi tiết các yêu cầu cần làm nhờ anh/ chị xem trong file đính kèm giúp em nhé!

Trân trọng cảm ơn anh/ chị nhiều!

Phuocam · 15/7/20

minhle166 đã viết:
tính số ngày nghỉ liên tiếp lớn nhất trên bảng chấm công

Thử công thức mảng này trong D3:

Mã:

=MAX(FREQUENCY(IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181="x"),ROW(Data!$X$2:$X$181)),
IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181<>"x")*(Data!$Y$2:$Y$181<>"Sun"),ROW(Data!$X$2:$X$181))))

Nhớ` kết thúc bằng Ctrl+Shif+Enter.

minhle166 · 15/7/20

phuocam đã viết:
Thử công thức mảng này trong D3:

Mã:

=MAX(FREQUENCY(IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181="x"),ROW(Data!$X$2:$X$181)), IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181<>"x")*(Data!$Y$2:$Y$181<>"Sun"),ROW(Data!$X$2:$X$181))))

Nhớ` kết thúc bằng Ctrl+Shif+Enter.

Công thức hay quá, em cảm ơn nhiều ạ!

Bài đã được tự động gộp: 15/7/20

Anh/ chị giúp em điền thêm dữ liệu nghỉ "Từ ngày" "Đến ngày" với ạ

Emp NO	Employee Name	Position Name	Số ngày nghỉ liên tiếp lớn nhất	Từ ngày	Đến ngày
262390	CAO THỊ HẰNG	Sewer	5	08/06/2020	12/06/2020
262393	NGUYỄN THỊ TRANG	Sewer	6	11/06/2020	17/06/2020
262397	NGUYỄN THỊ HIỀN	Sewer	6
244630	PHẠM THỊ LOAN	Back winding Operator	6	15/06/2020	22/06/2020
244635	TRƯƠNG VĂN HẬU	Knitter	4
244636	NGUYỄN VĂN TUYỀN	Roving Checker	2

minhle166 · 20/7/20

Em vẫn còn vấn đề liên quan đến điền ngày nghỉ từ ngày nào đến ngày nào, kính mong quý thầy cô/ anh chị quan tâm giúp đỡ với ạ!

minhle166 · 22/7/20

Có vẻ vấn đề này hơi khó giải quyết nếu dùng công thức thông thường, hy vọng các cao thủ VBA ra tay giúp đỡ ạ

HieuCD · 22/7/20

minhle166 đã viết:
Kính gửi anh/ chị thành viên GPE!

Em có 1 bài toán về tính số ngày nghỉ liên tiếp lớn nhất trên bảng chấm công mà nghĩ hoài không ra cách làm. Giải thích trên đây thì hơi dài dòng mà cũng khó hình dung ạ, các điều kiện & mô tả chi tiết các yêu cầu cần làm nhờ anh/ chị xem trong file đính kèm giúp em nhé!

Trân trọng cảm ơn anh/ chị nhiều!

Dùng Function

Mã:

Option Compare Text

Function XYZ(ByVal NhanVien As String, ByVal NV As Range, ByVal Ngay As Range, ByVal CC_CN As Range, Optional TypeRes As Long = 0)
  'TypeRes = 0: So ngay nghi lien tiep lon nhat
  'TypeRes = 1: "Ngay dau" cua So ngay nghi lien tiep lon nhat
  'TypeRes <> 0 va 1: "Ngay cuoi" cua So ngay nghi lien tiep lon nhat
  Dim sRow&, i&, i2&, iMax&, k&, ik&, ik2&
  sRow = NV.Rows.Count + 1
  Set NV = NV.Resize(sRow)
  Set Ngay = Ngay.Resize(sRow)
  Set CC_CN = CC_CN.Resize(sRow)
  For i = 1 To sRow
    If NV(i, 1).Value = NhanVien Then
      If CC_CN(i, 1) = "x" Then
        For i2 = i To sRow
          If CC_CN(i2, 1) = "x" Or CC_CN(i2, 2) = "Sun" Then
            If CC_CN(i2, 1) = "x" Then k = k + 1
          Else
            If k > iMax Then
              iMax = k:  ik = i:  ik2 = i2 - 1
            End If
            k = 0:    i = i2
            Exit For
          End If
        Next i2
      End If
    End If
  Next i
  If iMax = 0 Then XYZ = "": Exit Function
  If TypeRes = 0 Then
    XYZ = iMax
  ElseIf TypeRes = 1 Then
    XYZ = Ngay(ik, 1)
  Else
    XYZ = Ngay(ik2, 1)
  End If
End Function

minhle166 · 22/7/20

Tuyệt vời ông mặt trời, cảm ơn a HieuCD nhiều lắm ạ!

dazkangel · 23/7/20

Làm thử.

Phan Thế Hiệp · 23/7/20

minhle166 đã viết:
Có vẻ vấn đề này hơi khó giải quyết nếu dùng công thức thông thường, hy vọng các cao thủ VBA ra tay giúp đỡ ạ

Anh @HieuCD đã đưa cách giải bằng VBA tại bài #6, là phương án tốt nhất cho bạn.
Bạn @dazkangel đưa ra hướng xử lý bằng cột phụ tại bài #8 cũng là phương án hay.
Công thức 'trực tiếp' không dùng cột phụ dưới đây, dựa trên 'tinh thần' công thức của bạn hiền @phuocam triển khai và tính toán dành cho bạn tham khảo thêm:

Mã:

E3=IF(D3>0,WORKDAY.INTL(F3+1,-D3,"0000000",IFERROR(AGGREGATE(15,6,Data!$G$2:$G$181/(B3=Data!$E$2:$E$181)/(Data!$Y$2:$Y$181="Sun"),ROW($1:$200)),)),"")
F3=IF(D3>0,INDIRECT("Data!G"&LOOKUP(2,1/(OFFSET(Data!$X$1,,,MOD(AGGREGATE(14,6,ROW(Data!$X$2:$X$181)+FREQUENCY(IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181="x"),ROW(Data!$X$2:$X$181),),IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181<>"x")*(Data!$Y$2:$Y$181<>"Sun"),ROW(Data!$X$2:$X$181),))*10^6,2),10^6)-1)="x"),ROW($1:$200))),"")

Kết thúc bằng Ctrl+Shift+Enter. Fill xuống.

Thân

dazkangel · 23/7/20

Phan Thế Hiệp đã viết:
Anh @HieuCD đã đưa cách giải bằng VBA tại bài #6, là phương án tốt nhất cho bạn.
Bạn @dazkangel đưa ra hướng xử lý bằng cột phụ tại bài #8 cũng là phương án hay.
Công thức 'trực tiếp' không dùng cột phụ dưới đây, dựa trên 'tinh thần' công thức của bạn hiền @phuocam triển khai và tính toán dành cho bạn tham khảo thêm:

Mã:

E3=IF(D3>0,WORKDAY.INTL(F3+1,-D3,"0000000",IFERROR(AGGREGATE(15,6,Data!$G$2:$G$181/(B3=Data!$E$2:$E$181)/(Data!$Y$2:$Y$181="Sun"),ROW($1:$200)),)),"") F3=IF(D3>0,INDIRECT("Data!G"&LOOKUP(2,1/(OFFSET(Data!$X$1,,,MOD(AGGREGATE(14,6,ROW(Data!$X$2:$X$181)+FREQUENCY(IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181="x"),ROW(Data!$X$2:$X$181),),IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181<>"x")*(Data!$Y$2:$Y$181<>"Sun"),ROW(Data!$X$2:$X$181),))*10^6,2),10^6)-1)="x"),ROW($1:$200))),"")

Kết thúc bằng Ctrl+Shift+Enter. Fill xuống.

Thân

Với E3 thì có thể sửa thành thế này:

Mã:

E3=WORKDAY.INTL(F3+1,-D3,"0000000",MODE.MULT(IF((Data!Y$2:Y$181="Sun")*(Data!E2:E181=B3)*{1,1},Data!G$2:G$181)))

Phan Thế Hiệp · 24/7/20

dazkangel đã viết:
Với E3 thì có thể sửa thành thế này:

Mã:

E3=WORKDAY.INTL(F3+1,-D3,"0000000",MODE.MULT(IF((Data!Y$2:Y$181="Sun")*(Data!E2:E181=B3)*{1,1},Data!G$2:G$181)))

Dùng Mode.Mult() để liệt kê hay hơn Aggregate() hén.

Nhìn lại công thức F3 thì cũng cần chỉnh lại, không cần dùng Indirect() chi cho rườm rà

:

Mã:

E3=IF(D3>0,WORKDAY.INTL(F3+1,-D3,"0000000",MODE.MULT(IF({1,1}*(B3=Data!$E$2:$E$181)*(Data!$Y$2:$Y$181="Sun"),Data!$G$2:$G$181))),"")
F3=IF(D3>0,LOOKUP(2,1/(OFFSET(Data!$X$1,,,MOD(AGGREGATE(14,6,ROW(Data!$X$2:$X$181)+FREQUENCY(IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181="x"),ROW(Data!$X$2:$X$181),),IF((Data!$E$2:$E$181=B3)*(Data!$X$2:$X$181<>"x")*(Data!$Y$2:$Y$181<>"Sun"),ROW(Data!$X$2:$X$181),))*10^6,2),10^6)-1)="x"),Data!$G$1:$G$181),"")

Kết thúc bằng Ctrl+Shift+Enter.

Chúc anh em ngày vui.
/-*+/

minhle166 · 27/7/20

Cảm ơn anh dazkangel, anh Hiệp đã giúp em thêm PA dùng công thức quá hay ngoài VBA ạ!
Tiềm năng của excel quả là vô hạn trong khi kiến thức của em về excel thì quá nhỏ bé.
Một lần nữa xin chân thành cảm ơn đại gia đình GPE, chúc anh/ chị nhiều sức khỏe, niềm vui và giúp đỡ thêm nhiều người khác giống như e ạ!

minhle166 · 29/7/20

Hôm nay có thời gian em mới test lại các phương án, có một số vấn đề sau nhờ các anh đẹp zai xử lý giúp em ạ:
1. PA2: Sử dụng cột phụ (dazkangel), đây là PA cho ra kết quả đúng như em mong muốn ạ
2. PA1: Không sử dụng cột phụ (A Hiệp), PA này trích xuất ngày tháng chưa chuẩn ạ
Hai PA trên em thử test với dữ liệu 500.000 dòng thì công thức chạy rất chậm, gần như là treo máy luôn. Có cách nào để tăng tốc độ tính toán nếu dùng với dữ liệu lớn không ạ? (Dữ liệu e cần xử lý khoảng 500.000- 700.000 dòng)
3. PA sử dụng VBA (HieuCD): PA này cho kết quả chưa đúng ạ, a HieuCD xem giúp em với. Với cả cho em hỏi nếu dùng VBA mà dữ liệu khoảng 500k-700k dòng thì có ổn không ạ?

						Kết quả mong muốn
Emp NO	Employee Name	Position Name	Số ngày nghỉ liên tiếp lớn nhất	Từ ngày	Đến ngày	Số ngày nghỉ liên tiếp lớn nhất	Từ ngày	Đến ngày
200006	LÊ THỊ HIỀN	Officer	4	25/07/2020	03/07/2020	1	25/07/2020	25/07/2020
200008	PHẠM ANH CƯỜNG	Driver	6	20/07/2020	26/07/2020	6	20/07/2020	25/07/2020
200034	VŨ VĂN ĐẢO	Senior Mechanic	1	23/07/2020	23/07/2020	1	23/07/2020	23/07/2020
200059	BÙI VĂN NAM	Supervisor	3	24/07/2020	01/07/2020	2	24/07/2020	25/07/2020
200073	VŨ DUY NAM	Security	3	22/07/2020	26/07/2020	3	22/07/2020	26/07/2020
200089	BÙI THỊ TUYẾT	Recut Operator	22	01/07/2020	26/07/2020	22	01/07/2020	25/07/2020
200105	BÙI VĂN NGOÃN	Assistant Supervisor	1	09/07/2020	11/07/2020	1	14/07/2020	14/07/2020
200113	NGUYỄN THỊ LÝ	Senior Technician	2	17/07/2020	19/07/2020	2	17/07/2020	18/07/2020
200170	TĂNG THỊ NGUYỆT	Sewer	2	17/07/2020	19/07/2020	2	24/07/2020	25/07/2020
200193	LƯƠNG THỊ XUÂN	Sewer	2	17/07/2020	19/07/2020	2	17/07/2020	18/07/2020
200201	VÕ THỊ NGỌC	QA	2	17/07/2020	19/07/2020	2	24/07/2020	25/07/2020
200206	BÙI THỊ THÚY	Sewer	2	24/07/2020	26/07/2020	2	24/07/2020	25/07/2020
200334	VƯƠNG THỊ HOAN	Sample Cutter	2	17/07/2020	19/07/2020	2	24/07/2020	25/07/2020
200347	THÂN THỊ PHƯỢNG	QA	3	23/07/2020	26/07/2020	3	23/07/2020	25/07/2020
200354	NGUYỄN THỊ AN	Assistant Officer	1	25/07/2020	26/07/2020	1	25/07/2020	25/07/2020

Em cảm ơn ạ!

HieuCD · 29/7/20

minhle166 đã viết:
Hôm nay có thời gian em mới test lại các phương án, có một số vấn đề sau nhờ các anh đẹp zai xử lý giúp em ạ:
1. PA2: Sử dụng cột phụ (dazkangel), đây là PA cho ra kết quả đúng như em mong muốn ạ
2. PA1: Không sử dụng cột phụ (A Hiệp), PA này trích xuất ngày tháng chưa chuẩn ạ
Hai PA trên em thử test với dữ liệu 500.000 dòng thì công thức chạy rất chậm, gần như là treo máy luôn. Có cách nào để tăng tốc độ tính toán nếu dùng với dữ liệu lớn không ạ? (Dữ liệu e cần xử lý khoảng 500.000- 700.000 dòng)
3. PA sử dụng VBA (HieuCD): PA này cho kết quả chưa đúng ạ, a HieuCD xem giúp em với. Với cả cho em hỏi nếu dùng VBA mà dữ liệu khoảng 500k-700k dòng thì có ổn không ạ?

Kết quả mong muốn
Emp NO Employee Name Position Name Số ngày nghỉ liên tiếp lớn nhất Từ ngày Đến ngày Số ngày nghỉ liên tiếp lớn nhất Từ ngày Đến ngày
200006 LÊ THỊ HIỀN Officer
4
25/07/2020
03/07/2020
1
25/07/2020
25/07/2020
200008 PHẠM ANH CƯỜNG Driver
6
20/07/2020
26/07/2020
6
20/07/2020
25/07/2020
200034 VŨ VĂN ĐẢO Senior Mechanic
1
23/07/2020
23/07/2020
1
23/07/2020
23/07/2020
200059 BÙI VĂN NAM Supervisor
3
24/07/2020
01/07/2020
2
24/07/2020
25/07/2020
200073 VŨ DUY NAM Security
3
22/07/2020
26/07/2020
3
22/07/2020
26/07/2020
200089 BÙI THỊ TUYẾT Recut Operator
22
01/07/2020
26/07/2020
22
01/07/2020
25/07/2020
200105 BÙI VĂN NGOÃN Assistant Supervisor
1
09/07/2020
11/07/2020
1
14/07/2020
14/07/2020
200113 NGUYỄN THỊ LÝ Senior Technician
2
17/07/2020
19/07/2020
2
17/07/2020
18/07/2020
200170 TĂNG THỊ NGUYỆT Sewer
2
17/07/2020
19/07/2020
2
24/07/2020
25/07/2020
200193 LƯƠNG THỊ XUÂN Sewer
2
17/07/2020
19/07/2020
2
17/07/2020
18/07/2020
200201 VÕ THỊ NGỌC QA
2
17/07/2020
19/07/2020
2
24/07/2020
25/07/2020
200206 BÙI THỊ THÚY Sewer
2
24/07/2020
26/07/2020
2
24/07/2020
25/07/2020
200334 VƯƠNG THỊ HOAN Sample Cutter
2
17/07/2020
19/07/2020
2
24/07/2020
25/07/2020
200347 THÂN THỊ PHƯỢNG QA
3
23/07/2020
26/07/2020
3
23/07/2020
25/07/2020
200354 NGUYỄN THỊ AN Assistant Officer
1
25/07/2020
26/07/2020
1
25/07/2020
25/07/2020

Em cảm ơn ạ!

Dùng Sub tốc độ nhanh hơn nhiều

Mã:

Option Compare Text

Sub GPE()
  Dim maNV(), Ngay(), CC(), Arr, sArr(), Res(), Dic As Object
  Dim sRow&, i&, i2&, k&
  Dim fDay As Date, eDay As Date, iKey$

  With Sheets("Data")
    i = .Range("D" & Rows.Count).End(xlUp).Row
    maNV = .Range("D2:D" & i + 1).Value
    Ngay = .Range("G2:G" & i).Value
    CC = .Range("X2:Y" & i + 1).Value
  End With
 
  Set Dic = CreateObject("scripting.dictionary")
  sRow = UBound(maNV)
  For i = 1 To sRow
    If CC(i, 1) = "x" Then
      iKey = maNV(i, 1)
      If Dic.exists(iKey) = False Then Dic.Add iKey, Array(0, 0, 0)
      Arr = Dic.Item(iKey)
      fDay = Ngay(i, 1)
      For i2 = i To sRow
        If CC(i2, 1) = "x" Then
          k = k + 1
          eDay = Ngay(i2, 1)
        End If
        If maNV(i2 + 1, 1) <> iKey Or (CC(i2 + 1, 1) <> "x" And CC(i2 + 1, 2) <> "Sun") Then
          If k >= Arr(0) Then
            Arr(0) = k:     Arr(1) = fDay:    Arr(2) = eDay
            Dic.Item(iKey) = Arr
          End If
          k = 0:      i = i2:       Exit For
        End If
      Next i2
    End If
  Next i
 
  With Sheets("SUM")
    sArr = .Range("A3", .Range("A" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To 3)
  For i = 1 To sRow
    iKey = sArr(i, 1)
    If Dic.exists(iKey) Then
      Arr = Dic.Item(iKey)
      Res(i, 1) = Arr(0):   Res(i, 2) = Arr(1):   Res(i, 3) = Arr(2)
    End If
  Next i
  With Sheets("SUM")
    .Range("D3").Resize(sRow, 3) = Res
  End With
End Sub

minhle166 · 30/7/20

Tuyệt vời quá anh @HieuCD, em test trên dữ liệu 500.000 dòng mà chạy cái roẹt à. Cảm ơn anh nhiều nha!

Tính số ngày nghỉ liên tiếp lớn nhất theo nhiều điều kiện

Thành viên mới

File đính kèm

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

Chuyên gia GPE

File đính kèm

Thành viên mới

<New Horizons>

File đính kèm

-⍟-

File đính kèm

<New Horizons>

-⍟-

File đính kèm

Thành viên mới

Thành viên mới

File đính kèm

Chuyên gia GPE

File đính kèm

Thành viên mới

Facebook

Group