陶泽昱Excel应用技巧大全第37期：定义名称的对象

一、使用合并区域引用和交叉引用

（1）在名称中使用合并区域引用

有些工作表由于需要按照规定的格式，需要计算的数据存放在不连续的多个单元格区域中，在公式中直接使用合并区域引用让公式的可读性变弱，可以将其定义为名称来调用。

例1 使用合并区域名称统计多区域降雨量

如图1所示，为某地区降雨量报表（格式固定），在H5:H8单元格需要统计最高、最低、平均日雨量和降雨天数。由于日降雨量数据分散在B3:B12、D3:D12、F3:F12和H3这些不连续的单元格中，因此可使用联合运算符（逗号“,”）形成合并区域。

使用名称进行统计的操作方法如下。

步骤1 按住Ctrl键，选取B3:B12、D3:D12、F3:F12和H3单元格区域。

步骤2 在【名称框】中输入“降雨量”，按Enter键结束编辑，如图2所示。

也可以单击【公式】选项卡上【定义名称】按钮，在弹出的【新建名称】对话框中将自动为该合并区域引用“降雨量”作为命名，单击【确定】按钮退出对话框，如图3所示。

步骤3 在H5:H8单元格分别输入以下公式，即可完成多区域数据统计：

=MAX(降雨量)

=MIN(降雨量)

=AVERAGE(降雨量)

=COUNT(降雨量)

（2）在名称中使用交叉引用

在名称中使用交叉运算符（单个空格）的方法与在单元格的公式中一样，例如定义一个名称X，使之引用Sheet1工作表的A3:G7与C4:D12单元格的交叉区域，操作方法如下。

步骤1 单击【公式】选项卡【定义名称】按钮。

步骤2 如图4所示，在【新建名称】对话框中，在【名称】编辑框输入“X”。

步骤3 单击【引用位置】编辑框，然后鼠标选取A3:G7单元格区域，自动将”Sheet1!$A$3:$G$7”应用到该编辑后，按Space键入一个空格，再使用鼠标选取C4:D12单元格区域，单击【确定】按钮退出对话框。

二、使用常量

如果需要在整个工作簿中多次重复使用相同的常量，如产品利润率、增值税率、基本工资额等，那么将其定义为一个名称并在公式中使用名称，将使得所有公式的修改、维护变得更加容易。

例如，某公式经营报表中，需要在多个工作表的多处公式中计算营业税（税率为3%），当这个税率发生变动时，多出更改公式中的值效率不高，且容易发生遗漏造成计算结果不符合。可以定义一个名称“税率”以便公式调用和修改。才做方法如下。

步骤1 如图5所示，单击【定义名称】按钮，在【新建名称】对话框的【名称】编辑框中输入“税率”。

步骤2 在【备注】编辑框中输入该税率的文件依据“根据闽榕税【2009】382号规定”。

步骤3 在【引用位置】编辑框中输入“=3%”，单击【确定】按钮退出对话框。

三、使用常量数组

在单元格中存储查找所需的常用数据，可能影响工作表的美观，并且会由于误操作（例如删除行、列操作，数据单元格区域激活时不小心按到键盘造成数据以外更改等）导致查询结果错误。可在公式中使用常量数组或定义名称让公式易于阅读和维护。

例3 定义产品等级标准常量数组

如图6所示，某工厂生产产品按单批检验的不良率评定质量等级，其标准为不良率小于1.5%、5%、10%的分别算特级、优质、一般，达到或超过10%的为劣质产品。

原先使用F3:G6单元格区域存储质量等级对应关系，现改用常量数组定义名称，操作方法如下。

步骤1如图7所示，单击【定义名称】按钮，在【新建名称】对话框的【名称】编辑框中输入“级次”。

步骤2 在【引用位置】编辑框中输入以下等号和数量数组，单击【确定】按钮退出对话框：=(0,”特级”;1.5,”优质”;5,”一般”;10,”劣质”)。

步骤3 在D3单元格中输入以下公式并双击“填充柄”向下复制到D10单元格：

=LOOKUP(C3*100,级次)

其中，C3单元格为百分比数值，因此需要*100后查询。

四 使用函数与公式

在名称中，也可使用函数。例如在Excel 97~2003中，由于函数允许的最大嵌套层数为7层，当需要在B1单元格使用公式将A1单元格的数字剔除时，可以选择B1单元格后定义名称“X”，在【引用位置】中输入：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,0,),1,),2,),3,),4,),5,),6,),7,)

然后在B1单元格输入以下公式：

=SUBSTITUTE(SUBSTITUTE(X,8,),9,)

虽然Excel 2010版允许64层嵌套，基本不存在查过嵌套层数限制问题。但将部分公式定义为名称，也可大大缩短单元格中公式的长度，特别是重复使用的公式部分。

​