如何循环表格区域并使用多变量输入CountIfs函数？

问题分析 #

你的代码存在几个关键问题：

• 语法错误：Cells(2. 4)里的点号应为逗号，这会直接导致代码报错
• 未循环列：仅处理了第2列，没有遍历其他日期列
• 整列引用：BD.Range("AF:AF")这类整列引用会让CountIfs遍历百万行数据，导致Excel冻结
• 硬编码日期：wk1到wk5的写法不够灵活，无法适配表格列的动态变化

修正后的代码 #

Sub ConstData()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim BD As Worksheet, MD As Worksheet
    Set BD = wb.Sheets("BuiltData")
    Set MD = wb.Sheets("MonthData")
    
    ' 获取当前月第一天
    Dim CurrentMonth As Date
    CurrentMonth = DateSerial(Year(Date), Month(Date), 1)
    
    ' 定义表格范围
    Dim tcTable As Range
    Set tcTable = MD.Range("TC_Month")
    
    ' 填充第二行的周起始日期
    Dim col As Integer
    For col = 2 To 6
        tcTable.Cells(2, col).Value2 = DateAdd("d", 7 * (col - 2), CurrentMonth)
    Next col
    
    ' 优化：使用BD表的实际数据范围（避免整列引用）
    Dim lastRowBD As Long
    lastRowBD = BD.Cells(BD.Rows.Count, "AF").End(xlUp).Row
    Dim rngAF As Range, rngR As Range, rngAK As Range
    Set rngAF = BD.Range("AF2:AF" & lastRowBD)
    Set rngR = BD.Range("R2:R" & lastRowBD)
    Set rngAK = BD.Range("AK2:AK" & lastRowBD)
    
    ' 嵌套循环：遍历行（Associate）和列（日期周）
    Dim rowNum As Integer, colNum As Integer
    Dim currentAssociate As String
    Dim wkStart As Date, wkEnd As Date
    
    For rowNum = 3 To tcTable.Rows.Count ' 从第3行开始遍历Associate
        currentAssociate = tcTable.Cells(rowNum, 1).Value2
        If currentAssociate <> "" Then ' 跳过空行
            For colNum = 2 To tcTable.Columns.Count ' 遍历所有日期列
                wkStart = tcTable.Cells(2, colNum).Value2
                wkEnd = DateAdd("d", 7, wkStart) ' 周结束日期为起始日+7天
                
                ' 计算CountIfs结果
                tcTable.Cells(rowNum, colNum).Value2 = WorksheetFunction.CountIfs( _
                    rngAF, currentAssociate, _
                    rngR, ">250", _
                    rngAK, ">=" & CLng(wkStart), _
                    rngAK, "<" & CLng(wkEnd) _
                )
            Next colNum
        End If
    Next rowNum
End Sub

关键优化点 #

• 语法修复：修正了Cells(2. 4)的点号错误
• 动态日期填充：用循环生成周起始日期，替代硬编码的wk1到wk5
• 缩小数据范围：通过lastRowBD获取BD表的实际数据行，避免整列引用导致的性能问题
• 嵌套循环：同时遍历行（Associate）和列（日期周），一次性填充整个表格
• 空值判断：跳过Associate列为空的行，避免无效计算
• 变量复用：提前定义数据范围和Associate值，减少重复引用，提升代码效率

额外说明 #

如果你的TC_Month表格的行数或列数可能动态变化，还可以进一步优化：

• 用tcTable.Rows.Count替代固定的7，适配行数变化
• 用tcTable.Columns.Count替代固定的6，适配列数变化

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