获取范围内最后一个非空单元格的列号(如果值不是唯一的) [英] 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屋!