Excel 2019选择名称定义域

在工作簿中定义了较多的名称时,可以使用以下两种方法快速地选择名称所对应的单元格区域。下面以“设置名称引用.xlsx”工作簿为实例简单介绍。

方法一:使用“名称框”

单击“名称框”的下拉箭头,在下拉列表中会显示当前工作表中的所有名称(不包括常量名称和函数名称)。选择其中的一项就可以让该名称所引用的区域处于选择状态,如图9-34所示。

方法二:使用“定位”对话框

按“F5”键,在打开的“定位”对话框中会显示当前工作簿中的所有名称(不包括常量名称和函数名称)。双击其中的一项就可以让该名称所引用的区域处于选择状态,如图9-35所示。

图9-34 使用“名称框”选定名称区域

“定位”对话框

图9-35 “定位”对话框

Excel 2019定义名称应用

在工作表中定义名称后,默认情况下可应用于整个工作簿,并且同一工作簿中不能定义相同的名称。如果需要定义只适用于某张工作表的名称,可以采用以下步骤进行。例如,此处需要分别在工作表Sheet1和Sheet2中建立“销售金额”名称。

STEP01:打开“定义名称.xlsx”工作簿,首先在“Sheet1”工作表中选择要定义的单元格区域,这里选择“D4:D12”单元格区域。切换至“公式”选项卡,在“定义的名称”组中单击“定义名称”下三角按钮,在展开的下拉列表中选择“定义名称”选项,打开如图9-32所示的“新建名称”对话框。在“名称”文本框输入名称名,这里输入“销售金额”,然后单击“范围”选择框右侧的下拉按钮,在展开的下拉列表中选择“Sheet1”选项,最后单击“确定”按钮完成名称的新建,如图9-32所示。

STEP02:切换到“Sheet2”工作表中,选择要定义的单元格区域,这里选择“D4:D12”单元格区域。切换至“公式”选项卡,在“定义的名称”组中单击“定义名称”下三角按钮,在展开的下拉列表中选择“定义名称”选项,打开如图9-33所示的“新建名称”对话框。在“名称”文本框输入名称名,这里输入“销售金额”,然后单击“范围”选择框右侧的下拉按钮,在展开的下拉列表中选择“Sheet2”选项,最后单击“确定”按钮完成名称的新建。

设置名称范围为Sheet1工作表

图9-32 设置名称范围为Sheet1工作表

设置名称范围为Sheet2工作表

图9-33 设置名称范围为Sheet2工作表

Excel 2019编辑名称引用步骤图解

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

在通常情况下,用户会在编辑名称引用的时候遇到一些麻烦,接下来以“设置名称引用.xlsx”工作簿中的名称为例进行具体讲解。

STEP01:选择工作表中的任意一个单元格,这里选择B2单元格。切换至“公式”选项卡,在“定义的名称”组中单击“名称管理器”按钮,打开如图9-28所示的“名称管理器”对话框。在名称列表框中选择需要重新编辑的名称,这里选择“Date”名称行,然后单击“编辑”按钮。

STEP02:随后会打开如图9-29所示的“编辑名称”对话框,该对话框中显示了一个已经存在的名称,该名称的引用位置内容是“=Sheet1!$A$1:$E$10”。

图9-28 选择名称行

图9-29 待编辑的名称

STEP03:假设需要把引用位置修改为“=Sheet1!$A$5:$E$15”,操作方法是在编辑前把光标定位到“引用位置”文本框,按“F2”键切换至“编辑”模式。然后把光标定位到“= Sheet1!$A$”之后,按“Del”键删除1,输入5,然后使用右箭头键将光标往右移,把末尾的10修改为15,最后单击“确定”按钮完成编辑,如图9-30所示。

STEP04:返回“名称管理器”对话框,可以在名称列表框中选择“Date”名称行,此时在“引用位置”文本框中显示的单元格区域是编辑后的结果,如图9-31所示。

图9-30 编辑名称引用位置

编辑名称引用效果

图9-31 编辑名称引用效果

Excel 2019删除名称定义步骤图解

对于一些不再使用的名称,可以通过下面的操作来进行删除。

STEP01:打开“删除名称定义.xlsx”工作簿,选择工作表中的任意一个单元格,这里选择B2单元格。切换至“公式”选项卡,在“定义的名称”组中单击“名称管理器”按钮,打开如图9-25所示的“名称管理器”对话框。在名称列表框中选择需要删除的名称,这里选择“总价”名称行,然后单击“删除”按钮。

STEP02:随后会弹出“Microsoft Excel”提示框,询问“是否确实要删除名称 总价”,单击“确定”按钮即可删除“总价”名称,如图9-26所示。删除“总价”名称后的效果如图9-27所示,“总价”名称将不再显示在名称列表框中。

图9-25 删除名称

图9-26 提示框

删除名称后的效果

图9-27 删除名称后的效果

Excel 2019修改名称定义步骤图解

定义名称之后,如果需要修改(包含修改名称、引用位置),只需要对其重新编辑即可,而不需要重新定义。以下是修改已经定义的名称的具体操作步骤。

STEP01:打开“修改名称定义.xlsx”工作簿,选择工作表中的任意一个单元格,这里选择B2单元格。切换至“公式”选项卡,在“定义的名称”组中单击“名称管理器”按钮,打开“名称管理器”对话框,如图9-21所示。

STEP02:打开“名称管理器”对话框后,在名称列表框中选择需要重新编辑的名称,这里选择“金额”名称行,然后单击“编辑”按钮,如图9-22所示。

图9-21 “名称管理器”对话框

图9-22 选择需要修改的名称

