显示最后一个非空单元格的地址(如果值不唯一) [英] Display the address of the last non-empty cell (if values are not unique)
问题描述
我有以下 Excel 电子表格:
I have the following Excel spreadsheet:
A B C D E F
1 | | Jan. Feb. March April May
2 | Profit | 100 200 200
3 | Cell Reference last non-empty cell | ??
在第 2 行,您可以找到每个月的利润.在单元格B3中,我想显示第2行中最后一个非空单元格的地址.为此,我尝试了以下公式:
In Row 2 you can find the profit for each month. In Cell B3 I want to to display the address of the last non-empty cell in Row 2. For this I tried the following formula:
=ADDRESS(ROW(B2),MATCH((IFERROR(LOOKUP(2,1/($A2:$F2<>0),$A2:$F2),0)),$A2:$F2,0),4,1)
如果第 2 行中的所有数字都是唯一,则此公式完美适用.但是,在我上面的示例中,您可以看到 Februay
的利润等于 March
的利润;因此,上面的公式给了我地址 C2
而不是 D2
.
This formula works perfectly if all the numbers in Row 2 are unique. However, in my example above you can see that the profit in Februay
equals the profit in March
; therefore, the formula above gives me the address C2
instead of D2
.
我必须在公式中更改什么才能始终获取最后一个非空单元格的地址,无论它是否唯一?
What do I have to change in my formula to always get the address of the last non-empty cell no matter if it is unique or not?
推荐答案
使用:
=ADDRESS(ROW(B2),MATCH(1E+99,$A2:$F2),4,1)
这将返回具有该范围内数字的最后一个单元格.
Which will return the last cell with a number in that range.
对于文本和数字:
=ADDRESS(ROW(B2),MAX(IFERROR(MATCH(1E+99,$A2:$F2),0),IFERROR(MATCH("zzz",$A2:$F2),0)),4,1)
这篇关于显示最后一个非空单元格的地址(如果值不唯一)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!