vba之与excel司龄计算

224wifipassw:19640404

一、其实很简单：

1、计算工龄

"M2"为单元格的工龄日期。

=CEILING(DATEDIF(M2,NOW(),"M")/12,0.5)

2、工龄分段公式

=LOOKUP(L156,{0,5,10,15,20,25;"5年以下","5~10年","10~15年","15年~20年","20~25年","25年以上"})

二、适用VBA实现

Sub 插入新列并计算司龄()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim entryDate As Date
    Dim yearsOfService As Double
    
    ' 指定要操作的工作表
    Set ws = ThisWorkbook.Sheets("测试") '！！！！！！！！必须为此表名
    If IsError(Application.Match("司龄", ws.Rows(1), 0)) Then
    
    ' 在最后一列右侧插入两个新列
    ws.Cells(1, ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1).Value = "司龄"
    ws.Cells(1, ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1).Value = "司龄分段"

    End If
    Dim sllb As Variant
    Dim slfd As Variant
    Dim sl_start As Variant
    sllb = Application.Match("司龄", ws.Rows(1), 0)
    slfd = Application.Match("司龄分段", ws.Rows(1), 0)
    sl_start = Application.Match("进入集团日期", ws.Rows(1), 0) ' 进入集团日期所在的列，查找
    
    ws.Columns(sllb).Select
    With Selection
    .HorizontalAlignment = Excel.xlCenter
    .Font.Name = "宋体"
   End With
   
    ws.Columns(slfd).Select
    With Selection
    .VerticalAlignment = Excel.xlCenter
    .Font.Name = "宋体"
   End With

    
    
    
    ' 获取最后一行
    lastRow = ws.Cells(ws.Rows.Count, sl_start).End(xlUp).Row
    'ws.Cells(3, 6) = lastRow
    
    ' 计算司龄并写入新列
    For i = 2 To lastRow
        entryDate = ws.Cells(i, sl_start).Value  ' 进入集团日期所在的列，这里假设是第1列
        yearsOfService = Round(((Date - entryDate) / 365.25), 1)  ' 计算司龄，保留1位小数
        
        If yearsOfService - Int(yearsOfService) < 0.5 Then
            yearsOfService = Int(yearsOfService) + 0.5 ' 小数位小于0.5则按0.5年
        Else
            yearsOfService = Int(yearsOfService) + 1  ' 小数位大于0.5则按1年
        End If
        'ws.Cells(2, 5) = sllb
        'ws.Cells(i, sllb).Font.Name = "宋体"

        ws.Cells(i, sllb).Value = yearsOfService   ' 写入司龄列
        
        ' 计算司龄分段并写入新列
        'ws.Cells(i, slfd).Font.Name = "宋体"
        If yearsOfService < 5 Then
            ws.Cells(i, slfd).Value = "<5年"
        ElseIf yearsOfService < 10 Then
            ws.Cells(i, slfd).Value = "5~10年"
        ElseIf yearsOfService < 15 Then
            ws.Cells(i, slfd).Value = "10~15年"
        ElseIf yearsOfService < 20 Then
            ws.Cells(i, slfd).Value = "15~20年"
        Else
            ws.Cells(i, slfd).Value = ">20年"
        End If
    Next i
    
End Sub

这段VBA代码实现了在指定工作表中插入两列，并计算员工的司龄并写入新插入的列中。具体实现步骤如下：

1. 首先指定要操作的工作表为"测试"。
2. 判断是否已存在"司龄"和"司龄分段"列，若不存在，则在最后一列的右侧插入这两个新列。
3. 获取进入集团日期所在的列，并将司龄和司龄分段列的文字格式设置为居中和宋体。
4. 获取最后一行的行号。
5. 对每一行进行循环，获取进入集团日期的数值，并根据当前日期计算司龄，保留小数点后一位。
6. 根据司龄的值判断司龄分段，并将计算结果写入司龄和司龄分段列中。

总体来说，这段代码实现了在指定工作表中添加司龄和司龄分段两列，并根据员工的进入集团日期计算其司龄并分类。