☰

获取公式来引用变量范围

tag-icon • tag-icon microsoft-excel
获取公式来引用变量范围

因此，我试图让一个方程式在可变数量的单元格中起作用。所以我有一张包含当前成本和预计成本的表格。我希望预测数字仅基于当前成本。这由月份顶部的 ACT 表示。我最初的 sumifs/countifs 公式因产生循环引用错误而被拒绝，所以我想知道是否有一种方法可以让方程式根据另一个单元格动态更新。

我倾向于避免使用动态范围，因为我们讨论的是需要更新的约 400 行，而且据我了解，需要一次添加一个范围。

代表我希望公式执行的操作的一个例子：=Sum(A1:A(1+B1))，其中范围随着 B1 的增加而增加。

样本数据：

    Act   Act   Act   Proj   Proj  
A:   1     2     3      2      2
B:   1     3     5      3      3
C:   4     5     6      5      5
  • 投影 A：2
  • 投影B：3
  • 投影C：5

然后，当我添加第 4 个月的实际数字并将 Proj 更改为 Act 时，投影 A、投影 B 等计算的范围从 3 变为 4，而无需我进行任何进一步的输入。

答案1

您可以使用OFFSET()创建对 X-by-Y 列范围的引用，例如：

=OFFSET(A1,0,0,10,10)将创建一个引用 的 10×10 引用A1:J10。

您可以用 替换上述公式中的硬编码 10，以COUNTIF()推断范围应该参考多少行和多少列。

为了避免循环错误，您可以尝试使用此公式定义命名范围（AltMMD），并在使用公式的任何地方使用该命名范围。

或者，您可以使用不带宽度和高度参数（即上述公式中的 10）的 X-by-Y 引用构建文本字符串，而是查找范围内的第一个和最后一个单元格。然后将它们包装在一个将返回其地址的调用OFFSET()中。CELL("address",...)（注意：如果您的起点是 $A$1，那么您只需要找到结束单元格）。

将它们连接起来:，并INDIRECT()在需要使用的地方用 包裹起来。总之，假设硬编码的开始位置为$A$1：

=INDIRECT("Sheet1!$A$1:"&CELL("address",OFFSET(Sheet1!$A$1,COUNTIF(..x-rows..),COUNTIF(..y-cols..))

COUNTIF()找出剩余行数和列数的逻辑，作为读者的练习

相关内容