Excel求和需求求助：夜班动态录入数据的自动分配计算方案

核心逻辑 #

1. 定位第4行最后一个非空单元格（最新录入的数值）
2. 定位第4行倒数第二个非空单元格（上一次录入的位置）
3. 计算两个位置之间的列数（需均分的单元格数量）
4. 将最新数值均分填充到第8行对应列，其余列填0

公式方案（适用于Excel 365/2021 动态数组版本） #

在第8行任意单元格（比如B8）输入以下公式，向右填充即可：

=IF(AND(COLUMN()>=INDEX(FILTER(COLUMN($4:$4),$4:$4<>""),COUNTA($4:$4)-1)+1,COLUMN()<=MATCH(9.99999999999999E+307,$4:$4)),INDEX($4:$4,MATCH(9.99999999999999E+307,$4:$4))/(MATCH(9.99999999999999E+307,$4:$4)-INDEX(FILTER(COLUMN($4:$4),$4:$4<>""),COUNTA($4:$4)-1)),0)

公式拆解

• MATCH(9.99999999999999E+307,$4:$4)：获取第4行最后一个数值单元格的列号
• INDEX(FILTER(COLUMN($4:$4),$4:$4<>""),COUNTA($4:$4)-1)：获取第4行倒数第二个非空单元格的列号
• 分母计算需均分的列数，分子为最新录入的数值，判断当前列在目标区间内则返回均分结果，否则返回0

兼容旧版Excel的方案（2019及以前） #

如果无法使用动态数组，可结合数组公式实现：

1. 定义名称LastCol：=MATCH(9.99999999999999E+307,Sheet1!$4:$4)（替换为你的工作表名）
2. 定义名称SecondLastCol：=SUMPRODUCT(MAX((Sheet1!$4:$4<>"")*COLUMN(Sheet1!$4:$4))-MAX((Sheet1!$4:$4<>"")*(COLUMN(Sheet1!$4:$4)<MAX((Sheet1!$4:$4<>"")*COLUMN(Sheet1!$4:$4)))*COLUMN(Sheet1!$4:$4)))
3. 在第8行单元格输入公式，按Ctrl+Shift+Enter触发数组计算后向右填充：

=IF(AND(COLUMN()>=SecondLastCol+1,COLUMN()<=LastCol),INDEX($4:$4,LastCol)/(LastCol-SecondLastCol),0)

VBA自动触发方案（更便捷） #

如果允许使用宏，可设置工作表事件，每次在第4行录入数值后自动计算第8行：

1. 右键工作表标签 → 查看代码
2. 粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastCol As Integer, secondLastCol As Integer
    Dim totalVal As Double, splitNum As Integer
    
    '仅处理第4行的数值录入
    If Target.Row <> 4 Or Target.Cells.Count > 1 Then Exit Sub
    
    '获取最后一个非空列
    lastCol = Me.Cells(4, Me.Columns.Count).End(xlToLeft).Column
    '获取倒数第二个非空列
    secondLastCol = Me.Cells(4, lastCol).End(xlToLeft).Column
    '如果只有一个录入值，默认填充当前列
    If secondLastCol = 1 And Me.Cells(4, 1) = "" Then secondLastCol = lastCol
    
    totalVal = Me.Cells(4, lastCol).Value
    splitNum = lastCol - secondLastCol
    
    '清空第8行旧数据
    Me.Rows(8).ClearContents
    '填充均分结果
    Me.Range(Me.Cells(8, secondLastCol + 1), Me.Cells(8, lastCol)).Value = totalVal / splitNum
End Sub

3. 保存为启用宏的工作簿（.xlsm格式）

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