excel公式名称与函数.ppt

1、第5章 公式、名称与函数,Excel与数据处理 第3版,认识,函数好比EXCEL的武林秘笈，如果要成为EXCEL高手，谁都必须学习它。函数也是EXCEL与Word之类软件中的表格的主要区别，它使EXCEL具有强大的运算能力。EXCEL提供了涉及各个不同领域的函数接近400个。运用它们解决工作中的实际问题，能够极大提高工作效率，使枯燥的工作变得轻松、简单。但学习是一个枯燥而艰辛的过程！当然嘛，谁让你想成为高手呢？,本章学习目标,1、掌握数组公式的用法2、掌握名称的用法3、掌握常用工作表函数的用法4、掌握常见统计函数的用法5、掌握条件函数的用法，特别是：Ifsumifcountif等6、掌握解查找

2、引用类函数的用法7、掌握常见日期、文本类函数,5.1 数组公式及其应用,1、概述普通公式只执行一个简单计算，并且返回一个运算结果。数组公式能够同时对一组或两组以上的数据进行计算，计算的结果可能是一个，也可能是多个。在数组公式中使用的数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。,5.1 数组公式及其应用,2、数组公式的建立方法（1）选中需要保存数组公式结果的单元格或单元格区域。（2）输入公式的内容。（3）按Ctrl+Shift+Enter键。,5.1 数组公式及其应用,3、用数组公式计算两个数据区域的乘积 【例5.1】 某茶叶店经销多种茶叶，已知各种茶叶的单价以及12月份各种

3、茶叶的销量如图所示，计算各种茶叶的销售额。,5.1 数组公式及其应用,4、数组扩展 在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不匹配，Excel会自动扩展该参数 。比如，LG空调售价为7654元，重庆各商场的销售数据如下图的B、C两列所示。现要计算各商场的销售总额。,5.1 数组公式及其应用,【例5.2】 某次期末考试的学生成绩表如图5.2所示，其中有四科成绩，现要计算各科成绩的总分，以及各科成绩的综合测评分数。总分的计算方式为各科成绩的总和，综合测评分的计算方式为“政治经济学*0.2+(技术经济学+计算机基础+PASCAL语言程序设计)*0.8”。,5.1

4、 数组公式及其应用,【例5.3】假设某商场A型电视机售价一样，现有一个28天的销售记录，计算每天的销售额，如图5.3所示。D列销售额的计算方法如下：,5.1 数组公式及其应用,5、二维数组 涉及许多行(或列)数据处理的数组公式，就是所谓的二维数组。合理地运用二维数组的运算功能，会提高数据处理的能力，有时在不同工作表之间进行数据汇总时会特别有效 。,5.1 数组公式及其应用,案例【例5.4】 某商场秋季进行换季服装大降价，所有服装都在打折，打折的比例随时都在调整，计算打折后的新价格。如图5.4所示。,5.1 数组公式及其应用,【例5.5】出版社要统计2008年发往各地新华书店的各类图书的销售量，

5、并由此计算出各类图书的总销售额。设每个省都把本地各新华书店的销售量统计在一个工作表中，最后有许多工作表的数据要进行汇总，图5.5该出版社2008年各类图书在四川、重庆两者的销量情况表（实际的应用更加复杂）。,5.1 数组公式及其应用,用数组公式汇总图书,5.2 公式的循环引用,1、循环引用的概念如果公式引用了自己所在的单元格，不论是直接的还是间接的，都称为循环引用。在一般情况下，很少使用循环引用来处理问题。但对于迭代求解一类问题，如求数的阶乘、数列求和等问题，可以用循环引用求解。若循环引用的条件或迭代次数设置不当，EXCEL会报告一个错误信息。,5.2 公式的循环引用,【例5.6】下图是循环引

6、用的一个事例。在A1单元格中输入的公式“=A2+A3”；在A2单元格中输入数字2；在A3单元格中输入公式“=A1*0.3”，见图(a)。这样，A3单元格的值依赖于A1，而A1单元格的值又依赖于A3，它们形成了间接的循环引用，在未进行迭代求解情况下的结果如图（b）所示。该循环引用是可解的，进行迭代求解最后的结果如图（c）所示。,5.3 名称,5.3.1、名称概述 可以给一个单元格或单元格区域取一个名称，这个名称可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。名称其实是一个标识符，由字母或下划线开头的一个或多个字符组成。名称不区分字母的大小写，比如ABC、abc、Ab

