第七章使用命名公式——名称

1、 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 第七章第七章使用命名公式使用命名公式名称名称 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 主主 要要 内内 容容 7.2 7.2 定义名称的方法定义名称的方法 7.3 7.3 名称的编辑和使用名称的编辑和使用 7.4 7.4 定义名称的技巧定义名称的技巧 7.1 7.1 认识名称认识名称 7.5

2、7.5 使用名称的注意事项（选讲）使用名称的注意事项（选讲） ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 7.1 7.1 认识名称认识名称1.1.名称的概念名称的概念ExcelExcel的名称与普通公式类似，是一种由用户自行设计并能够进行数的名称与普通公式类似，是一种由用户自行设计并能够进行数据处理的算式。为了便于理解，可将名称看做是对工作簿中某些特定据处理的算式。为了便于理解，可将名称看做是对工作簿中某些特定元素的元素的“文本化文本化”标识，而这些元素包括标识，而这

3、些元素包括单元格区域、常量数据、常单元格区域、常量数据、常量数组甚至函数公式量数组甚至函数公式。当用户将这些元素定义为名称（及命名）后，。当用户将这些元素定义为名称（及命名）后，就可以在函数和公式中进行调用。就可以在函数和公式中进行调用。 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 2.2.为什么要使用名称为什么要使用名称（1 1）增强公式的可读性和便于公式修改）增强公式的可读性和便于公式修改 例如：例如： 假设某公司希望将各单位假设某公司希望将各单位3 3个月的营业

4、额进行汇总，公式为：个月的营业额进行汇总，公式为： =SUM=SUM（C3:E3C3:E3） 汇总公式正确，但是公式意图不明确，如果将汇总范围定义为名汇总公式正确，但是公式意图不明确，如果将汇总范围定义为名 称称“月营业额月营业额”，则公式变为，则公式变为=SUM=SUM（月营业额）（月营业额），更容易理解。，更容易理解。 此外，还可将某些常量（如营业税率）定义为名称，当其发生变此外，还可将某些常量（如营业税率）定义为名称，当其发生变 化时，只需调整名称中的值即可，毋需修改相关的公式。化时，只需调整名称中的值即可，毋需修改相关的公式。 ExcelExcel高级应用高级应用 河南工程学院公共选修

5、课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 （2 2）有利于简化公式）有利于简化公式 例如，如下计算增长率公式：例如，如下计算增长率公式： =IF=IF（SUMSUM（$B2:$F2)=0,0,G2/SUM($B2:$F2)$B2:$F2)=0,0,G2/SUM($B2:$F2) 若将重复使用的公式段若将重复使用的公式段SUM($B2:$F2)SUM($B2:$F2)定义为名称定义为名称TotalTotal，就可以简，就可以简 化公式的编辑，便于阅读。化公式的编辑，便于阅读。 简化后的公式为：简化后的公式为：=IF=IF（T

6、otal=0,0Total=0,0，G2/Total)G2/Total)（3 3）突破函数嵌套的限制）突破函数嵌套的限制 上一章说过，如果公式中嵌套函数超过上一章说过，如果公式中嵌套函数超过7 7层层，该公式就无法正常结，该公式就无法正常结束编辑，但如果将其中一部分嵌套函数定义为名称，就可以解决此类束编辑，但如果将其中一部分嵌套函数定义为名称，就可以解决此类问题。问题。 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 3.3.名称的分类名称的分类 ExcelExcel根据

7、名称的作用范围不同，通常被分为根据名称的作用范围不同，通常被分为“工作簿级名称工作簿级名称”和和“工作表级名称工作表级名称”。工作簿级名称工作簿级名称通常情况下，定义的名称可以在同一工作簿中任意一张工作表中使用。通常情况下，定义的名称可以在同一工作簿中任意一张工作表中使用。若需在公式中调用其他工作簿中的全局名称，就必须使用完整名称来输若需在公式中调用其他工作簿中的全局名称，就必须使用完整名称来输入公式。入公式。工作簿级名称的完整名称格式为：工作簿级名称的完整名称格式为：工作簿全名工作簿全名+ +半角感叹号半角感叹号+ +名称名称，如：，如：Book2.xls!dataBook2.xls!dat

8、a工作表级名称工作表级名称工作表级名称只在被定义的工作表中可见，而且工作表级名称只属于所定工作表级名称只在被定义的工作表中可见，而且工作表级名称只属于所定义的工作表。义的工作表。工作表级名称的完整名称格式为：工作表级名称的完整名称格式为：工作表名工作表名+ +半角感叹号半角感叹号+ +名称名称工作表级名称所在工作表公式可直接调用，其他工作表调用需用完整名称。工作表级名称所在工作表公式可直接调用，其他工作表调用需用完整名称。 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称

9、7.2 7.2 定义名称的方法定义名称的方法1.1.定义名称的限制定义名称的限制（1 1）名称可以是任意字符与数字组合在一起，但不能以数字开头，如）名称可以是任意字符与数字组合在一起，但不能以数字开头，如1pic1pic；如果要以数；如果要以数字开头，可在前面加下划线字开头，可在前面加下划线 _pic_pic。（2 2）名称不能以）名称不能以数字数字作为标识，如作为标识，如3 3等。等。（3 3）名称不能与）名称不能与单元格地址单元格地址相同，如相同，如A2A2不能用于名称标识。不能用于名称标识。（4 4）名称中不能包含）名称中不能包含空格空格，可以用下划线或点号代替。，可以用下划线或点号代替

