函数篇 – 第29页

Excel 掌握数据类型区分及转换的技巧

Excel数据一般可以分为文本、数值、日期、逻辑、错误等几种类型，其中，日期是数字的一个特殊格式。

此外，数字和数值是两个不同的概念，数字可以以文本的形式出现，也可以是数值、日期等。一般在明确指定的情况下，数字指的是数值型数字。

在Excel函数帮助文件中，经常会看到关于升序的描述为“数值必须按照升序排列：…-2、-1、0、1、2、…、A-Z、FALSE、TRUE”，这是Excel的一个规则，即数字小于文本，文本小于逻辑值，错误值不参与排序。

以上介绍了数据类型及排序规则，接下来介绍逻辑值与数值的关系。

在Excel中，逻辑值只有TRUE和FALSE两个，它们与数值的关系为：

1）在数值运算中，TRUE=1，FALSE=0。

2）在逻辑判断中，FALSE=0，TRUE=所有非0数值。

这两条准则非常重要，在Excel函数公式的简化及计算中用途很广。

介绍完逻辑值与数值的关系之后，接下来介绍一下数据类型转换的有关技巧。

以文本形式存储的数据，如在A1中输入“123”，或者将A1的单元格格式设置为文本的输入“123”，那么该数字不能直接作为数值参与函数计算。这时，公式“=A1=123”将返回FALSE，公式“=SUM（A1：A2）”将无法得到正确的计算结果。

通常用以下6种方法可将A1中以文本形式存储的数字转换为数值型。

=A1*1
=A1/1
=A1+1
=A1-1
=--A1
=value（A1）

注意：混淆文本型数字与数值型数字是许多用户经常犯而又不易觉察的错误，通过使用LEFT、MID、TEXT等文本函数，计算得到的结果都是文本型，如果未进行转换而代入下一步，与之进行不匹配的计算，将返回错误结果。

Excel 如何引用单元格

一个Excel工作表由65536行×256列单元格组成，以左上角第一个单元格为原点，向下、向右分别为行、列坐标的正反向。在Excel中，存在几种引用单元格的方式，下面分别加以介绍。

A1引用样式

默认情况下，Excel使用“A1引用样式”。该样式使用数字1~65536表示行号，用字母A~IV表示列标。例如，第C列和第5行交叉处的单元格的引用形式为“C5”，如果引用整行或者整列，可以省去列标或者行号，比如“1：1”表示第一行。

R1C1引用样式

执行“文件”、“选项”命令，打开“Excel选项”对话框，切换到“公式”选项卡，勾选“R1C1引用样式”复选框，如图9-23所示。利用R1C1引用，可以使用“R”与数字的组合来表示行号，“C”与数字的组合则表示列标，“R1C1样式”可以更加直观地体现单元格的“坐标”概念。

图9-23　设置R1C1引用

三维引用

引用单元格区域时，冒号表示以冒号两边所引用的单元格为左上角和右下角之间的所有单元格组成的矩形区域。

当右下角单元格与左上角单元格处在同一行或者同一列时，这种引用称为一维引用，如A1：D1或者A1：A5，而类似A1：C5，表示以A1单元格为左上角，C5单元格为右下角的5行3列的矩形区域，形成了一个二维的面，该引用称为二维引用。

当引用区域不只在构成二维平面的方向出现时，其引用就是多维的，是一个由不同层次上多个面组成的空间模型。例如公式：“=SUM（Sheet1：Sheet3！A1：C5）”表示对从工作表Sheet1到Sheet3的A1：C5单元格区域求和。在此公式的引用范围中，每个工作表的A1：C5都是一个二维平面，多个二维平面在行、列和表三个方向上构成了三维引用，如图9-24、图9-25、图9-26所示。

图9-24　Sheet1引用

图9-25　Sheet2引用

图9-26　Sheet3引用

Excel 按步查看公式中的计算结果

选中包含公式的单元格，然后切换到“公式”选项卡，单击“公式审核”列表组中的“公式求值”按钮，这时会弹出“公式求值”对话框，通过单击对话框中的“求值”按钮，“求值”域中将按公式计算的顺序逐步显示公式的计算过程。图9-22展示了对公式“=IF（A1>0，SUM（B1：G1），””）”进行“公式求值”的效果。

图9-22　使用“公式求值”对话框查看公式计算过程的5个步骤