7、c都是同一个名称。在Excel中，还可以用汉字作为名称。可以为同一单元格或单元格区域定义多个不同的名称。名称一旦定义就可以在同一工作薄的不同工作表之间共用。,5.3 名称,2、名称的意义在Excel中，同一个工作簿中的名称是共享的，在一个工作表中定义的名称，可以被另一个工作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的名称在不同的工作表中查找到正确的单元格或单元格区域。名称给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，用名称就能找到它。,5.3.2 名称的定义,在Excel中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单元格组

8、合定义一个名称（或多个名称） 定义名称有多种方法，如定义、粘贴、指定或标志等,5.3.2 名称的定义,(1)、使用命令定义单元格或单元格区域的名称【例5.7】在下图中，B2单元格被命名为“存款利率”，在D5中输入的公式是“=C5*存款利率”，其中的“存款利率”就是B2单元格的名称。,B2单元格的名称定义方法如下：,5.3.2 名称的定义,（1）单击“公式”|“定义的名称”组中|“定义名称”命令按钮.（2）在弹出的“新建名称”对话框的“引用位置” 输入要定义名称的单元格引用位置。（3）在“在当前工作簿中的名称” 中输入名称,5.3.2 名称的定义,(2)、使用指定的方式定义名称“指定”的方式一次

9、可定义多个名称：可以将表格的首行或首列指定为相应的列或行的名称。方法（1）选中要指定名称的单元格区域。（2）单击“公式”|“定义的名称”组中|“根据所选内容创建”命令按钮。（3）在弹出的“以选定区域创建名称”对话框中，选中“首行”、“最左列”的复选框。这样就把该表的首行、首列指定成了相应的列、行的名称,5.3.2 名称的定义,指定首行、最左列为名称的案例【例5.8】 图5.10是某建筑工地的工人奖金统计表，一共有几百个工人，其中的某些工人会随时查询自己的奖金，这就需要在A列的几百行数据中进行查找。名字能够很好地解决这类问题，方法如下。,5.3.2 名称的定义,(3)用名称栏定义名称工作表全选按

10、钮（工作表列标A左边的按钮）上面的编辑框称为名称栏，它随时显示当前单元格的名称或引用。它可以用来定义名称。【例5.9】 在图5.11中，C4:C8区域中输入的是数组公式：“=数量*单价”，其定义方法请见图中的标注。,名称栏,5.3.3 名称的应用,定义一个工作薄级的名字后，该名字就能被本工作簿中的各个工作表直接引用。【例5.10】 图5.12是某蔬菜超市的销售记录。该超市把蔬菜单价存放在一张工作表中，如图5.12（a）所示，每种蔬菜的销售记录则保存在不同的工作表中，如图5.12（b）所示。现以小白菜的销售金额计算为例，说明名字的跨表引用问题。,5.3.3 名称的应用,1）通过名称在不同的工作表

11、之间传递数据,通过名称查找蔬菜单价,指定A2:B12最左边为名称,5.3.3 名称的应用,2)名称结合Indirect函数的应用指定A2:B12的最左列为名称；用名称查找蔬菜单价，见D列；若不用名称，单价要从C列的几百个数据中去查找！ 这种方法虽然比直接从工作表的单元格中查找数据方便了不少，但输入量仍然较大。用它处理成千上万行的数据查询仍然低效率。最佳方法是用名称和Indirect函数相结合，非常方便，高效。,5.3.3 名称的应用,某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各蔬菜的单价。,指定A2:B11区域的“最左列”为名称,在D2中输入公式：=

12、INDIRECT(B2)向下复制该公式！,5.3.3 名称的应用,2）名称与Indirect函数结合应用Indirect函数与名称相结合，可使数据查找更方便。Indirect函数的用法是： Indirect(x) 其中的X可以是单元格引用或名称。若X为单元格引用，它必须用“”引起来，如：=INDIRECT(“A1”)，将返回A1单元格中的内容若X为单元格名称，它将返回该名称所对应的单元格的内容。,5.3.3 名称的应用,3)应用行列交叉点查找数据所谓行列交叉点指的是由行的名称 列的名称确定的单元格。 把一个工作表的首行、首列指定为名称之后，可以使用名称引用行、列交叉点对应单元格中的数据。案例【

