|
发表于 2016-7-4 13:25
|
显示全部楼层
本楼为最佳答案
mathking77 发表于 2016-7-4 12:01
N列到W列的数据都是是根据H列I列J列这3列算出来的
N列O列P列数据是 是每人周1-5数据之和
Q R S 是 每人 ...
在原代码基础上修改如下:- Sub xx()
- Dim arr, brr(), n&, i&, dA, dB, dC, dD, dE, dF, dG, dH, dI, j&, dJ
- With Sheet1
- n = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:J" & n)
- Set dA = CreateObject("Scripting.Dictionary")
- Set dB = CreateObject("Scripting.Dictionary")
- Set dC = CreateObject("Scripting.Dictionary")
- Set dD = CreateObject("Scripting.Dictionary")
- Set dE = CreateObject("Scripting.Dictionary")
- Set dF = CreateObject("Scripting.Dictionary")
- Set dG = CreateObject("Scripting.Dictionary")
- Set dH = CreateObject("Scripting.Dictionary")
- Set dI = CreateObject("Scripting.Dictionary")
- Set dJ = CreateObject("Scripting.Dictionary")
- For i = 1 To n - 1
- If Weekday(arr(i, 2), 2) < 6 Then
- If dA.Exists(arr(i, 1)) Then
- dA(arr(i, 1)) = dA(arr(i, 1)) + arr(i, 8)
- dB(arr(i, 1)) = dB(arr(i, 1)) + arr(i, 9)
- dC(arr(i, 1)) = dC(arr(i, 1)) + IIf(arr(i, 10) = "", 0, arr(i, 10))
- Else
- dA.Add arr(i, 1), arr(i, 8)
- dB.Add arr(i, 1), arr(i, 9)
- dC.Add arr(i, 1), IIf(arr(i, 10) = "", 0, arr(i, 10))
- dJ.Add arr(i, 1), arr(i, 7)
- End If
- ElseIf Weekday(arr(i, 2), 2) = 6 Then
- If dA.Exists(arr(i, 1)) Then
- dD(arr(i, 1)) = dD(arr(i, 1)) + arr(i, 8)
- dE(arr(i, 1)) = dE(arr(i, 1)) + arr(i, 9)
- dF(arr(i, 1)) = dF(arr(i, 1)) + IIf(arr(i, 10) = "", 0, arr(i, 10))
- Else
- dD.Add arr(i, 1), arr(i, 8)
- dE.Add arr(i, 1), arr(i, 9)
- dF.Add arr(i, 1), IIf(arr(i, 10) = "", 0, arr(i, 10))
- End If
- ElseIf Weekday(arr(i, 2), 2) = 7 Then
- If dA.Exists(arr(i, 1)) Then
- dG(arr(i, 1)) = dG(arr(i, 1)) + arr(i, 8)
- dH(arr(i, 1)) = dH(arr(i, 1)) + arr(i, 9)
- dI(arr(i, 1)) = dI(arr(i, 1)) + IIf(arr(i, 10) = "", 0, arr(i, 10))
- Else
- dG.Add arr(i, 1), arr(i, 8)
- dH.Add arr(i, 1), arr(i, 9)
- dI.Add arr(i, 1), IIf(arr(i, 10) = "", 0, arr(i, 10))
- End If
- End If
- Next
- x = 1
- For Each k In dA.keys
- x = x + 1
- .Cells(x, 14) = k
- .Cells(x, 15) = dA(k)
- .Cells(x, 16) = dB(k)
- .Cells(x, 17) = dC(k)
- .Cells(x, 18) = dD(k)
- .Cells(x, 19) = dE(k)
- .Cells(x, 20) = dF(k)
- .Cells(x, 21) = dG(k)
- .Cells(x, 22) = dH(k)
- .Cells(x, 23) = dI(k)
- .Cells(x, 24) = dJ(k)
- Next
- [N1].Value = "姓名"
- [O1].Value = "正常出勤时间"
- [P1].Value = "加点时间"
- [Q1].Value = "加点次数"
- [R1].Value = "周六白天"
- [S1].Value = "周六晚上"
- [T1].Value = "周六加点次数"
- [U1].Value = "周日白天"
- [V1].Value = "周日晚上"
- [W1].Value = "周日加点次数"
- [X1].Value = "部门"
- End With
- End Sub
复制代码 |
|