使用Office 2007根据最后填充的单元格动态更改单元格引用 [英] Change cell reference dynamically based on last filled cell using Office 2007

问题描述

我有以下简单的Excel电子表格:

I have the following simple Excel spreadsheet:

    A      B        C       D        E        F         G       H       I         J
1                  Jan      Feb      Mar      Apr       May     Jun     Jul      Aug
2                 5.000    4.000    5.000    8.000     9.000     0
3                
4                $C$2:$J$2
5
6

在单元格C4 中，我使用以下公式从Row 2获取单元格引用:

In Cell C4 I use the following formula to get the cell references from Row 2:

C4 = "$"&LEFT(ADRESS(1,COLUMN(C2),4),1+(COLUMN(C2)>26))&"$"&ROW(C2)&":$"&LEFT(ADRESS(1,COLUMN(J2),4),1+(COLUMN(J2)>26))&"$"&ROW(J2)

到目前为止，所有这些都工作正常.

All this works fine so far.

但是，我现在的目标是使单元格引用更加动态.如您在我的示例中看到的，对于Jun, Jul, Aug，有一个0或no number.因此，我也希望单元格引用不会比May(Cell G2)更远.一旦六月的number > 0出现，单元格引用应自动转到Cell H2，依此类推...

However, my target ist now to make the cell reference more dynamically. As you can see in my example there is either a 0 or no number for Jun, Jul, Aug. Therefore, I also want that the cell reference is not going further then May (Cell G2). Once there is a number > 0 for June the cell reference should automatically go until Cell H2 and so on ...

我尝试使用答案此处的公式.但是，由于我只有Office 2007可用，因此无法使用包含AGGREGAT函数的公式，如链接中的答案一样.

I tried to go with the formula from the answer here. However, since I only have Office 2007 availalbe I cannot use a formula that contains the AGGREGAT function as in the answer in the link.

Office 2007是否还有其他解决方案?

Is there any other solution for Office 2007?

推荐答案

Excel 2007(美国英语版)没有LINKS或ADRESS函数.但是该公式的英文版将返回第2行中最后一个包含大于0值的地址:

Excel 2007 (US English version) does not have the LINKS or ADRESS functions. But an English version of the formula which will return the last address in row 2 that contains a value greater than 0:

= ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))

在您的示例中，公式返回$G$2.

In your example that formula returns $G$2.

检查HELP的LOOKUP函数，以解释其工作原理(lookup_value大于要测试的数组中的任何项目时).

Examine HELP for the LOOKUP function for an explanation of how this works (when lookup_value is greater than any of the items in the array being tested).

我不确定您是要检测还是仅对第一个地址进行硬编码.对于硬编码，仅:

I'm not sure if you want to detect or merely hard code the first address. To hard code, merely:

= "$C$2:" & ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))

或以下的某些变体

= ADDRESS(2,3) & ":" & ADDRESS(2,LOOKUP(2,1/($2:$2>0),COLUMN($2:$2)))

这篇关于使用Office 2007根据最后填充的单元格动态更改单元格引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！