excel - 解释LOOKUP公式

Question

我正在尝试理解一些旧版 Excel 文件(它可以工作，但我真的很想了解它如何/为何工作)。

有一个用于数据输入的表(input sheet)和一些被调用来处理input sheet中的数据的代码。 。我发现 input sheet 中的行数使用如下查找公式确定:

=LOOKUP(2;1/('Input sheet'!E1:E52863<>"");ROW(A:A))

1. “E”列包含导入项目的名称，并且该列未排序
2. “A”列不包含任何特殊内容 - 我可以将其替换为 B、C 或任何列，并且它不会影响公式的结果

根据我发现的有关查找行为的信息:•If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

这个^-1运算到指定范围有何作用？如果 E(x) 不为空 -> 它应该变成 1，但如果它为空 - 那么它将是 1/0 -> 应该产生 #DIV/0!错误...

1/('Input sheet'!E1:E52863<>"")

如果我将 2 替换为任何正数，结果是相同的(好吧，只尝试了一些，但看起来就是这种情况)。如果我将查找值更改为 0，则会收到 #N/A 错误 -> •If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A

我被困住了......有人能解释一下吗？

Answer 1

LOOKUP 具有罕见的忽略错误的能力。每次 n 为零时，执行 1/n 运算都会产生错误。 False 与零相同。因此，对于您的公式，每个空单元格都会在此计算中产生错误。所有这些结果都放入第二个参数的向量数组中。

搜索任何大于 1 的正值(第一个参数)将导致 LOOKUP 找到上述向量中的最后一个非错误值。

它还有一个很好的可选第三个参数，您可以在其中指定要从中返回查找值的结果向量。这类似于 INDEX/MATCH 组合的 INDEX 组件。

就您的公式而言，第三个参数是一个如下所示的数组:{1;2;3;4;5;6;7;8;9;...n}，其中 n 是最后一个工作表的行号，在现代版本的 Excel 中为 1048576。

因此，LOOKUP 返回第三个参数中向量的值，该值对应于第二个参数中最后一个非错误(非空白单元格)。

请注意，这种确定最后一行的方法将忽略具有导致零长度字符串的公式的单元格。这些单元格看起来是空白的，但它们当然不是。根据具体情况，这可能正是您想要的。另一方面，如果您想要查找 E 列中包含公式的最后一行，即使它导致零长度字符串，那么也可以这样做:

=MATCH("";'Input sheet'!E:E;)