Excel 保护和隐藏工作表中的函数公式

如果不希望工作表中的公式被其他用户看到或者修改，可以对其进行保护和隐藏。下面是保护和隐藏工作表中的函数公式的具体操作步骤。

步骤1：按F5键，调出“定位”对话框，如图9-18所示。

步骤2：在打开的“定位”对话框中，单击“定位条件”按钮，在打开的“定位条件”对话框中选择“公式”选项，按“确定”按钮后，将选定工作表中所有包含公式的单元格，如图9-19所示。

图9-18　“定位”对话框

图9-19　设置定位条件

步骤3：选中需要设置保护的单元格，然后右键单击，打开“设置单元格格式”对话框，切换到“保护”选项卡，勾选“锁定”和“隐藏”复选框，单击“确定”按钮。如图9-20所示。

步骤4：切换到“审阅”选项卡，然后单击“保护工作表”按钮，打开“保护工作表”对话框，在该对话框中，设置需要的密码，如图9-21所示。

图9-20　设置单元格保护属性

图9-21　“保护工作表”对话框

Excel 妙用函数公式的查错与监视功能

在使用函数的过程中，经常会遇到一些不可预知的错误，对于普通用户来说，这些不同类型的错误往往是不容易理解的，而且不容易掌握。本节将对Excel中错误的类型，以及查错与监视功能展开综合论述。

错误类型

在使用Excel公式进行计算的时候，可能会因为某种原因而无法得到正确结果，返回一个错误值。表9-1列出了常见的错误值及其含义。

表9-1　常见Excel公式错误值说明

使用错误检查工具

当公式的结果返回错误值的时候，可以使用Excel的错误检查工具，快速查找错误原因。为了更好地使用这项功能，首先应该单击“文件”、“选项”按钮，在“Excel选项”对话框的“公式”选项卡中勾选“允许后台错误检查”复选框，如图9-14所示。

图9-14　设置错误检查选项

如此，当单元格内的公式出现错误的时候，单元格左上角会自动出现一个绿色小三角形，即Excel的智能按钮标记，如图9-15所示。

图9-15　计算错误的单元格中出现智能标记

选定包含错误的单元格单击出现的按钮，显示下拉菜单，如图9-16所示。菜单中包含错误的类型，关于此错误的帮助链接，显示计算步骤，忽略错误，以及在编辑栏中编辑等选项，用户可以方便地选择下一步操作。

图9-16　错误检查智能标记选项

监视窗口

如果用户创建了链接到其他工作簿的数据，可以利用监视窗口随时查看到工作表、单元格和公式函数在改动的时候是如何影响当前数据的。

切换到“公式”选项卡，然后单击“监视窗口”，这时将打开“监视窗口”对话框，通过它可以观察单元格及其中的公式，该对话框可以监视单元格的下列属性：所属工作簿、所属工作表、名称、单元格、值以及公式，如图9-17所示，每个单元格只可以有一个监视窗口。

可以先选择工作表上的一个或多个包含公式的单元格，然后单击“监视窗口”对话框中的“添加监视”按钮，监视所选的单元格，监视窗口可以移动并改变窗口边界来获取最佳视图。

图9-17　“监视窗口”对话框

Excel 利用函数工具提示轻松掌握函数

利用函数工具提示可以轻松快速掌握函数的使用方法，函数工具提示主要包括以下几种操作，下面逐一进行详细介绍。

设置函数工具提示选项

单击“文件”、“选项”按钮，打开“Excel选项”对话框，在该对话框中，切换到“公式”标签，然后按照图9-9所示进行设置，即可启用函数工具提示。

图9-9　设置属性

在单元格中显示函数完整语法

在单元格中输入一个函数公式的时候，按下“Ctrl+Shift+A”组合键可以得到包含该函数完整语法的公式，如键入“=IF（”，然后按下“Ctrl+Shift+A”组合键，则可以在单元格中得到以下结果：

=IF（logical_text，value_if_true，value_if_false）

如果所输入的函数有多种语法，如LOOKUP函数，此快捷菜单将弹出“选定参数”选择窗口，选择其中的一项参数组合后按“确定”按钮，Excel将返回相应的完整语法。

阅读使用函数帮助文件

Excel内置函数多数都有相应的帮助文件，如果想了解某个函数的详细用法，可以通过Excel帮助来实现查看。

