为数据区域定义名称的最大好处是:可以使用名称代替单元格区域以简化公式;另外,在大型数据库中,通过定义名称还可以方便对数据快速定位。因为将数据区域定义为名称后,只要使用这个名称就表示引用了这个单元区域。
关键点: 定义名称的作用
操作要点: 定义名称会带来哪些方便
应用场景: 在Excel中为一些数据区域定义名称,可以起到简化公式的作用。
下面来具体介绍使用名称定义可以为数据处理带来哪些方便。
① 在公式中可以直接使用名称代替这个单元格区域,名称在公式中不需要加双引号。如公式:=SUM(销售额)中的 “销售额” 就是一个定义好的名称,如图3-1所示。
图 3-1
② 尤其是跨表引用单元格计算时,先定义名称则不必使用 “工作表名!单元格区域” 这种引用方式,有效避免公式设置错误。
③ 定义名称后可以在编辑状态中实现快速输入序列。例如,将如图3-2所示的 “姓名” 列定义为名称,在新表格中选中要输入姓名的A1:A10单元格区域,在编辑栏中输入 “=姓名” ,并按Ctrl+Shift+Enter快捷键后,可以快速返回这个姓名序列,如图3-3所示。
图 3-2
图 3-3
关键点: 定义名称的操作方法
操作要点: ① “公式” → “定义的名称” 组→ “定义名称” 功能按钮
②名称框直接定义名称
应用场景: 为了简化函数公式中对单元格区域的引用,可以将需要引用的单元格区域定义为名称。
首先需要了解一下定义名称的规则:
√名称第一个字符必须是字母、汉字、下画线或反斜杠(\),其他字符可以是字母、汉字、半角句号或下画线。
√名称不能与单元格名称(如A1,B2等)相同。
√定义名称时,不能用空格符来分隔名称,可以使用 “.” 或下画线,如A.B或A_B。
√名称不能超过255个字符,字母不区分大小写。
√同一个工作簿中定义的名称不能相同。
√不能把单独的字母r或c作为名称,因为这会被认为是行row或column的简写。
定义名称可以打开 “新建名称” 对话框,设置名称和引用位置等,即可创建名称。在下面的工作表中要将 “店铺” 列定义名称。
① 选中要定义为名称的单元格区域,即A2:A20。在 “公式” 选项卡的 “定义的名称” 组中单击 “定义名称” 按钮(见图3-4),打开 “新建名称” 对话框。
图 3-4
② 在 “名称” 框中输入定义的名称,如 “店铺” ,如图3-5所示。单击 “确定” 按钮,即可完成名称的定义。
③ 按照相同的方法为其他单元格区域定义名称即可。
图 3-5
在上面的实例中要将 “店铺” 列定义为名称,除了可以使用“定义名称”功能来定义,还可以选中要命名的单元格区域后,直接在名称框中输入名称来创建。
选中要定义为名称的单元格区域,在名称框中输入需要定义的名称,按Enter键即可定义名称,如图3-6所示。
图 3-6
如果需要将连续单元格区域数据快速定义为名称,可以一次性创建。
如图3-7所示中,一次性选中多列,使用 “根据所选内容创建” 功能按钮,则可以一次性定义多个以列标识为名称的名称,如图3-8所示。
图 3-7
图 3-8
关键点: 修改或删除表格中定义的名称
操作要点: “公式” → “定义的名称” 组→ “名称管理器” 功能按钮
应用场景: 在创建了名称之后,如果想重新修改其名称或引用位置,可以打开 “名称管理器” 进行编辑。另外,如果有不再需要使用的名称,也可以将其删除。
本例中需要将指定单元格区域定义为 “在售产品” ,将其引用位置由B2:B18更改为B2:B20单元格区域。
① 在 “公式” 选项卡的 “定义的名称” 组中单击 “名称管理器” 按钮,如图3-9所示,打开 “名称管理器” 对话框。
图 3-9
② 选中需要修改的名称,可以看到设置好的引用位置是=Sheet1!$B$2:$B$18,如图3-10所示。
③ 继续在 “引用位置” 文本框中将其修改为=Sheet1!$B$2:$B$20即可,如图3-11所示。
图 3-10
图 3-11
在本例中需要删除名称 “店铺” ,具体操作如下。
① 首先打开 “名称管理器” 对话框。选中要编辑的名称 “店铺” ,单击 “删除” 按钮,如图3-12所示,弹出Microsoft Excel对话框。
图 3-12
② 单击 “确定” 按钮(见图3-13),即可将其删除。
图 3-13
要想查看这个工作簿中有没有定义名称或定义了哪些名称,可打开 “名称管理器” 对话框进行查看。
关键点: 公式应用名称的方法
操作要点: ①定义名称
② “公式” → “定义的名称” 组→ “用于公式” 功能按钮
应用场景: 在公式中使用定义的名称,即代表定义为该名称的单元格区域将参与计算,这样输入公式既方便又简洁。下面介绍将名称应用于公式计算的操作步骤。
本例的工作表中统计了公司第一季度各销售员的每个月的销售业绩,需要对销售成绩进行分析,计算每个月的平均销售额,最高销售额和最低销售额。每个月的销售数据已经定义为名称(打开 “名称管理器” 可查看到,如图3-14所示),下面以计算1月最高销售额为例,介绍如何在公式中应用名称。
图 3-14
① 将光标定位在单元格C9中,输入: =MAX( ,如图3-15所示。
图 3-15
② 在 “公式” 选项卡的 “定义的名称” 组中单击 “用于公式” 下拉按钮,在下拉菜单中选择要使用的名称,即 “_1月” ,如图3-16所示。
③ 接着输入公式的后面部分(即右括号),按Enter键,即得出1月的最高销售额,如图3-17所示。
图 3-16
图 3-17
① 名称名也可以直接手工输入。如直接输入公式:=MAX(_1月)即可。
② 使用名称时要注意,常规方法定义的名称是一个不变的单元格区域,即类似于绝对引用的一个单元格区域,因此公式中要使用名称时,首先要确保公式中这部分单元格区域不改变。
我们可以将需要作为参数的单元格区域定义为名称,再设置公式求解。
如图3-18所示,一次性定义表格各个列标识的名称,然后在公式中分别引用这些名称,就可以快速计算各部门的平均工资,如图3-19所示。
图 3-18
图 3-19
关键点: 学习将公式定义名称的方法及应用场合
操作要点: ①新建名称设置引用位置为公式
②公式中使用IF函数判断销售额的提成率
应用场景: 公式是可以定义为名称的,公式定义为名称可以简化原来更为复杂的公式。例如,嵌套公式中的一部分可以先定义为名称。
本例中需要根据不同的销售额计算提成金额。公司规定不同的销售额对应的提成比例各不相同。要求当总销售金额小于或等于50 000元时,给8%;当总销售金额50 000~100 000元时,给10%;当总销售金额大于100 000元时,给15%。
① 在 “公式” 选项卡的 “定义的名称” 组中单击 “定义名称” 按钮,打开 “新建名称” 对话框。
② 输入名称为 “提成率” ,并设置 “引用位置” 的公式为:=IF(C2<=50000,0.08,IF(!C2<=100000,0.1,0.15)),如图3-20所示。单击 “确定” 按钮即可完成 “提成率” 名称的定义。
图 3-20
这里的公式用来判断每位员工的总销售额是否小于等于50 000元,如果是,则给予奖金提成率为0.08;在50 000~100 000元,则给予提成率为0.1,在100 000元以上的提成率为0.15。这里使用了IF函数进行判断。并将这一部分的判断定义为名称。
③ 将光标定位在单元格D2中输入 “=” ,接着在 “公式” 选项卡的 “定义的名称” 组中单击 “用于公式” 下拉按钮,在打开的下拉菜单中选择 “提成率” 命令,如图3-21所示。
图 3-21
④ 此时可以看到公式为 “=提成率” ,如图3-22所示。
图 3-22
⑤ 继续输入公式剩余部分:=提成率*C2,按Enter键,即可根据C2单元格的总销售额计算出第一位员工的提成金额,如图3-23所示。
图 3-23
⑥ 向下填充D2单元格的公式即可实现根据C列的总销售额批量计算出其他员工各自对应的提成金额,如图3-24所示。
图 3-24
关键点: 了解如何在表格中创建动态名称实现动态公式计算
操作要点: ①按Ctrl+L快捷键创建表
②定义名称“各月销售额”
应用场景: 使用Excel的列表功能可以实现当数据区域中的数据增加或减少时,列表区域会自动扩展或缩小,因此结合这项功能可以创建动态名称,从而实现使用这个名称时,只要有数据源的增减,名称的引用区域也自动发生变化。
本例工作表中统计了公司各系列产品每月的销售总额。需要创建动态名称,以方便当数据增加或减少时,列表区域也做相应的扩展或减少,这样当引用名称进行数据计算时就能实现自动更新。
① 在 “公式” 选项卡的 “定义的名称” 组中单击 “定义名称” 按钮,打开 “新建名称” 对话框,设置名称为 “各月销售额” ,设置引用位置为 “=销售统计!$B$3:$D$5” ,如图3-25所示。
图 3-25
② 然后选中A2:D5单元格区域,按下Ctrl+L快捷键打开 “创建表” 对话框,选中 “表包含标题” 复选框,如图3-26所示。
图 3-26
③ 单击 “确定” 按钮,即可创建列表区域,如图3-27所示。
图 3-27
④ 将光标定位在单元格F3中,输入公式: =SUM(各月销售额) ,按Enter键,即可得到三个月的总销售额,如图3-28所示。
图 3-28
⑤ 当第6行和第7行有新数据输入时,如图3-29所示,可以看到自动扩展为表区域。
图 3-29
⑥ 打开 “编辑名称” 对话框,可以看到名称的引用位置会相应发生改变, “各月销售额” 的引用位置自动更改为 “=销售统计!$B$3:$D$7” ,如图3-30所示。
图 3-30
⑦ 当添加了数据时,可以看到表格中的F3单元格的总销量也自动计算,返回新的计算结果,如图3-31所示,达到动态计算的目的。
图 3-31