excel公式名称与函数

1、华信教育资源网华信教育资源网 Excel与数据处理与数据处理 第第3版版华信教育资源网华信教育资源网 认识认识n函数好比函数好比EXCEL的的武林秘笈武林秘笈，如果要成为，如果要成为EXCEL高高手手，谁都必须学习它。函数也是，谁都必须学习它。函数也是EXCEL与与Word之类之类软件中的表格的软件中的表格的主要区别主要区别，它使，它使EXCEL具有强大的具有强大的运算能力。运算能力。nEXCEL提供了涉及各个不同领域的函数接近提供了涉及各个不同领域的函数接近400个个。运用它们解决工作中的实际问题，能够极大提高工作运用它们解决工作中的实际问题，能够极大提高工作效率效率，使枯燥的工作变得轻松、

2、简单。，使枯燥的工作变得轻松、简单。n但学习是一个但学习是一个枯燥而艰辛枯燥而艰辛的过程！当然嘛，谁让你想的过程！当然嘛，谁让你想成为成为高手高手呢？呢？华信教育资源网华信教育资源网 本章本章学习目标学习目标1 1、掌握、掌握数组公式数组公式的用法的用法2 2、掌握、掌握名称名称的用法的用法3 3、掌握常用、掌握常用工作表函数工作表函数的用法的用法4 4、掌握常见、掌握常见统计函数统计函数的用法的用法5 5、掌握、掌握条件函数条件函数的用法，特别是：的用法，特别是：IfsumifcountifIfsumifcountif等等6 6、掌握解、掌握解查找引用类函数查找引用类函数的用法的用法7 7、

3、掌握常见、掌握常见日期、文本日期、文本类函数类函数华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用1、概述、概述n普通公式只执行一个简单计算，并且返回普通公式只执行一个简单计算，并且返回一个一个运算结果运算结果。数组公式能够同时对一组或两组以。数组公式能够同时对一组或两组以上的数据进行计算，计算的结果可能是上的数据进行计算，计算的结果可能是一个一个，也可能是也可能是多个多个。n在数组公式中使用的数据称为在数组公式中使用的数据称为数组参数数组参数，数组，数组参数可以是一个参数可以是一个数据区域数据区域，也可以是数组，也可以是数组常量常量。华信教育资源网华信教育资源网 5.1

4、 数组公式数组公式及其应用及其应用2、数组公式的建立方法、数组公式的建立方法n（1）选中需要保存数组公式结果的单元格或）选中需要保存数组公式结果的单元格或单元格区域。单元格区域。n（2）输入公式的内容。）输入公式的内容。n（3）按）按Ctrl+Shift+Enter键。键。华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用3、用数组公式计算两个数据区域的乘积、用数组公式计算两个数据区域的乘积 【例例5.1】 某茶叶店经销多种茶叶，已知各种茶某茶叶店经销多种茶叶，已知各种茶叶的单价以及叶的单价以及12月份各种茶叶的销量如图所示，月份各种茶叶的销量如图所示，计算各种茶叶的销售额

5、。计算各种茶叶的销售额。华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用4、数组扩展、数组扩展 n在公式中用数组作为参数时，所有的数组必须在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不是同维的。如果数组参数或数组区域的维数不匹配，匹配，Excel会自动扩展该参数会自动扩展该参数 。n比如，比如，LG空调售价为空调售价为7654元，重庆各商场的元，重庆各商场的销售数据如下图的销售数据如下图的B、C两列所示。现要计算两列所示。现要计算各商场的销售总额。各商场的销售总额。 华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用n

6、【例例5.2】 某次期末考试的学生成绩表如图某次期末考试的学生成绩表如图5.2所示，其中有四科成所示，其中有四科成绩，现要计算各科成绩的总分，以及各科成绩的综合测评分数。总分绩，现要计算各科成绩的总分，以及各科成绩的综合测评分数。总分的计算方式为各科成绩的总和，综合测评分的计算方式为的计算方式为各科成绩的总和，综合测评分的计算方式为“政治经济政治经济学学*0.2+(技术经济学技术经济学+计算机基础计算机基础+PASCAL语言程序设计语言程序设计)*0.8”。华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用n【例例5.3】假设某商场假设某商场A型电视机售价一样，现有一个型电

7、视机售价一样，现有一个28天的销售记录，计算每天的销售额，如图天的销售记录，计算每天的销售额，如图5.3所示。所示。D列列销售额的计算方法如下：销售额的计算方法如下：华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用5、二维数组、二维数组 n涉及许多行涉及许多行(或列或列)数据处理的数组公式，就是数据处理的数组公式，就是所谓的二维数组。所谓的二维数组。n合理地运用二维数组的运算功能，会提高数据合理地运用二维数组的运算功能，会提高数据处理的能力，有时在不同工作表之间进行数据处理的能力，有时在不同工作表之间进行数据汇总时会特别有效汇总时会特别有效 。华信教育资源网华信教育资源网

