Excel中自动按照货物名称实现结余累计的示例

来源：互联网发布：ajax post提交数据编辑：程序博客网时间：2024/04/29 01:33

日常生活中，购入消耗货物常运用Excel进行相关统计，能不能实现自动统计出某类货物的结余情况呢？能。示例如下：

Private Sub Worksheet_Change(ByVal Target As Range)Dim Lj, jy, Ljgr, LjxhIf Target.Row <= 1 Then Exit SubIf Target.Column < 3 Or Target.Column > 7 Then Exit SubIf Target.Count > 2 Then Exit Sub'判断输入值非负数If Target.Value < 0 And Target.Column > 3 And Target.Column < 6 Then   MsgBox "内容不能为负值！"   Target.Select   Target.Value = ""End IfIf Target.Row = 2 Then    Cells(2, 6) = Cells(2, 4) - Cells(2, 5)End IfIf Target.Row > 2 Then    Ljgr = 0    Ljxh = 0    For i = 2 To Target.Row - 1      If Cells(i, 3) = Cells(Target.Row, 3) Then        Ljgr = Ljgr + Cells(i, 4)        Ljxh = Ljxh + Cells(i, 5)      End If    Next    '判断消耗是否大于以往结余    If (Target.Value > Ljgr - Ljxh + Cells(Target.Row, 4)) And Target.Column = 5 Then     MsgBox "消耗过多了！"     Target.Value = ""     Target.Select    End If         Cells(Target.Row, 6) = Ljgr - Ljxh + Cells(Target.Row, 4) - Cells(Target.Row, 5) End IfEnd Sub

0 0