解释LOOKUP公式 [英] Explain LOOKUP formula
问题描述
我正在尝试了解一些传统的Excel文件（它的工作原理，但我真的很想了解它的工作原理）。
I'm trying to understand some legacy Excel file (it works, but I would really like to understand how/why it's working).
有一个数据输入表（输入表）和一些被调用来处理数据的代码输入表。我发现输入表中的行数是使用如下查找公式确定的：
There is a sheet for data input (input sheet)and some code that is called to process data in the input sheet. I found out that number of rows in the input sheet is determined using a Lookup formula like this:
= LOOKUP（2; 1 /（'输入表'！E1：E52863<>）; ROW（A：A））
- E列包含导入项目的名称，列为不排序
- A列不包含任何特殊的东西 - 我可以用B，C或任何列来替换它，它不影响公式的结果
根据什么我发现Lookup行为：•如果LOOKUP函数找不到完全匹配，它将在lookup_range中选择小于或等于该值的最大值。
According to what I have found about Lookup behaviour: •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！错误...
What does this ^-1 operation to the specified range? If E(x) is not empty -> it should turn into 1, but if it is empty - then it would be 1/0 -> that should produce #DIV/0! error...
1 /（'输入表'！E1：E52863<"） >
1/('Input sheet'!E1:E52863<>"")
结果是一样的，如果我用任何正数替换2（好的，只尝试一些，但看起来像这样）。如果我将查找值更改为0，则我得到＃N / A错误 - > •如果该值小于lookup_range中的所有值，则LOOKUP函数将返回＃N / A
The outcome is the same, if I replace 2 with any positive number (ok, tried only some, but it looks like this is the case). If I change lookup value to 0, then I get #N/A error -> •If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A
我被卡住了吗？任何人都可以散发光吗？
I am stuck... can anyone shed some light?
推荐答案
LOOKUP 具有忽略错误的罕见能力。执行 1 / n 操作将在每次 n 为零时产生错误。假与零相同。所以，对于你的公式，每个空单元格在这个计算中会产生一个错误。所有这些结果都放在第二个参数的向量数组中。
LOOKUP has the rare ability to ignore errors. Conducting the 1/n operation will produce an error every time n is zero. False is the same as zero. So, for your formula, every empty cell produces an error in this calculation. All of those results are put in a vector array in the 2nd argument.
搜索大于 1的任何正值（第一个参数） / code>将导致LOOKUP找到上述向量中的最后一个非错误值。
Searching for any positive value (the 1st argument) larger than 1 will result in LOOKUP finding the last non-error value in the above vector.
它还具有不错的可选第三个参数，您可以在其中指定返回查询值的结果向量。这与INDEX / MATCH组合的INDEX组件相似。
It also has the nice optional 3rd argument where you can specify the vector of results from which to return the lookup value. This is similar to the INDEX component of the the INDEX/MATCH combo.
在您的公式的情况下，第三个参数是一个如下所示的数组：{1 ; 2; 3; 4; 5; 6; 7; 8; 9; ... n}其中n是工作表的最后一行号，在现代版本的Excel中为1048576。
In the case of your formula, the 3rd argument is an array that looks like this: {1;2;3;4;5;6;7;8;9;...n} where n is the last row number of the worksheet, which in modern versions of Excel is 1048576.
所以LOOKUP从第二个参数中最后一个非错误（非空白单元格）的第三个参数中的向量中返回值。
So LOOKUP returns the value from the vector in the 3rd argument that corresponds to the last non-error (non-blank cell) in the 2nd argument.
请注意，确定最后一行的这种方法将忽略具有导致零长度字符串的公式的单元格。这样的细胞看起来是空白的，但当然不是。根据情况，这可能正是您想要的。另一方面，如果你想在列E中找到最后一行，即使它有一个零长度的字符串，那么它也会有一个公式，那么这样做：
Note that this method of determining the last row will ignore cells that have formulas that result in a zero-length string. Such cells look blank but of course they are not. Depending on the situation, this may be precisely what you want. If, on the other hand you want to find the last row in column E that has a formula in it even if it results in a zero-length string, then this will do that:
=MATCH("";'Input sheet'!E:E;)
这篇关于解释LOOKUP公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