8、 5.1 数组公式数组公式及其应用及其应用n案例案例n【例例5.4】 某商场秋季进行换季服装大降价，所有服装某商场秋季进行换季服装大降价，所有服装都在打折，打折的比例随时都在调整，计算打折后的新都在打折，打折的比例随时都在调整，计算打折后的新价格。如图价格。如图5.4所示。所示。 华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用【例例5.5】出版社要统计出版社要统计2008年发往各地新华书店的各类图书的销售量，年发往各地新华书店的各类图书的销售量，并由此计算出各类图书的总销售额。设每个省都把本地各新华书店的并由此计算出各类图书的总销售额。设每个省都把本地各新华书店的销售量

9、统计在一个工作表中，最后有许多工作表的数据要进行汇总，销售量统计在一个工作表中，最后有许多工作表的数据要进行汇总，图图5.5该出版社该出版社2008年各类图书在四川、重庆两者的销量情况表年各类图书在四川、重庆两者的销量情况表（实际的应用更加复杂）。（实际的应用更加复杂）。 华信教育资源网华信教育资源网 5.1 数组公式数组公式及其应用及其应用n用数组公式汇总图书用数组公式汇总图书华信教育资源网华信教育资源网 5.2 公式公式的循环引用的循环引用1、循环引用的概念、循环引用的概念n如果公式引用了自己所在的单元格，不论是如果公式引用了自己所在的单元格，不论是直直接接的还是的还是间接间接的，都称为的

10、，都称为循环引用循环引用。n在一般情况下，很少使用循环引用来处理问题。在一般情况下，很少使用循环引用来处理问题。但对于但对于迭代迭代求解一类问题，如求数的阶乘、数求解一类问题，如求数的阶乘、数列求和等问题，可以用列求和等问题，可以用循环引用循环引用求解。求解。n若循环引用的若循环引用的条件条件或或迭代次数迭代次数设置不当，设置不当，EXCEL会报告一个错误信息。会报告一个错误信息。 华信教育资源网华信教育资源网 5.2 公式公式的循环引用的循环引用n【例例5.6】下图是循环引用的一个事例。在下图是循环引用的一个事例。在A1单元格中输入的公式单元格中输入的公式“=A2+A3”；在；在A2单元格中

11、输入数字单元格中输入数字2；在；在A3单元格中输入公式单元格中输入公式“=A1*0.3”，见，见图图(a)。这样，。这样，A3单元格的值依赖于单元格的值依赖于A1，而，而A1单单元格的值又依赖于元格的值又依赖于A3，它们形成了间接的循环引用，在未进行迭代，它们形成了间接的循环引用，在未进行迭代求解情况下的结果如求解情况下的结果如图（图（b）所示。该循环引用是所示。该循环引用是可解可解的，进行迭代的，进行迭代求解最后的结果如求解最后的结果如图（图（c）所示。所示。 华信教育资源网华信教育资源网 5.3 名称名称5.3.1、名称概述、名称概述 n可以给一个单元格或单元格区域取一个名称，这个名称可以

12、给一个单元格或单元格区域取一个名称，这个名称可以出现在公式中，用来指代引用的单元格或单元格区可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。域，这会使公式的意义更加明确。n名称其实是一个标识符，由字母或下划线开头的一个或名称其实是一个标识符，由字母或下划线开头的一个或多个字符组成。名称不区分字母的大小写，比如多个字符组成。名称不区分字母的大小写，比如ABC、abc、Abc都是同一个名称。在都是同一个名称。在Excel中，还可以用中，还可以用汉字汉字作为名称。作为名称。n可以为同一单元格或单元格区域定义可以为同一单元格或单元格区域定义多个多个不同的不同的名称名称。n

13、名称一旦定义就可以在同一工作薄的不同名称一旦定义就可以在同一工作薄的不同工作表工作表之间共用之间共用。华信教育资源网华信教育资源网 5.3 名称名称2、名称的意义、名称的意义n在在Excel中，同一个工作簿中的名称是共享的，中，同一个工作簿中的名称是共享的，在一个工作表中定义的名称，可以被另一个工在一个工作表中定义的名称，可以被另一个工作表的公式引用，这使公式具有更强大的功能，作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的它能够利用单元格的名称名称在不同的工作表中在不同的工作表中查查找找到正确的到正确的单元格单元格或单元格或单元格区域区域。n名称给单元格的引用带来了许多方便，因为

14、它名称给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，使人们不必记住单元格在工作表中的引用位置，用用名称名称就能找到它。就能找到它。华信教育资源网华信教育资源网 5.3.2 名称的定义名称的定义n在在Excel中，可以为一个独立的单元格、连续中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单的单元格区域或许多不连续的单元格构成的单元格组合定义一个名称（或多个名称）元格组合定义一个名称（或多个名称） n定义名称有多种方法，如定义、粘贴、指定或定义名称有多种方法，如定义、粘贴、指定或标志等标志等 华信教育资源网华信教育资源网 5.3.2 名称的定

