查找没有VBA的最后一个非空单元格ADDRESS [英] Find the last non empty cell ADDRESS without VBA
问题描述
我需要在Excel电子表格中找到最后一个非空单元格,但是我需要它的地址,而不是它的值.
I need to find the last non empty cell in an Excel spreadsheet, but I need its address, not its value.
例如:当我想要K列中最后一个非空单元格的值时,我使用以下公式:
For example: When I want the value for the last non empty cell in column K, I use this formula:
=LOOKUP(2;1/(NOT(ISBLANK(K:K)));K:K)
我的问题是,我不需要单元格的值,我需要它的地址.
My problem is, I dont want cell's value, I need its address instead.
我已经尝试使用CELL函数,但没有成功.
I already tried to use function CELL without success.
推荐答案
You can get the last populated row number of a known column with the MATCH function but it helps to know whether the column is filled with numbers and/or dates or text.
=match("zzz"; K:K) 'for last text¹ in column K
=match(1e99; K:K) 'for last date or number in column K
地址功能返回其余部分.
=address(match("zzz"; K:K); 11; 4; 1) 'address of last text¹ in column K
=address(match(1e99; K:K); 11; 4; 1) 'address of last date or number in column K
=address(max(iferror(match(1e99; K:K); 0); iferror(match("zzz"; K:K); 0)); 11; 4; 1) 'address of last date or number or text¹ in column K
4用于相对寻址(例如K99); 1用于xlA1寻址.有关完整语法的信息,请参见地址功能.
The 4 is for relative addressing (e.g. K99); the 1 is for xlA1 addressing. See the provided link for full syntax on the ADDRESS function.
¹请注意,通常由错误控制的MATCH或VLOOKUP返回的零长度字符串"(例如""
)被视为文本值,而不是空白或空值.
¹ Note that a 'zero-length string' (e.g. ""
) like that typically returned by an error controlled MATCH or VLOOKUP is considered a text value, not a blank or null value.
这篇关于查找没有VBA的最后一个非空单元格ADDRESS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!