13、例5.11】 图5.13是一个学生成绩表，用指定的方式把它的首行、首列指定成了名字。,5.3.3 名称的应用,1、建立如下表格并选中A1:F8区域2、选择“公式”|“定义的名称”|“根据所选内容创建”命令4、指定名字对话框中和项为名称现在做试验1、选中K2:K8,输入“=化学”,按“Ctrl+Shift+Enter”2、选中A10:F10,输入“=李四”,按“Ctrl+Shift+Enter”3、在L2单元格中输入公式“=李达 化学”,5.3.3 名称的应用,4)引用其他工作簿中的数据 当一个公司的数据来源于不同的分公司或部门时，往往需要对不同分公司或部门的数据进行汇总，这就要涉及多个工作薄的

14、数据操作。名称可以很好地解决这个问题。【例5.12】 采虹电视经销全国各省，每季度都要统计各种型号电视的销售数量。各省的销售代理每季度向采虹厂提供一个含有各种型号电视的汇总统计表。现在对各省的报表数据进行汇总统计。,指定A3:B8区域”最左列”为名称,指定A3:B8区域”最左列”为名称,B3单元格中输入公式“=四川.xls!_21英寸+重庆.xls!_21英寸”；在B4单元格中输入公式“=四川.xls!_25英寸+重庆.xls!_25英寸”，其余以此类推,5.3.4 定义常量名称,1)常量名称的意义用名字代表常量，如圆周率3.14159，自然对数的底数e等，然后在整个工作簿中引用这些常量名，会

15、给数据处理带来许多方便，这使用户在运用这些数据时不必关心它所在的工作表或单元格，直接用其名字就可以了。常量名称的另一个好处:当常量值变化时,只需要修改常量定义就行了,不必须在各具工作表的公式中寻找常量值并进行修改.,5.3.4 定义常量名称,常量名称的定义【例5.13】 图5.15是用名字常量pi=3.14计算圆周长和圆面积的简单例子。,在名称中输入名称,指定名称的有效范畴,输入名称对应的公式或常数,直接应用了常量名称Pi计算圆的周长和面积,5.3.5 名称管理器,选择“公式”选项卡，单击其中“定义的名称”组中的“名称管理器”按钮,显示出名称管理器对话框,通过“名称管理器”可以新建编辑删除名称

16、,可以重指定名称的含义或对应的单元格引用位置.,5.4 函数简介,1、函数的概念函数是能够完成特定功能的程序。在Excel中，它是系统预定义的一些公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算，然后把计算的结果存放在某个单元格中。 在大多数情况下，函数的计算结果是数值。当然，它也可以返回文本、引用、逻辑值、数组或工作表的信息,5.4 函数简介,2、Excel函数分类,5.4.1 函数调用,1、函数的语法 函数名(参数1, 参数2, 参数3,) 在公式中调用函数,Excel 2007允许嵌套多达64层,Excel 2007可达255个参数,5.4.2 函数输入,在公式中直接输入函

17、数调用用函数调用向导输入5、Excel的帮助系统,5.5 使用Excel帮助理解函数,Excel的帮助系统具有极其强大的功能，利用帮助系统能够解决使用Excel过程中所遇到的各种问题。这些问题包括Excel的新技术、疑难、专用名字解决、函数说明、函数应用实例等。Excel的帮助系统比许多参考书内容更全面，讲述更清楚，一个Excel的真正用户应该会运用帮助系统，从中获取有用的资料。进入Excel帮助系统的常用方法有：按F1键。单击功能选项卡标题栏最右边的按钮,5.6 逻辑函数,5.6.1 Excel的比较运算符比较运算又称关系运算，就是人们常说的比较式。比较运算只有两种不同的结果，要么“正确”，

18、要么“错误”，不可能有第三种结果,5.6.2 AND、NOT、OR、TRUE、FALSE函数,AND、NOT、OR、TRUE、FALSE函数用法AND(x1,x2, ,x30)OR(x1, x2, ,x30)NOT(logical)案例AND(TRUE,TRUE)=TRUE，OR(TRUE,TRUE)=TRUEAND(TRUE,FALSE,TRUE, TRUE)=FALSE，OR(TRUE,FALSE,TRUE,TRUE)=TRUE。如果B1，B2，B3单元格中的值为 TRUE，FALSE，TRUE，则AND(B1:B3)=FALSE，但OR(B1:B3)=TRUE。AND(2,2+3=5)=