15、义名称的定义(1)、使用命令定义单元格或单元格区域的名称、使用命令定义单元格或单元格区域的名称【例例5.7】在下图中，在下图中，B2单元格被命名为单元格被命名为“存款利率存款利率”，在，在D5中输入的中输入的公式是公式是“=C5*存款利率存款利率”，其中的，其中的“存款利率存款利率”就是就是B2单元格的名单元格的名称。称。 B2单元格的名称定义方法如下：单元格的名称定义方法如下：华信教育资源网华信教育资源网 5.3.2 名称的定义名称的定义n（1）单击）单击“公式公式”|“定义的名称定义的名称”组中组中|“定义名称定义名称”命令按钮命令按钮.n（2）在弹出的）在弹出的“新建名称新建名称”对话框

16、的对话框的“引用引用位置位置” 输入要定义名称的单元格输入要定义名称的单元格引用位置引用位置。n（3）在）在“在当前工作簿中的名称在当前工作簿中的名称” 中输入中输入名称名称 华信教育资源网华信教育资源网 5.3.2 名称的定义名称的定义(2)、使用指定的方式定义名称、使用指定的方式定义名称n“指定指定”的方式一次可定义的方式一次可定义多个名称多个名称：可以将：可以将表格的首行或首列指定为相应的列或行的名称。表格的首行或首列指定为相应的列或行的名称。n方法方法n（1）选中选中要指定名称的单元格要指定名称的单元格区域区域。n（2）单击）单击“公式公式”|“定义的名称定义的名称”组中组中|“根据所

17、根据所选内容创建选内容创建”命令按钮。命令按钮。n（3）在弹出的）在弹出的“以选定区域创建名称以选定区域创建名称”对话框中，对话框中，选中选中“首行首行”、“最左列最左列”的复选框。的复选框。这样就把该表的首行、首列指定成了相应的列、行的这样就把该表的首行、首列指定成了相应的列、行的名称名称华信教育资源网华信教育资源网 5.3.2 名称的定义名称的定义n指定首行、最左列为名称的案例指定首行、最左列为名称的案例n【例例5.8】 图图5.10是某建筑工地的工人奖金统计表，是某建筑工地的工人奖金统计表，一共有几百个工人，其中的某些工人会随时查询自己一共有几百个工人，其中的某些工人会随时查询自己的奖金

18、，这就需要在的奖金，这就需要在A列的几百行数据中进行查找。名列的几百行数据中进行查找。名字能够很好地解决这类问题，方法如下。字能够很好地解决这类问题，方法如下。 华信教育资源网华信教育资源网 5.3.2 名称的定义名称的定义(3)用名称栏定义名称用名称栏定义名称n工作表全选按钮（工作表列标工作表全选按钮（工作表列标A左边的按钮）上面的编辑框称为左边的按钮）上面的编辑框称为名称栏名称栏，它随时显示当前单元格的名称或引用。它可以用来定义，它随时显示当前单元格的名称或引用。它可以用来定义名称。名称。n【例例5.9】 在图在图5.11中，中，C4:C8区域中输入的是数组区域中输入的是数组公式：公式：“

19、=数量数量*单价单价”，其定义方法请见图中的标注。，其定义方法请见图中的标注。名称栏名称栏华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用n定义一个工作薄级的名字后，该名字就定义一个工作薄级的名字后，该名字就能被本工作簿中的各个工作表直接引用。能被本工作簿中的各个工作表直接引用。【例例5.10】 图图5.12是某蔬菜超市的销售是某蔬菜超市的销售记录。该超市把蔬菜单价存放在一张工记录。该超市把蔬菜单价存放在一张工作表中，如图作表中，如图5.12（a）所示，每种蔬菜）所示，每种蔬菜的销售记录则保存在不同的工作表中，的销售记录则保存在不同的工作表中，如图如图5.12（b）所示。现以小白

20、菜的销）所示。现以小白菜的销售金额计算为例，说明名字的跨表引用售金额计算为例，说明名字的跨表引用问题。问题。华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用1）通过名称在不同的工作表之间）通过名称在不同的工作表之间传递数据传递数据 通过名称查找蔬菜单价通过名称查找蔬菜单价指定指定A2:B12最左边为名称最左边为名称华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用2)名称结合名称结合Indirect函数的应用函数的应用n指定指定A2:B12的的最左列为最左列为名称；名称；n用用名称查找名称查找蔬菜单价，见蔬菜单价，见D列；列；n若不用名称，单价要从若不用名称，单价要从

21、C列的几百个数据中去查列的几百个数据中去查找！找！ 这种方法虽然比直接从工作表的单元格中查找数这种方法虽然比直接从工作表的单元格中查找数据方便了不少，但据方便了不少，但输入量仍然较大输入量仍然较大。用它处理。用它处理成千上万行的数据查询仍然成千上万行的数据查询仍然低效低效率。最佳方法率。最佳方法是用是用名称和名称和Indirect函数函数相结合，非常方便，相结合，非常方便，高效。高效。华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用n某蔬菜商在一个工作表中保存蔬菜的单价，在另一某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各工作表中保存销售

22、记录，现要查找销售记录表中各蔬菜的单价。蔬菜的单价。指定指定A2:B11区域区域的的“最左列最左列”为名为名称称在D2中输入公式：=INDIRECT(B2)向下复制该公式！华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用n2）名称与）名称与Indirect函数结合应用函数结合应用nIndirect函数与函数与名称名称相相结结合，可使数据查找更方便。合，可使数据查找更方便。Indirect函数的用法是：函数的用法是： Indirect(x) 其中的其中的X可以是单元格引用或名称。可以是单元格引用或名称。n若若X为单元格引用，它必须用为单元格引用，它必须用“”“”引起来，如：引起来，

