函数 – 第2页

Excel 自定义函数

编辑表格时，除了可以使用内置的函数处理表中的数据外，还可以根据自己的实际需要，自定义函数来统计、处理与分析工作表中的数据。

下面以定义个人所得税函数（TAX）为例，假设个人所得税的收缴标准如下。

※　工资低于或等于1500元的免征所得税。

※　工资1500元以上至4500元的超过部分按10％的税率征收。

※　4500元以上至9000元的超过部分按20％的税率征收。

※　9000元以上至35000元的超过部分按25％的税率征收。

※　35000元以上至55000元的超过部分按30％的税率征收。

※　55000元以上至80000元的超过部分按35％的税率征收。

※　高于80000元的超过部分按45％的税率征收。

01　在打开的工作簿中，输入需要的数据内容。

02　按下“Alt+F11”组合键，打开“Visual Basic”窗口。

03　在菜单栏中单击“插入”按钮，在弹出的下拉菜单中单击“模块”命令。

04　在打开的窗口中，输入如下图所示的代码。

05　在菜单栏中单击“文件”按钮，在弹出的下拉菜单中单击“关闭并返回到Microsoft Excel”菜单命令，关闭窗口即可。

alt

在编辑自定义函数过程中，需要注意以下两个事项。

※　自定义函数的VBA结构为“Function…End Function”。

※　包含自定义函数的工作簿文档，必须另存为“Excel启用宏的工作簿”文档格式。

Excel 输入嵌套函数

在使用函数计算某些数据时，可能会将某个函数或函数的返回值作为另一个函数的计算参数来使用，这样的函数表达式就称为嵌套函数。输入嵌套函数的具体操作如下。

01　打开需要操作的工作簿，选中要显示计算结果的单元格，如“E3”单元格，单击编辑栏中的“插入函数”按钮。

02　弹出“插入函数”对话框，在“或选择类别”下拉列表框中根据需要选择函数类别，在“选择函数”列表框中选择需要的函数，单击“确定”按钮。

03　弹出“函数参数”对话框，在“Number1”文本框中设置第一个参数的计算公式，在“Number2”文本框中设置第2个参数的计算公式，依此类推设置需要的参数公式，完成后单击“确定”按钮即可。

alt

Excel 快速查询函数

Excel 2010的功能区中只提供了绝大部分常用的函数，如果在其中找不到需要的函数，则可以通过“插入函数”对话框进行查询。在Excel 2010中查询并使用函数的具体操作如下。

01　打开需要编辑的工作簿，选中要显示计算结果的单元格，如“G4”，单击数据编辑栏中的“插入函数”按钮。

02　弹出“插入函数”对话框，在“或选择类别”下拉列表框中选择函数类别，在“选择函数”列表框中选择需要使用的函数，这里选择“AVERAGE”函数，单击“确定”按钮。

alt

03　弹出“函数参数”对话框，在“Number1”参数框中输入求平均值参数，单击“确定”按钮即可。

实战：Excel 逻辑分段函数应用

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

假设分段函数需要满足以下条件：

当-10≤x≤10时，y=x3;

当10<x<20或-20<x<-10时，y=x;

当x≥20或x≤时，y=x2

如果要在工作表中计算随x变化的y的值，并制作出坐标图，可以按以下步骤进行操作。

STEP01：新建一个空白工作簿，重命名为“分段函数.xlsx”。打开该工作簿，切换至“Sheet1”工作表，在A1:A62单元格区域中输入所需要的数据，效果如图11-24所示。

STEP02：在B1单元格中输入文本“y”，然后在B2单元格中输入以上分段函数的表达式“=IF(AND(A2>=-10,A2<=10),A2^3,IF(OR(A2>=20,A2<=-20),(A2)^2,A2))”，按“Enter”键返回，即可得到y值的计算结果“900”，如图11-25所示。

图11-24　输入数据

图11-25　计算对应y值

STEP03：选中B2单元格，利用填充柄工具向下复制公式至B62单元格，通过自动填充功能来计算出其他的y值，最终结果如图11-26所示。

图11-26　复制公式

