常用EXCEL函数详解及应用实例(分类汇总版7)——财务函数.xls

常常用用ExcelExcel函函数数详详解解及及应应用用实实例例 一一 日日期期与与时时间间函函数数 序序号号函函数数函函数数定定义义页页码码 1Date通过年 月或日返回日期1 2Datedif计算期间内的天数 月数或年数1 3Datevalue将以文字表示的日期转换成系列数2 4Day从日期中返回 日 2 5Edate返回数月前或数月后的日期3 6Eomonth返回数月前或数月后的月末3 7Hour将序列号转换为小时4 8Minute将序列号转换为分钟4 9Month从日期中提取出 月 4 10Networkdays返回日期之间的全部工作日 除周六 周日和休息日之外的工作天数 5 11Now返回计算机系统的当前日期和时间5 12Second返回时间值的秒数 为0至59之间的一个整数 6 13Time把分散的日期合并换成AM或PM形式的时间表示方式6 14Timevalue返回由文本字符串所代表的时间的小数值6 15Today返回系统当前日期的序列号7 16Weekday返回某日期对应的星期数7 17Weeknum返回一个数字 该数字代表一年中的第几周8 18Workday计算给定日期之前或之后的除节假日和双休日之外的日期8 19Year返回某日期的年份9 20Yearfrac返回start date和end date之间的天数占全年天数的百分比9 整整理理日日期期 20132013年年6 6月月 ZylkfxyZylkfxy 常用Excel函数详解及应用实例 第 2 页 共 90 页 一一 日日期期与与时时间间函函数数 1 DATE1 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 年月日时分秒 546 124858 2018 5 242013 9 242013 5 302013 5 24 4 582013 5 24 17 46 2013 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 计算本月总天数 28 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 m 计算满月数 返回值为0以上的整数 d ym d 计算满日数 返回值为0以上的整数 ym 计算不满一年的月数 返回值为1 11之 yd md间的整数 yd计算不满一年的天数 返回值为0 365之间的整数 md 计算不满一个 月的天数 返回值为0 30之间的整数 要点 不能从 插入函数 对话框中输入 隐藏函数 在使用时必须直接键盘输入单元格中 注意事项 当start date或end date中指定的值无法识别为日期时返回错误值 VALUE 当返回值为 负数时 或者y m d ym yd md参数没有用双引号括住时 返回错误值 NAME 例4姓名入职日期 到现在工作年数到现在总月数到现在总天数 年内相差月数月内相差天数 张三 1971 1 1142509155145 22 李四 1976 2 1737448136514 16 王五 1993 9 71923772399 26 赵六 2007 10 556820988 28 DATEDIF C71 TODAY y 到当前工作的整年数 DATEDIF C71 TODAY m 到当前工作的总月数 DATEDIF C71 TODAY d 到当前工作的总天数 DATEDIF C71 TODAY ym 月份相差数 DATEDIF C71 TODAY md 天数相差数 DATEDIF C71 TODAY y TIMEVALUE time text 格式简义 TIME 时 分 秒 TIMEVALUE 时间 参数定义 hour用0 23之间的整数指定为时间的 时 参数的数值 或者指定单元格引用 当指定数值大 于24时 指定的数值为该数值除以24之后的余数 minute以整数指定为时间的 分 参数的数值 或者指定单元格引用 当指定数值大于60时 则 被视为指定下一个 时 如果指定数值小于0时 则被视为指定了上一个 时 secod以整数指定为时间的 秒 参数的数值 或者指定单元格引用 当指定数值大于60时 则 被视为指定下一个 分 如果指定数值小于0时 则被视为指定了上一个 分 time text以24小时制形式指定表示时间文本指定 指定表示时间文本时 日期用 双引号 括住 要点 TIME当表示0时 0分 0秒时 可以省略参数 但是要注意的是 不能省略 TIMEVALUE此函数引用的单元格内的时间必须是文本格式 即在先写 单引号 然后输入时间 注意事项 当参数中指定了数值无法识别为时间时 返回错误值 VALUE 例23日期时分秒结果公式显示 2011 5 613302813 30 28 TIME C310 D310 E310 2011 6 87107 01 00 TIME C311 D311 2011 11 223 10722 50 07 TIME C312 D312 E312 例24选手是否晋级 开始时间 10 00 00如果在1 30分钟 即90分钟 内完成即可晋级 姓名小时分钟秒用时化为分钟数 判断晋级 张三145281 45 28105 47不能晋级 李四128491 28 4988 82晋级 赵六21172 01 17121 28不能晋级 郑七112591 12 5972 98晋级 吴八138101 38 1098 17不能晋级 孙九118481 18 4878 80晋级 周十131151 31 1591 25不能晋级 TIME C22 D22 E22 实际用时 TIME C22 D22 E22 1440 化为分钟数 IF TIME C317 D317 E317 1440 90 晋级 不能晋级 判断晋级 例25 计算员工出勤规定上班时间09 00 出勤日期出勤时间上班误差时间显示迟到时间 2011 11 209 303030 TIMEVALUE C43 TIMEVALUE E 41 24 60 2011 11 309 0555 上班误差时间 2011 11 408 58 2 IF TIMEVALUE C329 TIMEVALUE E 327 24 605 休息 上班 上班 休息2 IF AND WEEKDAY B376 2 0 WEEKDAY B376 2 6 上班 加班 上班 休息3 IF OR WEEKDAY B376 2 6 WEEKDAY B376 2 7 休息 上班 上班 休息4 WEEKDAY B376 2 星期几 16 WEEKNUM16 WEEKNUM 计计算算日日期期是是一一年年中中的的第第几几周周 函数定义 计算从1月1日算起的第几个星期 返回一个数字 该数字代表一年中的第几周 使用格式 WEEKNUM serial num return type 格式简义 WEEKNUM 日期 2 参数定义 serial num 指定序列号值或单元格引用 return type指定每星期的第一天 要点 用来计算出当以一年的第一个星期为 1 时 指定的日期处于第几个星期中 注意事项 1 当指定的 serial num 的值无法识别为日期时 返回错误值 VALUE 如果当 return type 中指定 的值不是1 或者省略 或2时 则函数返回错误值 NUM 此外 当指定了无效的日期时 使用函数时要注 意确认参数是否正确 2 注意使用些函数必须安装 分析工具库 加载宏 3 有的国家以周日为一周的开始日 有的国家以周一为一周的开始日 我国以 2 来计算 例31计算日期是一周的第几天 一年的第几周某公司要以周为周期及指定星期几的销售报表 日期一周中第几天 一年中第几周日销售各种情况结果 2011 6 1323494023 WEEKNUM B402 2 1年中的第几周 2011 7 262778003 WEEKDAY B402 2 1周中的第几天 2011 8 3332343027 WEEKNUM TODAY 2 当天是1年中第几周 2011 9 4736653053 WEEKNUM 2013 12 31 2 2 1年共几周 2011 10 5341542113791 SUM WEEKDAY B 402 B 406 2 3 2013 6 307265421 E 402 E 406 计算周3的销售量 例32 当前日期是否是你本周生日 出生日期 1981 6 28 结果 ABCDEFGHI 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 常用Excel函数详解及应用实例 第 9 页 共 90 页 1 将出生年转为当前年份2013 6 28 DATE YEAR TODAY MONTH C410 DAY C410 日期转换 2 判断是否本周生日 非也 IF WEEKNUM TODAY 2 WEEKNUM DATE YEAR TODAY MONTH C410 DAY C410 2 本周生日 非也 判断本周是否是生日 17 WORKDAY17 WORKDAY 计计算算除除节节假假日日和和双双休休日日之之外外的的日日期期 函数定义 用来计算给定日期之前或之后的除节假日和双休日之外的日期 返回日期值为 1 1900年1月1日 2958465 999年12月31日 范围之间的整数 序列号值 使用格式 WORKDAY start date days holidays 格式简义 WORKDAY 指定日期 几个工作日 自己特定的非工作日 参数定义 start date指定表示日期的数值 序列号值 或单元格引用 start date有月份被视为 0 进行计算 days指定直接给定的日期 不包括周末和节假日 小数部分的值被向下舍入 若指定数值为 正数则返回指定日期之后的日期 若指定数值为负数则返回指定日期之前的日期 holidays指定节日或假日等休息日 可以指定序列号值 单元格引用和数组常量 此参数可以省 略 当省略了此参数时 返回除了周末之外的 直到给定日期天数 要点 当在工作表上编制了休息日列表时 可以用做参数的 holidays 此外 除了列表之外 也可使用 数组常量 注意事项 1 当指定 start date 或 holidays 的值无法识别为日期时 返回错误值 VALUE 若当 start date 或 days 的和小于1或者大于2958465时 则函数返回错误值 NUM 此外 当指定了无效的日期时 使用 函数时要注意确认参数是否正确 2 注意使用些函数必须安装 分析工具库 加载宏 例33 计算除了周末和节假日之外的4个工作日之后的发送日期 接收日2011 9 30国庆节假日 2011 10 1 发送日2011 10 7 2011 10 2 WORKDAY C432 4 H432 H434 4年工作日之后的发送日期 2011 10 3 18 YEAR18 YEAR 从从日日期期中中提提取取 年年 函数定义 从日期中提取出 年 将系列数转换为年 使用格式 YEAR serial number 格式简义 YEAR 要查的单元格 参数定义 serial numberserial number为一个日期值 其中包含要查找的年份 日期有多种输入方式 带引号的 文本串 系列数或其他公式或函数的结果 注意事项 当指定的 serial number 的值无法识别为日期时 返回错误值 VALUE 例34下表为各种日期时间格式 都可提取 年 日期 结果 公式显示 2011年1月31日2011 YEAR B445 407202011 2012 2 22012 2013 10 182013 例35 组合日期与时间的计算 日期时间基数2013 7 3 04 22 32 单位年月日时分秒 待加日期时间546124827 与基数相加2018119167059 2018 YEAR C451 C453 加5年后的日期与时间 11 MONTH C451 D453 加4个月后的日期与时间 9 DAY C451 E453 加6日后的日期与时间 16 HOUR C451 F453 加12小时后的日期与时间 70 MINUTE C451 G453 加48分后的日期与时间 59 SECOND C451 H453 加27秒后的日期与时间 2018 11 9 04 23 36 综合相加后的日期与时间 DATE YEAR C451 C453 MONTH C451 D453 DAY C451 E453 TEXT TIME HOUR C451 F453 MINUTE C451 G453 SECOND C451 H453 24 hh mm ss 19 YEARFRAC19 YEARFRAC 计计算算指指定定期期间间占占一一年年的的比比率率 函数定义 以1年为 1 时所占的比率的形式计算出从 start date 到 end date 之间的天数 返回值为 包含小数的数值 使用格式 YEARFRAC start date end date basis 格式简义 YEARFRAC 开始日期 结束日期 计算的方式 ABCDEFGHI 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 常用Excel函数详解及应用实例 第 10 页 共 90 页 参数定义 start date 指定序列号值或单元格引用 以 start date 的次日为 1 进行计算 end date指定序列号值或单元格引用 basis指定表示用于计算天数的基准天数 月 年 的值 如果此参数省略则视为指定值 0或 省略 30天 360天 NASD方法 1 实际天数 实际天数 2 实际天数 360天 3 实际天 数 365天 4 30天 360天 欧洲方法 要点 此函数的返回值以含小数的形式显示 如果要换成百分比形式显示 则必须通过 单元格格式 对话将数 字显示格式更改为 百分比 的形式 注意事项 1 当指定的 start date 或 end date 的值无法识别为日期时 返回错误值 VALUE 2 当 basis 中指定的值不在0 4范围内时返回错误值 NUM 计算下面日期在1年中的所占比率 项目开始日2011 4 10 项目完成日2011 9 20 占年间比率44 66 YEARFRAC C481 C482 1 ABCDEFGHI 469 470 471 472 473 474 475 476 477 478 479 480 481 482 常常用用ExcelExcel函函数数详详解解及及应应用用实实例例 二二 数数学学与与三三角角函函数数 序序号号函函数数函函数数定定义义页页码码 1Abs返回参数的绝对值1 2Ceiling沿绝对值增大的方向 舍入为最接通近的整数或基数1 3Combin计算从给定数目的对象集合中提取若干对象的组合数1 4Cos返回给定角度的余弦值1 5Degrees返回给定角度的余弦值2 6Even返回沿绝对值增大方向取整后最接近的偶数2 7Exp返回自然对数的底数e的幂计算2 8Fact返回参数的阶乘 一个数N的阶乘等于1 2 3 N该数 2 9Floor沿绝对值减小的方向去尾舍入使其等于最接近的倍数3 10Gcd返回最大公约数3 11Int将数字向下舍入为最接近的整数4 12Lcm返回最小公倍数4 13Ln返回数字的自然对数4 14Log返回数字的指定底数的对数4 15Log10返回数字的常用对数4 16Mod返回两数相除的余数5 17Mround返回参数按指定基数舍入后的数值6 18Odd返回对指定数值进行向上舍入后的奇数6 19Power返回给定数字的乘幂 同 6 20Product将所有以参数形式给出的数字相乘7 21Quotient返回商的整数部分 即舍去商的小数部分 7 22Radians将一个表示角度的数值或参数转换为弧度7 23Rand返回个大于等于0小于1的随机数8 24Randbetween返回位于两个指定数值之间的一个随机数8 25Round按指定位数四舍五入某个数字 8 26Rounddown按指定的位数依绝对值减小的方向舍入某一数字8 27Roundup按指定的位数依绝对值增大的方向舍入某一数字8 28Sin返回某一角度的正弦值9 29Sqrt返回某一正数的算术平方根 9 30Sqrtpi返回一个正实数与 的乘积的平方根 9 31Subtotal返回数据清单或数据库中的分类汇总9 32Sum返回单元格区域中数字 逻辑值及数字的文本表达式 直接键入的数字之和10 33Sumif根据指定条件对若干单元格 区域或引用求和12 34Sumproduct将数组间对应的元素相乘 并返回乘积之和13 35Sumsq返回多个 值 先计算各自的平方 之后再将结果全部合计求和14 36Summx2My2返回两数组中对应数值的平方差之和14 37Sumx2Py2返回两数组中对应数值的平方和的总和14 38Sumxmy2返回两数组中对应数值之差的平方和15 39Tan返回某一角度的正切值15 40Trunc根据指定的位数位置向下舍入并将数字的小数部分截去 返回整数15 整整理理日日期期 20132013年年6 6月月 Zylkfxy 常用Excel函数详解及应用实例 第 13 页 共 90 页 二二 数数学学与与三三角角函函数数 1 ABS1 ABS 计计算算绝绝对对值值一一 日日期期与与时时间间函函数数 函数定义 返回数字的绝对值 绝对值没有符号 使用格式 ABS number 格式简义ABS 要计算的单元格 参数定义 Number需要计算其绝对值的实数 要点 所谓绝对值 是指将数值上表示正负的符号去掉后得出的值 它表示该数值的 大小 用数轴 表示的话 就是与原点0的距离 注意事项 1 此参数只能是一个单元格 不能是单元格区域 2 如果参数为非数值型 则函数GCD返回错误值 VALUE 例1 数值结果公式显示 2121 ABS B13 22 0 340 34 00 中国人民 VALUE 出错说明 参数为非数值型 则函数ABS返回错误值 VALUE 2 CEILING2 CEILING 向向上上舍舍入入为为指指定定的的倍倍数数 函数定义 将参数Number向上舍入 沿绝对值增大的方向 为最接近的significance的倍数 使用格式 CEILING number significance 格式简义 CEILING 目标单元格 要保留位数 参数定义 Number指定数值或数值所在的单元格引用 要四舍五入的数值 significancesignificance为基数 是需要四舍五入的乘数 要点 1 如果任一参数为非数值参数 则CEILING将返回错误值 VALUE 2 如果number和significance符号相反 则函数CEILING将返回错误值 NUM 3 无论数字 符号如何 都按远离0的方向向上舍入 如果数字已经为Significance的倍数 则不进行舍入 注意事项 目标单元格参数只能指定一个 且不能指定单元格区域 例2值 86868686 公式显示 基准值5101520 结果909090100 CEILING C29 C30 例3 现有一批货要装箱 计算包装总数及所需箱子数量 商品名称总数每箱容量包装总数剩余数量 需箱子数量包装总数 A产品30230330 2811 CEILING C35 D35 B产品661272 66 C产品781284 67 D产品29830300 210 3 COMBIN3 COMBIN 计计算算组组合合数数或或二二项项系系数数 函数定义 计算从给定数目的对象集合中提取若干对象的组合数 使用格式 COMBIN number number chosen 格式简义 COMBIN 为对象的总数量 为每一组合中对象的数量 参数定义 Number为对象的总数量 number chosennumber chosen为每一组合中对象的数量 注意事项 1 数字参数截尾取整 2 如果参数为非数值型 则函数COMBIN返回错误值 VALUE 3 如果number 0 number chosen 0或number0 B177 INT B177 B177 INT B177 小数部分 15150 15 150 19 63456190 63456 1009 63 10100 63 例15 计算职工工资发放备钞张数 金额100502010521 317931110120 271827001111 237323110011 827482110020 INT B186 C 185 100元钞票 INT B186 SUM C 185 C 185 C186 C186 D 185 50元以后的钞票 12 LCM12 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 例1672012420 LCM B203 D203 02 6480 1060 22 NUM 参数小于零 则函数GCD返回错误值 NUM 1060 EXCEL VALUE 参数为非数值型 则函数返回错误值 VALUE 13 LOG LOG10 LN 13 LOG LOG10 LN 指指定定底底数数 以以1010为为底底 以以e 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为底数的对数 XY 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为底的对数 10 3 321928091 2 3025851 LN B219 对e为底的对数 210 30103 0 6931472 2 718281831 44269504 0 43429451 14 MOD14 MOD 计计算算余余数数 ABCDEFGHIJ 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 常用Excel函数详解及应用实例 第 17 页 共 90 页 函数定义 返回两数相除的余数 结果的正负号与除数相同 计算出未除尽数 不计算商的整数部分 使用格式 MOD number divisor 格式简义 MOD 被除数 除数 参数定义 Number被除数 Divisor除数 注意事项 1 如果 divisor 为零 函数MOD返回错误值 DIV 0 2 函数 MOD 可以借用函数INT来表示 MOD n d n d INT n d 3 此参数只能是一个单元格 不能是单元格区域 例18被除数除数结果 余数 公式显示 8695 MOD B235 C235 1563 24 830 8 例19利用MOD函数和行号来形成有规律的循环数据 被除数除以2结果除以3结果 除以4结果 结果1调整 结果2调整 结果3调整结果3倒置 11111114 20222223 31031332 40102141 51211214 60022323 71131132 80202241 91011314 100122123 111231232 120002341 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与单号不同 2121 IF MOD ROW 2 1 1 ROW 2 128 奇数行2行1变 122 IF MOD ROW 2 0 1 ROW 1 2 127 偶数行2行1变 311 IF MOD ROW 1 3 1 1 IF MOD ROW 1 3 0 1331ROUNDUP ROW 3 85 0 偶数行3行1变 413 IF MOD ROW 3 1 1 IF MOD ROW 3 0 141 ROW 2 3 84 奇数行3行1变 5141 154 注 要根据单元格所在不同的位置来增减相关数 611 1651 例21 条件格式中的函数应用 1 按地区 合并单元格 设置条件格式 不同间隔底纹 地区客户名称销售量 北京 A1115 A2289 N MOD COUNTA B 272 B273 2 0 地区条件格式1 A3314 N MOD COUNTA B 272 B273 2 1 地区条件格式2 A4157 上海 A2100 A4284 A3262 重庆 A5269 A1345 2 根据部门名称设置间隔底纹 月份部门名称基本工资奖金应发工资 ABCDEFGHIJ 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 常用Excel函数详解及应用实例 第 18 页 共 90 页 2012年2月生产科30631603223部门条件格式 2012年3月生产科27051702875 MOD SUM C 284 C284 2012年4月生产科27961862982 C 283 C283 2 0 2012年5月财务科20731112184 2012年6月财务科39021874089 MOD SUM C 284 C284 2012年7月办公室25931472740 C 283 C283 2 1 2012年8月办公室21011192220 2012年9月保卫科35001653665 2012年9月保卫科27341772911 3 黑白相间 黑白相间条件格式 MOD ROW A294 COLUMN B1 2 0 15 MROUND15 MROUND 按按指指定定基基数数舍舍入入后后的的数数值值 函数定义 返回参数按指定基数舍入后的数值 使用格式 MROUND number multiple 格式简义 目标单元格 要保留位数 参数定义 Number指定数值或数值所在的单元格引用 Multiple是要对数值number进行四舍五入的基数 要点 如果数值number除以基数的余数大于或等于基数的一半 则函数MROUND向远离零的方向舍入 注意事项 如果该函数不可用 并返回错误值 NAME 请安装并加载 分析工具库 加载宏 例22一批货要装箱 计算包装总数 余数及所需箱子数量 商品名称总数每箱容量包装总数剩余数量 所需箱子数量 A产品30230300210 MROUND C310 D310 包装总数 B产品661272 66 C310 E310 剩余数量 C产品781284 67 E310 D310 所需箱子数量 D产品29830300 210 16 ODD16 ODD 向向上上舍舍入入最最接接近近的的奇奇数数 函数定义 返回对指定数值进行向上舍入后的奇数 使用格式 ODD number 格式简义 ODD 目标数值或单元格 参数定义 Number目标数值或单元格 要点 1 如果number为非数值参数 函数ODD将返回错误值 VALUE 2 不论正负号如何 数值都朝着远离0的方向舍入 如果number恰好是奇数 则不须进行任何舍入处理 注意事项 目标单元格参数只能指定一个 且不能指定单元格区域 例23数值84 050 0 17 1 99 公式显示 结果951 1 3 ODD C323 17 POWER17 POWER 计计算算幂幂乘乘 函数定义 返回给定数字的乘幂 使用格式 POWER number power 格式简义 POWER 底数 指数 参数定义 Number底数 可以为任意实数 Power指数 底数按该指数次幂乘方 要点 可以用 运算符代替函数POWER来表示对底数乘方的幂次 例如 5 2 例24Xy x2公式显示 2 56 25 power B334 2 0 50 25 00 0 50 25 2 56 25 ABCDEFGHIJ 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 常用Excel函数详解及应用实例 第 19 页 共 90 页 18 PRODUCT18 PRODUCT 所所有有数数字字相相乘乘的的乘乘积积 函数定义 计算出作为参数指定的所有Number的乘积 使用格式 PRODUCT number1 number2 number30 格式简义 PRODUCT 为1到30个需要相乘的数字参数 参数定义 number指定想要乘积的值 或单元格引用 也可指定单元格区域 参数数量和SUM一样 30个 要点 忽略参数中的文本和空值 然后求所有参数的乘积 注意事项 1 当参数为数字 逻辑值或数字的文字型表达式时可以被计算 当参数为错误值或是不能转换成数 字的文字时 将导致错误 2 如果参数为数组或引用 只有其中的数字将被计算 数组或引用中的空白单元格 逻辑值 文本 或错误值将被忽略 例25数据1 数据2 542250 PRODUCT B351 B354 Excel被忽略 1564320000 PRODUCT B351 B354 C351 C354 308 所有数相乘 即 5 15 30 4 6 8 10 Excel10 例26用函数计算营业额和折扣后营业额 促销折扣 0 95 品名件单价营业额促销折扣 A产品3139417396 15 PRODUCT C358 D358 营业额 B产品635210199 5 PRODUCT C358 D358 G 356 促销折扣 C产品2186372353 4 D产品599495470 25 19 QUOTIENT19 QUOTIENT 商商的的整整数数部部分分 函数定义 返回商的整数部分 该函数可用于舍掉商的小数部分 使用格式 QUOTIENT numerator denominator 格式简义 QUOTIENT 被除数 除数 参数定义 numerator被除数 denominator除数 要点 1 如果任一参数为非数值型 函数QUETIENT返回错误值 VALUE 2 此参数只能是一个单元格 不能是单元格区域 注意事项 如果该函数不可用 并返回错误值 NAME 请安装并加载 分析工具库 加载宏 例27被除数除数商商的整数部分 186920 666720 IF ISERROR B373 C373 B373 C373 商 230 66670 QUOTIENT B373 C373 商的整数部分 156 2 5000 2 3 50 DIV 0 除数为0 880 997 777897文本数字 20 RADIANS20 RADIANS 角角度度转转换换为为弧弧度度 函数定义 将角度转换为弧度 使用格式 RADIANS angle 格式简义 RADIANS 目标单元格 参数定义 Angle为需要转换成弧度的角度 注意事项 如果参数为非数值型或单元格区域 则函数RADIANS返回错误值 VALUE 例28将角度转换为弧度 角度 901803600 90 360 公式显示 弧度 1 57079633 3 1415927 6 28318530 1 5707963 6 28319 RADIANS C386 21 RAND RANDBETWEEN 21 RAND RANDBETWEEN 随随机机数数 指指定定数数之之间间的的一一个个随随机机数数 函数定义 RAND返回大于等于0及小于1的均匀分布随机数 RANDBETWEEN 返回位于两个指定数之间的一个随机数 使用格式 RAND RANDBETWEEN bottom top 格式简义 RAND RANDBETWEEN 最小整数 最大整数 参数定义 bottom函数RANDBETWEEN将返回的最小整数 top函数RANDBETWEEN将返回的最大整数 注意事项 函数RAND 1 若要生成a与b之间的随机实数 请使用 RAND b a a ABCDEFGHIJ 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 常用Excel函数详解及应用实例 第 20 页 共 90 页 2 如果要使用函数RAND生成一随机数 并且使之不随单元格计算而改变 可以在编辑栏中输入 RAND 然后按F9 将公式永久性地改为随机数 例29 1 产生0 1以内的随机数 2 产生2000年 2005年以内的日期随机数 0 3552266 RAND 2004 5 26 INT RAND 38717 36526 36526 1 RANDBETWEEN 0 1 2001 7 12 RANDBETWEEN 36526 38717 22 ROUND22 ROUND ROUNDDOWN ROUNDUP ROUNDDOWN ROUNDUP 按按指指定定位位数数取取整整 向向下下取取整整 向向上上取取整整 函数定义 ROUND返回某个数字按指定位数取整后的数字 ROUNDDOWN靠近0值 向下 绝对值减小的方向 舍入数字 ROUNDUP远离0值 向上舍入数字 使用格式 ROUND number num digits 格式简义 ROUND 目标单元格 要保留位数 参数定义 number指定数值或数值所在的单元格引用 任意实数 num digits四舍五入的位数的位置 要点 ROUND 若num digits 0 则四舍五入到指定的小数位 若 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产品251393475 所有种类含有A产品的销售 件 B产品BBB635210 5 SUMIF B640 B646 A产品 C640 C646 C产品2186372 A产品 后缀名三个字 销售 件 A产品CCC599495 73 SUMIF C640 C646 5 C640 C646 A产品 11 3583938 销售大于等于5件的销售合计 件 A产品DD 18 681224 A产品 SUMIF B 640 E 646 F645 E 640 E 646 C产品 8 29232 7413 可以设置产品系列查询 例43多区域求和 数组 工号商品销售量工号商品销售量工号商品销售量 A001 A产品194 A001 产品B61 A002内裤 179 B001 产品B40 B001 C产品41 B001 A产品97 A002 C产品100 B002 A产品19 A002 产品B182 B001 A产品CCC130 B001 产品BBB101 B001 C产品54 B002 A产品110 B002 C产品124 B002 A产品CCC130 A002 A产品DD74 A002 A产品CCC179 B003 A产品61 A003 C产品100 A001 A产品135 A003 A产品DD82 A001 产品BBB143 A002 A产品DD146 A001 产品B158 A002 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产品的