STEP03:随后会打开“编辑名称”对话框,在“名称”框中可以重新修改名称名,这里在“名称”文本框中将“金额”修改为“总价”。在“引用位置”文本框中,可以手工对需要修改的部分进行更改,也可以选中需要修改的部分,然后单击右侧的单元格引用按钮返回工作表,重新选择数据源。这里设置的引用位置仍为“=Sheet1!$D$4:$D$12”,然后单击“确定”按钮完成修改,如图9-23所示。

STEP04:随后会返回“名称管理器”对话框,在列表框中便可以直观地看到修改后的名称效果,如图9-24所示。

“编辑名称”对话框

图9-23 “编辑名称”对话框

修改名称定义效果

图9-24 修改名称定义效果

Excel 2019查看名称定义步骤图解

在定义了多个名称之后,要想全面掌握所有定义的名称,可以使用Excel 2019中的“名称管理器”来查看。以下是具体的操作步骤。

STEP01:打开“定义多个名称.xlsx”工作簿,选择工作表中的任意一个单元格,这里选择A2单元格,切换至“公式”选项卡,在“定义的名称”组中单击“名称管理器”按钮,如图9-19所示。

STEP02:随后会打开“名称管理器”对话框,在列表框中可以清晰地看到当前工作簿中的所有名称及引用位置,如图9-20所示。

图9-19 单击“名称管理器”按钮

查看定义的名称

图9-20 查看定义的名称

Excel 2019创建动态名称步骤图解

利用OFFSET函数与COUNTA函数的组合,可以创建一个动态名称。动态名称是名称的高级用法,可以实现对一个未知大小的区域的引用,此用法在Excel的诸多功能中都可以发挥强大的威力。

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

如果需要创建一个名称来引用C列中的数据,但是又不希望这个名称引用到空白单元格,那么就不得不在每次追加记录以后,都改变名称的引用位置,以适应表格行数的增加。在这种情况下,可以创建动态名称,根据用户追加或删除数据的结果来自动调整引用位置,以达到始终只引用非空白单元格的效果。下面简单介绍创建动态名称的操作技巧。

STEP01:在工作表中任意选择一个单元格,这里选择B2单元格。切换到“公式”选项卡,在“定义的名称”组中单击“定义名称”下三角按钮,在展开的下拉列表中选择“定义名称”选项,打开“新建名称”对话框,如图9-15所示。

图9-14 不断追加记录的表格

选择“定义名称”选项

图9-15 选择“定义名称”选项

STEP02:打开“新建名称”对话框后,在“名称”文本框中输入“Data”,在“引用位置”文本框中输入公式“=OFFSET(Sheet1!$C$4,,,COUNTA(Sheet1!$C:$C)-1)”,然后单击“确定”按钮完成动态名称的创建,如图9-16所示。

图9-16 创建动态名称

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

STEP03:下面可以在C列以外的单元格中通过计算来验证此名称的引用是否正确。比如在B1单元格中输入公式“=SUM(Data)”,按“Enter”键即可得出计算结果,如图9-17所示。

STEP04:继续追加记录,这里在A14:C14单元格区域中增加一行记录,名称“Data”的引用区域就会自动发生改变,B1单元格中的计算结果能够体现这一点,如图9-18所示。

使用动态名称进行计算

图9-17 使用动态名称进行计算

动态适应表格的变化

图9-18 动态适应表格的变化

Excel 2019定义不连续单元格区域

不连续的单元格区域也可以定义为名称,其定义方法如下所示。

使用“Shift”键或者“Ctrl”键配合鼠标准确选中要定义为名称的不连续的单元格区域。切换至“公式”选项卡,在“定义的名称”组中单击“定义名称”下三角按钮,在展开的下拉列表中选择“定义名称”选项打开“定义名称”对话框,然后按照前面的操作方法对名称进行定义即可。

Excel 2019利用常量定义名称

常量也是可以定义为名称的。当某一个数值(例如营业税率)需要经常使用时,则可以将其定义为名称来使用。以下是将常量定义为名称的操作技巧。

在工作表中任意选择一个单元格,切换至“公式”选项卡,在“定义的名称”组中单击“定义名称”下三角按钮,在展开的下拉列表中选择“定义名称”选项,打开如图9-13所示的“新建名称”对话框。然后在“名称”文本框中输入名称名,如“tax”,在“引用位置”文本框中输入当前的营业税率(如0.25),最后单击“确定”按钮完成定义名称的设置即可,如图9-13所示。

Excel 2019利用公式定义名称步骤图解

公式是可以定义为名称的,尤其是在进行一些复杂运算或者实现某些动态数据源效果的时候,经常会将特定的公式定义为名称。

打开“公式定义.xlsx”工作簿,会显示如图9-9所示的工作表。其中,预计销量是可以变动的,此处选择预计销量的值为“80000”。现在需要计算利润值,利润值=预计销量*单位售价–总成本。此时可以定义一个名称为“毛利”,其计算公式为:预计销量*单位售价。

STEP01:在工作表中任意选择一个单元格,这里选择B2单元格。切换至“公式”选项卡,在“定义的名称”组中单击“定义名称”下三角按钮,在展开的下拉列表中选择“定义名称”选项,如图9-10所示。

STEP02:随后会打开“新建名称”对话框,在“名称”文本框中输入“毛利”,在“引用位置”文本框中输入“=利润计算!$B$3*利润计算!$B$4”,然后单击“确定”按钮完成定义名称,如图9-11所示。

STEP03:返回工作表后,在B5单元格中输入公式“=毛利-B2”,然后按“Enter”键返回即可得到利润值,最终结果如图9-12所示。

图9-9 原始数据

选择“定义名称”选项

图9-10 选择“定义名称”选项

图9-11 新建名称

计算利润值结果

图9-12 计算利润值结果