23、如：=INDIRECT(“A1”)，将返回，将返回A1单元格中的内容单元格中的内容n若若X为单元格名称，它将返回该名称所对应的单元格的为单元格名称，它将返回该名称所对应的单元格的内容。内容。华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用n3)应用行列交叉点查找数据应用行列交叉点查找数据n所谓行列交叉点指的是由所谓行列交叉点指的是由行的名称行的名称 列的名称列的名称确定的确定的单元格。单元格。 n把一个工作表的首行、首列指定为名称之后，可以使把一个工作表的首行、首列指定为名称之后，可以使用名称引用行、列交叉点对应单元格中的数据。用名称引用行、列交叉点对应单元格中的数据。n案例案例

24、n【例例5.11】 图图5.13是一个学生成绩表，用指定的是一个学生成绩表，用指定的方式把它的首行、首列指定成了名字。方式把它的首行、首列指定成了名字。华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用1、建立如下表格并选中、建立如下表格并选中A1:F8区域区域2、选择、选择“公式公式”|“定义的名定义的名称称”|“根据所选内容创建根据所选内容创建”命令命令4、指定名字对话框中、指定名字对话框中和和项为名称项为名称现在做试验现在做试验1、选中、选中K2:K8,输入输入“=化学化学”,按按“Ctrl+Shift+Enter”2、选中、选中A10:F10,输入输入“=李四李四”,按按“

25、Ctrl+Shift+Enter”3、在、在L2单元格中输入公式单元格中输入公式“=李达李达 化学化学”华信教育资源网华信教育资源网 5.3.3 名称的应用名称的应用4)引用其他工作簿中的数据引用其他工作簿中的数据 n当一个公司的数据来源于不同的分公司或当一个公司的数据来源于不同的分公司或部门时，往往需要对不同分公司或部门的部门时，往往需要对不同分公司或部门的数据进行汇总，这就要涉及多个工作薄的数据进行汇总，这就要涉及多个工作薄的数据操作。名称可以很好地解决这个问题。数据操作。名称可以很好地解决这个问题。n【例例5.12】 采虹电视经销全国各省，每季度采虹电视经销全国各省，每季度都要统计各种型

26、号电视的销售数量。各省的销都要统计各种型号电视的销售数量。各省的销售代理每季度向采虹厂提供一个含有各种型号售代理每季度向采虹厂提供一个含有各种型号电视的汇总统计表。现在对各省的报表数据进电视的汇总统计表。现在对各省的报表数据进行汇总统计。行汇总统计。 华信教育资源网华信教育资源网 指定指定A3:B8区区域域”最左最左列列”为名称为名称指定指定A3:B8区区域域”最左最左列列”为名称为名称B3单元格中输入公式“=四川四川.xls!_21英寸英寸+重庆重庆.xls!_21英寸英寸”；在B4单元格中输入公式“=四川四川.xls!_25英寸英寸+重庆重庆.xls!_25英寸英寸”，其余以此类推 华信教

27、育资源网华信教育资源网 5.3.4 定义定义常量名称常量名称1)常量名称的意义常量名称的意义n用名字代表常量，如圆周率用名字代表常量，如圆周率3.14159，自然对，自然对数的底数数的底数e等，然后在整个工作簿中引用这些等，然后在整个工作簿中引用这些常量名，会给数据处理带来许多方便，这使用常量名，会给数据处理带来许多方便，这使用户在运用这些数据时不必关心它所在的工作表户在运用这些数据时不必关心它所在的工作表或单元格，直接用其名字就可以了。或单元格，直接用其名字就可以了。n常量名称的另一个好处常量名称的另一个好处:当常量值变化时当常量值变化时,只需只需要修改常量定义就行了要修改常量定义就行了,不

28、必须在各具工作表的不必须在各具工作表的公式中寻找常量值并进行修改公式中寻找常量值并进行修改.华信教育资源网华信教育资源网 5.3.4 定义定义常量名称常量名称n常量名称的定义常量名称的定义【例例5.13】 图图5.15是用名字常量是用名字常量pi=3.14计算圆周长和圆面积的简单例子。计算圆周长和圆面积的简单例子。在名称中输入名称指定名称的有效范畴输入名称对应的公式或常数直接应用了常量名直接应用了常量名称称Pi计算圆的周计算圆的周长和面积长和面积华信教育资源网华信教育资源网 5.3.5 名称名称管理器管理器选择选择“公式公式”选项卡，单击选项卡，单击其中其中“定义的名称定义的名称”组中组中的的

