1、EXCEL补充知识补充知识EXCEL补充知识补充知识认识认识n函数好比函数好比EXCEL的的武林秘笈武林秘笈，如果要成为，如果要成为EXCEL高高手手，谁都必须学习它。函数也是，谁都必须学习它。函数也是EXCEL与与Word之类之类软件中的表格的软件中的表格的主要区别主要区别，它使，它使EXCEL具有强大的具有强大的运算能力。运算能力。nEXCEL提供了涉及各个不同领域的函数接近提供了涉及各个不同领域的函数接近400个个。运用它们解决工作中的实际问题，能够极大提高工作运用它们解决工作中的实际问题，能够极大提高工作效率效率，使枯燥的工作变得轻松、简单。，使枯燥的工作变得轻松、简单。n但学习是一个
2、但学习是一个枯燥而艰辛枯燥而艰辛的过程！当然嘛，谁让你想的过程！当然嘛，谁让你想成为成为高手高手呢？呢？EXCEL补充知识补充知识本章本章学习目标学习目标1 1、掌握、掌握数组公式数组公式的用法的用法2 2、掌握、掌握名字名字的用法的用法3 3、掌握常用、掌握常用工作表函数工作表函数的用法的用法4 4、掌握常见、掌握常见统计函数统计函数的用法的用法5 5、掌握、掌握条件函数条件函数的用法，特别是：的用法，特别是：IfIfsumifcountifsumifcountif 等等6 6、掌握解、掌握解查找引用类函数查找引用类函数的用法的用法7 7、掌握常见、掌握常见日期、文本日期、文本类函数类函数E
3、XCEL补充知识补充知识3.1 数组公式数组公式及其应用及其应用1、概述、概述n普通公式只执行一个简单计算，并且返回普通公式只执行一个简单计算，并且返回一个一个运算结果运算结果。数组公式能够同时对一组或两组以。数组公式能够同时对一组或两组以上的数据进行计算，计算的结果可能是上的数据进行计算，计算的结果可能是一个一个，也可能是也可能是多个多个。n在数组公式中使用的数据称为在数组公式中使用的数据称为数组参数数组参数，数组，数组参数可以是一个参数可以是一个数据区域数据区域，也可以是数组，也可以是数组常量常量。EXCEL补充知识补充知识3.1 数组公式数组公式及其应用及其应用2、数组公式的建立方法、数
4、组公式的建立方法n（1）选中需要保存数组公式结果的单元格或）选中需要保存数组公式结果的单元格或单元格区域。单元格区域。n（2）输入公式的内容。）输入公式的内容。n（3）按）按Ctrl+Shift+Enter键。键。EXCEL补充知识补充知识3.1 数组公式数组公式及其应用及其应用3、用数组公式计算两个数据区域的乘积、用数组公式计算两个数据区域的乘积【例例3.1】某茶叶店经销多种茶叶，已知各种茶某茶叶店经销多种茶叶，已知各种茶叶的单价以及叶的单价以及12月份各种茶叶的销量如图所示，月份各种茶叶的销量如图所示，计算各种茶叶的销售额。计算各种茶叶的销售额。EXCEL补充知识补充知识3.1 数组公式数
5、组公式及其应用及其应用4、数组扩展、数组扩展 n在公式中用数组作为参数时，所有的数组必须在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不是同维的。如果数组参数或数组区域的维数不匹配，匹配，Excel会自动扩展该参数会自动扩展该参数。n比如，比如，LG空调售价为空调售价为7654元，重庆各商场的元，重庆各商场的销售数据如下图的销售数据如下图的B、C两列所示。现要计算两列所示。现要计算各商场的销售总额。各商场的销售总额。EXCEL补充知识补充知识3.1 数组公式数组公式及其应用及其应用1、选中、选中F8:F14；2、输入公式：、输入公式：=C5:C8*C143、按、
6、按Ctrl+Shift+Entern用数组公式计算销售总额用数组公式计算销售总额EXCEL补充知识补充知识3.1 数组公式数组公式及其应用及其应用5、二维数组、二维数组 n涉及许多行涉及许多行(或列或列)数据处理的数组公式，就是数据处理的数组公式，就是所谓的二维数组。所谓的二维数组。n合理地运用二维数组的运算功能，会提高数据合理地运用二维数组的运算功能，会提高数据处理的能力，有时在不同工作表之间进行数据处理的能力，有时在不同工作表之间进行数据汇总时会特别有效汇总时会特别有效。EXCEL补充知识补充知识3.1 数组公式数组公式及其应用及其应用n案例案例n某食品店将每月的食品销售资料统计在一个工作
7、表中，某食品店将每月的食品销售资料统计在一个工作表中，16月的销售数据分别记录在月的销售数据分别记录在Book1工作簿的工作簿的Sheet1Sheet6中，现在要统计每种商品前中，现在要统计每种商品前6个月的销售总量和个月的销售总量和销售金额，并将结果存放在销售金额，并将结果存放在sheet7。见下图：。见下图：EXCEL补充知识补充知识3.2 公式公式的循环引用的循环引用1、循环引用的概念、循环引用的概念n如果公式引用了自己所在的单元格，不论是如果公式引用了自己所在的单元格，不论是直直接接的还是的还是间接间接的，都称为的，都称为循环引用循环引用。n在一般情况下，很少使用循环引用来处理问题。在
8、一般情况下，很少使用循环引用来处理问题。但对于但对于迭代迭代求解一类问题，如求数的阶乘、数求解一类问题，如求数的阶乘、数列求和等问题，可以用列求和等问题，可以用循环引用循环引用求解。求解。n若循环引用的若循环引用的条件条件或或迭代次数迭代次数设置不当，设置不当，EXCEL会报告一个错误信息。会报告一个错误信息。EXCEL补充知识补充知识3.2 公式公式的循环引用的循环引用n【例例3.5】下图是循环引用的一个事例。在下图是循环引用的一个事例。在A1单元格中输入的公式单元格中输入的公式“=A2+A3”；在；在A2单元格中输入数字单元格中输入数字2；在；在A3单元格中输入公式单元格中输入公式“=A1
9、*0.3”，见，见图图(a)。这样，。这样，A3单元格的值依赖于单元格的值依赖于A1，而，而A1单单元格的值又依赖于元格的值又依赖于A3，它们形成了间接的循环引用，在未进行迭代，它们形成了间接的循环引用，在未进行迭代求解情况下的结果如求解情况下的结果如图（图（b）所示。该循环引用是所示。该循环引用是可解可解的，进行迭代的，进行迭代求解最后的结果如求解最后的结果如图（图（c）所示。所示。EXCEL补充知识补充知识3.3 名字名字1、名字概述、名字概述 n可以给一个单元格或单元格区域取一个名字，这个名字可以给一个单元格或单元格区域取一个名字，这个名字可以出现在公式中，用来指代引用的单元格或单元格区
10、可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。域，这会使公式的意义更加明确。n名字其实是一个标识符，由字母或下划线开头的一个或名字其实是一个标识符，由字母或下划线开头的一个或多个字符组成。名字不区分字母的大小写，比如多个字符组成。名字不区分字母的大小写，比如ABC、abc、Abc都是同一个名字。在都是同一个名字。在Excel中，还可以用中，还可以用汉字汉字作为名字。作为名字。n可以为同一单元格或单元格区域定义可以为同一单元格或单元格区域定义多个多个不同的不同的名字名字。n名字一旦定义就可以在同一工作薄的不同名字一旦定义就可以在同一工作薄的不同工作表工作表之间共用
11、之间共用。EXCEL补充知识补充知识3.3 名字名字2、名字的意义、名字的意义n在在Excel中，同一个工作簿中的名字是共享的，中，同一个工作簿中的名字是共享的，在一个工作表中定义的名字，可以被另一个工在一个工作表中定义的名字，可以被另一个工作表的公式引用，这使公式具有更强大的功能，作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的它能够利用单元格的名字名字在不同的工作表中在不同的工作表中查查找找到正确的到正确的单元格单元格或单元格或单元格区域区域。n名字给单元格的引用带来了许多方便，因为它名字给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，使人们不必记
12、住单元格在工作表中的引用位置，用用名字名字就能找到它。就能找到它。EXCEL补充知识补充知识3.3 名字名字3、名字的定义、名字的定义 n在在Excel中，可以为一个独立的单元格、连续中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单的单元格区域或许多不连续的单元格构成的单元格组合定义一个名字（或多个名字）元格组合定义一个名字（或多个名字）n定义名字有多种方法，如定义、粘贴、指定或定义名字有多种方法，如定义、粘贴、指定或标志等标志等 EXCEL补充知识补充知识3.3 名字名字4、定义单元格或单元格区域的名字、定义单元格或单元格区域的名字【例例3.6】在下图中，在下图中，B
13、2单元格被命名为单元格被命名为“存款利率存款利率”，在，在D5中输入的中输入的公式是公式是“=C5*存款利率存款利率”，其中的，其中的“存款利率存款利率”就是就是B2单元格的名单元格的名字。字。B2单元格的名字定义方法如下：单元格的名字定义方法如下：EXCEL补充知识补充知识3.3 名字名字n（1）选择）选择“插入插入”|“名称名称”|“定义定义”菜单菜单命令命令.n（2）在弹出的）在弹出的“定义名字定义名字”对话框的对话框的“引用引用位置位置”输入要定义名字的单元格输入要定义名字的单元格引用位置引用位置。n（3）在）在“在当前工作簿中的名称在当前工作簿中的名称”中输入中输入名字名字 EXCE
14、L补充知识补充知识3.3 名字名字5、将工作表的首行（或首列）定义为名字、将工作表的首行（或首列）定义为名字n“指定指定”的方式一次可定义的方式一次可定义多个名字多个名字：可以将：可以将表格的首行或首列指定为相应的列或行的名字。表格的首行或首列指定为相应的列或行的名字。n方法方法n（1）选中选中要指定名字的单元格要指定名字的单元格区域区域。n（2）选择）选择“插入插入”|“名称名称”|“指定指定”菜单项。菜单项。n（3）在）在“指定名称指定名称”对话框中，选中对话框中，选中“首行首行”、“最左列最左列”的复选框。的复选框。这样就把该表的首行、首列指定成了相应的列、行的这样就把该表的首行、首列指
15、定成了相应的列、行的名字名字EXCEL补充知识补充知识3.3 名字名字n指定首行、最左列为名字的案例指定首行、最左列为名字的案例n例例某学生成绩表如下图所示。指定其某学生成绩表如下图所示。指定其首行、首行、最左列为最左列为名字。名字。指定首行、最指定首行、最左列为名字的左列为名字的好处是可以通好处是可以通过过行列交叉点行列交叉点查看单元格数查看单元格数据据EXCEL补充知识补充知识3.3 名字名字n用名字栏定义名字用名字栏定义名字n工作表全选按钮（工作表列标工作表全选按钮（工作表列标A左边的按钮）上面的编辑框称为左边的按钮）上面的编辑框称为名字栏名字栏，它随时显示当前单元格的名字或引用。它可以
16、用来定义，它随时显示当前单元格的名字或引用。它可以用来定义名字。名字。n例例用名字栏将下图用名字栏将下图B4:B8区域的名字定义为区域的名字定义为数量。数量。EXCEL补充知识补充知识3.3 名字名字6、名字的应用名字的应用n1）通过名字在不同的工作表之间）通过名字在不同的工作表之间传递数据传递数据 n实现方法见下页。实现方法见下页。通过名字查找蔬菜单价通过名字查找蔬菜单价指定指定A2:B12最左边为名字最左边为名字EXCEL补充知识补充知识3.3 名字名字n指定指定A2:B12的的最左列为最左列为名字；名字；n用用名字查找名字查找蔬菜单价，见蔬菜单价，见D列；列；n若不用名字，单价要从若不用
17、名字，单价要从C列的几百个数据中去查列的几百个数据中去查找！找！这种方法虽然比直接从工作表的单元格中查找数这种方法虽然比直接从工作表的单元格中查找数据方便了不少，但据方便了不少，但输入量仍然较大输入量仍然较大。用它处理。用它处理成千上万行的数据查询仍然成千上万行的数据查询仍然低效低效率。最佳方法率。最佳方法是用是用名字和名字和Indirect函数函数相结合，非常方便，相结合，非常方便，高效。高效。EXCEL补充知识补充知识3.3 名字名字n某蔬菜商在一个工作表中保存蔬菜的单价，在另一某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各工作表中保存销售记录，现
18、要查找销售记录表中各蔬菜的单价。蔬菜的单价。指定指定A2:B11区域区域的的“最左列最左列”为名为名字字在D2中输入公式：=INDIRECT(B2)向下复制该公式！EXCEL补充知识补充知识3.3 名字名字n2）名字与）名字与Indirect函数结合应用函数结合应用nIndirect函数与函数与名字名字相相结结合，可使数据查找更方便。合，可使数据查找更方便。Indirect函数的用法是：函数的用法是：Indirect(x)其中的其中的X可以是单元格引用或名字。可以是单元格引用或名字。n若若X为单元格引用，它必须用为单元格引用，它必须用“”“”引起来，如：引起来，如：=INDIRECT(“A1”
19、)，将返回，将返回A1单元格中的内容单元格中的内容n若若X为单元格名字，它将返回该名字所对应的单元格的为单元格名字，它将返回该名字所对应的单元格的内容。内容。EXCEL补充知识补充知识3.3 名字名字n3应用行列交叉点查找数据应用行列交叉点查找数据n所谓行列交叉点指的是由所谓行列交叉点指的是由行的名字行的名字 列的名字列的名字确定的确定的单元格。单元格。n把一个工作表的首行、首列指定为名字之后，可以使把一个工作表的首行、首列指定为名字之后，可以使用名字引用行、列交叉点对应单元格中的数据。用名字引用行、列交叉点对应单元格中的数据。n案例案例n某电信公司的电话用户档案，其中有成千上万个电某电信公司
20、的电话用户档案，其中有成千上万个电话用户的电话号码。如果知道某个用户的名字，那话用户的电话号码。如果知道某个用户的名字，那么要在这样的表中查找该用户的电话号码、电话类么要在这样的表中查找该用户的电话号码、电话类型，或申请日期等数据就显得很困难，因为表中的型，或申请日期等数据就显得很困难，因为表中的资料太多了。通过行、列交叉点的名字引用能很轻资料太多了。通过行、列交叉点的名字引用能很轻松地找到所需的数据。松地找到所需的数据。EXCEL补充知识补充知识3.3 名字名字1 1指定指定A2:E17A2:E17的的“首行，最首行，最左列左列”为名为名字。字。2 2、在任空白单、在任空白单元格中输入元格中
21、输入“客户名字客户名字库库 第二行第二行任一单元格任一单元格标题标题”，就，就能找到该客能找到该客户的对应数户的对应数据。据。EXCEL补充知识补充知识3.4 函数函数简介简介1、函数的概念、函数的概念n函数是函数是能够完成特定功能的能够完成特定功能的程序程序。在。在Excel中，中，它是系统它是系统预定义预定义的一些的一些公式公式，它们使用一些称，它们使用一些称为为参数参数的特定数值按特定的的特定数值按特定的顺序顺序或结构进行计或结构进行计算，然后把计算的算，然后把计算的结果结果存放在某个单元格中。存放在某个单元格中。n在大多数情况下，函数的计算结果是数值。当在大多数情况下，函数的计算结果是
22、数值。当然，它也可以返回文本、引用、逻辑值、数组然，它也可以返回文本、引用、逻辑值、数组或工作表的信息或工作表的信息 EXCEL补充知识补充知识3.4 函数函数简介简介n2、Excel函数分类函数分类分 类功 能 简 介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计
23、算等外部函数进行外部函数调用及数据库的链接查询等功能自定义函数用户用vba编写，用于完成特定功能的函数EXCEL补充知识补充知识3.4 函数函数简介简介3、函数调用、函数调用 n函数的语法函数的语法 n函数名函数名(参数参数1,参数参数2,参数参数3,)n在公式中调用函数在公式中调用函数n=IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套函嵌套函数数EXCEL补充知识补充知识3.4 函数函数简介简介4、函数输入、函数输入n在公式中直接输入函数调用在公式中直接输入函数调用n用函数调用向导输入用函数调用向导输入5、Excel的帮助系统的帮助系统EXCEL补充知识补充知识3.6
24、 常用常用工作表函数工作表函数1、汇总求和函数、汇总求和函数 n自动求和按钮自动求和按钮图示工作表图示工作表中的所有汇中的所有汇总数据可用总数据可用自动和按钮自动和按钮计算出来！计算出来！EXCEL补充知识补充知识3.6 常用常用工作表函数工作表函数2、SUM函数函数n用法用法SUM(x1,x2,x30)x1，x2，x30是需要求和的参数是需要求和的参数，可以是数据或单元格区域，可以是数据或单元格区域n功能功能n计算各参数的数值之和计算各参数的数值之和 n说明：说明：参数表中的数字、逻辑值及数字的文本表达式将被计参数表中的数字、逻辑值及数字的文本表达式将被计算，文本值被转换成数字，而逻辑值算，
25、文本值被转换成数字，而逻辑值“true”被转换成被转换成数字数字1。例如，例如，SUM(3,2)=5，SUM(9,20,true)=30。EXCEL补充知识补充知识3.6 常用常用工作表函数工作表函数n 如果参数为数组或引用，那么只有其中的数字被计如果参数为数组或引用，那么只有其中的数字被计算。数组或引用中的空白单元格、逻辑值、文本或错算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。误值将被忽略。n例如，设例如，设A1的值为的值为“9”，A2为为true，则公式，则公式SUM(A1,A2,20)的计算结果为的计算结果为20，而不是，而不是30。因。因为本公式中包括两个引用为本公式中
26、包括两个引用A1、A2，而，而A1的值为文的值为文本，本，A2的值为逻辑值，它们在计算时被忽略，最终的值为逻辑值，它们在计算时被忽略，最终就只有一个数值就只有一个数值20参与运算。参与运算。n SUM函数的参数最多可达函数的参数最多可达30个，不同类型的参数个，不同类型的参数可以同时出现。可以同时出现。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。EXCEL补充知识补充知识3.6 常用常用工作表函数工作表函数3、平均值函数
27、、平均值函数AVERAGE，AVERAGEA n用法用法nAVERAGE(n1，n2，n30)nAVERAGEA(n1，n2，n30)n其中，其中，n1，n2，n30是要计算平均值的参数，该是要计算平均值的参数，该函数最多允许有函数最多允许有30个参数个参数。参数可以是数字，或者是涉。参数可以是数字，或者是涉及数字的及数字的名称名称、数组数组或或引用引用 n功能功能n两函数都是求参数的两函数都是求参数的平均值平均值。AverAge不对不对文本文本和和逻辑值逻辑值类数据进行计算；类数据进行计算；nAverageA要对文本和逻辑函数进行平均值计算：要对文本和逻辑函数进行平均值计算：文本文本被视为被
28、视为0，逻辑值，逻辑值true被视为被视为1，false被视为被视为0，空文本（空文本（）也作为）也作为0计算计算 EXCEL补充知识补充知识3.6 常用常用工作表函数工作表函数n计数函数案例计数函数案例n下图说明下图说明AVERAGE函数与函数与AVERAGEA函数的区别函数的区别 EXCEL补充知识补充知识3.6 常用常用工作表函数工作表函数4、计数函数、计数函数COUNT、COUNTA、COUNTBLANK、COUNTIF n用法用法nCOUNT(x1,x2,x30)nCOUNTA(x1,x2,x30)nCOUNTBLANK(range)n功能功能nCount 统计数字的个数统计数字的个
29、数nCountA统计数字和文本的个数统计数字和文本的个数nCountBlank统计空白单元格的个数统计空白单元格的个数EXCEL补充知识补充知识3.6 常用常用工作表函数工作表函数n计数函数案例计数函数案例本例可以本例可以看出，用看出，用不同的函不同的函数对相同数对相同区域进行区域进行计数，统计数，统计结果是计结果是不同的。不同的。EXCEL补充知识补充知识3.6 常用常用工作表函数工作表函数n【例例】某班期末成绩表如图所示。现要统计每位同学的总某班期末成绩表如图所示。现要统计每位同学的总分，各科目的应考人数、缺考人数及各科平均成绩分，各科目的应考人数、缺考人数及各科平均成绩 EXCEL补充知
30、识补充知识3.7 逻辑函数逻辑函数n1、Excel的比较运算符的比较运算符n比较运算又称关系运算，就是人们常说的比较式。比较运算又称关系运算，就是人们常说的比较式。比较运算只有两种不同的结果，要么比较运算只有两种不同的结果，要么“正确正确”，要，要么么“错误错误”，不可能有第三种结果，不可能有第三种结果 比较运算符含 义示 例=等于a1=3大于a1b1小于a1=大于等于a1=60比较运算符含 义示 例=小于等于a1=”dd”不等于a10EXCEL补充知识补充知识3.7 逻辑函数逻辑函数2 AND、NOT、OR、TRUE、FALSE函数函数n用法用法nAND(x1,x2,x30)nOR(x1,x
31、2,x30)nNOT(logical)n案例案例nAND(TRUE,TRUE)=TRUE，nOR(TRUE,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)=TRUEEXCEL补充知识补充知识3.7 逻辑函数逻辑函数3、条件函数、条件函数IF n用法用法nIF(条件条件,表达式表达式1,表达式表达式2)。n功能功
32、能n当条件成立时，计算出表达式当条件成立时，计算出表达式1的值；当条件不成的值；当条件不成立时，计算出表达式立时，计算出表达式2的值的值n事例事例nIf(A160,“及格了及格了”,“不及格不及格”)，若，若A1单元格的单元格的值是值是76，则该函数的结果是：及格了；若，则该函数的结果是：及格了；若A1单元单元格的值是格的值是50，则该函数的结果是，则该函数的结果是:不及格。不及格。nIF函数的嵌套调用函数的嵌套调用EXCEL补充知识补充知识3.7 逻辑函数逻辑函数n【例例1】某单位有某单位有1 000多位党员，多位党员，要收党费要收党费。党。党费根据工资的高低，按不费根据工资的高低，按不同的
33、费率收取，收费的费同的费率收取，收费的费税如右表所示。税如右表所示。n类似于这样的工作，使用类似于这样的工作，使用IF函数进行数据的匹配与函数进行数据的匹配与查找是非常方便的。查找是非常方便的。n本例解决方法如下。本例解决方法如下。工 资党费费率2 000以上3%1 5002 0002.5%1 2001 5002%1 0001 2001.5%8001 0001%800以下0EXCEL补充知识补充知识3.7 逻辑函数逻辑函数nF3中公式意思为：当中公式意思为：当E3大于大于2 000时，公式的值为时，公式的值为3%；当当E3小于等于小于等于2 000，但大于，但大于1 500时，公式的值为时，公
34、式的值为2.5%；其余的以此类推；其余的以此类推 EXCEL补充知识补充知识3.7 逻辑函数逻辑函数n【例例2】某班考查成绩如下图某班考查成绩如下图A1:E10所示，将所示，将D列的地理列的地理成绩转换成等级制。成绩转换成等级制。EXCEL补充知识补充知识3.7 逻辑函数逻辑函数4、COUNTIF函数函数n用法用法nCOUNTIF(range,criteria)n功能功能 n统计出统计出range中满足条件中满足条件criteria的数据个数。的数据个数。EXCEL补充知识补充知识3.7 逻辑函数逻辑函数5、条件求和函数、条件求和函数SUMIFn用法用法SUMIF(range,criteria
35、,sum_range)n其中，其中，range是用于条件判断的单元格区域，是用于条件判断的单元格区域，criteria条件，条件，其形式可以为数字、表达式或文本；其形式可以为数字、表达式或文本；nsum_range是需求和的实际单元格。只有当是需求和的实际单元格。只有当range中的相应单元格满足条件时，才对中的相应单元格满足条件时，才对sum_range中的单元格求和。若省略中的单元格求和。若省略sum_range，则直接对则直接对range中的单元格求和。中的单元格求和。n功能功能 n对对range单元格区域中的数据进行单元格区域中的数据进行Criteria条件检查，然后对条件检查，然后对
36、满足条件行的满足条件行的sum_range同行进行求和。同行进行求和。EXCEL补充知识补充知识3.7 逻辑函数逻辑函数nSumIF案例案例n某商场的销售记录如图所示。统计各类商品的销售总数、总金某商场的销售记录如图所示。统计各类商品的销售总数、总金额。额。n统计男、女销售人员的人数及销售额在统计男、女销售人员的人数及销售额在10000以上的人数以上的人数n=SUMIF(D$3:D$13,I3,G$3:G$13)n向下复制此公式EXCEL补充知识补充知识逻辑函数逻辑函数工作表函数工作表函数5、综合案例、综合案例n某班学生成绩表如图所示，统计出其中的某班学生成绩表如图所示，统计出其中的平均分、总
37、平均分、总分、参考人数、等级及缺考人数分、参考人数、等级及缺考人数等数据。等数据。该工作表要该工作表要用到：用到：if,average,sum,count,countif，max,min等函数等函数EXCEL补充知识补充知识逻辑函数逻辑函数工作表函数工作表函数n在按下图所示，输入各单元格公式，然后向下在按下图所示，输入各单元格公式，然后向下或向右复制，就能得出需要的数据。或向右复制，就能得出需要的数据。EXCEL补充知识补充知识3.8 数学数学和三角函数和三角函数n1、概述、概述nExcel提供了许多数学和三角函数，它们能够提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可
38、以完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。返回到输入公式的单元格中。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)EXCEL补充知识补充知识3.8 数学数学和三角函数和三角函数3、数学函数应用案例、数学函数应用案例【例例1】用随机函数产生大量的实验数
39、据，计算排名。用随机函数产生大量的实验数据，计算排名。问题：有一张工资工作表，结构如图所示。现以问题：有一张工资工作表，结构如图所示。现以此表为例说明随机函数和数组的结合使用。此表为例说明随机函数和数组的结合使用。EXCEL补充知识补充知识3.8 数学数学和三角函数和三角函数n产生日期产生日期（1）在）在B2单元格中输入参加工作的最早时间，即在单元格中输入参加工作的最早时间，即在B2单元格中输入单元格中输入“1970/1/1”。（2）选中要产生日期的单元格区域，如）选中要产生日期的单元格区域，如B3:B8。（3）输入公式）输入公式“=B2+RAND()*1825”。（4）按）按Ctrl+Ent
40、er键。键。（5）将）将B3:B8格式化为需要的日期格式。格式化为需要的日期格式。n产生加班时间，计算加班工资产生加班时间，计算加班工资n（1）选中）选中C2:C8单元格区域。单元格区域。n（2）输入公式）输入公式“=INT(RAND()*600)”。n（3）按）按Ctrl+Enter键。键。EXCEL补充知识补充知识3.8 数学数学和三角函数和三角函数n产生基本工资产生基本工资假设基本工资在假设基本工资在8003 000这个范围内，其产生方法如下：这个范围内，其产生方法如下：（1）选择）选择D2:D8单元格区域。单元格区域。（2）输入公式）输入公式“=800+int(RAND()*2200)
41、”。（3）按）按Ctrl+Enter键。键。n产生其它数据产生其它数据假设图中的其他数据都在假设图中的其他数据都在1 000以内，其产生方法如下：以内，其产生方法如下：（1）选择）选择E2:H8单元格区域。单元格区域。（2）输入公式：）输入公式：“=int(RAND()*1000)”。（3）按）按Ctrl+Enter键键 EXCEL补充知识补充知识3.8 数学数学和三角函数和三角函数n计算总收入和收入排名计算总收入和收入排名n（1）在）在I2输入公式：输入公式：“=sum(D2:H2)”。向下复制此公式，。向下复制此公式，n（2）在）在J2中输入张大海的排名公式：中输入张大海的排名公式：“=R
42、ANK(I2,$I$2:$I$8)”。向下复制此公式。向下复制此公式。n上述操作的结果如下：上述操作的结果如下：EXCEL补充知识补充知识3.8 数学数学和三角函数和三角函数3、数学函数应用案例、数学函数应用案例【例例2】用舍入函数解决计算误差用舍入函数解决计算误差 问题：下图是一个工资数据表，问题：下图是一个工资数据表，B2:G3单元格区域的数单元格区域的数据是用数组公式据是用数组公式“=RAND()*100”产生的。产生的。n将小数点后的有效位将小数点后的有效位缩减为缩减为2位后的数据如第位后的数据如第4行所示；行所示；将将第第4行数据乘以行数据乘以3的结果如的结果如6行所示，可以看出，行
43、所示，可以看出，E6：G6中的计算结果是中的计算结果是错误错误的。的。n用用round函数将第函数将第1行的数据精确到小数点后行的数据精确到小数点后2位，其位，其结果如第结果如第8行所示，再将此结果乘以行所示，再将此结果乘以3，最后结果如第，最后结果如第10行所示。行所示。n第第10行的计算结果是正确的。行的计算结果是正确的。EXCEL补充知识补充知识3.8 数学数学和三角函数和三角函数EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数 1、Excel处理日期的方式处理日期的方式nMicrosoft Excel 将日期存储为序列号（称为序列值），即一系将日期存储为序列号（称为序列值）
44、，即一系列连续的数字编号，每一个数字编号代表一个日期。在默认情况列连续的数字编号，每一个数字编号代表一个日期。在默认情况下，数字下，数字1代表代表1900 年年 1 月月1 日，日，2代表代表1900年年1月月2日，日，39,448代表代表2008 年年 1 月月 1 日，因为从日，因为从1900 年年 1 月月 1 日到日到2008 年年 1 月月 1 日正好日正好 39,448 天。天。nExcel 将时间存储为小数，因为时间被看作天的一部分。时间也将时间存储为小数，因为时间被看作天的一部分。时间也被存为序列号，此序列号以秒为单位递增。因为一天共有：被存为序列号，此序列号以秒为单位递增。因
45、为一天共有：24*60*60=86400秒，所以秒，所以1/86400代表的时间是：代表的时间是：00:00:01，2/86400代表的时间是：代表的时间是：00:00:02。n日期和时间都是数值，因此它们也可以进行各种运算。日期和时间都是数值，因此它们也可以进行各种运算。如果要计算两个日期之间的差值，可以用一个日期减如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。去另一个日期。EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数2DATE函数函数n用法用法nDATE(year,month,day)n功能功能nDATE函数利用所给的参数，构造一个日期序列数函数利用所给的参数
46、，构造一个日期序列数 n例如例如nDATE(2005,3,21)的结果是的结果是2005-3-21EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数3YEAR，MONTH，DAY函数函数n用法用法nYEAR(serial_number)nMONTH(serial_number)nDAY(serial_number)n其中的其中的serial_number是一个日期或数字。是一个日期或数字。n功能功能nYEAR函数返回某日期的年份。函数返回某日期的年份。nMONTH函数返回以系列数表示的日期中的月份。函数返回以系列数表示的日期中的月份。nDAY函数返回以系列数表示的某日期的天数，用整函
47、数返回以系列数表示的某日期的天数，用整数数131表示。表示。EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数4TODAY、NOW函数函数n用法用法nTODAY()nNOW()n功能功能nTODAY函数返回系统的当前日期。函数返回系统的当前日期。nNOW函数计算当前日期和时间。函数计算当前日期和时间。EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数5WEEKDAYn用法用法nWEEKDAY(serial_number,return_type)n其中：其中：nserial_number代表要查找的日期，或日期的系列代表要查找的日期，或日期的系列数，以了解该日期为星期几；数，
48、以了解该日期为星期几；return_type确定返确定返回值类型的数字，。回值类型的数字，。n功能功能nWEEKDAY计算给定的日期是星期几计算给定的日期是星期几 EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数6、NETWORKDAYS函数函数n用法用法nNETWORKDAYS(start_date,end_date,holidays)n其中其中nstart_date表示代表开始日期，表示代表开始日期，end_date为终止为终止日期，日期，holidays表示不在工作日历中的一个或多个表示不在工作日历中的一个或多个日期所构成的可选区域，如元旦节、五一节、春节。日期所构成的可选区
49、域，如元旦节、五一节、春节。EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数n案例案例n【例例】某公司将每个员工的加班时间记录在某公司将每个员工的加班时间记录在Excel的工作表的工作表中，便于计算加班工资。加班工资按小时计算，若加班时中，便于计算加班工资。加班工资按小时计算，若加班时间不足间不足1小时，但超过半小时由按小时，但超过半小时由按1计时计算，不足半小时计时计算，不足半小时则不算加班时间。平时的加班工资每小时则不算加班时间。平时的加班工资每小时10元，节假时则元，节假时则加倍为加倍为20元每小时元每小时。n假设员工的加班时间如图的假设员工的加班时间如图的A1:C11所示，
50、现要计算加班时所示，现要计算加班时长（长（D列），判断加班时间是星期几和节假日（列），判断加班时间是星期几和节假日（E、F列），列），计算工龄（计算工龄（J列）、总加班时间（列）、总加班时间（L列）、节假时加班时间列）、节假时加班时间（L列）、工作日加班（列）、工作日加班（M列，本该休息，但加班了），以列，本该休息，但加班了），以及加班工资（及加班工资（N列）。列）。EXCEL补充知识补充知识3.9日期日期及时间函数及时间函数n用用日期、时间日期、时间函数计算下表函数计算下表D、E、F、J、K、L、M、N列相应数据。列相应数据。EXCEL补充知识补充知识3.10 字符字符函数函数1、概述、概述
