常用函数补充课件

常用函数补充1公式为：=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)式中：sum_range——需要求和的数值所在的区域；criteria_range1——用于做条件判断的区域1；criteria1——条件1，可以为数字、文本或表达式；criteria_range2——用于做条件判断的区域2；criteria2——条件2，可以为数字、文本或表达式；1.多条件求和函数SUMIFS()功能：如果criteria_range1里的数值满足criteria1，criteria_range2里的数值满足criteria2，则对Sum_range里的数值求和。2例：多条件统计手机销量.xls下图模拟了一份某通讯专卖场二季度手机的销售明细表。请统计出工号“A001”销售“iphone手机”的总销量。3注意：系统将A001视为单元格引用。公式：SUMIFS(D3:J11,B3:H11,“A001”,C3:I11,“iphone手机”)4例：多条件统计员工工资表数据.xls下图是某公司员工工资明细表。请利用公式统计出各个部门按指定“姓氏”的员工工资汇总。5注意：系统将N3*视为一串文本。公式：在O3单元格输入=SUMIFS($K$3:$K$17,$D$3:$D$17,M3,$C$3:$C$17,N3&"*“)62.VLOOKUP函数（查找与引用函数）7函数的语法为：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)V：vertical，垂直的，垂直线，垂直。lookup_value：查找值。支持使用通配符（“？”号和“*”号）进行查询。table_array：查找区域。查找值必须位于查找区域的首列。Col_index_num：列号。需要返回的数据在查找区域（table_array）中的第几列。8函数的语法为：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)range_lookup：决定查找方式。=0（或false）

精确匹配。支持无序查找。=1（或true）

模糊匹配。要求第2个参数（table_array）的首列按升序排列。函数功能：在查找范围(table_array)的首列纵向搜索某值(lookup_value)（确定返回值所在的行），然后根据指定的列号(col_index_num)返回对应的值。9例：使用VLOOKUP函数进行员工信息查询.xls下图展示了一份员工信息表。请针对不同的查询条件，从信息表中进行相应的查询。10B3单元格公式：=VLOOKUP(B2,D1:G9,2,0)或：=VLOOKUP(B2,D1:E9,2,0)注意查找区域的选取：查找值必须处于查找区域的首列。查找区域必须包含返回值所在的列。B6单元格公式：==VLOOKUP(B5,E1:F9,2,0)或：=VLOOKUP(B5,E1:G9,2,0)11B9单元格返回#N/A？注意：如果查找值与查找区域关键字的格式不一致（如文本型数字和数值），将导致查询失败。分析：B8单元格为文本型数字文本型数字：内容是数值，但作为文本类型作为存储。判别文本型数字：单元格左上角出现“错误检查”标识符（绿色三角形）。“错误检查”标识符：该单元格可能存在某些错误或需要注意的特点。如何将文本型数字转换为数值？文本型数字可以作为数值直接参与四则运算。方法：+0 -0 *1 /1B9单元格公式：=VLOOKUP(B8+0,D1:E9,2,0)12函数的语法为：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)H：horizontal，水平的，水平线，水平。lookup_value：查找值。支持使用通配符（“？”号和“*”号）进行查询。table_array：查找区域。查找值必须位于查找区域的首行。row_index_num：行号。需要返回的数据在查找区域（table_array）中的第几行。3.HLOOKUP函数（查找与引用函数）13函数的语法为：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)range_lookup：决定查找方式。=0（或false）

精确匹配。支持无序查找。=1（或true）

模糊匹配。要求第2个参数（table_array）的首行按升序排列。函数功能：在查找范围(table_array)的首行横向搜索某值(lookup_value)（确定返回值所在的列），然后根据指定的行号(row_index_num)返回对应的值。14例：动态查询员工年休假信息.xls下图展示了某公司人力资源部门的员工信息表。请查询指定员工的年假情况。15补充两个日期与时间函数：TODAY()、NOW()TODAY()——返回当前日期NOW()——返回当期日期和时间说明：该函数不需要参数每当用户编辑任意单元格或重新打开包含该函数的工作簿时，公式都会重新计算。16步骤一：计算工龄G5单元格公式：=YEARFRAC(E5,TODAY())步骤二：计算年假F5单元格公式：=HLOOKUP(G5,$C$1:$F$2,2,1)当Hlookup函数的最后一个参数为1或true时，表示实现模糊匹配。要求第2个参数（table_array，查找区域）的首行按升序排列。VLOOKUP函数和HLOOKUP函数对比17Vlookupvertical，垂直的，纵向的HlookupHorizontal，水平的，横向的区别纵向查询横向查询18例：在项目评测表中动态行次和列次查找.xls下图所示是一份某企业员工项目评测表，请利用常规的查找函数综合应用来实现员工项目评测结果的动态查询。19C13单元格公式：=VLOOKUP(A13,$A$1:$D$9,MATCH($B13,$A$1:$D$1,0),0)C14单元格公式：=HLOOKUP(B14,$A$1:$D$9,MATCH($A14,$A$1:$A$9,0),0)4.OFFSET函数（查找与引用函数）20OFFSET函数是Excel引用类函数中非常实用的函数之一，使用频率非常高。函数的语法：OFFSET(reference,rows,cols,[height],[width])offset，偏移，平移。reference，引用。函数引用基点。rows，行偏移。正数表示向下偏移，负数表示向上偏移。cols，列偏移。正数表示向右偏移，负数表示向左偏移。height，高度。 width，宽度。OFFSET函数的功能：以指定的引用为参照，通过给定偏移量得到一个新的单元格区域引用。21例：OFFSET函数引用示例.xls公式1：=offset(B2,2,2,5,2)分析：以B2为基点，行偏移2（向下），列偏移2（向右）。从偏移点向下扩展5行，向右扩展2列，并返回该区域的值。公式2：=offset(G11,-3,-2,-5,-2)分析：以G11为基点，行偏移-3（向上），列偏移-2（向左）。从偏移点向上扩展5行，向左扩展2列，并返回该区域的值。22例：OFFSET函数引用示例.xls公式3：=offset(B2:C6,2,2)分析：以区域B2:C6的左上角单元格B2为基点，行偏移2（向下），列偏移2（向右）。因为省略了参数height和width，公式取height=原区域高度=5，取width=原区域宽度=2；即从偏移点向下扩展5行，向右扩展2列。返回该区域的值。公式4：=offset(F10:G11,-2,-2,-5)分析：以区域F10:G11的左上角单元格F10为基点，行偏移-2（向上），列偏移-2（向左）。height=-5，即从偏移点向上扩展5行。因为省略了参数width，公式取width=原区域宽度=2；即从偏移点向左扩展2列。返回该区域的值。23使用OFFSET函数的注意事项：如果指定的基点是一个区域，则函数以该区域左上角单元格为基点进行偏移运算。如果省略参数height，则系统取height=原区域高度。如果省略参数width，则系统取width=原区域宽度。例：offset函数应用.xlsF2单元格公式：

=AVERAGE(OFFSET(C1,COUNT(C:C),0,-E2,1))查找与引用函数的综合应用2425例：查询申购基金的申购手续费.xls下图模拟了一份基金购买的申购费率表，其中左表为购买基金的费率标准，右表D1:E4区域为根据标准转换得到的数值关系表。请根据购买者的申购金额来计算应付的申购基金手续费。26回忆：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)range_lookup：决定查找方式。=0（或false）

