更新时间:2023-12-06 17:14:58
这个(没有数组公式)
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A))
它如何工作?
NOT(ISBLANK(A:A))
返回一个True
和False
1/(NOT(ISBLANK(A:A)))
,然后用1除以该数组将得到另一个1
或#DIV/0!
数组,该数组用作lookup_vector
2
用作lookup_value
,因为lookup_array
中的最大值是1
,所以找不到2
,因此查找将匹配数组中的最后一个1
.result_vector
,我们使用ROW(A:A)
来获取最后使用的单元格的行号A:A
获取值而不是行号).NOT(ISBLANK(A:A))
returns an array of True
and False
1/(NOT(ISBLANK(A:A)))
then 1 divided by that array results in another array of 1
or #DIV/0!
which is used as lookup_vector
2
as lookup_value
which cannot be found because the largest value in the lookup_array
is 1
, so lookup will match the last 1
in the array.result_vector
we use ROW(A:A)
to get the row number of the last used cellA:A
to get the value instead of the row number).