《Excel 2016电子表格制作案例教程》 项目五 使用公式和函数

Excel2016电子表格制作案例教程IBAOTU.COMIBAOTU.COM项目五使用公式和函数项目描述Excel强大的计算功能主要依赖于公式和函数，利用公式和函数可以对表格中的数据进行各种计算和处理操作，从而提高用户在制作复杂表格时的工作效率及计算准确率。学习要点公式的概念，公式中的运算符及其优先级，公式的输入、编辑和复制。公式中经常出现的问题及解决方法。单元格引用的分类及使用方法。函数的概念、分类及使用方法。数组公式的创建、编辑及使用规则。Contents01使用公式03了解公式的常见问题02认识单元格引用Contents04使用函数05使用数组公式Partone.使用公式使用公式一、认识公式在Excel2016中，公式是对工作表中的数据进行计算的表达式。利用公式可以对同一个工作表中各单元格的数值，同一个工作簿不同工作表中单元格的数值，以及不同工作簿的工作表中单元格的数值进行加、减、乘、除、乘方等运算。要输入公式，必须先输入“=”，然后再在其后输入表达式，否则Excel2016会将输入的内容作为文本型数据进行处理。表达式由运算符和参与运算的操作数组成：运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格引用和函数等。求A2单元格中的值乘以B5单元格中的值再除以B6单元格中的值，然后加100的值；使用函数AVERAGE求A2:B7单元格区域的平均值，并将求出的平均值乘以A4单元格中的值再除以3。使用公式二、了解公式中的运算符运算符是用来对公式中的元素进行运算的特殊符号。利用运算符，可以构建出复杂的公式，以完成复杂的运算。Excel2016包含4种类型的运算符算术运算符比较运算符引用运算符文本运算符使用公式1算术运算符算术运算符有6个，其作用是完成基本的数学运算并产生计算结果。算术运算符及其含义使用公式2比较运算符比较运算符有6个，其作用是比较两个值并得出一个逻辑值，即TRUE（真）或FALSE（假）。比较运算符及其含义使用公式3引用运算符引用运算符有3个，其作用是将单元格区域进行合并计算。引用运算符及其含义4文本运算符使用文本运算符“&”（与号）可将两个或多个文本值串起来，产生一个连续的文本值。例如，输入：“秋风起兮”&“白云飞”，会生成“秋风起兮白云飞”。使用公式5运算符的优先级通常情况下，Excel会按照从左到右的顺序进行公式运算。如果公式中同时用到了多个运算符，Excel将按各个运算符的优先级进行运算。对于相同优先级的运算符，将按从左到右的顺序进行运算。运算符的优先级如果想更改求值的顺序，可以用括号把公式中要先计算的部分括起来。括号可以嵌套使用，当嵌套使用括号时，最内层的括号优先计算。使用公式三、输入、编辑与删除公式1输入公式要输入公式，可以直接在单元格中输入，也可以在编辑栏中输入，方法与输入普通数据类似。例如，要计算餐厅消耗项目统计表中的“本日金额（元）”合计，操作步骤如下：

步骤一打开本书配套素材“项目五”/“任务一”/“餐厅消耗项目统计表”工作簿，将其另存为“餐厅消耗项目统计表（公式）”工作簿。

步骤二单击要输入公式的单元格，也就是要放置计算结果的单元格C20。使用公式

步骤三输入等号“=”，接着输入操作数和运算符（通过键盘输入），可看到编辑栏中同步显示输入的内容。输入公式使用公式

步骤四公式输入完毕，单击编辑栏左侧的“输入”按钮，或按“Enter”键得到计算结果。计算本例合计值时，也可单击C20单元格后单击“开始”选项卡“编辑”组中的“自动求和”按钮。计算出“本日金额（元）”合计2编辑与删除公式要编辑公式，可单击含有公式的单元格，然后在编辑栏中进行修改，或双击单元格后直接在单元格中进行修改，修改完毕按“Enter”键确认。如果选中公式所在单元格，然后按“Delete”键，可删除单元格中的公式和运算结果。使用公式四、复制与填充公式对于输入到单元格中的公式，用户可以使用填充柄，或复制、粘贴命令对其进行复制填充。其中，利用复制、粘贴命令复制单元格中的公式时，单元格引用会根据引用类型而发生变化，即系统会自动改变公式中引用的单元格地址。如果想将某个单元格中的公式复制到同列（行）中相邻的单元格，可以通过拖动填充柄来快速完成。例如，要计算餐厅消耗项目统计表中的“本周金额累计（元）”与“本月金额累计（元）”及“周每日平均金额（元）”值，可利用复制公式和拖动填充柄的方法实现，操作步骤如下：

