相册 – 第37页

Excel 利用名称框快速定义名称

前面介绍了利用“新建名称”对话框来定义名称，其实利用名称框来定义名称同样具有方便快捷的特点。下面详细介绍利用名称框快速定义名称的操作技巧。

步骤1：选中需要自定义名称的单元格区域，将光标移动到“名称框”中单击，进入编辑状态，结果如图8-4所示。

图8-4　选中单元格并进入名称编辑状态

步骤2：输入需要定义的名称，然后单击Enter键，即可完成名称的定义，如图8-5所示。

图8-5　成功定义名称

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

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

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

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

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

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

图8-2　设置名称属性

图8-3　引用名称

Excel 将单元格引用更改为命名区域

如果在公式中输入单元格引用后定义了单元格引用的名称，则通常需要更新对已定义名称的现有单元格引用。

将单元格引用更改为命名区域的具体操作步骤如下。

步骤1：可执行下列操作之一。

选择希望将单元格引用替换为已定义名称的公式所在的单元格区域。
选择任意一个空单元格，以便将工作表上所有公式中的引用更改为名称。

步骤2：选择“公式”选项卡中“定义的名称”组的“定义名称”右侧的按钮，从弹出的菜单中选择“应用名称”命令，弹出“应用名称”对话框，如图7-16所示。

图7-16　“应用名称”对话框

步骤3：在“应用名称”列表框中选择一个或多个名称，单击“确定”按钮即可。

Excel 断开外部引用的链接

断开外部引用的源工作簿链接时，源工作簿中使用该值的所有公式都将转换成它们的当前值。例如，外部引用“=SUM（[work.xls]Sheet1！A1：A4）”将转换为一个数据。

断开外部引用的链接的具体操作步骤如下。

步骤1：选择“数据”选项卡“连接”单元组中的“编辑链接”按钮，弹出如图7-13所示的“编辑链接”对话框。

图7-13　“编辑链接”对话框

提示：如果该文件不包含链接信息，那么“编辑链接”命令将呈现灰色的不可用状态。

步骤2：在“源”列表中，单击要断开的链接。

如果要选择多个链接对象，按住Ctrl键的同时，然后单击每个链接对象。
如果要选择所有链接，按快捷键“Ctrl+A”。

步骤3：单击“断开链接”按钮，弹出如图7-14所示的提示信息对话框。

图7-14　提示信息

步骤4：单击“断开链接”按钮，此时“编辑链接”对话框如图7-15所示。

图7-15　“编辑链接”对话框

Excel 创建对其他工作表的单元格引用

在进行公式运算时，很多情况下都需要使用其他工作表中的数据来参与计算。在引用其他工作表单元格中的数据时，通常的格式引用是：‘工作表名’！数据源地址。

步骤1：选中要引用其他工作表的单元格，在该单元格中输入函数，例如这里输入：“=SUM（”，如图7-9所示。

图7-9　输入部分公式

步骤2：单击“1-3月份销售量”工作表标签，选中要参与计算的单元格或单元格区域，例如这里选择“B2：D2”，如图7-10所示。

图7-10　选择被引用的单元格

步骤3：返回“上半年总销售量”工作表，按回车键，此时的工作表如图7-11所示。

图7-11　引用其他工作表中的数据

步骤4：单击单元格B2，将鼠标移动到该活动单元格右下角的黑色实心方块上，当指针变为黑色“十”字时向下拖动鼠标至单元格B6，就可以把B2中的公式复制到其他单元格中，如图7-12所示。

图7-12　将公式填充其他单元格

相对引用

相对引用单元格的方法非常简单，接下来通过一个小实例说明相对引用方式的方法。

步骤1：选中D2单元格，可以看到公式编辑栏中该单元格的公式，如图7-5所示。

图7-5　目标数据

步骤2：将指针移动到D2单元格右下角的黑色实心方块上，当指针变为黑色“十”字时，向下拖动鼠标至单元格D6，这时已将公式复制至其他单元格中。

步骤3：单击复制得到的公式，例如单击单元格D5，此时发现D5中的公式为“=B5+C5”，如图7-6所示。

绝对引用

单元格的绝对引用指的是把公式复制或移动到其他位置时，公式中的固定单元格地址保持不变。如果要对单元格采用绝对引用的方式，则需要使用“$”符号为标识。接下来还是通过一个小例子说明如何对单元格进行绝对引用。

图7-6　数据源自动改变

步骤1：选中D2单元格，可以看到公式编辑栏中该单元格的公式，如图7-7所示。

图7-7　目标数据

步骤2：将指针移动到D2单元格右下角的黑色实心方块上，当指针变为黑色“十”字时，向下拖动鼠标至单元格D5，这时已将公式复制至其他单元格中。

步骤3：单击复制得到的公式，例如单击单元格D5，此时发现D5中的公式为“=$B$2*C5”，如图7-8所示。

图7-8　绝对引用时地址不变

Excel 利用数组模拟AND和OR

AND（与关系）：当两个或多个条件必须同时成立时才判定为真时，则称判定与条件的关系为逻辑与关系，就是平常所说的“且”。

OR（或关系）：当两个或多个条件只要有一个成立时就判定为真时，则称判定与条件的关系为逻辑或关系。

图7-3　目标数据

