我有一个工作簿模板，其中第一个工作表将使用来自其他工作表的数据，而其他工作表将被常规数据库导出替换。
在一些情况下，汇总工作表上的信息将引用后续数据工作表作为数据透视表的范围。问题是不知道该范围内有多少行。当使用 A$:D$ 之类的范围作为数据透视表的源时，会出现一个“(空白)”项。可以使用筛选功能来抑制这种情况，但我担心如果添加新类别，这会使模板丢失一些行。
这个问题似乎在于范围不局限于工作表中实际包含任何数据的部分。
有没有办法让范围自动终止于其最后一行有数据的地方？
答案1
有多种方法可以实现动态范围：
在命名范围内使用OFFSETandCOUNTA
如果你使用=COUNTA($A:$A)，你将收到 A 列中的所有项目。请确保你选择一个始终填充的列，例如ID列。如果您的数据表从更下方的某个位置开始，您还必须考虑到这一点：=COUNTA($A:$A)-COUNTA($A$1:$A10)。
现在，您可以使用公式=OFFSET($A$1,0,0,COUNTA($A:$A),4)确定从 A1 开始、宽度为 4 列且包含所有行的范围。
使用此定义范围的技巧是将其作为命名范围输入！为此，请转到名称管理器（在公式选项卡中）并插入新名称（例如数据）。在这里，不要提供对固定范围的引用，而是复制上面的公式。（注意：如果您想编辑公式，请不要忘记在使用箭头键之前按下，否则公式将被单元格引用弄乱）。确保对所有单元格引用都F2使用。$
您现在可以使用名称数据作为数据透视表的来源。如果您想验证是否使用了完整范围，只需在公式栏左侧的名称框中输入名称（您需要在那里实际输入） - 或者只需单击名称管理器中公式旁边的小图标。
使用INDEX和COUNTA
的小缺点OFFSET是它是易变的，即每次计算时都会计算它，即使引用的数据根本没有改变。如果您在公式中频繁使用动态名称，这可能会减慢您的模型速度。
为了避免这种情况，请OFFSET按INDEX以下方式替换：
=INDEX($A:$A,COUNTA($A:$A)):INDEX($D:D$,COUNTA($A:$A))
这将完成相同的工作，但不会改变。此外，如果您在 D 列之前插入新列，此公式将进行调整，而在前一个选项中，您必须将 4 替换为 5（或使用COUNTA($1:$1)）。
使用 Excel 表格
比命名范围更优雅的方式是 Excel 表格。如果您导入数据，Excel 通常会将导入的数据存储在表格中（您可以通过交替的行颜色和选择后在功能区中可用的表格选项卡来识别它）。或者，您可以手动插入表格（插入选项卡 - 插入表格）。
为表格提供一个可读名称（在表格选项卡中的名称字段中） - 然后只需使用此名称作为数据透视表的来源！如果数据发生变化，表格以及数据透视表的相关范围将自动调整。
但是，请注意，任何更新后您仍然需要刷新数据透视表！