4.3使用公式和函数 课件

项目四学会使用Excel2013CONTENTS学习表格的基本应用掌握表格的格式设置0102使用公式和函数03使用表格的数据统计与分析功能04学习目标（1）能熟练掌握工作表和单元格的基本操作。（2）能熟练输入与编辑数据。（3）掌握自动填充数据、单元格格式设置、页面设置和打印设置等的方法。（4）重点掌握公式和函数的操作。（5）会使用排序和筛选功能对数据进行排序与筛选。（6）了解图表的类型，会创建并使用图表。使用公式和函数03使用公式和函数03Excel2013最强大的功能在于对数据的处理，公式和函数在其中起着至关重要的作用。使用公式和函数03肖华美化后的成绩表获得了老师的好评。为了进一步分析计17-3班的学生成绩，老师让肖华统计出每名学生的总分及名次，各科的平均分、最高分和最低分，总分平均分、最高分和最低分，判断学生成绩是否为优秀、良好、及格、不及格。为此，他需要用到Excel中的公式和函数的功能。最终效果如图4-41所示。任务描述使用公式和函数03公式和函数是Excel中的主要功能之一，使用Excel中的公式和函数，可以十分轻松地完成大量数据的统计和输入。为了完成本任务，肖华需要进行如下操作：（1）使用AVERAGE（）函数计算平均分。（2）使用SUM（）函数计算总分。（3）使用MAX（）函数计算最高分。（4）使用MIN（）函数计算最低分。（5）使用RANK（）函数计算排名。（6）使用IF（）函数进行评优判断。任务分析使用公式和函数03运算符及优先级复制公式输入公式相关知识创建公式使用函数防止“编辑栏”显示公式公式是对工作表中的数值进行计算的等式，公式以等号（=）开头，一般包括函数、引用、运算符和常量。使用公式和函数03运算符有以下4种类型：（1）算术运算符。算术运算符包括加号（+）、减号（-）、乘号（*）、除号（/）、乘方（＾）、百分比（%）、括号“（）”等，算术运算的结果为数值型。（2）比较运算符。比较运算符包括等于号（=）、大于号（>）、小于号（<）、大于等于号（>=）、小于等于号（<=）、不等于号（<>），比较运算结果为逻辑值：True或False。（3）文本连接运算符。文本连接运算符主要是“&”，用于连接一个或多个文本。例如，“辽宁”&“沈阳”的结果为“辽宁沈阳”。（4）引用运算符。引用运算符包括冒号（：）、逗号（，）、空格（）。其中，冒号（：）用于表示一个连续的单元格区域，如A1：C3；逗号（，）用于将多个单元格区域合并成一个引用，如AVERAGE（A1:A3，C1）表示计算单元格区域A1:A3和单元格C1中包含的所有单元格（A1、A2、A3、C1）的平均值；空格（）用于处理区域中互相重叠的部分，如AVERAGE（A1:B3B1:C3）表示计算单元格区域A1:C3和单元格区域C1:C3相交部分单元格（B1、B2、B3）的平均值。运算符的优先级见表4-1。运算符及优先级相关知识使用公式和函数03运算符及优先级相关知识使用公式和函数03Excel公式是由数字、运算符、单元格引用、名称和内置函数构成的。具体操作方法为：选中要输入公式的单元格，在“编辑栏”中输入“=”后，再输入具体的公式，单击“编辑栏”左侧的输入按钮或按Enter键，完成公式的输入。输入公式相关知识方法一：选中包含公式的单元格，利用“复制”“粘贴”命令完成公式复制。方法二：选中包含公式的单元格，拖动填充柄选中所有需要运用此公式的单元格，释放鼠标左键后，公式即被复制。复制公式使用公式和函数03若公式中包含了对其他单元格的引用或使用单元格名称，则可以用下面的方法创建公式。下面以在C1单元格中创建公式“＝A1＋B1:B3”为例进行说明。（1）单击需输入公式的单元格C1，在“编辑栏”中输入“=”。（2）单击B3单元格，此单元格将有一个带有方角的蓝色边框。（3）在“编辑栏”中接着输入“+”。（4）在工作表中选择单元格区域B1:B3，此单元格区域将有一个带有方角的绿色边框。（5）按Enter键结束。若彩色边框上没有方角，则引用的是命名区域。提示:要输入单元格名称，可以按F3键，在“粘贴名称”对话框中选择所需的名称即可。创建公式相关知识使用公式和函数03有时，可能不希望用户看到公式，即单击选中包含公式的单元格，在“编辑栏”不显示公式。这时可以按以下方法设置：（1）在要隐藏公式的单元格区域右击，从快捷菜单中选择“设置单元格格式”选项，打开“设置单元格格式”对话框，切换到“保护”选项卡，选中“锁定”复选框和“隐藏”复选框，单击“确定”按钮返回工作表。（2）在“审阅”选项卡的“更改”组中，单击“保护工作表”按钮，使用默认设置后单击“确定”按钮返回工作表。这样，用户就不能在“编辑栏”或单元格中看到已隐藏的公式，也不能编辑公式。要想取消保护，单击“更改”组中的“撤消工作表保护”按钮即可。防止“编辑栏”显示公式相关知识使用公式和函数03函数是预先定义好的内置公式，是一种特殊的公式，可以完成复杂的计算。（1）函数的格式。函数的格式为“函数名（参数1，参数2……）”。其中，参数可以是数字、文本、逻辑型数据、单元格引用或表达式等，还可以是常量、公式或其他函数。所有在函数中使用的标点符号，若不是作为文本输入的，都必须是英文符号。（2）函数的输入。①选择要输入函数的单元格，在“编辑栏”中输入“=”，再输入具体的函数，按Enter键，完成函数的输入。②选择要输入函数的单元格，单击“编辑栏”左侧的“插入函数”按钮或在“公式”选项卡的“函数库”组中单击的“插入函数”按钮，打开“插入函数”对话框，选择需要的函数，单击“确定”按钮，打开“函数参数”对话框，设置需要的函数参数，最后单击“确定”按钮即可完成函数的输入。使用函数相关知识使用公式和函数03（3）函数的嵌套。在某些情况下，可能需要将某函数作为另一函数的参数，这就是函数的嵌套。Excel2013中函数的嵌套可达64层。（4）自动求和。在Excel2013中，自动求和按钮被赋予了更多的功能，借助这个功能更强大的自动求和函数，可以快速计算选中单元格的平均值、最小值或最大值等。其使用方法为：选中某列要计算的单元格，或者选中某行要计算的单元格，在“公式”选项卡的“函数库”组中单击“自动求和”下拉按钮，在其下拉列表中选择要使用的函数，然后按Enter键即可。如果要进行求和的是m行×n列的连续区域，并且此区域的右边一列和下面一行是空白，用于存放每行之和及每列之和，可选中该区域及其右边一列或下面一行，也可以两者同时选中，单击“自动求和”按钮，则在选中区域的右边一列或下面一行会自动生成求和公式，得到计算结果。使用函数相关知识使用公式和函数03可通过以下步骤完成本任务：（1）在“计173班学生成绩表”中选中J2单元格，在其中输入“评优”，然后单击I2单元格，在“开始”选项卡的“剪贴板”组中单击“格式刷”按钮，再单击J2单元格，使之与I2格式相同，如图4-42所示。任务实现使用公式和函数03（2）用同样的方法，在C41、C42、C43单元格中分别输入“平均分”“最高分”“最低分”，并设置其格式与J2相同。首先计算每个学生的总成绩，将光标定位于H3单元格，然后输入“=SUM(D3:G3)”并按Enter键，则可发现在H3单元格中出现了对应学生的总成绩，如图4-43所示。任务实现使用公式和函数03注意：使用公式前需要先输入“=”。（3）其他学生的总成绩可以通过复制公式的方式来完成。将鼠标指针移到H3单元格右下角，当鼠标指针变为黑十字形状时，向下拖动鼠标指针到H39单元格，则“总分”列中的所有成绩被计算出来，如图4-44所示。任务实现使用公式和函数03注意：由于表格中成绩有“缺考”一项，属于非数值型数据，所以不能采用A+B+C这种形式，而只能采用SUM(A:C)的形式。（4）计算各科的平均分。将光标定位于D41单元格，然后输入平均分的计算公式“=AVERAGE(D3:D39)”并按Enter键，则可计算出英语成绩的平均分，如图4-45所示。任务实现使用公式和函数03（5）其他3科的成绩平均分可以采用复制公式的方式完成。计算最高分需要使用MAX()函数，使用方式与SUM()、AVERAGE()函数基本相同。将光标定位于D42单元格，输入公式“=MAX(D3:D39)”并按Enter键，则可计算出英语成绩的最高分，其他各科成绩的最高分可通过复制公式的方式完成。计算最低分使用MIN()函数，将光标定位于D43单元格，然后输入公式“=MIN(D3:D39)”并按Enter键，则可计算出英语成绩的最低分，其他各科最低分可通过复制公式的方式完成，结果如图4-46所示。任务实现使用公式和函数03注意：表格中存在“缺考”数据，在使用公式的过程中，该数据是按照“空”来处理的，因此计算出的最低分不是“缺考”。若想要将“缺考”按照最低分来计算，则需要使用更加复杂的条件函数来处理。关于此，用户可自行查阅相关资料。（6）计算“名次”列需要使用RANK()函数。将光标定位于I3单元格，输入公式“=RANK(H3，$H$3:$H$39)”并按Enter键，则可计算出第一名学生总成绩的排名，其他学生总成绩的排名可通过复制公式的方式完成，结果如图4-47所示。注意：这里因为通过总成绩来计算排名，所以需要将数据区域限定在H3:H39单元格区域，故使用绝对地址进行标识，否则在复制公式的时候会出现调用单元格区域不一致的情况，从而造成计算不准确。任务实现使用公式和函数03任务实现使用公式和函数03（7）计算“评优”列。在判断每名学生的成绩是优秀、良好、及格和不及格之前，需要先确定标准，此处的判断标准为：若总分大于等于340，则为优秀；若总分大于等于300而小于340，则为良好；若总分大于等于240而小于300，则为及格；若总分小于2