|
发表于 2016-12-22 11:12
|
显示全部楼层
本楼为最佳答案
请查收附件,结果输出在另外一个表中,输出了两个结果一个是中间过程,即将时间去对应匹配,另外一个是你要的最终结果,其实两个For语句可以合并成一个,即直接输出你要的结果,这个你自己可以修改,我这样编写的目的在于你能理解中间的处理过程。另外这样做其实还是有问题,比如我一天就打两次卡,12:10:00一次,下午17:00:00一次,我的程序会认为造成没有打卡,中午下班打了卡,下午上班未打卡,下午下班正常,但实际情况可能是上午这个人休息,中午的12:10:00可能是下午的上班打卡!具体情况你自己斟酌!
- Sub 打卡数据转换()
- Dim dc As Object
- Dim arr, i As Integer, n As Integer, brr(), m As Integer, x As Integer
- Dim 日期 As Date, 时间 As Date, 早上 As Date, 中下 As Date, 午上 As Date, 晚下 As Date
- Set dc = CreateObject("Scripting.Dictionary")
- 早上 = #9:00:00 AM#
- 中下 = #12:00:00 PM#
- 午上 = #2:00:00 PM#
- 晚下 = #5:00:00 PM#
- With Sheet1
- n = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("A2:D" & n)
- End With
- For i = 1 To n - 1
- 日期 = Application.Text(arr(i, 4), "yyyy-m-d")
- 时间 = Application.Text(arr(i, 4), "hh:mm:ss")
- If Not dc.Exists(arr(i, 3) & "_" & 日期) Then
- m = m + 1
- dc.Add arr(i, 3) & "_" & 日期, m
- ReDim Preserve brr(1 To 7, 1 To m)
- brr(1, m) = arr(i, 1)
- brr(2, m) = arr(i, 2)
- brr(3, m) = 日期
- Select Case 时间
- Case Is < 早上
- brr(4, m) = 时间
- Case Is < 中下
- brr(4, m) = 时间
- Case Is < 午上
- brr(5, m) = 时间
- Case Is < 晚下
- brr(6, m) = 时间
- Case Is >= 晚下
- brr(7, m) = 时间
- End Select
- Else
- x = dc(arr(i, 3) & "_" & 日期)
- Select Case 时间
- Case Is < 早上
- brr(4, x) = 时间
- Case Is < 中下
- If brr(4, x) = "" Then brr(4, x) = 时间 Else brr(5, x) = 时间
- Case Is < 午上
- If brr(5, x) = "" Then brr(5, x) = 时间 Else brr(6, x) = 时间
- Case Is < 晚下
- If brr(6, x) = "" Then brr(6, x) = 时间 Else brr(7, x) = 时间
- Case Is >= 晚下
- brr(7, x) = 时间
- End Select
- End If
- Next
- Sheet2.Range("A2").Resize(m, 7) = Application.WorksheetFunction.Transpose(brr)
- For i = 1 To m
- If brr(4, i) = "" Then
- brr(4, i) = "未打卡"
- ElseIf brr(4, i) <= 早上 Then
- brr(4, i) = "正常"
- Else
- brr(4, i) = "迟到"
- End If
-
- If brr(5, i) = "" Then
- brr(5, i) = "未打卡"
- ElseIf brr(5, i) < 中下 Then
- brr(5, i) = "早退"
- Else
- brr(5, i) = "正常"
- End If
-
- If brr(6, i) = "" Then
- brr(6, i) = "未打卡"
- ElseIf brr(6, i) <= 午上 Then
- brr(6, i) = "正常"
- Else
- brr(6, i) = "迟到"
- End If
-
- If brr(7, i) = "" Then
- brr(7, i) = "未打卡"
- ElseIf brr(7, i) < 晚下 Then
- brr(7, i) = "早退"
- Else
- brr(7, i) = "正常"
- End If
- Next
- Sheet2.Range("J2").Resize(m, 7) = Application.WorksheetFunction.Transpose(brr)
- End Sub
复制代码 |
|