excel2007函数应用(1)

《excel2007函数应用(1)》由会员分享，可在线阅读，更多相关《excel2007函数应用(1)（80页珍藏版）》请在金锄头文库上搜索。

1、EXCEL函数应用,管理部人力资源组 2008年9月25日,课程大纲,课堂讲解篇:函数功能说明与案例讲解(共20个) 课后自学篇:基础函数功能说明(25个),EXCEL函数教学目的,通过功能说明与案例演示,了解函数的应用范围与功效,激发后续学习函数的兴趣 掌握函数使用的方法与技巧 充分发挥Excel的强大数据处理功能，提升工作效率,函数应用初步演示,利用函数在excel中制作工资条 Go 利用函数将选定区域的偶数行全部做上颜色标记 Go,EXCEL函数的结构,Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。,Excel 函数结构:,也有一些函数是没有参数的,如ROW(),

2、左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,函数参数常用符号或表示方法,函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入 东南汽车或“东南汽车” 连接符: A2:B7； 1:1； 1:5； F:F；A：N,Go,函数列表,VALUE,用途：将表示数字的文字串转换成数字。 语法：VALUE(text)。 参数：Text 为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是Excel 可以识别的任意常数、日期或时间格式。如果Text 不属于上述格式，则VALUE 函数返回错误值#VALUE!。,Value演示Go,文本型态的数字不可计算,通过valu

3、e函数转换就可以计算了,If:逻辑函数,功能：IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果。它的应用很广泛，可以使用函数 IF 对数值和公式进行条件检测。 语法格式： IF(条件,A,B)。其中“条件”表示计算结果为 TRUE 或 FALSE 的任意值或表达式，若为常量，视为True。条件参数可使用任何比较运算符。条件返回的结果为真的话，则执行A，否则执行B。 IF函数仅可以嵌套七层，超过将会出错。,IF 函数的简单案例一 Go,性别为男者,称谓为XX先生;性别为女者,称谓为XX女士,案例二:依下述税率表,利用 if函数计算个人所得税 (课后练习),注意：因IF仅可嵌套七层

4、，所以无法计算月薪超过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:条件求和函数,

5、用途：根据指定条件对若干单元格、区域或引用求和。 语法：SUMIF(条件区域，条件，需求和的区域) 参数：条件是由数字、逻辑表达式等组成的判定条件。,Sumif案例 Go,请统计人资组同仁的工资总额: Sumif(A:A, 人资组,C:C),Countif：条件计数函数。,用途：计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(统计区域，条件) 参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件” 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。,Countif案例 Go,以上述样表为据,完成以下案例: 例1：依“师员”字段，统计师员级的人

6、数 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(

7、A1:A7)”的计算结果等于?。 例2：统计C列的非空白单元格的表示方法为：？; 若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为?,Offset,用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域（并不返回值）。 语法：OFFSET(作为偏移量参照系的引用位置，上下偏移的行数，左右偏移的列数，height，width)。 参数： 作为偏移量参照系的引用位置：它必须是单元格或相连单元格区域的引用； 上(下)偏移的行数：正数代表在起始引用下边;负数代表在起始引用的上边； 0代表没有上（下）偏移。 左(右)偏移的列数:正数代表在起始引用右边;

8、负数代表在起始引用的左边；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

9、,COUNTA(C:C)-1,1) 注：以上公式只能正确计算不间断的连续数据,如果表格中销量的数据有空白单元格,那么动态名称的引用位置将发生错误,COLUMN,用途：返回给定引用的列标。 语法：COLUMN(单元格引用)。 参数：“单元格引用”为需要得到其列标的单元格，如果省略，则假定函数COLUMN 是对所在单元格的引用。 实例：公式“=COLUMN(A3)”返回1，=COLUMN(D5)返回?。,Row,用途：返回给定引用的行号。 语法：ROW(单元格引用)。“单元格引用”为需要得到其行号的单元格或单元格区域。 实例:利用row建立序号: Go,MOD：取余,用途：返回两数相除的余数，其结

10、果的正负号与除数相同。 语法：MOD(被除数，除数) 参数：除数不能为零。,Mod函数的案例,实例1：公式“=MOD(14，4)”返回?；“=MOD(-5，-2)”返回？。 实例2:如何利用MOD函数，将整张EXCEL表的偶数行都标上底色 Go,Vlookup,返回表格或数组当前行中指定列处的数值。 语法：VLOOKUP(索引值，数据区域，列序号，查找方式) 参数： 索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。 数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。 列序号 指数据区域 中待返回的匹配值的列序号。它等于1 时，返回数据区域 第一列中的数值

11、；它等于 2时，返回数据区域第二列中的数值，以此类推。 查找方式为一逻辑值,指明函数VLOOKUP 返回时是精确匹配还是近似匹配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值 的最大数值；如果为0，函数VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。,Vlookup案例,案例一:利用vlookup 抓取另一张表中的资料。 Go 案例二(请参考链接之案例,课后自学)：用vlookup计算所得税,解决IF只能嵌套七层的局限。 Go,match,用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置(不是具体的单元格)。 语法：MATCH

12、(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：截取字符串函数,用途：根据指定的字符数返回文本

13、串中的第一个或前几个字符。此函数用于双字节字符。 语法：LEFT(文本, 返回的字符数) Right(文本, 返回的字符数) 参数：返回的字符数必须大于或等于0。 实例：如果A1=电脑爱好者，则LEFT(A1，2)返回? ;Right(A1,3)返回?,Left与right的综合运用 Go,如何利用Left及Right从身份证号中提取出生年份。,提示：假如A2存放身份证号，提取年份的做法：=RIGHT(LEFT(A2,10),4),Mid：截取字符串函数,用途：MID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。 语法：MID(文本串,开始位置,返回字符的个数) 参数：开始位

14、置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。 实例1：如果a1=东南(福建)汽车工业有限公司，则公式“=MID(A1，4，2)”返回“福建” 。 案例:利用MID从身份证号中提取出生年份。 Go,Replace：替换指定位置处的任意文本,用途：REPLACE 使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。 语法：REPLACE(需替换其部分字符的文本，被替换字符的起始位置，替换的字符个数，用于替换到原文本中去的字符) 。 思考：EXCEL本身就有替换功能，为什么要用replace函数？-解决替换字符多变的问题,Replace的案例,案例一：请分

15、别说出下列两个函数公式的返回值： 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“,“CM00“&MID(A1,3

16、,LEN(A1)-2),A1),SUBSTITUTE：替换指定文本,用途：在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定的文本，可以使用函数SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，就应当使用函数REPLACE。 语法：SUBSTITUTE(需要替换其中字符的文本，需被替换的旧文本，用於替换的文本，第几次出现的旧文本)。 参数： 第几次出现的旧文本为一数值，用来指定以新的文本替换第几次出现的旧文本；如果指定了该参数，则只有满足要求的旧文本被替换；否则将用新文本替换所有出现的旧文本。,SUBSTITUTE、MID、LEN的综合运用案例 Go,将左边的物料代码中无序空格,处理为右边的统一格式：,Rept：补位函数,用途：按照给定的次数重复显示文本。可以通过REPT 函数对单元格进行重复填充。 语法：REPT(需重复显示的文本，重复显示的次数)。 参数：重复显示的次数需为正数。 注意：REPT 函数的结果不能多于255 个字符。 实例：公式“=REPT(很好，2