步骤1：单击“公式”菜单，然后单击“插入函数”按钮，打开“插入函数”对话框，在“选择函数”列表中选中需要了解的函数（如COUNTIF），单击对话框左下角的“有关该函数的帮助”链接，如图9-10所示。

步骤2：进入“Microsoft Excel 2016帮助”窗口中，将显示该函数的作用、语法及使用示例（向下滑动窗口可以看到），如图9-11所示。

图9-10　“插入函数”对话框

图9-11　函数的作用、语法

“自动求和”的使用

在输入函数时，Excel系统还提供了“自动求和”功能，可以快速插入求和、平均值、计数、最大值、最小值等函数，方便用户快速输入使用频率较高的函数。

步骤1：打开Excel表格，选中要显示自动求和的单元格，切换至“公式”选项卡，单击“函数库”组中的“∑自动求和”右侧的下三角按钮，在展开的下拉列表中单击“求和”选项，如图9-12所示。

步骤2：自动在所选单元格中输入求和公式“=SUM（）”，输入需要引用的单元格，按Enter键即可快速计算出结果，如图9-13所示。

图9-12　单击“求和”选项

图9-13　查看计算结果

Excel 巧妙输入和编辑函数公式

输入和编辑函数公式的时候，存在许多技巧，下面分别进行详细介绍。

使用工具栏按钮输入函数

许多读者接触Excel公式计算都是从求和开始，所以对工具栏上的按钮应该不会陌生。单击“公式”选项卡，即可看到该按钮。用鼠标单击此按钮右侧的倒三角按钮，将出现求和、平均值、计数、最大值、最小值、其他函数（默认为求和）6个选项，如图9-6所示，选择其中一项，就可以在单元格中快捷地插入相对应的常用函数。

图9-6　工具栏按钮

使用插入函数向导

插入函数向导是一个交互式输入函数的对话框，选中任意单元格，按下“Shift+F3”组合键或者单击编辑栏按钮左侧的按钮，即可打开“插入函数”对话框，如图9-7所示。

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

如果对函数所属类别不是很熟悉，可以在此对话框的“搜索函数”文本框里输入简单的描述来寻找合适的函数。比如输入“余数”，然后按Enter键，则Excel会在“选择函数”列表框中“推荐”一些函数供用户使用，如MOD。

如果知道所需函数的类别，可以先在“选项类别”下拉列表中选取分类，然后从“选择函数”列表框中选择函数，当类别中的函数数量较多的时候，可以移动滚动条或者按函数开头字母来快速定位函数。

选定函数后，单击“确定”按钮，Excel会将函数写入编辑栏中，同时弹出“函数参数”对话框，利用此对话框用户可以方便地输入函数所需的各项参数，每个参数框右边会显示该参数的当前值。对话框下方有关于所选函数的一些简单文字描述以及对各个参数的相关说明，如图9-8所示。

图9-8　“函数参数”对话框

手工输入函数

熟练的用户可以直接往单元格中输入函数公式，输入函数公式的方法与输入其他数据没有差别，只要保证输入的内容符合函数公式的结构即可。

公式的编辑和复制

当需要修改公式的时候，可以在编辑栏中移动光标到相应的地方直接修改，或者单击编辑栏左边的按钮，在弹出的“函数参数”对话框中进行修改。

公式可以通过双击或者拖动单元格右下角的填充柄进行复制，也可以用复制、粘贴单元格的方式进行复制。

Excel 函数的作用、构成、参数及种类

Excel中的函数是一些预定义的公式，它可运用一些参数并按照特定的顺序和结构对数据进行复杂的计算。使用函数进行计算可以简化公式的输入过程，并且只需设置函数的必要参数就可进行正确的计算，所以与使用公式进行计算比较，使用函数占用的空间小，速度更快。

认识函数的重要作用

公式是对工作表中数值执行计算的等式，函数则是一些预先编写的，按照特定顺序或者结构执行计算的特殊等式。根据应用领域的不同，Excel函数一般可以分为：逻辑函数、信息函数、日期与时间函数、数学与三角函数、统计、查找与引用、数据库、文本、财务、工程等函数类别，此外还有Excel 4.0宏表函数、扩展函数以及外部函数等。

许多读者在碰到较复杂的函数公式，尤其是函数嵌套公式的时候，往往不知从何读起。其实只要掌握了函数公式的结构等基本知识，就可以像庖丁解牛一样将公式进行分段解读。