函数 – Excel22

Excel 逻辑函数综合实战

逻辑函数在各个领域中的应用非常广泛。本节将通过一个简单的分段函数实例来介绍逻辑函数在实际中的应用技巧。

某分段函数要满足的条件如下：

当-10≤x≤10时，y=x³。
当10<x<20或-20<x<-10时，y=x。
当x≥20或x≤-20时，y=x²。

如果要在工作表中计算随x变化的y的值，并制作坐标图，则可以使用下面的方法进行操作。

步骤1：首先输入所需要的数据（A列数值直到单元格A62），如图12-23所示。

步骤2：在单元格B2中输入以上分段函数的表达式：=IF（AND（A2>=-10，A2<=10），A2^3，IF（OR（A2>=20，A2<=-20），（A2）^2，A2）），如图12-24所示。

图12-23　输入所需要的数据

图12-24　输入函数表达式

步骤3：指向B2单元格的右下角，拖动填充柄，直到B62单元格，将公式复制到B3~B62单元格区域，如图12-25所示。

步骤4：选中B2~B62单元格区域，然后单击功能区“插入”选项卡中“图表”组的“散点图”按钮，并单击列表中的“散点图”选项，如图12-26所示。

步骤5：此时在工作表中出现该分段函数的散点坐标图，如图12-27所示。

图12-25　复制公式

图12-26　单击列表中的“散点图”选项

图12-27　分段函数的图表

提示：有关图表的制作方法将在后续相关章节中详细介绍。

有时，当用户打开一个工作簿但不做任何更改就关闭时，Excel却提醒是否保存。这是因为Excel文件用到了一些具有volatile特性的函数，即易失性函数。这种函数一个典型的特点是：使用这些函数以后，会引发工作表的重新计算。因此没激活一个单元格，或者在一个单元格输入数据，甚至只是打开工作簿，具有易失性的函数都会自动重新计算。常见的易失性函数有NOW（）、TODAY（）、RAND（）、CELL（）、OFFSET（）、INDIRECT（）、INFO（）、RANDBETWEEN（）等。

虽然易失性函数在实际应用中非常有用，但是如果大量使用易失性函数，则会因为重新计算工作量太大而影响表格的运行速度。

Excel 巧妙处理函数参数

在函数的实际使用过程中，并非总是需要将一个函数的所有参数都写完整才可以计算，可以根据需要对参数进行省略和简化，以达到缩短公式长度或者减少计算步骤的目的。本节将具体讲解如何省略、简写以及简化函数参数。

函数的帮助文件会将其各个参数表达的意思和要求罗列出来，仔细看看就会发现，有很多参数的描述包括“忽略”、“省略”、“默认”等词，而且会注明，如果省略该参数，则表示默认该参数代表某个值。对于参数的省略，是指该参数连同该参数存在所需的逗号间隔都不出现在函数中。

例：判断B2是否与A2的值相等，如果是则返回TRUE，否则返回FALSE。

=IF(B2=A2,TRUE,FALSE)

可以省略为：

=IF(B2=A2,TRUE)

部分函数中的参数为TRUE或者FALSE，比如VLOOKUP函数的参数range_lookup。当指定其为FALSE的时候，可以用“0”来替代。甚至连“0”也不写，而只是用逗号占据参数位置。下面3个公式是等价的。

= VLOOKUP(A1,B1:C10,2,FALSE)
= VLOOKUP(A1,B1:C10,2,0)
= VLOOKUP(A1,B1:C10,2,)

此外，有些针对数值的逻辑判断，可利用“0=FALSE”和“非0数值=TRUE”的规则来简化，比如已知A1单元格的数据只可能是数值的前提下，可以将公式=IF（A1<>0，B1/A1，””），简化为=IF（A1，B1/A1，””）。

Excel 浅谈函数公式的限制和突破

Excel在公式计算方面有其自身的标准与规范，这些规范对公式的编写有一定的限制，主要包括以下几个方面。

