显示列中最后一个非空单元格的地址(无论列是否包含值或文本) [英] Display the address of the last non-empty cell in a column (no matter if column contains values or text)

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

         A              B                      C
1     Product 01      Brand
2     Product 02      Brand A             Cell Reference last non-empty cell
3     Product 03      Brand A                 ??
4     Product 04      Brand B   
5     Product 05      Brand B
6     Product 06      Brand B
7

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

In Column B you can find the Brand for each Product. 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 go with the solution from this question:

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

但是，此公式仅在 B列由值组成时有效.

However, this formula only works if Column B consists of values.

我必须更改公式以始终获取 B列中最后一个非空单元格的地址，而不管它是用值还是文本填充?

What do I have to change in the formula to always get the address of the last non-empty cell in Column B no matter if it's filled with values or text?

推荐答案

那(根据我的评论)呢?

So what about (as per my comment):

="B"&COUNTA(B:B)

假设您的数据集没有空白.

Assuming you have a dataset without gaps.

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