廖晨起名字Excel之自定义名称的用法终

花叶草木深，工作要用心。我们又见面了，上文介绍自定义名称在引用地址上的用法，其中的关键就是如何创建需要的映射关系？

要使用映射关系，需要注意的关键点有3：

1.创建名称时，注意选择单元格与引用位置(S)的相对位置；2.名称的引用地址必须为相对引用或混合引用；3.选中单元格和名称引用位置的映射关系，只与两者的相对位置有关，不论是否再同一个工作表。熟知这3点，再加上多多练习，自定义名称的这类用法必能运用自如。

有的学生学到这，就跟我说：老师，你说公式的部分还用学吗？我觉得学到这，已经够用了啊！

我说：我是学生的时候，也跟你有一样的想法，不过参加工作了，你就知道了，如果想过好的生活，拿高的工资，就需要比别人学的要深要精啊！而且判断你能不能干活的，不是你，而是面试官，尽量多学些，别到用时方恨少啊！

学生：哪学到什么程度才可以呢？

我：最少也得把自定义名称的公式和名称的部分学完哈，光学完还不行，还需要多多练习啊！

01

公式

在定义名称使用公式也是我们最常用的方法之一，只不过好多人用的公式都缺少灵性。那么问题来了，怎么才算是有灵性的公式呢？这就要从网友一个留言留言说起：

例如b1单元格输入20*500*1200在c1需要计算结果，怎样用公式？

要解决这个问题，需要用到Excel的一特殊函数：宏函数EVALUATE;

语法结构=EVALUATE(字符串)；

它的功能将参数字符串表达式进行运算求出结果；

使用宏函数的时候，要注意2点：

1.宏函数并不能单独在单元格中运行，否则会返回错误：该函数无效2.若想存储包含宏函数的名称，必须将文件保存为XLSM文件格式。至于更详细用法查看《EVALUATE函数不会用，怎么办？初、中、高级用法，这都有！》，这里就不再赘述。

当时一看，SO EASY!,于是写下了下面的步骤：

1.点击【公式】下的【定义名称】按钮，弹出窗口录入名称：Calcu,引用位置录入：=EVALUATE($b$1),点击确定。2.选中C1,录入=Calcu，回车；

操作步骤示意图（图1）

结果网友看了，留言说：你这每计算一个单元格，都需要定义一个名称，没有法搞，太麻烦了，我这需要的是批量运算！

我心说：你太顽皮了，你不说清楚，还来怪我喽，不过没有问题，只需稍微变化一下就行了啊！

假设所需要运算表达式都是在B列，计算的结果在C列，虽然模拟数据只到B3,我们将C列的公式填充到C100,这样即便B有新公式录入，C列会自动生成结果，不过这里需要前一篇文章的知识加持，具体操作步骤如下：

1.选中C1,ctrl+f3,调出名称管理器，选中名称Calcu,在引用位置将行号前的绝对引用$去掉，点击√按钮，2.名称框输入C1:C100,点击栏,ctrl+enter(回车)（上次已经录入=Calcu）

案例操作步骤示意图（图2）

不过看完测试结果，觉得这样做不太美观，因为只要B列单元格没有数据，对应C列单元格就是#VALUE!；去掉这种错误编码最常见的方法，就是直接修改C1的公式，改为=IFERROR(Calcu,""),鼠标移至C1右下角，鼠标变为实心＋，双击更新所有公式；

案例操作步骤示意图（图3）

如果你想偷懒，不想更新公式，记得还是要先选择C1,重复图2第一步骤，将引用位置公式修改为=IFERROR(EVALUATE($B1),"")，点击√按钮。

说到这，是不是你也发现了，映射关系的烦人之处就在于，你要时刻记住，若要修改名称，需谨记当前选中单元格的位置，因为一不小心就可能导致名称输出的结果是错误的。那么问题来了，有没有不用注意选中单元格位置，又能完美实现映射关系的方法呢？

答案当然是肯定，就是之前《合并求和有多难？挺简单的，一个公式+自定义名称就能搞定！》说过的能实现映射关系的通用公式，之前文中用的是字符串拼接的方法；这次我们用ROW,COLUMN函数来生成单元格位置信息，再用ADDRESS函数把位置信息转化成引用地址字符串，再用INDIRECT加持成引用地址。