公式内容的长度不能超过1024个字符。
公式中函数的嵌套不能超过7层。
公式中函数的参数不能超过30个。

正是因为上面的这些限制条件，使得用户在操作Excel的过程中存在诸多问题。下面以具体实例来详细讲解如何突破函数的7层嵌套以及30个函数参数的限制。

当函数B在函数A中用作参数的时候，函数B为第2级函数。如公式“=IF（A1>0，SUM（B1，G1），””）”，其中SUM函数是第2级函数，因为它是IF函数的参数。如果在SUM函数中继续嵌套函数则为第3级函数，以此类推，Excel函数公式可以包含多达7级的嵌套函数。函数的7层嵌套限制了使用多个函数，要解决这个问题，可以通过定义名称的方法来实现。

例如，要将A1中字符串“我113爱322学43习75E56x5353c256382e85626l54”的数字去掉，利用SUBSTITUTE函数来解决，在B1中输入公式“=SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（A1，0，），1，），2，），3，），4，），5，），6，），7，）”，之后就不能直接再套用函数了，因为从第2个SUBSTITUTE开始，每一个都是前一个的参数，已经达到了7级嵌套。此时，切换到“公式”选项卡，然后单击“定义名称”按钮，打开“新建名称”对话框，将上面的名称定义为X，然后在B1中输入“=SUBSTITUTE（SUBSTITUTE（X，8，），9，）”，这样就可以得到去掉数字的字符串“我爱学习Excel了！”。同样，利用名称定义的方法可解决用IF判断，以及其他函数因为嵌套层数超过7层而导致公式无法输入的问题。

上面介绍了如何突破函数的7层嵌套限制，在实际应用过程中，用户还会受到Excel中30个函数参数的限制，下面简单介绍如何突破30个函数参数的限制。

Excel规定的函数参数最多为30个，如SUM函数、COUNT函数、COUNTA函数、AVERAGE函数、CHOOSE函数等。例如需要计算某些特定单元格中数值的平均值，有以下公式“=AVERAGE（A1，A2，B1，B3，B6，C6，D8，…）”，其中，括号里面的参数多于30个。这个时候，可以在函数参数的两边加上一对括号，形成联合区域来作为参数，相当于只有1个参数。这样，公式就只受到字符个数的限制了。

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宏表函数、扩展函数以及外部函数等。

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

函数的构成

函数的类型虽然多样，但其结构却大同小异，输入函数时，以等号开头，然后是函数名、括号、参数、参数分隔符组成一个完整的函数结构。下面以函数“=COUNTBLANK（A1：C18）”为例，来具体介绍函数的结构。

函数的参数及其说明

函数分为有参数函数和无参数函数。当函数有参数时，其参数就是指函数名称后圆括号内的常量值、变量、表达式或函数，多个参数间使用逗号分隔。无参数的函数由函数名称与“（）”组成，如NA（）。在Excel中绝大多数函数都是有参数的。

在使用函数时，如果想了解某个函数包含哪些参数，可以按照如下方法来查看。

步骤1：选中单元格，在公式编辑栏中输入“=函数名（”，此时可以看到显示出函数参数名称，如图9-1所示。

图9-1　函数参数名称

步骤2：如果想更加清楚地了解每个参数该如何设置，可以单击公式编辑栏前的“”按钮，打开“插入函数”对话框，选择IF函数，单击“确定”按钮，在弹出的“函数参数”对话框中将光标定位到不同参数编辑框中，则可以看到该参数设置的提示文字，如图9-2所示。

函数参数类型举例如下：

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

·在公式“=SUM（B2：B10）”中，括号中的“B2：B10”就是函数的参数，且是一个变量值。

·在公式“=IF（D3=0，0，C3/D3）”中，括号中“D3=0”、“0”、“C3/D3”分别为IF函数的3个参数，且参数为常量和表达式两种类型。