29、“名称管理器名称管理器”按钮按钮,显示出名称管理器对话框显示出名称管理器对话框 通过通过“名称管理器名称管理器”可可以新建以新建编辑编辑删除名称删除名称,可以可以重指定名称的含义或对应的单重指定名称的含义或对应的单元格引用位置元格引用位置.华信教育资源网华信教育资源网 5.4 函数函数简介简介1、函数的概念、函数的概念n函数是函数是能够完成特定功能的能够完成特定功能的程序程序。在。在Excel中，中，它是系统它是系统预定义预定义的一些的一些公式公式，它们使用一些称，它们使用一些称为为参数参数的特定数值按特定的的特定数值按特定的顺序顺序或结构进行计或结构进行计算，然后把计算的算，然后把计算的结果

30、结果存放在某个单元格中。存放在某个单元格中。 n在大多数情况下，函数的计算结果是数值。当在大多数情况下，函数的计算结果是数值。当然，它也可以返回文本、引用、逻辑值、数组然，它也可以返回文本、引用、逻辑值、数组或工作表的信息或工作表的信息 华信教育资源网华信教育资源网 5.4 函数函数简介简介n2、Excel函数分类函数分类分 类功 能 简 介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进

31、行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计算等外部函数进行外部函数调用及数据库的链接查询等功能自定义函数用户用vba编写，用于完成特定功能的函数华信教育资源网华信教育资源网 5.4.1 函数函数调用调用1、函数的语法、函数的语法 n函数名函数名(参数参数1, 参数参数2, 参数参数3,) n在公式中调用函数在公式中调用函数n =IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套函嵌套函数数Excel 2007允许嵌套多达允许嵌套多达64层层Excel 2007可达可达255个参数个参数华信教育

32、资源网华信教育资源网 5.4.2 函数函数输入输入n在公式中直接输入函数调用在公式中直接输入函数调用n用函数调用向导输入用函数调用向导输入5、Excel的帮助系统的帮助系统华信教育资源网华信教育资源网 5.5 使用使用Excel帮助理解函数帮助理解函数nExcel的帮助系统具有极其强大的功能，利用帮的帮助系统具有极其强大的功能，利用帮助系统能够解决使用助系统能够解决使用Excel过程中所遇到的各种过程中所遇到的各种问题。这些问题包括问题。这些问题包括Excel的新技术、疑难、专的新技术、疑难、专用名字解决、函数说明、函数应用实例等。用名字解决、函数说明、函数应用实例等。nExcel的帮助系统比

33、许多参考书内容更全面，讲的帮助系统比许多参考书内容更全面，讲述更清楚，一个述更清楚，一个Excel的真正用户应该会运用帮的真正用户应该会运用帮助系统，从中获取有用的资料。进入助系统，从中获取有用的资料。进入Excel帮助帮助系统的常用方法有：系统的常用方法有：n按按F1键。键。n单击功能选项卡标题栏最右边的按钮单击功能选项卡标题栏最右边的按钮 华信教育资源网华信教育资源网 5.6 逻辑函数逻辑函数n5.6.1 Excel的比较运算符的比较运算符n比较运算又称关系运算，就是人们常说的比较式。比较运算又称关系运算，就是人们常说的比较式。比较运算只有两种不同的结果，要么比较运算只有两种不同的结果，要

34、么“正确正确”，要，要么么“错误错误”，不可能有第三种结果，不可能有第三种结果 比较运算符比较运算符含含 义义示示 例例=等于等于a1=3大于大于a1b1小于小于a1=大于等于大于等于a1=60比较运算符比较运算符含含 义义示示 例例=小于等于小于等于a1=”dd”不等于不等于a10华信教育资源网华信教育资源网 5.6.2 AND、NOT、OR、TRUE、FALSE函数函数nAND、NOT、OR、TRUE、FALSE函数函数n用法用法nAND(x1,x2, ,x30)nOR(x1, x2, ,x30)nNOT(logical)n案例案例nAND(TRUE,TRUE)=TRUE，nOR(TRUE

35、,TRUE)=TRUEnAND(TRUE,FALSE,TRUE, TRUE)=FALSE，OR(TRUE,FALSE,TRUE,TRUE)=TRUE。n如果如果B1，B2，B3单元格中的值为单元格中的值为 TRUE，FALSE，TRUE，则则AND(B1:B3)=FALSE，但，但OR(B1:B3)=TRUE。nAND(2,2+3=5)=TRUE华信教育资源网华信教育资源网 5.6.3 条件条件函数函数IFn用法用法nIF(条件条件, 表达式表达式1, 表达式表达式2)。n功能功能n当条件成立时，计算出表达式当条件成立时，计算出表达式1的值；当条件不成的值；当条件不成立时，计算出表达式立时，计

36、算出表达式2的值的值n事例事例nIf(A160,“及格了及格了”,“不及格不及格”)，若，若A1单元格的单元格的值是值是76，则该函数的结果是：及格了；若，则该函数的结果是：及格了；若A1单元单元格的值是格的值是50，则该函数的结果是，则该函数的结果是:不及格。不及格。nIF函数的嵌套调用函数的嵌套调用华信教育资源网华信教育资源网 5.6 逻辑函数逻辑函数n【例例5.15】某单位有某单位有1 000多位党员，多位党员，要收党费要收党费。党费根据工资的高低，按党费根据工资的高低，按不同的费率收取，收费的不同的费率收取，收费的费税如右表所示。费税如右表所示。n类似于这样的工作，使用类似于这样的工作