步骤一选中C20单元格，然后单击“开始”选项卡“剪贴板”组中的“复制”按钮。复制公式使用公式

步骤二选中目标单元格E20后，直接单击“剪贴板”组中的“粘贴”按钮，或单击按钮，在展开的下拉列表中选择“公式”选项，在编辑栏中可看到系统自动改变了公式中引用的单元格地址并得到了计算结果。按“Esc”键可取消复制单元格后的动态边框效果粘贴公式计算出“本周金额累计（元）”的合计

步骤三使用同样的方法，可以计算出“本月金额累计（元）”的值。使用公式

步骤四在G4单元格中输入公式“=E4/7”，然后向下拖动G4单元格的填充柄到G19单元格后释放鼠标，计算出“周每日平均金额（元）”的值。计算出“周平均金额（元）”的值使用公式

通过计算成绩表中的平均分和总分，练习利用公式计算工作表中数据的操作。任务实施计算成绩平均分和总分计算成绩平均分和总分效果Parttwo.认识单元格引用认识单元格引用一、单元格的引用方式Excel2016提供了相对引用、绝对引用和混合引用3种引用方式，用户可以根据实际情况进行选择。1相对引用相对引用指的是当复制公式到其他单元格时，Excel保持从属单元格与引用单元格的相对位置不变。其引用形式为直接用列标和行号表示单元格，如B5；或用引用运算符表示单元格区域，如B5:D15。默认情况下，在公式中对单元格的引用都是相对引用，如果公式所在单元格的位置改变，那么引用也会随之改变。引用单元格区域时，应先输入单元格区域开始位置的单元格地址，然后输入引用运算符，再输入单元格区域结束位置的单元格地址。认识单元格引用2相对引用绝对引用指的是当复制公式到其他单元格时，Excel保持公式所引用的单元格绝对位置不变。也就是说，它与包含公式的单元格的位置无关。其引用形式为在列标和行号的前面都加上“$”符号。例如，在公式中引用$B$5单元格，则不论将公式复制或移动到什么位置，引用的单元格地址的行和列都不会改变。例如，要计算餐厅消耗项目统计表中的“日均比例（%）”“周均比例（%）”“月均比例（%）”值，可利用单元格的绝对引用实现，操作步骤如下：

步骤一打开本书配套素材“项目五”/“任务一”/“餐厅消耗项目统计表（公式）”工作簿，将其以“餐厅消耗项目统计表（单元格引用）”为名另存到“项目五”/“任务二”文件夹中。认识单元格引用

步骤二在D4单元格中单击，输入“=C4/”后单击C20单元格，然后在单元格地址C20的列标和行号前分别添加“$”符号。输入含有单元格绝对引用的公式认识单元格引用

步骤三按“Enter”键，计算出“肉类”的日均比例值。向下拖动D4单元格的填充柄到D19单元格后释放鼠标，计算出其他项目的日均比例值。可以看到，无论将公式复制到哪个单元格中，公式中绝对引用的单元格地址$C$20保持不变，而相对引用的单元格地址则发生了相应的改变。

步骤四在F4单元格中输入公式“=E4/$E$20”，然后向下拖动F4单元格的填充柄到F19单元格后释放鼠标，计算出所有项目的周均比例值。认识单元格引用