·在公式“=VLOOKUP（$A$9，$A$2：$D$6，COLUMN（B1））”中，除了使用了变量值作为参数，还使用了函数表达式“COLUMN（B1）”作为参数（以该表达式返回的值作为VLOOKUP函数的第3个参数），这个公式是函数嵌套使用的例子。

函数可以嵌套使用，嵌套使用时是将某个函数的返回结果作为另一个函数的参数来使用。有时为了达到某一计算要求，需要嵌套多个函数再设置公式，此时则需要用户对各个函数的功能及其参数有详细的了解。

函数的种类

不同的函数可以达到不同的计算目的，在Excel中提供了300多个内置函数，为满足不同的计算需求，划分了多个函数类别，下面来了解一下函数的类别及其包含的函数。

步骤1：单击“公式”菜单，在“函数库”选项组中显示了多个不同的函数类别，单击函数类别可以查看该类别下所有的函数（按字母顺序排列），如图9-3所示。

步骤2：单击“其他函数”按钮，可以看到还有其他几种类别的函数，如图9-4所示。

图9-3　查看该类别下所有的函数

图9-4　查看其他函数

步骤3：单击“插入函数”按钮打开“插入函数”对话框，在“或选择类别”框下拉菜单中显示了各个函数类别，选择类别后，在下面的列表中将显示出该类别下的所有函数，如图9-5所示。

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

Excel 采购相关函数：AND函数的语法和功能

AND函数是指返回逻辑值，即如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。

图5-9　在E2单元格中输入公式计算入职日期

图5-10　向下填充完成表格

一、语法

AND（logical1,logical2,…）

Logical1、logical2…表示待检测的1~30个条件值，各条件值可为TRUE或FALSE。

二、函数说明

参数必须是逻辑值TRUE或FALSE，或者包含逻辑值的数组（用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量）或引用。

如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。

如果指定的单元格区域内包括非逻辑值，则AND将返回错误值#VALUE!。

三、具体函数实例应用

假如有一些人上街，每个人买零食或者买衣服花费了一定的金额，现在要筛选出既买了衣服，又买了零食的人，那么可以用AND函数进行如下操作。

步骤01：在E2单元格里输入公式“=AND（B2<>””,C2<>””）”，返回逻辑值如图5-11所示。

图5-11　在D2单元格中输入公式返回逻辑值

步骤02：双击D2单元格的右下角，向下填充单元格，效果如图5-12所示。

图5-12　向下填充完成表格

Excel 资产负债表相关函数：MAX函数、ABS函数

MAX函数的语法和功能

说明：返回一个最大数值。

语法：MAX（number1,[number2],…）

MAX函数语法具有下列参数：number1,[number2],…表示number1是必需的，后续数字是可选的。

注意事项：可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果逻辑值和文本不能忽略，请使用函数MAXA来代替。此外，如果参数不包含数字，函数MAX返回0。

示例：我们以负债表中的数值为例。在F13单元格中输入公式“=MAX（F9:F12）”，按Enter键输出结果，可得到数值为220000，它表述的意思为在F9:F12单元格区域中，最大的一个值为220000，如图2-62所示。

ABS函数的语法和功能

说明：返回数字的绝对值。一个数字的绝对值是该数字不带其符号的形式。正数和0返回数字本身，负数返回数字的相反数。

语法：ABS（number）

ABS函数语法具有以下参数：number是必需的参数，需要计算其绝对值的实数。

注意事项：参数必须为数值类型，即数字、文本格式的数字或逻辑值。如果是文本，则返回错误值#VALUE!。

图2-62　输入公式

示例：这里我们新建一个空白表格以计算温差为例来解释ABS函数的应用。在D2单元格中输入公式“=ABS（C2-B2）”，按Enter键输出结果，可得到数值2，它表达的意思是两地温差的“绝对值”，如图2-63所示。

图2-63　两地温差

标签：函数