在命名区域中对列求平均值：Excel VBA新手技术求助

Hey Chase, totally get how frustrating it is when you're just starting out with Excel VBA and the Dummies book isn't covering your specific dynamic data scenario. Let's break this down into two practical solutions—one no-code for quick wins, and a VBA script to automate the repetitive work so you don't have to manually copy-paste every month.

一、无VBA快速方案（适合新手快速上手） #

First, we need to turn your score column into a dynamic named range—this way, when the number of employees changes each month, the range automatically expands without you having to adjust cell references manually.

步骤1：创建动态命名区域 #

• Open your project workbook, select the first data row under your score header (e.g., cell A2, assuming A1 is "考核分数")
• Go to the top menu: 公式 → 定义名称
• In the pop-up window:
  • 名称: Enter a memorable name like DynamicScores
  • 引用位置: Paste this formula (replace Sheet1 and $A:$A with your actual sheet name and score column):

    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
    

  • Click 确定—now your range will automatically include new scores added to the column each month.

步骤2：计算平均值（相对引用，无固定单元格） #

Just type this formula in the cell where you want the average to show:

=AVERAGE(DynamicScores)

This formula relies entirely on the dynamic range, so you never have to hardcode cell ranges like A2:A100. The average will refresh automatically when you update monthly data.

If your named range includes multiple columns (e.g., employee name, score, department) and you need the average of a specific column, use INDEX for relative column referencing:

=AVERAGE(INDEX(YourMultiColumnRange,0,2))

Here, 2 is the position of your score column in the named range. To make it fully relative (so the column updates when you drag the formula right), use:

=AVERAGE(INDEX(YourMultiColumnRange,0,COLUMN()-1))

COLUMN()-1 adjusts the column index dynamically—drag the formula right, and it’ll switch to the next column in your named range automatically.

二、VBA自动化方案（解放双手，每月一键完成） #

Since you need to repeat this calculation monthly and save data to a archive workbook, a simple macro will handle all the repetitive work for you. I’ve added detailed comments so you can tweak it to fit your setup.

步骤1：编写VBA宏 #

1. Open your project workbook, press Alt+F11 to open the VBA Editor
2. Right-click your workbook name in the left pane → 插入 → 模块
3. Paste the code below, and replace the placeholder paths/names with your actual information:

Sub SaveMonthlyAverage()
    ' Declare variables (no need to change this part as a beginner)
    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim averageScore As Double
    Dim scoreRange As Range
    
    ' --------------------------
    ' 1. Set current project workbook as data source
    ' --------------------------
    Set sourceWorkbook = ActiveWorkbook
    
    ' --------------------------
    ' 2. Get dynamic named range (replace with your range name)
    ' --------------------------
    On Error Resume Next ' Avoid error if range isn't found
    Set scoreRange = sourceWorkbook.Names("DynamicScores").RefersToRange
    On Error GoTo 0
    
    ' Popup alert if range isn't found
    If scoreRange Is Nothing Then
        MsgBox "找不到命名区域'DynamicScores'，请先按前面的方法创建动态区域！", vbExclamation
        Exit Sub
    End If
    
    ' --------------------------
    ' 3. Calculate average score
    ' --------------------------
    averageScore = Application.Average(scoreRange)
    
    ' --------------------------
    ' 4. Open archive workbook (replace with your archive file path)
    ' --------------------------
    Set targetWorkbook = Workbooks.Open("C:\Work\月度考核留存.xlsx")
    
    ' --------------------------
    ' 5. Find first empty row in archive sheet and write data
    ' --------------------------
    Dim lastEmptyRow As Long
    ' Assume archive data is in "考核记录" sheet, column 1 = month, column 2 = average
    With targetWorkbook.Sheets("考核记录")
        lastEmptyRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        ' Write current month (auto-generated from system date, format: "2024年05月")
        .Cells(lastEmptyRow, 1).Value = Format(Date, "yyyy年mm月")
        ' Write calculated average
        .Cells(lastEmptyRow, 2).Value = averageScore
    End With
    
    ' --------------------------
    ' 6. Save and close archive workbook
    ' --------------------------
    targetWorkbook.Save
    targetWorkbook.Close
    
    ' Success alert
    MsgBox "本月考核平均值已成功保存到留存工作簿！", vbInformation
End Sub

步骤2：运行宏 #

1. Return to Excel, press Alt+F8, select SaveMonthlyAverage
2. Click 执行—the macro will automatically calculate the average and save it to your archive workbook.

注意事项 #

• Create your archive workbook first, with a sheet named 考核记录 (or update the sheet name in the code to match yours)
• If the macro won’t run, check Excel’s macro settings: 文件 → 选项 → 信任中心 → 信任中心设置 → 宏设置, select 启用所有宏 (or 启用带通知的宏)
• After updating monthly scores in your project workbook, just run the macro—no manual formula adjustments needed.

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