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

问题描述

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

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.

还有一张图片:

推荐答案

我建议基于工作表更改事件处理的 VBA 解决方案.打开VBA Project，将下面的代码放入Microsoft Excel Objects部分的目标工作表中:

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

我为常量添加了一些注释.在 Scope 中设置要监控的变化范围，在 DX 和 DY 中输出结果增量的偏移量，以及作为奖励该算法不仅支持计算最后一个和前一个数字之间的增量，还支持通过组织为字典数组的缓冲区为每个目标单元格计算任意数量的帧之间的增量，因此在 Buf 中设置缓冲区的大小，如果您不想使用缓冲区然后只保留 0 大小，例如.G.3 的值将计算最后一个值与延迟 800 毫秒的值之间的差值.

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.

更新

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

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 每 200 毫秒计算一次值的增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！