欢迎访问江门电脑培训-短期零基础速成学电脑!
语言选择: 繁體中文

办公软件教程

首页>专业知识>办公软件教程>办公软件Excel绝对 定义动态引用的名称

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

定义动态引用的名称

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

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

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

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

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

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

公式为:=OFFSET(销售明细表!$A$,,,COUNTA(销售明细表!$A:$A),COUNTA(销售明细表!$1:$1))

image.png

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

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

image.png

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

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

image.png

2

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

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

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

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

image.png

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

image.png

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

创建自定义函数


在工作中,有时会使用到非常复杂的嵌套函数,这类函数很适合通过定义名称,借助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)


在线留言


用手机扫描二维码关闭
二维码