获取 Excel 中一列的最后一行,无需 VBA [英] 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))
他们的问题是第一个不起作用,如果范围中有错误,第二个不返回真正的最后一行,如果是错误,但最后一个非错误行,因此返回 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))
它是如何工作的?
NOT(ISBLANK(A:A))
返回True
和False
的数组1/(NOT(ISBLANK(A:A)))
然后 1 除以该数组会产生另一个1
或#DIV/0 数组!
用作lookup_vector
- 我们使用
2
作为lookup_value
,因为lookup_array
中的最大值是1
,所以找不到,所以查找将匹配数组中的最后一个1
. - 作为
result_vector
,我们使用ROW(A:A)
来获取最后使用的单元格的行号
(或者使用A:A
获取值而不是行号).
NOT(ISBLANK(A:A))
returns an array ofTrue
andFalse
1/(NOT(ISBLANK(A:A)))
then 1 divided by that array results in another array of1
or#DIV/0!
which is used aslookup_vector
- We use
2
aslookup_value
which cannot be found because the largest value in thelookup_array
is1
, so lookup will match the last1
in the array. - As
result_vector
we useROW(A:A)
to get the row number of the last used cell
(alternatively useA:A
to get the value instead of the row number).
这篇关于获取 Excel 中一列的最后一行,无需 VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!