Excel 快速选择名称对应的单元格区域

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

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

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

图8-22 使用名称框选定名称区域

图8-23 使用“定位”对话框选定名称区域

Excel 创建动态名称

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

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

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

步骤1:切换到“公式”选项卡,然后单击“定义的名称”组中的“定义名称”按钮,打开“新建名称”对话框。

步骤2:在打开的“新建名称”对话框中,在“名称”后面的输入框中输入“Data”,在“引用位置”文本框中输入公式“=offset(Sheet1!$C$4,,,counta(Sheet1!$C:$C)-1)”,如图8-19所示。

图8-18 不断追加记录的表格

图8-19 创建动态名称

步骤3:单击“确定”按钮,即可完成动态名称的创建。

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

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

如果继续追加记录,名称“Data”的引用区域就会自动发生改变,B1中的计算结果能够体现这一点,如图8-21所示。

图8-20 使用动态名称进行计算

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

Excel 编辑名称引用

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

在通常情况下,用户会在编辑名称引用的时候遇到一些麻烦,例如,图8-16显示了一个已经存在的名称,该名称的引用位置是:=Sheet3!$A$1:$E$10。

假设需要把引用位置修改为“=Sheet3!$A$5:$E$15”,操作方法是将光标定位到“=Sheet3!$A$”之后,按Del键删除1,输入5,然后使用右箭头键将光标往右移,希望能够将末尾的10修改为15。可是,当按下右箭头的时候,光标并没有发生移动,引用内容却发生了改变,如图8-17所示。

图8-16 待编辑的名称

图8-17 编辑时按箭头键的结果

出现上述情况,在于“引用位置”文本框默认情况下处于“指向”模式,此时箭头键的作用是在工作表中选定区域而不是移动光标。解决方法是在编辑前将光标定位到“引用位置”文本框,然后按F2键,切换到“编辑”模式,再进行内容编辑。

Excel 查看当前工作簿中的所有名称

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

打开需要查看名称的工作簿,切换到“公式”选项卡,在“定义的名称”工具栏中单击“名称管理器”按钮,打开“名称管理器”,当前工作簿中的所有名称及引用位置都可以清晰地看到,结果如图8-15所示。

图8-15 查看定义的名称

Excel 利用“新建名称”对话框快速定义名称

在Excel 2016中,利用“名称定义”的功能,不仅可以快速定义名称,还可以方便地管理名称。下面通过具体实例来讲解利用“名称定义”对话框快速定义名称的操作技巧。

步骤1:输入原始数据,然后选中要定义为名称的单元格区域,如图8-1所示。

步骤2:切换到“公式”选项卡,然后在“定义的名称”组中单击“定义名称”右侧的箭头,从中选择“定义名称”命令,打开“新建名称”对话框,在该对话框中对名称的各种属性进行设置,如图8-2所示为设置结果。

步骤3:单击“确定”按钮,即可完成名称的定义。这时单击工作表左上角的“名称框”编辑栏,即可看到刚刚定义的名称,单击选中该名称,即可将工作表中的相应单元格区域选中,如图8-3所示。

图8-1 输入原始数据并选中操作区域

图8-2 设置名称属性

图8-3 引用名称

Excel 名称定义的规则何在

在定义单元格、数值、公式等名称的时候,需要遵循一定的规则,具体要求如下所示。

  • 名称的第一个字符必须是字母、数字或者下划线,其他字符可以是字母、数字、句号或者下划线等符号。
  • 名称长度不能超过255个字符,字母不区分大小写。
  • 名称之间不能有空格符。
  • 名称不能与单元格的名称相同。
  • 同一工作簿中定义的名称不能相同。

Excel 中名称定义的重要作用

在Excel 2016中使用名称定义,可以极大地简化公式,从而提高工作效率。具体来说,Excel中名称定义具有以下重要作用。

减少输入的工作量:如果在一个文档中需要输入很多相同的文本,可以使用定义的名称。例如,定义国家=“中华人民共和国”,那么在需要输入该文本的位置处输入“=国家”,都会显示“中华人民共和国”。

快速定位:例如在大型数据库中,经常需要选择某些特定的单元格区域进行操作,那么可以事先将这些特定的单元格区域定义为名称。当需要定位时,可以在“名称框”下拉菜单中选择名称,程序会自动选择特定的单元格区域。

方便计算:简化了编辑公式的时候对单元格区域的引用,尽可能地减少出错概率。

Excel 在公式中使用名称

在公式中使用名称比直接引用单元格区域具有很多的优点,既可以避免公式过于冗长,过于复杂,还可以增强公式的可读性,而且使公式的录入相对更加简便。

步骤01:在公式中直接输入名称。如果用户能够记清要引用的单元格区域的名称,在输入公式时可直接输入名称作为参数,如在单元格E2中输入公式“=VLOOKUP(C2,data1,2,FALSE)”,按下Enter键后得到如图6-19所示的计算结果。

步骤02:从下拉列表中选择名称。如果用户记不清名称也没关系,当需要输入名称作为参数时,在“定义的名称”组中单击“用于公式”下三角按钮,从展开的下拉列表中选择要使用的名称即可,如图6-20所示。

步骤03:设置公式引用期初余额。在单元格F2中输入公式“=VLOOKUP(C2,datal,7,FALSE)”,按下Enter键后向下复制公式,得到如图6-21所示的结果。

步骤04:在单元格I2中输入公式“=IF(D2=”预收账款”,F2+H2-G2,F2+G2-H2)”,按下Enter键后,向下复制公式,计算出期末余额数据,如图6-22所示。

图6-19 在公式中直接输入名称

图6-20 从下拉列表中选择名称

图6-21 设置公式引用期初余额

图6-22 设置公式计算期末余额