Excel 2016高级应用案例教程课件

第3章公式与函数的基本应用Excel办公应用高级教程目录Contents3.1

初识公式3.2

理解Excel函数3.7

疑难解答3.3

审核和检查公式3.4

常用数学函数3.6

实战演练：制作销售数据统计表3.5

常用统计函数3.1.1公式的组成Excel公式通常由“等号”、“运算符”、“单元格引用”、“函数”、“数据常量”等组成，下面列举了一些常见的公式。3.1.2

公式的输入通常情况下，当以“=”为开头，在单元格内输入时，单元格将自动变成公式输入状态。进入公式输入模式后，鼠标选中其他单元格或区域时，该选中的单元格将会作为引用自动输入到公式中。3.1.2

公式的输入当公式输入完成后，按【Enter】键，即可结束输入状态，计算出结果。如果需要修改公式，则可以通过以下几种方法再次进入公式编辑状态。1.双击公式所在单元格，即可进入编辑状态。2.选中公式所在单元格，单击上方的“编辑栏”，在编辑栏中进行修改。3.选中公式所在单元格，按【F2】键，即可进入编辑状态。3.1.3

复制和填充公式当表格中多个单元格所需公式的计算规则相同时，可以使用复制和填充功能进行计算。1.复制公式选择公式所在单元格，按【Ctrl+C】组合键进行复制。然后选择目标单元格区域，按【Ctrl+V】组合键，粘贴公式。3.1.3

复制和填充公式2.填充公式方法一：选择公式所在单元格，将鼠标光标移至该单元格右下角，向下拖动鼠标填充公式。方法二：选择公式所在单元格，将鼠标光标移至该单元格右下角。双击鼠标，公式将向下填充到其他单元格中。3.1.4

公式中的常见错误使用公式进行计算时，可能会因为某种原因而无法得到或显示正确结果，在单元格中返回错误值信息。3.1.5

单元格的3种引用在公式中的引用具有以下关系：如果单元格A1包含公式“=B1”。那么B1就是A1的引用单元格，A1就是B1的从属单元格。从属单元格与引用单元格之间的位置关系称为单元格引用的相对性，可以分为3种不同的引用方式，即相对引用、绝对引用和混合引用。3.1.5

单元格的3种引用1.相对引用当复制公式到其他单元格时，Excel保持从属单元格与引用单元格的相对位置不变，称为相对引用。例如，在B1单元格输入公式“=A1”。当向右复制公式时，将依次变为：=B1、=C1、=D1等。3.1.5

单元格的3种引用2.绝对引用当复制公式到其他单元格时，Excel保持公式所引用的单元格绝对位置不变，称为绝对引用。例如，在B1单元格输入公式“=$A$3”。则无论公式向右还是向下复制，都始终保持为“=$A$3”不变。3.1.5

单元格的3种引用3.混合引用当复制公式到其他单元格时，Excel仅保持所引用单元格的行或列方向之一的绝对位置不变，而另一方向位置发生变化，这种引用方式称为混合引用，可分为行绝对列相对引用和行相对列绝对引用。例如，在B1单元格输入公式“=$A2”。则公式向右复制时始终保持为“=$A2”。向下复制时行号将发生变化。即行相对列绝对引用。目录Contents3.1

初识公式3.2

理解Excel函数3.7

疑难解答3.3

审核和检查公式3.4

常用数学函数3.6

实战演练：制作销售数据统计表3.5

常用统计函数3.2.1

函数的结构在公式中使用函数时，通常由表示公式开始的“=”号、函数名称、左括号、以半角逗号相间隔的参数和右括号构成。有的函数可以允许多个参数，有的函数没有参数或可以不需要参数。函数的参数，可以由数值、日期和文本等元素组成，可以使用常量、数组、单元格引用或其他函数。当使用函数作为另一个函数的参数时，称为函数的嵌套。3.2.2

函数的类型由于Excel的版本不同，所包含的函数类型也有所不同。在Excel2019版本中，函数的类型可分为：逻辑函数、文本函数、日期和时间函数、查找与引用函数、数学和三角函数、统计函数、信息函数、财务函数等。用户可以在“公式”选项卡中的“函数库”选项组中对函数的种类进行查看。3.2.3

函数的输入方法用户可以通过多种方法输入函数，例如，使用函数库输入已知类别的函数、使用“插入函数”向导输入函数。方法一：使用函数库输入已知类别的函数在“公式”选项卡中的“函数库”选项组中单击选择需要的函数。打开“函数参数”对话框，从中设置各参数，单击“确定”按钮。即可在单元格中输入函数公式。3.2.3

函数的输入方法方法二：使用“插入函数”向导输入函数如果用户对函数所属的类别不太熟悉，则可以使用“插入函数”向导选择或搜索所需函数。在“公式”选项卡中单击“插入函数”按钮，或单击“编辑栏”左侧的“插入函数”按钮。打开“插入函数”对话框，在“搜索函数”文本框中输入关键词，单击“转到”按钮，即可显示“推荐”的函数列表，在“选择函数”列表框中选择需要的函数，单击“确定”按钮。在打开的“函数参数”对话框中设置参数即可。3.2.3

