Excel制造标准成本模板：切换验证列表时自动重填数据需求

针对你需要在切换「Current/Increase/Decrease/NPI」选项时自动按规则填充数据的需求，最直接的快速方案是用**工作表变更事件（Worksheet_Change）**绑定VBA代码，不用复杂公式，切换选项自动触发逻辑：

操作步骤 #

1. 右键点击ROI - Target工作表标签，选择「查看代码」打开VBA编辑器
2. 粘贴下面的代码，根据你的实际单元格位置调整引用（代码里有注释提示）
3. 保存工作簿为.xlsm格式（启用宏的工作簿）

核心VBA代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 下拉选项所在单元格，改成你实际用的单元格（比如A1）
    Dim dropdownCell As Range
    Set dropdownCell = Me.Range("A1")
    
    ' 只处理下拉选项的变更，避免其他操作触发
    If Not Intersect(Target, dropdownCell) Is Nothing Then
        Application.EnableEvents = False ' 防止循环触发事件
        
        ' 引用ROI - Current工作表
        Dim wsCurrent As Worksheet
        Set wsCurrent = ThisWorkbook.Worksheets("ROI - Current")
        
        ' 根据选项执行对应逻辑
        Select Case dropdownCell.Value
            Case "Current"
                ' 从ROI - Current读取固定值
                Me.Range("B2").Value = wsCurrent.Range("C2").Value ' List Price
                Me.Range("B3").Value = wsCurrent.Range("C3").Value ' Bulk Price
                Me.Range("B4").Value = wsCurrent.Range("C4").Value ' Materials Cost
                
                ' 可选：锁定单元格防止修改（要先保护工作表）
                Me.Range("B2:B4").Locked = True
                Me.Range("B2:B4").Interior.ColorIndex = 35 ' 浅绿标记锁定状态
                
            Case "Increase/Decrease"
                ' 解锁List Price和Materials Cost允许修改
                Me.Range("B2,B4").Locked = False
                Me.Range("B2,B4").Interior.ColorIndex = xlColorIndexNone
                
                ' 自动计算Bulk Price：(当前Bulk/当前List)*新List Price
                Me.Range("B3").Formula = "=(" & wsCurrent.Range("C3").Address(True, True, xlExternal) & "/" & wsCurrent.Range("C2").Address(True, True, xlExternal) & ")*B2"
                
            Case "NPI"
                ' 仅允许修改List Price，锁定其他两个单元格
                Me.Range("B2").Locked = False
                Me.Range("B2").Interior.ColorIndex = xlColorIndexNone
                Me.Range("B3:B4").Locked = True
                Me.Range("B3:B4").Interior.ColorIndex = 35
                
                ' 按规则计算Bulk Price和Materials Cost
                Me.Range("B3").Formula = "=0.7*B2" ' 0.7*List Price
                ' 假设ROI - Current的C5是毛利率比例（比如0.3），计算材料成本
                Me.Range("B4").Formula = "=B3*" & wsCurrent.Range("C5").Value
        End Select
        
        Application.EnableEvents = True ' 恢复事件触发
    End If
End Sub

关键调整提示 #

• 把代码里的A1（下拉选项位置）、B2/B3/B4（成本数据单元格）改成你实际用的单元格
• ROI - Current里的C2/C3/C4/C5也要对应改成你存储当前值、毛利率比例的位置
• 如果需要锁定单元格，先到「审阅」选项卡点击「保护工作表」，取消勾选「选定锁定单元格」，这样锁定的单元格就不能编辑了

这个方法完全是“快速粗暴”的实现，切换选项自动完成所有填充和公式设置，不会出现手动输入覆盖公式的问题。

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