19、TRUE,5.6.3 条件函数IF,用法IF(条件, 表达式1, 表达式2)。功能当条件成立时，计算出表达式1的值；当条件不成立时，计算出表达式2的值事例If(A160,“及格了”,“不及格”)，若A1单元格的值是76，则该函数的结果是：及格了；若A1单元格的值是50，则该函数的结果是:不及格。IF函数的嵌套调用,5.6 逻辑函数,【例5.15】某单位有1 000多位党员，要收党费。党费根据工资的高低，按不同的费率收取，收费的费税如右表所示。类似于这样的工作，使用IF函数进行数据的匹配与查找是非常方便的。本例解决方法如下。,5.6 逻辑函数,F3中公式意思为：当E3大于2 000时，公式的值为

20、3%；当E3小于等于2 000，但大于1 500时，公式的值为2.5%；其余的以此类推,5.6 逻辑函数,【例5.16】某班考查成绩如下图A1:E10所示，将D列的地理成绩转换成等级制。,5.7 统计函数,5.7.1、汇总求和函数 自动求和按钮,1、选中F62、单击“公式”选项卡中“自动求和”按钮，就能求出F3、F4、F5之和。3、同方法求其它汇总和。,5.7.1、汇总求和函数,2、SUM和SumQ函数用法SUM(x1, x2,x255) SUMQ(x1, x2,x255) x1，x2，x30是需要求和的参数 ，可以是数据或单元格区域功能sum计算各参数的数值之和 ;sumq计算各数的平方之后

21、说明： 参数表中的数字、逻辑值及数字的文本表达式将被计算，文本值被转换成数字，而逻辑值“true”被转换成数字1。 例如，SUM(3,2)=5，SUM(9,20,true)=30。,5.7.1、汇总求和函数, 如果参数为数组或引用，那么只有其中的数字被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。例如，设A1的值为“9”，A2为true，则公式SUM(A1,A2,20)的计算结果为20，而不是30。因为本公式中包括两个引用A1、A2，而A1的值为文本，A2的值为逻辑值，它们在计算时被忽略，最终就只有一个数值20参与运算。 参数最多可达255个，不同类型的参数可以同时出现。例如，

22、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、条件求和函数SUMIF用法SUMIF(range,criteria,sum_range) 其中，range是用于条件判断的单元格区域，criteria条件，其形式可以为数字、表达式或文本；sum_range是需求和的实际单元格。只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。若省略sum_range，则直接对range中的单元格求和。 功

23、能 对range单元格区域中的数据进行Criteria条件检查，然后对满足条件行的sum_range同行进行求和。,5.7.1 汇总求和函数,2、条件求和函数SUMIFS用法SUMIFS(sum_range, range1,criteria1, range2,criteria2) 其中range1, range2, 是计算关联条件的 1 至 127 个区域；Criteria1, criteria2, 是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求和。这些区域与条件是对应的，即Criteria1是用于range1区域的条件，criteria2是用于ran

24、ge2的条件，以此类推。Sum_range是求和区域。功能 SUMIFS函数对某一区域内满足多重条件的单元格求和。,5.7.1 汇总求和函数,【例5.18】 某家电商场的销售记录如图5.22的A1:G13所示。现要统计出各种电器的销售总数量和销售总金额，并将它统计在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$1

25、0)(2)将此公式向下复制到K4、K5、K6,在J10单元格中输入统计李本成销售的彩电总数量的计算公式：=SUMIFS($E$3:$E$13,$D$3:$D$13,$I10,$A$3:$A$13,J$9)(2)将此公式向下角复制到L13，,5.7.1 汇总求和函数,4、乘积和与平方和SUMPRODUCT函数可在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。用法如下：SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 为 2 到 255 个数组，其相应元素需要进行相乘并求和。此外，SUMXMY2(array_x,a

26、rray_y)SUMX2MY2(array_x,array_y)SUMX2PY2(array_x,array_y) SUMXMY2计算两数组中对应数值之差的平方和 ，SUMX2PY2计算两数组中对应数值的平方和之和 ，SUMX2MY2计算两数组中对应数值的平方差之和。,5.7.2 平均值函数,1、平均值函数AVERAGE，AVERAGEA 用法AVERAGE(n1，n2，n255)AVERAGEA(n1，n2，n255)其中，n1，n2，n255是要计算平均值的参数，该函数最多允许有255个参数。参数可以是数字，或者是涉及数字的名称、数组或引用 功能两函数都是求参数的平均值。AverAge不对