37、，使用IF函数进行数据的匹配与函数进行数据的匹配与查找是非常方便的。查找是非常方便的。n本例解决方法如下。本例解决方法如下。工工 资资党费费率党费费率2 000以上以上3%1 5002 0002.5%1 2001 5002%1 0001 2001.5%8001 0001%800以下以下0华信教育资源网华信教育资源网 5.6 逻辑函数逻辑函数nF3中公式意思为：当中公式意思为：当E3大于大于2 000时，公式的值为时，公式的值为3%；当当E3小于等于小于等于2 000，但大于，但大于1 500时，公式的值为时，公式的值为2.5%；其余的以此类推；其余的以此类推 华信教育资源网华信教育资源网 5.

38、6 逻辑函数逻辑函数n【例例5.16】某班考查成绩如下图某班考查成绩如下图A1:E10所示，将所示，将D列的列的地理成绩转换成等级制。地理成绩转换成等级制。华信教育资源网华信教育资源网 5.7 统计统计函数函数5.7.1、汇总求和函数、汇总求和函数 n自动求和按钮自动求和按钮1、选中F62、单击“公式公式”选项卡中“自动求和”按钮，就能求出F3、F4、F5之和。3、同方法求其它汇总和。华信教育资源网华信教育资源网 5.7.1、汇总求和函数、汇总求和函数2、SUM和和SumQ函数函数n用法用法SUM(x1, x2,x255) SUMQ(x1, x2,x255) x1，x2，x30是需要求和的参数

39、是需要求和的参数 ，可以是数据或单元格区域，可以是数据或单元格区域n功能功能nsum计算各参数的数值之和计算各参数的数值之和 ;sumq计算各数的平方之后计算各数的平方之后n说明：说明： 参数表中的数字、逻辑值及数字的文本表达式将被计算，文本值参数表中的数字、逻辑值及数字的文本表达式将被计算，文本值被转换成数字，而逻辑值被转换成数字，而逻辑值“true”被转换成数字被转换成数字1。 例如，例如，SUM(3,2)=5，SUM(9,20,true)=30。华信教育资源网华信教育资源网 5.7.1、汇总求和函数、汇总求和函数n 如果参数为数组或引用，那么只有其中的数字被计如果参数为数组或引用，那么只

40、有其中的数字被计算。数组或引用中的空白单元格、逻辑值、文本或错算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。误值将被忽略。n例如，设例如，设A1的值为的值为“9”，A2为为true，则公式，则公式SUM(A1,A2,20)的计算结果为的计算结果为20，而不是，而不是30。因。因为本公式中包括两个引用为本公式中包括两个引用A1、A2，而，而A1的值为文的值为文本，本，A2的值为逻辑值，它们在计算时被忽略，最终的值为逻辑值，它们在计算时被忽略，最终就只有一个数值就只有一个数值20参与运算。参与运算。n 参数最多可达参数最多可达255个，不同类型的参数可以同时出个，不同类型的参数可以同

41、时出现。现。n例如，例如，A2:E2 包含包含 5、15、30、40、50、a3的值的值为为10，则，则SUM(A2:C2,A3) =60，SUM(B2:E2,15) =150，SUM(A2:D2,1,2,3,4,A3,10)=110。华信教育资源网华信教育资源网 5.7.1 汇总求和函数汇总求和函数1、条件求和函数、条件求和函数SUMIFn用法用法SUMIF(range,criteria,sum_range) n其中，其中，range是用于条件判断的单元格区域，是用于条件判断的单元格区域，criteria条件，条件，其形式可以为数字、表达式或文本；其形式可以为数字、表达式或文本；nsum_r

42、ange是需求和的实际单元格。只有当是需求和的实际单元格。只有当range中的相应单元格满足条件时，才对中的相应单元格满足条件时，才对sum_range中的单元格求和。若省略中的单元格求和。若省略sum_range，则直接对则直接对range中的单元格求和。中的单元格求和。 n功能功能 n对对range单元格区域中的数据进行单元格区域中的数据进行Criteria条件检查，然后对条件检查，然后对满足条件行的满足条件行的sum_range同行进行求和。同行进行求和。华信教育资源网华信教育资源网 5.7.1 汇总求和函数汇总求和函数2、条件求和函数、条件求和函数SUMIFSn用法用法SUMIFS(s

43、um_range, range1,criteria1, range2,criteria2) n其中其中range1, range2, 是计算关联条件的是计算关联条件的 1 至至 127 个区个区域；域；Criteria1, criteria2, 是数字、表达式、单元格引用是数字、表达式、单元格引用或文本形式的或文本形式的 1 至至 127 个条件，用于定义要对哪些单元格求个条件，用于定义要对哪些单元格求和。这些区域与条件是对应的，即和。这些区域与条件是对应的，即Criteria1是用于是用于range1区区域的条件，域的条件，criteria2是用于是用于range2的条件，以此类推。的条件，

