《Excel函数教程》PPT课件

欧派财务部,Excel函数教程,作成：杨春梅,欧派财务部,一，数组介绍二，SUMIF函数三，VLOOKUP函数四，COUNTIF函数五，MATCH函数六，SUMPRODUCT函数七，LOOKUP函数八，日期与时间函数九，数学函数十，统计函数十一，查找与引用函数十二，文本函数十三，逻辑、信息函数,01,05,09,14,18,21,27,31,32,35,37,39,43,生产管理科,复杂函数的运算原理数组运算,运算类型：1）四则运算：、2）逻辑运算：、,生产管理科,以数组的角度理解函数应用,理解最基本原理，学会举一反三,授人于鱼，不如授人于渔,数组的运算原理,1，1，0，0，1，0，0，0，1，0，1,0，1，1，0，1，1，0，0，1，1，1,=,?,0,1,0,0,1,0,0,0,1,0,1,1，1，0，0，1，0，0，0，1，0，1,0，1，1，0，1，1，0，0，1，1，1,0，1，0，0，1，0，0，0，1，0，1,1）相运算的数组里数字的个数必须相等，如例子，数组1与数组2均为11个。2）运算时，数组中的数字，按顺序分别进行计算，不会交叉或错乱。3）数组进行求和，SUM(0,1,0,0,1,0,0,0,1,0,1)=0+1+0+0+1+0+0+0+1+0+1=4,相当于：SUM(0,1,0,0,1,0,0,0,1,0,1),运算方法如下,SumIF单条件求和函数,H7:H19=E,E,D,E,E,A,C,B,C,A,D,A,B,M7=A，在函数中，自动转换成数组形式，即A,A,A,A,A,A,A,A,A,A,A,A,A,E,E,D,E,E,A,C,B,C,A,D,A,B,A,A,A,A,A,A,A,A,A,A,A,A,A,|,0,0,0,0,0,1,0,0,0,1,0,1,0,1）按次序逐对进行比较。2）相符（TRUE）为1，不相符（FALSE）为0。,第步得出的结果，再进行下一步运算,需要求A组的段取时间累计，怎么做呢？,SumIF单条件求和函数,结果,0,0,0,0,0,0.50,0,0,0,0.50,0,0.33,0,最后求和得：1.33,11=101=0,SumIF单条件求和函数,SumIf（Range，Criteria，Sum_Range）,Range：判断区域，一维数组Criteria：条件，可以是固定值Sum_Range：求和区域，一维数组,运算步骤及注意点：1）Range与Criteria进行逻辑运算，得出结果2）结果与Sum_Range进行相乘运算3）Range与Sum_Range的范围大小必须一致,SumIF单条件求和函数,SumIf（Range，Criteria，Sum_Range）,例子：1）SUMIF（A1:A100，D2，B1:B100）2）SUMIF（A1:A100，”A”，B1:B100）3）SUMIF（A1:A100，”3”，B1:B100）,解释：1）汇总等于D2单元格的所有值2）汇总等于”A”的所有值3）汇总大于3的所有值,Vlookup单条件引用函数,二维数组,逐一按顺序进行比较,|,0，0，0，0，1，0,结果,Vlookup单条件引用函数,二维数组,第步结果0，0，0，0，1，0,与第2列比较B1,B2,B3,B4,B5,B6,B7,返回1对应的数据,B6=Z-0,Vlookup单条件引用函数,Vlookup（lookup_value,table_array,col_index_num,range_lookup）,Lookup_value：条件值，是值，非区域，比如A1，而非A1:A100，即使A1:A100，也只选择左上角值A1Table_array：搜索的区域/范围，是一个多维数组，如A1:D100之类Col_index_num：需要返回的值所在列，从搜索范围的起始列开始算Range_lookup：匹配方式，一般使用精确匹配，即0或FALSE,I:10-15成品库报表.xls,Vlookup单条件引用函数,Vlookup（lookup_value,table_array,col_index_num,range_lookup）,例子：1）Vlookup（D2，A1:C100，2，0）2）Vlookup（”848K15293”，A1:C100，2，0）3）Vlookup（D2COUNTBLAN用于计算区域内空值单元格的个数2）空值单元格不等于含空格或零值的单元格，它是指单元格内字符长度为零的一个空值，函数中常用两个双引号表示，如”，另外可用LEN函数检测其与空格单元格的差别,Max(区域)、Min(区域),求区域内的最大值（MAX）和最小值（MIN），文本型的数值将不参与运算,统计函数,Large（区域，排位）-第几大,1）此函数用于计算所属区域中，从大到小排行第几位的数值，比如，Large（A1:A100,5）意思是，在A1:A100范围内，求得第5大的数值，利用其它函数（如ROW和COLUMN等函数或单元格值），将”排位”替换成变量，可以快速地从一组数据中提取从大到小的排列顺序2）不允许存在并列第几的状态，如果有相同的数值，将顺位排序，如100,90,90,85,82取第二大数值时是90，第三大是90，第四大是85.,Small（区域，排位）-第几小,原理与LARGE相同，参照如上。,其它查找与引用函数,ROW、COLUMN,1）直接=ROW()用于取得当前单元格的行数，进行拖拉时，可以取得与EXCEL行数相对应的递增或递减的数值。2）添加参数，如=ROW(单元格)，如=ROW(A1)可返回该单元格的行号，如=ROW(B2)返回2第2行，作用同上。3）将一些函数中固定系数、逻辑值按需要替换成变量，可利用这两个函数，以方便拖拉，而不须逐个更改函数中的固定系数值，提高效率。,参数值可变量代替，可以实现一次性拖拉得出，节省时间,其它查找与引用函数,INDIRECT(用字符表示的引用区域),1）在函数中引用位置可以变成变量形式，INDIRECT（”A1”）即等于单元格A1的值,注意两者形式比较,B2相当于”Sheet2”,在统计年、月度报表（日报表分散成一个一个工作表时）时用处较大，可巧妙地运用此函数，快速地汇总所需数据。,2）此函数属易失函数，即在引用另一个工作薄数据时，必须在两个EXCEL同时打开的情况下，才有效。3）我们在工作表中直接引用，即=A1之类，在删除单元格时，引用会出错（#REF），如果改用=INDIRECT(A1)则不管如何删除，它都能保证引用返回A1的值。,文本函数,FIND(单个字符，字符串，起始位置),函数说明：=FIND(“A”,”BDFCAFDAD”,2)在字符串中，从第2位开始，查找”A”首次出现的位置，即返回5，如不包含，则返回错误值#VALUE!，如果省略”起始位置”，则默认为从第1位开始查找。,LEN(单元格或字符),函数说明：=LEN(A1)，计算A1单元格中字符串的长度，不能用区域，如LEN(A1:A10)，也只返回最左上角A1单元格的长度值,VALUE(单元格或字符),函数说明：=VALUE(A1)，将A1单元格中文本型的数字转换成数值型，以便参与函数公式的运算。相当于=-A1，即在A1前面添加两个减号，含“负负得正”的意思。另一种方法是用数值性粘贴方法，将A1单元格乘于1，即转换成数值型。,文本函数,LOWER、UPPER、TRIM、REPT,1）LOWER、UPPER用于将小/大写字母转换成大/小写字母，如下图:,2）TRIM函数用于删除字符前后的空格，字符中间的空格无法删除，此函数常用于整理手工输入时误输空格符的情况3）REPT(字符，重复个数)函数用于产生N个重复的字符，当函数中需要用到多个重复字符时，用这个函数减少函数体积4）TEXT(字符，格式类型)它相当于单元格格式设置中的自定义设置，一般在嵌套在其它函数中使用，如下图：,使资料规范统一,两种方式的效果对比,文本函数,SUBSTITUTE字符替换函数,1）Substitute(需要替换掉的字符，字符串，需要替换成的字符)如Substitute(“a”,”AFGERHAaFe”,”中”)意思是将字符串中A字符全部替换成”中”，返回结果是“中FGERH中中Fe”，故它是不区分大小写的。2）经常用途，需要去掉部番中间的空格时（848K15293）如果单纯用查找替换方式，容易将一些059E开头的部番变成科学记数法，而成为乱码，影响数据准确性，此时，可先用本函数去除空格，再数值性粘贴到文本区域。3）替换空格时，可以简写成这样：SUBSTITUTE（“”，A1，）即将A1中的空格清除,空格字符,空值字符，可以不填，或用”表示,文本函数,LEFT、RIGHT、MID,1）Left(字符串，位数N)截取字符串左边的N个字符，用法示例：Left(“中华人民共和国”,3)=“中华人”2）Right(字符串，位数N)截取字符串右边的N个字符，用法示例：Right(“中华人民共和国”,3)=“共和国”3）Mid(字符串，起始位置Y，位数N)从字符串的第Y位开始，截取N位字符，用法示例：MID(“中华人民共和国”，2，3)=“人民共”,逻辑、信息函数,IF函数,IF（条件，条件成立时返回结果，条件不成立时返回结果）,1）IF（A160，“合格”，“不合格”）即判断A1单元格的值，如果从于60，则返回“合格”，反之，则返回“不合格”2）嵌套使用，如IF(A190,”优秀”,”合格”)意思为如果A1小于60则返回“不合格”，大于60但小于90，返回“合格”，大于90则返回“优秀”，依此类推，共可以嵌套七层，也就是说只可以区分七个等级，超过则会出错，同时，如果函数判断层级达到4-5个以上，函数式会变得很长，极不利于检查核对，故此时需要考虑改用LOOKUP函数来替代3）使用此函数时，逻辑关系务必100%严密，否则将返回不到正确的结果,逻辑、信息函数,ANDOR函数,AND（条件1，条件2，条件3）,1）AND表示多