总结报告：修改解决方案 [英] Summary Report: Modifying Solution
问题描述
这是对
注意：数据继续超越可以见于上图;直到行8651.
有没有办法可以修改这个解决方案来计算1s列D到S，而不是看B列中的十进制代码？ 。我想像这样会导致使用很多列，所以也许有更好的方式来做我想要完成的工作。要重申，我想做的是找到五个或更多个连续1的开始和结束时间，时间戳在每个进程的2分钟内，并且相应的描述符与开始和结束时间戳一起显示，以便我可以实现上面显示的数据更可读的格式。它看起来像：
所以有没有办法修改当前的解决方案，还是应该考虑一个全新的解决方案？如果以后，你会推荐什么？
要实验：
我们将一个样本工作簿与Eh培根解决方案相结合，并提供了我正在使用的数据示例。该工作簿可从此处下载。
谢谢，
Dan
strong>编辑1：
我已经修改了蒂姆·威廉姆斯贴上的代码，以满足我的需要，并添加了一些功能，在做Google云端硬盘上的文件已更新完整的代码，但总而言之，我对Tim的代码进行了一些轻微更改，以使其符合我所拥有的数据，然后再写入另外两个附件。第一个是使用引擎状态描述符替换运行代码时出现的所有各种Flag＃：XX值，另一个是按照开始时间戳排序值。这是他的代码看起来像我所做的更改：
公共类型标志
tStart As Variant'flag start时间
tLast As Variant'last event time
Count As Long'number of 1's
结束类型
子报表（）
'键入以跟踪每个位置（事件类型）
Dim flags（）As flag
Dim c As Range，nF As Long，i As Long，v，f，t
Dim shtReport As Worksheet ，bContinue As Boolean
设置shtReport = ThisWorkbook.Sheets（技术员报告摘要）
设置c =表（RAW数据）。范围（C2）
nF = Len（c.Value）
ReDim标志（1到nF）
bContinue = True
Do
v = c.Value'bitstring
t = c.Offset（0，-2）.Value'timestamp
如果Len（v）= 0然后
'没有更多的数据，所以插入一个dummy值来触发
写出任何curr entevents
v = String（nF，0）
bContinue = False
如果
'在字符串$ b中的每个位 $ b对于i = 1到nF
f = Mid（v，i，1）
如果f =1，那么
'这个标志的第一个实例？
如果flags（i）.Count = 0然后
flags（i）.tStart = t'事件开始 - 设置开始时间
Else
flags（i）.tLast = t '事件继续 - 设置最后时间
结束If
flags（i）.Count = flags（i）.Count + 1
End If
如果f =0然后
'结束5或更多的1的运行？
如果flags（i）.Count> = 5然后
'将事件写入报表页
带有shtReport.Cells（Rows.Count，1）.End（xlUp）.Offset（1 ，0）
.Value =Flag＃：& i
.Offset（0，1）.Value = flags（i）.tStart
.Offset（0，2）.Value = flags（i）.tLast
End with
结束如果
标志（i）.Count = 0
结束If
Next i
设置c = c.Offset（1，0）
循环当bContinue
调用排序
调用FindAndReplace
End Sub
我认为现在我正在寻找。我会进一步验证，但是我认为我已经找到了一个可行的解决方案。
您可以尝试以下方式：
'键入跟踪每个位位置（事件类型）的数据
公共类型标志
tStart As Variant'flag start time
tLast As Variant'last event time
Count As Long'1的
结束类型
子报表（）
Dim flags（）As flag
Dim c As范围，nF As Long，i As Long，v，f，t
Dim shtReport As Worksheet，bContinue As Boolean
设置shtReport = ThisWorkbook.Sheets（Report）
设置c = Sheet1.Range（B2）
nF = Len（c.Value）
ReDim标志（1到nF）
bContinue = True
v = c.Value'bitstring
t = c.Offset（0，-1）.Value'timestamp
如果Len（v）= 0然后
'否更多的数据，所以插入一个虚拟值触发
'写出任何当前的事件
v = String（nF，0）
bContinue = False
结束如果
'在字符串中的每个位循环
对于i = 1 To nF
f = Mid（v，i，1）
如果f =1然后
'这个标志的第一个实例？
如果flags（i）.Count = 0然后
flags（i）.tStart = t'事件开始 - 设置开始时间
Else
flags（i）.tLast = t '事件继续 - 设置最后时间
结束If
flags（i）.Count = flags（i）.Count + 1
End If
如果f =0然后
'结束5或更多的1的运行？
如果flags（i）.Count> = 5然后
'将事件写入报表页
带有shtReport.Cells（Rows.Count，1）.End（xlUp）.Offset（1 ，0）
.Value =Flag＃：& i
.Offset（0，1）.Value = flags（i）.tStart
.Offset（0，2）.Value = flags（i）.tLast
End with
结束如果
标志（i）.Count = 0
结束If
Next i
设置c = c.Offset（1，0）
循环当bContinue
End Sub
编辑：修复了几个打字错误，并说明了如果有事件正在进行，数据结尾会发生什么。
This is a follow up to a question found here.
The solution provided by Grade 'Eh' Bacon assumed that each decimal code corresponded to a specific status, however that is not the case. The decimal code must first be converted to binary and each bit corresponds to a specific description. So, for example, 0000000000000001 would correspond to just one status "Idle Cutout Active", and 0000000001000001 would correspond to two statuses "High Coolant Temperature" and "Idle Cutout Active".
I've devised a way to split the 16-bit string into each bit and populate a series of columns such that my data looks like this:
Note: The data continues well beyond the row that can be seen in the image above; it goes until Row 8651.
Is there a way that this solution could be modified to count the 1s columns D through S rather than look at the decimal code in Column B?. I imagine this would result in a great number of columns being used, so perhaps there's a more optimal way to do what I'm trying to get done. To reiterate, what I want to do is find the Start and End time of five or more consecutive 1s with timestamps that are within 2 minutes of eachtoher, and have the corresponding descriptor appear with that Start and End time stamp so that I can achieve a more readable format for the data that appears above. It'd look something like:
So, is there a way to modify the current solution or should I consider an entirely new solution? If the later, what would you recommend?
To Experiment:
I've put together a sample Workbook with Grade 'Eh' Bacon's solution implemented and an example of the data that I'm working with. The workbook can be downloaded from here.
Thank you,
Dan
EDIT 1:
I've altered the code Tim Williams posted to suit my needs, and also added some functionality to make it better for what I'm doing. The file on Google Drive has the updated code in full, but to summarize I made some slight alterations to Tim's code so that it fit the data I had and then wrote two additional subs. The first is to replace all the various "Flag#:XX" values that appear when the code is run with the engine status descriptors, and the other is to sort the values by their start time stamps. Here's what his code looks like with the alterations that I made:
Public Type flag
tStart As Variant 'flag start time
tLast As Variant 'last event time
Count As Long 'number of 1's
End Type
Sub Report()
'Type to track data for each of the bit positions (event types)
Dim flags() As flag
Dim c As Range, nF As Long, i As Long, v, f, t
Dim shtReport As Worksheet, bContinue As Boolean
Set shtReport = ThisWorkbook.Sheets("Technician Report Summary")
Set c = Sheets("RAW DATA").Range("C2")
nF = Len(c.Value)
ReDim flags(1 To nF)
bContinue = True
Do
v = c.Value 'bitstring
t = c.Offset(0, -2).Value 'timestamp
If Len(v) = 0 Then
'No more data, so insert a "dummy" value to trigger
' writing out any current "events"
v = String(nF, "0")
bContinue = False
End If
'loop over each "bit" in the string
For i = 1 To nF
f = Mid(v, i, 1)
If f = "1" Then
'first instance of this flag ?
If flags(i).Count = 0 Then
flags(i).tStart = t 'event begins - set start time
Else
flags(i).tLast = t 'event continues - set "last" time
End If
flags(i).Count = flags(i).Count + 1
End If
If f = "0" Then
'end of a run of 5 or more 1's ?
If flags(i).Count >= 5 Then
'write event to Report sheet
With shtReport.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
.Value = "Flag#:" & i
.Offset(0, 1).Value = flags(i).tStart
.Offset(0, 2).Value = flags(i).tLast
End With
End If
flags(i).Count = 0
End If
Next i
Set c = c.Offset(1, 0)
Loop While bContinue
Call Sort
Call FindAndReplace
End Sub
I think it now does what I'm looking for. I'll verify further, but as it stands I think I've found a solution that works.
You could try something like this:
'Type to track data for each of the bit positions (event types)
Public Type flag
tStart As Variant 'flag start time
tLast As Variant 'last event time
Count As Long 'number of 1's
End Type
Sub Report()
Dim flags() As flag
Dim c As Range, nF As Long, i As Long, v, f, t
Dim shtReport As Worksheet, bContinue As Boolean
Set shtReport = ThisWorkbook.Sheets("Report")
Set c = Sheet1.Range("B2")
nF = Len(c.Value)
ReDim flags(1 To nF)
bContinue = True
Do
v = c.Value 'bitstring
t = c.Offset(0, -1).Value 'timestamp
If Len(v) = 0 Then
'No more data, so insert a "dummy" value to trigger
' writing out any current "events"
v = String(nF, "0")
bContinue = False
End If
'loop over each "bit" in the string
For i = 1 To nF
f = Mid(v, i, 1)
If f = "1" Then
'first instance of this flag ?
If flags(i).Count = 0 Then
flags(i).tStart = t 'event begins - set start time
Else
flags(i).tLast = t 'event continues - set "last" time
End If
flags(i).Count = flags(i).Count + 1
End If
If f = "0" Then
'end of a run of 5 or more 1's ?
If flags(i).Count >= 5 Then
'write event to Report sheet
With shtReport.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
.Value = "Flag#:" & i
.Offset(0, 1).Value = flags(i).tStart
.Offset(0, 2).Value = flags(i).tLast
End With
End If
flags(i).Count = 0
End If
Next i
Set c = c.Offset(1, 0)
Loop While bContinue
End Sub
EDIT: fixed up a couple of typos and accounted for what happens at the end of the data if there are events "ongoing"
这篇关于总结报告：修改解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
