常用Excel函数详解及应用实例一、日期与时间函数序号 函数 函数定义 页码1 Date 通过年、月或日返回日期 12 Datedif 计算期间内的天数、月数或年数 13 Datevalue 将以文字表示的日期转换成系列数 24 Day 从日期中返回日 25 Edate 返回数月前或数月后的日期 36 Eomonth 返回数月前或数月后的月末 37 Hour 将序列号转换为小时 48 Minute 将序列号转换为分钟 49 Month 从日期中提取出月 410 Networkdays 返回日期之间的全部工作日(除周六、周日和休息日之外的工作天数) 511 Now 返回计算机系统的当前日期和时间 512 Second 返回时间值的秒数(为0至59之间的一个整数) 613 Time 把分散的日期合并换成AM或PM形式的时间表示方式 614 Timevalue 返回由文本字符串所代表的时间的小数值 615 Today 返回系统当前日期的序列号 716 Weekday 返回某日期对应的星期数 717 Weeknum 返回一个数字，该数字代表一年中的第几周 818 Workday 计算给定日期之前或之后的除节假日和双休日之外的日期 819 Year 返回某日期的年份 920 Yearfrac 返回start_date和end_date之间的天数占全年天数的百分比 9整理日期：2013年6月常用Excel函数详解及应用实例第 2 页，共 88 页一、日期与时间函数1.DATE 返回特定日期的序列号函数定义： 合并年、月、日三个数为完整的日期格式,从指定的年、月、日来计算日期序列号值.使用格式： DATE(year,month,day)格式简义： DATE(年,月,日)参数定义： year 参数 year 可以为一到四位.Excel 将根据所使用的日期系统解释 year 参数Excel支持1900年和1904年两种日期系统,这两种日期系统使用了不同的日期作为参照基础,00年日期系统规定1900年的1月1日为第一天,其存储的日期系列编号为1,最后天是9999年12月31日.04日期系统规定1904年1月1日为第一天,基存储的日期系列为0,最后一天同上.系统默认为1900日期系统.month 以整数形式指定日期的月部分的数值,或者指定单元格引用.若指定数大于12,则被视为下一年的1月之后的数值.如果指定的数值小于0,则被视为指定了前一个月份.day 以整数的形式指定日期的日部分的数值,或者指定单元格引用.如果指定数大于月份的最后一天,则被视为下一月份的1日之后的数值.如果指定的数值小于0,则被视为指定了前一个月份.注意事项： 此函数也可以将公式指定为参数.当参数中指定了数值范围外的值时,返回错误值#VALUE!.因此,使用函数时要注意确认参数是否正确.例1 求日期时间相加(date,year,month,day,time,minute,second)基数日期时间： 2013/5/24 16:58:28年 月 日 时 分 秒5 4 6 12 48 582018/5/24 2013/9/24 2013/5/302013/5/24 4:582013/5/24 17:462013/5/24 16:59增加年： =DATE(YEAR(D20)+B22,MONTH(D20),DAY(D20)增加月： =DATE(YEAR(D20),MONTH(D20)+C22,DAY(D20)增加日： =DATE(YEAR(D20),MONTH(D20),DAY(D20)+D22)增加时： =DATE(YEAR(D20),MONTH(D20),DAY(D20)+TIME(HOUR(D20)+E22,MINUTE(D20),SECOND(D20)增加分： =DATE(YEAR(D20),MONTH(D20),DAY(D20)+TIME(HOUR(D20),MINUTE(D20)+F22,SECOND(D20)增加秒： =DATE(YEAR(D20),MONTH(D20),DAY(D20)+TIME(HOUR(D20),MINUTE(D20),SECOND(D20)+G22)例2 有关日期时间的判断(day,eomonth,today)31 =DAY(DATE(YEAR(TODAY(),MONTH(TODAY()+1,0)(计算本月总天数)31 =DAY(EOMONTH(TODAY(),0)(计算本月总天数)29 =DAY(EOMONTH(TODAY(),0)-DAY(TODAY()(计算本月还剩几天）31 =DAY(EOMONTH(TODAY(),1)(计算下个月总天数）3 =INT(MONTH(TODAY()+2)/3)(计算本月属第几季度)3 =INT(MONTH(TODAY()/3.1+1)(计算本月属第几季度)3 =MONTH(MONTH(TODAY()&0)(计算本月属第几季度)3 =ROUNDUP(MONTH(TODAY()/3,)(计算本月属第几季度)3 =CEILING(MONTH(TODAY()/3,1)(计算本月属第几季度)3 =LEN(POWER(2,MONTH(TODAY()(计算本月属第几季度)工作日 =IF(WEEKDAY(TODAY(),2)5,双休日,工作日)(计算当天是休息/工作日)27=WEEKNUM(TODAY(),2)(计算当天是本年的第几周)183=TODAY()-DATE(YEAR(TODAY(),1,0)(计算当年已经过的天数)2013/9/10=WORKDAY(TODAY(),50)(第50个工作日后日期)例3 从身份证中提取出生日期、性别及计算退休日期身份证号码 3426261958102600171958-10-26 =TEXT(MID(C48,7,8),#-00-00)(提取出生日期)男 =IF(MOD(MID(C48,17,1),2)=1,男,女)(判断性别)2018/10/26=DATE(YEAR(B49)+IF(B50=男,60,55),MONTH(B49),DAY(B49)（退休日期）2018/10/26=DATE(YEAR(TEXT(MID(C48,7,8),#-00-00)+IF(IF(MOD(MID(C48,17,1),2)=1,男,女)=男,60,50),MONTH(TEXT(MID(C48,7,8),#-00-00),DAY(TEXT(MID(C48,7,8),#-00-00)(综合公式)2.DATEDIF 计算期间内的年数、月数、天数函数定义： 以指定的单位进行天数计算,通过更改单位,可以进行6种类型天数的计算.使用格式： DATEDIF(start_date,end_date,y)；DATEDIF(start_date,end_date,m)DATEDIF(start_date,end_date,d)；DATEDIF(start_date,end_date,ym)；DATEDIF(start_date,end_date,yd)；DATEDIF(date1,date2,md)格式简义： DATEDIF(开始日期,结束日期,要计算的单位)A B C D E F G H I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859常用Excel函数详解及应用实例第 3 页，共 88 页参数定义： start_date 指定表示日期的数值(序列号值)或单元格引用.start_date的月份被视为0进行计算end_date 指定序列号值或单元格引用.y、m、 y:计算满年数,返回值为0以上的整数;m:计算满月数,返回值为0以上的整数;d、ym、 d:计算满日数,返回值为0以上的整数;ym:计算不满一年的月数,返回值为111之yd、md 间的整数;yd计算不满一年的天数,返回值为0365之间的整数;md:计算不满一个月的天数,返回值为030之间的整数.要点： 不能从插入函数对话框中输入（隐藏函数）.在使用时必须直接键盘输入单元格中.注意事项： 当start_date或end_date中指定的值无法识别为日期时返回错误值#VALUE!.当返回值为负数时,或者y、m、d、ym、yd、md参数没有用双引号括住时,返回错误值#NAME!.例4 姓名 入职日期 到现在工作年数到现在总月数到现在总天数年内相差月数月内相差天数张三 1971/1/11 42 509 15513 5 21李四 1976/2/17 37 448 13650 4 15王五 1993/9/7 19 237 7238 9 25赵六 2007/10/5 5 68 2097 8 27=DATEDIF(C71,TODAY(),y)(到当前工作的整年数)=DATEDIF(C71,TODAY(),m)(到当前工作的总月数)=DATEDIF(C71,TODAY(),d)(到当前工作的总天数)=DATEDIF(C71,TODAY(),ym)(月份相差数)=DATEDIF(C71,TODAY(),md)(天数相差数)=DATEDIF(C71,TODAY(),y)&年&DATEDIF(C71,TODAY(),ym)&月&DATEDIF(C71,TODAY(),md)&天(综合)=TEXT(SUM(DATEDIF(C71,TODAY(),y,ym,md)*10000,100,1),#年#月#日)(综合)例5 计算工龄工资（工龄足5年的每月加100，足10年的每月加200，足20年的每月加300，20年以上的每月加500）姓名 入职日期 工龄 工龄工资 绩效工资 实发工资张三 2000/1/20 13 2600 1800 4400王五 2003/2/20 10 2000 1800 3800洋洋 1991/9/10 21 6300 1800 8100李小军 2005/4/16 8 800 1800 2600李阳 2012/12/1 0 0 1800 1800=IF(DATEDIF(C85,TODAY(),y)5,休息,上班)(上班/休息2)=IF(AND(WEEKDAY(B376,2)0,WEEKDAY(B376,2)0,B177-INT(B177),(-B177-INT(-B177)(小数部分)15 15 0-15 -15 019.63456 19 0.63456-1009.63 -1010 0.63例15 计算职工工资发放备钞张数金额 100 50 20 10 5 2 13179 31 1 1 0 1 2 02718 27 0 0 1 1 1 12373 23 1 1 0 0 1 18274 82 1 1 0 0 2 0=INT(B186/$C$185)(100元钞票)=INT($B186-SUM($C$185:C$185*$C186:C186)/D$185)(50元以后的钞票)12.LCM 整数的最小公倍数函数定义：返回整数的最小公倍数使用格式：LCM(number1,number2,number29)格式简义：LCM(要计算的单元格1,要计算的单元格2,要计算的单元格29)参数定义：number 要计算的单元格或区域要点： 1.最小公倍数是所有整数参数number1、number2等等的最小正整数倍数.用函数LCM可以将分母不同的分数相加2.要计算最小公倍数的1到29个参数.如果参数不是整数,则截尾取整.注意事项：1.如果该函数不可用,并返回错误值#NAME?,请安装并加载分析工具库加载宏2.如果参数为非数值型,函数LCM返回错误值#VALUE!,如果有任何参数小于0,函数LCM返回错误值#NUM!例16 7 20 12 420=LCM(B203:D203)0 2.6 48 010 60 -22 #NUM! 参数小于零,则函数GCD返回错误值#NUM!10 60EXCEL #VALUE! 参数为非数值型,则函数返回错误值#VALUE!.13.LOGLOG10LN指定底数以10为底以e为底的对数函数定义：LOG按所指定的底数,返回一个数的对数；LOG10用来计算指定的Number的以10为底数的对数；LN用来计算指定的Number的以e为底数的对数使用格式：LOG(number,base)；LOG(nmber)格式简义：LOG(目标单元格,对数的底数);LOG10(目标单元格)参数定义：Number 为用于计算对数的正实数.Base 为对数的底数.如果省略底数,假定其值为10.注意事项：1.如果参数Number为0或负值,函数返回错误值#NUM!.2.如果参数为非数值型,则函数GCD返回错误值#VALUE!.例17 计算分别以指定2、以10和以e为底数的对数X Y=Log2(X) Y=Log10(X) Y=Ln(X)0.1-3.3219281 -1 -2.302585=LOG(B219,2)(以2为底的对数)0.5 -1 -0.30103 -0.693147=LOG10(B219)(以10为底的对数)103.32192809 1 2.3025851=LN(B219)(对e为底的对数)2 1 0.30103 0.69314722.718281831.44269504 0.4342945 114.MOD 计算余数A B C D E F G H I J168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224常用Excel函数详解及应用实例第 16 页，共 88 页函数定义：返回两数相除的余数.结果的正负号与除数相同（计算出未除尽数,不计算商的整数部分）使用格式：MOD(number,divisor)格式简义：MOD(被除数,除数)参数定义：Number 被除数Divisor 除数注意事项：1.如果 divisor 为零,函数MOD返回错误值#DIV/0!.2.函数 MOD 可以借用函数INT来表示:MOD(n,d) = n-d*INT(n/d).3.此参数只能是一个单元格,不能是单元格区域.例18 被除数 除数 结果（余数）公式显示86 9 5=MOD(B235,C235)-15 6 324.8 3 0.8例19 利用MOD函数和行号来形成有规律的循环数据被除数 除以2结果 除以3结果 除以4结果 结果1调整 结果2调整 结果3调整结果3倒置1 1 1 1 1 1 1 42 0 2 2 2 2 2 33 1 0 3 1 3 3 24 0 1 0 2 1 4 15 1 2 1 1 2 1 46 0 0 2 2 3 2 37 1 1 3 1 1 3 28 0 2 0 2 2 4 19 1 0 1 1 3 1 410 0 1 2 2 1 2 311 1 2 3 1 2 3 212 0 0 0 2 3 4 1=MOD(B241-1),2)+1 =MOD(B241+1),2)+1 =IF(MOD(B241,2),MOD(B241,2),2)(除以2的结果)=MOD(B241-3)+2,3)+1 =MOD(B241+3)+2,3)+1 =IF(MOD(B241,3),MOD(B241,3),3)(除以3的结果)=5-MOD(B241+3),4)-1(除以4并倒置顺序的结果)例20 偶数行 奇数行 偶数行 奇数行1 与单行号不同 1 与单号不同2 1 2 1 =IF(MOD(ROW(),2)=1,1,ROW()/2-128)(奇数行2行1变)1 2 2 =IF(MOD(ROW(),2)=0,1,(ROW()-1)/2-127)(偶数行2行1变)3 1 1 =IF(MOD(ROW()+1,3)=1,1,IF(MOD(ROW()+1,3)=0,1 3 3 1 ROUNDUP(ROW()/3-85),0)(偶数行3行1变)4 1 3 =IF(MOD(ROW(),3)=1,1,IF(MOD(ROW(),3)=0,1 4 1 (ROW()-2)/3-84)(奇数行3行1变)5 1 4 11 5 4 注：要根据单元格所在不同的位置来增减相关数6 1 11 6 5 1例21 条件格式中的函数应用(1)按地区（合并单元格）设置条件格式（不同间隔底纹）地区 客户名称 销售量北京A1 115A2 289 =N(=MOD(COUNTA($B$272:$B273),2)=0)(地区条件格式1)A3 314 =N(=MOD(COUNTA($B$272:$B273),2)=1)(地区条件格式2)A4 157上海A2 100A4 284A3 262重庆 A5 269A1 345(2)根据部门名称设置间隔底纹月份 部门名称 基本工资 奖金 应发工资A B C D E F G H I J225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283常用Excel函数详解及应用实例第 17 页，共 88 页2012年2月 生产科 3063 160 3223 部门条件格式2012年3月 生产科 2705 170 2875 =MOD(SUM(-($C$284:$C2842012年4月 生产科 2796 186 2982 $C$283:$C283),2)=12012年8月 办公室 2101 119 22202012年9月 保卫科 3500 165 36652012年9月 保卫科 2734 177 2911(3)黑白相间黑白相间条件格式：=MOD(ROW(A294)+COLUMN(B1),2)=015.MROUND 按指定基数舍入后的数值函数定义：返回参数按指定基数舍入后的数值使用格式：MROUND(number,multiple)格式简义：(目标单元格,要保留位数)参数定义：Number 指定数值或数值所在的单元格引用.Multiple 是要对数值number进行四舍五入的基数.要点： 如果数值number除以基数的余数大于或等于基数的一半,则函数MROUND向远离零的方向舍入.注意事项：如果该函数不可用,并返回错误值#NAME?,请安装并加载分析工具库加载宏.例22 一批货要装箱,计算包装总数、余数及所需箱子数量商品名称 总数 每箱容量 包装总数 剩余数量所需箱子数量A产品 302 30 300 2 10=MROUND(C310,D310)(包装总数)B产品 66 12 72 -6 6=C310-E310(剩余数量)C产品 78 12 84 -6 7=E310/D310(所需箱子数量)D产品 298 30 300 -2 1016.ODD 向上舍入最接近的奇数函数定义：返回对指定数值进行向上舍入后的奇数使用格式：ODD(number)格式简义：ODD(目标数值或单元格)参数定义：Number 目标数值或单元格要点： 1.如果number为非数值参数,函数ODD将返回错误值#VALUE!2.不论正负号如何,数值都朝着远离0的方向舍入.如果number恰好是奇数,则不须进行任何舍入处理.注意事项：目标单元格参数只能指定一个,且不能指定单元格区域.例23 数值 8 4.05 0 -0.17 -1.99 公式显示结果 9 5 1 -1 -3 =ODD(C323)17.POWER 计算幂乘函数定义：返回给定数字的乘幂使用格式：POWER(number,power)格式简义：POWER(底数,指数)参数定义：Number 底数,可以为任意实数.Power 指数,底数按该指数次幂乘方.要点： 可以用运算符代替函数POWER来表示对底数乘方的幂次,例如 52.例24 X y=x2 公式显示-2.5 6.25=power(B334,2)-0.5 0.250 00.5 0.252.5 6.25A B C D E F G H I J284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338常用Excel函数详解及应用实例第 18 页，共 88 页18.PRODUCT 所有数字相乘的乘积函数定义：计算出作为参数指定的所有Number的乘积使用格式：PRODUCT(number1,number2.number30)格式简义：PRODUCT(为1到30个需要相乘的数字参数)参数定义：number 指定想要乘积的值,或单元格引用.也可指定单元格区域.参数数量和SUM一样(30个)要点： 忽略参数中的文本和空值,然后求所有参数的乘积.注意事项：1.当参数为数字、逻辑值或数字的文字型表达式时可以被计算；当参数为错误值或是不能转换成数字的文字时,将导致错误.2.如果参数为数组或引用,只有其中的数字将被计算.数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略.例25 数据1 数据25 4 2250=PRODUCT(B351:B354)(Excel被忽略)15 6 4320000=PRODUCT(B351:B354,C351:C354)30 8 (所有数相乘，即：=5*15*30*4*6*8*10)Excel 10例26 用函数计算营业额和折扣后营业额 促销折扣：0.95品名 件 单价 营业额 促销折扣A产品 3 139 417 396.15=PRODUCT(C358,D358)(营业额)B产品 6 35 210 199.5=PRODUCT(C358,D358,$G$356)(促销折扣)C产品 2 186 372 353.4D产品 5 99 495 470.2519.QUOTIENT 商的整数部分函数定义：返回商的整数部分,该函数可用于舍掉商的小数部分.使用格式：QUOTIENT(numerator,denominator)格式简义：QUOTIENT(被除数,除数)参数定义：numerator 被除数denominator除数要点： 1.如果任一参数为非数值型,函数QUETIENT返回错误值#VALUE!.2.此参数只能是一个单元格,不能是单元格区域.注意事项：如果该函数不可用,并返回错误值#NAME?,请安装并加载分析工具库加载宏.例27 被除数 除数 商 商的整数部分186 9 20.6667 20=IF(ISERROR(B373/C373),B373/C373)(商)2 3 0.6667 0=QUOTIENT(B373,C373)(商的整数部分)-15 6 -2.5000 -23.5 0 #DIV/0! 除数为088 0.9 97.7778 97文本数字20.RADIANS 角度转换为弧度函数定义：将角度转换为弧度使用格式：RADIANS(angle)格式简义：RADIANS(目标单元格)参数定义：Angle 为需要转换成弧度的角度注意事项：如果参数为非数值型或单元格区域,则函数RADIANS返回错误值#VALUE!.例28 将角度转换为弧度角度 90 180 360 0 -90 -360公式显示弧度 1.57079633 3.1415927 6.2831853 0-1.5707963 -6.28319=RADIANS(C386)21.RANDRANDBETWEEN随机数指定数之间的一个随机数函数定义：RAND返回大于等于0及小于1的均匀分布随机数,RANDBETWEEN返回位于两个指定数之间的一个随机数使用格式：RAND()；RANDBETWEEN(bottom,top)格式简义：RAND() RANDBETWEEN(最小整数，最大整数）参数定义：bottom 函数RANDBETWEEN将返回的最小整数top 函数RANDBETWEEN将返回的最大整数注意事项：函数RAND：1.若要生成a与b之间的随机实数,请使用:RAND()*(b-a)+aA B C D E F G H I J339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394常用Excel函数详解及应用实例第 19 页，共 88 页2.如果要使用函数RAND生成一随机数,并且使之不随单元格计算而改变,可以在编辑栏中输入=RAND(),然后按F9,将公式永久性地改为随机数.例29 (1)产生01以内的随机数： (2)产生2000年2005年以内的日期随机数：0.7397427=RAND() 2000/5/30=INT(RAND()*(38717-36526)+36526)0=RANDBETWEEN(0,1) 2000/4/15=RANDBETWEEN(36526,38717)22.ROUNDROUNDDOWNROUNDUP按指定位数取整向下取整向上取整函数定义：ROUND返回某个数字按指定位数取整后的数字；ROUNDDOWN靠近0值，向下(绝对值减小的方向)舍入数字；ROUNDUP远离0值，向上舍入数字使用格式：ROUND(number,num_digits)格式简义：ROUND(目标单元格,要保留位数)参数定义：number 指定数值或数值所在的单元格引用.任意实数num_digits四舍五入的位数的位置要点： ROUND：若num_digits0,则四舍五入到指定的小数位;若=0,则四舍五入到最接近的整数;若0，则向下舍入到指定的小数位;若=0,则向下舍入到最接近的整数;如果0,则向上舍入到指定的小数位；若=0，则向上舍入到最接近的整数；若80)*(E565:E57580,=100、男 等.但,当指定条件为引用单元格时无需双引号括住2.使用SUMIF比VLOOKUP查找更方便,可以避免无匹配时返回的错误的问题.通配符 含义 示例*(星号) 与任意文字一致 文*或*A 以文开头的任意文本;以A结尾的任意文本?(问号) 与任意一个文字一致 文? 文后一定是3个字符的文本.例文G16等.(否定号) 指定不将*和?视为通配符 文* 与文*文本一致.此时的*就是字符,不是通配符.(否定号) 指定不将*和?视为通配符 文? 与文?文本一致,此时的?就是字符,不是通配符.例42 品名 件 单价 营业额 59 =SUMIF(B640:B646,*A产品*,C640:C646)A产品 25 139 3475 (所有种类含有A产品的销售/件)B产品BBB 6 35 210 5 =SUMIF(B640:B646,A产品?,C640:C646)C产品 2 186 372 (A产品(后缀名三个字)销售/件)A产品CCC 5 99 495 73 =SUMIF(C640:C646,=5,C640:C646)A产品 11 358 3938 (销售大于等于5件的销售合计/件)A产品DD 18 68 1224 A产品 =SUMIF($B$640:$E$646,F645,$E$640:$E$646)C产品 8 29 232 7413(可以设置产品系列查询)例43 多区域求和(数组)工号 商品 销售量 工号 商品 销售量 工号 商品 销售量A001 A产品 194 A001 产品B 61 A002 内裤 179B001 产品B 40 B001 C产品 41 B001 A产品 97A002 C产品 100 B002 A产品 19 A002 产品B 182B001 A产品CCC 130 B001 产品BBB 101 B001 C产品 54B002 A产品 110 B002 C产品 124 B002 A产品CCC 130A002 A产品DD 74 A002 A产品CCC 179 B003 A产品 61A003 C产品 100 A001 A产品 135 A003 A产品DD 82A001 产品BBB 143 A002 A产品DD 146 A001 产品B 158A002 C产品 121 A003 C产品 146 A001 C产品 158多条件、多列计算 条件 公式结果 公式显示使用常量数组公式 A001、B001的销售量 1312 =SUM(SUMIF(B650:H658,A001,B001,D650:J658)使用单元格引用公式(数组)A001、B001的销售量 1312 =SUM(SUMIF(B650:H658,B650:B651,D650:J658)公式标准写法 A001的销售量 849 =SUMIF(B650:H658,A001,D650:J658)公式简写 A001的销售量 849 =SUMIF(B650:H658,A001,D650)商品名称第4个字为B的销售量带B字的商品销售量 244 =SUMIF(C650:I658,?B*,D650:J658)汇总多品名商品销售量 A产品.产品B.C产品的销售量 1901 =SUM(SUMIF($C$650:$J$658,$C$650:$C$652,$D$650:$J$658)例44 计算单日最高销量和单日最高销量的日期(单字段多条件求和)日期 销售量 单日最高销售量为：2008/8/1 165 1256 =MAX(SUMIF(B669:B677,B669:B677,C669:C677)2008/8/1 1352008/8/2 653 单日销售最高的日期：2008/8/2 254 2008/8/2 =INDEX(B669:B677,MATCH(D669,SUMIF(B669:B677,B669:B677,C669),0)2008/8/2 349 2008/8/2=INDEX(B669:B677,MATCH(MAX(SUMIF(B669:B677,B669:B677,2008/8/3 425 C669:C677),SUMIF(B669:B677,B669:B677,C669),0)2008/8/3 4872008/8/4 6322008/8/4 8628.SUMPRODUCT 计算多个数组的元素的乘积再求和。函数定义：在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和使用格式：SUMPRODUCT(array1,array2,array30)格式简义：SUMPRODUCT(数据1,数据2,，数据30) 其相应元素需要进行相乘并求和代替SUMIF函数多条件计算:SUMPRODUCT(包含条件1的区域=条件1)*(包含条件2的区域=条件2),要计算的区域)参数定义：array 指定包含构成计算对象的值的数组或单元格区域.A B C D E F G H I J626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683常用Excel函数详解及应用实例第 24 页，共 88 页要点： 各个数组行*列的大小必须一样.注意事项：1.数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!.2.数据区域引用不能整列引用.如:A:A、B:B.3.将非数值型的数组元素作为0处理.4.数据区域不大,可以用sumproduct函数,否则,运算速度会变很慢.例45 1 4 32=SUMPRODUCT(B12:B14,C12:C14)2 5 32=1*4+2*5+3*6.(具体的计算)3 6例46 多条件求和姓名 男/女 新老员工 店名 计算结果赵一 女 老 一店 2 =SUMPRODUCT(E696:E701=一店)钱二 女 新 二店 *(D696:D701=新)(一店新员工人数)孙三 女 老 一店 2 =SUMPRODUCT(C696:C701=C696)*(D696:D701=D696)李四 女 新 一店 *(E696:E701=E696)(一店老女员工人数)周五 男 新 一店 1 =SUM(C696:C701=女)*(D696:D701=新)吴六 女 老 二店 *(E696:E701=二店)(二店的新女员工人数)综上所示,对于多条件的计数或者求和,可以用数学函数SUMPRODUCT来比较方便的解决.在使用函数时，进行数据引用的单元格区域或数组应该大小一致,不能采取整列引用(形如A:A).如果跨表使用函数SUMPRODUCT，与其它函数跨表引用数据一样，数据区域前面应该标明工作表名称.计数公式中最关键的是确定计数的判断条件.求和公式在原来的计数公式中,在相同判断条件下增加了一个求和的数据区域.用函数SUMPRODUCT求和,函数需要的参数一个是进行判断的条件,另一个是用来求和的数据区域.例47 计算区域奇、偶数行、列的和1 2 3 4 5 6 71 2 3 4 5 6 7 816 =SUMPRODUCT(MOD(COLUMN(C709:I709),2)=1)*(C709:I709)(上行奇数列的和)20 =SUMPRODUCT(MOD(COLUMN(B710:I710),2)=1)*(B710:I710)(下行奇数列的和)12 =SUMPRODUCT(MOD(COLUMN(B709:I709),2)=0)*(B709:I709)(上行偶数列的和)16 =SUMPRODUCT(MOD(COLUMN(B710:I710),2)=0)*(B710:I710)(下行偶数列的和)3 =SUMPRODUCT(MOD(ROW(E709:E710),2)=1)*(E709:E710)(单列奇数行的和)4 =SUMPRODUCT(MOD(ROW(E709:E710),2)=0)*(E709:E710)(单列偶数行的和)例48 根据表2得分等级对照表将表1换算出每人的具体得分，并统计每个班的平均分表1 表2班级 评分 换算 得分 等级 1.利用表2的等级得分标准来对表1进行换算：1班 A* 10 10 A* =SUMPRODUCT(C721=$F$721:$F$730)*$E$721:$E$730)2班 A* 9 9 A* =SUM(D48=$F$721:$F$730)*$E$721:$E$730)2班 B* 7 8 A 2.各班平均得分1班 A 8 7 B* 一班 7.75 7.753班 D 1 6 B* =ROUND(SUMP