Excel人力资源管理函数说明.xls

培培训训成成绩绩统统计计分分析析表表 IFIF函函数数 函函数数用用途途 此函数又称条件函数 改函数用于对目标区域进行判断 返回真假逻辑值 据此输出相应的结果 函函数数语语法法 IF logical test value if true value if false IF 条件表达式 看也是一个比较式或逻辑式 当条件表达式的逻辑值 为TRUE时的返回值 当条件表达式的逻辑值为FALSE是的返回值 函函数数说说明明 利用IF函数可以构造IF函数嵌套来完成复杂的功能 但是IF函数最多只能 嵌套7层 IF函数还可以喝数组相结合 借此能够进行工作所需的计算 函函数数简简单单示示例例 现假设在A1单元格里输入数字15 在B1单元格里进行如下的操作 观察出 现的结果 公公式式说说明明 结结果果 IF A1 10 真 假 A1单元格里的数字为 15 大于 10 因此逻辑值为真 Y IF A1 SUM 2 3 Y N SUM 2 3 的计算结果为 5 小于A1 单元格里的 15 因此逻辑值为假 N RANKRANK函函数数 函函数数用用途途 次函数的功能是在某个区域对目标数字进行排位计算 函函数数语语法法 RANK number ref order RANK 目标数字 要进行排行的区域 何种排位的方式 order为0或者省略 系统会按降序排列目标数字排位 相反 当order不是0 系统会按升序排 列对目标数字排位 函函数数说说明明 在应用RANK函数的过程中 当存在重复时数会影响后面的排位 函函数数简简单单示示例例 先在假设A1到A7单元格里分别输入如下数字 3 1 2 3 5 7 6 在B1 单元格里进行如下的操作 观察出现的结果 公公式式说说明明 结结果果 RANK A4 A1 A7 1 数字3在列表里排列第3 但因为出现了两 次 影响了数字5的排位 因此数字5在A1 到A7单元格列表里升序排位为5 5 RANK A5 A1 A7 0 7在A1到A7单元格的数字列表里降序排位 为1 1 员员工工销销售售奖奖金金计计算算表表 VLOOKUPVLOOKUP函函数数 函函数数用用途途 此函数的功能是在给定区域的首列里查找目标数值 然后返回目标数值所 在行里某一列的相关数值 函函数数语语法法 VLOOKUP lookup value table array col index num range lookup VLOOKUP 目标数值 给定区域 制定返回给定区域中某一列的序号 用来 规定VLOOKUP函数查找类型 函函数数说说明明 range lookup为TRUE或省略 VLOOKUP函数将进行近似匹配查找 range lookup为FALSE或0 VLOOKUP函数将进行精确匹配查找 range lookup为TRUE或省略时 给定区域内首列值要以生序排列 注意要查找的目标数值的大小应该是比查找区域内的最小值大 否则应用 VLOOKUP函数时会显示错误值 函函数数简简单单示示例例 在A1至A5单元格里分别输入了 1 4 3 2 9 5 和 2 在B1至B5单元格里分别输入了 第一名 第四名 第三名 第 五名 和 第二名 在C1只C5单元格里分别输入了 张 李 陈 王 和 滕 接着在D5单元格里输入如下公式 观察出现结果 公公式式说说明明 结结果果 VLOOKUP 1 A1 C5 2 FALSE 使用精确匹配查找A1 C5单元格区域中首 列 即A列中的数值 1 然后返回同一 行中B列的值 第一名 VLOOKUP 3 A1 C5 3 TRUE 使用近似匹配查找A列中的数值 3 在 A列中找到小于3的最大值2 9 然后返回同 一行中C列的值 陈 LOOKUPLOOKUP函函数数 函函数数用用途途 此函数的功能是事先确定两个单行或单列区域 将其中的一个作为查找区 域 然后进行目标数值查找 最后返回目标数值所在列或行与另一个区域香 蕉的单元格内容 LOOKUP函数具有两种语法形式 即向量形式和数组形式 函函数数语语法法 向量形式 LOOKUP lookup value lookup vector result vector LOOKUP 目标数值 查找区域 输出结果的区域 其构成和查 找区域相同 即同为单行或单列 数组形式 LOOKUP lookup value array LOOKUP 查找的目标数值 查找区域 函函数数说说明明 不同于VLOOKUP函数实现需要规定近似或精确匹配查找 LOOKUP函数默认为 精确匹配查找 当查找不到目标数值是LOOKUP函数开始近似匹配查找 函函数数简简单单示示例例 向量形式 现假设在A1至A5单元格里分别输入了 1 4 3 2 9 5 和 2 在B1至B5单元格里分别输入了 第一名 第四名 第三名 第五名 和 第二名 在C1只C5单元格里分别输入了 张 李 陈 王 和 滕 接着在D5单元格里输入如下公式 观察出现结果 公公式式说说明明 结结果果 LOOKUP 1 1 A1 C5 B1 B5 由于A列中没有1 1 故找到小于1 1的最 大值1 然后返回同一行中B列的值 张 LOOKUP 5 A1 C5 B1 B5 查找A列中的5 然后返回同一行中B列的 值 王 数组形式 数组中的值必须以升序顺便放置 2 1 0 1 2 A Z FALSE TRUE 否则LOOKUP函数无法提供正确的值 现在在D5单元格里输入如 下公式 观察出现结果 公公式式说说明明 结结果果 LOOKUP c a b c d 1 2 3 4 在数组找到第一行中查找 c 查找小 于或等于它 c 的最大值 然后返 回同一列内最后一行中的值 3 LOOKUP bump a 1 b 2 c 3 在数组第一行中查找 c 查找小于或 等于它 b 的最大值 然后返回同 一行内最后一列中的值 2 HLOOKUPHLOOKUP函函数数 函函数数用用途途 此函数的功能是在给定区域的首行里查找目标数值 然后返回目标数值所 在列某一行的相关值 显然该函数与VLOOKUP函数相似 无非一个是在首行里 查找 一个是在首列里查找 类似于LOOKUP函数 HLOOKUP函数也具有两种语法形式 即向量形式和数组 形式 下面简答介绍一下向量形式的函数语法 至于数组形式的函数语法 可以参阅LOOKUP函数的数组形式函数语法 函函数数语语法法 HLOOKUP lookup value table array row index num range lookup HLOOKUP 要查找的目标数值 给定区域 系统将在给定区域的首行中查找 目标数值 返回给定区域中某一行的序号 函函数数说说明明 range lookup为TURE或省略 HLOOKUP函数将进行近似匹配查找 range lookup为FALSE或0 HLOOKUP函数将进行精确匹配查找 注意要查找的目标数值的大小应该是在查找区域数值的范围内 否则应用 HLOOKUP函数是会显示错误值 函函数数简简单单示示例例 现假设在A1至E1单元格里分别输入了 1 4 3 2 9 5 和 2 在A2至E2单元格里分别输入了 张 李 陈 王 和 滕 接着在A3单元格里输入如下公式 观察出现结果 公公式式说说明明 结结果果 HLOOKUP 1 1 A1 E2 2 TRUE 由于1列中没有1 1 故找到小于1 1的最 大值1 然后返回同一列中第2行的值 张 HLOOKUP 1 1 A1 E2 2 FALSE 查找A列中的5 然后返回同一列中第2行 的值 王 HLOOKUP 3 1 2 3 a b c d e f 2 TRU E 该例为HLOOKUP函数在数组形式里的应用 在数组常量的第一行中查找3 并返回 同列中第2行的值 c 带带薪薪年年假假天天数数统统计计表表 DATEDIFDATEDIF函函数数 函函数数用用途途 此函数的功能计算起始日期和截止日期之间的天数 月数或年数 函函数数语语法法 DATEDIF start date end date unit DATEDIF 起始日期 截止日期 返回类型 函函数数简简单单示示例例 在B3单元格里输入下列公式 观察出现结果 公公式式说说明明 结结果果 DATEDIF 2001 1 1 2003 1 1 Y 在起始日期和截止日期中有两个整年 2 DATEDIF 2001 1 1 2003 1 2 M 在起始日期和截止日期中有24个月 24 DATEDIF 2001 6 1 2002 8 15 D 在2001年6月1日和2002年8月15日之间有 440天 440 DATEDIF 2001 6 1 2002 8 16 YD 在6月1日与8月15日之间有75天 忽略日 期中的年 75 DATEDIF 2001 6 1 2002 8 17 MD 计算开始日期1和结束日期15之间的差 忽略日期中的年和月 14 加加班班统统计计表表 TEXTTEXT函函数数 函函数数用用途途 此函数的功能是一句需要目标数字转换为指定的文本格式 函函数数语语法法 TEXT value format text TEXT 目标数值 可以为数字可以为计算结果 需要设定的文本格式 函函数数简简单单示示例例 在A2和A3单元格里分别输入 工业 和 服务业 在B2和B3单元格里分 别输入 45 和 1000亿 然后在C4和C5单元格里分别输入下面表格左侧 的公式 按键确认 观察出现结果 公公式式说说明明 结结果果 A2 总占GDP比重 TXET B2 0 将A2和B2单元格里的内容以及引号间的内 容合并为一句 工业占总GDP比重的45 A3 的GDP为 TEXT B3 将A3和B3单元格里的内容以及引号间的内 容合并为一句 服务业的GDP为1000亿 ROUNDROUND函函数数 函函数数用用途途 此函数的功能是设置指定位数 按此位数对数字进行四舍五入去整 然后 返回相应的结果 函函数数语语法法 ROUND number num digits TEXT 目标数字 指定的位数 系统按此位数进行四舍五入 函函数数说说明明 如果num digits小于 等于或者大于0 都会有不同的结果 函函数数简简单单示示例例 在Excel任意一个单元格输入以下公式 观察出现的结果 公公式式说说明明 结结果果 ROUND 3 1415 1 将3 1415四舍五入到一位小数 3 1 ROUND 3 1415 0 将3 1415四舍五入到整数 3 ROUND 314 15 1 将3 1415四舍五入到小数点左侧以为 310 YEARYEAR函函数数 函函数数用用途途 此函数的功能是返回给定日期所属的年份 函函数数语语法法 YEAR serial number YEAR 给定日期 函函数数简简单单示示例例 在A1单元格里输入 2007 8 2 在D5单元格里输入以下公式 观察出现 的结果 公公式式说说明明 结结果果 YEAR A1 A1单元格内日期的年份 2007 MONTHMONTH函函数数 函函数数用用途途 此函数的功能是返回给定日期所属的月份 函函数数语语法法 MONTH serial number MONTH 给定日期 函函数数说说明明 应用MONTH函数时 应注意给定日期的输入格式 函函数数简简单单示示例例 在A1单元格里输入 DATE 2007 8 2 在D4单元格里输入以下公式 观 察出现的结果 公公式式说说明明 结结果果 MONTH A1 A1单元格内日期的月份 8 在A2单元格里输入 2007 9 2 在D5单元格里输入以下公式 观察出现 的结果 公公式式说说明明 结结果果 MONTH A2 A2单元格内日期的月份 9 DAYDAY函函数数 函函数数用用途途 此函数的功能是返回给定日期的具体天数 函函数数语语法法 DAY serial number DAY 给定日期 函函数数简简单单示示例例 在A1单元格里输入 2007 9 2 在D5单元格里输入以下公式 观察出现 的结果 公公式式说说明明 结结果果 DAY A1 A1单元格内日期的天数 2 NOWNOW函函数数 函函数数用用途途 此函数的功能是显示系统当前的时间 函函数数语语法法 NOW 函函数数简简单单示示例例 在Excel任意一个单元格里输入以下公式 观察出现的结果 公公式式说说明明 结结果果 NOW 返回当前的系统日期 2012 12 7 16 45 TODAYTODAY函函数数 函函数数用用途途 此函数的功能是显示系统当前日期 函函数数语语法法 TODAY 函函数数简简单单示示例例 在Excel任意一个单元格里输入以下公式 观察出现的结果 公公式式说说明明 结结果果 TODAY 返回当前的系统日期 2012 12 7 NETWORKDAYSNETWORKDAYS函函数数 函函数数用用途途 此函数的功能是计算两个给定参数间的工作日数值 函函数数语语法法 NETWORKDAYS start date end date hoildays NETWORKDAYS 起始时间 终止时间 假日 函函数数说说明明 应用NETWORKDAYS函数时应注意给定日期的输入格式 操作NETWORKDAYS函数时 若该函数不可用 则显示 NAME 错误值 此时 需分要加载相应的宏 具体方法如下 依次单击菜单 工具 加载宏 淡出一个对话框 勾选 分析工具库 复选框 然后单击 确定 按钮 函函数数简简单单示示例例 在A1单元格里输入 DATE 2007 8 2 作为开始日期 在A2单元格里输入 DATE 2008 3 2 作为结束日期 在A3单元格里输入 DATE 2007 10 13 作为假日在D4单元格里输入以下公式 观察出现的结 果 公公式式说说明明 结结果果 NETWORKDAYS A1 A2 计算开始日期和终止日期之间间隔工作日 的数值 152 NETWORKDAYS A1 A2 A3 计算开始日期和终止日期之间间隔工作日 的数值 同时扣除A3单元格里的假日 151 EOMONTHEOMONTH函函数数 函函数数用用途途 此函数的功能是给定一个起始日期 再辅以一个指定数字 将一个指定数 字 将起始日期的月份向前或向后拨动该数字大小距离 最后返回所得月份 的最后一天日期 函函数数语语法法 EOMONTH start date months EOMONTH 起始日期 制定数字 正数表示向未来移动的月数 负数表示向 过去移动的月数 函函数数说说明明 应用EOMONTH函数时应注意给定日期的输入格式 操作EOMONTH函数一样 若EOMONTH函数第一次不能使用 也要进行加载宏 的操作 函函数数简简单单示示例例 在A1单元格里输入 DATE 2007 8 2 在D4单元格里输入以下公式 观察 出现的结果 注 若显示的结果为 39386 此时则可通过调整单元格格式来获得日期格式 公公式式说说明明 结结果果 EOMONTH A1 2 计算 2007年8月2日 该日期两个月后所 在月份的最后一天的日期 2007 10 31 WORKDAYWORKDAY函函数数 函函数数用用途途 此函数的功能是返回某一个日期前后相隔制定工作日的日期 函函数数语语法法 WORKDAY start date days holiday WORKDAY 起始日期 自起始日期起推算的天数 当该值为正值时意味着将 来的日期 当该值为负值时意味着过去的日期 并且推算中是不包含共假日 的 要剔除的日期 可以自定义的日期 也可以定义公休假日 函函数数说说明明 应用WORKDAY函数时应注意给定日期的输入格式 操作WORKDAY函数一样 若WORKDAY函数第一次不能使用 也要进行加载宏 的操作 函函数数简简单单示示例例 在B1单元格里输入 DATE 2007 8 2 在B2单元格里输入 179 作为工作 天数 在B3单元格里输入 DATE 2007 10 17 作为假日 在D4单元格里输 入以下公式 观察出现的结果 注 若显示的结果为 39547 此时则可通过调整单元格格式来获得日期格式 公公式式说说明明 结结果果 WORKDAY B1 B2 从起始日期开始计算179个工作日后的日 期 2008 4 9 WORKDAY B1 B2 B3 从起始日期开始计算179个工作日后的日 期 期间还要扣除B3单元格里的假日 2008 4 10 TIMETIME函函数数 函函数数用用途途 此函数的功能是返回某一个特定时间的小数值 函函数数语语法法 TIME hour minute second TIME 小时 分钟 秒 三个数值都在在区间 0 32767 内取值 函函数数简简单单示示例例 在E1单元格里输入 8 作为小时 在F1单元格里输入 12 作为分钟 在 G1单元格里输入 0 作为秒 在G4单元格里输入以下公式 观察出现的结果 公公式式说说明明 结结果果 TIME E1 F1 G1 将E1 F1和G1单元格里的数字构成时间形 式 8 12AM 单击任意一个单元格 然后输入表格里的任意一个公式 按键确认 系统将自动显示对应日期 公公式式说说明明 结结果果 YEAR NOW 返回当前年份 MONTH NOW 返回当前月份 DAY NOW 返回当前日期 TODAY 返回当前日期 NOW 返回当前时间 NETWORKDAY TODAY EOMONTH TO DAY 0 放回当前时间距离月末还有多少工作人 哦 WORKDAY TODAY 15 返回当前时间15天以后的工作时间 NOW TIME 3 30 0 返回当前时间丧再加3个半小时 人事信息数据表 TEXTTEXT函函数数 函函数数用用途途 此函数的功能是一句需要目标数字转换为指定的文本格式 函函数数语语法法 TEXT value format text TEXT 目标数值 可以为数字可以为计算结果 需要设定的文本格式 函函数数简简单单示示例例 在A2和A3单元格里分别输入 工业 和 服务业 在B2和B3单元格里分 别输入 45 和 1000亿 然后在C4和C5单元格里分别输入下面表格左侧 的公式 按键确认 观察出现结果 公公式式说说明明 结结果果 A2 总占GDP比重 TXET B2 0 将A2和B2单元格里的内容以及引号间的内 容合并为一句 工业占总GDP比重的45 A3 的GDP为 TEXT B3 将A3和B3单元格里的内容以及引号间的内 容合并为一句 服务业的GDP为1000亿 MIDMID函函数数 函函数数用用途途 此函数的功能是在目标数字符串中指定一个开始位置 按设定的数值返回 该字符串中的相应数目字符内容 函函数数语语法法 MID text start num num chars MID 目标字符串 字符串开始的位置 通常start num是从字符串的首个 字符编号 依次为1 2 3 start num有取值范围 不能大于字符串的长 度 不能小于1 设定的数目 MID函数将按次数目返回相应的字符个数 显 然该数值不为负 否则函数将输出错误值 函函数数简简单单示示例例 在A2输入 wonderful 然后在B3单元格里输入下列公式 观察出现结果 公公式式说说明明 结结果果 MID A2 1 5 提取A2单元格里字符串中的5个字符 且 从第一个字符开始提取 wonde MID A2 5 20 提取A2单元格里字符串中的20个字符 且 从第7个字符开始提取 因为A2单元格中的 字符串中总字符数是9 所以最多只能从 第7个字符开始 将余下的所有字符都提 取 erful wonde MID A2 10 2 因为A2单元格中的字符串中总字符数是9 此时要从第10个字符开始 所以系统返 回空格 MID A2 0 2 因为start num小于1 所以系统返回错 误值 VALUE LENLEN函函数数 函函数数用用途途 此函数的功能是计算目标字符中的字符数 函函数数语语法法 LEN text LEN 目标字符串 函函数数说说明明 空格也视为字符 加到字符串的字符数中 函函数数简简单单示示例例 在A3单元格里输入 wonderful 在A4单元格里输入 Excel word 然后 在B4单元格里输入下列的公式 观察出现结果 公公式式说说明明 结结果果 LEN A3 返回A3单元格里的字符串的长度 9 LEN A4 返回A4字符串的长度 包含一个空格的位 置 10 LEFTLEFT函函数数 函函数数用用途途 此函数的功能是从字符串的左侧开始 按照指定的数值返回相应的字符内 容 函函数数语语法法 LEFT text num chars LEFT 目标字符串 指定的数值 该数值必须大于等于0 函函数数简简单单示示例例 在A3单元格里输入 wonderful 然后在B4单元格里输入下列的公式 观 察出现结果 公公式式说说明明 结结果果 LEFT A3 4 对A3单元格里的字符串 按从左到右提取 前4位字符 wond LEFT A3 因为省略了num chars 因此系统假定其 为1 将提取A3单元格里字符串左侧的首 字符 w LEFT A3 11 因为指定指数大于目标字符串的总数 所 以系统提取目标字符串的所有字符 wonderful RIGHTRIGHT函函数数 函函数数用用途途 此函数的功能是从字符串的右侧开始 按照指定的数值返回相应的字符内 容 该函数恰好与LEFT函数的功能相反 函函数数语语法法 RIGHT text num chars RIGHT 目标字符串 指定的数值 该数值必须大于等于0 函函数数简简单单示示例例 在A3单元格里输入 wonderful 然后在B5单元格里输入下列的公式 观 察出现结果 公公式式说说明明 结结果果 RIGHT A3 6 对A3单元格里的字符串 按从右到右提取 6位字符 derful RIGHT A3 因为省略了num chars 因此系统假定其 为1 将提取A3单元格里字符串右侧的首 字符 l MODMOD函函数数 函函数数用用途途 此很熟的功能是求解对目标数值除以指定数后的余数 余数的符号和除数 相同 函函数数语语法法 MOD number divisor MOD 目标数值 作为被除数 指定数 作为除数 函函数数说说明明 当指定数为0时 系统将显示错误值 DIV 0 函函数数简简单单示示例例 在A1 B1和C1单元格里分别输入数字 3 2 和 2 再在C5单元 格里输入下列任意公式 观察出现的结果 公公式式说说明明 结结果果 MOD A1 B1 3除以2余数为1 又因为除数2的符号为正 1 MOD A1 C1 3除以 2余数为1 又因为除数 2的符号为 负 1 MOD A1 0 除数为0 DIV 0 TODAYTODAY函函数数 函函数数用用途途 此函数的功能是显示系统当前日期 函函数数语语法法 TODAY 函函数数简简单单示示例例 在Excel任意一个单元格里输入以下公式 观察出现的结果 公公式式说说明明 结结果果 TODAY 返回当前的系统日期 2012 12 7 DATEDIFDATEDIF函函数数 函函数数用用途途 此函数的功能计算起始日期和截止日期之间的天数 月数或年数 函函数数语语法法 DATEDIF start date end date unit DATEDIF 起始日期 截止日期 返回类型 函函数数简简单单示示例例 在B3单元格里输入下列公式 观察出现结果 公公式式说说明明 结结果果 DATEDIF 2001 1 1 2003 1 1 Y 在起始日期和截止日期中有两个整年 2 DATEDIF 2001 1 1 2003 1 2 M 在起始日期和截止日期中有24个月 24 DATEDIF 2001 6 1 2002 8 15 D 在2001年6月1日和2002年8月15日之间有 440天 440 DATEDIF 2001 6 1 2002 8 16 YD 在6月1日与8月15日之间有75天 忽略日 期中的年 75 DATEDIF 2001 6 1 2002 8 17 MD 计算开始日期1和结束日期15之间的差 忽略日期中的年和月 14 ExcelDExcelD 字字符符查查找找和和替替换换技技术术 在实际工作中 有时可能需要从姓名字符串中提取姓或者名 如外籍员工 的姓名等 此时需要用到Excel的字符查找和替换技术 姓姓名