步骤五在I4单元格中输入公式“=H4/$H$20”，然后向下拖动I4单元格的填充柄到I19单元格后释放鼠标，计算出所有项目的月均比例值。计算月均比例值3混合引用混合引用指的是引用中既包含绝对引用又包含相对引用，如$A1或A$1等，用于表示行变列不变或列变行不变的引用。如果公式所在单元格的位置改变，则相对引用改变，绝对引用不变。认识单元格引用二、单元格引用的使用方法1引用同一个工作簿不同工作表中的单元格在同一个工作簿中，不同工作表中的单元格可以相互引用，其表示方法为“工作表名称!单元格或单元格区域地址”，如“Sheet2!F8:F16”。B2单元格中的公式引用了当前工作簿“8月”和“9月”两个工作表中F14单元格中的数据。引用同一工作簿不同工作表中的单元格2引用不同工作簿中的单元格在当前工作表中引用不同工作簿中的单元格或单元格区域的表示方法为“‘[工作簿名称.xlsx]工作表名称‘!单元格或单元格区域地址”，如“‘[员工档案表.xlsx]Sheet2‘!F8:F16”。认识单元格引用

通过计算图书销售结算表中的实际销售额，以及数量合计、销售额合计与实际销售额合计，练习在公式中使用单元格引用计算工作表中数据的操作。任务实施计算实际销售额及合计图书销售结算表效果Partthree.了解公式的常见问题了解公式的常见问题一、公式常见错误代码在Excel2016中，当用公式进行计算时，如果给定的操作数、运算符及函数参数等不符合运算规则，单元格中会给出相应的错误信息。了解这些错误信息的含义，有助于发现并修正公式或函数中的错误。1#####原因：列宽不足，或者使用了负的日期或时间。解决方法：（1）列宽不足：增加列宽或缩小单元格内容的字宽度，或更改数字格式，如减少小数位数等。（2）日期或时间为负数：检查公式，确保日期和时间的运算结果为正数。了解公式的常见问题2#VALUE!原因：使用了错误类型的参数或操作数。解决方法：检查并重新设置引用单元格，或重新输入正确类型的数据。3#DIV/0!原因：除数为零（0）。解决方法：检查并确保除数或除数引用的数字单元格的值不为零或不为空。了解公式的常见问题4#NAME?原因：引用的单元格区域名称、函数名称或函数参数中可能有拼写错误，系统不能正常识别。解决方法：（1）检查公式中的函数名称是否正确。（2）检查函数参数中的文本是否正确使用了双引号。（3）检查引用的单元格区域是否漏掉了单元格区域引用中的冒号（:）。（4）检查当引用工作簿或工作表的名称中包含非字母字符或空格时，是否用单引号将它们括起来了。5#N/A原因：数值不能用于函数或公式。解决方法：检查数值是否允许用于函数或公式，函数参数是否缺失。了解公式的常见问题6#REF!原因：单元格引用无效。解决方法：检查被引用的单元格是否存在。7#NUM!原因：公式或函数中使用了无效的数字型参数。解决方法：（1）检查公式或函数中使用的数字是否为纯数字（不能带格式，如$500，￥20）。（2）检查公式的结果是否正确，运算结果数字是否太小或太大（Excel2016允许使用的数据范围是−1×10307～1×10307）。8#NULL!原因：计算并不相交的两个单元格区域的交集。解决方法：更改引用以使单元格区域相交。了解公式的常见问题二、公式检查与审核通过追踪单元格，可以显示公式与单元格之间的关系，这对于查找公式的逻辑错误及在删除某个单元格时的核查非常有用。追踪单元格包括追踪引用单元格和追踪从属单元格。追踪引用单元格用于查找影响当前单元格值的单元格，即当前单元格中的公式引用过的单元格；追踪从属单元格用于查找受当前单元格值影响的单元格，即公式中引用了当前单元格的单元格。1追踪单元格了解公式的常见问题

步骤一继续在打开的一班成绩表中操作。选中含有公式的单元格，如G3，然后单击“公式”选项卡“公式审核”组中的“追踪引用单元格”按钮，此时可看到为当前公式提供数据的单元格C3、D3和E3。追踪引用单元格

步骤二如果公式的引用单元格又引用了其他单元格，那么再次单击“追踪引用单元格”按钮，可进一步追踪下一级引用单元格。了解公式的常见问题

步骤三如果要追踪从属单元格，可选中要查看的单元格，如C6，然后单击“公式审核”组中的“追踪从属单元格”按钮，此时可看到当前单元格被哪些单元格中的公式引用过。

