![根据 2 个条件获取行号(使用 1 个模糊搜索)](https://linux22.com/image/1453777/%E6%A0%B9%E6%8D%AE%202%20%E4%B8%AA%E6%9D%A1%E4%BB%B6%E8%8E%B7%E5%8F%96%E8%A1%8C%E5%8F%B7%EF%BC%88%E4%BD%BF%E7%94%A8%201%20%E4%B8%AA%E6%A8%A1%E7%B3%8A%E6%90%9C%E7%B4%A2%EF%BC%89.png)
我正在尝试创建一些以动态单元格引用为索引的图表。这些图表基于测试机器生成的时间戳。我有一列布尔值(0 或 1)用于标记“力日志”,以及一个包含机器各种数据字段的电子表格。这台机器还在单独的工作簿上生成电子表格(此电子表格包含标记开始和结束时间的实际时间戳),我正在手动将其复制并粘贴到包含数据的工作簿的另一张工作表中(它有大约 5000 行)。我想根据时间戳找到图表的开始和结束索引。不幸的是,单独生成的具有正确时间戳可供选择的 Excel 工作簿与主数据电子表格上的时间戳滞后约 1 秒(此滞后是可变的,但在 0 到 2 秒之间)。此外,我的“力日志”布尔值应该是时间戳的 1,但很多时候我有额外的力日志,它们与启动图表的正确时间不对应。以下是按要求提供的代表性样本。
Worksheet containing data (Call this Sheet 2)
A B C D
Time Stamp Force Log Field 1 Field 2
1 2/6/2015 3:21:22 PM 1 100 500 <- Extraneous boolean value
2 2/6/2015 3:21:23 PM 0 101.4 499.2
3 2/6/2015 3:21:24 PM 1 99.3 501.4 <- I want this index for graph start
4 2/6/2015 3:21:25 PM 0 100.3 498.9
5 2/6/2015 3:21:26 PM 0 102.4 500.7
6 2/6/2015 3:21:27 PM 1 101.8 499.3 <- I want this index for graph end
Worksheet containing machine generated data summary (call this Sheet 1)
Start Time End Time
3:21:23 PM 3:21:26 <- The times here lead the other worksheet by less than 2 seconds
They are also off by 2/6/2015, but I can generate a helper column
After obtaining the indices 3 and 6, I would graph the values of Field 1 and Field 2.
以下是我从我的问题中了解到的我需要做的事情:通过截断或有条件地通过范围(正负 2 秒)测试等效性来执行模糊搜索时间戳,并确保“强制日志”布尔值列也设置为 1。
我对 Excel 参考和搜索功能还不熟悉,所以不确定是否遗漏了什么。到目前为止,我尝试了 LOOKUP、VLOOKUP(由于我的数据列的格式不符合 table_array 的要求,所以无法使用)、INDEX MATCH(由于我的某个搜索要求模糊,所以无法使用),但似乎找不到任何易于理解的解决方案,允许使用多个条件进行搜索并返回多个结果(我有多个时间每次都需要一个结果,而不是单个时间需要多个结果;我一直在努力掌握 Excel 中的数组公式)。
我在此非常感谢任何提供解决方案或建议的人。
编辑:我知道我的问题可能有一个非常奇怪的背景。如果您需要我澄清或详细说明我到底需要做什么,请告诉我。谢谢!
答案1
这是一个解决方案,它将为您提供起点和终点的索引号。您可以使用这两个数字为要绘制图表的值创建动态命名范围。
首先,以下是我对您的数据所做的两项操作,以使公式更简单。您不必也这样做,但您需要更改公式的几个部分以匹配您的数据设置。
- 我将您的数据转换为具有默认名称的表格
Table1
。当公式引用“时间戳”的所有值时,您会看到它为Table1[Time Stamp]
。 - 我将这两个单元格命名为开始时间和结束时间
StartTime
和,EndTime
以便于理解公式。否则,它会显示为A10
和B10
,不太容易理解。
以下是给出开始时间索引号的公式:
{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(StartTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(StartTime),IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(StartTime)>=0,1))))),0)}
...这是结束时间:
{=MATCH(1,IF(Table1[Force Log]=1,IF(HOUR(Table1[Time Stamp])=HOUR(EndTime),IF(MINUTE(Table1[Time Stamp])=MINUTE(EndTime),IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)<=2,IF(SECOND(Table1[Time Stamp])-SECOND(EndTime)>=0,1))))),0)}
关键点是它们都是数组公式,因此必须使用Ctrl+ Shift+输入Enter。不要尝试粘贴大括号 { },因为这样不起作用。粘贴除大括号之外的所有内容,使用Ctrl+ Shift+ Enter,大括号 { } 就会显示出来。
工作原理如下:
它实际上只是一堆嵌套的IF
公式,结果是一个1
由 和组成的数组0
,其中有一个MATCH
公式用于查找第一个1
。这些IF
语句设置了一系列条件,如果满足所有条件,则值为1
。如果其中任何一个不是满足,则值为0
。(嗯,实际上是,FALSE
但其计算结果为0
。)它会检查Force Log
= 1
,小时是否相同,分钟是否相同,并且秒数是否包含在您要查找的时间的 +0 / +2 秒内。请注意,我没有检查日期是否相同,因为您的开始和结束时间不包括日期值。
要点:如果有多个记录符合此条件(即,如果示例中的时间戳 2/6/2015 3:21:23 PM 的强制日志值为 1),则此公式将找到第一的符合条件的记录。