获取范围内最后一个非空单元格的列号(如果值不是唯一的) [英] Get the column number of the last non-empty cell within a range (if values are not unique)

问题描述

我有以下Excel电子表格:

I have the following Excel-Spreadsheet:

     A  B  C    D       E       F       G      H     I      J     
1
2
3
4              200     150     80     300                   4
5              200     150     80     150                   2
6
7

在范围J4:J5中，我具有以下公式:

In Range J4:J5 I have the following formulas:

J4 = MATCH(LOOKUP(2,1/(D4:I4<>0),D4:I4),D4:I4,0)
J5 = MATCH(LOOKUP(2,1/(D4:I4<>0),D4:I4),D4:I4,0)

使用此公式，我想确定范围内的最后一个非空单元格，并获取其列号.所有这一切在Cell J4中都可以正常工作.

With this formula I want to identify the last non-empty cell within the range and get the the column number of it back. All this works perfectly in Cell J4.

但是，在Cell J5中，我得到的是 2号，而不是 4号.据我所知，这是因为范围D5:I5 中的值不是唯一的(150次出现两次).

However, in Cell J5 I get back number 2 instead of number 4. As far as I can see the reason for this is that in Range D5:I5 the values are not unique (150 appears two times).

无论值是否唯一，我都需要更改公式以始终获取最后一个非空单元格的列号吗?

What do I need to change in my formula to always get the column number of the last non-empty cell no matter if the values are unique or not?

来自

The answer from this question does not help because my range is not starting in Column A.
The range can be anywhere on the sheet.

推荐答案

或者，您可以如下调整现有的LOOKUP函数:

Alternatively, you can adjust your existing LOOKUP function as follows:

=LOOKUP(2,1/(D4:I4<>0),COLUMN(D4:I4)-MIN(COLUMN(D4:I4))+1)

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