薪酬专员必杀技EXCEL进行薪酬分析技巧大全
教你用电子表格之EXCEL做工资大全
教你用EX C E L做工资如果你用电子表格做的话:姓名、岗位工资、年功津贴、技能津贴、绩效工资、卫生费、夜班津贴及安全奖、加班工资、补发工资、应发工资、养老保险、医疗保险、失业保险、住房公积金、其他保险、所得税基数、个人所得税、其他扣款、实发工资、领款人签章其中应发工资=岗位工资+年功津贴+技能津贴+绩效工资+卫生费+夜班津贴及安全奖+加班工资+补发工资所得税基数=应发工资-养老保险-医疗保险-失业保险-住房公积金-其他保险实发工资=应发工资-养老保险-医疗保险-失业保险-住房公积金-其他保险-个人所得税-其他扣款E x c e l的秘技一、建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。
1.在S h e et2中，将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，建立一个企业名称数据库。
2.选中A列(“工业企业”名称所在列)，在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。
仿照上面的操作，将B、C……列分别命名为“商业企业”、“个体企业”……3.切换到S he et1中，选中需要输入“企业类别”的列(如C 列)，执行“数据?有效性”命令，打开“数据有效性”对话框。
在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”……序列(各元素之间用英文逗号隔开)，确定退出。
再选中需要输入企业名称的列(如D列)，再打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中输入公式:=IN D IR E CT(C1)，确定退出。
4.选中C列任意单元格(如C4)，单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。
然后选中该单元格对应的D 列单元格(如D4)，单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。
薪酬操作与设计提高效率之IF函数的计算技巧
薪酬操作与设计提高效率之IF函数的计算技巧一、Excel特殊函数的运用做工资，有一些特定的时间点，每月的发工资日，年终奖发放节点等，为了不延误，平时要做好基础工作，不要把所有的录入、加工都在最后一周做。
而且，Excel做工资表，特殊函数的熟练运用可以有效提高计算效率，这对新手变为熟手来说，是很重要的技能。
二、IF函数的计算技巧计算公式：=IF（条件，是则结果，否则结果）。
三、模拟问题1（一）问题提成W=毛利M*提成比例P。
其中：当销售额达成率Q≥100%时，P=10%；当销售额达成率Q<100%时，P=8%。
实际数据：毛利W=20万，计划销售额=1000万，预测实际销售额=1100万，1000万，900万。
请在Excel中，用IF函数设置提成比例P的自动计算，并得到提成W是多少。
（二）技巧分析新建Excel表，见表2.16。
销售额达成率P=预测实际销售额/计划销售额*100%。
表2.16 IF条件函数的计算技巧-提成比例P的自动计算假定情况1在行3，销售额达成率P在D列，则计算公式设置：D3=C3/B3。
下来，自动完成D4、D5的计算公式设置。
毛利在E列，提成比例T在F列，提成W在G列，则提成W=毛利M*提成比例T，Excel计算公式设置：G3=E3*F3。
关键是提成比例T，如何用IF条件函数设置。
这在情况有多种需要计算是有助于提高效率。
第一步：我们把前面提及的计算公式：=IF（条件，是则结果，否则结果）细化如下：F3=IF(D3>=100%,10%,8%)这个公式，就是表达了：当P>=100%时，提成比例T=10%，否则，当P<100%时，提成比例T=8%。
注意：IF后面，先同时按下键盘“Ctrl键+空格键”，从“全角“转换为“半角”，或按下大写字母锁定键“CapsLock”，否则在提成比例P对应的情况1的格子，会出现错误提示：#NAME?。
很可能就是没切换到半角状态。
人事行政excel使用技巧-薪酬奖金核算技巧篇
三茅人力资源网9:30打卡学习奖励资料人事行政excel使用技巧(三)薪酬奖金核算技巧篇目录第1 部分 ( 星期一)考勤统计提醒功能篇 (3)第2 部分 ( 星期二)员工信息管理公式篇 (3)第3 部分 ( 星期三)薪酬奖金核算技巧篇 (3)第4 部分 ( 星期四)入职离职分析图表篇 (3)第5 部分 ( 星期五)人事年度报表组合篇 (3)1、输入方式（1） 分数的输入如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。
（2） 序列“001”的输入如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“‘”（西文单引号），然后输入“001”。
（3） 日期的输入如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。
如果要输入当前日期，按一下“Ctrl+；”键。
（4） 填充条纹如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。
先在一单元格内填入“*”或“~”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮（如图1）。
图１（5） 多张工作表中输入相同的内容几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。
输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。
（6） 不连续单元格填充同一数据选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。
人力资源常用EXCEL函数汇总
人力资源常用EXCEL函数汇总在人力资源工作中，Excel是一项非常重要的工具，它可以帮助我们进行数据分析、计算和报表制作。
下面是一些人力资源常用的Excel函数的汇总：1.SUM函数：用于计算一系列数字的总和。
在人力资源中，可以使用SUM函数计算薪资总额、奖金总额等。
2.AVERAGE函数：用于计算一系列数字的平均值。
在人力资源中，可以使用AVERAGE函数计算员工的平均薪资、平均加班时数等。
3.COUNT函数：用于计算一系列单元格中包含数字的个数。
在人力资源中，可以使用COUNT函数计算员工数量、未请假天数等。
4.MAX函数和MIN函数：用于计算一系列数字中的最大值和最小值。
在人力资源中，可以使用MAX函数和MIN函数找出最高和最低薪资、最长和最短请假天数等。
5.IF函数：用于根据条件判断进行相应的计算。
在人力资源中，可以使用IF函数根据员工绩效进行薪资调整。
6.VLOOKUP函数：用于在一个区域中查找一些值，并返回相应的数值。
在人力资源中，可以使用VLOOKUP函数根据员工姓名查找相应的薪资等级。
7.CONCATENATE函数：用于将多个文本字符串连接在一起。
在人力资源中，可以使用CONCATENATE函数将员工的名字和姓氏拼接成一个完整的姓名。
8.DATE函数：用于创建一个日期值。
在人力资源中，可以使用DATE函数计算员工的入职年限、工龄等。
WORKDAYS函数：用于计算两个日期之间的工作日天数。
在人力资源中，可以使用NETWORKDAYS函数计算员工请假天数、工作期限等。
10.RANK函数：用于计算一系列数值在一个区域中的排名。
在人力资源中，可以使用RANK函数计算员工的绩效排名。
11.ROUND函数：用于将一个数值四舍五入到指定的位数。
在人力资源中，可以使用ROUND函数计算员工薪资的四舍五入值。
12.PMT函数：用于计算贷款的每月还款额。
在人力资源中，可以使用PMT函数计算员工的工资分期付款每月还款额。
HR必会的EXCEL技巧
HR必会的EXCEL技巧也许你已经在Excel中完成过上百张财务报表，也许你已利用Excel函数实现过上千次的复杂运算，也许你认为Excel也不过如此，甚至了无新意。
但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧的百分之一。
今天小编从Excel中的一些鲜为人知的技巧入手，领略一下关于Excel的别样风情。
一、让不同类型数据用不同颜色显示在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。
1.打开“工资表”工作簿，选中“工资总额”所在列，执行“格式→条件格式”命令，打开“条件格式”对话框。
单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。
单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。
2.按“添加”按钮，并仿照上面的操作设置好其它条件(大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”)。
3.设置完成后，按下“确定”按钮。
看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。
二、建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。
1.在Sheet2中，将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，建立一个企业名称数据库。
2.选中A列(“工业企业”名称所在列)，在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。
仿照上面的操作，将B、C……列分别命名为“商业企业”、“个体企业”……3.切换到Sheet1中，选中需要输入“企业类别”的列(如C列)，执行“数据→有效性”命令，打开“数据有效性”对话框。
在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”……序列(各元素之间用英文逗号隔开)，确定退出。
hr常用excel公式
hr常用excel公式HR 常用 Excel 公式在人力资源管理中，Excel 是一个非常常用的工具，可以帮助 HR 专业人士处理各种任务和数据。
以下是一些 HR 常用的 Excel 公式，可以帮助简化数据处理、计算和分析。
1. SUM 函数：用于计算一列或多列数字的总和。
例如，可以使用 SUM 函数计算员工工资总和或某个部门的总支出。
2. AVERAGE 函数：用于计算一列或多列数字的平均值。
例如，可以使用AVERAGE 函数计算员工绩效评分的平均值。
3. COUNT 函数：用于计算一列或多列单元格中包含数字的个数。
例如，可以使用 COUNT 函数计算某个部门的员工人数。
4. MIN 函数：用于找出一列或多列数字中的最小值。
例如，可以使用 MIN 函数找出某个部门的最低工资。
5. MAX 函数：用于找出一列或多列数字中的最大值。
例如，可以使用 MAX 函数找出某个部门的最高工资。
6. VLOOKUP 函数：用于在一个区域中查找特定值，并返回该值所在行或列的数值。
例如，可以使用 VLOOKUP 函数查找员工的工资等级。
7. CONCATENATE 函数：用于合并两个或多个文本字符串。
例如，可以使用CONCATENATE 函数将员工的名字和姓氏合并成一个完整的名字。
8. IF 函数：用于根据指定条件返回不同的值。
例如，可以使用 IF 函数根据员工的绩效评分决定是否给予奖金。
9. COUNTIF 函数：用于计算满足特定条件的单元格个数。
例如，可以使用COUNTIF 函数计算某个部门中绩效评分达到预期标准的员工数量。
10. SUBTOTAL 函数：用于在过滤或排序数据时计算结果。
例如，可以使用SUBTOTAL 函数在筛选出某个部门的员工数据后重新计算平均工资。
这些是 HR 所常用的 Excel 公式，它们可以极大地简化工作流程，提高效率和准确性。
熟练掌握这些公式，对于 HR 专业人士来说是非常重要的技能。
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%
薪酬结构与薪酬政策
• 什么是薪酬结构？什么是薪酬政策？ • 如何做薪酬结构图？ • 分析什么？
内部公平性分析
• 内部公平性是按绝对和相对标准对内部薪 酬关系合适性的分析
薪酬水平25分位excel公式
薪酬水平25分位excel公式常见的excel函数公式有很多，如if函数公式=if(条件区域)，求和函数公式=sumif(求和条件区域)，日期计算函数公式=datedif(条件区域) 等。
一、数字处理01.挑绝对值=abs(数字)02.数字取整=int(数字)03.数字四舍五入=round(数字,小数位数)二、判断公式04.把公式回到的错误值表明为空公式：c2=iferror(a2/b2,'')表明：如果就是错误值则表明为空，否则正常表明。
05.if的多条件判断公式：c2=if(and(a2\uc\ue说明：两个条件同时成立用and,任一个成立用or函数。
三、统计数据公式06.统计两表重复公式:b2=countif(sheet15!a:a,a2)说明：如果返回值大于0说明在另一个表中存在，0则不存在。
07.统计数据年龄在30~40之间的员工个数=frequency(d2:d8,{40,29})08.统计不重复的总人数公式：c2=sumproduct(1/countif(a2:a8,a2:a8))说明:用countif统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。
09.按多条件统计数据平均值f2公式=averageifs(d:d,b:b,'财务',c:c,'大专')10.中国式位列公式=sumproduct(($d$4:$d$9\ue=d4)*(1/countif(d$4:d$9,d$4:d$9)))四、求和公式11.隔列议和公式：h3=sumif($a$2:$g$2,h$2,a3:g3)=sumproduct((mod(column(b3:g3),2)=0)*b3:g3)表明：如果标题行没规则用第2个公式12.单条件求和公式：f2=sumif(a:a,e2,c:c)说明：sumif函数的基本用法13.单条件模糊不清议和说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如'*a*'就表示a前和后有任意多个字符，即包含a。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
•
•
•
最简单的回归模型 •一元线性回归
指数回归
•使这些截距（垂直距离）之和最小
回归得到的薪酬曲线
y = a*bc Pay= a*bjob size
小结
• 统计是帮助我们从纷繁复杂的数据世界当中寻找 规律的方法之一
• 统计分析的目的是对数据进行解读从而发掘其内 在的规律，并对未来趋势进行预测
• 日常的人力资源管理当中，利用EXCEL我们可以 实现基本的统计分析计算
• 正态分布
平均值与中位数
• 平均值与中位数的关系：
•中位数
•平均值
中位数一般小于平均值
平均值与中位数
平均值比中位数高多少个百分点？
相关分析和回归分析
相关分析和回归分析试验现象之间相关的两种基本 方法。 相关分析是用一个指标来表明现象之间相互依存关系 的密切程度； 回归分析是根据相关关系的具体形态，选择一个合适 的数学模型，来近似的表达变量间的平均变化关系。
有效的模型 •员工离职
•薪酬的竞争 •职业发展的机会
•员工关系 •绩效管理
可能的关系
•①
•②
•③
•纵坐标为离职率，横坐标为员工的薪酬数据。
•④
•⑤
•⑥
相关性
•正关相
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•无关相 • •
•
•
•
•
•
•负关相
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•错值误 •
•
•
•
•
根据变量的特点选择分析方法
பைடு நூலகம்
• 名义变量 • 次序变量 • 定距变量 • 定比变量
••球是 分衣与号非码的/判存断折、帐概号念
• 类、不能计算
••奥大运小奖的牌判、断评、顺估序级分别
• 类、间隔不能计算
• 间•距温相度同、、海没有拔真正
的 • 零、加减有意义
•财富、距离
• 间距有意义、有真正 意义
根据变量的特点选择分析方法
薪酬专员必杀技EXCEL 进行薪酬分析技巧大全
2020年7月20日星期一
课程提纲
• 统计学的基本知识 • 薪酬分析——分析什么 • 薪酬从业者常用EXCEL八大功能 • 利用EXCEL做相关薪酬数据分析
统计学的基本知识
什么是统计？
• 统计学是介于理性思维和艺术思维之间的一个科学。它 告诉人们如何在现实世界中收集信息或收集数据，组成 有限的资料；如何使用这些有限的资料，兼顾理性思维 与艺术思维的平衡，采取合适的行动。目的是让我们更 好的掌握环绕我们周围的不确定性。
•
内部公平性分析
外部竞争力分析
• 分析方法：
√ 公司 公司的政策线（或实际情况）与外部市场数据进行比较
√ 部门 部门数据与市场薪酬数据进行比较 部门数据与市场上相同部门进行比较
√ 个人 个人数据与市场薪酬数据进行比较 个人数据与市场相同岗位进行比较
•
外部竞争力分析
薪酬分析
• 绩效与报酬的关系 • 支付能力 • 业务一致性分析
•
课程回顾
• 了解能用EXCEL做哪些分析 • 薪酬分析中常用的函数和公式 • 数据处理与整理 • 使用图表和展示数据
谢谢参与！
• 名义变量 • 次序变量 • 定距变量 • 定比变量
• 集中趋势
• 众数
离散趋势
• （可变比率 ）
• 中位值、百 分
• 位、四分位
• 范围、四分 位
• 范围
• 平均值、加 权
• 标准偏差
• 所有以上 • 所有以上
分布频率与集中趋势
• 众数 • 平均值 • 中位值
• 百分位 • 四分位
• 范围 • 四分位距 • 标准偏差
内部公平性分析
• 分析方法：
√ 公司 √ 部门 √ 个人
回归线、整体与政策线的比率 回归线、整体与政策线的比率 个人与政策线比较
外部竞争力分析
• 外部竞争力是针对外部市场与公司报酬政策和操作的分 析。基本工资和现金总收入是两个标准的分析类别
• 外部竞争力分析着眼于： √ 基本工资和现金总收入的回归分析 √ 基本工资和现金总收入的实际分析 √ 公司竞争力、地域竞争力、职能部门竞争力和岗 位竞争力
薪酬结构与薪酬政策
• 什么是薪酬结构？什么是薪酬政策？ • 如何做薪酬结构图？ • 分析什么？
内部公平性分析
• 内部公平性是按绝对和相对标准对内部薪 酬关系合适性的分析
• 内部公平性分析着眼于： √ 公司岗位价值的关系 √ 薪酬操作VS政策 √ 薪酬离散度 √ 按薪资比率分配薪酬
内部公平性分析
• 统计学的目的，不单单是给出人们解决问题的可操作性 方法，重要的是能够比较所有方法的好坏。
• 统计推断的结论，不保证其结论是百分之百的自然规律 ，但它能证明他们的行为是现有资料下的可以采取的最 佳行为。
——《统计学的起源》
数据分析的五大步骤
• 数据收集 • 数据处理 • 数据分析 • 数据解读 • 数据演示
薪酬分析——分析什么
薪酬分析
• 内部公平性 • 外部竞争性 • 绩效与报酬的关系 • 支付能力（政策） • 战略一致性分析
常用EXCEL八大功能
HR人员要掌握的Excel
• 数据准备——分类、筛选、数据表 • 分析数据——查找、条件格式化、常用函数 • 展示数据——表格与图表
利用EXCEL进行相关薪酬数据分析