Excel计算每200ms增加一次值 [英] Excel calculate increase in value every 200ms
问题描述
<我已经添加了一个图像进行澄清。这显示了我如何从软件获取数据。 工作表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屋!