《(3.4)--5.公式与函数Excel数据处理与分析.ppt》由会员分享，可在线阅读，更多相关《(3.4)--5.公式与函数Excel数据处理与分析.ppt（84页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第 5 5 章章 名称与常用函数名称与常用函数认识认识n函数好比函数好比EXCEL的的武林秘笈武林秘笈，如果要成为，如果要成为EXCEL高高手手，谁都必须学习它。函数也是，谁都必须学习它。函数也是EXCEL与与Word之类之类软件中的表格的软件中的表格的主要区别主要区别，它使，它使EXCEL具有强大的运具有强大的运算和数据处理能力。算和数据处理能力。nEXCEL提供了涉及各个不同领域的函数接近提供了涉及各个不同领域的函数接近400个个。运用它们解决工作中的实际问题，能够极大提高工作运用它们解决工作中的实际问题，能够极大提高工作效率效率，使枯燥的数据处理工作变得轻松、简单。，使枯燥的数据处理工
2、作变得轻松、简单。本章本章学习目标学习目标1 1、掌握名称的用法、掌握名称的用法2 2、了解中、了解中Excel公式和函数的作用公式和函数的作用3 3、掌握常用、掌握常用逻辑逻辑函数的用法函数的用法4 4、掌握常见统计函数的用法、掌握常见统计函数的用法5 5、掌握日期函数的用法、掌握日期函数的用法6 6、掌握常见文本类函数、掌握常见文本类函数7 7、了解函数使用中常见的错误、了解函数使用中常见的错误5.1 名称名称5.1.1、名称概述、名称概述 n可以给一个单元格或单元格区域取一个名称，这个名称可可以给一个单元格或单元格区域取一个名称，这个名称可以出现在公式中，用来指代引用的单元格或单元格区域
3、，以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。这会使公式的意义更加明确。n名称其实是一个标识符，由字母或下划线开头的一个或多名称其实是一个标识符，由字母或下划线开头的一个或多个字符组成。名称不区分字母的大小写，比如个字符组成。名称不区分字母的大小写，比如ABC、abc、Abc都是同一个名称。在都是同一个名称。在Excel中，还可以用中，还可以用汉字汉字作为名作为名称。称。n可以为同一单元格或单元格区域定义可以为同一单元格或单元格区域定义多个多个不同的不同的名称名称。n名称一旦定义就可以在同一工作薄的不同名称一旦定义就可以在同一工作薄的不同工作表工作表之间共用之间
4、共用。5.1 名称名称2、名称的意义、名称的意义n在在Excel中，同一个工作簿中的名称是共享的，中，同一个工作簿中的名称是共享的，在一个工作表中定义的名称，可以被另一个工在一个工作表中定义的名称，可以被另一个工作表的公式引用，这使公式具有更强大的功能，作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的它能够利用单元格的名称名称在不同的工作表中在不同的工作表中查查找找到正确的到正确的单元格单元格或单元格或单元格区域区域。n名称给单元格的引用带来了许多方便，因为它名称给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，使人们不必记住单元格在工作表中的引用位置，
5、用用名称名称就能找到它。就能找到它。5.1.2 名称的定义名称的定义n在在Excel中，可以为一个独立的单元格、连续中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单的单元格区域或许多不连续的单元格构成的单元格组合定义一个名称（或多个名称）元格组合定义一个名称（或多个名称）n定义名称有多种方法，如定义、粘贴、指定或定义名称有多种方法，如定义、粘贴、指定或标志等标志等 5.1.2 名称的定义名称的定义(1)、使用命令定义单元格或单元格区域的名称、使用命令定义单元格或单元格区域的名称【例例5.7】在下图中，在下图中，B2单元格被命名为单元格被命名为“存款利率存款利率”，在，在
6、D5中输入的中输入的公式是公式是“=C5*存款利率存款利率”，其中的，其中的“存款利率存款利率”就是就是B2单元格的名称。单元格的名称。B2单元格的名称定义方法如下：单元格的名称定义方法如下：5.1.2 名称的定义名称的定义n（1）单击）单击“公式公式”|“定义的名称定义的名称”组中组中|“定义名称定义名称”命令按钮命令按钮.n（2）在弹出的）在弹出的“新建名称新建名称”对话框的对话框的“引用引用位置位置”输入要定义名称的单元格输入要定义名称的单元格引用位置引用位置。n（3）在）在“在当前工作簿中的名称在当前工作簿中的名称”中输入中输入名称名称 5.1.2 名称的定义名称的定义(2)、使用指定
7、的方式定义名称、使用指定的方式定义名称n“指定指定”的方式一次可定义的方式一次可定义多个名称多个名称：可以将：可以将表格的首行或首列指定为相应的列或行的名称。表格的首行或首列指定为相应的列或行的名称。n方法方法n（1）选中选中要指定名称的单元格要指定名称的单元格区域区域。n（2）单击）单击“公式公式”|“定义的名称定义的名称”组中组中|“根据所根据所选内容创建选内容创建”命令按钮。命令按钮。n（3）在弹出的）在弹出的“以选定区域创建名称以选定区域创建名称”对话框中，对话框中，选中选中“首行首行”、“最左列最左列”的复选框。的复选框。这样就把该表的首行、首列指定成了相应的列、行的这样就把该表的首
8、行、首列指定成了相应的列、行的名称名称5.1.2 名称的定义名称的定义n指定首行、最左列为名称的案例指定首行、最左列为名称的案例n【例例5.8】图图5.10是某建筑工地的工人奖金统计表，是某建筑工地的工人奖金统计表，一共有几百个工人，其中的某些工人会随时查询自己一共有几百个工人，其中的某些工人会随时查询自己的奖金，这就需要在的奖金，这就需要在A列的几百行数据中进行查找。名列的几百行数据中进行查找。名字能够很好地解决这类问题，方法如下。字能够很好地解决这类问题，方法如下。5.1.2 名称的定义名称的定义(3)用名称栏定义名称用名称栏定义名称n工作表全选按钮（工作表列标工作表全选按钮（工作表列标A
9、左边的按钮）上面的编辑框称为左边的按钮）上面的编辑框称为名称栏名称栏，它随时显示当前单元格的名称或引用。它可以用来定义，它随时显示当前单元格的名称或引用。它可以用来定义名称。名称。n【例例5.9】在图在图5.11中，中，C4:C8区域中输入的是数组区域中输入的是数组公式：公式：“=数量数量*单价单价”，其定义方法请见图中的标注。，其定义方法请见图中的标注。名称栏名称栏5.1.3 名称的应用名称的应用n定义一个工作薄级的名字后，该名字就定义一个工作薄级的名字后，该名字就能被本工作簿中的各个工作表直接引用。能被本工作簿中的各个工作表直接引用。【例例5.10】图图5.12是某蔬菜超市的销售是某蔬菜超
10、市的销售记录。该超市把蔬菜单价存放在一张工记录。该超市把蔬菜单价存放在一张工作表中，如图作表中，如图5.12（a）所示，每种蔬菜）所示，每种蔬菜的销售记录则保存在不同的工作表中，的销售记录则保存在不同的工作表中，如图如图5.12（b）所示。现以小白菜的销售）所示。现以小白菜的销售金额计算为例，说明名字的跨表引用问金额计算为例，说明名字的跨表引用问题。题。5.1.3 名称的应用名称的应用1）通过名称在不同的工作表之间）通过名称在不同的工作表之间传递数据传递数据 通过名称查找蔬菜单价通过名称查找蔬菜单价指定指定A2:B12最左边为名称最左边为名称5.1.3 名称的应用名称的应用2)名称结合名称结合
11、Indirect函数的应用函数的应用n指定指定A2:B12的的最左列为最左列为名称；名称；n用用名称查找名称查找蔬菜单价，见蔬菜单价，见D列；列；n若不用名称，单价要从若不用名称，单价要从C列的几百个数据中去查列的几百个数据中去查找！找！这种方法虽然比直接从工作表的单元格中查找这种方法虽然比直接从工作表的单元格中查找数据方便了不少，但数据方便了不少，但输入量仍然较大输入量仍然较大。用它处。用它处理成千上万行的数据查询仍然理成千上万行的数据查询仍然低效低效率。最佳方率。最佳方法是用法是用名称和名称和Indirect函数函数相结合，非常方便，相结合，非常方便，高效。高效。5.1.3 名称的应用名称
12、的应用n某蔬菜商在一个工作表中保存蔬菜的单价，在另一某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各工作表中保存销售记录，现要查找销售记录表中各蔬菜的单价。蔬菜的单价。指定指定A2:B11区域区域的的“最左列最左列”为名为名称称在D2中输入公式：=INDIRECT(B2)向下复制该公式！5.1.3 名称的应用名称的应用n2）名称与）名称与Indirect函数结合应用函数结合应用nIndirect函数与函数与名称名称相结合，可使数据查找更方便。相结合，可使数据查找更方便。Indirect函数的用法是：函数的用法是：Indirect(x)其中的其中的X可以是
13、单元格引用或名称。可以是单元格引用或名称。n若若X为单元格引用，它必须用为单元格引用，它必须用“”引起来，如：引起来，如：=INDIRECT(“A1”)，将返回，将返回A1单元格中的内容单元格中的内容n若若X为单元格名称，它将返回该名称所对应的单元格的为单元格名称，它将返回该名称所对应的单元格的内容。内容。5.1.3 名称的应用名称的应用n3)应用行列交叉点查找数据应用行列交叉点查找数据n所谓行列交叉点指的是由所谓行列交叉点指的是由行的名称行的名称 列的名称列的名称确定的确定的单元格。单元格。n把一个工作表的首行、首列指定为名称之后，可以使把一个工作表的首行、首列指定为名称之后，可以使用名称引
14、用行、列交叉点对应单元格中的数据。用名称引用行、列交叉点对应单元格中的数据。n案例案例n【例例5.11】图图5.13是一个学生成绩表，用指定的是一个学生成绩表，用指定的方式把它的首行、首列指定成了名字。方式把它的首行、首列指定成了名字。5.1.3 名称的应用名称的应用1、选中、选中A1:F8区域区域2、选择、选择“公式公式”|“定义的名称定义的名称”|“根据所选内容创建根据所选内容创建”命令命令4、指定名字对话框中、指定名字对话框中和和项为名称项为名称现在做试验现在做试验在在L2单元格中输入公式单元格中输入公式“=李达李达 化学化学”5.1.3 名称的应用名称的应用4)引用其他工作簿中的数据引
15、用其他工作簿中的数据 n当一个公司的数据来源于不同的分公司或当一个公司的数据来源于不同的分公司或部门时，往往需要对不同分公司或部门的部门时，往往需要对不同分公司或部门的数据进行汇总，这就要涉及多个工作薄的数据进行汇总，这就要涉及多个工作薄的数据操作。名称可以很好地解决这个问题。数据操作。名称可以很好地解决这个问题。n【例例5.12】采虹电视经销全国各省，每季度采虹电视经销全国各省，每季度都要统计各种型号电视的销售数量。各省的销都要统计各种型号电视的销售数量。各省的销售代理每季度向采虹厂提供一个含有各种型号售代理每季度向采虹厂提供一个含有各种型号电视的汇总统计表。现在对各省的报表数据进电视的汇总
16、统计表。现在对各省的报表数据进行汇总统计。行汇总统计。指定指定A3:B8区区域域”最左最左列列”为名称为名称指定指定A3:B8区区域域”最左最左列列”为名称为名称B3单元格中输入公式“=四川四川.xls!_21英寸英寸+重庆重庆.xls!_21英寸英寸”；在B4单元格中输入公式“=四川四川.xls!_25英寸英寸+重庆重庆.xls!_25英寸英寸”，其余以此类推 5.1.4 名称管理器名称管理器选择选择“公式公式”选项卡，单击选项卡，单击其中其中“定义的名称定义的名称”组中组中的的“名称管理器名称管理器”按钮按钮,显示出名称管理器对话框显示出名称管理器对话框 通过通过“名称管理器名称管理器”可
17、可以新建以新建编辑编辑删除名称删除名称,可以可以重指定名称的含义或对应的单重指定名称的含义或对应的单元格引用位置元格引用位置.5.2 函数简介函数简介1、函数的概念、函数的概念n函数是函数是能够完成特定功能的能够完成特定功能的程序程序。在。在Excel中，中，它是系统它是系统预定义预定义的一些的一些公式公式，它们使用一些称，它们使用一些称为为参数参数的特定数值按特定的的特定数值按特定的顺序顺序或结构进行计或结构进行计算，然后把计算的算，然后把计算的结果结果存放在某个单元格中。存放在某个单元格中。n在大多数情况下，函数的计算结果是数值。当在大多数情况下，函数的计算结果是数值。当然，它也可以返回文
18、本、引用、逻辑值、数组然，它也可以返回文本、引用、逻辑值、数组或工作表的信息。或工作表的信息。5.2 函数简介函数简介n2、Excel函数分类函数分类分 类功 能 简 介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计算等外部函数进行外部函数调用及数据库的链接查询等功
19、能自定义函数用户用vba编写，用于完成特定功能的函数5.2.1 函数调用函数调用1、函数的语法、函数的语法 n函数名函数名(参数参数1,参数参数2,参数参数3,)n在公式中调用函数在公式中调用函数n=IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套函嵌套函数数Excel 2007允许嵌套多达允许嵌套多达64层层Excel 2007可达可达255个参数个参数5.2.2 函数输入函数输入n在公式中直接输入函数调用在公式中直接输入函数调用n用函数调用向导输入用函数调用向导输入1手工输入函数 如果用户能记住函数的名称、参数，可直接在单元格中输入函数。2使用函数向导输入函数 如果不
20、能确定函数的拼写或参数，可以使用函数向导输入函数。5.2.3 使用使用Excel帮助理解函数帮助理解函数nExcel的帮助系统具有极其强大的功能，利用帮的帮助系统具有极其强大的功能，利用帮助系统能够解决使用助系统能够解决使用Excel过程中所遇到的各种过程中所遇到的各种问题。这些问题包括问题。这些问题包括Excel的新技术、疑难、专的新技术、疑难、专用名字解决、函数说明、函数应用实例等。用名字解决、函数说明、函数应用实例等。nExcel的帮助系统比许多参考书内容更全面，讲的帮助系统比许多参考书内容更全面，讲述更清楚，一个述更清楚，一个Excel的真正用户应该会运用帮的真正用户应该会运用帮助系统
21、，从中获取有用的资料。进入助系统，从中获取有用的资料。进入Excel帮助帮助系统的常用方法有：系统的常用方法有：n按按F1键。键。n单击功能选项卡标题栏最右边的？按钮单击功能选项卡标题栏最右边的？按钮 5.3 逻辑函数逻辑函数n5.3.1 Excel的比较运算符的比较运算符n比较运算又称关系运算，就是人们常说的比较式。比较运算又称关系运算，就是人们常说的比较式。比较运算只有两种不同的结果，要么比较运算只有两种不同的结果，要么“正确正确”，要，要么么“错误错误”，不可能有第三种结果，不可能有第三种结果 比比较较运算符运算符含含 义义示示 例例=等于等于a1=3大于大于a1b1小于小于a1=大于等
22、于大于等于a1=60=小于等于小于等于a1=”dd”不等于不等于a105.3.2 AND、NOT、OR逻辑逻辑函数函数nAND、NOT、OR函数函数n用法用法nAND(x1,x2,x30)nOR(x1,x2,x30)nNOT(logical)n案例案例nAND(x100,x100,y500)n如果如果B1，B2，B3单元格中的值为单元格中的值为 TRUE，FALSE，TRUE，则则AND(B1:B3)=FALSE，但，但OR(B1:B3)=TRUE。nAND(2,2+3=5)=TRUE5.3.3 条件函数条件函数IFn用法用法nIF(条件条件,表达式表达式1,表达式表达式2)。n功能功能n当条
23、件成立时，计算出表达式当条件成立时，计算出表达式1的值；当条件不成的值；当条件不成立时，计算出表达式立时，计算出表达式2的值的值n事例事例nIf(A160,“及格了及格了”,“不及格不及格”)，若，若A1单元格单元格的值是的值是76，则该函数的结果是：及格了；若，则该函数的结果是：及格了；若A1单单元格的值是元格的值是50，则该函数的结果是，则该函数的结果是:不及格。不及格。nIF函数的嵌套调用函数的嵌套调用nExcel 2007允许嵌套多达允许嵌套多达64层层5.3.4 逻辑函数的应用逻辑函数的应用n【例例5.15】某单位有某单位有1 000多位党员，要收党费。多位党员，要收党费。党费根据工
24、资的高低，按党费根据工资的高低，按不同的费率收取，收费的不同的费率收取，收费的费税如右表所示。费税如右表所示。n类似于这样的工作，使用类似于这样的工作，使用IF函数进行数据的匹配与函数进行数据的匹配与查找是非常方便的。查找是非常方便的。n本例解决方法如下。本例解决方法如下。工工 资资党党费费费费率率2 000以上以上3%1 5002 0002.5%1 2001 5002%1 0001 2001.5%8001 0001%800以下以下05.4.4 逻辑函数的应用逻辑函数的应用nF3中公式意思为：当中公式意思为：当E3大于大于2 000时，公式的值为时，公式的值为3%；当当E3小于等于小于等于2
25、000，但大于，但大于1 500时，公式的值为时，公式的值为2.5%；其余的以此类推；其余的以此类推 5.3.4 逻辑函数的应用逻辑函数的应用n【例例5.16】某班考查成绩如下图某班考查成绩如下图A1:E10所示，将所示，将D列的列的地理成绩转换成等级制。地理成绩转换成等级制。5.4 统计函数统计函数5.4.1、汇总求和函数、汇总求和函数 n自动求和按钮自动求和按钮1、选中F62、单击“公式公式”选项卡中“自动求和”按钮，就能求出F3、F4、F5之和。3、同方法求其它汇总和。5.4.1、汇总求和函数、汇总求和函数2、SUM函数函数n用法用法SUM(x1,x2,x255)x1，x2，x30是需要
26、求和的参数是需要求和的参数，可以是数据或单元格区域，可以是数据或单元格区域n功能功能nsum计算各参数的数值之和计算各参数的数值之和n说明：说明：参数表中的数字、逻辑值及数字的文本表达式将被计算，文本值参数表中的数字、逻辑值及数字的文本表达式将被计算，文本值被转换成数字，而逻辑值被转换成数字，而逻辑值“true”被转换成数字被转换成数字1。例如，例如，SUM(3,2)=5，SUM(9,20,true)=30。5.4.1、汇总求和函数、汇总求和函数n 如果参数为数组或引用，那么只有其中的数字被计如果参数为数组或引用，那么只有其中的数字被计算。数组或引用中的空白单元格、逻辑值、文本或错算。数组或引
27、用中的空白单元格、逻辑值、文本或错误值将被忽略。误值将被忽略。n例如，设例如，设A1的值为的值为“9”，A2为为true，则公式，则公式SUM(A1,A2,20)的计算结果为的计算结果为20，而不是，而不是30。因。因为本公式中包括两个引用为本公式中包括两个引用A1、A2，而，而A1的值为文的值为文本，本，A2的值为逻辑值，它们在计算时被忽略，最终的值为逻辑值，它们在计算时被忽略，最终就只有一个数值就只有一个数值20参与运算。参与运算。n 参数最多可达参数最多可达255个，不同类型的参数可以同时出个，不同类型的参数可以同时出现。现。5.4.1 汇总求和函数汇总求和函数3、条件求和函数、条件求和
28、函数SUMIFn用法用法SUMIF(range,criteria,sum_range)n其中，其中，range是用于条件判断的单元格区域，是用于条件判断的单元格区域，criteria条件，条件，其形式可以为数字、表达式或文本；其形式可以为数字、表达式或文本；nsum_range是需求和的实际单元格。只有当是需求和的实际单元格。只有当range中的相应单元格满足条件时，才对中的相应单元格满足条件时，才对sum_range中的单元格求和。若省略中的单元格求和。若省略sum_range，则直接对，则直接对range中的单元格求和。中的单元格求和。n功能功能 n对对range单元格区域中的数据进行单元
29、格区域中的数据进行Criteria条件检查，然后对条件检查，然后对满足条件行的满足条件行的sum_range同行进行求和。同行进行求和。5.4.1 汇总求和函数汇总求和函数4、条件求和函数、条件求和函数SUMIFSn用法用法SUMIFS(sum_range,range1,criteria1,range2,criteria2)n其其中中range1,range2,是是计计算算关关联联条条件件的的 1 至至 127 个个区区域域；Criteria1,criteria2,是是数数字字、表表达达式式、单单元元格格引引用用或或文文本本形形式式的的 1 至至 127 个个条条件件，用用于于定定义义要要对对
30、哪哪些些单单元元格格求求和和。这这些些区区域域与与条条件件是是对对应应的的，即即Criteria1是是用用于于range1区区域域的的条条件件，criteria2是是用用于于range2的的条条件件，以以此此类类推推。Sum_range是求和区域。是求和区域。n功能功能 nSUMIFS函数对某一区域内满足多重条件的单元格求和。函数对某一区域内满足多重条件的单元格求和。5.4.1 汇总求和函数汇总求和函数【例例5.18】某某家家电电商商场场的的销销售售记记录录如如图图5.22的的A1:G13所所示示。现现要要统统计计出出各各种种电电器器的的销销售售总总数数量量和和销销售售总总金金额额，并并将将它
31、它统统计计在在I1:K6区区域域的的对对应应单单元元格格中中；统统计计每每位位职职工工销销售售各各种种产产品品的的总总数数量量，并并将将它它统统计在计在I8:L13区域中。区域中。（1）在图5.22的J3单元格中输入公式：=SUMIF($D$3:$D$10，I3，$E$3:$E$10)(2)将此公式向下复制到将此公式向下复制到J4、J5、J6（1）在K3单元格输入公式：=SUMIF($D$3:$D$10，I3，$G$3:$G$10)(2)将此公式向下复制到将此公式向下复制到K4、K5、K6在J10单元格中输入统计李本成销售的彩电总数量的计算公式：=SUMIFS($E$3:$E$13,$D$3:
32、$D$13,$I10,$A$3:$A$13,J$9)(2)将此公式向下角复制到将此公式向下角复制到L13，5.4.2 平均值函数平均值函数1、平均值函数、平均值函数AVERAGE，AVERAGEA n用法用法nAVERAGE(n1，n2，n255)nAVERAGEA(n1，n2，n255)n其中，其中，n1，n2，n255是要计算平均值的参数，该是要计算平均值的参数，该函数最多允许有函数最多允许有255个参数个参数。参数可以是数字，或者是涉。参数可以是数字，或者是涉及数字的及数字的名称名称、数组数组或或引用引用 n功能功能n两函数都是求参数的两函数都是求参数的平均值平均值。AverAge不对不
33、对文本文本和和逻辑值逻辑值类数据进行计算；类数据进行计算；nAverageA要对文本和逻辑函数进行平均值计算：要对文本和逻辑函数进行平均值计算：文本文本被视为被视为0，逻辑值，逻辑值true被视为被视为1，false被视为被视为0，空文本（，空文本（）也作为）也作为0计算计算 5.4.2 平均值函数平均值函数n计数函数案例计数函数案例n下图说明下图说明AVERAGE函数与函数与AVERAGEA函数的区别函数的区别 5.4.2 平均值函数平均值函数2条件平均值函数条件平均值函数nExcel2007提供了两个根据条件计算平均值的函数提供了两个根据条件计算平均值的函数AVERAGEIF和和AVERA
34、GEIFS。AVERAGEIF(average_range,range1,criteria1)AVERAGEIFS(average_range,range1,criteria1,range2,criteria2)n其中其中average_range是要计算平均值的一个或多个单元格，是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用；其中包括数字或包含数字的名称、数组或引用；range1,range2,是计算关联条件的是计算关联条件的 1 至至 127 个区域；个区域；Criteria1是用于是用于range1的条件的条件,criteria2是用于是用于range2的条件
35、的条件,它可以是数字、表达式、单元格引用或文本形式它可以是数字、表达式、单元格引用或文本形式的的 1 至至 127 个条件，用于定义要对哪些单元格求平均值。个条件，用于定义要对哪些单元格求平均值。5.4.2 平均值函数平均值函数【例例5.20】某高三年级的期末考试成绩表如图某高三年级的期末考试成绩表如图5.21的的B1:F10区域所示，统计每个班男女生的各科目平均成绩。区域所示，统计每个班男女生的各科目平均成绩。在在J4中输入如下公式中输入如下公式,并将它向下向右复制到其它单元格并将它向下向右复制到其它单元格=AVERAGEIFS(D$4:D$10,$B$4:$B$10,$H4,$C$4:$C
36、$10,$I4)5.4.3 统计个数统计个数的函数的函数1、计数函数、计数函数COUNT、COUNTA、COUNTBLANK、COUNTIF n用法用法nCOUNT(x1,x2,x30)nCOUNTA(x1,x2,x30)nCOUNTBLANK(range)n功能功能nCount 统计数字的个数统计数字的个数nCountA统计数字和文本的个数统计数字和文本的个数nCountBlank统计空白单元格的个数统计空白单元格的个数5.4.3 统计个数统计个数的函数的函数【例例5.21】用用count和和countA函数统计单元格个数。函数统计单元格个数。图图5.22是用这两个函数统计同一区域的不同结果
37、。是用这两个函数统计同一区域的不同结果。5.4.3 统计个数统计个数的函数的函数2、COUNTIF函数函数n用法用法nCOUNTIF(range,criteria)n功能功能 n统计出统计出range中满足条件中满足条件criteria的数据个数。的数据个数。3、CountifsExcel 2007还提供了一个多条件统计函数还提供了一个多条件统计函数COUNTIFS，它可，它可以一次对多个不同区域进行不同条件的计数，其用法如下：以一次对多个不同区域进行不同条件的计数，其用法如下：COUNTIF(range1，criteria1，range2，criteria2，range127，criteri
38、a127)5.4.3 统计个数统计个数的函数的函数n【例例5.22】有学生成绩表如图有学生成绩表如图5.23所示。统计其中总分所示。统计其中总分220分以下的人数；统计总分分以下的人数；统计总分200分以上，物理分以上，物理80分以下，分以下，地理地理70分以下，化学分以下，化学65分以下的人数；统计姓王的同学分以下的人数；统计姓王的同学人数。人数。5.4.3 统计个数统计个数的函数的函数3计算大小、百分比和名次的函数计算大小、百分比和名次的函数LARGE(array,k)SMALL(array,k)nLARGE函数返回数据集中第函数返回数据集中第k个最大值，个最大值，SMALL返回数据集中第
39、返回数据集中第 k 个最小值，这两个最小值，这两个函数可以根据相对标准来选择数值，返个函数可以根据相对标准来选择数值，返回数据集中特定位置上的数值。例如，可回数据集中特定位置上的数值。例如，可以使用函数以使用函数LARGE得到第一名、第二名或得到第一名、第二名或第三名的得分。第三名的得分。5.4.3 统计个数统计个数的函数的函数n中间值函数中间值函数MEDIAN和众数函数和众数函数MODEMEDIAN(number1,number2,.)MODE(number1,number2,.)nMEDIAN函数计算出给定数据的中值，函数计算出给定数据的中值，MODE函数返回一组数中出现次数最多的数函数返
40、回一组数中出现次数最多的数（众数）。（众数）。5.4.3 统计个数统计个数的函数的函数n排名函数排名函数RANKRANK(number,ref,order)。nRANK函数返回一个数字在一组数中的排位，即位函数返回一个数字在一组数中的排位，即位次。次。nNumber为需要找到排位的数字，为需要找到排位的数字，Ref可以是一个可以是一个数组或单元格区域，数组或单元格区域，Ref 中的非数值型参数将被忽中的非数值型参数将被忽略。略。Order用于指明排位的方式，如果为用于指明排位的方式，如果为 0或省略，或省略，则则Rank 对数字的排位是基于对数字的排位是基于 ref 为按照降序排列为按照降序排
41、列的列表；如果的列表；如果 order 不为零，则不为零，则Rank对数字的排对数字的排位是基于位是基于 ref 为按照升序排列的列表。为按照升序排列的列表。【例例5.23】有学生成绩表如图有学生成绩表如图5.24中中A1:F10区域所示。计区域所示。计算各学生成绩的名称、百分比排位、第算各学生成绩的名称、百分比排位、第3名和倒数第名和倒数第3名名总分、中间成绩、出现次数最多的分数及在总分、中间成绩、出现次数最多的分数及在38%总分位总分位置的分数大致是多少等数据。置的分数大致是多少等数据。在G3中输入公式：=RANK(F3,$F$3:$F$10)向下复制此公式可计算名次在H3中输入公式：=P
42、ERCENTRANK($F$3:$F$10,F3,2)向下复制此公式可计算名次=LARGE(F3:F10,3)=SMALL(F3:F10,3)=MEDIAN(F3:F10)=MODE(C3:E10)=PERCENTILE(F3:F10,0.38)5.4.4 统计函数的综合应用统计函数的综合应用n【例例5.24】某班期末成绩表如图所示。现要统计每位同某班期末成绩表如图所示。现要统计每位同学的总分，各科目的应考人数、缺考人数及各科平均成绩学的总分，各科目的应考人数、缺考人数及各科平均成绩 5.5 数学数学和三角函数和三角函数n1、概述、概述nExcel提供了许多数学和三角函数，它们能够提供了许多数
43、学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。返回到输入公式的单元格中。2、常见的数学函数、常见的数学函数nMOD、TRUNC、ABS、SQRT、SIN、ASIN(n1)，ACOS(n1)，ATAN(n2)、EXP(n)、POWER(x,n)、LN(n)、FACT(n)、LOG(n,base)、MINVERSE(array)、MMULT(array1,array2)5.5 数学数学和三角函数和三角函数3、数学函数应用案例、数学函数应用案例【例
44、例5.25】用随机函数产生大量的实验数据，计算排名。用随机函数产生大量的实验数据，计算排名。问问题题：有有一一张张工工资资工工作作表表，结结构构如如图图所所示示。现现以以此表为例说明随机函数和数组的结合使用。此表为例说明随机函数和数组的结合使用。5.5 数学数学和三角函数和三角函数n产生日期产生日期（1）在）在B2单元格中输入参加工作的最早时间，即在单元格中输入参加工作的最早时间，即在B2单元格中输入单元格中输入“1970/1/1”。（2）选中要产生日期的单元格区域，如）选中要产生日期的单元格区域，如B3:B8。（3）输入公式）输入公式“=B2+RAND()*1825”。（4）按）按Ctrl+
45、Enter键。键。（5）将）将B3:B8格式化为需要的日期格式。格式化为需要的日期格式。n产生加班时间，计算加班工资产生加班时间，计算加班工资n（1）选中）选中C2:C8单元格区域。单元格区域。n（2）输入公式）输入公式“=INT(RAND()*600)”。n（3）按）按Ctrl+Enter键。键。5.5 数学数学和三角函数和三角函数n产生基本工资产生基本工资假设基本工资在假设基本工资在8003 000这个范围内，其产生方法如下：这个范围内，其产生方法如下：（1）选择）选择D2:D8单元格区域。单元格区域。（2）输入公式）输入公式“=800+int(RAND()*2200)”。（3）按）按Ct
46、rl+Enter键。键。5.6日期日期及时间函数及时间函数 1、Excel处理日期的方式处理日期的方式nMicrosoft Excel 将日期存储为序列号（称为序列值），即一系将日期存储为序列号（称为序列值），即一系列连续的数字编号，每一个数字编号代表一个日期。在默认情况列连续的数字编号，每一个数字编号代表一个日期。在默认情况下，数字下，数字1代表代表1900 年年 1 月月1 日，日，2代表代表1900年年1月月2日，日，39,448代表代表2008 年年 1 月月 1 日，因为从日，因为从1900 年年 1 月月 1 日到日到2008 年年 1 月月 1 日正好日正好 39,448 天。天
47、。nExcel 将时间存储为小数，因为时间被看作天的一部分。时间也将时间存储为小数，因为时间被看作天的一部分。时间也被存为序列号，此序列号以秒为单位递增。因为一天共有：被存为序列号，此序列号以秒为单位递增。因为一天共有：24*60*60=86400秒，所以秒，所以1/86400代表的时间是：代表的时间是：00:00:01，2/86400代表的时间是：代表的时间是：00:00:02。n日期和时间都是数值，因此它们也可以进行各种运算。日期和时间都是数值，因此它们也可以进行各种运算。如果要计算两个日期之间的差值，可以用一个日期减如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。去另一个日期
48、。5.6日期日期及时间函数及时间函数2DATE函数函数n用法用法nDATE(year,month,day)n功能功能nDATE函数利用所给的参数，构造一个日期序列数函数利用所给的参数，构造一个日期序列数 n例如例如nDATE(2005,3,21)的结果是的结果是2005-3-215.6日期日期及时间函数及时间函数3YEAR，MONTH，DAY函数函数n用法用法nYEAR(serial_number)nMONTH(serial_number)nDAY(serial_number)n其中的其中的serial_number是一个日期或数字。是一个日期或数字。n功能功能nYEAR函数返回某日期的年份。
49、函数返回某日期的年份。nMONTH函数返回以系列数表示的日期中的月份。函数返回以系列数表示的日期中的月份。nDAY函数返回以系列数表示的某日期的天数，用整函数返回以系列数表示的某日期的天数，用整数数131表示。表示。5.6日期日期及时间函数及时间函数4TODAY、NOW函数函数n用法用法nTODAY()nNOW()n功能功能nTODAY函数返回系统的当前日期。函数返回系统的当前日期。nNOW函数计算当前日期和时间。函数计算当前日期和时间。5.6日期日期及时间函数及时间函数5WEEKDAYn用法用法nWEEKDAY(serial_number,return_type)n其中：其中：nserial
50、_number代表要查找的日期，或日期的系列代表要查找的日期，或日期的系列数，以了解该日期为星期几；数，以了解该日期为星期几；return_type确定返确定返回值类型的数字，。回值类型的数字，。n功能功能nWEEKDAY计算给定的日期是星期几计算给定的日期是星期几 5.6日期日期及时间函数及时间函数6、NETWORKDAYS函数函数n用法用法nNETWORKDAYS(start_date,end_date,holidays)n其中其中nstart_date表示代表开始日期，表示代表开始日期，end_date为终止为终止日期，日期，holidays表示不在工作日历中的一个或多个表示不在工作日历