在 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。
欄標題中間若有空白,後面的欄也會落在範圍外。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 的建立下拉式清單說明