步骤四追踪从属单元格如果要取消工作表中的所有追踪箭头，可单击“公式审核”组中的“移去箭头”按钮；如果要移去引用单元格或从属单元格的追踪箭头（需要先选中原始单元格），可单击“移去箭头”下拉按钮，在展开的下拉列表中选择相应选项，“移去箭头”下拉列表了解公式的常见问题在使用公式的过程中，有时会因人为疏忽（如公式参数设置不当），导致计算结果发生错误。此时，可以使用系统提供的“错误检查”功能对公式进行检查，确保公式最终结果正确，操作步骤如下：2查找错误原因

步骤一继续在打开的工作表中操作。假设学生“石小龙”的“网络基础”科目缺考。在C16单元格中输入“缺考”文本，然后在“公式”选项卡的“公式审核”组中单击“错误检查”按钮，系统会自动对工作表中的公式和函数进行检查。了解公式的常见问题

步骤二当找到错误时，系统会显示类似下图所示的“错误检查”对话框，其中显示了工作表中出错的单元格及错误产生的原因。通过“公式审核”组进行错误检查

步骤三根据需要单击对话框右侧的可选操作按钮（操作按钮会因错误类型的不同而不同），对其中的错误逐一按步骤进行分析，然后在编辑栏中进行修改，这里重新在C16单元格中输入补考成绩“62”，确认后，可看到错误代码消失。

步骤四如果还有其他错误，可以单击“下一个”按钮，直至完成整个工作表的错误更正。在最后出现的提示对话框中单击“确定”按钮，结束错误检查和更正操作。了解公式的常见问题

通过审核年度销售计划表，练习利用Excel的公式审核功能审核工作表中公式的操作。任务实施审核年度销售计划表Partfour.使用函数使用函数一、认识函数及其分类在Excel2016中，函数是一类特殊的、预定义的公式，主要用于复杂计算，又被称为“特殊公式”。函数通常表示为：函数名([参数1],[参数2],…)，括号中的参数可以有多个，中间用逗号分隔。在函数的表现形式中，带方括号的参数是可选参数，也有的函数没有参数。函数的参数可以是数字、文本、逻辑值（如TRUE，FALSE）、数组、已定义的名称、错误值（如“#N/A”）或单元格引用，也可以是常量、公式或函数。1认识函数函数的构成在公式中合理地使用函数，可以实现诸如求和、逻辑判断和财务分析等众多数据处理功能。使用函数2函数的分类常用的函数类型和使用范例使用函数二、输入与编辑函数1手动输入函数使用函数时，应首先确认已在单元格中输入了等号“=”，即已进入公式编辑状态。接下来可输入函数名称，再紧跟着一对括号，括号内为一个或多个参数，参数之间用逗号分隔。用户可以在单元格中手动输入函数，也可以使用“插入函数”按钮和函数库输入函数。手动输入一般用于参数比较单一，且比较常用的函数，即用户能记住函数的名称、参数等。例如，在餐厅消耗项目统计表中计算本日、本周和本月金额合计，以及日均、周均和月均比例合计时，可以利用求和函数计算，即在C20单元格中输入公式“=SUM(C4:C19)”，确认后向右拖动该单元格的填充柄到I20单元格后释放鼠标，最后设置比例的百分比格式即可。利用求和函数计算各项目的合计使用函数2通过“插入函数”按钮输入函数如果不能准确输入函数的名称或参数，可以通过“插入函数”按钮输入函数。例如，要统计餐厅消耗项目统计表中“主营业务成本”和“运营费用类”支出项目的数量，可通过“插入函数”按钮输入函数，操作步骤如下：

步骤一打开本书配套素材“项目五”/“任务二”/“餐厅消耗项目统计表（单元格引用）”工作簿，将其以“餐厅消耗项目统计表（函数）”为名另存到“项目五”/“任务四”文件夹中。

步骤二单击D22单元格，然后单击编辑栏左侧的“插入函数”按钮，打开“插入函数”对话框。单击“插入函数”按钮使用函数

步骤三在“选择函数”列表中选择“COUNT”函数，然后单击“确定”按钮。选择“COUNT”函数

