excel - 获取 Excel 中某一列的最后一行，无需 VBA

Question

一般来说，使用 VBA 中的用户定义函数可以轻松获取 Excel 中给定列的最后一行:

Function lastRow(wsName As String, Optional columnToCheck As Long = 1) As Long
    Dim ws As Worksheet
    Set ws = Worksheets(wsName)
    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row
End Function

对于最后一行，我到目前为止找到了两个 Excel 公式:

• =SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))
• =MAX(IFERROR(MATCH(E1+99,A:A),0),IFERROR(MATCH("zzz",A:A),0))

他们的问题是，如果范围内有错误，第一个不起作用，如果有错误，第二个不会返回真正的最后一行，但最后一个非错误行，因此返回 6 而不是 7:

问题是如何获得返回 7 的公式，因为这是 A 列中使用的最后一行？虽然，这是一个错误。没有 VBA。

Answer 1

这个(没有数组公式)

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)) 

它是如何工作的？

1. NOT(ISBLANK(A:A)) 返回一个由 True 和 False 组成的数组
2. 1/(NOT(ISBLANK(A:A))) 然后 1 除以该数组会得到另一个 1 或 #DIV/0 数组! 用作 lookup_vector
3. 我们使用 2 作为 lookup_value，但无法找到，因为 lookup_array 中的最大值是 1，因此查找将匹配数组中的最后一个 1。
4. 作为result_vector，我们使用ROW(A:A)来获取最后使用的单元格的行号
   (或者使用 A:A 获取值而不是行号)。