☰

Excel：如何在添加列时自动更新求和公式，同时保持相同的范围

tag-icon • tag-icon microsoft-excel worksheet-function
Excel：如何在添加列时自动更新求和公式，同时保持相同的范围

我目前在 Excel 中遇到了一个问题。我试图计算年度成本总和，并且此 Excel 表将随着月份的推移而更新。问题是，我可以使用 sum 函数轻松地计算年度成本总和，但我希望当该列中更新新列（月份）和成本时，我仍然要计算年度成本。

因此，例如，现在的年度成本将是 2022 年 6 月至 2021 年 7 月的总和。下个月，它将是 2022 年 6 月至 2022 年 8 月。我已经看到了 sum(indirect) 函数，但这似乎不包括这样一个事实，即当添加新列时，我必须从公式中删除开始列。

非常感谢您的帮助，谢谢。

答案1

通常，当我希望总和包含稍后插入的列时，我会在要求和的各个列和总和列之间留出一个空列，并将此空列包含在 SUM 范围内。然后，当我在空列左侧插入一列时，SUM 函数将包含此新列中的单元格，因为它位于范围内。

但是，一种更加通用的方法是使用如下公式，这种方法也消除了对这个空列的需要：

=SUM(A1:OFFSET(K1,0,-1))

此示例是在单元格 K1 中输入从 A1 到 J1 的范围的总和。该OFFSET函数对单元格引用起作用，而不是对单元格中的值起作用。 OFFSET(cell, y, x)表示下方的单元y格行和x右侧的列cell。

如果您希望总和范围的起始位置向前滚动，也可以使用 OFFSET 函数。例如

=SUM(OFFSET(K1,0,-6):OFFSET(K1,0,-1))

会返回最近的六列。我不明白你到底想做什么。

答案2

像这样的公式：

=SUM(INDIRECT(CELL("地址",OFFSET(A2,0,COUNTA(2A:ZZ2)-3))&":"&CELL("地址",OFFSET(A2,0,COUNTA(A2:ZZ2)-1))))

将首先计算 A2:ZZ2 中包含值的单元格数量
（由于COUNTA(A2:ZZ2)这也强制第一个 OFFSET() 参数为 A2

CELL("address",OFFSET(A2,0,COUNTA(A2:ZZ2)-3))将创建一个包含单元格地址的字符串二范围内最后一个单元格（含有值）左侧的列。（第二个-3OFFSET -1() 使其找到最后一个值）

CELL(...) & ":" & CELL(...)...这将使创建与该行最后 3 个单元格相对应的单元格范围所形成的文本 。

INDIRECT()然后“解释”该单元格范围文本，并将其交给SUM(...)，然后进行计算。

注意：要获得完整的行范围；例如使用COUNTA(2:2)第二行。

已在 LibreOffice 中验证，适用于任何版本的 SpreadSheet。
许多 INDIRECT() 函数调用可能会导致电子表格运行缓慢

相关内容