《职业素养Excel函数教程.ppt》由会员分享，可在线阅读，更多相关《职业素养Excel函数教程.ppt（46页珍藏版）》请在三一办公上搜索。
1、生管管理科,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，
2、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:H
3、19=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=1 01
4、=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:A1
5、00，”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:A10
6、0，即使A1:A100，也只选择左上角值A1Table_array：搜索的区域/范围，是一个多维数组，如A1:D100之类Col_index_num：需要返回的值所在列，从搜索范围的起始列开始算Range_lookup：匹配方式，一般使用精确匹配，即0或FALSE,Vlookup单条件引用函数,Vlookup（lookup_value,table_array,col_index_num,range_lookup）,例子：1）Vlookup（D2，A1:C100，2，0）2）Vlookup（”848K 15293”，A1:C100，2，0）3）Vlookup（D2&E2，A1:C100，2，0）
7、,解释：1）根据D2单元格的值，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。2）根据”848K 15293”，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。3）根据D2与E2单元格合并后的值，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。,Vlookup单条件引用函数,Vlookup（lookup_value,table_array,col_index_num,range_lookup）,注意点:1）如果在查找区域table_array含有多个与lookup_value相等的值，即含有重复项，只能返回首个lookup_value值所对应的
8、结果2）如果table_array中没有与lookup_value相等的值，函数将返回错误值#N/A,CountIf统计符合条件的个数，单条件,统计区域,条件,函数解释：根据条件H2在统计区域E2：E11中，进行逐一比对（逻辑运算），最终汇总符合条件的个数,条件：A，A，A，A，A，A，A，A，A，A,统计区域：E，B，E，C，E，A，B，A，D，A,运算方式按条件,0，0，0，0，0，1，0，1，0，1,最后对结果进行求和，如下：SUM(0,0,0,0,0,1,0,1,0,1)=3,Click,CountIf统计符合条件的个数，单条件,CountIf（Range，Criteria）,Rang
9、e：统计区域，是一维数组，即A1:A100之类，而不能是A1:C100这样多列/行Criteria：条件值，是一个值，而不是区域/数组。可以是单元格，如D2，或者固定值，如”0”,例子：1）CountIf（A1:A100,D2）在A1:A100之中，统计等于D2单元格里数值的个数2）CountIf（A1:A100,”2”）在A1:A100之中，统计大于2的个数,CountIf统计符合条件的个数，单条件,CountIf（Range，Criteria）,利用两个CountIf函数相结合使用，可以处理一些简单的双条件求个数,例子：求在A1:A100之中，大于2且小于10的个数1）COUNTIF（A1
10、:A100，”2”）统计大于2的个数2）COUNTIF（A1:A100，”10”）统计大于10的个数3）COUNTIF(A1:A100,”2”)-COUNTIF(A1:A100,”10”)即结果：大于2的个数减去大于10的个数,/,CountIf统计符合条件的个数，单条件,CountIf（Range，Criteria）,函数解释：在单行/列的范围内（Range）统计符合条件（Criteria）的个数。主要用途：1）按条件统计个数。2）筛选有重复项的记录。,用它本身A2在A2:A8中进行比对，即得出A2在A2:A8中的个数，故只要是大于1的结果，都表示含有重复项。,Match查找符合条件的值所在
11、的行号/列号,例如，需查C列中的型番在A列中是否全部都包含有。,C2，C2，C2，C2，C2，C2，C2，C2，C2，C2，C2,A1，A2，A3，A4，A5，A6，A7，A8，A9，A10，A11,比对,0，0，0，0，1，0，0，0，0，0，0,返回第一个1的位置，即5,Match查找符合条件的值所在的行号/列号,Match（lookup_value，lookup_array，match_type）,Lookup_value：是值，非区域，例如A1，而非A1:A8。根据这个值，进行逐一比对。Lookup_array：是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，A1:A100
12、，或A1:G1，而不能A1:D10Match_type：匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值0表示。,函数解释：在lookup_array中，查找与lookup_value相符的值，并返回它所在的行/列数。1）如果lookup_array存在两个或以上的lookup_value值，函数只会返回首个lookup_value的所在行/列数。,Match查找符合条件的值所在的行号/列号,Match（lookup_value，lookup_array，match_type）,Lookup_value：是值，非区域，例如A1，而非A1:A8。根据这个值，进行逐一比对。Lookup_ar
13、ray：是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，A1:A100，或A1:G1，而不能A1:D10Match_type：匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值0表示。,函数解释：在lookup_array中，查找与lookup_value相符的值，并返回它所在的行/列数。,2）这里所指的行/列数，与EXCEL本身的行列不同，而是看lookup_array中的行列，比如A2:A10，虽然A5是处于EXCEL的第5行，但函数会返回4，因为它是从A2开始算起,Sumproduct多条件求个数、多条件求和,一、多条件求和Sumproduct（(条件1)(条件2)(求和
14、区域)）,条件1,条件2,求和区域,此时，如果用单条件求和函数SumIf则结果将会是1200+800+800=2800,条件一：A2:A8=E2 E2与A2:A8进行逐个比较，符合条件的返回1，不符合的返回0，条件二亦同样原理。,条件一返回结果（等于“台湾”的）：0，0，1，0，1，0，1,条件二返回结果（等于“57623”的）：1，1，0，0，0，0，1,Sumproduct多条件求个数、多条件求和,一、多条件求和Sumproduct（(条件1)(条件2)(求和区域)）,条件1,条件2,求和区域,条件一返回结果：0，0，1，0，1，0，1,条件二返回结果：1，1，0，0，0，0，1,相乘,相
15、乘的结果为：0，0，0，0，0，0，1,求和区域：1200,800,1500,250,520,400,800,0，0，0，0，0，0，800,|,Sumproduct多条件求个数、多条件求和,一、多条件求和Sumproduct（(条件1)(条件2)(求和区域)）,条件1,条件2,求和区域,0，0，0，0，0，0，800,条件1、条件2、求和区域相乘后的结果,最后求和：SUM（0，0，0，0，0，0，800）=800,Sumproduct多条件求个数、多条件求和,=Sumproduct(A2:A8=E2)(B2:B8=F2)(C2:C8),注意点：1）条件、求和区域，都是一维数组，必须是单行/列
16、，而不能是多行/列，比如A2:A8，而不能是A2:C82）条件、求和区域，必须同时是行或同时是列，不能一个是行，一个是列，比如A2:A8是行，A2:H2是列，则错误3）条件、求和区域的范围大小必须一样，比如条件一是A2:A8条件二不能是B1:B7，求和区域也一样道理，必须同时都是从第2行到第8行范围4）如果求和区域中包含有非数值型字符，则结果会返回#value5）任意个条件都可以，只需保证最后一个是求和区域就OK6）条件的书写位置不限制，既可以A2:A8=E2也可E2=A2:A8,Sumproduct多条件求个数、多条件求和,=Sumproduct(A2:A8=E2)(B2:B8=F2)(C2
17、:C8),7）条件不只是等于，还可以是大于、小于，等等8）条件间的相隔符（星号）可以理解为“并且”，既N个条件都符合的情况下，对求和区域进行累加。如上面例子，可解释为：统计出货地为”台湾”，并且部番是”802K 57623”的数量9）按此推理，也可以求“或者”的情况，即“统计802K 57623”出货“台湾”和”泰国”的总和，则函数式可变通为这样：Sumproduct(A2:A8=E2)+(B2:8=F2)*(C2:C8)=1600Sumproduct(条 件 一)+(条 件 二)*(求和区域)从以上可以看出，函数中用“”表示“或者”10）”或者”的逻辑运算次序要低于并且，请留意此点！,Sum
18、product多条件求个数、多条件求和,二、多条件求个数,Sumproduct（条件1）（条件2）,少了“求和区域”,条件1结果：0,1,0,1,1,0,1,1,0,1,条件2结果：0,1,0,0,1,1,1,0,1,0,相乘后结果：0,1,0,0,1,0,1,0,0,0,并且：同时为1时才是1，其它都是0或者：同时为0时才是0，其它都是1,最后求和：Sum(0,1,0,0,1,0,1,0,0,0)=3,Lookup另一种查找函数,LOOKUP(lookup_value,lookup_vector,result_vector),最常用途：对评分结果进行多级别的分类。在供应商评价、员工评价工作中
19、经常使用，较之一般的IF函数简洁明了，且效率高。,条件范围,函数运算时，检验目标值A2是否处在X的各个范围，是返回1，否返回0，形成一个数组,Lookup另一种查找函数,LOOKUP(lookup_value,lookup_vector,result_vector),条件范围,待返回的结果,1，0，0，0，0，0,F，E，D，C，B，A,45分对应的等级是F,Lookup另一种查找函数,LOOKUP(lookup_value,lookup_vector,result_vector),条件范围,待返回的结果,1）”条件范围”必须是从小到大2）”条件范围”与”待返回的结果”必须个数相等3）”条件范
20、围”最左端为范围下限，上限不限制，即如上例，最小值等于0，最大值无限制，可以是无限大,Lookup另一种查找函数,LOOKUP(lookup_value,lookup_vector,result_vector),条件范围,待返回的结果,4）”条件范围”从左至右，其逻辑关系如上图示，即只包含左端数值，如60 x70，即6070只包含60，而不包含70。5）”待返回的结果”是文本型，必须用双引号括起来。6）”条件范围”与”待返回的结果”也可以是变量，即用单元格代替，如Lookup(A2,B1:B6,C1:C6)，范围要相等，单行或单列,日期与时间函数,Date（年，月，日）,主要用途：将数值转化成
21、日期，如Date(2011,5,10)转换成日期2011/5/10，当日期需要作为变量处理时经常用到。单独使用较少，一般与其它函数嵌套使用，或利用此原理，作其它变通。,Day(日期)、Month(日期)、Year(日期)、Hour(时间)、Minute(时间)、Second(时间),主要用途：将数值转化成日期时间中的日、月、年、小时、分钟、秒，以作筛选汇总使用。比如，需要对出货记录进行按月汇总，则可以先将日期用Month函数提取出月数，再按月数进行分类汇总或数据透视。,Today()、Now(),主要用途：取得系统日期、日期时间，today只获取日期，now除了获取日期外，还包含有时间。,数学
22、函数,Subtotal(系数，区域),1）主要用途：对有筛选的数据区域进行求和、求个数、求最大值、求最小值、求平均值等。,筛选后，求和函数的差别,Sum函数在求和时，永远只能求整个区域的值，遇到有筛选时，缺陷就出现，此时，需要用Subtotal函数替代，才可避免求和错误。其它求个数、最大值、最小值等，同样道理。,数学函数,Subtotal(系数，区域),2）常用的系数如下：,一般使用该类,Int(数值)、Mod(数值，被除数),1）INT用于对数值进行不取舍的取整，如INT(1.25)=12）MOD用于求余数，如MOD(100,3)=1,数学函数,Round(数值，保留小数位数),1）该函数用
23、于四舍五入取值，效果优于下图设置：,右图的保留小数设置不能真正实现四舍五入，只是显示为整数，但参与运算时，仍然是原数值，故当数据量比较大时，误差也就随之增大。,Roundup(数值，保留小数位数),该函数用于向上舍入取整，比如，2.1取整时为3，即凡有小数，都向上约进，一般用于包材核算较多，不足1箱的，约进1箱，Roundup（2.31，0）表示向上取整,统计函数,Counta（区域）、CountBlank（区域）,1）COUNTA用于计算区域内非空单元格的个数;COUNTBLAN用于计算区域内空值单元格的个数2）空值单元格不等于含空格或零值的单元格，它是指单元格内字符长度为零的一个空值，函数
24、中常用两个双引号表示，如”，另外可用LEN函数检测其与空格单元格的差别,Max(区域)、Min(区域),求区域内的最大值（MAX）和最小值（MIN），文本型的数值将不参与运算,统计函数,Large（区域，排位）-第几大,1）此函数用于计算所属区域中，从大到小排行第几位的数值，比如，Large（A1:A100,5）意思是，在A1:A100范围内，求得第5大的数值，利用其它函数（如ROW和COLUMN等函数或单元格值），将”排位”替换成变量，可以快速地从一组数据中提取从大到小的排列顺序2）不允许存在并列第几的状态，如果有相同的数值，将顺位排序，如100,90,90,85,82取第二大数值时是90，
25、第三大是90，第四大是85.,Small（区域，排位）-第几小,原理与LARGE相同，参照如上。,其它查找与引用函数,ROW、COLUMN,1）直接=ROW()用于取得当前单元格的行数，进行拖拉时，可以取得与EXCEL行数相对应的递增或递减的数值。2）添加参数，如=ROW(单元格)，如=ROW(A1)可返回该单元格的行号，如=ROW(B2)返回2第2行，作用同上。3）将一些函数中固定系数、逻辑值按需要替换成变量，可利用这两个函数，以方便拖拉，而不须逐个更改函数中的固定系数值，提高效率。,参数值可变量代替，可以实现一次性拖拉得出，节省时间,其它查找与引用函数,INDIRECT(用字符表示的引用区
26、域),1）在函数中引用位置可以变成变量形式，INDIRECT（”A1”）即等于单元格A1的值,注意两者形式比较,B2相当于”Sheet2”,在统计年、月度报表（日报表分散成一个一个工作表时）时用处较大，可巧妙地运用此函数，快速地汇总所需数据。,2）此函数属易失函数，即在引用另一个工作薄数据时，必须在两个EXCEL同时打开的情况下，才有效。3）我们在工作表中直接引用，即=A1之类，在删除单元格时，引用会出错（#REF），如果改用=INDIRECT(A1)则不管如何删除，它都能保证引用返回A1的值。,文本函数,FIND(单个字符，字符串，起始位置),函数说明：=FIND(“A”,”BDFCAFDA
27、D”,2)在字符串中，从第2位开始，查找”A”首次出现的位置，即返回5，如不包含，则返回错误值#VALUE!，如果省略”起始位置”，则默认为从第1位开始查找。,LEN(单元格或字符),函数说明：=LEN(A1)，计算A1单元格中字符串的长度，不能用区域，如LEN(A1:A10)，也只返回最左上角A1单元格的长度值,VALUE(单元格或字符),函数说明：=VALUE(A1)，将A1单元格中文本型的数字转换成数值型，以便参与函数公式的运算。相当于=-A1，即在A1前面添加两个减号，含“负负得正”的意思。另一种方法是用数值性粘贴方法，将A1单元格乘于1，即转换成数值型。,文本函数,LOWER、UPP
28、ER、TRIM、REPT,1）LOWER、UPPER用于将小/大写字母转换成大/小写字母，如下图:,2）TRIM函数用于删除字符前后的空格，字符中间的空格无法删除，此函数常用于整理手工输入时误输空格符的情况3）REPT(字符，重复个数)函数用于产生N个重复的字符，当函数中需要用到多个重复字符时，用这个函数减少函数体积4）TEXT(字符，格式类型)它相当于单元格格式设置中的自定义设置，一般在嵌套在其它函数中使用，如下图：,使资料规范统一,两种方式的效果对比,文本函数,SUBSTITUTE 字符替换函数,1）Substitute(需要替换掉的字符，字符串，需要替换成的字符)如Substitute(
29、“a”,”AFGERHAaFe”,”中”)意思是将字符串中A字符全部替换成”中”，返回结果是“中FGERH中中Fe”，故它是不区分大小写的。2）经常用途，需要去掉部番中间的空格时（848K 15293）如果单纯用查找替换方式，容易将一些059E开头的部番变成科学记数法，而成为乱码，影响数据准确性，此时，可先用本函数去除空格，再数值性粘贴到文本区域。3）替换空格时，可以简写成这样：SUBSTITUTE（“”，A1，）即将A1中的空格清除,空格字符,空值字符，可以不填，或用”表示,文本函数,LEFT、RIGHT、MID,1）Left(字符串，位数N)截取字符串左边的N个字符，用法示例：Left(“
30、中华人民共和国”,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，返回
31、“合格”，大于90则返回“优秀”，依此类推，共可以嵌套七层，也就是说只可以区分七个等级，超过则会出错，同时，如果函数判断层级达到4-5个以上，函数式会变得很长，极不利于检查核对，故此时需要考虑改用LOOKUP函数来替代3）使用此函数时，逻辑关系务必100%严密，否则将返回不到正确的结果,逻辑、信息函数,ANDOR函数,AND（条件1，条件2，条件3）,1）AND表示多个条件并列，如AND(A12,B160,A12,B12或B13时条件成立，返回TRUE，用OR时，所有条件只要有一个符合，就返回TRUE，IF(OR(A1=2,A1=3),”良好”,”其它等级”)即A1在等于2或3时都返回“良好”,OR（条件1，条件2，条件3）,逻辑、信息函数,Iserror、Isna函数,用于判断错误值，ISNA只判断#NA型的错误值，ISERROR可判断所有错误值，一般结果其它函数使用，如下：,无法计算合计数,
