☰

Excel：新增列時如何自動更新求和公式，同時保持相同的範圍

tag-icon • tag-icon microsoft-excel worksheet-function
Excel：新增列時如何自動更新求和公式，同時保持相同的範圍

我目前在 Excel 中遇到問題。我正在嘗試找出每年的成本總和，並且此 Excel 表將隨著月份的推移而更新。問題是，我可以使用 sum 函數輕鬆地對年度成本求和，但我希望當新列（月）和成本在該列中更新時，我仍然想計算年度成本。

例如，現在的年度成本是 2022 年 6 月到 2021 年 7 月的總和。包括這樣一個事實：當新增列時，我必須從公式中刪除開始列。

非常感謝您的幫助，謝謝。

答案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))將建立一個包含單元格位址的字串二範圍中最後一個單元格（帶有值）左側的列。（在第二個 OFFSET() 中使其找到最後一個值-3）-1

CELL(...) & ":" & CELL(...)...這將使透過建立與該行的最後 3 個單元格相對應的單元格範圍形成的文字 。

INDIRECT()然後“解釋”該單元格範圍文本，並將其交給SUM(...)，然後它可以進行計算。

注意：要獲得完整的行範圍；使用例如COUNTA(2:2)第二行。

已在 LibreOffice 中驗證，應適用於任何 SpreadSheet 版本。
許多 INDIRECT() 函數呼叫可能會使電子表格變慢

相關內容