在杂乱工作簿中使用MAXIFS函数的VBA实现问题

问题分析 #

你要实现的是根据C列匹配条件，提取E列对应组的最大值填充到H列，但原代码存在几个关键问题：

• 重复打开同一个工作簿，完全冗余
• MAXIFS公式写法错误，VBA中无法直接通过字符串拼接单元格地址调用函数
• 未正确获取动态最后行号lLastRow
• 仅设置H2单元格，未批量填充所有目标行

解决步骤及修正代码 #

1. 获取动态最后行号：用xlUp从列末尾向上定位最后有数据的行，兼容任意行数范围
2. 优化工作簿引用：直接使用当前工作簿，避免重复打开操作
3. 正确构造MAXIFS公式：将公式作为字符串赋值，严格区分绝对引用与相对引用
4. 批量填充公式：一次性给H列所有目标行赋值公式，大幅提升效率

修正后的代码：

Sub FillMaxValues()
    Dim wsTarget As Worksheet
    Dim lLastRow As Long
    
    ' 直接引用当前工作簿的目标工作表
    Set wsTarget = ThisWorkbook.Worksheets("Priority Values calculated")
    
    ' 简化G2值粘贴为纯文本的操作，设置H1表头
    wsTarget.Range("G2").Value = wsTarget.Range("G2").Value
    wsTarget.Range("H1").Value = "Seats In Each State"
    
    ' 获取C列最后有数据的行号（用E列也可，确保范围一致即可）
    lLastRow = wsTarget.Cells(wsTarget.Rows.Count, "C").End(xlUp).Row
    
    ' 批量给H2到H最后一行设置MAXIFS公式
    With wsTarget.Range("H2:H" & lLastRow)
        .Formula = "=MAXIFS($E$2:$E$" & lLastRow & ", $C$2:$C$" & lLastRow & ", C2)"
        ' 可选：若需将公式转为纯数值，取消下面一行注释
        '.Value = .Value
    End With
End Sub

关键说明 #

• 用wsTarget.Cells(wsTarget.Rows.Count, "C").End(xlUp).Row获取最后行号，无论数据是50行还是数万行都能准确识别
• 公式中$E$2:$E$" & lLastRow和$C$2:$C$" & lLastRow是绝对引用范围，C2是相对引用的当前行条件，确保填充时条件自动对应每行C列值
• 把复制粘贴值的操作简化为直接赋值，避免剪贴板操作的冗余与潜在问题
• 若最终需要纯数值而非公式，取消.Value = .Value的注释即可完成转换

内容的提问来源于stack exchange，提问作者Tony Lima