VBA中AverageIfs函数如何实现动态条件范围匹配？

Hey there! Let's break down why your current code isn't matching each row in column C with the corresponding cell in column A, and walk through a few solid fixes.

The Root of the Problem #

Your original code assigns the entire C1:C676 range to a single AverageIfs result—one that only uses A1 as the condition. The WorksheetFunction.AverageIfs returns a single value by default, not an array that automatically maps to each row. That's why every cell in column C ends up using the same A1 value instead of their row-matched A column cell.

Solution 1: Loop Through Each Row (Simple & Intuitive) #

For small datasets like 676 rows, a straightforward loop works great. It explicitly calculates the average for each row using the corresponding A column value:

Dim i As Long
Dim targetWs As Worksheet
Dim modelWs As Worksheet

' Set references to your worksheets first (cleaner code)
Set targetWs = ThisWorkbook.Sheets(wsName)
Set modelWs = ThisWorkbook.Sheets(modelName)

' Loop through each row from 1 to 676
For i = 1 To 676
    targetWs.Range("C" & i).Value = Application.WorksheetFunction.AverageIfs( _
        modelWs.Range("M:M"), _
        modelWs.Range("G:G"), _
        targetWs.Range("A" & i).Value _
    )
Next i

Solution 2: Use Excel Formulas with FillDown (Leverage Relative References) #

Excel's built-in formula relative referencing does the row-matching work for you. We'll write the formula once, fill it down, then optionally convert it to values:

Dim targetWs As Worksheet
Dim modelWs As Worksheet

Set targetWs = ThisWorkbook.Sheets(wsName)
Set modelWs = ThisWorkbook.Sheets(modelName)

' Write the formula to C1 (note the relative reference to A1)
targetWs.Range("C1").Formula = "=AVERAGEIFS('" & modelWs.Name & "'!M:M,'" & modelWs.Name & "'!G:G,A1)"

' Fill the formula down to C676—Excel will automatically update A1 to A2, A3, etc.
targetWs.Range("C1:C676").FillDown

' Optional: Convert formulas to static values (so they don't recalculate unexpectedly)
targetWs.Range("C1:C676").Value = targetWs.Range("C1:C676").Value

Solution 3: Use Evaluate for Batch Calculation (Most Efficient) #

The Evaluate method lets you run an array-style calculation in one line, which is faster than looping for larger datasets:

Dim targetWs As Worksheet
Dim modelWs As Worksheet

Set targetWs = ThisWorkbook.Sheets(wsName)
Set modelWs = ThisWorkbook.Sheets(modelName)

' Evaluate the formula as an array to match each row in A1:A676
targetWs.Range("C1:C676").Value = targetWs.Evaluate( _
    "AVERAGEIFS('" & modelWs.Name & "'!M:M,'" & modelWs.Name & "'!G:G,A1:A676)" _
)

All three solutions will make sure that C82 uses A82 as the condition, C1 uses A1, and so on. Pick the one that fits your coding style and needs!

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