【Excel教程】Excel在人力资源管理的应用

为 TRUE 或 FALSE。
示false
17
IF函数的应用实例
✓ 区域段自动算出对应等级
分数段
分数≥90分 80分≤分数＜90分 70分≤分数＜80分
分数＜70分
等级
A B C D
=IF(K4>=90， "A"， IF(K4>=80， "B"， IF(K4>=70， "C"， "D")))
• 嵌套了两个IF函数
ng
Row
Column
语法/例子
函数结构
=value(b1)
=NumberString(7890， 1） 运行结果： 七千八百九十 =NumberString(7890， 2) 运 行结果： 柒仟捌佰玖拾 =NumberString(7890， 3) 运 行结果： 七八九○
=numberstrin g(b1， 2)
18
函数名
语法
函数结构
函数作用
SUMIF ROW
SUMIF(range， criteria， sum_range)
其中： Range 为用于条件判断的单元格 区域。
Criteria 为确定哪些单元格将被相加求 和的条件， 其形式可以为数字、 表 达式或文本。 例如， 条件可以表示 为 32、 "32"、 ">32" 或 "apples"。
相对引用指公式中的单元格位置将随着公式单元格的位置而改变
2. 绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”， 如$A$4， $C$6
3. 混合引用GO
混合引用是指在一个单元格引用中， 既有绝对引用， 也有相对引 用.
例： 行变列不变： $A4
左右括号成
嵌
对出现
套
结
构
8
函数名
1、 Left
语法
函数结构
1、 LEFT(text， num_chars) 其中： Text 是包含要提取字符的文本字 符串。 Num_chars 指定要由 LEFT 所提 取的字符数。
=LEFT(A1， 2)
函数作用
从A1最左边 向右取两位
例子运行 结果
2、 Right
0)
如果VLOOKUP函数运 行值是个错误值的话 （找不到数据）， 就等 于0， 否则， 就等于 VLOOKUP函数运行的 值。
AND
AND(logical1， logical2， ...) Logical1， logical2， ... 表示待检 测的 1 到 30 个条件值， 各条件值可 为 TRUE 或 FALSE。
20
函数名
1、 NOW 2、 TODAY 3、 WEEKDAY 4、 DAY 5、 MONTH 6、 YEAR
语法
函数结构 =now()
=today() =weekday(b1， 2) =day(b1) =month(b1) =year(b1)
函数作用
显示当前的日期+时间 显示今天的日期 判断b1为星期几 显示b1的号数 显示b1的月数 显示b1的年数
突出年龄大于30的人等
如何取消条件格式？
1-3 条件格式
6
认识Excel函数
7
2-1 EXCEL函数的结构
参数与参数之间 使用半角逗号进
行分隔
❖Excel 函数即是预先定义， 执行计算、 分析等处理 数据任务的特殊公式。
Excel 函数结构：
也有一些函数 是没有参数的，
如NOW()
单 一 结 构
常用于计算两日期的时间差
=DATEDIF(较早的日期， 较晚的日期， 第三个参数）
第三个参数： “Y” ---两日期间共多少年。 “M” ----两日期间共多少个月。 “D” ----两日期间共多少天。 “MD” 是两日期中天数的差。 忽略日期中的月和年。 “YM” 是两日期中月数的差。 忽略日期中的年。 “YD” 是两日期中天数的差。 忽略日期中的年。
23
常用函数之日期类函数
✓举例： 算两个日期间差几年、 几月、 几日
开始时间是2012-2-21 结束时间是2014-2-19
C2=DATEDIF(A2， B2， "Y") D2=DATEDIF(A2， B2， "YM") E2=DATEDIF(A2， B2， "MD") F2=DATEDIF(A2， B2， "D") G2=DATEDIF(A2， B2， "M") H2=DATEDIF(A2， B2， “YD") I2=C2&"年"&D2&"个月" I2=DATEDIF(A2， B2， “y”)&“年”&DATEDIF(A2， B2， “ym”)&“个月"
语法
函数结构
函数作用
=LEN(text) 其中： Text 是要查找
其长度的文本。 空格 将作为字符进行计数 。
=len(C2)
计算C2的字符长 度
RANK(number， ref，
计算b1在b1：
order)
b10区域中从
Number 为需要找到排 =rank(b1， b1： 大到小排第几
位的数字。 Ref 为数字列表数组或
2、 RIGHT(text， num_chars) 其中： Text 是包含要提取字符的文本字 符串。 Num_chars 指定希望 RIGHT 提 取的字符数。
=RIGHT(A1， 2)
从A1最右边 向左取两位
3、 Mid
3、 MID(text， start_num， num_chars) 其中： Text 是包含要提取字符的文本字 符串。 Start_num 是文本中要提取的第 一个字符的位置。 Num_chars 指定希望 MID 从文本中返回字符的个数。
10
函数名 FIND
语法
函数结构
函数作用
在c6中查找龙， =FIND("龙"， C6) 结果显示龙在
c6中的位数， 查不到显示 #VALUE！
11
常用函数之查找类函数
❖函数VOOKUP
=VLOOKUP(lookup_value， table_array， col_index_num，
range_lookup)
=MID(A1， 2， 3)
4、 &
（连接符号）
XX&XX
=A2&B2
从A1左边第 2位开始取3 位
将A2和B2内 容合并 在一起
9
❖应用实例
✓ 从身份证号码提取出生日期
在C2单元格中输入 =MID(B2， 7， 4)&"-"&MID(B2， 11， 2)&"-"&MID(B2， 13， 2)
公式解析：
参数
简单说明
输入数据类型
lookup_value 要查找的值
数值、 引用或文本字符串
table_array
要查找的区域
数据表区域
col_index_num
返回数据在查找区域的第几列 数
正整数
range_lookup 精确匹配/模糊匹配
0或1
(以从8月工资表导入考勤到8月考核表为例） GO
12
常用函数之查找类函数
分析： 思路是从左边开始， 截取“+”号前的位数。
在C1输入=RIGHT(A1， LEN(A1)-FIND("+"， A1))
分析： 思路是截取“+”号后的位数。
16
函数名
语法
函数结构
函数作用
IF IFREEOR
IF(logical_test， value_if_true， value_if_false)
列变行不变： A$4
3
1-2 数据的有效性
✓ 控制单元格输入文本长度 GO
选中区域---数据---数据有效性---允许---文本长度
可防止身份证/银行卡号/手机号码输入多一位或者少一位数
✓ 序列
选中区域---数据---数据有效性---序列---在“来源”输入（A，
B， C， D)
注意逗号要半角格式
对数字列表的引用。 Ref 中的非数值型参数
b10， 0) =rank(b1， b1
： b10， 1)
名； 计算b1在b1：
b10区域中从
将被忽略。
小到大排第几
Order 为一数字， 指明
名。
排位的方式。
15
❖应用实例
在C1输入=A1&"+"&B1
✓如何根据 A1、 B1得 出C1？
✓ 如何根据A1得出B1和C1？ 在B1输入=LEFT(A1， FIND("+"， A1)-1)
19
Hale Waihona Puke Baidu
❖应用实例
✓ 从身份证号码中提取性别 （身份证倒数第2位若为偶数为女性， 奇数为男性）
在C2单元格中输入=IF(MOD(MID(B2， 17， 1)， 2)=1， "男 "， "女")
公式分析：
MID(B2， 17， 1)是从身份证第17位起取1位字符； 在本例即为数字 5， MOD函数就是取余数的函数， 在本例中就是MOD(5， 2)， 5除 以2的余数， 最外面是一个IF函数， 当这个余数为1时， 即为奇数， 奇数就是男性， 否则为女性。
24
常用函数之日期类函数
❖应用实例3
✓ 从出生日得出年龄
在D2单元格中输入=DATEDIF(C2， NOW()， “y”)
因为从身份证提取出生日的C2 =MID(B2， 7， 4)&"-"&MID(B2， 11， 2)&"-
人力资源部门
Excel在人力资源管理中的应用
用好工具， 高效工作
1
课程大纲
数据录入
➢ 单元格的引用 ➢ 数据有效性 ➢ 条件格式
认识Excel函数
➢ Excel函数的结构 ➢ 常见的几类函数 ➢ 应用实例
认识数据透视表
➢ 数据透视表是什么 ➢ 数据透视表的几个简单应用实例
2
1-1 单元格的引用
1.相对引用
Sum_range 是需要求和的实际单元格。
当B列满足了条
件（HI）， 对
=sumif(B：
应的E列的单元 格的数值之和
B， E：
H1， E)
GO
求5除以2的余数是
求余数的函数， 当第二个参数为2时， =ROW(5， 2) 多少， 本例的
常用于判断奇偶数， 第二个参数为2
运行结果是1
时， 运行结果为0是偶数， 1是奇数
MID(B2， 7， 4)表示提取B2单元格中从第7位开始连续4位的字符， 即出生年； MID(B2， 11， 2)表示提取B2单元格中从第11位开始连续2位的字符， 即出生月； MID(B2， 11， 2)表示提取B2单元格中从第13位开始连续2位的字符， 即出生日； 在公式中加入&可以用来连接两个文本字符串。
✓ 禁止输入重复数据
=countif(区域， 起始单元格)=1
选中区域---数据---数据有效性---
允许（自定义) ---
公式填写=COUNTIF(A： A， A1)=1
4
1-2 数据的有效性
❖如何取消对于“数据有效性”的设置？
选中区域---数据---数据有效性---允许---任何值
GO
5
✓利用突出的格式， 经常能 起到提醒的功能 如： 突出不满勤的人GO
❖函数VOOKUP
• 选择区域里最好不要有合并的单元格（先“取消单元格合并”）
• 如果单元格里有空格， 有可能导不出来
（提示： 函数TRIM（A1)可以去除字符串首部和尾部的空格）
• 注意重复的名字 • 查找区域首列必须含有查找的内容（第一个参数） • 介绍vookup函数与IFERROR函数组合使用
第一个参数一般为一个函数， 当这 个函数运行是个错误值时（ #N/A #DIV/0 ！ #N/A #VALUE！ #NAME #NULL！ #NUM ！ #REF！ ）， 运行结果等于第 二个参数
=if(a2>0， 1， 0)
当a2>0时， 显示1， 否 则显示0
=iferror(vookup(A1， A： B， 2， 0)，
=and(a3>0， b3<0)
当A3>0， 并且b3<0时， 显示true， 否则显示
false
OR
OR(logical1， logical2， ...) Logical1， logical2， ... 表示待检 测的 1 到 30 个条件值， 各条件值可
=or(a4>0， b4<0)
当a4>0， 或者b4<0时， 都显示true， 否则显
21
函数名 WEEKDAY DAY MONTH YEAR
以单元格b1为20134-5为例的运行结果
函数结构
函数作用
判断b1为星期几
6
=weekday(b1，
2)
5
=day(b1)
显示b1的号数
4
显示b1的月数
=month(b1)
2013
显示b1的年数
=year(b1)
22
常用函数之日期类函数
DATEDIF函数
=ROW(reference) 其中： Reference 为需 =row(b1)
要得到其行号的单元 格或单元格区域。
=COLUMN(reference) =column(b1)
函数作用
将b1数值化
将b1数字转为大写 汉字
求b1是第几行 （值为1)
求b1是第几列(值 为2)
14
函数名 len
rank
(以从8月工资表导入考勤到8月考核表为例）GO
=IFERROR(VLOOKUP(A4， '8月工资'！ B： F， 5， 0)， 0)
如果VOOKUP函数运行后找不到对应的数据， 就会显示为#N/A， IFERROR的作用就是把这些#N/A都变为第2个参数。
13
函数名
Value Numberstri