☰

取得引用變數範圍的公式

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 替換為 a ，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()用於找出剩餘行數和列數的邏輯，作為讀者的練習

相關內容