函数的输入方法自动计算出“总金额”数据用户可以使用“自动求和”功能，快速对数据进行求和，下面将介绍具体的操作方法。（1）选择D2:D6单元格区域，在“公式”选项卡中单击“自动求和”下拉按钮，从列表中选择“求和”选项。（2）即可快速计算出“总金额”。目录Contents3.1

初识公式3.2

理解Excel函数3.7

疑难解答3.3

审核和检查公式3.4

常用数学函数3.6

实战演练：制作销售数据统计表3.5

常用统计函数3.3.1

追踪单元格追踪单元格作用是跟踪选定单元格内公式的引用或从属单元格。分为：追踪引用单元格和追踪从属单元格。1.追踪引用单元格追踪引用单元格用于指示哪些单元格会影响当前所选单元格的值。选择单元格，在“公式”选项卡中单击“追踪引用单元格”按钮，即可出现蓝色箭头，指明当前所选单元格引用了哪些单元格。此外，选择单元格，按【Ctrl+[】组合键，可以定位到所选单元格的引用单元格。3.3.1

追踪单元格2.追踪从属单元格追踪从属单元格用于指示哪些单元格受当前所选单元格的值影响。选择单元格，单击“追踪从属单元格”按钮，蓝色箭头指向受当前所选单元格影响的单元格。此外，选择单元格，按【Ctrl+］】组合键，可以定位到所选单元格的从属单元格。3.3.2

显示公式本身当输入完公式并结束编辑后，并未得到计算结果而是显示公式本身。以下是两种可能的原因和解决方法。1.检查是否启用了“显示公式”模式如果用户在“公式”选项卡中单击“显示公式”按钮，则会将表格中的公式显示出来。解决方法：在“公式”选项卡中再次单击“显示公式”按钮，取消其选中状态。3.3.2

显示公式本身2.检查是否单元格设置了“文本”格式如果未开启“显示公式”模式，单元格中仍然是显示公式本身而不是计算结果，则可能是由于单元格设置了“文本”格式后再输入公式。解决方法：选择公式所在单元格，按【Ctrl+1】组合键，打开“设置单元格格式”对话框，在“数字”选项卡中选择“常规”选项，单击“确定”按钮，重新激活单元格中的公式，并结束编辑。3.3.3

自动检查错误公式当公式的结果返回错误值时，应该及时查找错误原因，并修改公式以解决问题。Excel提供了后台检查错误的功能，用户只需要单击“文件”按钮，选择“选项”选项，打开“Excel选项”对话框，选择“公式”选项卡，在“错误检查”区域勾选“允许后台错误检查”复选框，并在“错误检查规则”区域勾选相应的规则选项即可。3.3.3

自动检查错误公式当单元格中的公式或值出现与“错误检查规则”选项中相符的情况时，单元格左上角会显示绿色小三角。选择该单元格，在其左侧会出现感叹号形状的“错误指示器”。单击“错误指示器”下拉按钮，在列表中可以查看公式错误的原因，列表中第一个选项表示错误原因。3.3.3

自动检查错误公式检查并修改错误公式当表格中出现错误值时，用户可以使用“错误检查”功能，手动检查错误公式，下面将介绍具体的操作方法。3.3.3

自动检查错误公式（1）选择C2:C6单元格区域。单击区域左上角的“

”按钮，从列表中选择“转换为数字”选项。（2）C7单元格中，即可显示出正确的求和数量。（3）在“公式”选项卡中单击“错误检查”按钮，打开“错误检查”对话框，在该对话框中显示出错的单元格，以及出错原因，用户在对话框的右侧可以进行“关于此错误的帮助”、“显示计算步骤”、“忽略错误”、“在编辑栏中编辑”操作，这里单击“在编辑栏中编辑”按钮。（4）在“编辑栏”中修改公式后，单击“继续”按钮，继续检查其他错误公式，检查并修改完成后会弹出一个提示对话框，直接单击“确定”按钮即可。目录Contents3.1

初识公式3.2

理解Excel函数3.7

疑难解答3.3

审核和检查公式3.4

常用数学函数3.6

实战演练：制作销售数据统计表3.5

常用统计函数3.4.1

SUM函数SUM函数用于对单元格区域中所有数值求和。其语法格式为：参数说明：number1：必需参数。表示要求和的第1个数字，可以是直接输入的数字、单元格引用或数组。number2：可选参数。表示要求和的第2-255个数字，可以是直接输入的数字、单元格引用或数组。3.4.1

SUM函数计算“销售总额”数据用户可以使用SUM函数，计算“销售总额”，下面将介绍具体的操作方法。（1）选择G2单元格，输入公式“=SUM(E2:E11)”。（2）按【Enter】键确认，即可计算出销售总额。3.4.2

SUMIF函数SUMIF函数用于根据指定条件对若干单元格求和。其语法格式为：参数说明：Range：为条件区域，用于条件判断的单元格区域。Criteria：是求和条件，由数字、逻辑表达式等组成的判定条件。Sum_range：为实际求和区域，需要求和的单元格、区域或引用。3.4.2

SUMIF函数计算出“笔记本”的销售总额用户可以使用SUMIF函数，计算“笔记本”的销售总额，下面将介绍具体的操作方法。（1）选择H2单元格，输入公式“=SUMIF(B2:B11,G2,E2:E11)”。（2）按【Enter】键确认，即可计算出“笔记本”的销售总额。3.4.3

SUMIFS函数SUMIFS函数用于解决多条件求和问题。其语法格式为：sum_range:是求和的实际单元格，即求和区域。criteria_range1:是要为特定条件计算的单元格区域，即条件1区域。criteria1:是数字、表达式或文本形式的条件，即条件1。它定义了单元格求和的范围。3.4.3

SUMIFS函数计算2月扫描仪的入库数量用户可以使用SUMIFS函数，计算2月扫描仪的入库数量，下面将介绍具体的操作方法。（1）选择C13单元格，输入公式“=SUMIFS(D2:D10,A2:A10,"2月",C2:C10,"扫描仪")”。（2）按【Enter】键确认，即可计算出2月扫描仪的入库数量。3.4.4

RAND函数RAND函数用于返回大于等于0及小于1的均匀分布随机数。其语法格式为：3.4.4

RAND函数随机安排值班表排序“值班人员”要想随机安排值班人员的排班顺序，可以使用RAND函数生成随机小数作为辅助列，然后进行排序。（1）选择B2单元格，输入公式“=RAND()”,按【Enter】键确认，计算出结果。（2）将公式向下填充，然后对“随机数”进行“升序”排序。目录Contents3.1

初识公式3.2

理解Excel函数3.7

疑难解答3.3

审核和检查公式3.4

常用数学函数3.6

实战演练：制作销售数据统计表3.5

常用统计函数3.5.1

COUNTIF函数COUNTIF函数用于求满足给定条件的数据个数。其语法格式为：Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。3.5.1

COUNTIF函数统计销售业绩大于3万的人数用户可以使用COUNTIF函数，统计销售业绩大于3万的人数，下面将介绍具体的操作方法。（1）选择E2单元格，输入公式“=COUNTIF(C2:C8,">30000")”。（2）按【Enter】键确认，即可统计出销售业绩大于3万的人数。3.5.2

MAX函数MAX函数用于返回一组值中的最大值。其语法格式为：参数说明：number1,number2,...为指定需求最大值的数值或者数值所在的单元格。如果参数为错误值或不能转换成数字的文本，将产生错误。如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或文本将被忽略。3.5.2

MAX函数统计最大销售业绩用户可以使用MAX函数，统计最大销售业绩，下面将介绍具体的操作方法。（1）选择E2单元格，输入公式“=MAX(C2:C8)”。（2）按【Enter】键确认，即可统计出最大销售业绩。3.5.3

MIN函数MIN函数用于返回一组值中的最小值。其语法格式为：参数说明：number1,number2,...是要从中找出最小值的1到255个数字参数。参数可以是数字、空白单元格、逻辑值或表示数值的文字串。如果参数中有错误值或无法转换成数值的文字时，将引起错误。如果参数是数组或引用，则函数MIN仅使用其中的数字、数组或引用中的空白单元格，逻辑值、文字或错误值将忽略。3.5.3

MIN函数统计最小销售业绩用户可以使用MIN函数，统计最小销售业绩，下面将介绍具体的操作方法。（1）选择E2单元格，输入公式“=MIN(C2:C8)”。（2）按【Enter】键确认，即可统计出最小销售业绩。3.5.4

RANK函数RANK函数用于返回一个数值在一组数值中的排位。其语法格式为：参数说明：number：为指定需找到排位的数值，或数值所在的单元格。ref：为指定包含数值的单元格区域或区域名称。ref区域内的空白单元格或文本、逻辑值将被忽略。order：指明排位方式。升序时指定为1，降序时指定为0。如果省略order，则用降序排位。3.5.4

RANK函数对员工销售业绩进行排名用户可使用RANK函数，对员工销售业绩进行排名，下面将介绍具体的操作方法。（1）选择D2单元格，输入公式“=RANK(C2,$C$2:$C$8,0)”。（2）按【Enter】键确认，即可计算出结果，并将公式向下填充，统计出销售业绩排名。目录Contents3.1

初识公式3.2

理解Excel函数3.7

疑难解答3.3

审核和检查公式3.4

常用数学函数3.6

实战演练：制作销售数据统计表3.5

常用统计函数实战演练：制作销售数据统计表本章实战演练将制作销售数据统计表，运用前面所学知识，以熟练掌握和巩固公式与函数的应用。1.案例效果本章实战演练为制作销售数据统计表。实战演练：制作销售数据统计表2.操作思路掌握SUM函数、COUNT函数、FREQUENCY函数的使用方法，下面将进行简单介绍。（1）计算“销售额”。在A2单元格中输入公式“=SUM(J5:J17)”。（2）计算“订单量”。在D2单元格中输入公式“=COUNT(0/FREQUENCY(A5:A17,A5:A17))”。（3）计算“下单人数”。在G2单元格中输入公式“=COUNT(0/FREQUENCY(B