Excel计算每200ms增加一次值 [英] Excel calculate increase in value every 200ms

问题描述

我有这个工作表从API获取数据，它每200毫秒刷新一次。我想计算每200毫秒不断增加的值的变化。例如，单元格B2在200ms之后具有4的值，其改变为7，然后变为16，然后到26等，它只是不断增加值。我想要的是从最新值中减去旧值，以获得更改，例如7-4 = 3或16-7 = 9或26-16 = 10。

<我已经添加了一个图像进行澄清。这显示了我如何从软件获取数据。 工作表http://i64.tinypic.com/2uny5w2.png

还有一个图像：

工作表http://i66.tinypic.com/2ppajvn.jpg

解决方案

我建议VBA解决方案，基于工作表更改事件处理。打开 VBA项目，并将以下代码放入 Microsoft Excel对象部分中的目标工作表中：

  Option Explicit 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
 
'通过菜单添加对Microsoft Scripting Runtime的引用 - 工具 - 参考文献
 
 Const Scope =C2：C5'监控区域
 Const DX = 1'水平结果偏移
 Const DY = 0'垂直结果偏移
 Const Buf = 0'FIFO缓冲区大小
 
静态oData（0到Buf）作为新字典
静态oIndex作为新字典
 Dim rCells As Range 
 Dim oCell 
 Dim i As Long 
 
设置rCells = Application.Intersect（Target，Target.Parent.Range（Scope））
如果不是rCells是没有
每个oCell在rCells 
与oCell 
i = oIndex（.Address）
 .Offset（DY，DX）.Value = .Value  -  oData（i） （.Address）
 oData（i）（。Address）= .Value 
 i = i + 1 
如果i> Buf然后i = 0 
 oIndex（.Address）= i 
结束
下一个
结束如果
 
 End Sub 
  

我为常量添加了一些注释。在 Scope 中设置要监视的更改范围，在 DX 和 DY ，作为一种奖励，该算法不仅支持最后和之前的数字之间的计算增量，而且还可以通过组织为字典数组的缓冲区中的每个目标单元的任意数量的帧，所以设置缓冲区的大小在 Buf 中，如果不想使用缓冲区，那么只需离开 0 size ，e。 G。 3 的值将计算最后一个值与您的案例延迟800 ms之间的差值。

更新

注释中要求的版本略有简化，将以下代码放入目标工作表中：

  Private Sub Worksheet_Change（ByVal Target As Range）
 
 Const Scope =C2：C5'监控区
 
静态oData作为新字典
 Dim rCells As Range 
 Dim oCell 
 Dim dDelta 
 
设置rCells = Application.Intersect（Target，Target.Parent .Range（Scope））
如果不是rCells是没有
对于每个oCell在rCells 
与oCell 
 dDelta = .Value  -  oData（.Address）
如果dDelta 0然后
 .Offset（0，1）.Value = dDelta 
 oData（.Address）= .Value 
 End If 
 End With 
 Next 
结束如果
 
 End Sub 
  

I have this worksheet which gets data from API and its refreshes itself every 200 milliseconds. I want to calculate the change in value which is constantly increasing every 200 ms. For example Cell B2 has a value of 4 after 200 ms its changes to 7 then to 16 then to 26 etc, it just keeps adding value into it. All I want is to subtract the old value from the latest value to get the change for example 7-4=3 or 16-7=9 or 26-16=10.

I have added an image for clarification. This shows how I'm getting a data from software. worksheet http://i64.tinypic.com/2uny5w2.png

And one more image:

worksheet http://i66.tinypic.com/2ppajvn.jpg

解决方案

I suggest VBA solution, based on worksheet change event handling. Open VBA Project and put the below code into the target worksheet in Microsoft Excel Objects section:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    ' add reference to Microsoft Scripting Runtime via Menu - Tools - References

    Const Scope = "C2:C5" ' monitoring area
    Const DX = 1 ' horizontal result offset
    Const DY = 0 ' vertical result offset
    Const Buf = 0 ' FIFO buffer size

    Static oData(0 To Buf) As New Dictionary
    Static oIndex As New Dictionary
    Dim rCells As Range
    Dim oCell
    Dim i As Long

    Set rCells = Application.Intersect(Target, Target.Parent.Range(Scope))
    If Not rCells Is Nothing Then
        For Each oCell In rCells
            With oCell
                i = oIndex(.Address)
                .Offset(DY, DX).Value = .Value - oData(i)(.Address)
                oData(i)(.Address) = .Value
                i = i + 1
                If i > Buf Then i = 0
                oIndex(.Address) = i
            End With
        Next
    End If

End Sub

I added some comments for constants. Set the range which change to be monitored in Scope, the offsets where the resulting delta will be output in DX and DY, and as a bonus that algorithm supports computing delta not only between last and previous numbers, but also between any number of frames for each target cell via buffer organized as array of dictionaries, so set the size of the buffer in Buf, if you do not want to use the buffer then just leave 0 size, e. g. the value of 3 will compute delta between the last value and the one delayed by 800 ms for your case.

UPDATE

There is slightly simplified version of the code as requested in comment, put the below code into the target worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Const Scope = "C2:C5" ' monitoring area

    Static oData As New Dictionary
    Dim rCells As Range
    Dim oCell
    Dim dDelta

    Set rCells = Application.Intersect(Target, Target.Parent.Range(Scope))
    If Not rCells Is Nothing Then
        For Each oCell In rCells
            With oCell
                dDelta = .Value - oData(.Address)
                If dDelta <> 0 Then
                    .Offset(0, 1).Value = dDelta
                    oData(.Address) = .Value
                End If
            End With
        Next
    End If

End Sub

这篇关于Excel计算每200ms增加一次值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！