Excel技巧之名称的妙用.docx

Excel技巧之名称的妙用.docx

《Excel技巧之名称的妙用.docx》由会员分享，可在线阅读，更多相关《Excel技巧之名称的妙用.docx（17页珍藏版）》请在冰豆网上搜索。

Excel技巧之名称的妙用

定义名称有3种方法，用户在工作中可以针对不同的情况选择使用最适合的方法。

771 插入名称

标准的名称定义方法是：

先选择待定义的单元格或单元格区域，然后单击菜单“插入”→“名称”→“定义”（或者按组合键），在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入名称字符，单击“确定”按钮，如图771所示。

图771“定义名称”对话框

在“定义名称”对话框中，用户可以连续定义多个名称，查看名称，以及删除名称。

名称一旦被定义，只能修改其引用位置，而不能修改名称的名字。

比如，要把图771中所定义的名称“姓名”改为“客户姓名”，只能先添加一个“客户姓名”的名称，再删除名称“姓名”。

772 使用名称框

用户可以利用工作表中的“名称框”快速定义名称。

以图772所示的表格为例，要将单元格区域A2∶A5定义名称为“姓名”的具体步骤如下。

图772 使用名称框快速定义名称

选定A2:

A5。

把光标定位到名称框中，输入“姓名”，按键。

773 指定名称

使用“指定”的方法可以大批量地进行名称定义。

仍以图772所示的表格为例，如果需要把单元格区域A2∶A5定义名称为“姓名”，同时把单元格区域B2∶B5定义名称为“金额”，步骤如下。

选定A1:

B5。

单击菜单“插入”→“名称”→“指定”（或按组合键），在“指定名称”对话框中，勾选“首行”复选框，如图773所示。

单击“确定”按钮。

图773“指定名称”对话框

在定义名称的时候，许多用户遇到Excel提示“输入的名称无效”，定义不成功。

这是因为，在定义名称时，不是任意字符都可以作为名称的，名称的定义有一定的规则。

1．名称可以是任意字符与数字组合在一起，但不能以数字开头，更不能以数字作为名称，如1PL。

同时，名称不能与单元格地址相同，如B3。

如果要以数字开头，可在前面加上下划线，如_1PL。

2．不能以字母R、C、r、c作为名称，因为R、C在R1C1引用样式中表示工作表的行、列。

3．名称中不能包含空格，可以用下划线或点号代替。

4．不能使用除下划线、点号和反斜线（/）以外的其他符号，允许用问号（?

），但不能作为名称的开头，如Wange?

可以，但?

Wage就不可以。

5．名称字符不能超过255个字符。

一般情况下，名称应该便于记忆且尽量简短，否则就违背了定义名称的初衷。

6．名称中的字母不区分大小写。

另外，在Excel中，有一些有特殊含义的名称，是用户在使用了诸如高级生产筛选功能以后，由Excel自动定义的。

在此介绍两个实用的特殊名称，Print_Titles和Print_Area。

被定义为Print_Titles的区域将成为当前工作表的打印的顶端标题行和左端标题行。

比如，将工作表的1∶1定义名称为“Print_Titles”，则工作表在打印时，会在每页中都打印这一行。

有关定义打印的顶端标题行和左端标题行的相关内容，请参阅技巧112。

被定义为Print_Area的区域将被设置为工作表的打印区域。

有关与此的更多内容，请参阅技巧111

许多用户对名称的理解不够透彻，这有碍于他们在使用Excel的过程中进一步挖掘名称的用途。

Excel中的名称，并不仅仅是为单元格或单元格区域提供一个容易记忆的名字这么简单。

在“定义名称”对话框中，如图771所示，“引用位置”文本框中的内容永远是以“=”开头的，而“=”在Excel中是公式的标志。

所以，完全可以把名称理解为一个有名字的公式。

创建名称，实质上是创建命名公式，只不过这个公式不存放于单元格中而已。

