如何在Excel单元格中找到先前值和当前值之间的差异? [英] How to find a difference between previous and current values in Excel Cell?

问题描述

A1 =使用cntrl + shift +开始显示4:00 AM的时间

A1 = time start using cntrl+shift+ which shows 4:00 AM

B1 =使用cntrl + shift +的时间结束，显示8:00 AM

B1 = time end using cntrl+shift+ which shows 8:00 AM

C1 =使用= MOD(B1-A1,1)到4:00:00之间的时间

C1 = time between using =MOD(B1-A1,1) which shows 4:00:00

D1 =单元格C1中的时间使用= C1 * 1440转换为分钟，显示240分钟

D1 = time from cell C1 converted into minutes using =C1*1440 which shows 240 minutes

E1 = D1新值-D1旧值(例如:如果旧值是240，新值是360，则360-240 = 120.)

大胆是我想要的，我只能希望你们能理解我正在努力实现的目标.如果需要的话，我会尽力弄清楚.预先感谢！

The bold is what I want, and I can only hope you guys can understand what I am trying to accomplish. I'll try to be clearer if need be as you wish. Thanks in advance!

推荐答案

为了找到新值和旧值之间的差异，例如，在"D1"中(发生单元格更改事件)，并在其中显示单元格"E1"，下面的代码段显示了一般的解决方案(请参见清单1):

In order to find a difference between new and old values entered, for example, in "D1" (on cell change event) and display it cell "E1", the general solution is shown in following code snippet (see Listing 1):

清单1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 4 And Target.Row = 1 Then
        'new val
        newVal = Range("D1").Value
        Application.EnableEvents = False
        Application.Undo
        'old val
        oldVal = Range("D1").Value
        Range("D1").Value = newVal
        'diff between new and old val
        Range("E1").Value = newVal - oldVal
        Application.EnableEvents = True
    End If
End Sub

与您的特定情况有关(假设通过在单元格"A1"或"B1"中按组合键 CTRL + SHIFT +:(或刚刚键入)来输入时间，则会显示修改后的代码段)在清单2中.

Pertinent to your particular case (assuming that time is entered by pressing combination CTRL+SHIFT+: (or just typed) in either cell "A1" or "B1", the modified code snippet is shown in Listing 2.

清单2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Row = 1 And (Target.Column = 1 Or Target.Column = 2) Then
        'new val
        a1 = Range("A1").Value
        b1 = Range("B1").Value
        newVal = Range("D1").Value

        'disable events and undo
        Application.EnableEvents = False
        Application.Undo
        'old val
        oldVal = Range("D1").Value

        'diff between new and old val
        diff = newVal - oldVal

        Range("A1").Value = a1
        Range("B1").Value = b1
        Range("E1").Value = diff

        're-enable events
        Application.EnableEvents = True
    End If
End Sub

另外，如果您使用分钟差公式，例如:在单个单元格中输入 = MOD(B1-A1,1)* 1440 ，可以简化工作表的计算(例如"D1")，而不要使用一对("C1"和"D1").同样，整个计算可以仅在VBA模块中执行，并基于"A1"和"B1"中的条目更新"E1"值(在这种情况下无需"C1"和"D1").

On a separate note, your Worksheet computation could be simplified if you use a formula for a difference in minute like: =MOD(B1-A1,1)*1440 entered in a single cell (e.g. "D1") instead of using the couple ("C1" and "D1"). Also, the entire computation could be performed solely in VBA module, updating "E1" value based on entries in "A1" and "B1" (no need for "C1" and "D1" in this scenario).

希望这会有所帮助.最好的问候，

Hope this will help. Best regards,

这篇关于如何在Excel单元格中找到先前值和当前值之间的差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！