如何实现Excel物料概览表与变动日志表的库存数值自动联动更新？

实现两表库存数据自动同步的核心逻辑为：以两表共有的全局唯一物料标识为匹配键，将日志表的所有出入库变动按物料维度分类汇总，自动计算物料概览表的实时库存。以下是两种可直接落地的实现路径：

方案1：纯函数计算（适配全版本Excel，零代码） #

操作前先统一两张表的列位规则，避免跨表匹配错位：

• Material Overview（物料概览表）列位约定：
  • A列：物料唯一编码（全局无重复，作为跨表匹配的核心键）
  • B列：物料名称
  • C列：总库存数量（需自动计算的列）
• Log（日志表）列位约定：
  • A列：变动时间
  • B列：对应物料的唯一编码（与概览表编码格式完全一致）
  • C列：变动值（入库记正数、出库记负数，纯数值格式）

具体操作：

1. 点击Material Overview表第一个物料对应的总库存单元格（即C2），输入公式：
   =SUMIF(Log!B:B, A2, Log!C:C)
2. 按回车确认公式生效后，将鼠标悬停到C2单元格右下角，等光标变成黑色十字形状，向下拖拽填充到所有物料行的总库存单元格即可。

公式逻辑为：逐行遍历Log表的B列，找到与当前概览表A列物料编码完全匹配的记录，将这些记录对应的C列变动值全部加总，即为该物料的实时总库存。后续在Log表新增任何变动记录，概览表的总库存会自动更新，无需手动调整公式。
如果存在录入时多余空格导致的匹配失效问题（比如Log表的物料编码末尾多打了空格），可将公式替换为带文本清洗的版本：
=SUMPRODUCT(TRIM(Log!B:B)=TRIM(A2)*TRIM(Log!C:C))

方案2：VBA增量更新（适配超大体量日志场景，避免计算卡顿） #

当Log表的变动记录超过1万行时，整列范围的SUMIF公式会导致表格明显卡顿，此时可以通过Excel内置的VBA事件机制，在Log表录入变动值时自动完成库存增量更新——仅计算刚录入的单条记录，不会触发全表重算。

具体操作：

1. 按Alt+F11打开VBA编辑器，在左侧工程导航栏双击Log工作表，打开对应代码编辑区
2. 将以下代码粘贴到编辑区：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 定义触发范围：Log表的C列（变动值列），从第2行开始跳过表头
    Dim watchRange As Range
    Set watchRange = Me.Range("C2:C" & Rows.Count)
    
    ' 用户修改的单元格不在触发范围内时，直接退出
    If Intersect(Target, watchRange) Is Nothing Then Exit Sub
    
    ' 逐行处理刚录入的变动内容
    Dim logRow As Range
    For Each logRow In Target.Rows
        ' 提取当前日志行的物料编码、变动值
        Dim matCode As String
        Dim changeVal As Double
        matCode = Me.Cells(logRow.Row, "B").Value
        changeVal = logRow.Value
        
        ' 在物料概览表的A列匹配对应物料行
        Dim ovRange As Range
        Set ovRange = ThisWorkbook.Sheets("Material Overview").Columns("A").Find(What:=matCode, LookAt:=xlWhole)
        
        ' 匹配成功则更新总库存
        If Not ovRange Is Nothing Then
            ThisWorkbook.Sheets("Material Overview").Cells(ovRange.Row, "C").Value = _
                ThisWorkbook.Sheets("Material Overview").Cells(ovRange.Row, "C").Value + changeVal
        Else
            ' 未匹配到物料时弹出提示，避免录入错误
            MsgBox "日志行" & logRow.Row & "的物料编码【" & matCode & "】在物料概览表中不存在，请检查", vbExclamation
        End If
    Next
End Sub

3. 保存文件时选择「.xlsm」格式（支持宏的Excel格式），后续每次在Log表的C列录入变动值后按回车，系统会自动匹配概览表的对应物料行，同步更新总库存。

常见问题避坑 #

• 不要用物料名称作为跨表匹配的键：不同批次、不同规格的同名称物料很容易出现重名，导致匹配计算错误，必须用全局唯一的物料编码作为匹配依据
• Log表的变动值列不要混入文本内容：比如不要填“买入5个”“卖出1件”这类描述性文字，必须填写纯数值，否则公式或代码会将文本识别为无效内容，导致库存计算偏差
• 启用公式方案后，不要手动修改物料概览表的总库存单元格：否则手动填写的内容会被自动重算的公式覆盖，所有库存变动必须通过Log表录入，保证数据逻辑自洽

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