27、文本和逻辑值类数据进行计算；AverageA要对文本和逻辑函数进行平均值计算：文本被视为0，逻辑值true被视为1，false被视为0，空文本（）也作为0计算,5.7.2 平均值函数,计数函数案例下图说明AVERAGE函数与AVERAGEA函数的区别,5.7.2 平均值函数,2条件平均值函数Excel2007提供了两个根据条件计算平均值的函数AVERAGEIF和AVERAGEIFS。AVERAGEIF(average_range, range1,criteria1)AVERAGEIFS(average_range,range1,criteria1,range2,criteria2)其中aver

28、age_range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用；range1, range2, 是计算关联条件的 1 至 127 个区域；Criteria1是用于range1的条件, criteria2是用于range2的条件, 它可以是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求平均值。,5.7.2 平均值函数,【例5.20】某高三年级的期末考试成绩表如图5.21的B1:F10区域所示，统计每个班男女生的各科目平均成绩。,在J4中输入如下公式,并将它向下向右复制到其它单元格=AVERAGEIFS(D$4:D$10,$B$

29、4:$B$10,$H4,$C$4:$C$10,$I4),5.7.3 统计个数的函数,1、计数函数COUNT、COUNTA、COUNTBLANK、COUNTIF 用法COUNT(x1, x2, ,x30)COUNTA(x1, x2, ,x30)COUNTBLANK (range)功能Count 统计数字的个数CountA统计数字和文本的个数CountBlank统计空白单元格的个数,5.7.3 统计个数的函数,【例5.21】 用count和countA函数统计单元格个数。图5.22是用这两个函数统计同一区域的不同结果。,5.7.3 统计个数的函数,2、COUNTIF函数用法COUNTIF(rang

30、e, criteria)功能 统计出range中满足条件criteria的数据个数。3、CountifsExcel 2007还提供了一个多条件统计函数COUNTIFS，它可以一次对多个不同区域进行不同条件的计数，其用法如下：COUNTIF(range1， criteria1，range2， criteria2，range127， criteria127),5.7.3 统计个数的函数,【例5.22】有学生成绩表如图5.23所示。统计其中总分220分以下的人数；统计总分200分以上，物理80分以下，地理70分以下，化学65分以下的人数；统计姓王的同学人数。,5.7.3 统计个数的函数,3计算大小、

31、百分比和名次的函数LARGE(array,k)SMALL(array,k)LARGE函数返回数据集中第k个最大值，SMALL返回数据集中第 k 个最小值，这两个函数可以根据相对标准来选择数值，返回数据集中特定位置上的数值。例如，可以使用函数LARGE得到第一名、第二名或第三名的得分。,5.7.3 统计个数的函数,中间值函数MEDIAN和众数函数MODEMEDIAN(number1,number2,.)MODE(number1,number2,.)MEDIAN函数计算出给定数据的中值，MODE函数返回一组数中出现次数最多的数（众数）。,5.7.3 统计个数的函数,排名函数RANKRANK(num

32、ber,ref,order)。RANK函数返回一个数字在一组数中的排位，即位次。Number为需要找到排位的数字，Ref可以是一个数组或单元格区域，Ref 中的非数值型参数将被忽略。Order用于指明排位的方式，如果为 0或省略，则Rank 对数字的排位是基于 ref 为按照降序排列的列表；如果 order 不为零，则Rank对数字的排位是基于 ref 为按照升序排列的列表。,【例5.23】有学生成绩表如图5.24中A1:F10区域所示。计算各学生成绩的名称、百分比排位、第3名和倒数第3名总分、中间成绩、出现次数最多的分数及在38%总分位置的分数大致是多少等数据。,在G3中输入公式：=RANK

