在 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)))
让 Excel 表格随新增行扩展
新建清单时,选中含表头的区域,点击“插入”→“表格”。将表格命名为 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 的下拉列表创建说明