我在 Excel 中有一个值表（如果这有区别的话是 2103），绘制后看起来像这样：
我试图为每个系列找到值的索引，在此之后，所有后续值都位于某个范围内（在这种情况下介于 99 和 101 之间）。请注意，这些值不一定按升序或降序排列，但它们最终都会收敛到同一个值（在这种情况下为 100）。
我搜索了一下，感觉 MATCH、IF 和/或 VLOOKUP 的组合应该可以实现这个功能，但我搞不懂。有人能帮忙吗？
谢谢！
答案1
几种可能的方法：
选项1
1) 根据 X 值对数据进行排序，并按降序排列。您希望最大的 X 值作为第一个点。
2) 确定相邻列中每个后续点之间的直线的斜率。
3) 当计算出的直线斜率大于点 1 的最大值和点 2 的最小值（或反之亦然）时，您就知道已经超出了收敛点。
选项 2
1）根据 X 值按降序对数据进行排序。与选项 1 相同
2）假设您有一种方法可以确定收敛值，并将其放入单元格中。
3) 找到列表中第一个超过收敛值 + 或 - 您的容差的 Y 值。
寻找首次出现
为了找到某个事物的第一次出现，您需要使用数组公式或执行类似数组计算的公式。我将尝试使用 AGGREGATE 函数进行说明，但还有其他方法。由于 AGGREGATE 针对我选择的选项执行数组计算，因此请避免在 AGGREGATE 函数中使用完整的列引用，否则可能会进行过多的计算，从而降低系统速度。
AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)
以上是 AGGREGATE 函数的基本分解。我们要做的就是告诉 AGGREGATE 按升序对公式的结果进行排序，忽略任何错误的公式结果，然后为我们提供排序列表中的第一个条目。信不信由你，其中大部分都非常简单明了。我现在先不讨论这个，专注于最难的部分……我们的公式。
我们正在寻找的是某事物的第一次出现。我们要么需要该事物的值，要么需要它在列表中的位置（或行号）。通常在这种情况下，我试图找到的东西我通常作为公式中的分子。任何需要满足的条件都会给出 TRUE 或 FALSE 结果，这些条件都会放在公式的分母中。
这里的技巧是，当通过数学运算放置 TRUE 或 FALSE 时，它们会转换为 1（表示 TRUE）和 0（表示 FALSE）。 （总之，当像 IF 这样的函数在决策中寻找 TRUE 和 FALSE 时，0 被视为 FALSE，而其他每个数字都被视为 TRUE。） 因此，任何时候用 FALSE 除时，结果都会除以 0，这会导致计算错误，而之前已经指出 AGGREGATE 将被设置为忽略。
因此，通过适当的公式构建，您将获得一个仅符合分母条件的项目排序列表。由于它是一个按升序排序的列表，当设置为返回第一个条目时，您将获得（在我们的例子中）最低的行号或最小值。
示例：假设您的 X 值在 B 列，Y 值在 C 列，数据在第 2:100 行。我们想找到第一个距离 81.3 大于 2.4 的 Y 值
=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)
现在要返回该单元格的值，您需要将 AGGREGATE 公式嵌入 INDEX 公式中。
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))
请注意，B:B 的完整列引用位于 AGGREGATE 函数之外，这是可以的。如果您想知道 Y 值是多少，请将 B:B 更改为 C:C。
根据您介于 101 至 99 之间的标准，您可以使用：
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))
这些方程式将给出超出限制的第一个点的值。因此，要获取超出限制的最后一个点，您需要从行号中减去 1，这将得出新的公式：
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)
注意*分母中两个条件检查之间的 。 的*作用类似于 AND 函数（只有 1 * 1 = 1；两个条件都必须为真）。 A 的+作用类似于 OR 函数（其中一个或两个条件都可以为真，结果才为非零）。