办公软件Excel绝对 定义动态引用的名称

定义动态引用的名称

动态引用是相对静态而言的，一个静态的区域引用，如 $A$1:$A$100 是始终不变的。动态引用则可以随着数据的增加或减少，自动扩大或是缩小引用区域。

使用函数公式定义动态引用的名称

借助引用类函数来定义名称，可以根据数据区域变化，对引用区域进行实时的动态引用。配合数据透视表或图表，能够实现动态实时分析的目的。在复杂的数组公式中，结合动态引用的名称，还可以减少公式运算量，提高公式运行效率。

示例1-1 创建动态的数据透视表

通常情况下，用户创建了数据透视表之后，如果数据源中增加了新的行或列，即使刷新数据透视表，新增的数据仍然不能在数据透视表中呈现。可以为数据源定义名称或使用插入“表格”功能获得动态的数据源，从而生成动态的数据透视表。

在图 1-1 所示的销售明细表中，首先定义名称“Data”。

公式为：=OFFSET（销售明细表！＄A＄,,,COUNTA（销售明细表！＄A：＄A）,COUNTA（销售明细表！＄1：＄1））

接下来使用定义的名称作为数据源，生成数据透视表。

步 骤 1 单击数据明细表中的任意单元格，如 A5 单元格，在【插入】选项卡下单击【数据透视表】按钮，弹出【创建数据透视表】对话框。在【表 / 区域】编辑框中输入已经定义好的名称“data”，单击【确定】按钮，如图1-2 所示。

步 骤 2 此时自动创建一个包含透视表的工作表“Sheet1”。在【数据透视表字段列表】中，依次将“销售人员”字段拖动到行区域，将“产品规格”字段拖动到列区域，将“销售数量”字段拖动到值区域，完成透视表布局设置。

在销售明细表中增加记录后，右击数据透视表，在快捷菜单中选择【刷新】命令，数据透视表即可自动添加新增加的数据汇总记录，如图 1-3 所示。

2

利用“表”区域动态引用Excel 的“表格”功能除支持自动扩展、汇总行等功能以外，还支持结构化引用。当单元格区域创建为“表格”后，Excel 会自动定义“表 1”样式的名称，并允许修改命名。

示例1-2 利用“表”区域动态引用

如图 1-4 所示，单击数据区域任意单元格，如 A2，依次单击【插入】→【表格】按钮，弹出【创建表】对话框。保留默认设置，单击【确定】按钮，将普通数据表转换为“表格”。

插入“表格”后，Excel 自动创建“表 + 数字”的名称。

如图 1-5 所示，按下组合键弹出【名称管理器】对话框，单击名称“表 1”，此时【删除】按钮和引用位置都呈灰色无法修改状态，随着数据的增加，名称“表 1”的引用范围会自动变化。

用户可以使用此名称来创建数据透视表或是图表，实现动态引用数据的目的。如果在公式中引用了“表格”中的一行或一列数据，数据源增加后，公式的引用范围也会自动扩展。

创建自定义函数

在工作中，有时会使用到非常复杂的嵌套函数，这类函数很适合通过定义名称，借助Microsoft365专属Excel中的LAMBDA函数

LAMBDA 函数能够让用户在 Excel 中创建自定义的函数，从而完成一些较为复杂的计算。其语法如下：

=LAMBDA([parameter1,parameter2, ...,] 计算 )

其中的 parameter1、parameter2,……参数是要传递给函数的值，可以是单元格的引用、字符串或是数字，最多可以输入 253 个参数。“计算”部分则是要执行并作为函数结果返回的公式。其语法相当于：

=LAMBDA( 定义的参数 1, 定义的参数 2,…执行的计算方式 )

例如，以下公式定义了两个参数 x 和 y，然后执行相加运算，返回两个参数的和。

=LAMBDA(x,y,x+y)

单击【公式】选项卡下的【定义名称】按钮，弹出【新建名称】对话框。在【名称】文本框中输入“Mysum”作为自定义函数的名称。在【引用位置】编辑框中使用以上公式，如图 1-6 所示。

在工作表的 C1 单元格中输入以下公式，即可返回 A1 和 B1 两个单元格的合计值，如图 1-7 所示。

=Mysum(A1,B1)