在没有VBA的情况下获取Excel中列的最后一行 [英] Get the last row of a column in Excel, without VBA

问题描述

通常，使用VBA中的用户定义函数可以轻松完成Excel中给定列的最后一行:

In general, getting the last row of a given column in Excel is easily done with a User-Defined Function in VBA:

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公式:

For the last row there are two excel formulas I found so far:

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

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

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

The problem with them is that the first does not work, if there is an error in the range and the second does not return the real last row, if it is an error, but the last non-error row, thus returning 6 and not 7:

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

The question is how to get a formula that returns 7, as this is the last row in column A that is used? Although, it is an error. Without VBA.

推荐答案

这个(没有数组公式)

=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，所以找不到2，因此查找将匹配数组中的最后一个1.
4. 作为result_vector，我们使用ROW(A:A)来获取最后使用的单元格的行号
   (或者使用A:A获取值而不是行号).

1. NOT(ISBLANK(A:A)) returns an array of True and False
2. 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
3. We use 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.
4. As result_vector we use ROW(A:A) to get the row number of the last used cell
   (alternatively use A:A to get the value instead of the row number).

这篇关于在没有VBA的情况下获取Excel中列的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！