EXCEL函数应用.ppt

excel函数应用,管理部人力资源组2008年9月25日,课程大纲,课堂讲解篇:函数功能说明与案例讲解(共20个)课后自学篇:基础函数功能说明(25个),excel函数教学目的,通过功能说明与案例演示,了解函数的应用范围与功效,激发后续学习函数的兴趣掌握函数使用的方法与技巧充分发挥excel的强大数据处理功能，提升工作效率,函数应用初步演示,利用函数在excel中制作工资条go利用函数将选定区域的偶数行全部做上颜色标记go,excel函数的结构,excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。,excel函数结构:,也有一些函数是没有参数的,如row(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,函数参数常用符号或表示方法,函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入东南汽车或“东南汽车”连接符:a2:b7；1:1；1:5；f:f；a：n,go,1.2.5台湾地区的认证要求,台湾“标准检验局”（bsmi）为了岛内电子、电机产品的电磁辐射干扰，于1995年5月公布商品电磁兼容性管理办法并于1996年7月正式公告自1997年1月1日起管制复印机等产品的电磁兼容性能之后陆续管制信息周边产品、家电与广播音响产品。而“标准检验局”也依据cispr与iec的emc标准，逐渐修订岛内相应标准cns，例如cns13438就是信息类产品的标准。岛内申请厂商其产品符合了emc要求后，便可以依检验单位提供的产品电磁兼容型式试验报告正本一份（含外观及内部结构照片）并加附下列资料：中文使用手册及规格，登录号码（id）标示位置及式样说明，电路方框图，对策元件及干扰源一览表。再填具申请书后，向所在地检验机构申请，由检验机构核发检磁号码证书。,1.2.4新西兰与澳大利亚的认证要求,新西兰与澳大利亚的电磁兼容管理主要是依据1992年公告的无线电波法（radiocommunicationact）。该法于1996年1月1日生效，并于1997年1月1日起强制实施。对信息技术设备产品需符合as/nzs3548电磁辐射干扰规定。澳洲在emc方面管制的架构与欧盟ce-marking大致雷同，均采用自我认证的方式。依产品标准执行且通过测试后，签署一自我宣告书（doc）即可。所不同的是宣告书必须由澳洲境内的进口商、供货商或制造商签署宣告。另澳洲政府还要求每一澳洲本地的供货商或进口商必须向其执行单位aca(australiancommunicationsauthority)登录。按规定做成c-tick标记，贴于产品适当位置。,函数列表,value,用途：将表示数字的文字串转换成数字。语法：value(text)。参数：text为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是excel可以识别的任意常数、日期或时间格式。如果text不属于上述格式，则value函数返回错误值#value!。,value演示go,文本型态的数字不可计算,通过value函数转换就可以计算了,if:逻辑函数,功能：if函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果。它的应用很广泛，可以使用函数if对数值和公式进行条件检测。语法格式：if(条件,a,b)。其中“条件”表示计算结果为true或false的任意值或表达式，若为常量，视为true。条件参数可使用任何比较运算符。条件返回的结果为真的话，则执行a，否则执行b。if函数仅可以嵌套七层，超过将会出错。,if函数的简单案例一go,性别为男者,称谓为xx先生;性别为女者,称谓为xx女士,案例二:依下述税率表,利用if函数计算个人所得税(课后练习),注意：因if仅可嵌套七层，所以无法计算月薪超过102000者个人所得税,假如a3为存放税前工资的单元格,个人所得税计算公式如下:=if(a382000,(a3-2000)*40%-10375,if(a362000,(a3-2000)*35%-6375,if(a342000,(a3-2000)*30%-3375,if(a322000,(a3-2000)*25%-1375,if(a37000,(a3-2000)*20%-375,if(a34000,(a3-2000)*15%-125,if(a32500,(a3-2000)*10%-25,if(a32000,(a3-2000)*5%,0),go,sumif:条件求和函数,用途：根据指定条件对若干单元格、区域或引用求和。语法：sumif(条件区域，条件，需求和的区域)参数：条件是由数字、逻辑表达式等组成的判定条件。,sumif案例go,请统计人资组同仁的工资总额:sumif(a:a,人资组,c:c),countif：条件计数函数。,用途：计算区域中满足给定条件的单元格的个数。语法：countif(统计区域，条件)参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件”为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。,countif案例go,以上述样表为据,完成以下案例:例1：依“师员”字段，统计师员级的人数countif(c:c,师)；countif(c:c,员)例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif(i:i,=3)；countif(i:i,=3)-countif(i:i,=5)例3:检测到职编号是否有重复值countif(a:a,a2),counta,用途：返回参数组中非空值的数目。利用函数counta可以计算数组或单元格区域中数据项的个数。语法：counta(单元格区域1,单元格区域2)说明：参数的个数为130个。,counta的思考案例,例1：如果a1=6.28、a2=3.74，其余单元格为空，则公式“=counta(a1:a7)”的计算结果等于?。例2：统计c列的非空白单元格的表示方法为：？;若整张excel表的每个单元格都有数据,前述函数公式的结果为?,offset,用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域（并不返回值）。语法：offset(作为偏移量参照系的引用位置，上下偏移的行数，左右偏移的列数，height，width)。参数：作为偏移量参照系的引用位置：它必须是单元格或相连单元格区域的引用；上(下)偏移的行数：正数代表在起始引用下边;负数代表在起始引用的上边；0代表没有上（下）偏移。左(右)偏移的列数:正数代表在起始引用右边;负数代表在起始引用的左边；0代表没有左（右）偏移。height：是要返回的引用区域的总行数（必须为正数）width：是要返回的引用区域的总列数（必须为正数）。,offset的思考题,问题一：offset(b1,2,1,4,1)定位到哪几个单元格？问题二：以a1单元格为参照，要定位到红线区域如何写函数：=offset(a1,6,1,2,2),counta、offset的综合应用案例go,要求：当记录增加时，公式自动统计总销量（假设销量存放600数值的单元格为c3）：=sum(offset(c3,0,0,counta(c:c)-1,1)或=sum(offset(c2,1,0,counta(c:c)-1,1)注：以上公式只能正确计算不间断的连续数据,如果表格中销量的数据有空白单元格,那么动态名称的引用位置将发生错误,column,用途：返回给定引用的列标。语法：column(单元格引用)。参数：“单元格引用”为需要得到其列标的单元格，如果省略，则假定函数column是对所在单元格的引用。实例：公式“=column(a3)”返回1，=column(d5)返回?。,row,用途：返回给定引用的行号。语法：row(单元格引用)。“单元格引用”为需要得到其行号的单元格或单元格区域。实例:利用row建立序号:go,mod：取余,用途：返回两数相除的余数，其结果的正负号与除数相同。语法：mod(被除数，除数)参数：除数不能为零。,mod函数的案例,实例1：公式“=mod(14，4)”返回?；“=mod(-5，-2)”返回？。实例2:如何利用mod函数，将整张excel表的偶数行都标上底色go,vlookup,返回表格或数组当前行中指定列处的数值。语法：vlookup(索引值，数据区域，列序号，查找方式)参数：索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。列序号指数据区域中待返回的匹配值的列序号。它等于1时，返回数据区域第一列中的数值；它等于2时，返回数据区域第二列中的数值，以此类推。查找方式为一逻辑值,指明函数vlookup返回时是精确匹配还是近似匹配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值的最大数值；如果为0，函数vlookup将返回精确匹配值。如果找不到，则返回错误值#n/a。,vlookup案例,案例一:利用vlookup抓取另一张表中的资料。go案例二(请参考链接之案例,课后自学)：用vlookup计算所得税,解决if只能嵌套七层的局限。go,match,用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置(不是具体的单元格)。语法：match(a，b，c)。参数：a:为需要在数据表中查找的数值或单元格引用。b:是可能包含所要查找的数值的连续单元格区域。c:它说明excel如何在b中查找a。c的常用值为0，表示函数match查找等于a的第一个数值。注意：match函数返回b中目标值的位置，而不是数值本身。,match函数的案例go,=match(11，a1:a9，0)返回的值是?=match(420,a3:e3,0)返回的值是?,vlookup与match函数的综合运用go,利用vlookup和match函数查找出相应月份和产品的销售额。,left/right：截取字符串函数,用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。语法：left(文本,返回的字符数)right(文本,返回的字符数)参数：返回的字符数必须大于或等于0。实例：如果a1=电脑爱好者，则left(a1，2)返回?;right(a1,3)返回?,left与right的综合运用go,如何利用left及right从身份证号中提取出生年份。,提示：假如a2存放身份证号，提取年份的做法：=right(left(a2,10),4),mid：截取字符串函数,用途：mid返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：mid(文本串,开始位置,返回字符的个数)参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。实例1：如果a1=东南(福建)汽车工业有限公司，则公式“=mid(a1，4，2)”返回“福建”。案例:利用mid从身份证号中提取出生年份。go,replace：替换指定位置处的任意文本,用途：replace使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。语法：replace(需替换其部分字符的文本，被替换字符的起始位置，替换的字符个数，用于替换到原文本中去的字符)。思考：excel本身就有替换功能，为什么要用replace函数？-解决替换字符多变的问题,replace的案例,案例一：请分别说出下列两个函数公式的返回值：go,案例二：如何用replace取出身份证号中的出生年月go,len：计算字符长度。,用途：len返回文本串的字符数。语法：len(text)。参数：text待要查找其长度的文本。注意：此函数用于双字节字符，且空格也将作为字符进行统计。实例：如果a1=电脑爱好者，则公式“=len(a1)”返回？,if、len及mid函数综合运用go,如果a列里cm后没有“00”的话，就把“00“加到cm后,但是如果cm后有”00“话，就不加了（产生的效果如b列所示），怎么做？,公式提示：=if(mid(a1,3,2)00,cm00average扩展函数：必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。当你发现你某些函数不可用时，请执行这一步骤。如：iseven函数的使用必须执行“工具加载宏分析工具库”方能使用,函数数组公式的输入方法,数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。如:=frequency(g2:g10,h2:h4)数组公式放在“”内部，按下ctrl+shift+enter组合键自动生成，不可用输入大括号的方式来完成go,函数参数中的单元格引用,根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用（如a2）、绝对引用（$a$2）和混合引用三种类型混合引用有“绝对列和相对行”（如$a3:$e3）；或是“绝对行和相对列”（如a$3:e$3）两种形式。前者不论公式怎么复制,列都不变。后者不论公式怎么复制，行号不变。绝对符号的添加:用f4放在公式中的单元格引用中进行切换几种单元格引用表示法：a2:b7（某一单元格区域）；1:1（第一行）；1:5(1至5行)；f:f（f列）；a：n（a到n列）,go,函数参数中的名称使用,为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用（如：average(销量)）。创建好的名称可以被所有工作表引用，而且引用时不需要在名称前面添加工作表名，因此名称引用实际上是一种绝对引用。,在这里，“销量”不是文本哟,函数参数中名称的命名方法,给一个单元格或区域命名的两个方法是：1、选中要命名的单元格或单元格区域，鼠标单击编辑栏顶端的“名称框”，在其中输入名称后回车。2、选中要命名的单元格或单元格区域，单击“插入名称定义”菜单命令，在打开的“定义名称”对话框中输入名称后确定即可。如果你要删除已经命名的区域，可以按第2种方法打开“定义名称”对话框，选中你要删除的名称删除即可。如果要查看所有的名称:“插入名称粘贴”菜单命令。名称使用演示：,go,函数参数中标志的使用方法,由于excel工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“b2:b46”区域存放着学生的物理成绩，而b1单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=average(物理)”。需要特别说明的是，公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。,go,rank,用途：返回一个数值在一组数值中的排位(如果数据清单已经排过序了，则数值的排位就是它当前的位置)。语法：rank(需要计算其排位的数字，存放要比较的数字的区域，排序方式)参数：排序方式为一数字，指明排位的方式。如果排序方式为0或省略，则按降序排列的数据清单进行排位。如果排序方式不为零，当作按升序排列的数据清单进行排位。注意：函数rank对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中，若整数60出现两次，其排位为5，则61的排位为7(没有排位为6的数值)。,rank的案例go,对汽车厂商8月份销量进行排名：,quartile,用途：返回一组数据的四分位点（值）。四分位数通常用于在考试成绩之类的数据集中对总体进行分组，如求出一组分数中前25%的分数。语法：quartile(array，quart)参数：array为需要求得四分位数值的数组或数字引用区域，quart决定返回哪一个四分位值。quart取0、1、2、3、4，则函数quartile分别返回最小值、第一个四分位数(第25个百分排位)、中分位数(第50个百分排位)、第三个四分位数(第75个百分排位)和最大数值。,quartile的案例go,请用quartile函数，统计左表中不同分位值（结果如右表）：,sign：返回数字的符号,用途：返回数字的符号。正数返回1，零返回0，负数返回-1。语法：sign(number)参数：number是需要返回符号的任意实数。实例：如果a1=65.25，则公式“=sign(a1)”返回1；=sign(6-12)返回-1；=sign(9-9)返回0。,sum：求和函数,用途：返回某一单元格区域中所有数字之和。语法：sum(number1，number2，.)。语法格式：number1，number2，.为1到30个需要求和的数值(包括逻辑值及文本表达式)、区域或引用。注意：参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1、文本被转换为数字。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。sum函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，sum函数括号中出现的分隔符（逗号）不能多于29个，否则excel就会提示参数太多。参数太多的突破方法：巧用括号法,average:求算术平均值,用途：计算所有参数的算术平均值。语法：average(number1，number2，.)。参数：number1、number2、.是要计算平均值的130个参数。实例：如果a1：a5区域命名为分数，其中的数值分别为100、70、92、47和82，则公式“=average(分数)”返回78.2。,subtotal：返回数据清单的分类汇总,用途：返回数据清单或数据库中的分类汇总。如果用户使用“数据”菜单中的“分类汇总”命令创建了分类汇总数据清单，即可编辑subtotal函数对其进行修改。语法：subtotal(函数名代表号，区域1，区域2)语法说明：函数名代表号为1到11之间的自然数，用来指定分类汇总计算使用的函数。常用的代表号为：1是average；3是counta；4是max；5是min；9是sum。区域1、区域2则是需要分类汇总的1到29个区域或引用。,and,用途：所有参数的逻辑值为真时返回true(真)；只要有一个参数的逻辑值为假，则返回false(假)。语法：and(逻辑表达式1，逻辑表达式2，)。参数：逻辑表达式最多可达30个，它们的结论或为true(1)或为false(0)。参数必须是逻辑值或者包含逻辑值的数组或引用。如果指定的单元格区域内包括非逻辑值，and将返回错误值#value！。实例：公式“=and(32,0)”返回false;公式“=and(32,86)”返回true,or,用途：所有参数中的任意一个逻辑值为真时即返回true(真)。语法：or(逻辑表达式1，逻辑表达式2，.)参数：同and的参数。实例：如果a1=6、a2=8，则公式“=or(a1+a2a2，a1=a2)”返回true；而公式“=or(a1a2，a1=a2)”返回false。,abs：求绝对值,用途：返回某一参数的绝对值。语法：abs(number)参数：number是需要计算其绝对值的一个实数。实例：如果a1=-16，则公式“=abs(a1)”返回16。,round：对数值进取四舍五入。,用途：按指定位数四舍五入某个数字。语法：round(需四舍五入的数字，保留的小数位)注意：如果保留的小数位大于0，则四舍五入到指定的小数位；如果等于0，则四舍五入到最接近的整数；如果小于0，则在小数点左侧按指定位数四舍五入。实例：如果a1=65.25，则公式“=round(a1，1)”返回65.3；=round(82.149，2)返回82.15；=round(21.5，-1)返回20；round(a1,0)返回65。,countblank,用途：计算某个单元格区域中空白单元格的数目。语法：countblank(range)参数：range为需要计算其中空白单元格数目的区域。,countblank的思考案例,1、在一个空白的excel表中：countblank(1:1)=?(相当于excel的列数)countblank(a:a)=?(相当于excel的行数)2、在一个非空白的excel表中：counta(a:a)+countblank(a:a)=?,int:无条件取整,用途：将任意实数向下取整为最接近的整数。语法：int(实数)参数：number为需要处理的任意一个实数。实例：如果a1=16.24、a2=-28.389，则公式“=int(a1)”返回16，=int(a2)返回-29。,trunc,用途：将数字的小数部分依需截去（不作四舍五入），返回实数。语法：trunc(需要截去小数部分的数字，保留小数的位数)注意：trunc函数可以按需要截取数字的小数部分，而int函数则将数字向下舍入到最接近的整数。int和trunc函数在处理负数时有所不同：trunc(-4.3)返回-4，而int(-4.3)返回-5。实例：如果a1=78.652，则公式“=trunc(a1，1)”返回78.6，=trunc(a1，2)返回78.65，=trunc(-8.963，2)返回8.96。,exact:比较两个字符串是否相同,用途：测试两个字符串是否完全相同。如果它们完全相同，则返回true；否则返回false。exact函数能区分大小写，但忽略格式上的差异。语法：exact(text1，text2)。参数：text1是待比较的第一个字符串，text2是待比较的第二个字符串。实例：如果a1=得利卡、a2=富利卡、a3=戈蓝，则公式“=exact(a1，a2)”返回false，=exact(a1，a3)返回false，=exact(word，word)返回true。,iseven,用途：测试参数的奇偶性，如果参数为偶数返回true，否则返回false。语法：iseven(number),number待测试的数值。如果参数值不是整数，则自动截去小数部分取整。注意：该函数必须加载“分析工具库”方能使用。如果参数number不是数值，iseven函数返回错误值#value!。实例：公式“=iseven(11)返回false”，=iseven(6)返回true。,iserror,用途：它们可以检验是否发生错误值。发生错误返回true，否则返回false。语法：iserror(value)参数：value是需要进行检验的参数。实例：用vlookup函数找不到相应的值时，显示空值。=if(isseror(vlookup(a2,a2:g5,2,0),vlookup(a2,a2:g5,2,0),concatenate,用途：将若干文字串合并到一个文字串中，其功能与&运算符相同。语法：concatenate(text1，text2，.)参数：text1，text2，.为1到30个将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。实例：如果a1=98、a2=千米，则公式“=concatenate(a1，a2)”返回“98千米”，与公式“=a1&a2”等价。,yearmonthday,用途：返回某日期的年份/月份/日期。语法：year/month