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

问题描述

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

I have the following simple Excel spreadsheet:

      A        B        C        D         E       F       G      H
1    Jan      Feb      Mar      Apr       Jun     Jul     Aug    Sept
2   5.000    4.000    5.000    8.000     9.000   
3   $A$2:$H$2
4   

在 A3单元格中，我使用以下公式从第2行获取单元格引用:

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

A3 ="$"&LEFT(ADDRESS(1,COLUMN(A2),4),1+(COLUMN(A2)>26))&"$"&ROW(A2)&":$"&LEFT(ADDRESS(1,COLUMN(H2),4),1+(COLUMN(H2)>26))&"$"&ROW(H2)

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

All this works fine so far.

但是，我现在的目标是更动态地创建单元格引用.如您在我的示例中看到的，Jul, Aug, Sept尚无数字.因此，我还希望单元格引用不会比Jun(单元格E2 )更远.
我可以通过手动更改公式中的引用来做到这一点，但是我更喜欢一种能够自动检测最后填充的单元格并将该单元格用作最后一个单元格引用的解决方案.
在我的情况下，最后一个填充的单元格是E2，但是一旦7月结束，它将改变，然后最后一个填充的单元格是8月(F2)，依此类推...

However, my target ist now to make the cell reference more dynamically. As you can see in my example there is no number yet for Jul, Aug, Sept. Therefore, I also want that the cell reference is not going further then Jun (Cell E2).
I could do this by just manually change the reference in my formula but I would prefer a solution that automatically detects the last filled cell and uses that cell as the last cell reference.
In my case the last filled cell is E2 but it will change once July is over and then the last filled cell will be August (F2) and so on ...

您是否知道我该如何解决这个问题?

Do you have any idea how I could solve this issue?

推荐答案

尝试

=ADDRESS(2, COLUMN(), 1, 1)&":"&ADDRESS(2, MATCH(1E+99, 2:2), 1, 1)

对于最后一个非空白/非零数字的地址，

For the address of the last non-blank/non-zero number,

=ADDRESS(2, COLUMN(), 1, 1)&":"&ADDRESS(2, AGGREGATE(14, 7, COLUMN($A:$Z)/($A$2:$Z$2<>0), 1), 1, 1)

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