在讲解通用公式之前，先来简单回忆ADDRESS函数的用法：

ADDRESS(行号,列标, [1/2/3/4],[TRUE/FALSE],[工作表名称])

行号：以数值的形式输入指定单元格行号；列标：以数值的形式输入指定单元格列标；1：范例 $A$1，2：范例：A$1,3:范例：$A1,4：范例：A1;省略默认为1；TRUE/1：表示A1模式，FALSE/0：表示为R1C1模式，省略默认为1或TRUE工作表名称：通常省略，省略时引用为当前工作表名称；而ROW和COLUMN()函数就比较简单，一句就能概括：

ROW()获取参数行号，COLUMN()获取参数列数，参数为引用单元格地址或范围，可以省略，省略返回当前单元格的行号或列标；所用的函数已经备齐了，再回想这个功能，可以换成一句话就是：

当前单元格运行同行且前一列单元格的数学表达式并输出结果。

这句话可以拆解为：

1.位置：同行且是前一列，以运行单元格为参考，则用函数表示行号，列标即：ROW(),COLUMN()-1；

2.转化成位置字符串：ADDRESS(ROW(),COLUMN()-1)=>引用地址：INDIRECT(ADDRESS(ROW(),COLUMN()-1))

3.运行输出结果对应的函数EVALUATE(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

4.最后再容个错：=IFERROR(EVALUATE(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),"");

然后修改Calcu的引用公式为上面的公式，重新填充C1:C100的公式=Calcu；

操作步骤示意图（图4）

可能有人要说：你这不是把简单的问题复杂化了吗？

确实，本来一个小的注意就能解决，却要花些时间来搞什么通用公式，不过多数员工都会采用第二种方法，因为它确保准确，尤其在处理数字上，来不得半点马虎，一个数字错误都可能让你的工资所剩无几！

如果上面用法是单兵作战，那接下来的用法就是团战了，也就是名称自定义中使用：

02

名称的嵌套

它常用于来简化公式，比如一个名称的公式：

=EVALUATE(SUBSTITUTE(SUBSTITUTE(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),"×","*"),"÷","/"))

这个公式不算特别长，但在排查错误，足够让我们眼花缭乱了，哪我们该如何拆分呢？

拆解公式也是有规律可循的，就是程序员设计模式中6大原则之一：单一职责，即模块开发中，尽量保持单模块的功能单一，这样就能避免逻辑与之前逻辑互相干扰而引发更多的错误。

仔细观察公式，可分为3大功能，引用前一单元格，替换中文×和÷为计算机的运算符，执行替换后表达式；

引用前一单元格的名称为PRE：=INDIRECT(ADDRESS(ROW(),COLUMN()-1))替换符号的名称为：REPLA,并带入上以名称PRE,最终公式为SUBSTITUTE(SUBSTITUTE(PRE),"×","*"),"÷","/")执行表达式的名称为：EVAL,引用位置录入公式为：=EVALUATE(REPLA);这种做法还有一个好处就是，其中一个功能的规则变了，只需修改单一名称就可以了，比如表达式所在单元格位置变了，在结果单元格的左2列,只需将名称PRE中的COLUMN()-1,改为COLUMN()-2。

你要是觉得每次都需要修改名称比较麻烦，还可以将ROW()和COLUMN()-1,配置成两个单元格地址，通过修改单元格数值来控制规则，至于后面你还想怎么玩，就看你的思路有多广了。

名称这种用法还有个文雅的名字：名称调用链；由于它的调用结构，新功能插入调用链的位置，是能决定操作步骤的多少，而只有在末端前插入，则步骤最少。

比如本例中增加清除表达式中空格的功能，而末端调用EVAL名称，假设定义名称为DELB,引用位置公式为：=TRIM(REPLA),最后修改EVAL公式为：=EVALUATE(DELB);

好了关于名称的部分内容就告一段落，希望你能从中有所启发，也欢迎你留言，告诉我你想知道的Excel知识点有哪些，我会尽快安排！花叶草木深，工作要认真，预想涨工资，关注胖廖晨。

石材杂志社

提升网站排名的办法

云端杂志

石材