职业素养-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=1 01=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，也只选择左上角值A1 Table_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）,解释： 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值所对应的结果 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）,Range：统计区域，是一维数组，即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: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查找符合条件的值所在的行号/列号,例如，需查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，或A1:G1，而不能A1:D10 Match_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_array：是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，A1:A100，或A1:G1，而不能A1:D10 Match_type：匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值0表示。,函数解释： 在lookup_array中，查找与lookup_value相符的值，并返回它所在的行/列数。,2）这里所指的行/列数，与EXCEL本身的行列不同，而是看lookup_array中的行列，比如A2:A10，虽然A5是处于EXCEL的第5行，但函数会返回4，因为它是从A2开始算起,Sumproduct多条件求个数、多条件求和,一、多条件求和 Sumproduct（(条件1)(条件2)(求和区域)）,条件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,相乘,相乘的结果为：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）条件、求和区域，都是一维数组，必须是单行/列，而不能是多行/列，比如A2:A8，而不能是A2:C8 2）条件、求和区域，必须同时是行或同时是列，不能一个是行，一个是列，比如A2:A8是行，A2:H2是列，则错误 3）条件、求和区域的范围大小必须一样，比如条件一是A2:A8 条件二不能是B1:B7，求和区域也一样道理，必须同时都是从第2行到第8行范围 4）如果求和区域中包含有非数值型字符，则结果会返回#value 5）任意个条件都可以，只需保证最后一个是求和区域就OK 6）条件的书写位置不限制，既可以A2:A8=E2也可E2=A2:A8,Sumproduct多条件求个数、多条件求和,=Sumproduct(A2:A8=E2)(B2:B8=F2)(C2:C8),7）条件不只是等于，还可以是大于、小于，等等 8）条件间的相隔符（星号）可以理解为“并且”，既N个条件都符合的情况下，对求和区域进行累加。如上面例子，可解释为：统计出货地为”台湾”，并且部番是”802K 57623”的数量 9）按此推理，也可以求“或者”的情况，即“统计802K 57623”出货“台湾”和”泰国”的总和，则函数式可变通为这样： Sumproduct(A2:A8=E2)+(B2:8=F2)*(C2:C8)=1600 Sumproduct(条 件 一)+(条 件 二)*(求和区域) 从以上可以看出，函数中用“”表示“或者” 10）”或者”的逻辑运算次序要低于并且，请留意此点！,Sumproduct多条件求个数、多条件求和,二、多条件求个数,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),最常用途：对评分结果进行多级别的分类。在供应商评价、员工评价工作中经常使用，较之一般的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）”条件范围”最左端为范围下限，上限不限制，即如上例，最小值等于0，最大值无限制，可以是无限大,Lookup另一种查找函数,LOOKUP(lookup_value,lookup_vector,result_vector),条件范围,待返回的结果,4）”条件范围”从左至右，其逻辑关系如上图示，即只包含左端数值，如60x70，即6070只包含60，而不包含70。 5）”待返回的结果”是文本型，必须用双引号括起来。 6）”条件范围”与”待返回的结果”也可以是变量，即用单元格代替，如Lookup(A2,B1:B6,C1:C6)，范围要相等，单行或单列,日期与时间函数, Date（年，月，日）,主要用途：将数值转化成日期，如Date(2011,5,10)转换成日期2011/5/10，当日期需要作为变量处理时经常用到。单独使用较少，一般与其它函数嵌套使用，或利用此原理，作其它变通。, Day(日期)、Month(日期)、Year(日期)、Hour(时间)、Minute(时间)、Second(时间),主要用途：将数值转化成日期时间中的日、月、年、小时、分钟、秒，以作筛选汇总使用。比如，需要对出货记录进行按月汇总，则可以先将日期用Month函数提取出月数，再按月数进行分类汇总或数据透视。, Today()、Now(),主要用途：取得系统日期、日期时间，today只获取日期，now除了获取日期外，还包含有时间。,数学函数, Subtotal(系数，区域),1）主要用途：对有筛选的数据区域进行求和、求个数、求最大值、求最小值、求平均值等。,筛选后，求和函数的差别,Sum函数在求和时，永远只能求整个区域的值，遇到有筛选时，缺陷就出现，此时，需要用Subtotal函数替代，才可避免求和错误。其它求个数、最大值、最小值等，同样道理。,数学函数, Subtotal(系数，区域),2）常用的系数如下：,一般使用该类, Int(数值)、Mod(数值，被除数),1）INT用于对数值进行不取舍的取整，如INT(1.25)=1 2）MOD用于求余数，如MOD(100,3)=1,数学函数, Round(数值，保留小数位数),1）该函数用于四舍五入取值，效果优于下图设置：,右图的保留小数设置不能真正实现四舍五入，只是显示为整数，但参与运算时，仍然是原数值，故当数据量比较大时，误差也就随之增大。, Roundup(数值，保留小数位数),该函数用于向上舍入取整，比如，2.1取整时为3，即凡有小数，都向上约进，一般用于包材核算较多，不足1箱的，约进1箱，Roundup（2.31，0）表示向上取整,统计函数, Counta（区域）、CountBlank（区域）,1）COUNTA用于计算区域内非空单元格的个数;COUNTBLAN用于计算区域内空值单元格的个数 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”，故它是不