搜索列连续值，并将三个不同的值返回给其他单元格 [英] Searching column for consecutive values and returning three different values to other cells

问题描述

我有一系列的列。一个包含数据点的时间戳，下一个是数据点，之后是数据点的从十进制转换为二进制，其余列是分割为每个位的二进制字符串。每列都有一个标题：无引擎速度，引擎降级等。

这是我要做的，但没有与VBA / Excel做的技能。我试图把所有的信息，并把它放在一个读者更友好的东西。

所以，例如：

会得到我的：

描述来自列标题和时间范围开始/结束将来自第一列。根据描述列的内容，我将使用Excel IF函数或某种类型的VLOOKUP函数获取错误代码。我需要的是一个VBA代码或一组Excel函数，它将根据这些位列的内容为我填充这些描述和时间戳列。

我如何设想它的工作原理如下：搜索每个位列，如果找到1，并且其中的四个单元格还在其下方也被发现为1s前1和最后1的日期戳在我创建的更易读的报表中填写开始/结束时间。除此之外，列标题被复制到描述字段。

如果连续有5个或更多的1次，我只想得到时间戳的原因是我希望有一段时间过去之前，状态被认为是事件'。我想见到的第二个条件是时间戳相当接近（比如说在彼此的2分钟内）。这就是为什么即使在16-May-15 21:52:47链接的图片中有一个1事件，我将其从我链接的第二个图像中排除。

时间戳（和时间戳本身）旁边的数字将根据用户打开工作簿的时间而改变。这些列是对数据库的查询的结果，并根据上一个班次的开始和结束时间进行更改。另外，当复制时间戳时，我知道您需要粘贴单元格值，而不仅仅是一个常规的粘贴，否则你将查询数组复制到数据库。我不知道这是否对编码解决方案有任何影响，但我认为值得一提。

我考虑过尝试使用某种类型的VLOOKUP功能，但是我发现我不会做我想要的，因为它不检查是否有五个事件或更多陆续。任何可以提供的指导或指导将不胜感激。我觉得我有一个如何做到这一点的想法，但我是VBA的新手，而我的Excel功能还没有完成任务，而我的Google Googling并没有提供我所需要的东西。

我希望我对我正在努力的解释清楚，如果不是，请随时提问。

$ b $感谢，

Dan

编辑1：

作为呃培根级别，我问了一个与此相关的问题，导致更适合我需求的解决方案。可以在此处找到它。

解决方案

由于您似乎没有使用帮助列的问题，所以可以以相当简单的方式完成，给定您的数据已按日期排序。

RAW数据表

添加一个新的列（我们称之为列X），它检查你的单元格是否是第一个单元格，它启动一个5个日期戳的字符串，所有这些都是你所说的2分钟[从X2开始，结束假设X100假设日戳是列A，十进制中的参考代码是列B]：

  = COUNTIFS（A2 ：A $ 100，> =&A2  -  TIMEVALUE（00:02:00），B2：B $ 100，B2）
  

这将计算当前单元格下面的多少个单元格不超过2分钟，包括本身，并且在列B中也有相同的代码。我们将使用它来检查该单元格是否启动一个新的5 +相同的字符串，接近时间的代码。

在列Y中，把它从Y2开始：

  = IF（AND（OR（B2，B1，A2  -  TIMEVALUE（00： 02:00）> A1），X2> = 5），TRUE）
  

首先检查是否（1）当前单元格中的代码与上一单元格中的代码不匹配; OR（2）当前行的时间比最后一行晚至少2分钟（无论如何，这是一个新的循环）。然后，我们检查AND条件，列X中的当前行是否在相同的时间周期内使用相同的代码显示至少5个以下的单元格的匹配项。如果为TRUE，则返回TRUE。否则，它将返回FALSE。

