这个程序员用他的Lookup函数做什么? [英] What is this programmer doing with his Lookup function?

问题描述

我在论坛上发现了一个公式，用于查找列中最后一个填充的单元格:

I found on a forum a formula to find the last populated cell in a column:

LOOKUP(2,1/(G:G<>""),ROW(G:G)))

但是这是怎么回事?

1/(G:G<>"")

一个除以??? (这不等于"吗?)在这里我不理解逻辑.

One divided by ??? (something that's not equal to ""?) I don't understand the logic, here.

推荐答案

如果要通过公式审核"评估公式逐步观察计算，我强烈建议限制范围.请说:

If you want to observe the calculation step by step by evaluating the formula with Formula Auditing I strongly recommend limiting the range. Say apply:

=LOOKUP(2,1/(G1:G10<>""),ROW(G1:G10))

出于说明目的，请在表格的下方填充不超过G6的内容(但在G1:G5范围之间的空隙在这种情况下可能有助于了解正在发生的情况).

And, for illustration purposes, populate no lower down the sheet than say G6 (but a void between, in the range G1:G5, may in that case help to understand what is happening).

对于这个答案，我只考虑五个单元:G1，G3和G4填充，G2和G5(向前)填充.

For this answer I am only going to consider five cells: G1, G3 and G4 populated, G2 and G5 (onwards) not.

1/(G1:G5<>"")

确实是这个公式的核心.正如您已经认识到的那样，G1:G5<>""会测试是否不等于" . ""是Excel单元格中空"的约定.如果填充(即不为空")，则返回TRUE，否则返回FALSE.因此，对于在此示例中选择的五个单元格，将返回一个数组，该数组关于G1:G5的顺序为:

Is indeed at the heart of this formula. G1:G5<>"" does, as you have recognised, test whether not equal to "". "" is the convention for 'empty' for an Excel cell. If populated (ie "not empty") this returns TRUE and FALSE otherwise. Hence for the five cells as chosen for this example an array is returned, regarding G1:G5 in order, of:

TRUE;FALSE;TRUE;TRUE;FALSE.

在算术计算中，Excel将TRUE视为1，将FALSE视为0.因此，使用上述真值表作为分母，并使用1作为分子将给出一个数组(再次按顺序):

In arithmetic calculations Excel treats TRUE as 1 and FALSE as 0. Hence using the above truth table as the denominator and 1 as the numerator gives an array (again in order) of:

1/1;1/0;1/1;1/1;1/0

解析为:

1;#DIV/0!;1;1;#DIV/0!.

在2上方的LOOKUP函数中，选择它为 lookup_value . (其他大于1的数字也同样适用.)因此，我们在仅由1或错误组成的数组中寻找2.因此，没有机会找到完全匹配的内容，因此默认值即为最后一个值(按顺序排列，不计算错误).数组中的最后一个1是第四个元素，而ROW(G1:G5)中的第四个元素是…4.

In the LOOKUP function above 2 was chosen as the lookup_value. (Any other number greater than 1 would serve equally well.) So we are looking for 2 in an array that is composed exclusively of either 1s or errors. Therefore there is no chance of finding an exact match, so the default kicks in, which is the last value (in order, not counting errors). The last 1 in the array is the fourth element, and the fourth element in ROW(G1:G5) is …4.

G4是ColumnG中最后一个填充的单元格(在我的示例中).

G4 is the last populated cell in ColumnG (in my example).

这篇关于这个程序员用他的Lookup函数做什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！