步骤四打开“函数参数”对话框，在第1个参数编辑框中单击并选中已有内容，然后在工作表中选择要统计数量的单元格区C4:C11，可看到选择的单元格区域地址显示在参数编辑框中，单击“确定”按钮，计算出“主营业务成本”支出项目的数量。选择单元格区域使用函数

步骤五向下拖动D22单元格的填充柄到D23单元格后释放鼠标，然后修改D23单元格中的公式为“=COUNT(C12:C17)”，确定后计算出“运营费用类”支出项目的数量。计算“运营费用类”支出项目的数量使用函数3使用函数库输入函数当明确知道所需函数属于哪一类时，可采用该方法输入函数。例如，用于计算日期和时间的函数DAY、EOMONTH、TODAY等，属于日期和时间函数，此时可以通过“公式”选项卡“函数库”组中的“日期和时间”分类输入这些函数。使用函数

步骤一在J4单元格中单击，然后在“公式”选项卡的“函数库”组中单击“日期和时间”按钮，在展开的下拉列表中选择“DAY”选项。选择“DAY”选项

步骤二打开“函数参数”对话框，此时插入点在参数编辑框内闪烁，单击工作表中存放日期的单元格K2，可看到选择的单元格引用显示在参数编辑框中。选择存放日期数据的单元格使用函数

步骤三单击“确定”按钮，得到该日期在一个月中的天数。在编辑栏中选中公式中的参数“K2”，然后在“日期和时间”下拉列表中选择“EOMONTH”选项，打开“函数参数”对话框，在第1个参数编辑框中单击，然后单击工作表中的K2单元格，在第2个参数编辑框中输入0，此时在编辑栏中可看到输入的公式。设置EOMONTH函数参数

步骤四单击“确定”按钮，即可计算出当前月的天数。该数值受K2单元格值的影响计算当前月的天数使用函数

步骤五计算出当前月的天数后，在K4单元格中输入公式“=H4/$J$4”并按“Enter”键，向下拖动K4单元格的填充柄到K19单元格后释放鼠标，计算出各支出项目的月平均金额，计算各支出项目的月每日平均金额如果在K2单元格中输入公式“=TODAY()”，那么，此后只要在“本日金额（元）”“本周金额累计（元）”“本月金额累计（元）”列中输入数据，其他列中的数据会自动计算出来，非常方便，即可以把这个工作簿作为模板使用。使用函数三、函数的嵌套在有些情况下，可以将某个函数作为另一个函数的参数使用，形成函数的嵌套。Excel2016中函数的嵌套不可超过64层。函数嵌套IF函数中嵌套使用了AVERAGE和SUM函数。该公式的含义是：先将AVERAGE函数的结果与75进行比较，当单元格区域A2:A9的平均值大于或等于75时，该公式才会对单元格区域B2:B9求和，否则返回0。创建嵌套函数公式的方法有如下两种：（1）在带函数的公式中直接输入嵌套的函数及其参数。（2）在“函数参数”对话框中单击参数编辑框，然后单击“名称框”右侧的下拉按钮，在展开的下拉列表中选择所需嵌套的函数，再在打开的对话框中设置其参数。使用函数

通过统计成绩名次和是否及格，练习在公式中使用函数计算工作表中数据的操作。任务实施统计成绩名次和是否及格成绩名次和是否及格效果Partfive.使用数组公式使用数组公式一、认识与创建数组公式1认识数组公式数组是指按一行、一列或多行多列排列的一组数据元素的集合。数组元素可以是数值、文本（用双引号括起来）、日期、逻辑值或错误值等。数组公式是指可以同时对一组或两组以上的数据进行计算的公式，计算的结果可能是一个，也可能是多个。在二维数组里，同行的元素间用逗号“,”分隔，不同行的用分号“;”分隔。在数组公式中使用的数据称为数组参数。数组参数可以是一个区域数组，也可以是一个常量数组。使用数组公式2创建数组公式要创建数组公式，首先必须选择用来存放结果的单元格区域（也可以是一个单元格），然后输入公式，最后按组合键“Ctrl+Shift+Enter”锁定数组公式，Excel将在公式两边自动加上花括号“{}”。例如，可以利用数组公式计算饮料销售统计表中的销售额和销售利润合计，操作步骤如下：