STEP04：选中B2:B62单元格区域，切换至“插入”选项卡，在“图表”组中单击“插入散点图或气泡图”下三角按钮，在展开的下拉列表中选中“散点图”选项，如图11-27所示。此时，在工作表中会插入该分段函数的散点坐标图。在“图表标题”文本框中输入“散点图”，最终图表效果如图11-28所示。

图11-27　选择散点图

图11-28　分段函数的图表

Excel 2019保护和隐藏函数公式

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

STEP01：打开“总收入统计.xlsx”工作簿，在工作表页面按“F5”键打开如图10-18所示的“定位”对话框。在对话框中单击“定位”条件按钮，打开“定位条件”对话框。

STEP02：打开“定位条件”对话框后，在“选择”列表框中单击选择“公式”单选按钮，然后单击“确定”按钮返回工作表，如图10-19所示。此时，工作表中会自动选择所有包含公式的单元格。

图10-18　“定位”对话框

图10-19　设置定位条件

STEP03：在选择的单元格处单击鼠标右键，在弹出的隐藏菜单中选择“设置单元格格式”选项，如图10-20所示。

STEP04：随后会打开“设置单元格格式”对话框，切换至“保护”选项卡，依次勾选“锁定”和“隐藏”复选框，然后单击“确定”按钮返回工作表，如图10-21所示。

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

图10-21　设置保护选项

STEP05：如图10-22所示，切换至“审阅”选项卡，在“保护”组中单击“保护工作表”按钮，会打开如图10-23所示的“保护工作表”对话框。在“取消工作表保护时使用的密码”文本框中输入“123456”，然后单击“确定”按钮。

STEP06：此时，会弹出“确认密码”对话框，在“重新输入密码”文本框中再次输入密码“123456”，然后单击“确定”按钮即可完成保护设置，如图10-24所示。

STEP07：若用户想要修改工作表中被保护的公式数据，会弹出如图10-25所示的对话框。

图10-22　单击“保护工作表”按钮

图10-23　设置密码

图10-24　确认密码

图10-25　提示框

STEP08：如果用户想要取消对工作表的保护，在主页将功能区切换至“审阅”选项卡，单击“保护”组中的“撤销工作表保护”按钮，打开如图10-26所示的“撤销工作表保护”对话框。然后在“密码”文本框中输入“123456”，单击“确定”按钮即可。

图10-26　撤销工作表保护

Excel 处理函数参数

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

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

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

=if（b2=a2,true,false）

可以省略为：

=if（b2=a2,true）

部分函数中的参数为TRUE或者FALSE，比如HLOOKUP函数的参数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 2019函数查错与监视

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

错误类型

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

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

使用错误检查工具

当公式的结果返回错误值的时候，可以使用Excel的错误检查工具，快速查找错误原因。

在工作表中切换至“文件”选项卡，在左侧导航栏中单击“选项”标签，打开“Excel选项”对话框。单击“公式”标签，在对应的右侧窗格中向下滑动滑块至“错误检查”列表框下，勾选“允许后台错误检查”复选框，并设置使用“绿色”标识错误，然后单击“确定”按钮完成设置，如图10-14所示。

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

图10-14　设置“错误检查”选项

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

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

监视窗口

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

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

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

图10-17　监视窗口

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

Excel 2019设置函数工具提示

利用函数工具提示，可以轻松快速掌握函数的使用方法。函数工具提示主要包括以下几种操作。

设置函数工具提示选项

STEP01：切换至“文件”选项卡，在左侧导航栏中单击“选项”标签，打开“Excel选项”对话框，如图10-11所示。

STEP02：单击“公式”标签，在对应的右侧窗格中对更改与公式计算、性能和错误处理相关的选项进行相关设置。这里在“计算选项”列表框下选择“自动重算”单选按钮，在“使用公式”列表框下取消勾选“R1C1引用样式”复选框，在“错误检查”列表框下勾选“允许后台错误检查”复选框，并设置使用“绿色”标识错误，然后在“错误检查规则”列表框下取消勾选“引用空单元格的公式”复选框。完成设置后，单击“确定”按钮即可返回工作表，如图10-12所示。

图10-11　单击“选项”标签

图10-12　设置公式属性

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

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

图10-13　利用组合键查询语法