44、以此类推。Sum_range是求和区域。是求和区域。n功能功能 nSUMIFS函数对某一区域内满足多重条件的单元格求和。函数对某一区域内满足多重条件的单元格求和。华信教育资源网华信教育资源网 5.7.1 汇总求和函数汇总求和函数【例例5.18】 某家电商场的销售记录如图某家电商场的销售记录如图5.22的的A1:G13所示。现要统计出各种电所示。现要统计出各种电器的销售总数量和销售总金额，并将它统器的销售总数量和销售总金额，并将它统计在计在I1:K6区域的对应单元格中；统计每区域的对应单元格中；统计每位职工销售各种产品的总数量，并将它统位职工销售各种产品的总数量，并将它统计在计在I8:L13区域

45、中。区域中。华信教育资源网华信教育资源网 （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:$D$13,$I10,$A$3:$A$13,J$9)(2)将此公式向下角复制到将此公式向下角复制到L13，华信教育资源网华信教育资源网 5

46、.7.1 汇总求和函数汇总求和函数4、乘积和与平方和、乘积和与平方和nSUMPRODUCT函数可在给定的几组数组中，将数组间对应的元素函数可在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。用法如下：相乘，并返回乘积之和。用法如下：SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 为为 2 到到 255 个数组，其个数组，其相应元素需要进行相乘并求和。相应元素需要进行相乘并求和。n此外，此外，nSUMXMY2(array_x,array_y)nSUMX2MY2(array_x,array_y)nSUMX2PY2(

47、array_x,array_y) SUMXMY2计算两数组中对应数值之差的平方和计算两数组中对应数值之差的平方和 ，SUMX2PY2计算两数组中对应数值的平方和之和计算两数组中对应数值的平方和之和 ，SUMX2MY2计算两数组中对应数值的平方差之和。计算两数组中对应数值的平方差之和。华信教育资源网华信教育资源网 5.7.2 平均值平均值函数函数1、平均值函数、平均值函数AVERAGE，AVERAGEA n用法用法nAVERAGE(n1，n2，n255)nAVERAGEA(n1，n2，n255)n其中，其中，n1，n2，n255是要计算平均值的参数，该是要计算平均值的参数，该函数最多允许有函数最

48、多允许有255个参数个参数。参数可以是数字，或者是涉。参数可以是数字，或者是涉及数字的及数字的名称名称、数组数组或或引用引用 n功能功能n两函数都是求参数的两函数都是求参数的平均值平均值。AverAge不对不对文本文本和和逻辑值逻辑值类数据进行计算；类数据进行计算；nAverageA要对文本和逻辑函数进行平均值计算：要对文本和逻辑函数进行平均值计算：文本文本被视为被视为0，逻辑值，逻辑值true被视为被视为1，false被视为被视为0，空文本（空文本（）也作为）也作为0计算计算 华信教育资源网华信教育资源网 5.7.2 平均值平均值函数函数n计数函数案例计数函数案例n下图说明下图说明AVERA

49、GE函数与函数与AVERAGEA函数的区别函数的区别 华信教育资源网华信教育资源网 5.7.2 平均值平均值函数函数2条件平均值函数条件平均值函数nExcel2007提供了两个根据条件计算平均值的函数提供了两个根据条件计算平均值的函数AVERAGEIF和和AVERAGEIFS。AVERAGEIF(average_range, range1,criteria1)AVERAGEIFS(average_range,range1,criteria1,range2,criteria2)n其中其中average_range是要计算平均值的一个或多个单元格，是要计算平均值的一个或多个单元格，其中包括数字或包

50、含数字的名称、数组或引用；其中包括数字或包含数字的名称、数组或引用；range1, range2, 是计算关联条件的是计算关联条件的 1 至至 127 个区域；个区域；Criteria1是用于是用于range1的条件的条件, criteria2是用于是用于range2的条件的条件, 它可以是数字、表达式、单元格引用或文本形式它可以是数字、表达式、单元格引用或文本形式的的 1 至至 127 个条件，用于定义要对哪些单元格求平均值。个条件，用于定义要对哪些单元格求平均值。华信教育资源网华信教育资源网 5.7.2 平均值平均值函数函数【例例5.20】某高三年级的期末考试成绩表如图某高三年级的期末考试

51、成绩表如图5.21的的B1:F10区域所示，统计每个班男女生的各科目平均成绩。区域所示，统计每个班男女生的各科目平均成绩。在在J4中输入如下公式中输入如下公式,并将它向下向右复制到其它单元格并将它向下向右复制到其它单元格=AVERAGEIFS(D$4:D$10,$B$4:$B$10,$H4,$C$4:$C$10,$I4)华信教育资源网华信教育资源网 5.7.3 统计个数统计个数的函数的函数1、计数函数、计数函数COUNT、COUNTA、COUNTBLANK、COUNTIF n用法用法nCOUNT(x1, x2, ,x30)nCOUNTA(x1, x2, ,x30)nCOUNTBLANK (ra

52、nge)n功能功能nCount 统计数字的个数统计数字的个数nCountA统计数字和文本的个数统计数字和文本的个数nCountBlank统计空白单元格的个数统计空白单元格的个数华信教育资源网华信教育资源网 5.7.3 统计个数统计个数的函数的函数【例例5.21】 用用count和和countA函数统计单元格个数。函数统计单元格个数。图图5.22是用这两个函数统计同一区域的不同结果。是用这两个函数统计同一区域的不同结果。华信教育资源网华信教育资源网 5.7.3 统计个数统计个数的函数的函数2、COUNTIF函数函数n用法用法nCOUNTIF(range, criteria)n功能功能 n统计出统