基于以上理论，在名称中不但能够使用单元格引用，还能够使用常量与函数。

791 使用常量

在名称中，可以使用数字、文本、数组，或者简单的计算公式。

使用常量名称的优点是，可简化公式的编写并使工作表更加整洁，并且随时可以修改常量名称的定义，以实现对表格中的大量计算公式快速修改。

示例一

假设有一张表格用于计算公司应缴税额，其中需要频繁引用营业税的税率，此时可以使用一个名称来存储税率。

方法如下。

单击菜单“插入”→“名称”→“定义”（或者按组合键）。

在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入“Tax”，在“引用位置”文本框中输入“=5%”，单击“确定”按钮，如图791所示。

图791 创建常量名称

下面就可以在工作表中使用刚才创建的常量名称，例如要计算B2单元格中营业额对应的税额，可以使用公式：

=Tax*B2

如图792所示。

图792 使用常量名称进行公式计算

如果修改“Tax”的定义，将引用位置改为“=3%”，则表格中所有引用了该名称的公式都会改变计算结果。

示例二

假设在表格中经常会使用到相同的文字，如公司名称，则也可以把它定义为名称来使用，创建文本名称的方法同示例一。

如，可以创建一个名称“Co”，定义其引用位置为“=”人民邮电出版社””。

792 使用函数

在如图793所示的表格中，H列用于计算总成绩，它的公式为：

“=总成绩”。

图793 使用带有函数的名称做求和计算

原来，该工作簿中已经创建了一个带有求和函数的名称，如图794所示。

图794 创建带函数的名称

注意

定义此名称时，其公式中使用的是相对引用，而非绝对引用。

如果名称中使用相对引用，则工作表中引用该名称的公式在求值时，会随活动单元格的位置变化而对不同区域进行计算。

在默认情况下，所有的名称都能够在工作簿中的任何一张工作表中使用。

例如，创建一个叫做“Name”的名称，引用Sheet1工作表中的单元格A1，然后在当前工作簿的所有工作表中都可以直接使用这一名称。

这种能够作用于整个工作簿的名称被称为工作簿级名称。

在实际工作中，用户可能需要在多张工作表中使用相同的名称，比如，Sheet1是用于记录公司1月份的销售数据，其中的A1∶A100被定义名称为“城市”。

Sheet2与Sheet1的表格形式完全相同，用于记录公司2月份的销售数据，如果希望为Sheet2的A1∶A100也定义名称为“城市”，要怎么解决呢？

在这种情况下，需要创建仅能作用于一张工作表的名称，称为工作表级名称，或局部性名称。

创建工作表级名称的方法有两种，分别是使用名称框和“插入名称”，具体操作步骤与创建工作簿级名称无异，请参阅技巧77。

工作表级名称的特征是：

工作表名称＋感叹号＋名称，也就是在工作簿级名称的前面加上工作表名称和感叹号。

例如，“城市”是一个工作簿级名称，如果创建一个“Sheet2!

城市”的名称，就成为只作用于Sheet2的工作表名称。

如果工作表名称中包含有空格，那么在创建名称时必须用单引号把工作表名称引起来。

例如，在“销售数据新”工作表中创建工作表级名称，必须写为

'销售数据新'!

城市

工作表级名称所在的工作表中使用该名称，可以不加入工作表名称。

例如，在Sheet2中使用名称“Sheet2!

城市”，只用写“城市”即可。

但是在其他工作表中使用该名称，必须用完整的名称写法“Sheet2!

城市”。

在Excel，如果需要重新编辑已定义名称的引用位置，可按组合键，在“定义名称”对话框中选中目标名称，然后把光标定位到“引用位置”文本框，进行修改。

在通常情况下，用户会在编辑名称引用时遇到一些麻烦。

比如，图811中显示了一个已经存在的名称，该名称的引用位置内容是：

=Sheet1!

$A$1:

$E$10