阅读使用函数帮助文件

Excel内置函数多数都有相应的帮助文件，单击“插入函数”对话框或者“函数参数”对话框左下角的“有关该函数的帮助”的链接，或者单击“函数提示工具”左边的函数名称，都可以调出相关函数的帮助窗口。

Excel 2019输入与编辑函数

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

一、使用工具栏按钮输入函数

许多读者接触Excel公式计算都是从求和开始的，所以对功能区上的“自动求和”按钮应该不会陌生。在工作表页面切换至“公式”选项卡，在“函数库”组中单击“自动求和”下三角按钮将出现求和、平均值、计数、最大值、最小值、其他函数6个选项（默认为求和），如图10-7所示。选择其中一项，就可以在单元格中快捷地插入相对应的常用函数。

二、使用插入函数向导

插入函数向导是一个交互式输入函数的对话框，选中任意单元格，按“Shift+F3”组合键或者直接单击“公式”选项卡下的“插入函数”按钮，都可以打开如图10-8所示的“插入函数”对话框。

如图10-9所示，如果对函数所属类别不是很熟悉，可以在此对话框的“搜索函数”文本框里输入简单描述来寻找合适的函数。比如，在文本框中输入“返回两数相除的余数”，然后单击“转到”按钮，则Excel会在“选择函数”列表框中“推荐”供用户选择的函数，如MOD函数。

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

图10-7　函数选项

图10-8　插入函数向导

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

图10-9　使用“搜索函数”文本框

图10-10　函数参数对话框

三、手工输入函数

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

四、公式的编辑

当需要修改公式的时候，可以在编辑栏中移动光标到相应的地方直接修改，或者单击“公式”选项卡下的“插入函数”按钮，在打开的“函数参数”对话框中进行修改。

Excel图表制作之前：函数、定义名称及条件格式

图表的制作过程常常要使用到一些作图必要的辅助计算，这些计算通常由函数完成。Excel提供了非常丰富的工作表函数（如图5.4-8所示），从时间到数值、文本，从数学到工程与科学无所不包，从某种角度而言，Excel的强大在于其有强大的工作表函数，使用者发问最多的Excel功能一般也是函数的使用。

Excel函数对话框

图5.4-8　Excel函数对话框

这些函数通过相互嵌套可以变为功能多样的公式，编制一个嵌套公式其实和使用VBA编程，以及Excel内部处理功能按钮的逻辑没有两样，只是其返回的是数值结果。

定义名称可以看作是Excel对函数公式的一种封装形式。Excel图表系列的数据源、单元格公式中通过对名称的调用，可直接调用定义名称所定义的公式及地址引用。要使用定义名称，在Excel 2003中，插入>名称>定义名称；在Excel 2007/2010中，公式>定义名称，即可在如图5.4-9［Excel 2003］和图5.4-10［Excel 2007/2010］所示的对话框中输入名称，然后在引用位置中输入函数公式即可。当然不是所有的函数公式都支持被直接封装，一般而言，大多数复杂的嵌套数组公式无法被直接封装到名称中。

Excel 2003定义名称

图5.4-9　Excel 2003定义名称

Excel 2007/2010定义名称

图5.4-10　Excel 2007/2010定义名称

注：在定义名称中所有单元格地址引用建议使用绝对引用，不建议在实际输入时使用键盘左右方向键。

定义名称的范围包括工作簿和工作表两种。属于工作簿引用的可以被其他工作簿调用，但修改时必须打开所属工作簿，必须注明相应的工作簿名称，并使用英文半角单引号将工作簿名称括起来。范围是工作表的引用只能使用在当前工作表中，无法被其他工作簿调用。

条件格式是Excel通过函数公式对单元格文字、背景、边框的动态格式设置，Excel 2007/2010还提供了更加丰富的格式化设定。在Excel 2003中，格式>条件格式，即会看到如图5.4-11所示的对话框；在Excel 2007/2010中，开始>条件格式，可见如图5.4-12所示的下拉菜单。Excel 2003支持最多3个条件格式，Excel 2007/2010则为64个。

Excel 2003条件格式设定

图5.4-11　Excel 2003条件格式设定

图5.4-12　Excel 2007/2010