33、(F3,$F$3:$F$10) 向下复制此公式可计算名次,在H3中输入公式：=PERCENTRANK($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.7 统计函数,【例5.24】某班期末成绩表如图所示。现要统计每位同学的总分，各科目的应考人数、缺考人数及各科平均成绩,5.8 数学和三角函数,1、概述Excel提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将

34、公式的计算结果返回到输入公式的单元格中。2、常见的数学函数MOD 、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 数学和三角函数,3、数学函数应用案例【例5.25】用随机函数产生大量的实验数据，计算排名。问题：有一张工资工作表，结构如图所示。现以此表为例说明随机函数和数组的结合使用。,5.8 数学和三角函数,产生日期（1）在B2单元格中输入参加工作的最早时间，即在B

35、2单元格中输入“1970/1/1”。（2）选中要产生日期的单元格区域，如B3:B8。（3）输入公式“=B2+RAND()*1825”。（4）按Ctrl+Enter键。 （5）将B3:B8格式化为需要的日期格式。产生加班时间，计算加班工资（1）选中C2:C8单元格区域。（2）输入公式“= INT(RAND()*600)”。（3）按Ctrl +Enter键。,5.8 数学和三角函数,产生基本工资假设基本工资在8003 000这个范围内，其产生方法如下：（1）选择D2:D8单元格区域。（2）输入公式“=800+int(RAND()*2200)”。（3）按Ctrl +Enter键。产生其它数据假设图中

36、的其他数据都在1 000以内，其产生方法如下：（1）选择E2:H8单元格区域。（2）输入公式：“=int(RAND()*1000)”。（3）按Ctrl +Enter键,5.8 数学和三角函数,计算总收入和收入排名（1）在I2输入公式：“=sum(D2:H2)”。向下复制此公式，（2）在J2中输入张大海的排名公式：“=RANK(I2,$I$2:$I$8)”。向下复制此公式。上述操作的结果如下：,5.8 数学和三角函数,3、数学函数应用案例【例5.26】用舍入函数解决计算误差 问题：下图是一个工资数据表，B2:G3单元格区域的数据是用数组公式“=RAND()*100”产生的。 将小数点后的有效位缩

37、减为2位后的数据如第4行所示；将第4行数据乘以3的结果如6行所示，可以看出，E6：G6中的计算结果是错误的。用round函数将第1行的数据精确到小数点后2位，其结果如第8行所示，再将此结果乘以3，最后结果如第10行所示。第10行的计算结果是正确的。,5.8 数学和三角函数,5.9日期及时间函数,1、Excel处理日期的方式Microsoft Excel 将日期存储为序列号（称为序列值），即一系列连续的数字编号，每一个数字编号代表一个日期。在默认情况下，数字1代表1900 年 1 月1 日，2代表1900年1月2日，39,448代表2008 年 1 月 1 日，因为从1900 年 1 月 1 日

38、到2008 年 1 月 1 日正好 39,448 天。Excel 将时间存储为小数，因为时间被看作天的一部分。时间也被存为序列号，此序列号以秒为单位递增。因为一天共有：24*60*60=86400秒，所以1/86400代表的时间是：00:00:01，2/86400代表的时间是：00:00:02。日期和时间都是数值，因此它们也可以进行各种运算。如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。,5.9日期及时间函数,2DATE函数用法DATE(year, month, day)功能DATE函数利用所给的参数，构造一个日期序列数 例如DATE(2005,3,21)的结果是2005-3-2

39、1,5.9日期及时间函数,3YEAR，MONTH，DAY函数用法YEAR(serial_number)MONTH(serial_number)DAY(serial_number)其中的serial_number是一个日期或数字。功能YEAR函数返回某日期的年份。MONTH函数返回以系列数表示的日期中的月份。DAY函数返回以系列数表示的某日期的天数，用整数131表示。,5.9日期及时间函数,4TODAY、NOW函数用法TODAY( )NOW( )功能TODAY函数返回系统的当前日期。NOW函数计算当前日期和时间。,5.9日期及时间函数,5WEEKDAY用法WEEKDAY(serial_numbe

40、r, return_type)其中：serial_number代表要查找的日期，或日期的系列数，以了解该日期为星期几；return_type确定返回值类型的数字，。功能WEEKDAY计算给定的日期是星期几,5.9日期及时间函数,6、NETWORKDAYS函数用法NETWORKDAYS(start_date, end_date, holidays) 其中start_date表示代表开始日期，end_date为终止日期， holidays表示不在工作日历中的一个或多个日期所构成的可选区域，如元旦节、五一节、春节。,5.9日期及时间函数,案例【例.27】某公司将每个员工的加班时间记录在Excel的工

41、作表中，便于计算加班工资。加班工资按小时计算，若加班时间不足1小时，但超过半小时由按1计时计算，不足半小时则不算加班时间。平时的加班工资每小时10元，节假时则加倍为20元每小时 。假设员工的加班时间如图的A1:C11所示，现要计算加班时长（D列），判断加班时间是星期几和节假日（E、F列），计算工龄（J列）、总加班时间（L列）、节假时加班时间（L列）、工作日加班（M列，本该休息，但加班了），以及加班工资（N列）。,5.9日期及时间函数,用日期、时间函数计算下表D、E、F、J、K、L、M、N列相应数据。,5.10 字符函数,1、概述Excel提供了接近30个文本、字符方面的函数，用这些函数对工作表

42、中的文本数据进行查找、替换、取子串、数字与文本的转换，以及数据的格式化操作等都有十分重要的作用 。2、常用文本类函数RIGHT(text,n)该函数从text文本的右边取出n个字符。LEFT(text,n)该函数从text文本的左边取出n个字符,5.10 字符函数,MID(text,n,m)该函数从text文本的第n个字符起，取出m个字符。REPLACE(text1,n,m,text2)该函数用text2替换text1文本的第n个位置开始的m个字符。SEARCH(text1,text2,n)该函数从text1的第n个字符位置开始查找text1中是否包含text2，若找就返回text2在text

43、1中的起始位置。若没有找到则返回错误值“#value!”,5.10 字符函数,TEXT(value,format_text)该函数指定的格式码format_text将数值型数据Value格式化为文本。其中value可为数字、结果为数字的计算公式、包含数字的单元格引用。format_text可为任何数字格式（有关格式码请参考第4章的相关内容）。VALUE(text)将数字形式的文本转换成数字。比如：VALUE(32)=32，VALUE(09)=9,5.10 字符函数,案例【例5.28】文本函数应用举例 某银行要建立下图所示的用户资料表。其中的出生日期从身份证号码中提取；如果是男性，其尊称就是“X

44、先生”，如果是女性，其尊称就是“X女士”，X是用户的姓氏；存款帐号是出生日期（去掉出生年的前两位数字）；存款密码是身份证号码的最后6位数字；最后要找出解放西路的用户姓名，以便给他们发新的银行卡。,5.10 字符函数,用文本类函数产生E2:I9区域的数据。,5.10 字符函数,（1）产生出生日期在E2单元格输入公式，然后将它向下复制此公式就能够生成所有人的出生日期。=MID(D2,7,4)&年&VALUE(MID(D2,11,2)&月&MID(D2,13,2)&日“（2）生成用户的尊称在F2单元格输入下面的公式，并将此公式向下复制，生成所有人的尊称。=LEFT(A2,1)&IF(B2=女,女士,

45、先生)（3）生成存款帐号在G2单元格输入下面的公式，并将此公式向下复制，生成所有人的存款帐号。=TEXT(E2,yymmdd),5.10 字符函数,（4）生成存款密码在H2中输入下面的公式，并向下复制，产生所有人的存款密码。=RIGHT(D2,6)（5）找出解放西路的用户姓名在I2单元格中输入下面的公式，并向下复制=IF(SEARCH(解放西路,C2),A2),5.11 错误信息函数,1、 Excel的常见错误信息,5.11 错误信息函数,2、 Excel错误信息函数,5.11 错误信息函数,案例【例5.29】某商场将各种商品的定价保存在一个独立的工作表中，如图（a）所示。 将产品的销售记录保存在另一个工作表中，如图（b）所示。其中销售记录表中的“单价”是用Vlookup函数从“商品定价表”中查找。F列总额是用公式计算出来的；G列数据是条件函数和文本函数查询的结果；,3.11 错误信息函数,虽然图（b）的信息是我们需要的，但在实际工作中，这样的工作表并不实用。用错误信息处理函数处理后的工作表如下页所示，这才是可用的工作表,3.11 错误信息函数,用错误信息函数处理的结果,The End,谢谢大家！,