如何根据当前年月与L列年月自动设置J列时间权重值？

需求明确：根据当前年月和L列的年月差值，自动给J列设置对应权重：

• 过去6个月内（含第6个月）：权重3
• 过去7-12个月：权重2
• 过去13-24个月：权重1
• 更早的记录：权重0

一、Excel基础函数方案（无需宏） #

用DATEDIF和LOOKUP组合就能实现，公式简洁易维护。

情况1：L列是「YYYY年MM月」文本格式 #

在J2单元格输入以下公式，下拉填充到所有行：

=LOOKUP(DATEDIF(DATE(LEFT(L2,4),MID(L2,6,2),1),EOMONTH(TODAY(),0),"m"),{0,6,12,24},{3,2,1,0})

拆解说明：

1. DATE(LEFT(L2,4),MID(L2,6,2),1)：把L列的文本年月转成当月第一天的日期（方便计算月份差）
2. EOMONTH(TODAY(),0)：取当前月份最后一天，保证月份差计算准确
3. DATEDIF(..., ..., "m")：算出两个日期之间的月份间隔
4. LOOKUP自动匹配权重：月份差0-5→3，6-11→2，12-23→1，≥24→0

情况2：L列是日期格式 #

公式可以简化：

=LOOKUP(DATEDIF(L2,EOMONTH(TODAY(),0),"m"),{0,6,12,24},{3,2,1,0})

二、VBA宏方案（自动触发更新） #

如果需要打开文件或修改L列时自动更新J列，用VBA更省心：

1. 按Alt+F11打开VBA编辑器
2. 找到你要操作的工作表（比如Sheet1），双击打开代码窗口，粘贴以下代码：

' 修改L列内容时，自动更新对应行的J列权重
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cell As Range
    Dim currentDate As Date, targetDate As Date
    Dim monthDiff As Integer
    
    Set rng = Intersect(Target, Me.Range("L:L"))
    If rng Is Nothing Then Exit Sub
    
    currentDate = EOMonth(Date, 0)
    
    For Each cell In rng
        On Error Resume Next
        ' 兼容文本格式的年月（如"2025年9月"）
        targetDate = DateSerial(Left(cell.Value, 4), Mid(cell.Value, 6, 2), 1)
        If Err.Number <> 0 Then targetDate = cell.Value ' 是日期格式就直接用
        On Error GoTo 0
        
        monthDiff = DateDiff("m", targetDate, currentDate)
        
        Select Case monthDiff
            Case 0 To 5: Me.Range("J" & cell.Row).Value = 3
            Case 6 To 11: Me.Range("J" & cell.Row).Value = 2
            Case 12 To 23: Me.Range("J" & cell.Row).Value = 1
            Case Else: Me.Range("J" & cell.Row).Value = 0
        End Select
    Next cell
End Sub

' 打开文件时自动计算所有行的权重
Private Sub Workbook_Open()
    Dim lastRow As Long, i As Long
    Dim currentDate As Date, targetDate As Date
    Dim monthDiff As Integer
    
    lastRow = Me.Sheets("Sheet1").Range("L" & Me.Sheets("Sheet1").Rows.Count).End(xlUp).Row
    ' 注意：把Sheet1改成你的实际工作表名称
    currentDate = EOMonth(Date, 0)
    
    For i = 2 To lastRow
        With Me.Sheets("Sheet1")
            On Error Resume Next
            targetDate = DateSerial(Left(.Range("L" & i).Value, 4), Mid(.Range("L" & i).Value, 6, 2), 1)
            If Err.Number <> 0 Then targetDate = .Range("L" & i).Value
            On Error GoTo 0
            
            monthDiff = DateDiff("m", targetDate, currentDate)
            
            Select Case monthDiff
                Case 0 To 5: .Range("J" & i).Value = 3
                Case 6 To 11: .Range("J" & i).Value = 2
                Case 12 To 23: .Range("J" & i).Value = 1
                Case Else: .Range("J" & i).Value = 0
            End Select
        End With
    Next i
End Sub

使用提示：

• 代码支持L列是文本或日期格式
• 修改工作表名称（把代码里的Sheet1换成你的表名）
• 保存文件时要选「Excel启用宏的工作簿（.xlsm）」格式

内容的提问来源于stack exchange，提问作者Tom Carron