用EXCEL进行薪酬分析课程分享.pptx
合集下载
《Excel 2010典型应用案例教程》教学课件 项目一 企业员工薪酬管理
任务一 制作员工薪资统
计表
任务实施1
步骤7：
翻开“数据有效性〞对话框--“设置〞--“允 许〞--“序列〞--“来源〞中输入内容。
创立员工薪资统计表格
步骤8： 在“输入信息〞选项卡的“输入 信息〞编辑框。
在编辑框中输 入部门名称，各部 门间用英文逗号隔 开。
输入“请在下 拉列表中选择部 门类别！〞。
北京金企鹅文化开展中心 h
Excel 2021 典型应案用例教程
7/7/2021
引文 第1页
项目一 企业员工薪酬管理
传统手工方式：
利用Excel办公：
每一个成功者都有一个开始。勇于开始，才能找到成功的路。
目录 第2页
项目一 企业员工薪酬管理
任务一 制作员工薪资统计表 任务二 制作员工出勤统计表 任务三 制作员工业绩统计表 任务四 制作员工福利统计表 任务五 制作员工性质管理表 任务六 制作工资条
步骤1： 在F3单元格输入公式“=E3*0.1”，
确定后即可得到计算结果。
步骤2： 双击F3单元格的填充柄，可复制公
式计算其他员工的提成额。
每一发奋努力的背后，必有加倍的赏赐。
正文 第 29 页
任务三 制作员工业绩统 计表
任务实施2
步骤3：
设置业绩额和提成额所在列的数字格 式为“会计专用〞，员工业绩统计表 制作完成。
假设不给自己设限，那么人生中就没有限制你发挥的藩
正文 第3页
任务一 制作员工薪资统 计表
任务分析
员工薪资统计表可以看作是员工工资统计表， 用来记录员工入职后的根本工资及调薪后的工资情 况。员工薪资统计表主要包括员工的工号、姓名、 职务、部门、调薪后的根本工资、工龄工资和岗位 工资等内容。
Excel在薪酬管理中的应用
平均薪资与市场中位值相当
高于1.00
平均薪资高于结构中点值 薪资在结构高位，须考虑是否适当 调整薪资结构
平均薪资高于市场中位值，有 外部竞争力
最大值 中位值 最小值
内部公平性分析
薪酬体系红绿点分析
红点
绿点
想一想：如何让红绿点回归正常水平？
绿点原因分析 红点典型原因
任职期较长 挖来的优秀人才 公司重组 职位的重新配置 上新佳雇的佣业绩 新晋升 公司的重组 较差的业绩
• 统计学基础知识 • 公司内部薪酬数据统计 • 薪酬分析-分析什么
BEFORE--薪酬数据分析
统计学基础知识
平均数 VS 加权平均数
平 指在一组数据中所有数据之和再除以数据 的个数.它是反映数据集中趋势的一项指标
均 当需要分析某个代表性数值水平时，多用
简单的平均值。
加 不同比重数据的平均数,加权平均数就是把 权 原始数据按照合理的比例来计算 平 在需要控制总量或者考虑每组数据出现的 均 频率的时候，需要采用加权平均的方式。
想一想：如何激励不同偏离度组合癿员 工？
外正 内负
外负 内正
双正 双负
内部薪酬数据统计
基本信息统计 收集多方面癿样本信息，便于后期从多个维度 迚行数据分析
职位名称
工作年限
职位等级
最高学 历
职能类 别
想一想： 如何根据出生年仹自劢计算年龄 如何根据入司时间自劢计算司龄 如何推算总工作年限
城市
年龄
类别 A B C
样本数 平均年薪
2
23000
5
25000
10
26000
年薪总额 46000 125000 260000
简单平均：N=3 平均值=（23000+25000+26000）/3=24666 加权平均：N=17 平均值=（46000+125000+260000）/17=25353
用EXCEL进行薪酬分析
用EXCEL进行薪酬分析薪酬分析是一个组织内对员工薪资进行评估和比较的过程。
通过使用Excel进行薪酬分析，我们可以更好地了解公司的薪酬结构，识别薪酬差异并制定合理的薪酬策略。
以下是一个关于如何使用Excel进行薪酬分析的详细说明。
1.收集薪酬数据首先，我们需要从组织内部搜集员工的薪酬数据。
这些数据包括员工的基本工资、津贴和奖金等各项收入。
可以从人力资源部门或财务部门获得这些数据。
将这些数据以Excel的表格形式整理好，并确保它们有足够的可行性。
2.绘制薪酬分布图接下来，我们可以使用Excel的图表工具绘制薪酬分布图来展示员工薪酬的分布情况。
选择“插入”选项卡，然后在“图表”组中选择合适的图表类型，如柱状图或饼图。
在这些图表中，我们可以清楚地看到员工薪酬的范围以及可能存在的差异。
3.计算薪酬指标计算一些薪酬指标可以帮助我们更全面地了解员工薪资的情况。
下面是一些常见的薪酬指标：- 平均薪资：使用Excel的“AVERAGE”函数可以计算出员工薪资的平均水平。
- 中位数薪资：使用Excel的“MEDIAN”函数可以计算出员工薪资的中位数。
-薪资范围：将最高薪资和最低薪资进行比较可以计算出员工薪资的范围。
-工资差异：计算不同职位或不同部门之间的薪酬差异。
-薪酬比例：计算员工薪酬与公司总薪酬之间的比值。
通过计算这些指标，我们可以更好地了解员工薪酬的整体分布情况，并找到任何存在的薪酬差异。
4.进行薪酬比较Excel的数据透视表是进行员工薪酬比较的强大工具。
通过选择数据透视表工具，我们可以选择要分析的数据和想要比较的维度。
例如，我们可以比较不同职位、不同部门或不同地区之间的薪酬情况。
数据透视表还可以进行过滤、排序和汇总等操作，以生成更详细的结果。
5.建立薪酬模型使用Excel的“IF”函数和其他数学函数，我们可以建立一个薪酬模型来预测员工的薪资水平。
通过分析员工的绩效和其他相关因素，我们可以建立一套公式来预测每个员工的薪资。
Excel在薪酬管理中的应用
第9页
二、计算职员工资汇总表
（一）职员工资汇总表表头 （二）职员工资汇总表取数
Excel在薪酬管理中的应用
单元格 B2 C2 D2 E2 F2 G2 H2 I2
取数公式
公式 =VLOOKUP($A2,考勤,2,0) =VLOOKUP($A2,考勤,3,0) =VLOOKUP($A2,考勤,4,0) =VLOOKUP($A2,考勤,5,0) =VLOOKUP($A2,考勤,6,0) =VLOOKUP($A2,考勤,7,0) =VLOOKUP($A2,考勤,8,0)
第25页
G2 =ROUND($E2/30*0.1*$I2+$E2/30*0.5*$J2+$E2/30*$K2,2)
H2 =300*$L2+200*$M2
重点提醒：
ROUND/ROUNDUP/ROUNDDOWN函数使用方法： 三者语法格式均为：(number,num_digits)
Excel在薪酬管理中的应用
第6页
一、工资管理系统表格
（三）薪资奖惩标准表
重点提醒：
将表格网格线去掉做法是：选择【视图】选项卡，将网格线前面小方框勾去掉，
整个工作表就会展现白纸状态。另外也能够按照自己习惯来调整行高。
Excel在薪酬管理中的应用
第7页
一、工资管理系统表格
（四）职员考勤奖惩表
“职员考勤奖惩表”取数公式及含义
单元格
三、薪资查询
（一）薪资信息查询
3.高级筛选——“逻辑与”
Excel在薪酬管理中的应用
图6 生产车间员工中“加班工资”不为零员工信息
第21页三、薪资查询（一）薪 Nhomakorabea信息查询
3.高级筛选——“逻辑或”
Excel在经济管理中的应用课件-第8章Excel在工资绩效管理中的应用
15
EXCEL在工资绩效管理中的应用
8-11
编制计算ABC单位的二月份、三月份的工资表
[案例要求]
根据二月份、三月份职工具体完成的工作量情况(如图8-21,、
8-22所示)，编制）二月份、三月份的工资表。
16
EXCEL在工资绩效管理中的应用
8.6.2一季度工作量、工资情况汇总分析
案例8-12
ABC单位一季度工作量、工资情况汇总分析
14
EXCEL在工资绩效管理中的应用
3.CELL
格式：CELL(info_type, [reference])
功能：返回指定单元的信息，包括格式、位置或值等。
说明：
Reference：欲取得其信息的单元。可省略，默认为最后被更改的单元格。
info_type：欲获得信息的类型，可为12种选项，如表8-7所示。
20
8.1.2 工资表的建立 根据所建立的实体-联系图，在Excel中建立各个表格，一般 每个实体对应一个基本的表格。
案例8-3 ABC单位的工资表的建立 [案例要求] 在“ABC单位工资表”工作薄中，建立各个工资表。
5
EXCEL在工资绩效管理中的应用
8.2基本工资的计算
案例8-4 ABC单位的各月份工资表的编制 [案例要求] 根据绩效工资信息，按照月份编制ABC单位的工资表。
EXCEL在工资绩效管理中的应用
2. FIND 格式：find(find_text,within_text,start_num) 功能：返回一个内容在另一个内容中所在的位置。 说明： find_text：要查找的内容。 within_text：要查找的单元格。 start_num：起始位置。可省略，默认为1。 FIND函数示例： 设A2的值为“abcdefg”，则 = FIND("d",A2,2)的结果为4。
EXCEL进行薪酬分析技巧大全(很直观)课件
平均值与中位数
• 平均值与中位数的关系：
300,000 250,000
200,000
150,000
平均值 中位数
100,000
50,000
0 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
中位数一般小于平均值
次序变量
定距变量
定比变量
根据变量的特点选择分析方法 集中趋势 名义变量 次序变量 定距变量 定比变量
众数
离散趋势
（可变比率）
中位值、百分 位、四分位
范围、四分位 范围
平均值、加权
标准偏差
所有以上
所有以上
分布频率与集中趋势
• 众数 • 平均值 • 中位值
• 百分位 • 四分位
• 范围 • 四分位距 • 标准偏差 • 正态分布
平均值与中位数
平均值比中位数高多少个百分点？
北京 一般员工 -9.3%
马来西亚 +2.5%
新加坡 +9.9%
香港 +2.7%
专业人员
管理层 高级管理层 合计/加权
+6.3%
+4.6% +5..2%
+9.8% +6.1% +5.3%
+10.9%
+7.6% +8.4% +4.2%
+9.2%
薪酬结构与薪酬政策
• 什么是薪酬结构？什么是薪酬政策？ • 如何做薪酬结构图？ • 分析什么？
内部公平性分析
• 内部公平性是按绝对和相对标准对内部薪 酬关系合适性的分析
Excel在财务会计中的应用 第3版 项目四 工资管理课件
模块二 工资管理
知识链接
新个人所得税法于2019年1月1日起全面实施，主要变化有： （1）建立对综合所得税按年计税的制度 此次改革，将工资薪金、劳务报酬、稿酬和特许权使用费4项劳务性所得纳入综合征税范围，在年计税 的基础上，实行“代扣代缴、自行申报、汇算清缴、多退少补”的征管制度。 （2）适当提高基本减除费用标准 将综合所得的基本减除费用从原来的3500元/月提高至5000元/月（每年6万元）。 （3）首次设立专项附加扣除 在提高基本减除费用的基础上增加子女教育、继续教育、大病医疗、住房贷款利息或者住房租金、赡 养老人等支出，进一步增强税制的公平性。 （4）调整优化个人所得税税率结构 以改革前工薪所得3%-45%七级超额累进税率为基础，扩大3%、10%、20%三档低税率的级距，缩小25% 税率的级距，30%、35%、45%三档较高税率级距不变。
能力目标
学会使用Excel设置工资项目公式的能力
学会使用Excel筛选功能进行工资数据查 询的能力
学会使用Excel数据透视表进行工资数据 分析的能力
素质目标 培养学生严谨细致的工作态度 培养学生诚信纳税的爱国精神 培养学生勤于思考的学习习惯 培养学生自主学习的学习能力
《Excel在财务会计中的应用》
他首先整理出与工资管理模块相关的部门设置情况、员工基本情况及公司岗位设置情况等信息，具体
如下：
东方公司现设8个部门，包括：行政部、财务部、技术部、采购部、销售部、生产一车间、生产二车间、 生产三车间。职工类别பைடு நூலகம்类，包括：总经理、部门经理、管理人员、采购人员、销售人员、生产人员。员工 人数25名。
《Excel在财务会计中的应用》
《Excel在财务会计中的应用》 任务1.1 建立基本工资信息表
Excel的应用—工资管理.pptx
第3步 建立考勤表
– Value_if_false logical_test 为 FALSE 时返回的值。例如，如果本参 数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE，则 IF 函数将显示文本“超出预算”。如果 logical_test 为 FALSE 且忽略了 Value_if_false（即 value_if_true 后没有逗号），则会返回逻辑值 FALSE。如果 logical_test 为 FALSE 且 Value_if_false 为空（即 value_if_true 后有逗号，并紧跟着右括号），则本参数返回 0（零）。 Value_if_false 也可以是其他公式。
第七次实验
——员工工资管理 2011.09
实验内容
Hale Waihona Puke • 一、本实验知识点 • 二、设计步骤
建立基本数据表格
增加查询数据
制作工资条
保存模板
工资管理系统的创建 工资表的管理 工资条的制作 工资管理系统模板的创建
建立工作表并输入数据 分析工资数据 打印工资条
使用模板
一、本实验知识点
• 1.名称的定义 • 2.自动筛选 • 3.函数的应用:IF()、VLOOKUP() • 4.数据透视表、数据透视图
1.名称的定义
• 基本工资=基本工资!$A$2:$E$13 • 工资表=工资表!$A$3:$K$14 • 补贴表=补贴记录表!$A$2:$E$13
• 考勤表=考勤表!$A$2:$E$13 • 员工奖金记录表=奖金记录表!$A$2:$E$13
2.自动筛选
• 数据筛选是指把数据库或者数据清单中所有不满 足条件的数据记录隐藏起来，只显示满足条件的 数据记录，该条件由用户针对某列指定。常用的 筛选方法有自动筛选、自定义筛选和高级筛选3种， 筛选是查找和处理区域中数据子集的重要的快捷 方式。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
利用EXCEL进行相关薪酬数据分析
薪酬结构与薪酬政策
• 什么是薪酬结构？什么是薪酬政策？ • 如何做薪酬结构图？ • 分析什么？
内部公平性分析
• 内部公平性是按绝对和相对标准对内部薪 酬关系合适性的分析
• 内部公平性分析着眼于： √ 公司岗位价值的关系 √ 薪酬操作VS政策 √ 薪酬离散度 √ 按薪资比率分配薪酬
香港
+2.7% +9.2% +4.9% +0.2% +2.1%
相关分析和回归分析
相关分析和回归分析试验现象之间相关的两种基本 方法。
相关分析是用一个指标来表明现象之间相互依存关系 的密切程度；
回归分析是根据相关关系的具体形态，选择一个合适 的数学模型，来近似的表达变量间的平均变化关系。
有效的模型 员工离职
根据变量的特点选择分析方法
名义变量 次序变量 定距变量 定比变量
是球与衣非号的码判断/存、折概帐念分号
类、不能计算
大奥小运的奖判牌断、、顺评序估分级别
类、间隔不能计算
间距相温同度、、没有海真拔正的
零、加减有意义
间距有财意富义、、有距真离正意义
的零、四则运算有意义
根据变量的特点选择分析方法
名义变量
• 统计学的目的，不单单是给出人们解决问题的可操作性 方法，重要的是能够比较所有方法的好坏。
• 统计推断的结论，不保证其结论是百分之百的自然规律， 但它能证明他们的行为是现有资料下的可以采取的最佳 行为。
——《统计学的起源》
数据分析的五大步骤
• 数据收集 • 数据处理 • 数据分析 • 数据解读 • 数据演示
50,000
0 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
内部公平性分析
400000
结构离散度
350000
300000
250000
200000
150000
100000
50000
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
√ 公司 公司的政策线（或实际情况）与外部市场数据进行比较
√ 部门 部门数据与市场薪酬数据进行比较 部门数据与市场上相同部门进行比较
√ 个人 个人数据与市场薪酬数据进行比较 个人数据与市场相同岗位进行比较
外部竞争力分析
300000 250000 200000 150000 100000 50000
外部竞争力分析
• 外部竞争力是针对外部市场与公司报酬政策和操作的分 析。基本工资和现金总收入是两个标准的分析类别
• 外部竞争力分析着眼于： √ 基本工资和现金总收入的回归分析 √ 基本工资和现金总收入的实际分析 √ 公司竞争力、地域竞争力、职能部门竞争力和岗 位竞争力
外部竞争力分析
• 分析方法：
300,000
250,000
200,000
150,000 100,000
50,000
中位数
பைடு நூலகம்
平均值
0 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
中位数一般小于平均值
平均值与中位数
平均值比中位数高多少个百分点？
薪酬的竞争 职业发展的机会
员工关系 绩效管理
可能的关系
①
②
③
纵坐标为离职率，横坐标为员工的薪酬数据。
④
⑤
⑥
相关性
正相关
无相关
负相关 错误值
最简单的回归模型
一元线性回归
16
14
12
10
8
6
4
2
0
20
40
60
80
100
使这些截距（垂直距离）之和最小
指数回归
300,000 250,000 200,000 150,000 100,000 50,000
• 日常的人力资源管理当中，利用EXCEL我们可以 实现基本的统计分析计算
薪酬分析——分析什么
薪酬分析
• 内部公平性 • 外部竞争性 • 绩效与报酬的关系 • 支付能力（政策） • 战略一致性分析
常用EXCEL八大功能
HR人员要掌握的Excel
• 数据准备——分类、筛选、数据表 • 分析数据——查找、条件格式化、常用函数 • 展示数据——表格与图表
内部公平性分析
• 分析方法：
√ 公司 √ 部门 √ 个人
回归线、整体与政策线的比率 回归线、整体与政策线的比率 个人与政策线比较
内部公平性分析
350,000
薪酬方案和政策比较
300,000 250,000
y = 86.936e0.1268x R2 = 0.9459
200,000
150,000
100,000
集中趋势 离散趋势
众数
（可变比率）
次序变量 定距变量 定比变量
中位值、百分 范围、四分位
位、四分位
范围
平均值、加权
标准偏差
所有以上
所有以上
分布频率与集中趋势
• 众数 • 平均值 • 中位值
• 百分位 • 四分位
• 范围 • 四分位距 • 标准偏差
• 正态分布
平均值与中位数
• 平均值与中位数的关系：
北京 马来西亚 新加坡
一般员工 专业人员 管理层 高级管理层 合计/加权
-9.3% +6.3% +4.6% +5.5% +3.7%
+2.5% +7.2% +9.8% +6.1% +5.3%
+9.9% +10.9% +7.6% +8.4% +4.2%
来源：2001 Mercer 总体薪资调研——全行业
0 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
回归得到的薪酬曲线
y = a*bc Pay= a*bjob size
小结
• 统计是帮助我们从纷繁复杂的数据世界当中寻找 规律的方法之一
• 统计分析的目的是对数据进行解读从而发掘其内 在的规律，并对未来趋势进行预测
《薪酬从业人员基本的数理统 计和分析技巧》课程分享
—— 人力资源部 王凤芝
课程提纲
• 统计学的基本知识 • 薪酬分析——分析什么 • 薪酬从业者常用EXCEL八大功能 • 利用EXCEL做相关薪酬数据分析
统计学的基本知识
什么是统计？
• 统计学是介于理性思维和艺术思维之间的一个科学。它 告诉人们如何在现实世界中收集信息或收集数据，组成 有限的资料；如何使用这些有限的资料，兼顾理性思维 与艺术思维的平衡，采取合适的行动。目的是让我们更 好的掌握环绕我们周围的不确定性。