然后，列Z中的代码返回我们在该行的第n个命中代码的编号。即：这是否是第一，第二，第二次，一串5代码被击中[从Z2开始;硬编码Z1为0，或做一些其他特殊情况，所以第一个不会添加上面的单元格的标题，导致#VALUE！错误]：

  = IF（Y2，Z1 + 1，Z1）
  / pre> 
 
 
这将使Z变成升序的位置列表，每当新的代码创建失败时重复值。现在我们需要抓住这一行代码的描述。
 
 
 
假设你有一个所有代码的有序列表，其中列1将等于1000000 ...，第2列将与01000000 ..等同。将单行列（或单列行）命名为Range，我将调用Code_Index。
 
 
 
在AA中，从AA2开始，输入以下内容：
  = INDEX（Code_Index，SEARCH 1，C2））
  
此检查在该行的C列出现哪个字符1 ，这就成为我们想要从（我们在命名范围Code_Index中放置）的描述的位置。
 
 
 
最后，我们需要添加一行检查以查看何时结束5+代码的特定块。说AB：AB（我最初忘了这个，最初有点不合乎规定）。在AB2中并复制下来，您将在新的2分钟内检查是否在同一件事情的行中至少有5行，在2分钟内，还有下一行是否相同。 
  = IF（AND（COUNTIFS（B $ 1：B2，B2，A $ 1：A2，>A2  -  TIMEVALUE （00:02:00）> = 5，或（B3→B2，A3 + TIMEVALUE（00:02:00）> = A2）），MAX（Z $ 1：Z1） ）
  
 结果页 
 
 
 
现在假设这一切都在Sheet1上，而您希望Sheet2上的干净结果。
 
 
 
在表2中，列A为索引，只需从A2开始，每行后加1，列B将是另一个帮助列，列C将拉出描述，列D将拉起开始时间，列E将拉动结束时间。 p> 
 
 
在列B中，输入以下公式，它将检查我们所在的新索引（从最后一个选项卡的列Z开始）从B2开始， p> 
 
 
  = MATCH（A2，Sheet1！Z：Z，0）
  
这将从原始数据选项卡中找到与A1上当前索引号匹配的第一行。然后只需在下一个3列的每个索引公式中使用它，即可提取说明，开始时间和结束时间。
 
 
 
在C2中（将描述从在最后一个选项卡上的AA）：
  = INDEX（Sheet1！AA：AA，B2）
  
在D2（从最后一个选项卡的A开始时间）
  = INDEX（Sheet1！A：A，B2）
  
 E2（从最后一个标签上的A拉出结束时间，*根据AB列索引中的行号）
  = INDEX（Sheet1！A：A，MATCH（A2，Sheet1！AB：AB，0））
  
让我知道，如果我误解了你想要你的2分钟时间块的设置;做一些严格的测试，以确保它按照你的期望行事。
 
I have a series of columns. One contains a time stamp for a data point, the next is the data point, the one following that is a conversion from decimal to binary for that data point, and the remaining columns are that binary string split into each bit. Each column has a title: "No Engine Speed", "Engine Derate", and so on.

Here's what I'd like to do, but don't have the skill with VBA/Excel to do. I'm trying to take all that information and put it into something that's more friendly to a reader. 

So, for example this:


would get me this: 


The description comes from the column titles, and the time range start/end would come from the first column. The error codes I'll get with an Excel IF function or some kind of VLOOKUP function based on the contents of the description column. What I need is a VBA code or set of Excel functions that will populate those description and time stamp columns for me based on the contents of those bit columns. 

How I envisioning it working is as follows: each bit column is searched through, if a 1 is found and the four cells more more below it are also found to be 1s the date stamp of the first 1 and final 1 populate the Start/End times in the more readable report I'm creating. In addition to this, the column header is copied to the description field. 

The reason I want to only get the time stamps if there are five or more 1s consecutively is that I want some time to pass before the state is considered to be an 'event'. A second condition I'd like to meet would be that the time stamps are reasonably close together (say, within 2 minutes of each other). This is why even though there is a '1' event in the picture I linked for "16-May-15 21:52:47" I excluded it from the second image I linked.  

The numbers next to the time stamps (and the timestamps themselves) will change depending on when the user opens the workbook. Those columns are the result of a query to a database and change based on what the previous shift's start and end time were. As a side note, when copying the time stamps I know that you need to paste the cell value and not just a regular paste, otherwise you wind up copying a query array to the database. I don't know if that has any bearing on a coding solution but I thought it was worth mentioning. 

I considered trying to use some type of VLOOKUP function, but what I found doesn't quite do what I want because it doesn't check if there's five events or more in succession. Any guidance or direction you all can provide would be greatly appreciated. I feel like I have an idea of how to do this but I'm new to VBA and my Excel abilities are not up to the task just yet, and my Googling isn't turning up what I need. 

I hope I was clear in my explanation of what I'm trying to do, feel free to ask questions if I wasn't.

Thanks, 

Dan

EDIT 1:

As Grade 'Eh' Bacon suggested, I asked an additional question related to this one that resulted in a solution better suited to my needs. It can be found here. 
解决方案
Since you don't seem to have a problem using helper columns, this can be done in a fairly straightforward way, given that your data is already sorted by date.

RAW DATA TAB

Add a new column (we'll call it column X) which checks to see if your cell is the first cell which starts a string of 5 date stamps, all of which being, as you say, 2 minutes apart [starting in X2, ending at an assumed X100 assuming the datestamp is column A, and the reference code in decimal is column B]:
=COUNTIFS(A2:A$100,">=" & A2 - TIMEVALUE("00:02:00"),B2:B$100,B2)
This counts how many cells below the current cell are no more than 2 minutes later, including itself, and also have the same code in column B. We will use this to check whether that cell starts a new string of 5 + identical, near-in-time, codes.

In Column Y, starting at Y2, put:
=IF(AND(OR(B2<>B1,A2 - TIMEVALUE("00:02:00")>A1),X2 >= 5), TRUE)
This will first check if either (1) the code in the current cell doesn't match the code in the previous cell; OR (2) the time of the current row is at least 2 minutes later than the last line (either way, this is a new cycle). Then we check for the AND condition of whether the current row in column X shows a match of at least 5 cells below with the same code in the same time cycle. If TRUE, then it will return TRUE. Otherwise, it will return FALSE.

Then the code in column Z returns the number of the nth "hit code" we're on for that row. ie: whether this is the 1st, 2nd, nth time that a string of 5 codes has been hit [starting in Z2; hardcode Z1 as "0", or do some other special case so the first one won't add the title of the cell above, resulting in a #VALUE! error]:
=IF(Y2,Z1+1,Z1)
This will turn Z into an ascending list of positions, repeating values whenever a NEW code has failed to be created. Now we need to grab the description of the code that this row represents.

Assume you have an ordered list of all your codes, where column 1 would be equivilent to "1000000...", column 2 would be equivilent to "01000000.." etc. Name that single-row column (or, single column row) as a Range, which I will call Code_Index.

In column AA, starting at AA2, put the following:
=INDEX(Code_Index,SEARCH("1",C2))
This checks at which character "1" appears in column C at that row, and that becomes the position we want to pull the description from (which we have placed in the named range Code_Index).

Finally, we need to add a row which checks to see when a specific block of 5+ codes ends. Say, AB:AB (I forgot about this initially, hence its a little out of order initially). In AB2 and copied down, you will check to see whether there are at least 5 rows in a row of the same thing, within the 2 minute block, and also whether the next row is the same thing, within a new 2 minute block.
=IF(AND(COUNTIFS(B$1:B2,B2,A$1:A2,">"A2 - TIMEVALUE("00:02:00")>=5,OR(B3<>B2, A3 + TIMEVALUE("00:02:00")>= A2)),MAX(Z$1:Z1),"")
RESULTS PAGE

Now assume that's all on Sheet1, and you want your 'clean' results on Sheet2.

In sheet 2, have column A be an index, which simply starts at 1 on A2 & adds 1 each row afterwards. Column B will be another 'helper' column, column C will pull the description, column D will pull the start time, and column E will pull the end time. 

In column B, put the following formula, which will check which 'new' index we're on (from column Z on the last tab). Starting at B2,
=MATCH(A2,Sheet1!Z:Z,0)
This will find the first row from the raw data tab which matches the current index number on A1. Then simply use this in an index formula in each of the next 3 columns, to pull the description, start time, and end time.

In C2 (pulling the description from AA on the last tab):    
=INDEX(Sheet1!AA:AA,B2)
In D2 (pulling the start time from A on the last tab)
=INDEX(Sheet1!A:A,B2)
In E2 (pulling the end time from A on the last tab, *based on the row number from the column AB index)
=INDEX(Sheet1!A:A,MATCH(A2,Sheet1!AB:AB,0))
Let me know if I've misconstrued how you want your "2 minute time blocks" set up; do some rigorous testing to make sure it acts the way you expect.

                        
这篇关于搜索列连续值，并将三个不同的值返回给其他单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！