在Excel中，*和+可以与逻辑判断函数AND和OR互换，但在数组公式中，*和+号能够替换AND和OR函数，反之则行不通。这是因为AND函数和OR函数返回的是一个单值TRUE或FALSE，如果数据公式要执行多重计算，单值不能形成数组公式各参数间的一一对应关系。例如要统计如图7-3所示的表格中基本工资为2000~2500的员工人数，就是说统计工资高于2000且低于2500的人数，由此可以判定该条件是一个“逻辑与”关系。

·如果在单元格A8中输入公式“=SUM（AND（C3：C7>2000，C3：C7<2500）*1）”，按组合键“Ctrl+Shift+Enter”后，返回的结果是0。

因为公式中“C3：C7>2000”返回的值是{FALSE；TRUE；TRUE；FALSE；FALSE}。而公式“C3：C7<2500”返回的值是{TRUE；TRUE；TRUE；TRUE；TRUE}。

这两个公式返回的值再逻辑与，则返回的值是FALSE。所以计算结果“=SUM（FALSE*1）=SUM（0*1）=0”。因此返回的结果为0。

·在单元格B8中输入公式“=SUM（（C3：C7>2000）*（C3：C7<2500））”，按组合键“Ctrl+Shift+Enter”后，返回的结果是2。

这是因为在公式中“（C3：C7>2000）*（C3：C7<2500）”

={TRUE；FALSE；TRUE；TRUE；TRUE}*{TRUE；TRUE；FALSE；TRUE；FALSE}

={1；0；1；1；1}*{1；1；0；1；0}

={1；0；0；1；0}

所以公式的计算结果为“=SUM（{1；0；0；1；0}）”=2。

如图7-4所示。

图7-4　返回结果

Excel 公式兼容性问题

Excel 2016公式与Excel 2007和早期版本兼容。依次选择“公式”选项卡“函数库”单元组中的“其他函数”按钮，然后从弹出的菜单中选择“兼容性”命令，在下一级菜单中选择“插入函数”命令，如图7-1所示。最后弹出如图7-2所示的“插入函数”对话框。

图7-1　选择“插入函数”命令

图7-2　“插入函数”对话框

Excel 工作簿网络应用：获取网上数据、创建web页面

随着网络的发展，人们不仅能够通过网络获得需要的信息，而且可以将自己的信息发布到网络上。网络中也包含适合Excel进行分析处理的信息，Excel可以直接从网络获得这些信息并对数据进行分析处理。本节将介绍Excel 2016网络应用的有关知识。

获取网上数据

在实际工作中，有时需要对网页上的一些数据信息进行分析。在Excel中，可以通过创建一个Web查询将包含在HTML文件中的数据插入Excel工作表中。下面将介绍在工作表中创建Web查询的操作方法。

步骤1：启动Excel并创建一个工作表。在“数据”选项卡中单击“获取外部数据”组的“自网站”按钮，如图6-45所示。

图6-45　单击“自网站”按钮

步骤2：打开“新建Web查询”对话框，在对话框的“地址”下拉列表框中输入Web页的URL地址，单击“转到”按钮打开相应的网页，如图6-46所示。

步骤3：打开“新建Web查询”对话框，单击“选项”按钮打开“Web查询选项”对话框，在该对话框中对查询的选项进行设置。这里采用默认设置即可，完成设置后单击“确定”按钮，如图6-47所示。

图6-46　打开指定的网页

图6-47　“Web查询选项”对话框

提示：“Web查询选项”对话框的“格式”栏中的设置项用于设置导入页面时使用的格式。勾选“将<PRE>块导至列中”复选框，可以对导入列中预设格式的数据进行分隔，只要使用分隔符来确定在列中分配数据的方式即可。勾选“连续分隔符号视为单个处理”复选框，则可以在导入数据时将多个连续分隔符标记或结束标记作为一个符号来处理。勾选“全部使用相同的导入设置”复选框，则可以对Web页上的所有预设格式的内容使用“连续分隔符视为单个处理”设置。

步骤4：在“新建Web查询”对话框中可以看到当前Web页面由若干数据表构成，单击数据表左侧的箭头按钮“”选择表格。完成选择后，箭头按钮变为“”。完成选择后鼠标单击“导入”按钮，如图6-48所示。

图6-48　选择区域

提示：选择区域后，单击按钮“”将能够取消对该区域的选择。如果需要选择页面中的所有区域，单击页面左上角的箭头按钮“”即可。

步骤5：打开“导入数据”对话框，在对话框中设置数据放置的位置。这里将其放置在从当前工作表的第一个单元格开始的单元格区域，然后单击“属性”按钮，如图6-49所示。

图6-49　“导入数据”对话框

步骤6：打开“外部数据区域属性”对话框，使用该对话框对外部数据的区域属性进行设置。这里只对“名称”进行设置，其他设置使用默认值，如图6-50所示。完成设置后单击“确定”按钮。

图6-50　“外部数据区域属性”对话框

提示：如果需要数据能够刷新，应勾选“保存查询定义”复选框。如果数据源在查询时需要密码，可勾选“保存密码”复选框，在第一次输入密码后，以后的查询操作就不再需要输入密码了。勾选“允许后台刷新”复选框，则在后台进行查询而不影响当前的Excel操作。勾选“刷新频率”复选框，可在其后的增量框中输入数据刷新的时间间隔。勾选“打开文件时刷新数据”复选框时，在打开保存了查询定义的工作簿时可以对外部数据进行自动刷新。

步骤7：完成设置后单击“导入数据”对话框中的“确定”按钮，Excel从Internet上获取指定页面的数据信息，获取的数据在工作表中显示出来，如图6-51所示。