10、。（5 5）不能使用除）不能使用除下划线、点号、和反斜线下划线、点号、和反斜线（ ）以外的其他符号，允许用问号（？），）以外的其他符号，允许用问号（？）， 但不能作为名称的开头。但不能作为名称的开头。（6 6）名称字符数不能超过）名称字符数不能超过255255。一般情况下，名称应该便于记忆且尽量简短，否则就违一般情况下，名称应该便于记忆且尽量简短，否则就违 背了定义名称的初衷。背了定义名称的初衷。（7 7）在定义工作表级名称时，必须使用名称所在工作表的）在定义工作表级名称时，必须使用名称所在工作表的标签名标签名。（8 8）建议不使用）建议不使用Print_AreaPrint_Area和和Pri

11、nt_TitlesPrint_Titles这两个名称，因为这是用户在这两个名称，因为这是用户在“页面设置页面设置” 中进行工作表区域和标题行列设置时，中进行工作表区域和标题行列设置时，ExcelExcel自动添加的内置名称。自动添加的内置名称。 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 2.2.使用使用“定义名称定义名称”对话框对话框 快捷键为快捷键为，该对话框是编辑和管理名称的唯一方法。，该对话框是编辑和管理名称的唯一方法。3.3.使用名称框使用名称框4.4.批量

12、创建名称批量创建名称5.5.为其他元素定义名称为其他元素定义名称使用组合区域使用组合区域使用常量使用常量使用函数使用函数 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 7.3 7.3 名称的编辑和使用名称的编辑和使用 1.1.名称的修改名称的修改修改名称中的公式修改名称中的公式修改名称标识修改名称标识2.2.名称的删除名称的删除3.3.查看名称中的公式查看名称中的公式 使用快捷键使用快捷键可快速打开可快速打开“粘贴名称粘贴名称”对话框对话框 ExcelExcel高级应用

13、高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 4.4.在公式中使用名称在公式中使用名称在公式中使用名称时，例如在公式中使用名称时，例如DeptDept，可以通过手工输入名称，也可以，可以通过手工输入名称，也可以在公式的编辑过程中按下在公式的编辑过程中按下键，调用键，调用“粘贴名称粘贴名称”对话框来插入对话框来插入名称。如：名称。如：=COUNTA=COUNTA（DeptDept）由于由于“工作簿级名称工作簿级名称”可以作用于任意工作表，所以可以直接调用可以作用于任意工作表，所以可以直接调用此类名称

14、进行公式编写；但如果希望调用其他工作表的此类名称进行公式编写；但如果希望调用其他工作表的“工作表级工作表级名称名称”，则必须像引用其他工作表的引用一样，则必须像引用其他工作表的引用一样，输入完整的名称输入完整的名称。如：如：=SUM=SUM（Sheet1Sheet1！TotalTotal）* *TaxTax若用户输入了一个并不存在的名称，公示的运算结果会若用户输入了一个并不存在的名称，公示的运算结果会“显示显示#NAME#NAME？错？错误误”，此时用户应对，此时用户应对“定义名称定义名称”对话框中的名称列表进行检查修改。对话框中的名称列表进行检查修改。 ExcelExcel高级应用高级应用

15、河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 7.4 7.4 定义名称的技巧定义名称的技巧 1.1.相对引用和混合引用定义名称相对引用和混合引用定义名称2.2.省略工作表名定义名称省略工作表名定义名称如果希望使用名称来分别引用各表的固定区域，如在每张工作表中都如果希望使用名称来分别引用各表的固定区域，如在每张工作表中都引用本工作表的引用本工作表的“A1”A1”区域，需要在定义名称时使用区域，需要在定义名称时使用“缺省工作表名缺省工作表名的单元格引用的单元格引用”。如：首先打开如：首先打开“定义名称定义名称”

16、对话框，定义一个全局名称对话框，定义一个全局名称LinkLink，在，在“引引用位置中输入：用位置中输入：=!$A$1=!$A$1就可以在当前工作簿的任何一张工作表中使用这个名称时，自动引用就可以在当前工作簿的任何一张工作表中使用这个名称时，自动引用输入公式所在工作表中的输入公式所在工作表中的A1A1单元格进行计算单元格进行计算例例7.1 7.1 相对引用和混合引用定义名称相对引用和混合引用定义名称 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 3.3.如何定义动态名称

17、如何定义动态名称“动态引用动态引用”的概念：动态是相对于静态而言的。一个静态的区域引的概念：动态是相对于静态而言的。一个静态的区域引用，例如用，例如$A$1:$A$10$A$1:$A$10，通常是保持不变的，而动态引用则是随着明细，通常是保持不变的，而动态引用则是随着明细数据的增加或减少，引用区域数据的增加或减少，引用区域动态动态的扩大或缩小，即是动态变化的。的扩大或缩小，即是动态变化的。“动态名称动态名称”是用于生成动态变化数据的一种特殊命名公式。其中动是用于生成动态变化数据的一种特殊命名公式。其中动态变化的数据通常指的是态变化的数据通常指的是动态引用动态引用和和动态数组动态数组。例例7.2

18、 7.2 借用借用“列表列表”定义动态名称定义动态名称 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第7 7章章 使用命名公式使用命名公式- -名称名称 7.5 7.5 使用名称的注意事项（选讲）使用名称的注意事项（选讲） 1.1.工作表复制时的名称问题工作表复制时的名称问题由于工作表在同一工作簿中的复制会导致工作簿中存在由于工作表在同一工作簿中的复制会导致工作簿中存在相同名字的全相同名字的全局名称和局部名称局名称和局部名称，用户应清晰的了解并有目的地进行调整或删除，用户应清晰的了解并有目的地进行调整或删除，以便于公式中名称的合理利用。以便于公式中名称的合理利用。2.2.同名名称的编辑问题同名名称的编辑问题若用户确实需要同时存在相同名字的若用户确实需要同时存在相