我有大量包含日期、时间、ID 的交易数据
像这样:
DATE TIME ID
7/6/2015 9:30:22 0266731550
7/6/2015 9:33:19 0266731550
7/6/2015 10:31:38 0266731550
然后我想计算一下有多少笔交易每天即独特的(可以通过ID识别)但时间不超过5分钟。
比如有3个相同的ID,对于前2个ID,由于它与前一个ID的时间间隔不超过5分钟,0266731550
所以算作1个( )。0266731550
9:33:19-9:30:22 < 5mins
与第三个ID不同,由于它与前一个ID的时间间隔超过5分钟(),0266731550
因此被算作1个。0266731550
10:31:38-9:33:19 > 5mins
附言:我使用的是 Excel 2010
答案1
如果您不反对使用 VBA,我可以稍后再提供更详细的代码,但使用伪代码:
Dim array(1, 1) As Long
For i = 1 To Len(table)
' if value is already in the array, skip this part. if not, redim the array and store the value.
If Not Match(Cells(1, i), array) <> 0 Then
ReDim array(1, i)
array(1, i) = Cells(1, i).Value
End If
' write the time intervals for comparison
Dim intervals(2, 1)
' iterate over the column where timestamps are and write all of them to array, checking with array(1, i) to make sure the ID is correct. redim as required.
' sort the array and measure for 5min intervals.
' keep a unique-values variable that you increment every time a satisfactory interval is found.
Next i
您可以将这些值推送到工作表、文件或消息框。