☰

根据 2 个条件获取行号（使用 1 个模糊搜索）

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
根据 2 个条件获取行号（使用 1 个模糊搜索）

我正在尝试创建一些以动态单元格引用为索引的图表。这些图表基于测试机器生成的时间戳。我有一列布尔值（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

这是一个解决方案，它将为您提供起点和终点的索引号。您可以使用这两个数字为要绘制图表的值创建动态命名范围。

首先，以下是我对您的数据所做的两项操作，以使公式更简单。您不必也这样做，但您需要更改公式的几个部分以匹配您的数据设置。

  1. 我将您的数据转换为具有默认名称的表格Table1。当公式引用“时间戳”的所有值时，您会看到它为Table1[Time Stamp]。
  2. 我将这两个单元格命名为开始时间和结束时间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），则此公式将找到第一的符合条件的记录。

相关内容