图811 待编辑的名称

假设需要把引用位置改为=Sheet1!

$A$5:

$E$15，操作方法是把光标定位到=Sheet1!

$A$之后，按键删除1，输入5，然后使用右箭头键将光标往右移，希望能够把末尾的10改为15。

可是，当按下右箭头键时，光标并没有发生移动，引用内容却改变了，如图812所示。

这是因为，“引用位置”文本框默认情况下处于“指向”模式，此时箭头键的作用是在工作表中选定区域而不是移动光标。

解决方法是在编辑前把光标定位到“引用位置”文本框，按一下键，切换到“编辑”模式，再进行内容编辑。

图812 编辑时按箭头键的结果

这一技巧在任何出现类似文本框的地方都适用，比如在设置数据有效性的来源，或者在条件格式的公式编辑时。

利用OFFSET函数与COUNTA函数的组合，可以创建一个动态的名称。

动态名称是名称的高级用法，能够实现对一个未知大小的区域的引用，此用法在Excel的诸多功能中都能发挥强大的威力。

在实际工作中，经常会使用如图821所示的表格来连续记录数据，表格的行数会随着记录追加而不断增多。

图821 不断追加记录的表格

如果需要创建一个名称来引用C列中的数据，但又不希望这个名称引用到空白单元格，那么就不得不在每次追加记录后都改变名称的引用位置，以适应表格行数的增加。

在这种情况下，可以创建动态名称，根据用户追加或删除数据的结果来自动调整引用位置，以达到始终只引用非空白单元格的效果。

创建动态名称的方法如下。

单击菜单“插入”→“名称”→“定义”（或者按组合键）。

在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入“Data”，在“引用位置”文本框中输入公式：

=OFFSET（Sheet1!

$C$4,,,COUNTA（Sheet1!

$C:

$C）1）

如图822所示。

图822 创建动态名称

单击“确定”按钮。

以上公式先计算B列中除了列标题以外的非空白单元格的数量，然后以C4单元格（首个数据单元格）为基准开始定位，定位的行数等于刚才计算出来的数量。

下面可以在C列以外的单元格中通过计算来验证此名称的引用是否正确，比如在B1中输入公式：

=SUM（Data），如图823所示。

图823 使用动态名称进行计算

如果继续追回记录，名称“Data”的引用位置会自动发生改变，B2中的计算结果能够体现这一点，如图824所示。

图824 动态名称的引用位置自动适应表格的变化

注意

以上公式只能正确计算不间断的连续数据，如果表格中的数据有空白单元格，那么动态名称的引用位置将发生错误。

在Excel中，使用动态名称与ActiveX控件，能够轻松地实现工作表中的图片自动更新的特殊效果。

本技巧中将以制作一个简单的职员资料表为例，使职员的相片能够随着姓名的改变而改变。

职员资料表工作簿内含有两张工作表，“资料表”工作表用于显示职员的资料，“图片”工作表用于存储所有职员的相片。

“资料表”中的表格如图831所示。

图831 职员资料表格

“图片”工作表中的表格如图832所示，A列是职员的姓名，B列当前是空白，用于存放职员的相片。

图832“图片”工作表

采用以下步骤增加相片。

单击B1，然后单击菜单“插入”→“图片”→“来自文件”，在“插入图片”对话框中选择相应的相片文件，单击“插入”。

因为相片的原始大小比单元格大，所以需要调整大小。

单击相片，然后把光标移动到右下角的圆圈上，当光标变成一个斜线箭头时，往左上方向拖动，如图833所示，直到单元格能容纳整张相片。

图833 调整相片大小

为了能使相片更好地被单元格所容纳，还可以使用以下方法。

单击相片，然后单击绘图工具栏的“绘图”→“自动靠齐”→“对齐网格”，如图834所示。

如果绘图工具栏没有显示，可以右键单击工具栏，在弹出的菜单中单击“绘图”项。

