我想知道公式中使用的行,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))
尽管它可能根本不起作用保证正确的结果,因为它在逻辑上存在缺陷。
问候