高级函数汇总.xls

数数学学函函数数示例 SUBTOTAL SUM 计算单元格区域中所有数值的和 SUMIF 计算单元格区域中所有数值的和 SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 POWER幂函数 210 POWER 2 10 1024 1024 LOG求对数 LOG21024 LOG 1024 2 10 10 FACT 求阶乘 例 FACT 5 120 120 逻逻辑辑函函数数 AND 判断参数的逻辑值 参数只要一个为假 则输出 为假 参数全为真 则输出为真 OR只要一个为真 则输出为真 全假则输出为假 NOT FALSE IF 条件函数 TRUE 文文本本 日日期期 时时间间函函数数 MID从第几个字节开始取 共取几个abcdefgbcd LEFT 取左边的字节 abcdefgab RIGHT 取右边的字节 abcdefgefg HOUR 返回时间值的小时数 即一个介于 0 12 00 A M 到 23 11 00 P M 之间的整数 11 MINUTE 返回时间值中的分钟 即一个介于 0 到 59 之间 的整数 43 DATE 等于日期2006 9 9 NOW 等于当前时间 没有参数 2006 11 30 11 43 TODAY 等于当前日期 没有参数 2006 11 30 WEEKDAY 返回星期几4 UPPER TEXT 将字符转为大写函数 abcdefgABCDEFG LOWER TEXT 将字符转为小写函数 ABCDEFGabcdefg SEARCH 返回从 start num 开始首次找到特定字符或文本 串的位置上特定字符的编号 第几个编号 abcdefg4 LEN返回文本串中的字符个数 abcdefg7 VALUE text 将代表数字的文字串转换成数字 查查询询与与引引用用函函数数 HLOOKUP 在表格或数值数组的首列查找指定的数值 并由 此返回表格或数组当前行中指定行处的数值 水平方向查找 VLOOKUP 在表格或数值数组的首列查找指定的数值 并由 此返回表格或数组当前行中指定列处的数值 垂直方向查找 LOOKUP 从单行或单列区域或者从一个数组返回值 MATCH 返回在指定方式下与指定数值匹配的数组 MOD返回两数相除的余数 结果的正负号与除数相同 220 ROW返回指定引用的行标 43 ROWS返回引用或数组的行数 2 COLUMN返回指定引用的列标 3 CEILING将数值向上取舍最接近的参数或倍数 7880 FLOOR将数值向下取舍最接近的参数或倍数 7870 INDEX 返回表或区域中的值或值的引用将数值向下取舍最接近的参数或倍数 统统计计函函数数 AVERAGE求参数的算术平均值函数 21 AVERAGEA 求参数的算术平均值函数 包含所有不为空的单 元格 1 51 TRIMMEAN 求数据集的内部平均值 函数TRIMMEAN先从数据 集的头部和尾部除去一定百分比的数据点 然后 再求平均值 当希望在分析中剔除一部分数据的 计算时 可以使用此函数 比如 我们在计算选 手平均分数中常用去掉一个最高分 去掉一个最 低分 XX号选手的最后得分 就可以使用该函数 来计算 COUNT 用于求单元格个数的统计函数 有效数值单元格 字符除外 COUNTA 用于求单元格个数的统计函数 非空单元格个数 包括数值 字符等单元格 COUNTIF用于求某个区域中给定条件的单元格数目 COUNTBLANK 用于求单元格个数的统计函数 统计空单格个个 数 FREQUENCY求区域中数据的频率分布 MAX求数据集的最大值MAX MIN求数据集的最小值MIN LARGE求数据集中第K个最大值LARGE SMALL求数据集中第k个最小值SMALL MODE求数据集中出现频率最多的数 MEDIAN求数据集中的中位数 RANK一个数值在一组数值中的排位的函数 PERCENTRANK求特定数值在一个数据集中的百分比排位的函数 财财务务函函数数 FV 基于固定利率及等额分期付款方式 返回 某项投资的未来值 NPV 基于一系列现金流和固定的各期贴现率 返回一项投资的净现值 投资的净现值是 指未来各期支出 负值 和收入 正值 的当前值的总和 DB 使用固定余额递减法 计算一笔资产在给 定期间内的折旧值 PMT 计算在固定利率下 固定代款额的分期偿 还额 PV 返回投资的现值 现值为一系列未来付款 的当前值的累积和 例如 借入方的借入 款即为贷出方贷款的现值 数数据据库库函函数数 DCOUNT 计算列表或数据库的列中满足指定条件并 且包含数字的单元格个数 条件可为表达式 DMAX 计算列表或数据库的列中满足指定条件并 且包含数字的最大数 DSUM 返回数据清单或数据库的指定列中 满足给定条 件单元格中的数字之和 DAVERAGE 返回数据库或数据清单中满足给定条件的数据列 中数值的平均值 INT将数字向下舍入到最接近的整数3 ABS 工作表函数返回参数的绝对值 ACOS 工作表函数返回数字的反余弦值 ACOSH 工作表函数返回参数的反双曲余弦值 ASIN 工作表函数返回参数的反正弦值 ASINH 工作表函数返回参数的反双曲正弦值 ATAN 工作表函数返回参数的反正切值 ATAN2 工作表函数返回给定的 X 及 Y 坐标值的反正切值 ATANH 工作表函数返回参数的反双曲正切值 CEILING 工作表函 数 将参数 Number 沿绝对值增大的方向 舍 入为最接近的整数或基数 COMBIN 工作表函数 计算从给定数目的对象集合中提取若干对 象的组合数 COS 工作表函数返回给定角度的余弦值 COSH 工作表函数返回参数的双曲余弦值 COUNTIF 工作表函 数 计算给定区域内满足特定条件的单元格的 数目 DEGREES 工作表函 数 将弧度转换为度 EVEN 工作表函数 返回沿绝对值增大方向取整后最接近的偶 数 EXP 工作表函数 返回 e 的 n 次幂常数 e 等于 2 71828182845904 是自然对数的底数 FACT 工作表函数 返回数的阶乘 一个数的阶乘等于 1 2 3 该数 FACTDOUBLE 工作表 函数 返回参数 Number 的半阶乘 FLOOR 工作表函数 将参数 Number 沿绝对值减小的方向去尾 舍入 使其等于最接近的 significance 的倍数 GCD 工作表函数返回两个或多个整数的最大公约数 INT 工作表函数返回实数舍入后的整数值 LCM 工作表函数返回整数的最小公倍数 LN 工作表函数 返回一个数的自然对数自然对数以常数项 e 2 71828182845904 为底 LOG 工作表函数按所指定的底数 返回一个数的对数 LOG10 工作表函数返回以 10 为底的对数 MDETERM 工作表函 数 返回一个数组的矩阵行列式的值 MINVERSE 工作表函 数 返回数组矩阵的逆距阵 MMULT 工作表函数返回两数组的矩阵乘积结果 MOD 工作表函数 返回两数相除的余数结果的正负号与除数 相同 MROUND 工作表函数 返回参数按指定基数舍入后的数值 MULTINOMIAL 工作 表函数 返回参数和的阶乘与各参数阶乘乘积的比 值 ODD 工作表函数返回对指定数值进行舍入后的奇数 PI 工作表函数 返回数字 3 14159265358979 即数学常 数 pi 精确到小数点后 15 位 POWER 工作表函数返回给定数字的乘幂 PRODUCT 工作表函 数 将所有以参数形式给出的数字相乘 并返 回乘积值 QUOTIENT 工作表函 数 回商的整数部分 该函数可用于舍掉商的 小数部分 RADIANS 工作表函 数 将角度转换为弧度 RAND 工作表函数 返回大于等于 0 小于 1 的均匀分布随机 数 RANDBETWEEN 工作 表函数 返回位于两个指定数之间的一个随机数 ROMAN 工作表函数将阿拉伯数字转换为文本形式的罗马数字 ROUND 工作表函数返回某个数字按指定位数舍入后的数字 ROUNDDOWN 工作表 函数 靠近零值 向下 绝对值减小的方向 舍 入数字 ROUNDUP 工作表函 数 远离零值 向上 绝对值增大的方向 舍 入数字 SERIESSUM 工作表 函数 返回基于以下公式的幂级数之和 SIGN 工作表函数 返回数字的符号当数字为正数时返回 1 为零时返回 0 为负数时返回 1 SIN 工作表函数返回给定角度的正弦值 SINH 工作表函数返回某一数字的双曲正弦值 SQRT 工作表函数返回正平方根 SQRTPI 工作表函数 返回某数与 pi 的乘积的平方根 SUBTOTAL 工作表函 数 返回数据清单或数据库中的分类汇总 SUM 工作表函数返回某一单元格区域中所有数字之和 SUMIF 工作表函数根据指定条件对若干单元格求和 SUMPRODUCT 工作表 函数 在给定的几组数组中 将数组间对应的元 素相乘 并返回乘积之和 SUMSQ 工作表函数返回所有参数的平方和 SUMX2MY2 工作表函 数 返回两数组中对应数值的平方差之和 SUMX2PY2 工作表函 数 返回两数组中对应数值的平方和之和 平 方和加总在统计计算中经常使用 SUMXMY2 工作表函 数 返回两数组中对应数值之差的平方和 TAN 工作表函数返回给定角度的正切值 TANH 工作表函数返回某一数字的双曲正切值 TRUNC 工作表函数将数字的小数部分截去 返回整数 3 FF23 FF23 将数值向下取舍最接近的参数或倍数 身份证号码性别年龄 362137790306053男2727 求性别IF LEN A2 15 IF MOD VALUE RIGHT A2 1 2 1 男 女 IF MOD VALUE MID A2 17 1 2 1 男 女 求年龄IF LEN A2 15 YEAR NOW VALUE 19 Divisor为除数 MID函数返回文本字符串中从指定位置开始的特定数目的字符 该数目由用户指定 语法 MID text start num num chars Text为包含要提取字符的文本字符串 Start num为文本中要提取的第一个字符的位置 文本中第一个字符的start num为1 以此类推 Num chars指定希望MID从文本中返回字符的个数 2 实例分析 为了适应上述情况 必须设计一个能够适应两种身份编码的性别计算公式 在D2单元格中输入 IF LEN C2 15 IF MOD MID C2 15 1 2 1 男 女 IF MOD MID C2 17 1 2 1 男 女 回车后即可在单元格获得该职工的性别 而后只要把公式复制到D3 D4等单元格 即可得到其他职工的性别 IF LEN A2 15 IF MOD VALUE RIGHT A2 1 2 1 男 女 IF MOD VALUE MID A2 17 1 2 1 男 女 IF LEN A2 15 YEAR NOW VALUE 19 Start num为文本中要提取的第一个字符的位置 文本中第一个字符的start num为1 以此类推 Num chars指定希望MID从文本中返回字符的个数 为了适应上述情况 必须设计一个能够适应两种身份编码的性别计算公式 在D2单元格中输入 IF LEN C2 15 IF MOD MID C2 15 1 2 1 男 女 IF MOD MID C2 17 1 2 1 男 女 回车后即可在单元格获得该职工的性别 而后只要把公式复制到D3 D4等单元格 即可得到其他职工的性别 为了适应上述情况 必须设计一个能够适应两种身份编码的性别计算公式 在D2单元格中输入 IF LEN C2 15 IF MOD MID C2 15 1 2 1 男 女 IF MOD MID C2 17 1 2 1 男 女 回车后即可在单元格获得该职工的性别 而后只要把公式复制到D3 D4等单元格 即可得到其他职工的性别 某某某某公司 工资计算表 2006年1月 总经理 人事 制表 村夫 员工代码姓名部门岗位工资标准 上班 天数 基本工资 加班 时间 加班工 资 销售 数量 销售 工资 奖励应发金额所得税其他扣款应扣金额实发金额 1001行政部员工1总经理3 000 0021 03 000 005 065 03 070 00147 00130277 002 793 00 1002行政部员工2行政部1 200 0021 01 200 0010 064 01 274 00129129 001 145 00 1003行政部员工3行政部1 200 0021 01 200 0015 063 01 278 00128128 001 150 00 1004行政部员工4行政部1 200 0021 01 200 0020 062 01 282 00127127 001 155 00 1005行政部员工5行政部1 200 0021 01 200 0025 061 01 286 00126126 001 160 00 1006行政部员工6行政部1 200 0021 01 200 0030 060 01 290 00125125 001 165 00 1007行政部员工7行政部1 200 0021 01 200 0035 059 01 294 00124124 001 170 00 1008行政部员工8行政部1 200 0021 01 200 0040 058 01 298 00123123 001 175 00 1009行政部员工9行政部1 200 0021 01 200 0045 057 01 302 00122122 001 180 00 1010行政部员工10行政部1 200 0021 01 200 0050 056 01 306 00121121 001 185 00 2001销售部员工1销售部1 500 0021 01 500 0055 055 01 610 000 50120120 501 489 50 2002销售部员工2销售部1 500 0021 01 500 0060 054 01 614 000 70119119 701 494 30 2003销售部员工3销售部1 500 0021 01 500 0065 053 01 618 000 90118118 901 499 10 2004销售部员工4销售部1 500 0021 01 500 0070 052 01 622 001 10117118 101 503 90 2005销售部员工5销售部1 500 0021 01 500 0075 051 01 626 001 30116117 301 508 70 2006销售部员工6销售部1 500 0021 01 500 0080 050 01 630 001 50115116 501 513 50 2007销售部员工7销售部1 500 0021 01 500 0085 049 01 634 001 70114115 701 518 30 2008销售部员工8销售部1 500 0021 01 500 0090 048 01 638 001 90113114 901 523 10 2009销售部员工9销售部1 500 0021 01 500 0095 047 01 642 002 10112114 101 527 90 2010销售部员工10销售部1 500 0021 01 500 00100 046 01 646 002 30111113 301 532 70 2011销售部员工11销售部1 500 0021 01 500 00105 045 01 650 002 50110112 501 537 50 2012销售部员工12销售部1 500 0021 01 500 00110 044 01 654 002 70109111 701 542 30 2013销售部员工13销售部1 500 0021 01 500 00115 043 01 658 002 90108110 901 547 10 2014销售部员工14销售部1 500 0021 01 500 00120 042 01 662 003 10107110 101 551 90 2015销售部员工15销售部1 500 0021 01 500 00125 041 01 666 003 30106109 301 556 70 2016销售部员工16销售部1 500 0021 01 500 00130 040 01 670 003 50105108 501 561 50 2017销售部员工17销售部1 500 0021 01 500 00135 039 01 674 003 70104107 701 566 30 2018销售部员工18销售部1 500 0021 01 500 00140 038 01 678 003 90103106 901 571 10 2019销售部员工19销售部1 500 0021 01 500 00145 037 01 682 004 10102106 101 575 90 2020销售部员工20销售部1 500 0021 01 500 00150 036 01 686 004 30101105 301 580 70 2021销售部员工21销售部1 500 0021 01 500 00155 035 01 690 004 50100104 501 585 50 2022销售部员工22销售部1 500 0021 01 500 00160 034 01 694 004 7099103 701 590 30 某某某某公司 工资计算表 2006年1月 总经理 人事 制表 村夫 员工代码姓名部门岗位工资标准 上班 天数 基本工资 加班 时间 加班工 资 销售 数量 销售 工资 奖励应发金额所得税其他扣款应扣金额实发金额 2023销售部员工23销售部1 500 0021 01 500 00165 033 01 698 004 9098102 901 595 10 2024销售部员工24销售部1 500 0021 01 500 00170 032 01 702 005 1097102 101 599 90 2025销售部员工25销售部1 500 0021 01 500 00175 031 01 706 005 3096101 301 604 70 3001生产部员工1生产部800 0021 0800 00180 030 01 010 009595 00915 00 3002生产部员工2生产部800 0021 0800 00185 029 01 014 009494 00920 00 3003生产部员工3生产部800 0021 0800 00190 028 01 018 009393 00925 00 3004生产部员工4生产部800 0021 0800 00195 027 01 022 009292 00930 00 3005生产部员工5生产部800 0021 0800 00200 026 01 026 009191 00935 00 3006生产部员工6生产部800 0021 0800 00205 025 01 030 009090 00940 00 3007生产部员工7生产部800 0021 0800 00210 024 01 034 008989 00945 00 3008生产部员工8生产部800 0021 0800 00215 023 01 038 008888 00950 00 3009生产部员工9生产部800 0021 0800 00220 022 01 042 008787 00955 00 3010生产部员工10生产部800 0021 0800 00225 021 01 046 008686 00960 00 3011生产部员工11生产部800 0021 0800 00230 020 01 050 008585 00965 00 3012生产部员工12生产部800 0021 0800 00235 019 01 054 008484 00970 00 3013生产部员工13生产部800 0021 0800 00240 018 01 058 008383 00975 00 3014生产部员工14生产部800 0021 0800 00245 017 01 062 008282 00980 00 3015生产部员工15生产部800 0021 0800 00250 016 01 066 008181 00985 00 3016生产部员工16生产部800 0021 0800 00255 015 01 070 008080 00990 00 3017生产部员工17生产部800 0021 0800 00260 014 01 074 007979 00995 00 3018生产部员工18生产部800 0021 0800 00265 013 01 078 007878 001 000 00 3019生产部员工19生产部800 0021 0800 00270 012 01 082 007777 001 005 00 3020生产部员工20生产部800 0021 0800 00275 011 01 086 007676 001 010 00 3021生产部员工21生产部800 0021 0800 00280 010 01 090 007575 001 015 00 3022生产部员工22生产部800 0021 0800 00285 09 01 094 007474 001 020 00 3023生产部员工23生产部800 0021 0800 00290 08 01 098 007373 001 025 00 3024生产部员工24生产部800 0021 0800 00295 07 01 102 007272 001 030 00 3025生产部员工25生产部800 0021 0800 00300 06 01 106 007171 001 035 00 3026生产部员工26生产部800 0021 0800 00305 05 01 110 007070 001 040 00 3027生产部员工27生产部800 0021 0800 00310 04 01 114 006969 001 045 00 3028生产部员工28生产部800 0021 0800 00315 03 01 118 006868 001 050 00 3029生产部员工29生产部800 0021 0800 00320 02 01 122 006767 001 055 00 某某某某公司 工资计算表 2006年1月 总经理 人事 制表 村夫 员工代码姓名部门岗位工资标准 上班 天数 基本工资 加班 时间 加班工 资 销售 数量 销售 工资 奖励应发金额所得税其他扣款应扣金额实发金额 3030生产部员工30生产部800 0021 0800 00325 01 01 126 006666 001 060 00 这是一张示例工资表 内容当然不完整啦 你可以根据贵单位的实际情况自行增删 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1001行政部员工1 总经理0300021300005 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1002行政部员工2 行政部01200211200010 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1003行政部员工3 行政部01200211200015 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1004行政部员工4 行政部01200211200020 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1005行政部员工5 行政部01200211200025 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1006行政部员工6 行政部01200211200030 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1007行政部员工7 行政部01200211200035 员工代码 姓名部门岗位工资标准 上班天数 基本工资 加班时间 加班工资 1008行政部员工8 行政部01200211200040 IF MOD ROW 3 0 IF MOD ROW 3 1 基本数据 A 1 INDEX date