EXCEL函数应用.ppt

课程大纲课程大纲u课堂讲解篇:函数功能说明与案例讲解(共20个)u课后自学篇:基础函数功能说明(25个)EXCEL函数教学目的通过功能说明与案例演示,了解函数的应用范围与功效,激发后续学习函数的兴趣掌握函数使用的方法与技巧充分发挥Excel的强大数据处理功能，提升工作效率 函数应用初步演示n利用函数在excel中制作工资条 Gon利用函数将选定区域的偶数行全部做上颜色标记 GoEXCEL函数的结构Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式特殊公式。Excel 函数结构:也有一些函数是没有参数的,如ROW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔函数参数常用符号或表示方法n函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入 东南汽车或“东南汽车”n连接符: & 如：东南& 汽车的值为 东南汽车n空值/空格的表示法: n 空值：n 空格： n相关数学符号:（不等于）;=（大于等于）;82000,(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)GoGoSumif:条件求和函数用途：根据指定条件对若干单元格、区域或引用求和。语法：SUMIF(条件区域，条件，需求和的区域)参数：条件是由数字、逻辑表达式等组成的判定条件。Sumif案例 Go请统计人资组同仁的工资总额:Sumif(A:A, 人资组,C:C)Countif：条件计数函数。用途：计算区域中满足给定条件的单元格的个数。语法：COUNTIF(统计区域，条件)参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件” 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。Countif案例 Go以上述样表为据以上述样表为据, ,完成以下案例完成以下案例: :n例例1 1：依“师员”字段，统计师员级的人数 countif(C:C,师)；countif(C:C,员)n例例2 2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数 countif(I:I, =3)；countif(I:I, =3)- countif(I:I, =5)n例例3 3:检测到职编号是否有重复值 countif(A:A, A2)Counta用途：返回参数组中非空值的数目。利用函数COUNTA 可以计算数组或单元格区域中数据项的个数。语法：COUNTA(单元格区域1,单元格区域2) 说明：参数的个数为130 个。Counta的思考案例n例1：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于?。n例2：统计C列的非空白单元格的表示方法为：？; 若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为?Offsetn用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域（并不返回值）。n语法：OFFSET(作为偏移量参照系的引用位置，上下偏移的行数，左右偏移的列数，height，width)。n参数： 作为偏移量参照系的引用位置：它必须是单元格或相连单元格区域的引用； 上(下)偏移的行数：正数代表在起始引用下边;负数代表在起始引用的上边； 0代表没有上（下）偏移。 左(右)偏移的列数:正数代表在起始引用右边;负数代表在起始引用的左边；0代表没有左（右）偏移。 Height ：是要返回的引用区域的总行数（必须为正数） Width ：是要返回的引用区域的总列数（必须为正数）。Offset的思考题n问题一：OFFSET(B1,2,1,4,1)定位到哪几个单元格？ n问题二： 以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建立序号: GoMOD：取余用途：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(被除数，除数)参数：除数不能为零。Mod函数的案例n实例1：公式“=MOD(14，4)”返回?；“=MOD(-5，-2)”返回？。n实例2:如何利用MOD函数，将整张EXCEL表的偶数行都标上底色 GoVlookup返回表格或数组当前行中指定列处的数值。语法：VLOOKUP(索引值，数据区域，列序号，查找方式)参数： 索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。 数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。 列序号 指数据区域 中待返回的匹配值的列序号。它等于1 时，返回数据区域 第一列中的数值；它等于 2时，返回数据区域第二列中的数值，以此类推。 查找方式为一逻辑值,指明函数VLOOKUP 返回时是精确匹配还是近似匹配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值 的最大数值；如果为0，函数VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。Vlookup案例案例一:利用vlookup 抓取另一张表中的资料。 Go案例二(请参考链接之案例,课后自学)：用vlookup计算所得税,解决IF只能嵌套七层的局限。 Gomatch用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置(不是具体的单元格)。语法：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从身份证号中提取出生年份。 GoReplace：替换指定位置处的任意文本用途：REPLACE 使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。语法：REPLACE(需替换其部分字符的文本，被替换字符的起始位置，替换的字符个数，用于替换到原文本中去的字符) 。思考：EXCEL本身就有替换功能，为什么要用replace函数？-解决替换字符多变的问题Replace的案例案例一：请分别说出下列两个函数公式的返回值： Go案例二：如何用replace取出身份证号中的出生年月 GoLEN：计算字符长度。用途：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,LEN(A1)-2),A1)SUBSTITUTE：替换指定文本用途：在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定的文本，可以使用函数SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，就应当使用函数REPLACE。语法：SUBSTITUTE(需要替换其中字符的文本，需被替换的旧文本，用於替换的文本，第几次出现的旧文本)。参数： 第几次出现的旧文本为一数值，用来指定以新的文本替换第几次出现的旧文本；如果指定了该参数，则只有满足要求的旧文本被替换；否则将用新文本替换所有出现的旧文本。SUBSTITUTE、MID、LEN的综合运用案例 Go将左边的物料代码中无序空格,处理为右边的统一格式：Rept：补位函数用途：按照给定的次数重复显示文本。可以通过REPT 函数对单元格进行重复填充。语法：REPT(需重复显示的文本，重复显示的次数)。参数：重复显示的次数需为正数。注意：REPT 函数的结果不能多于255 个字符。实例：公式“=REPT(很好，2)”返回“很好很好”。Rept函数与len函数的综合运用GoIndex用途：返回表格或区域中的数值。语法：INDEX(单元格区域，行序号，列序号)返回数组中指定的单元格或单元格数组的数值。案例：题一：=INDEX（A1：C5，2，2）的返回值是？题二：=INDEX（A1：A5，5，1）的返回值是？GoMOD、COLUMN、ROW、index函数综合运用-打印工资条Go工资数据原表（存放在sheet1中）：希望能在另一个工作表中将上述工资表转换为以下样式：提示：=IF(MOD(ROW(),2)0,Sheet1!A$1,INDEX(Sheet1!$A:$N,ROW()/2+1,COLUMN()PERCENTRANK用途：返回某个数值在一个数据集合中的百分比排位，可用于查看数据在数据集中所处的位置。语法：PERCENTRANK(数据集合，需要在数值集合中得到排位的值）PERCENTRANK的案例案例一：计算出各汽车厂商相应销量的百分比排位 Go案例二：计算某一薪资的市场分位Go自学篇：函数基础知识及常用函数函数的种类-扩展函数的启用按照函数的来源，Excel 函数可以分为：n内置函数：只要启动了Excel，用户就可以使用它们。如：sum; averagen扩展函数：必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。当你发现你某些函数不可用时，请执行这一步骤。如：iseven函数的使用必须执行“工具加载宏分析工具库”方能使用函数数组公式的输入方法n数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。 如:=FREQUENCY(G2:G10,H2:H4)n数组公式放在“” 内部，按下Ctrl+Shift+Enter 组合键自动生成，不可用输入大括号的方式来完成 Go函数参数中的单元格引用n根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用（如A2）、绝对引用（$A$2）和混合引用三种类型n混合引用有“绝对列和相对行”（如$A3:$E3）；或是“绝对行和相对列”（如A$3:E$3）两种形式。前者不论公式怎么复制,列都不变。 后者不论公式怎么复制，行号不变。n绝对符号的添加: 用F4放在公式中的单元格引用中进行切换n几种单元格引用表示法： A2:B7（某一单元格区域）； 1:1（第一行）； 1:5(1至5行)； F:F（F列）；A：N（A到N列）Go函数参数中的名称使用为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用（如：average(销量)）。创建好的名称可以被所有工作表引用，而且引用时不需要在名称前面添加工作表名，因此名称引用实际上是一种绝对引用。在这里，“销量”不是文本哟函数参数中名称的命名方法n给一个单元格或区域命名的两个方法是： 1、选中要命名的单元格或单元格区域，鼠标单击编辑栏顶端的“名称框”，在其中输入名称后回车。 2、选中要命名的单元格或单元格区域，单击“插入名称定义”菜单命令，在打开的“定义名称”对话框中输入名称后确定即可。n如果你要删除已经命名的区域，可以按第2种方法打开“定义名称”对话框，选中你要删除的名称删除即可。n如果要查看所有的名称: “插入名称粘贴”菜单命令。 n名称使用演示： Go函数参数中标志的使用方法由于Excel 工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1 单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=AVERAGE(物理)”。需要特别说明的是，公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。GoRank用途：返回一个数值在一组数值中的排位(如果数据清单已经排过序了，则数值的排位就是它当前的位置)。语法：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)”返回TrueOR用途：所有参数中的任意一个逻辑值为真时即返回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。ISERRORn用途：它们可以检验是否发生错误值。发生错误返回TRUE ，否则返回FALSE。n语法：ISERROR(value) n参数：Value 是需要进行检验的参数。n实例：用VLOOKUP函数找不到相应的值时，显示空值。 =IF(ISSEROR(vlookup(A2,A2:G5,2,0), , vlookup(A2,A2:G5,2,0)CONCATENATEn用途：将若干文字串合并到一个文字串中，其功能与&运算符相同。n语法：CONCATENATE(text1，text2，.)n参数：Text1，text2，.为1 到30 个将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。n实例：如果A1=98、A2=千米，则公式“=CONCATENATE(A1，A2)”返回“98 千米”，与公式“=A1&A2”等价。YEARMONTHDAYn用途：返回某日期的年份/月份/日期。n语法：YEAR/MONTH/DAY(日期值)n实例： 假设A2单元格存放的日期为2008-8-20u 公式“=YEAR(A2)返回2008”u 公式“ =MONTH(A2)返回8”u 公式“ =DAY(A2)返回20”Date用途：返回代表特定日期的序列号。语法：DATE(year，month，day)参数：year 为一到四位,代表年份。Month 代表每年中月份的数字。Day 代表在该月份中第几天的数字。实例：将身份证中提取出的年月日直接转化成出生年月日: 假若A=350102197405253613,则 date(mid(a,7,4),mid(a,11,2),mid(a,13,2)返回1974-5-25Today用途：返回系统当前日期的序列号。参数：无语法：TODAY()实例1：公式“=TODAY()”返回执行公式时的系统时间。实例2：依出生日期计算年龄的示例 表示方法：（today()-出生日期）/365weekday用途：返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。语法：WEEKDAY(日期，返回类型)参数：“返回类型 ”为确定返回值类型的数字，数字1 或省略则1 至7 代表星期天到数星期六，数字2 则1 至7 代表星期一到星期天（推荐使用），数字3 则0 至6 代表星期一到星期天。实例：公式“=WEEKDAY(”2008/8/20“，2)”返回3(星期三)，=WEEKDAY(“2008/8/20”，3)返回2(星期三)。EDATE用途：返回指定日期之前或之后指定月份的日期序列号。语法：EDATE(开始日期，在开始日期之前或之后的月份数)参数：“在开始日期之前或之后的月份数” 未来日期用正数表示，过去日期用负数表示。实例：公式“=EDATE(2008/8/20，2)”返回39741 即2008-10-20FREQUENCYn用途：以一列垂直数组返回某个区域中数据的频率分布。它可以计算出在给定的值域和接收区间内，每个区间包含的数据个数。n语法：FREQUENCY(数据源，分段点)n注意该函数的特性： 1、忽略文本或空白单元格：数据源区域出现文本或空格，只统计数据，其他没有影响。 2、支持数据乱序：支持数据源及分段点的乱序。 3、对于每一分段点，统计小于等于此分段点，大于上一分段点的频数，最后一格始终统计大于最大分段点的频数。 4、要以数组形式输入公式。(在公式的编辑栏同时按下ctrl+alt+enter)FREQUENCY的案例 Go统计某科成绩不同分数段的人数：n思考：用FREQUENCY与用EXCEL原有的数据透视表功能有何不同？结束结束