步骤一打开本书配套素材“项目五”/“任务五”/“饮料销售统计表”工作簿，将其另存为“饮料销售统计表（数组公式）”工作簿。使用数组公式

步骤二选择要放置计算结果的单元格区域E4:E16，然后输入数组公式“=C4:C16*D4:D16”。

步骤三按组合键“Ctrl+Shift+Enter”，得到所有饮料的销售额。这是使用数组公式计算两个数据区域的乘积。输入数组公式使用数组公式计算两个数据区域的乘积使用数组公式

步骤四根据利润率，在E18单元格中输入数组公式“=SUM(E4:E16*E2)”，按组合键“Ctrl+Shift+Enter”，得到所有饮料的销售利润合计。这是使用数组公式计算一个单元格区域与一个单元格的乘积。计算销售利润合计使用数组公式二、编辑数组公式（1）如果要编辑数组公式，需要先选中公式所在单元格或单元格区域，然后按“F2”键进入编辑模式或在编辑栏中修改数组公式，操作完成后，按组合键“Ctrl+Shift+Enter”即可。（2）如果要删除数组公式，操作方法与编辑数组公式类似，在编辑模式或编辑栏中删除公式后按组合键“Ctrl+Shift+Enter”即可。三、数组公式的使用规则（1）输入数组公式时，首先选择用来保存计算结果的单元格或单元格区域。如果计算公式将产生多个计算结果，必须选择一个与计算结果所需大小和形状都相同的单元格区域。（2）输入数组公式后，按组合键“Ctrl+Shift+Enter”，此时在编辑栏中可以看见公式的两边自动加上了花括号，表示该公式是一个数组公式。（3）在数组公式所涉及的单元格区域，不能编辑、删除或移动单个单元格，也不能插入单元格。也就是说，数组公式所涉及的单元格区域只能作为一个整体进行操作。使用数组公式

通过利用数组法计算成绩表中的平均分和总分，练习利用数组公式计算工作表中数据的操作。任务实施利用数组法计算成绩平均分和总分利用数组法计算成绩表中的平均分和总分高手秘籍一、单元格显示公式本身的解决方法当在单元格中输入公式并确认后，如果未得到计算结果而显示公式本身，出现这种情况的原因有如下两种。（1）启用了“显示公式”模式。“公式”选项卡“公式审核”组中的“显示公式”按钮处于选中状态，即呈高亮显示。解决方法：单击“显示公式”按钮，或按组合键“Ctrl+`”，可在普通模式与显示公式模式之间进行切换。（2）单元格的格式可能为“文本”。确认不是第1种原因后，有可能是因为单元格的格式设置成“文本”了。解决方法：选中显示为公式本身的单元格，然后在“开始”选项卡“数字”组的“数字格式”下拉列表中选择“常规”选项，再重新激活单元格中的公式即可。高手秘籍二、定义名称并在公式中使用在Excel2016中，定义名称的作用是方便实现绝对引用。例如，为一个单元格或单元格区域定义为名称后，当引用这个单元格或单元格区域时，可直接使用其名称；为一个公式定义名称后，当要重复使用该公式时，也可直接使用其名称。在Excel2016中，可以定义名称的对象包括常量、单元格或单元格区域、公式等。要定义名称，方法有3种：一是使用编辑栏左侧的名称框；二是使用“新建名称”对话框（单击“公式”选项卡“定义的名称”组中的“定义名称”按钮）；三是根据所选内容创建（根据所选单元格自动生成名称）。高手秘籍

步骤一选中要定义名称的单元格或单元格区域（包括不相邻的单元格区域）。

步骤二单击编辑栏左侧的名称框，输入引用选中内容时要使用的名称，如“姓名”。利用名称框定义名称显示定义的名称

步骤三按“Enter”键确认，此时在名称框的下拉列表中可看到定义的名称。高手秘籍定义的名称可用来快速选中被命名的区域，更为重要的是，可以在公式中引用名称以实现绝对引用。1通过“名称框”引用

步骤一单击“名称