人力资源管理中的EXCEL函数应用.xls

第 1 页 共 20 页 人人力力资资源源及及行行政政管管理理中中的的EXCELEXCEL函函数数应应用用 序序号号函函数数类类别别分分序序号号函函数数名名称称用用 途途 1数学 1 1SUM number1 number2 计算某个目标区域中单元格里数字的总和 1 2ROUND number num digits 设定指定位数 按此位数对目标数字进行四舍五入取整 1 3MOD number divisor 对目标值除以指定数后的余数 余数的符号和除数相同 1 4INT number 对目标数字进行舍入处理 处理的结果是得到小于目标数的最大整数 1 5ROUNDUP number num digits 对目标数字按照指定的条件进行相应的舍入处理 1 6SUMPRIDUCT array1 array2 对几组给定的数组对应的元素相乘 所得结果再进行加总 2查询和引用 2 1VLOOKUP lookup value table array col index num range lookup 在给定区域的首列里查找目标数值 然后返回目标数值所在行里某一列的 相关值 2 2HLOOKUP lookup value table array row index num range lookup 在给定区域的首行里查找目标数值 然后返回目标数值所在列里某一行的 相关值 2 3 向量形式 LOOKUP lookup value lookup vector result vector 事先确定两个单行或单列区域 将其中的一个作为查找区域 然后进行目 标数值查找 最后返回目标数值所在列或行与吃一堑一个区域相关的单元 格内容 数组形式 lOOKUP lookup value array 2 4COLUMN reference 返回目标单元格或单元格区域 可返回列序号 2 5ROW reference 返回目标单元格或单元格区域 的行序号 2 6 数组形式 INDEX array row num column num 按照相关条件返回目标区域里的值 引用形式 INDEX reference row num column num area num 2 7OFFSET reference rows cols height width 将给定的区域作为目标引用区域 同时设定一个偏移量 然后依据该偏移 量得到新的引用 最终的结果包括单元格和单元格区域的形式 3文本 3 1TEXT value format text 依据需要将目标数字转换为指定的文本格式 3 2MID text start num num chars 在目标字符串中指定一个开始位置 按设定的数值返回该字符串中的相应 数目字符内容 3 3LEN text 计算目标字符中的字符数 3 4LEFT text num chars 从字符串的左侧开始 按照指定的数值返回相应数目的字符内容 3 5RIGHT text num chars 从字符串的右侧开始 按指定的数值返回相应数目的字符内容 3 6FIND find text within text start num 查找字符串中首字符编号为1 接着对余下的字符按顺序依次进行编号 然后在查找字符串中查找目标字符 并且返回目标字符在查找字符串中的 编号 3 7SUBSTITUTE text old text new text instance num 将目标字符或字符串替换到需要被替换的字符串中 4统计 4 1RANK number ref order 在某个区域内对目标数字进行排位计算 4 2COUNTIF range criteria 在目标区域中统计满足预设条件的单元格数目 4 3FREQUENCY data array bins array 计算目标数值在某个区域内出现的次数 然后返回一个重直组 5日期和时间 5 1YEAR serial number 返回给定日期所属的年份 5 2MONTH serial number 返回给定日期所属的月份 5 3DAY serial number 返回给定日期所属的目体天数 第 2 页 共 20 页 人人力力资资源源及及行行政政管管理理中中的的EXCELEXCEL函函数数应应用用 序序号号函函数数类类别别分分序序号号函函数数名名称称用用 途途 5日期和时间 5 4NOW 显示系统当前的日期和时间 5 5TODAY 显示系统当前的日期 5 6NETWORKDAYS start date end date holidays 计算两个给定参数间的工作日数值 5 7EOMONTH start date months 给定一个起始日期 再辅以一个指定数字 将起始日期的月份向前或向后 拨运该数字大小距离 最后返回所得月份的最后一天日期 5 8WORKDAY start date days holidays 返回某个日期前后相隔指定工作日的日期值 5 9TIME hour minute second 返回某一个特定时间的小数值 5 9 1DATEDIF start date end date unit 计算起始日期和截止日期之间的天数 月数和年数 6逻辑 6 1IF logical test value if true value if false 用于对目标区域进行判断 返回真假逻辑值 据此输出相应的结果 6 2NOT logical 对单元格或单元格区域内的逻辑值求反 6 3OR logical1 logical2 对指定区域内的逻辑值进行判断 当逻辑值为TRUE时返回TRUE 若逻辑 值为FALSE则返回FALSE 7信息ISERROR value 判断测试内容是否为错 8数据库DSUM database field criteria 在指定区域查找符合条件2的区域 然后在查找到的区域内计算符合条件1 的数值之和 9行政管理 9 1Excel抽奖器 9 2公议室使用安排表 9 3办公室布局平面图 第 3 页 共 20 页 数数学学函函数数的的应应用用 1 1 SUMSUM函函数数 用途 计算某个目标区域中单元格里数字的总和 语法 sum number1 number2 number1 number2 为需要计算的参数 number数一共可以有30个 说明 对于计算区域里的内容 其表达形式可以是数字也可以是逻辑值 若计算的参数为数组形式 那么只计算数组中的数字 同样 若计算的参数为引用类型 那也只计算引用类型中的数字 简单示例 假设在B2 B5区域里分别输入了 2 55 24 和 12 然后在C5单元格里输入下列公式 观察出现的结果 公式说明 结果 sum 12 3 将数字12和数字3相加 15 sum B2 B5 将B2至B5单元格里的数据相加 93 扩展知识点讲解 一 快速获得简单合计的另外几种方法 1 单击 格式 工具栏中的 自动求和 按钮 此时在求和目标列的下方单元格自动显示求和公式 按键确认即可获得合计的结果 2 在求和目标列的下方单元格按组合键 即显示求和公司 按键确认即可获得合计的结果 3 选中求和目标列 在状态栏里 右下方 已显示合计的结果 Excel 2003在默认状态下此功能打开且为 求和 二 SUM函数计算区域的交叉合计 非连续区域合计 1 交叉合计操作 指定区域公共部份求和 1234指定区域 B24 E26指定区域如方框示 2345C23 C27公共区域如黄色部份 3456公式 sum B24 E26 C23 C27 4567注 公式里 B24 E26 和 C23 C27 之间留有一个空格 5678结果 12 2 非连续区域合计操作 指定区域求和 指定区域 B31 B33指定区域如方框示 1234D30 E34 2345公式 sum B31 B33 D30 E34 3456注 指定的区域 B31 B33 和 D30 E34 之间要用逗号隔开 4567结果 64 5678 三 SUM函数应用 1 SUM函数直接求和 2 SUM结合IF COUNTIF函数编制中国式排名公式例 P100 3 应用内存数组公式计算带薪年假天数例 P142 2 2 ROUNDROUND函函数数 第 4 页 共 20 页 用途 设置指定位数 按此位数对目标数字进行四舍五入取整 然后返回相应的结果 语法 ROUND number num digits number为目标数字 num digits为指定的位数 系统将按此位数进行四舍五入 说明 如果num digits小于 等于或大于0 都会有不同的结果 简单示例 公式说明 结果 ROUND 3 1415 1 将3 1415四舍五入到一位小数 3 1 ROUND 3 1415 0 将3 1415四舍五入到整数 3 ROUND 314 15 1 将314 15四舍五入到小数点左侧一位 310 ROUND函数的应用 1 应用ROUND结合TEXT函数舍入计算加班时间例 P109 2 应用ROUND函数计算应发工资例 P134 3 3 MODMOD函函数数 用途 求对目标值除以指定数后的余数 余数的符号和除数相同 语法 MOD number divisor number为目标数值 作为被除数 divisor为指定数 作为除数 说明 当指定数为0时 系统将显示错误值 DIV 0 简单示例 32 2 公式说明 结果 MOD B61 C61 3除以2余数为1 又因为除数2的符号为正 1 MOD B61 D61 3除以 2余数为1 又因为除数 2的符号为负 1 MOD B61 0 除数为0 DIV 0 MOD函数的应用 1 应用MOD函数编制工资条例 P162 4 4 INTINT函函数数 用途 对目标数字进行舍入处理 处理的结果是得到小于目标数的最大整数 语法 INT number number为需要处理的目标数字 也可以是含数字的单元格引用 说明 向下舍入 简单示例 公式说明 结果 INT 3 1415 将数字3 1415向下舍入到最接近的整数 3 INT 3 1415 将数字 3 1415向下舍入到最接近的整数 4 INT 3 1415 INT 3 1415 先将第二个INT函数内的数字3 1415向下舍入到最接近的整数3 然后用数字3 1415减去该数 字3 得到数字0 1415 最后当数字0 1415由第一个INT函数进行向下舍入 0 INT函数的应用 1 应用INT结合ROUNDUP SUM函数计算零钞例 P170 5 5 ROUNDUPROUNDUP函函数数 第 5 页 共 20 页 用途 对目标数字接照指定的条件进行相应的舍入处理 不要求四舍五入的数字进位 语法 ROUNDUP number num digits number为需要处理的目标数字 num digits为指定的条件 将决定目标数字处理后的结果位数 说明 num digits小于 等于或大于0 都会有不同的结果 简单示例 公式说明 结果 ROUNDUP 3 1415 0 向上舍入到最接近的整数 4 ROUNDUP 3 1415 3 舍入到小数点千位上 3 142 ROUNDUP 314 15 2 在小数点左侧舍入两位 400 ROUNDUP 314 15 1 在小数点左侧舍入一位 320 ROUNDUP 3 1415 0 向上舍入到最接近的整数 4 ROUNDUP函数的应用 1 应用ROUNDUP函数计算零钞例 P170 6 6 SUMPRODUCTSUMPRODUCT函函数数 用途 将几组给定的数组对应的元素相乘 所得结果再进行加总 语法 SUMPRODUCT array1 array2 array3 array1 array2 为给定数组 可以包含30个数组 说明 给定的数组应当是含用相同维数的数组 否则将返回错误值 VALUE 对于不是数值形式 比如文本形式的元素 该函数将其统统视为0 简单示例 5623 3255 6732 公式说明 结果 SUMPRODUCT B103 B105 C103 C105 D103 D105 3个数组的所有元素对应相乘 然后将乘积相加 即5X6X2 3X2X5 6X7X3 216 SUMPRODUCT B103 C105 C103 E105 两组数组的所有元素对应相乘 然后将乘积相加 即5X2 3X5 6X3 6X3 2X5 7X2 85 SUMPRODUCT函数的应用 1 应用SUMPRODUCT函数查询某年龄阶段例 P246 第 6 页 共 20 页 查查询询和和引引用用函函数数的的应应用用 1 1 VLOOKUPVLOOKUP函函数数 用途 在给定区域的首列里查找目标数值 然后返回目标数值所在行里某一列的相关值 语法 VLOOKUP lookup value table array col index num range lookup lookup value为目标数值 table array为给定区域 即查找区域 系统将在给定区域的首列中查找目标数值 col index num为指定返回给定区域中某一列的序号 比如该数值取3时 意思是返回给定区域中第三列的值 range lookup规定查找类型 说明 range lookup为TRUE或省略 VLOOKUP函数将进行近似匹配查找 range lookup为FALSE或0 VLOOKUP函数将进行精确匹配查找 range lookup为TRUE或省略时 给定区域内首列值要以升序排列 注意要查找的目标数值的大小应该是比查找区域内的最小值大 否则应用VLOOKUP函数时会显示错误值 简单示例 1 第一名张公式运算结果 第一名第一名 4 3 第四名李陈 2 9 第三名陈 5 第五名王 2 第二名滕 公式说明 结果 VLOOKUP 1 B11 D15 2 FLASE 使用精确匹配查找B11 D15单元格区域中首列 即B列中的数值 1 然后返回同一行中C列 的值 第一名 VLOOKUP 3 B11 D15 3 TRUE 使用近似匹配查找A列中的数值 3 在A列中找到小于3的最大值2 9 然后返回同一行中 D列的值 陈 VLOOKUP函数的应用 1 应用VLOOKUP函数查找销售额所符合奖金比例例 P126 2 应用VLOOKUP函数制作工资条例 P159 2 2 HLOOKUPHLOOKUP函函数数 用途 在给定区域的首行里查找目标数值 然后返回目标数值所在列里某一行的相关值 功能相似 VLOOKUP是首列查找 HLOOKUP是首行查找 语法 HLOOKUP lookup value table array row index num range lookup lookup value为要查找的目标数值 table array为给定区域 即查找区域 系统将在给定区域的首行中查找目标数值 row index num为指定 返回给定区域中某一行的行号 比如该数值取2时 意味着返回给定区域中第二行的值 说明 range lookup为TRUE或省略 HLOOKUP函数将进行近似匹配查找 range lookup为FALSE或0 HLOOKUP函数将进行精确匹配查找 注意要查找的目标数值的大小应该是在查找区域数值的范围内 否则应用HLOOKUP函数时会显示错误值 简单示例 14 32 952公式运算结果 张王 张李陈王滕 公式说明 结果 HLOOKUP 1 1 B35 F36 2 TRUE 由于行1中没有1 1 故找到小于1 1的最大值1 然后返回同一列中第2行的值 张 HLOOKUP 5 B35 F36 2 FALSE 查找A列中的5 然后返回同一列中第2行的值 王 第 7 页 共 20 页 HLOOKUP 3 1 2 3 a b c d e f 2 TRUE 该列为HLOOKUP函数在数组形式里的应用 在数组常量的第一行中查找3 并返回同列中第2行的值 C 3 3 LOOKUPLOOKUP函函数数 用途 功能是事先确定两个单行或单列区域 将其中的一个作为查找区域 然后进行目标数值查找 最后返回目标数值所在列或行与另一个区域相关的单元格内容 语法 1 向量形式 LOOKUP lookup value lookup vector result vector lookup value为要查找的目标数值 lookup vector为查找区域 result vector为输出结果的区域 其构成和查找区域相同 即同为单行或单列 2 数组形式 LOOKUP lookup value array lookup value为要查找的目标数值 array为查找区域 为数组形式 具体构成内容可以多样化 说明 不同于VLOOKUP函数事先需规定近似或精确匹配查找 LOOKUP函数默认为精确匹配查找 当查找不到目标数值时LOOKUP函数开始近似匹配查找 简单示例 1 向量形式 1 张运算结果 张 4 3 李王 2 9 陈 5 王 2 滕 公式说明 结果 LOOKUP 1 1 B50 B54 C50 C54 由于B列中没有1 1 故找到小于1 1的最大值1 然后返回同一行中C列的值 张 LOOKUP 5 B50 B54 C50 C54 查找B列中的5 然后返回同一行中C列的值 王 2 数组形式 注 数组中的值必须以升序顺序设置 公式说明 结果 LOOKUP c a b c d 1 2 3 4 在数组的第一行中查找 C 查找小于或等于它 C 的最大值 然后返回同一列内最后一行中的值 3 LOOKUP bump a 1 b 2 c 3 在数组的第一行中查找 bump 查找小于或等于它 b 的最大值 然后返回同一行内最后一列中的值 2 4 4 COLUMNCOLUMN函函数数 用途 返回目标单元格或单元格区域 语法 COLUMN reference reference为目标单元格或单元格区域 说明 若reference省略 此时系统返回COLUMN函数所在单元格的列序号 简单示例 运算结果 37 公式说明 结果 COLUMN C70 C列是第3列 3 COLUMN 当前列的列序号 结果选中区域 第 8 页 共 20 页 COLUMN函数应用 应用COLUMN函数得到引用的列序号例 P159 5 5 ROWROW函函数数 用途 返回目标单元格或单元格区域的行序号 该函数与COLUMN函数的功能恰好相反 前者是返回行序号 后者是返回列序号 语法 ROW reference reference为目标单元格或单元格区域 说明 若reference省略 此时系统返回ROW函数所在单元格的行序号 简单示例 运算结果 86 公式说明 结果 ROW 返回当前行的行序号 ROW C86 返回 86 结果选中区域 91运算过程及结果 选中B91 B94 然后输入公式 ROW C91 C94 然后按组合键确认 92 93 94 ROW函数应用 应用ROW函数编制工资条例 P162 6 6 INDEXINDEX函函数数 用途 按照相关条件返回目标区域里的值 分为数组形式和引用形式 数组形式返回由行和列序号索引选定的值 引用形式返回特定行和列交叉处单元格的引用 数组形式语法 INDEX array row num column num array是目标单元格区域或数组 Row num为目标区域中待返回值的行序号 column为目标区域中待返回的列序号 例 当row num为2 column为1时 返回目标区域第二行第一列中的数值 说明 若省略row num INDEX函数将返回整列的元素 若省略column num INDEX函数将返回整行的元素 简单示例 张第一名运算结果 第一名 陈第二名孙 孙第三名 公式说明 结果 INDEX B105 C107 1 2 系统将返回B105 B107区域里的第一行与第2列交叉单元格里的元素 即C105单元格里的内容 第一名 INDEX B105 C107 3 1 系统将返回B105 B107区域里的第三行与第1列交叉单元格里的元素 即B107单元格里的内容 孙 引用形式语法 INDEX reference row num column num area num reference是目标单元格区域里的引用 row num是要从中返回目标单元格区域中的行序号 column num是要从中返回目标单元格区域中的列序号 例 当row num为2 column num为1时 则返回目标区域第二行第一列中的数值 因为在引用形式下 INDEX函数可以对若干个区域进行引用 所以area num用来指明从第几个区域进行引用 说明 若省略row num INDEX函数将返回整列的元素 若省略column num INDEX函数将返回整行的元素 将INDEX函数和其他的公式组合使用 比如利用INDEX函数的结果作为另一个函数的控制条件 就可以满足工作中复杂功能的需求 第 9 页 共 20 页 简单示例 张第一名12 运算结果 第一名 陈第三名12 5陈 孙第二名12 3 公式说明 结果 INDEX B122 C124 B122 C123 1 2 2 系统将返回第2个区域 即B122 C123区域里的第1行与第2列交叉单元格里的元素 即C122单元格里的内容 第一名 INDEX B122 C124 B122 C123 2 1 1 系统将返回第1个区域 即B122 C124区域里的第2行与第1列交叉单元格里的元素 即B123单元格里的内容 陈 7 7 OFFSETOFFSET函函数数 用途 将给定的区域作为目标引用区域 同时设定一个偏移量 然后依据该偏移量得到新的引用 最终的结果包括单元格和单元格区域的形式 语法 OFFSET reference rows cols height width reference是目标引用区域 可以是单元格 也可以是单元格区域 rows是相对于目标引用区域里的左上角单元格上 下 偏移的行数 行数可为正数 代表在起始引用的下方 或负数 代表在起始引用的上方 cols是相对于目标引用区域里的左上角单元格左 右 偏移的列数 列数可为正数 代表在起始引用的右边 或负数 代表在起始引用的左边 height为高度 即返回的引用区域的行数 Height必须为正数 width为宽度 即返回的引用区域的列数 Width必须为正数 说明 如果行数和列数偏移量超出了工作表边缘 OFFSET函数将返回错误值 REF 如果省略了height或width 则假设其高度或宽度与reference相同 OFFSET函数实际上并不移动任何一个单元格或更改选取定区域 它只是返回一个引用 OFFSET函数可用于任何需要将引用作为参数的函数 简单示例 161116运算结果 18 27121737 381318 491419 5101520 公式说明 结果 OFFSET C141 3 2 1 1 从C141单元格出发 先向下偏移3个单元格单位 再向右偏移2个单元格单位 因为height和 width都为1 所以返回E144单元格里的内容 18 SUM OFFSET C141 3 2 2 1 从C141单元格出发 先向下偏移3个单元格单位 再向右偏移2个单元格单位 因为height为2 width为1 所以返回E4 E5单元格区域 然后函数SUM对该区域里的数值进行加总 37 OFFSET函数应用 应用OFFSET函数得到新引用 第 10 页 共 20 页 文文本本函函数数的的应应用用 1 1 TEXTTEXT函函数数 用途 依据需要将目标数字转换为指定的文本格式 语法 TEXT value format text value为目标数值 可以为数字 也可以为计算结果 format text为读者需要设定的文本格式 该格式设置方式 按组合键弹出 单元格格式 对话框 单击 数字 选项卡 在 分类 列表框中选中文本形式的数字格式 最后单击 确定 按钮即可 简单示例 工业45 运算结果 工业占总GDP比重45 服务业1000亿服务业的GDP为1000亿 公式说明 结果 B7 占总GDP比重 TEXT C7 0 将B7和C7单元格里的内容以及引号间的内容合并为一句 工业占总GDP比重的45 B8 的GDP为 TEXT C8 将B8和C8单元格里的内容以及引号间的内容合并为一句 服务业的GDP为1000亿 TEXT函数的应用 结合TEXT函数舍入计算加班时间例 P109 2 2 MIDMID函函数数 用途 在目标字符串中指定一个开始位置 按设定的数值返回该字符串中的相应数目字符内容 语法 MID text start num num chars text是目标字符串 start num是字符串中开始的位置 通常start num是从字符串的首个字符编号 依次为1 2 3 start num有取值范围 不能大于字符串的长度 不能小于1 num chars指设定的数目 MID函数将按此数目返回相应的字符个数 显然该数值不为负 否则函数将输出错误值 简单示例 wonderful运算结果 wondeerful VALUE 公式说明 结果 MID B22 1 5 提取B22单元格里字符串中的5个字符 且从第一个字符开始提取 wonde MID B22 5 20 提取B22单元格里字符串中的20个字符 且从第5个字符开始提取 因为B22单元格中的字符串 中总字符数是9 所以最多只能从第5个字符开始 将余下的所有字符都提取 erful MID B22 10 2 因为B22单元格中的字符串中总字符数是9 此时要从第10个字符开始提取 所以系统返回空格 MID B22 0 2 因为start num小于1 所以系统返回错误值 VALUE 3 3 LENLEN函函数数 用途 计算目标字符中的字符数 语法 LEN text text是目标字符串 说明 空格也视为字符 加到字符串的字符数中 简单示例 wonderfulExcel word运算结果 公式说明 结果 LEN C34 返回C34单元格里字符串的长度 9 LEN D34 返回D34单元格里字符串的长度 10 应用TEXT结合MID LEN函数从身份证号中提出生日信息例 P232 4 4 LEFTLEFT函函数数 第 11 页 共 20 页 用途 从字符串的左侧开始 按照指定的数值返回相应数目的字符内容 语法 LEFT text num chars text是目标字符串 num chars是指定的数值 该数值必须大于或等于0 简单示例 wonderful运算结果 wondwwonderful 公式说明 结果 LEFT B43 4 对A3单元格里的字符串 按从左到右提取前4位字符 wond LEFT B43 因为省略了num chars 因此系统假定其为1 将提取A3单元格里字符串左侧的首字符 W LEFT B43 11 因为指定数值大于目标字符的总数 所以系统将提取目标字符的所有字符 wonderful 5 5 RIGHTRIGHT函函数数 用途 从字符串的右侧开始 按指定的数值返回相应数目的字符内容 该函数恰好与LEFT函数的功能相反 语法 RIGHT text num chars text是目标字符串 num chars是指定的数值 该数值必须大于或等于0 简单示例 wonderful运算结果 derfull 公式说明 结果 RIGHT B53 6 对B53单元格里的字符串 按从右到左提取6位字符 derful RIGHT B53 因为省略了num chars 因此系统假定其为1 将提取A3单元格里字符串右侧的首字符 1 6 6 FINDFIND函函数数 用途 查找字符串中首字符编号为1 接着对余下的字符按顺序依次进行编号 然后在查找字符串中查找目标字符 并且返回目标字符在查找字符串中的编号 语法 FIND find text within text start num find text是目标字符 within text是查找字符串 start num是查找字符串中开始查找的起始位置 Start num有取值范围 要大于0 同时又不能大于查找字符串的长度 说明 FIND函数区分大小写 简单示例 subsititute运算结果 2 VALUE VALUE 9 公式说明 结果 FIND u B65 因为忽略了start num 故而系统假设start num为1 所以查找的是字符串里第一个 u 的位置 2 FIND u B65 0 因为start num为0 所以系统会显示错误值 VALUE FIND A B65 3 因为目标字符在查找字符串中没有出现 所以FIND函数会显示错误值 VALUE FIND u B65 5 从字符串的第5个字符开始查找第一个 u 的位置 9 7 7 SUBSTITUTESUBSTITUTE函函数数 第 12 页 共 20 页 用途 将目标字符或字符串替换到需要被替换的字符串中 语法 SUBSTITUTE te