图834 设置图片自动靠齐网格

双击相片，在“设置图片格式”对话框的“属性”选项卡中，选择“大小位置，随单元格而变”项，单击“确定”按钮，如图835所示。

图835 设置相片的属性

使用相同的方法为所有职员插入相片，如图836所示。

图836 为所有职员插入相片

按组合键，在“定义名称”对话框中，在“在当前工作簿中的名称”文本框中输入“pic”，在“引用位置”文本框中输入“=OFFSET（图片!

$B$1,MATCH（资料表!

$A$2,图片!

$A$1:

图片!

$A$4,0）1,0）”，单击“确定”按钮。

切换到“资料表”工作表，右键单击工具栏，在弹出的菜单中选择“控件工具箱”项，在“控件工具箱”工具栏中单击“命令按钮”控件，如图837所示。

图837“控件工具箱”工具栏

单击B3单元格的左上角，然后往右下方向拖动，画出一个符合单元格大小的命令按钮，如图838所示。

图838 在表格中插入命令按钮

在A2中输入某职员的姓名，如“张三”。

单击命令按钮，把光标定位到编辑栏，将原有内容“=EMBED（"Forms.CommandButton.1",""）”改为“=pic”。

现在，张三的相片就显示出来了，如图839所示。

图839 图像在命令按钮中显示

为了让相片的大小与单元格大小相匹配，可以调整命令按钮的大小，方法同步骤2。

调整大小后的显示效果如图8310所示。

图8310 调整大小后的相片

在A2单元格内输入不同职员的姓名，在B3中就能够自动显示其相片，如图8311所示。

图8311 相片自动更新

当工作簿中定义了较多的名称时，可以使用以下两种方法快速选择定义名称的区域。

方法1 使用名称框

单击名称框的下拉箭头，在下拉列表中会显示当前工作簿中的所有名称（但不包含常量名称与函数名称），选择其中的一项，就能让该名称所引用的区域处于选择状态。

如图841所示。

图841 使用名称框选定名称区域

方法2 使用定位

按键，在“定位”对话框中，会显示当前工作簿中的所有名称（但不包含常量名称与函数名称），双击其中的一项，就能让该名称所引用的区域处于选中状态。

如图842所示。

图842 使用定位选定名称区域

如果某个工作簿文件中定义了很多名称，可以创建一份名称表格来查看所有的名称以及它们的引用内容。

选定要存放名称表格的首个单元格，如A1，单击菜单“插入”→“名称”→“粘贴”，或者按键，在“粘贴名称”对话框中，可以看到所有的名称，如图851所示。

单击“粘贴列表”按钮，所有的名称和引用内容都会填入到单元格中，如图852所示。

            图851“粘贴名称”对话框                            图852 名称表格

如果原有单元格区域存储了数据，则会覆盖原有数据，所以粘贴列表时一定要注意。

在Excel中有一个鲜为人知的特性，就是能够使用图形方式查看定义了名称的区域。

假设有一张工作表中定义了3个名称，分别是Myrange1、Myrange2、Myrange3。

单击菜单“视图”→“显示比例”，在“显示比例”对话框中，选择“自定义”项并把比例值设置为小于40%，如39%，Excel就会以黑色边框显示名称区域，并以蓝色字体显示名称，如图861所示。

图861 以图形方式显示名称

在Excel的“定义名称”对话框中，只能逐个删除名称，这在需要大量删除名称时是非常繁琐的。

利用一小段宏代码，可以轻松完成这项工作。

按打开VBA编辑器窗口，单击菜单“插入”→“模块”来插入一个新模块，默认情况下为“模块1”，然后在模块1的代码窗口中输入以下代码：

SubDelNames（）

   DimnmAsName

   ForEachnmInThisWorkbook.Names

       nm.Delete

   Nextnm

EndSub

最后，按F5键来运行这段代码，就能一次性删除工作簿中的所有名称。