显示列中最后一个非空单元格的地址(如果值不是唯一的) [英] Display the address of the last non-empty cell in a column (if values are not unique)

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

       A          B                C
1     Month     Profit      
2     Jan.        100             Cell Reference last non-empty cell
3     Feb.        200             ??
4     Mar.        300
5     Apr.        300
6
7

在 B列中，您可以找到每个月的利润.在 Cell C3 中，我想在 B列中显示最后一个非空单元格的地址.在这种情况下，它将是 B5 .为此，我尝试从

In Column B you can find the profit for each month. In Cell C3 I want to to display the address of the last non-empty cell in Column B. In this case it would be B5. For this I tried to modify the formular from this question:

=ADDRESS(COLUMN(B2),MAX(IFERROR(MATCH(1E+99,$B2:$B5),0),IFERROR(MATCH("zzz",$B2:$B5),0)),4,1)

但是，我无法使其正常工作.

However, I could not make it work.

我必须更改公式以始终获取 B列中最后一个非空单元格的地址，无论其是否唯一?

What do I have to change in my formula to always get the address of the last non-empty cell in Column B no matter if it is unique or not?

推荐答案

使用:

="B"&MATCH(1E+99,B:B)

这篇关于显示列中最后一个非空单元格的地址(如果值不是唯一的)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！