如何引用变量范围

I want to refer a range of data in my excel sheet that is of variable range. Means this month data have 80 rows but next month it could be of 100 rows. So i just wanted a method to refer a range for variable range. So that i can use that method in following formula:-

我想在excel表中引用一系列可变范围的数据。意味着本月数据有80行,但下个月可能有100行。所以我只想要一个方法来引用变量范围的范围。所以我可以在下面的公式中使用该方法: -

=SUMPRODUCT(Allocation_Updt!$J$2:$J$83*((RIGHT(Allocation_Updt!$F$2:$F$83,6)+0)=$E62))/100

Here 83 is the last row of the data sheet. but it can be changed next time. Setting it to 10000(Almost max limit of my data) will give me error.

这里83是数据表的最后一行。但下次可以改变。将其设置为10000(我的数据的几乎最大限制)将给我错误。

2 个解决方案

#1

Use,

match(1e99, Allocation_Updt!$J:$J)

... to find the row number of the last number or date in a column. With the last value in J83 all of the following three range references are the same thing

...查找列中最后一个数字或日期的行号。对于J83中的最后一个值,以下三个范围引用都是相同的

Allocation_Updt!$J$2:$J$83
Allocation_Updt!$J$2:index(Allocation_Updt!J:J, match(1e99, Allocation_Updt!$J:$J))
index(Allocation_Updt!J:J, 2)):index(Allocation_Updt!J:J, match(1e99, Allocation_Updt!$J:$J))

So your SUMPRODUCT function can be dynamically limited to exactly what is needed with,

因此,您的SUMPRODUCT功能可以动态地限制到所需的功能,

=SUMPRODUCT(Allocation_Updt!$J$2:index(Allocation_Updt!$J:$J, match(1e99, Allocation_Updt!$J:$J))*((RIGHT(Allocation_Updt!$F$2:index(Allocation_Updt!$F:$F, match(1e99, Allocation_Updt!$J:$J)),6)+0)=$E62))/100

Note that the last row number in column J is used to get the last valid entry in both column F and column J.

请注意,列J中的最后一个行号用于获取F列和J列中的最后一个有效条目。

Given the persnickety nature of SUMPRODUCT, I might perform some tests with,

考虑到SUMPRODUCT的顽固性,我可能会进行一些测试,

=sumifs(Allocation_Updt!$J:$J, Allocation_Updt!$F:$F, "*"&$E62)/100

That is not specifically a 'right-most 6 character match'; it is an 'ends-with-E62' match. Some testing on your own data will quickly prove whether this is a viable alternative. It is more efficient, more forgiving and you can use full column references without penalty.

这不是一个'最右边的6个角色';这是'与E62结束'的比赛。对您自己的数据进行一些测试将很快证明这是否是一个可行的选择。它更有效,更宽容,您可以使用完整列引用而不会受到惩罚。

#2

Try converting the range of data to a table. It will automatically apply a name to each column. These column names can be used to refer to the data in the column, and that range of data will be dynamic going forward.

尝试将数据范围转换为表格。它会自动为每列应用名称。这些列名称可用于引用列中的数据,并且该范围的数据将是动态的。

#1