Excel自定义公式

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2014/10/15,#,Excel,自定义公式,李晓平,数据计算,（,1,）自定义公式方式,（,2,）函数方式,什么是公式,Excel,中的公式是一种对工作表中的数值进行计算的等式，它可以帮助用户快速的完成各种复杂的运算。公式以,“,=”,开始，其后是公式的表达式，如“,=A1+A2”,例如,:,公式,”,=(A2+67)/SUM(B2:F2),”,单元格引用,=(A2+67)/SUM(B2:F2),除法运算符,函数,加法运算符,常量,单元格区域引用,公式通常由,运算符,和参与运算的,操作数,组成。,运算符,算术运算符,含义,示例,+,（加号）,加法,3+3,（减号）,减法,负数,3-1-1,*,（星号）,乘法,3*3,/,（正斜杠）,除法,3/3,%,（百分号）,百分比,20%,（脱字号）,乘方,32,比较运算符,含义,示例,=,（等号）,等于,A1=B1,（大于号）,大于,A1B1,（小于号）,小于,A1=,（大于等于号）,大于等于,A1=B1,=,（小于等于号）,小于等于,A1=,（大于等于）,;=3),；,countif(I:I,=3)-countif(I:I,=5),例,3,:,检测到职编号是否有重复值,countif(A:A,A2),Counta,用途：返回参数组中,非空值的数目,。利用函数,COUNTA,可以计算数组或单元格区域中数据项的个数。,语法：,COUNTA(,单元格区域,1,单元格区域,2),说明：参数的个数为,1,30,个。,Counta,的思考案例,例,1,：如果,A1=6.28,、,A2=3.74,，其余单元格为空，则公式“,=COUNTA(A1:A7)”,的计算结果等于,?,。,例,2,：统计,C,列的非空白单元格的表示方法为：,？,;,若整张,EXCEL,表的每个单元格都有数据,前述函数公式的结果为,?,MOD,：取余,用途：返回两数相除的余数，其结果的正负号与除数相同。,语法：,MOD(,被除数，除数,),参数：除数不能为零。,Mod,函数的案例,实例,1,：公式“,=MOD(14,，,4)”,返回,?,；“,=MOD(-5,，,-2)”,返回,？,。,实例,2:,如何利用,MOD,函数，将整张,EXCEL,表的偶数行都标上底色,Vlookup,返回表格或数组当前行中指定列处的数值。,语法：,VLOOKUP(,索引值，数据区域，列序号，查找方式,),参数：,索引值,为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。,数据区域,为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。,列序号,指数据区域 中待返回的匹配值的列序号。它等于,1,时，返回数据区域 第一列中的数值；它等于,2,时，返回数据区域第二列中的数值，以此类推。,查找方式,为一逻辑值,指明函数,VLOOKUP,返回时是精确匹配还是近似匹配。如果为,1,或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回,小于索引值 的最大数值,；如果为,0,，函数,VLOOKUP,将返回精确匹配值。,如果找不到，则返回错误值,#N/A,。,字符函数,Left/Right,：截取字符串函数,用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。,语法：,LEFT(,文本,返回的字符数,),Right(,文本,返回的字符数,),参数：返回的字符数必须大于或等于,0,。,实例：如果,A1=,电脑爱好者，则,LEFT(A1,，,2),返回,?,;Right(A1,3),返回,?,Left,与,right,的综合运用,如何利用,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,的案例,案例一：请分别说出下列两个函数公式的返回,值,案例二：如何用,replace,取出身份证号中的出生,年月,LEN,：计算字符长度。,用途：,LEN,返回文本串的字符数。,语法：,LEN(text),。,参数：,Text,待要查找其长度的文本。,注意：此函数用于双字节字符，且空格也将作为字符进行统计。,实例：如果,A1=,电脑爱好者，则公式“,=LEN(A1)”,返回,？,If,、,Len,及,mid,函数综合,运用,如果,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,的综合,运用案例,将左边的物料代码中无序空格,处理为右边的统一格式：,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,。,INT:,无条件取整,用途：将任意实数,向下取整,为最接近的,整数,。,语法：,INT(,实数,),参数：,Number,为需要处理的任意一个实数。,实例：如果,A1=16.24,、,A2=-28.389,，则公式“,=INT(A1)”,返回,16,，,=INT(A2),返回,-29,。,YEARMONTHDAY,用途：返回某日期的年份,/,月份,/,日期。,语法：,YEAR/MONTH/DAY(,日期值,),实例：,假设,A2,单元格存放的日期为,2008-8-20,公式“,=YEAR(A2),返回,2008”,公式“,=MONTH(A2),返回,8”,公式“,=DAY(A2),返回,20”,常规函数,Sum,Average,Max,Min,Count,Counta,If,