检索 MIN IF 公式的行号

检索 MIN IF 公式的行号

我想知道公式中使用的行,MIN(IF())以便我可以使用它来获取同一行中的另一个值。

我有这个公式:

两张 Sheet2 上,A 是 ID,B 是日期,C 是另一个 ID

{=MIN(IF(Sheet2!A:A=A1,B1-Sheet2!B:B,""))}

我想知道哪一行Sheet2!B:B用于计算公式,然后返回 Sheet2 中该行的 C 列的值。

也许我可以使用INDEX或者MATCH如果我只知道计算中使用的行号!

可能需要使用不同的公式来实现这一点,但我想演示一下我目前所做的。它计算正确。我现在只需要它来获取 Sheet2 C 列中的 ID...

答案1

SUMPRODUCT首先,在数组公式(或任何其他非 CSE 数组处理函数,如、AGGREGATE等)中使用整列引用根本不是一个好主意。

与某些函数不同,例如COUNTIF(S)SUMIF(S)使用整个列引用几乎不会损害计算性能,而数组处理函数必须计算超过所有细胞传递给它们。这同样适用于传递范围内最后一个非空单元格之外的单元格。

因此,例如,如果您只有到第 1000 行的数据,那么通过在这样的公式中使用整个列引用,您将强制 Excel 计算超过一百万细胞的数量超出了实际需要,这对于单一配方来说是一个惊人的数量。

因此,重要的是要么为上行参考选择一个适当的低 - 但足够的 - 值,要么使范围动态,以便通过自动检测列中最后使用的单元格的函数确定上行参考,从而提供最高的效率。

至于您的问题,您可以使用以下数组公式:

=INDEX(Sheet2!C:C,MATCH(MIN(IF(Sheet2!A1:A100=A1,B1-Sheet2!B1:B100)),IF(Sheet2!A1:A100=A1,B1-Sheet2!B1:B100),0))

请注意,我在这里选择了 100 作为上行参考,当然您可以修改,但要记住我之前提出的观点。如果您愿意,我还可以向您展示如何设置我提到的动态范围。

MATCH还请注意 中的条件语句的重复查找数组,在针对此类问题提出的解决方案中,一个关键点被忽略了无数次,错误在于简单地使用:

=INDEX(Sheet2!C:C,MATCH(MIN(IF(Sheet2!A1:A100=A1,B1-Sheet2!B1:B100)),B1-Sheet2!B1:B100,0))

尽管它可能根本不起作用保证正确的结果,因为它在逻辑上存在缺陷。

问候

相关内容