下载掌阅APP，畅读海量书库
立即打开
函数是应用于公式中的一个最重要的元素，有了函数的参与，才可以解决非常复杂的手工运算，甚至是无法通过手工完成的运算。函数是Excel软件中一项强大的功能，共有9大类函数，如数学函数、统计函数、文本函数、查找函数等，不同的函数能解决不同的问题，如AVERAGE函数计算一组数据的平均值、IPMI函数计算贷款分期偿还额、SYD函数计算固定资产折旧值、VLOOKUP函数查找满足条件的值等。
关键点： 了解函数的构成、学习函数的用法
操作要点： “函数参数” 对话框、Excel帮助
应用场景： 函数对于初学者而言有些难度，首先要了解函数的构成并掌握学习函数的方法，从易到难逐步学会用函数解决工作中遇到的问题
函数的结构以函数名称开始，后面是左圆括号，接着是参数，各参数间使用逗号分隔，参数设置完毕输入右圆括号表示结束。
“=IF(B3=0,0,C3/D3)” 公式中就使用了一个IF函数，其中IF是函数名称，B3=0、0、C3/D3是IF函数的3个参数。
单一函数不能返回值，因此必须以公式的形式出现，即前面添加上 “=” 号才能得到计算结果。
函数必须要在公式中使用才有意义，单独的函数是没有意义的，在单元格中只输入函数，返回的是一个文本而不是计算结果，如图4-46所示，因为没有使用 “=” 号开头，所以返回的是一个文本。
图4-46
另外，函数的参数设定必须满足此函数的规则，否则也会返回错误值，如图4-47所示，因为 “合格” 与 “不合格” 是文本，应用于公式中时必须要使用双引号，当前未使用双引号，所以参数不符合规则。
图4-47
在函数使用过程中，参数的设置是关键，可以通过插入函数参数向导学习函数的设置，还可以通过Excel内置的帮助功能学习函数的用法。
（1）了解函数的参数
① 选中单元格，在编辑栏中输入 “=函数名()” ，将光标定位在括号内，此时可以显示出该函数的所有参数，如图4-48所示。
②
如果想更加清楚地了解每个参数该如何设置，可以单击编辑栏前的
按钮，打开
“插入函数”
对话框，选择函数后打开
“函数参数”
对话框，将光标定位到不同参数编辑框中，下面会显示对该参数的解释，从而便于初学者正确设置参数，如图4-49、图4-50所示。
图4-48
图4-49
图4-50
（2）帮助功能学习函数
①
在编辑栏中单击
按钮，打开
“插入函数”
对话框，选中要使用的函数，单击
“有关该函数的帮助”
按钮，如图4-51所示，打开
“Excel帮助”
窗格。
图4-51
② 在打开的 “Excel帮助” 窗格中可以看到显示了该函数的用法与语法，如图4-52所示。
图4-52
关键点： 求和与按条件求和
操作要点： “自动求和” 按钮、通过文中公式解析学会SUMIF与SUMIFS函数的参数规则
应用场景： 对一个区域快速求和运算在办公中极为常用。另外有时也需要只对满足条件的数据进行求和，满足单条件时使用SUMIF函数，满足双条件时使用SUMIFS函数
已知1号仓库和2号仓库各个产品的库存量，可以使用SUM函数快速计算出总的库存量。
① 选中B10单元格，单击 “公式” 选项卡，在 “函数库” 选项组中单击 “自动求和” 按钮，此时自动插入SUM函数并且给出默认的数据源，如图4-53所示。
② 利用鼠标选取的方式将引用的单元格区域更改为B2:C8，如图4-55所示，按Enter键，即可计算出库存总量，如图4-56所示。
图4-53
在 “自动求和” 下拉菜单中预置了如图4-54所示的几种最常用的函数。当需要使用时可以从此处选择使用，用法都与SUM函数一样。
图4-54
图4-55
图4-56
在选择 “自动求和” 下拉菜单中的常用函数计算时，系统默认引用计算单元格所在同一行或同一列数据源，用户在计算时，需要根据实际情况手动调整用于计算的单元格区域。
当前表格中统计了本月员工的销售记录，现在需要统计出指定销售员的总销售额。
① 选中E2单元格，在编辑栏输入公式：
=SUMIF(B2:B11,"何慧兰",C2:C11)
② 按Enter键，即可计算出销售员 “何慧兰” 的总销售额，如图4-57所示。
图4-57
SUMIF函数是指对区域中满足条件的单元格求和。它的参数设置详解如下：
① 用于条件判断的单元格区域。
② 指定的条件。
③ 用于求和计算的单元格区域。
本例公式表示先判断B2:B11单元格区域中哪些单元格为 “何慧兰” ，然后将对应于C2:C12单元格区域上的值求和。
当前表格统计了公司3月各品牌产品在各门店的销售额，为了对销售数据进一步分析，需要计算 “新都汇店”“玉肌” 品牌产品的总销售额，即要同时满足两个条件。
① 选中F2单元格，在编辑栏中输入公式：
=SUMIFS(D2:D14,B2:B14,"新都汇店",C2:C14,"玉肌")
② 按Enter键，即可同时满足店面要求与品牌要求，利用D2:D14单元格区域中的值求和，如图4-58所示。
图4-58
SUMIFS函数是指对区域中满足多个条件的单元格求和。它的参数设置详解如下：
① 指定用于求和运算的单元格区域。
② 指定第一个条件判断的区域与第一个条件。
③ 指定第二个条件判断的区域与第二个条件。
本例公式表示先判断B2:B14单元格区域中哪些单元格为 “新都汇店” ，再判断C2:C14单元格区域中哪些单元格为 “玉肌” ，当同时满足这两个条件时，取对应在D2:D14单元格区域上的值，并进行求和计算。
如图4-59所示，要求一次性计算出所有销售员各自的总销售额。这时的公式有两个注意点：
（1）注意单元格区域的引用方式（不能变的要绝对引用）。
（2）指定条件时也使用引用单元格的方式。
图4-59
关键点： 求平均值与按条件求平均值
操作要点： AVERAG函数、AVERAGEIF函数
应用场景： 对一个区域快速求平均值运算在办公中极为常用。另外有时也需要只对满足条件的数据进行求平均值，此时需要使用AVERAGEIF函数
已知某班级某次测试的成绩，需要快速计算出平均分。
① 选中B12单元格，在编辑栏输入公式 “=AVERAGEA(B2:B11)” 。
② 按Enter键，即可计算出平均成绩，如图4-60所示。
图4-60
当前表格中统计了某年级某次竞赛的成绩（每班均有5人参赛），现在需要统计出各个班级的平均成绩。
① 选中G2单元格，在编辑栏输入公式 “=AVER AGEIF($B$2:$B$11,F2,$D$2:$D$11)” 。
② 按Enter键，即可计算出 “（1）班” 的平均分，如图4-61所示。
图4-61
③ 选中G2单元格，拖动填充柄即可向下复制公式得到其他班级的平均分，如图4-62所示。
图4-62
AVERAGEIF函数用于返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。它的参数设置详解如下：
① 用于条件判断的单元格区域。
② 指定的条件。
③ 用于求平均值计算的单元格区域。
本例公式表示先判断B2:B11单元格区域中哪些为与F2中相同的班级，然后将满足条件的对应在D2:D11单元格区域的数据求平均值。
如图4-63所示，要求统计出所有电视类商品的平均销量，这时注意在判断条件中可以使用通配符。
图4-63
关键点： 求最大值与第几个最大值
操作要点： 通过文中公式解析学会MAX、LARGE函数的参数规则
应用场景： MAX用于求一个区域的最大值，LARGE函数用于求取一个区域中的第几个最大值。只求最大值时二者均可，如果求指定的第几个最大值则只能使用LARGE函数
已知第三季度各月各个店铺的销量，使用MAX函数查找最高销量。
① 选中B6单元格，在编辑栏输入公式：
=MAX(B2:E4)
② 按Enter键，即可返回第三季度最高销量，如图4-64所示。
图4-64
除了求最大值外，还可以使用MIN函数来快速求取最小值。例如，使用公式 “=MIN(B2:E4)” ，可以快速求取B2:E4单元格区域中的最小值。
MAX函数本身不具备按条件判断的功能，因此要实现按条件判断则需要如同本例一样利用数组公式实现。此公式非常实用，读者可记住这种应用方法。表格中统计的是某次竞赛的成绩统计表，其中包含有3个班级，现在需要分别统计出各个班级的最高分。
① 选中G2单元格，在编辑栏中输入公式：
=MAX(IF($C$2:$C$16=F2,$D$2:$D$16))
② 按Enter键，即可计算出 “二（1）班” 的最高分，如图4-65所示。”
图4-65
③ 选中G2单元格，拖动右下角的填充柄向下复制公式即可一次得到每个班级的最高分，如图4-66所示。
图4-66
要想让MAX函数也具备条件判断的功能，需要配合IF函数来进行公式设置。本例公式详解如下：
① 因为是数组公式，所以用IF函数依次判断C2:C16单元格区域中的各个值是否等于F2单元格的值，如果等于返回TRUE，否则返回FALSE。返回的是一个数组。
② 将 ① 步数组依次对应D2:D16单元格区域取值， ① 步数组中为TRUE的返回其对应的值， ① 步数组为FALSE的返回FALSE。结果还是一个数组。
公式最终对 ② 步数组中的值取最大值。
当前表格中统计了10位评委的打分情况，要求统计出每位参赛选手的去除最高分后的最高分，即第二个最大值。
① 选中B13单元格，在编辑栏中输入公式：
=LARGE(B2:B11,2)
② 按Enter键即可统计出B2:C11单元格区域中的第二个最大值，如图4-67所示。
图4-67
③ 选中B13单元格，拖动右下角的填充柄向右复制公式（复制到D13单元格），可依次返回每位参赛者分数列表中的第二个最大值，如图4-68所示。
图4-68
LARGE函数返回某一数据集中的某个最大值。其参数详解如下：
① 指定需要从中查询第k个最大值的数组或数据区域。
② 指定要返回第几个最大值。
本例公式表示B2:B11单元格区域中寻找第2个最大值，即评分排名第二的值。
如图4-69所示，要求返回各个班级中前三名的成绩。要同时返回前三名的成绩，就需要用到数组的部分操作。需要一次性选中要返回结果的3个单元格，然后配合IF函数对班级进行判断。要求解 “2班” 的前三名成绩时，只要将F2:F4单元格区域中的公式复制到G2:G4单元格区域中即可。
图4-69
关键点： 按条件计数
操作要点： 通过文中公式解析学会COUNTIF函数的参数规则
应用场景： COUNTIF函数可以先判断条件然后对满足条件的数字进行计数，如统计工资额大于5000元的人数、学历为 “研究生” 的人数等
当前表格统计了公司员工的姓名、性别、部门、年龄及学历信息，需要统计 “本科” 学历员工的人数。
① 选中G2合并单元格，在编辑栏输入公式：
=COUNTIF(E2:E14,"本科")
② 按Enter键，即可统计出学历为 “本科” 的人数，如图4-70所示。
图4-70
COUNTIF函数用于对区域中满足单个指定条件的单元格进行计数。它的参数设置详解如下：
① 表示需要计算其中满足条件的单元格数目的单元格区域。
② 指定的条件。
本例公式表示统计出E2:E14单元格区域中 “本科” 数据的记录数。
当前表格中统计了各个学生的总成绩，要求计算出总分在600分以上的学生有多少人。
① 选中E2单元格，在编辑栏输入公式：
=COUNTIF(C2:C14,">600")
② 按Enter键，即可计算出大于600分的人数，如图4-71所示。
图4-71
本例公式表示统计出C2:C14单元格区域中大于600的数据的记录数。注意，在设置条件时也可以使用比较运算符。
要求统计出 “90以上”“80～90”“80以下” 几个分数段的人数。
统计 “90以上” 与 “80以下” 分数段人数时可以直接使用COUNTIF函数，如图4-72所示。而统计 “80 ～ 90” 这个分数段人数时则需要使用COUNTIFS函数，因为它涉及两个判断条件（"<90"和">=80"），如图4-73所示。COUNTIFS函数计算某个区域中满足多重条件的单元格数目。在设置COUNTIFS函数的参数时逐一写出条件判断区域与判断条件即可。
图4-72
图4-73
关键点： 按条件判断并返回指定的值
操作要点： 通过文中公式解析学会IF函数参数的规则
应用场景： 条件判断即判断给定条件的 “真”“假” ，如果为真返回某个指定值；如果为假返回某个指定值。这在日常工作中是极为常用的，如当考核成绩达到90分时刚好录取，当库存量小于10件时提示补货等
当前表格中统计了各商品的库存数量，要求建立公式起到库存提醒的作用，即当库存小于10时提示 “补货” 。
① 选中D2单元格，在编辑栏输入公式：
=IF(C2<=10,"补货","")
② 按Enter键，即可根据C2单元格的数值返回相应值（即如果小于等于10件就显示 “补货” 文字），如图4-74所示。
③ 选中D2单元格，拖动右下角的填充柄向下复制公式即可批量判断各商品的库存情况，如图4-75所示。
图4-74
图4-75
IF函数是根据指定的条件来判断其 “真” （TRUE）、 “假” （FALSE），从而返回其相对应的内容。其参数设置详解如下：
① 表示判断条件，当此条件为真时返回 ② 步指定的值，当此条件为假时返回 ③ 步指定的值。
② 表示当 ① 步为真时想返回的值。
③ 表示当 ① 步为假时想返回的值。
本例公式表示如果C2单元格数据小于等于10，则返回 “补货” 文字，否则返回空值。
当前表格统计了一系列产品的定价，现在需要对部分产品进行调价。具体规则为：当产品是 “十年陈” 时，价格上调50元，其他产品保持不变。
要完成这项工作，需要公式能自动找出 “十年陈” 这项文字，从而实现当满足条件时进行提价运算。由于 “十年陈” 文字都显示在产品名称的后面，因此可以使用RIGHT这个文本函数实现提取。
① 选中D2单元格，在编辑栏输入公式：
=IF(RIGHT(A2,5)="（十年陈）",C2+50,C2)
② 按Enter键，即可根据A2单元格的中的产品名称，判断其是否满足 “十年陈” 这个条件，从图4-76中可以看到当前是满足的，因此计算结果是 “C2+50” 的值。
图4-76
③ 选中D2单元格，拖动右下角的填充柄向下复制公式即可批量判断各商品是否满足提价条件，如图4-77所示。
图4-77
IF函数的第一个判断条件可以嵌套其他函数，从而实现更加灵活的条件判断。例如，本例中要想完成对是否包含有 “十年陈” 文字这个条件的判断，则需要公式能自动找到 “十年陈” 这项文字，由于 “十年陈” 文字都在显示产品名称的后面，因此可以使用RIGHT这个文本函数实现提取。RIGHT函数用于从给定的文本字符的最右侧开始提取，提取的字符数用第2个参数指定：
① 从A2单元格中的最右侧开始共提取5个字符，然后判断是否是 “（十年陈）” 。
② 如果 ① 步为真时执行“C2+50”运算。
③ 如果 ① 步为假时返回C2，即不进行提价。
在设置 “RIGHT(A2,5)="（十年陈）"” ，注意 “（十年陈）” 前后的括号是区分全半角的，即如果在单元格中是使用的全角括号，那么公式中也需要使用全角括号，否则会导致公式错误。
如图4-78所示，要求根据库存数量一次性返回 “补货” （小于等于10件时）、 “准备” （小于20件时）、 “充足” （大于等于20件时）的提示。
公式进行了IF函数的嵌套，为了同时满足多条件的判断，IF函数可以最多达7层嵌套。
图4-78
关键点： 按所给的查找对象自动匹配数据
操作要点： 通过文中公式解析学会LOOKUP、VLOOKUP函数的参数规则
应用场景： 根据给定的查找对象自动为这个对象匹配其他值。这在日常工作中是极为常用的，如实现考生分数的自动查询、实现任意产品库存量的查询、实现产品销售单价的查询等
当前表格为学生成绩表，要求通过任意学生的姓名快速查询成绩。
① 选中 “姓名” 列的任意单元格，在 “数据” 选项卡的 “排序和筛选” 组中单击 “升序” 按钮，先将此列升序排序，如图4-79所示。
图4-79
② 首先输入查询对象，选中F2单元格，在编辑栏输入公式：
=LOOKUP(E2,B2:B14,C2:C14)
③ 按Enter键，即可查找出指定姓名的总分，如图4-80所示。
图4-80
④ 在更改E2单元格中的查询对象，按Enter键即可重新快速查询，如图4-81所示。
图4-81
LOOKUP函数在单行区域或单列区域中查找值，然后返回第二个单行区域或单列区域中相同位置的值。其参数设置详解如下：
① 指定查找对象。
② 用于条件判断的只包含一行或一列的区域。
③ 用于返回值的只包含一行或一列的区域。
本例公式用于在B2:B14中查找E2中指定的姓名，找到后返回对应在C2:C14单元格区域中相同位置上的值。
LOOKUP函数查找时，用于查找的行或列的数据都应按升序排列。如果不排列，在查找时会出现查找错误。
LOOKUP函数还有一个语法，即数组型语法，即只有两个参数，第一个是查找值，第二个是数组。表示在数组的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。
在VLOOKUP函数中通过设置第4个参数为TRUE时，可以实现模糊查找，而LOOKUP函数本身就具有模糊查找的属性。即如果LOOKUP找不到所设定的目标值，则会寻找小于或等于目标值的最大数值。利用这个特性可以实现模糊匹配。
① 选中G3单元格，在编辑栏中输入公式：
=LOOKUP(F3,$A$3:$B$7)
② 按Enter键，即可根据F3单元格的分数返回其对应的等级，如图4-82所示。
③ 选中G2单元格，拖动右下角的填充柄向下复制公式即可返回批量结果，如图4-83所示。
图4-82
图4-83
本例公式是利用了LOOKUP模糊查找的属性。其判断原理如下：
查找对象 “92” 在A3:A7单元格区域中找不到，则找到的就是小于92的最大数90，其对应在B列上的数据是 “A” 。再如，查找对象 “85” 在A3:A7单元格区域中找不到，则找到的就是小于85的最大数80，其对应在B列上的数据是 “B” 。
对于本例中的求解目的，也可以使用IF函数的多层嵌套来实现。但有几个判断区间就需要有几层IF嵌套，区间越多，嵌套的层数就会越多，因此很容易出错，使用LOOKUP函数则很好地解决了这个问题。
LOOKUP查找并不是只能进行单条件的查找，也可以实现双条件的查找。LOOKUP函数双条件查找，读者可以记住一个通用公式， “=LOOKUP(1,0/（（条件1=条件1判断区域）*（条件2=条件2判断区域）*…）,返回值区域）” ，即有几个条件，就使用 “*” 符号连接几个条件。下面看一下具体实例。
① 选中G2单元格，在编辑栏中输入公式：
=LOOKUP(1,0/((E2=A2:A11)*(F2=B2:B11)),C2:C11)
② 按Enter键，即可根据E2与F2单元格的条件查询到其销售额，如图4-84所示。
图4-84
如果函数LOOKUP找不到指定的查找值，则查找给定区域中小于或等于查找值的最大数值。利用这一特性，我们可以用 “=LOOKUP（1,0/（条件）,引用区域）” 这样一个通用公式来进行查找引用。因此对于想满足的查询条件都写入 “0” 的除数下，如果同时满足多条件则使用 “*” 相连。
当前工作簿的 “产品信息表” 中显示了各个产品的编号、产品名称及销售单价信息，如图4-85所示。现在在 “销售表” 中需要根据产品编号自动匹配该产品的销售单价。可以使用VLOOKUP函数来设计公式。
图4-85
① 在 “销售表” 中选中C2单元格，在编辑栏输入公式：
=VLOOKUP(A2,产品信息表!$A$1:$D$11,4,FALSE)
② 按Enter键，即可得到A2单元格中编号产品的销售单价，如图4-86所示。
图4-86
③ 选中C2单元格，拖动右下角的填充柄向下复制公式即可根据A列中显示的各个产品编号自动从 “产品信息表” 中匹配其销售单价，如图4-87所示。
图4-87
VLOOKUP函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前行中指定列处的值。其参数设置详解如下：
① 指定查找对象。
② 表示包含查找值、返回值在内的单元格区域。
③ 指定返回哪一列的值。
④ 可选参数，当为FALSE时表示精确匹配。
本例公式用于在 “产品信息表!$A$1:$D$11” 单元格区域中的首列中查找产品编号，找到返回对应在“产品信息表!$A$1:$D$11”单元格区域第4列上的值。
如图4-88所示，要求根据给出的地址自动匹配该地址的补贴标准。其公式设置要点如下：
（1）使用LOOKUP的通用公式： “=LOOKUP（1,0/（条件）,引用区域）” 。
（2）在设置条件时可以灵活地嵌套其他函数。
图4-88
关键点： 实现日期数据的计算
操作要点： 通过文中公式解析学会DATEDIF函数的参数规则
应用场景： 日期数据也属于数值数据，它是可以进行计算的。而在日常工作中也经常需要使用到日期的计算，如计算固定资产的已使用月份、计算员工的工龄等
固定资产统计时经常要根据已使用的月份数来计提折旧，因此可以根据固定资产统计表中的新增日期来计算已使用月份数。
① 选中D2单元格，在编辑栏中输入公式：
=DATEDIF(C2,TODAY(),"m")
② 按Enter键，即可根据C2单元格中的新增日期计算出第一项固定资产已使用月数，如图4-89所示。
图4-89
③ 选中D2单元格，拖动右下角的填充柄向下复制公式即可实现批量计算各固定资产的已使用月数，如图4-90所示。
图4-90
DATEDIF函数用于计算两个日期之间的年数、月数和天数。其参数设置详解如下：
① 表示起始日期。
② 表示结束日期。本例中使用 “TODAY()” 返回当前日期来作为结束日期。
③ 表示指定要返回两个日期哪种差值的参数代码。 “Y” 表示返回两个日期之间的年数； “M” 表示返回两个日期之间的月数； “D” 表示返回两个日期之间的天数。 “YM” 表示忽略两个日期的年数和天数，返回之间的月数； “YD” 表示忽略两个日期的年数，返回之间的天数； “MD” 表示忽略两个日期的月数和天数，返回之间的年数。
本例公式用于计算C2单元格中日期与当前日期相差的月份数。
一般在员工档案表中会记录员工的入职日期，根据入职日期可以使用DATEDIF函数计算员工的工龄。
① 选中D2单元格，在编辑栏中输入公式：
=DATEDIF(C2,TODAY(),"y")
② 按Enter键，即可根据C2单元格中的入职日期计算出其工龄，如图4-91所示。
图4-91
③ 选中D2单元格，拖动右下角的填充柄向下复制公式即可实现批量获取各员工的工龄，如图4-92所示。
图4-92
本例公式用于计算C2单元格中日期与当前日期相差的年数。因为C2单元格中是入职日期，因此计算结果为该员工至今日的年数。
如图4-93所示，要求当员工在三天内生日时给出 “提醒” 文字。其公式设置要点如下：
（1）计算日期差值要忽略年数，只返回天数。
（2）配合IF函数。
图4-93