'***************************************************************************
'功能:考勤数据统计
'作者: 刘相涛
'参数:考勤分析后文件,统计结果文件
'出力:将每个人的请假情况进行汇总统计
'***************************************************************************
Function HRDataStatic(AnaHRName As String, StaticHRName As String)
'计数工具
Dim i As Integer, j As Integer, k As Integer, NewRange As Integer, n As Integer
Dim thisworkbook As Workbook
Dim StaSumRange As Integer, AnaSumRange As Integer
Dim temparr As Variant, temparr1 As Variant
'-----------------------------汇总文件员工人数----------------------------------------------
'Set thisworkbook = Workbooks(StaticHRName)
i = 2
StaSumRange = 2
'统计汇总文件数据行数
Do While Workbooks(StaticHRName).Worksheets(1).Cells(i, 3).Value <> ""
i = i + 1
Loop
'去header
StaSumRange = i - 1
If StaSumRange = 1 Then
MsgBox ("员工人数为0,请确认!")
End If
'-----------------------------考勤系统导出数据文件HRsystemName----------------------------------------------
Set thisworkbook = Workbooks(AnaHRName)
k = 1
'统计考勤系统导出的数据文件行数(含header)
AnaSumRange = 1
'统计考勤系统导出文件行数
Do While thisworkbook.Worksheets(1).Cells(k, 1).Value <> ""
k = k + 1
Loop
'去header
AnaSumRange = k - 1
If AnaSumRange = 1 Then
MsgBox ("考勤系统导出文件为空,请确认!")
End If
application.ScreenUpdating = False
Dim Name As String, Nianjia As Double, Jiaban As Double, Tiaoxiu As Double, Buqian As Integer, Chidaoyantui As Integer, Zaotui As Integer, Chidao As Integer, _
Kuanggong As Double, Shijia As Double, Bingjia As Double, Gongchu As Double, Chuchai As Double, Hunjia As Double, Chanjian As Double, _
Chanjia As Double, Burujia As Double, Peichanjia As Double, Sangjia As Double, Tanqin As Double
Dim isInHRsystemData As Boolean
For i = 3 To StaSumRange
Name = "" '姓名
Nianjia = 0 '年假 天
Tiaoxiu = 0 '调休假 天
Buqian = 0 '补签次数 次
Chidaoyantui = 0 '迟到延退次数 次
Zaotui = 0 '早退次数 次
Chidao = 0 '迟到次数 次
Kuanggong = 0 '旷工时长 天
Shijia = 0 '事假 天
Bingjia = 0 '病假 天
Gongchu = 0 '公出 天
Chuchai = 0 '出差 天
Hunjia = 0 '婚假 天
Chanjian = 0 '产检假 天
Chanjia = 0 '产假 天
Burujia = 0 '哺乳假 小时
Peichanjia = 0 '陪产假 天
Sangjia = 0 '丧假 天
Tanqin = 0 '探亲假 天
Jiaban = 0 '加班 小时
'获取统计对象的姓名
Name = Workbooks(StaticHRName).Worksheets(1).Cells(i, 3).Value
'考勤数据是否存在
isInHRsystemData = False
j = 2
Do While j <= AnaSumRange
temparr = ""
temparr1 = ""
If thisworkbook.Worksheets(1).Cells(j, 1).Value = Name Then
isInHRsystemData = True
temparr = Split(thisworkbook.Worksheets(1).Cells(j, 7).Value, ":")
If InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "年假") <> 0 Then
Nianjia = Nianjia + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "调休") <> 0 Then
Tiaoxiu = Tiaoxiu + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "补签") <> 0 Then
Buqian = Buqian + 1
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "旷工") <> 0 Then
Kuanggong = Kuanggong + CDbl(Replace(temparr(1), "天", ""))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "病假") <> 0 Then
Bingjia = Bingjia + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "公出") <> 0 Then
Gongchu = Gongchu + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "出差") <> 0 Then
Chuchai = Chuchai + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "婚假") <> 0 Then
Hunjia = Hunjia + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "产检假") <> 0 Then
Chanjian = Chanjian + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "陪产假") <> 0 Then
Peichanjia = Peichanjia + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "产假") <> 0 Then
Chanjia = Chanjia + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "哺乳假") <> 0 Then
Burujia = Burujia + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "丧假") <> 0 Then
Sangjia = Sangjia + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "探亲假") <> 0 Then
Tanqin = Tanqin + CDbl(temparr(1))
ElseIf InStr(thisworkbook.Worksheets(1).Cells(j, 7).Value, "事假") <> 0 Then
Shijia = Shijia + CDbl(temparr(1))
End If
If thisworkbook.Worksheets(1).Cells(j, 8).Value = "早退" Then
Zaotui = Zaotui + 1
End If
If thisworkbook.Worksheets(1).Cells(j, 9).Value = "迟到" Then
Chidao = Chidao + 1
End If
If thisworkbook.Worksheets(1).Cells(j, 10).Value = "迟到延退" Then
Chidaoyantui = Chidaoyantui + 1
End If
If InStr(thisworkbook.Worksheets(1).Cells(j, 11).Value, "加班") <> 0 Then
temparr1 = Split(Replace(thisworkbook.Worksheets(1).Cells(j, 11).Value, "小时", ""), ":")
Jiaban = Jiaban + temparr1(1)
End If
j = j + 1
Else
j = j + 1
End If
Loop
Workbooks(StaticHRName).Activate
If isInHRsystemData = True Then
Cells(i, 7).Value = Round(Nianjia, 1)
Cells(i, 8).Value = Jiaban
Cells(i, 9).Value = Tiaoxiu
Cells(i, 10).Value = Buqian
Cells(i, 11).Value = Chidaoyantui
Cells(i, 12).Value = Chidao
Cells(i, 13).Value = Zaotui
Cells(i, 14).Value = Kuanggong
Cells(i, 15).Value = Shijia
Cells(i, 16).Value = Bingjia
Cells(i, 17).Value = Gongchu
Cells(i, 18).Value = Chuchai
Cells(i, 19).Value = Hunjia
Cells(i, 20).Value = Chanjian
Cells(i, 21).Value = Chanjia
Cells(i, 22).Value = Burujia
Cells(i, 23).Value = Peichanjia
Cells(i, 24).Value = Sangjia
Cells(i, 25).Value = Tanqin
Cells(i, 26).Value = ""
Else
'Cells(i, 26).Value = "未找到考勤记录,请人工核对!"
End If
Next i
For i = 3 To StaSumRange
If Cells(i, 7).Value = "" And Cells(i, 25).Value = "" Then
Cells(i, 26).Value = "未找到考勤记录,请人工核对!"
Else
Cells(i, 26).Value = ""
End If
Next i
APPlication.DisplayAlerts = False
'打开屏幕刷新
Application.ScreenUpdating = True
'焦点定位到文件首
Cells(1, 1).Select
'完成提醒
MsgBox ("Thanks for use!")
End Function
评论
boke112导航
回复VBA弄好的话,也非常给力,不过我们很多人都只是用到excel极小部分的功能而已
访客
回复@boke112导航 不错,Vba是座宝库