部落格

在 Excel 設定動態範圍:何時使用 OFFSET 或表格

讓加總結果與下拉清單隨新增列更新。透過範例了解 OFFSET 和 COUNTA、空白 ID 的影響,以及如何改用 Excel 表格。

新增一列時,醒目標示的參照範圍向下延伸。

在 Excel 商品清單新增一項,合計和下拉選項卻沒有改變。若參照仍是 A2:A10,第 11 列就不在範圍內。使用動態範圍讓 Excel 判斷清單的邊界,更新資料時就不必再改公式。

用 OFFSET 計算範圍的高度與寬度

如果資料連續,可計算欄標題與商品 ID 數量,作為 OFFSET 的高度與寬度。請在 Sheet1 建立下表,再把加總公式輸入另一張工作表。請照範例輸入日文欄標題,讓後續的結構化參照能直接運作。

儲存格 A 欄 B 欄 C 欄
第 1 列 商品ID(商品編號) 商品名(商品名稱) 数量(數量)
第 2 列 A01 筆 4
第 3 列 A02 筆記本 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

從 A1 開始,列與欄的位移皆為零。高度是 A 欄有資料的儲存格數,寬度是第 1 列的欄標題數。範例得到三列三欄的 A1:C3,合計為 10。在第 4 列加入 A03、尺與 2 後,範圍變成 A1:C4,合計為 12。這裡假設商品 ID 為文字,計數範圍不包含其他資料。Microsoft 的 OFFSET 函數說明

現在只清除 A3 的 ID。A 欄只有標題 A1 和含有 ID 的 A2、A4 會計入,共三格。因此 OFFSET 傳回由左上角開始的前三列 A1:C3。第 3 列的數量 6 仍計入,第 4 列的數量 2 落在範圍外,合計回到 10。

只有 A3 空白,OFFSET 參照 A1:C3,其中包含第 3 列的 6,第 4 列的 2 不在範圍內,合計為 10。
綠色框線表示 Windows 版 Excel 中 OFFSET 傳回的 A1:C3。

欄標題中間若有空白,後面的欄也會落在範圍外。COUNTA 會計入公式結果為空字串("")的儲存格。若在現有活頁簿採用此方法,應確保標題與 ID 連續,並檢查計數上限和空白儲存格。ID 已填寫時,數量欄空白不影響高度。Microsoft 的 COUNTA 函數說明

標題從第 2 列開始時

第 1 列若放報表名稱,可讓清單由 A2 開始。高度與寬度也從第 2 列計數,便不會納入報表名稱。將範例向下移一列後,標題與兩筆商品會落在 A2:C4,合計仍是 10。

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

用新增列時會自動延伸的表格

若要建立新清單,選取含標題的範圍,再按「插入」→「表格」。將表格命名為 Items,便可在表格外用下列公式加總數量欄。表格新增列時,加總的範圍也會擴大。Microsoft 的結構化參照說明

=SUM(Items[数量])

只加總有商品 ID 的列

將 A1:C4 轉成 Items 表格後,即使 A3 空白,上述公式仍會加上 4 + 6 + 2,傳回 12。只計算有商品 ID 的列時,用 SUMIF 指定 ID 欄不可空白。此例將第 2 與第 4 列的 4 + 2 相加,得到 6。Microsoft 的 SUMIF 函數說明

=SUMIF(Items[商品ID],"<>",Items[数量])

讓下拉式清單跟著表格更新

若要在另一張工作表設置商品名稱下拉式清單,在桌面版 Excel 中選擇「公式」→「名稱管理員」。建立以活頁簿為範圍的 ItemChoices 名稱,將「參照到」設為 =Items[商品名]。將輸入格的資料驗證設為「清單」,來源填入 =ItemChoices。標題不會列入,表格末尾新增列便會增加選項。請在新增時填上商品名稱,避免空白選項。Microsoft 的建立下拉式清單說明