基于总额或百分比调整单个值的Excel VBA功能优化需求

需求说明：

• 存在Plan A和Plan B两个方案，Plan B由Plan A预填充
• 需要支持三种预算调整方式：
  • 调整底部总预算单元格（B11）
  • 调整每行的品牌/支出占比百分比（C3:C10）
  • 输入每行的绝对支出额（B3:B10）
• 此前使用公式出现「循环引用过多」错误，现有VBA已实现总额与单个支出额的双向调整，需补充占比调整的功能

修改后的完整VBA代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TotalCell As Range
    Dim ExpenseRange As Range
    Dim RatioRange As Range
    Dim Total As Double
    Dim Cell As Range
    Dim TargetRow As Long
    
    ' 定义关键单元格/范围
    Set TotalCell = Range("B11")
    Set ExpenseRange = Range("B3:B10")
    Set RatioRange = Range("C3:C10")
    
    Application.EnableEvents = False ' 禁用事件防止循环触发
    
    ' 情况1：修改总预算单元格（B11）
    If Not Intersect(Target, TotalCell) Is Nothing Then
        Total = TotalCell.Value
        ' 根据总预算和占比重新计算所有支出额
        For Each Cell In ExpenseRange
            TargetRow = Cell.Row
            Cell.Formula = "=IF(" & TotalCell.Address & "=0,0," & TotalCell.Address & "*($C$" & TargetRow & "/SUM($C$3:$C$10)))"
        Next Cell
    
    ' 情况2：修改支出额单元格（B3:B10）
    ElseIf Not Intersect(Target, ExpenseRange) Is Nothing Then
        Total = WorksheetFunction.SUM(ExpenseRange)
        TotalCell.Value = Total
        ' 更新对应行的占比为当前支出额/总预算
        TargetRow = Target.Row
        Range("C" & TargetRow).Value = Target.Value / Total
    
    ' 情况3：修改占比单元格（C3:C10）
    ElseIf Not Intersect(Target, RatioRange) Is Nothing Then
        Total = TotalCell.Value
        TargetRow = Target.Row
        ' 根据总预算和修改后的占比重新计算对应行的支出额
        Range("B" & TargetRow).Formula = "=IF(" & TotalCell.Address & "=0,0," & TotalCell.Address & "*($C$" & TargetRow & "/SUM($C$3:$C$10)))"
        ' 重新计算总预算（确保和支出额总和一致）
        TotalCell.Value = WorksheetFunction.SUM(ExpenseRange)
    End If
    
    Application.EnableEvents = True ' 重新启用事件
End Sub

关键逻辑说明 #

1. 总预算调整：修改B11时，所有支出额单元格（B3:B10）会自动按对应行的占比（C列）重新分配总预算，公式自动处理总预算为0的边界情况。
2. 支出额调整：修改某行B列数值时，总预算会自动更新为所有支出额的总和，同时对应行的C列占比会同步更新为「该行支出额/总预算」，保证占比与金额匹配。
3. 占比调整：修改某行C列占比时，对应行的B列支出额会基于当前总预算重新计算，随后总预算会同步更新为所有支出额的总和（若占比总和不为100%，支出额总和会与原总预算有偏差，此逻辑保留了用户手动调整占比的灵活性）。

注意事项 #

• 确保C列的占比数值为百分比格式或小数形式（如20%输入0.2）
• 若需要强制占比总和为100%，可添加额外逻辑校验并提示用户

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