精确匹配。支持无序查找。=1（或true）

模糊匹配。要求第2个参数（table_array）的首列按升序排列。B8单元格的公式：=B6*VLOOKUP(B6,D2:E4,2,1)回忆：公式：=MATCH(lookup_value,lookup_array,match_type)式中：Match_type：如何查找。可取三个值：-1,0,1.当match_type=1时：Lookup_array必须按升序排列，MATCH函数从小往大查，查找到≤Lookup_value的最大数值。27查什么在哪里查以什么方式查C8单元格的公式：=B6*INDEX(D2:E4,MATCH(B6,D2:D4,1),2)28Match()和Vlookup()的联系与区别MATCH(lookup_value,lookup_array,match_type)——在lookup_array中搜索lookup_value，返回其位置序号。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)——在table_array的首列纵向搜索lookup_value，然后根据col_index_num返回对应的值。Match()Vlookup()搜索区域lookup_array是一维的（一行或一列）table_array至少包含两列返回值lookup_value的相对位置序号根据col_index_num，返回与lookup_value在同一行的某个值。29例：利用查找函数进行多表数据标识.xls下图展示了一份某行政部门的员工分工明细表，各员工的数据表结构相同，为了便于部门经理了解各位员工的工作分配情况，请将各员工的工作明细表汇总到部门工作一览表。30一、补充：逻辑函数IFERROR(value,value_if_error)error：错误。解释：在第一个参数“value”的位置放入一个公式，如果公式能顺利计算出结果，则返回该结果；如果公式计算结果为错误，则返回value_if_error。功能：屏蔽错误值。例：iferror()示例31C3单元格公式：=MATCH(B3,张三!$B$3:$B$6,0)二、分析1.张三是否做了该项工作？返回位置序号：张三做了该工作。返回错误值#N/A：张三没有做该工作。2.屏蔽错误值，让其显示为0C3单元格公式：=IFERROR(MATCH(B3,张三!$B$3:$B$6,0),0)3.用”√”代替非0，用空文本替代0.C3单元格公式：=IF(IFERROR(MATCH(B3,张三!$B$3:$B$6,0),0),"√","")非零（正数、负数）为真，零为假32例：通过身份证号提取个人相关信息.xls33一、新编号UPPER(text)：将一个字符串中的所有小写字母转换为大写字母。upper：上面的，上部的；较高的。LOWER(text)：将一个字符串中的所有大写字母转换为小写字母。lower：下游的，下级的，下等的。PROPER(text)：将字符串的首字母转换为大写，将其余的字母转换为小写。proper：适当的。补充：3个文本函数B3单元格公式：=PROPER(A3)34二、出生日期（1）补充：文本函数MID(text,start_num,num_chars)MID：middle，中间的。Start_num：text中要提取的第一个字符的位置。num_chars：character，字符。提取字符的数量。分析：增添辅助列“年”、“月”、“日”。（2）补充：日期与时间函数DATE(year,month,day)DATE：日期。功能：根据指定的年份、月份和日期返回具体的日期值。例：=DATE(K3,M3+1,L3)E3单元格公式：=DATE(MID(F3,7,4),MID(F3,11,2),MID(F3,13,2))35三、年龄补充：日期与时间函数DATEDIF()——一个隐藏的、但功能非常强大的日期函数。没有在函数列表中显示出来。帮助文件中没有相关说明。（1）函数的语法：=DATEDIF(start_date,end_date,unit)Start_date、end_date：分别为起始日期和结束日期。可以是单元格引用可以是带引号的日期，如”2010-7-3”或”2010/7/3”可以是其他公式或函数的结果，如DATE(2010,8,3)36（1）函数的语法：=DATEDIF(start_date,end_date,unit)unit代码函数返回值“y”时间段中的整年数“m”时间段中的整月数“d”时间段中的整天数“md”start_date与end_date日期中天数的差。忽略日期中的年和月“ym”start_date与end_date日期中月数的差。忽略日期中的年和日“yd”start_date与end_date日期中月数的差。忽略日期中的年例：datedif函数示例.xlsG3单元格公式：=DATEDIF(E3,TODAY(),"y")37四、资历补充：文本合并函数concatenate(text1,[text2],…)concatenate：连结。功能：将几个文本项合并为一个文本项。连接项可以是文本、数字、单元格地址。=A1&B1=concatenate(A1,B1)功能相同J3单元格公式：=CONCATENATE(DATEDIF(I3,TODAY(),"y"),"年",DATEDIF(I3,TODAY(),"ym"),"月")38例：统计员工相关数据.xls39一、员工总人数补充：统计函数COUNTA(value1,[value2],…)功能：统计指定区域中不为空的单元格的个数。C3单元格公式：=COUNTA(档案!A4:A21)二、女性员工人数C4单元格公式：=COUNTIF(档案!D4:D21,"女")40三、学历为本科的男性员工人数补充：多条件计数函数COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)功能：统计指定区域内符合多个给定条件的单元格的数量。C5单元格公式：=COUNTIFS(档案!H4:H21,"本科",档案!D4:D21,"男")说明：criteria_range1、criteria_range2、…必须具有相同的列数和行数。四、基本工资总额C6单元格公式：=SUM(档案!J4:J21)41五、管理人员工资总额C7单元格公式：=SUMIF(档案!G4:G21,"管理",档案