在Excel中创建和维护各种财务和商业模型是一项挑战，尤其是当涉及到数据源变化时。本文将探讨如何通过定义动态数据范围来解决这一问题，使得图表和数据透视表能够自动反映数据的变化。
新成员加入团队后，虽然在短时间内创建了两个新模型，界面整洁且运行良好，但随着产品线的扩展，模型很快就出现了问题。这是因为模型建立在静态数据源上，一旦底层数据发生变化，就需要在多个地方更新数据源，这不仅耗时，而且容易出错。
动态数据范围可以自动适应数据大小和形状的变化，减少手动更新的需求，降低出错几率。本文将分享几种在Excel中定义动态范围的简单方法。
将以一个简单的数据集为例，创建一个按性别分类的销售额总和的数据透视表，以及一个以员工代码为类别轴、销售额为值轴的柱状图。希望数据集中的任何变化都能自动反映在图表和数据透视表上。
创建动态范围的方法主要分为两大类：使用Excel表格和使用Excel函数（如INDIRECT/OFFSET/INDEX）。
这可能是最简单的方法。Excel表格允许添加或删除行或列，同时自动应用格式、公式和过滤器，从而使模型具有动态特性。
选择要转换为Excel表格的数据范围转到“插入”选项卡，选择“表格”。Excel会询问表格是否有标题行，如果有，请勾选并点击确定。创建图表或数据透视表时，它们将自动随着行/列的添加或删除而更新。
本文将介绍使用INDEX函数创建动态范围的方法。将在后续文章中探讨使用其他两个函数创建动态范围的方法。
INDIRECT函数用于间接引用工作表中的单元格。简而言之，这个函数帮助将一个单元格的地址放到另一个单元格，并通过对另一个单元格的引用来获取一个单元格的数据。
例如，如果单元格B2的值为“A1”，并且在单元格C1中输入了=INDIRECT(B2)，则该函数将在C1中返回A1的值。
INDIRECT函数的语法为：INDIRECT(Ref_Text [, a1])，其中Ref_Text是一个有效的单元格引用、范围或已定义的范围名称，必须以字符串格式提供。a1是一个可选参数，默认值为TRUE，它定义了Ref_text参数中包含的单元格引用样式。
以下是INDIRECT函数的几个用例：
=INDIRECT(B2)：如果B2的值为“A1”，则此函数将在C2中检索A1的值。假设想要为范围A1:C6定义一个名称，但每当有行的增加或删除时，名称范围必须自动引用修改后的数据范围。
转到“公式”选项卡，选择“定义名称”。给范围命名，例如“Range1”，并在“引用”框中写下动态范围的公式。例如：=INDIRECT("Sheet2!$A$1:$C$"&COUNTA(Sheet2!$C:$C))，这将自动找到范围的最后一行的引用。
定义动态范围名称（如上所述）转到“插入”选项卡，选择“数据透视表”命令。选择位置，然后点击确定。现在选择数据透视表的维度和值。现在，如果在给定范围内添加或删除行，数据透视表的数据范围将自动更改。仍然需要刷新数据透视表。
为每个系列创建动态范围名称。选择当前数据范围并创建柱状图。转到“选择数据”（右键单击图表并从迷选项中选择），然后选择值系列并点击编辑。现在点击类别的编辑按钮，并用定义的类别范围名称更改范围，然后按确定。