53、计出range中满足条件中满足条件criteria的数据个数。的数据个数。3、CountifsExcel 2007还提供了一个多条件统计函数还提供了一个多条件统计函数COUNTIFS，它可，它可以一次对多个不同区域进行不同条件的计数，其用法如下：以一次对多个不同区域进行不同条件的计数，其用法如下：COUNTIF(range1， criteria1，range2， criteria2，range127， criteria127)华信教育资源网华信教育资源网 5.7.3 统计个数统计个数的函数的函数n【例例5.22】有学生成绩表如图有学生成绩表如图5.23所示。统计其中总分所示。统计其中总分220

54、分以下的人数；统计总分分以下的人数；统计总分200分以上，物理分以上，物理80分以分以下，地理下，地理70分以下，化学分以下，化学65分以下的人数；统计姓王的分以下的人数；统计姓王的同学人数。同学人数。华信教育资源网华信教育资源网 5.7.3 统计个数统计个数的函数的函数3计算大小、百分比和名次的函数计算大小、百分比和名次的函数LARGE(array,k)SMALL(array,k)nLARGE函数返回数据集中第函数返回数据集中第k个最大值，个最大值，SMALL返回数据集中第返回数据集中第 k 个最小值，这两个最小值，这两个函数可以根据相对标准来选择数值，返个函数可以根据相对标准来选择数值，返

55、回数据集中特定位置上的数值。例如，可回数据集中特定位置上的数值。例如，可以使用函数以使用函数LARGE得到第一名、第二名或得到第一名、第二名或第三名的得分。第三名的得分。华信教育资源网华信教育资源网 5.7.3 统计个数统计个数的函数的函数n中间值函数中间值函数MEDIAN和众数函数和众数函数MODEMEDIAN(number1,number2,.)MODE(number1,number2,.)nMEDIAN函数计算出给定数据的中值，函数计算出给定数据的中值，MODE函数返回一组数中出现次数最多的数函数返回一组数中出现次数最多的数（众数）。（众数）。华信教育资源网华信教育资源网 5.7.3 统

56、计个数统计个数的函数的函数n排名函数排名函数RANKRANK(number,ref,order)。nRANK函数返回一个数字在一组数中的排位，即位函数返回一个数字在一组数中的排位，即位次。次。nNumber为需要找到排位的数字，为需要找到排位的数字，Ref可以是一个可以是一个数组或单元格区域，数组或单元格区域，Ref 中的非数值型参数将被忽中的非数值型参数将被忽略。略。Order用于指明排位的方式，如果为用于指明排位的方式，如果为 0或省略，或省略，则则Rank 对数字的排位是基于对数字的排位是基于 ref 为按照降序排列为按照降序排列的列表；如果的列表；如果 order 不为零，则不为零，则

57、Rank对数字的排对数字的排位是基于位是基于 ref 为按照升序排列的列表。为按照升序排列的列表。 华信教育资源网华信教育资源网 【例例5.23】有学生成绩表如图有学生成绩表如图5.24中中A1:F10区域所示。计区域所示。计算各学生成绩的名称、百分比排位、第算各学生成绩的名称、百分比排位、第3名和倒数第名和倒数第3名名总分、中间成绩、出现次数最多的分数及在总分、中间成绩、出现次数最多的分数及在38%总分位总分位置的分数大致是多少等数据。置的分数大致是多少等数据。在G3中输入公式：=RANK(F3,$F$3:$F$10) 向下复制此公式可计算名次在H3中输入公式：=PERCENTRANK($F

58、$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.7 统计函数统计函数n【例例5.24】某班期末成绩表如图所示。现要统计每位同某班期末成绩表如图所示。现要统计每位同学的总分，各科目的应考人数、缺考人数及各科平均成绩学的总分，各科目的应考人数、缺考人数及各科平均成绩 华信教育资源网华信教育资源网 5.8 数学数学和三角函数和三角函数n1、概述、概述nExcel提供了许多数学和三角函数

59、，它们能够提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。返回到输入公式的单元格中。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.8

60、数学数学和三角函数和三角函数3、数学函数应用案例、数学函数应用案例【例例5.25】用随机函数产生大量的实验数据，计算排用随机函数产生大量的实验数据，计算排名。名。问题：有一张工资工作表，结构如图所示。现以问题：有一张工资工作表，结构如图所示。现以此表为例说明随机函数和数组的结合使用。此表为例说明随机函数和数组的结合使用。 华信教育资源网华信教育资源网 5.8 数学数学和三角函数和三角函数n产生日期产生日期（1）在）在B2单元格中输入参加工作的最早时间，即在单元格中输入参加工作的最早时间，即在B2单元格中输入单元格中输入“1970/1/1”。（2）选中要产生日期的单元格区域，如）选中要产生日期的