17个有用的Excel人力资源公式和函数

微软Excel不能代替你的核心人力资源管理系统或高级人力资源分析，但作为人力资源的日常跟踪和临时报告工具，它是很难被击败的。使用Excel中的人力资源公式和函数来回答问题并做出更好的决策，将帮助您在成为数据驱动的人力资源专业人员的道路上走得更远。它还将帮助你理解你的嵌入式分析工具告诉你什么。

使用Excel比依靠IT来做报告更容易、更快、更便宜，而且你的人力资源团队中有一个或多个人员至少对它有一定的了解。

然而，大多数用户的技能只是简单地跟踪和使用其他人创建的报告。我们发现，成为人力资源部的“Excel向导”对你的就业能力和收入有积极的影响。

微软大量的文档是为日常用户编写的。优秀的培训资源和数十本优秀的书籍使学习变得容易——对我们来说，学习从未停止。

考虑到这一点，我们在这里提供了一些最常用的Excel人力资源公式、工具和函数。我们希望你发现它们有用，并渴望了解更多。

内容
处理日期
使用透视表
格式表
常见的人力资源公式和指标

处理日期

让我们从一些常用的日期管理函数开始。

Excel将日期存储为以1开头的连续序列号，从1900年1月1日凌晨00:00开始。2021年3月16日是44271，因为它是44271^圣自Excel时间开始的第一天。

小于一天的值存储为十进制分数。

Excel为我们提供了多种存储、比较和计算日期和时间的方法。日期有16种标准格式，自定义格式规则可以让您更好地控制显示。要选择格式，请右键单击单元格或日期列表，并选择其中一种日期格式或选择自定义格式以查看更多内容。

在开始计算日期之前，让我们看看如何输入今天的日期——即使我们不知道它是什么。

使用TODAY()函数

TODAY是一个节省时间的快捷方式。它从你的电脑中检索今天的日期和时间，所以它会自动更新。当您打开或更改工作表时，工作表将自动更新。

该函数对于计算年龄、服务时间或任何您所使用的周期没有特定结束的情况非常方便。例如，如果要计算员工的年龄，可以从今天减去员工的出生日期。首先，我们将列格式化为显示年份，而不是序列号或日期。

1. 右键单击年龄列标题，操作面板将打开。
2. 选择格式细胞……
3. 自定义打开选项;然后输入"yy(没有引号)类型字段。
4. 点击好吧．

求两个日期的差值

人力资源部门最常做的一些事情是衡量两次约会之间的差异。我们使用这些计算有很多原因，包括员工年龄、服务时间、福利资格、养老金和资历等基本因素。

计算差异的方法有很多种。如何计算它们取决于您公司的实践、行业部门和国家或地区。

我们将为您提供四个我们认为有用且易于使用的方法:

DATEDIF函数

Excel DATEDIF返回以年、月或日为单位的两个日期值之间的差值。DATEDIF(日期+ Dif)函数是一个函数“兼容”函数来自Lotus 1-2-3．

=DATEDIF(start_date, end_date, unit)

单位值

• “y”年
• “m”几个月
• “d”天
• “ym”月份，忽略年份
• “yd”的天数，忽略年和月

我们可以使用以下公式计算年、月和日:

得到这样的结果:

下面是在单个单元格中使用年、月和日的相同方法:

DAYS360函数

我们的公历给会计人员带来了麻烦，所以他们发明了360天的一年，每个月有30天，以创建一个整洁的框架。

虽然美国证券交易委员会(SEC)不接受它作为正式公布的财务账目，但是360天商年被广泛使用简化全球商业的利息计算和内部估值。两种公认的标准是美国(NASD)方法和欧洲方法。

语法为:

=DAYS360(start_date, end_date, method)

其中method表示360天的标准:美国方法为假，欧洲方法为真。

• 在美国方法中，如果开始日期是该月的最后一天，则该日期为30^th每月的第一天。结束日期为本月最后一天，且开始日期小于30^th一个月的第一天，结束日等于1^圣下个月的一天。否则，结束日期就等于30^th同月的。
• 在欧洲的方法中，开始和结束日期在31日^圣这个月等于30^th本月的。这样算不算整洁?

警告:

日期必须从另一个函数派生，或者使用DATE格式输入:

日期(yyyy, m, d)在哪里日期(2021、3、11)是2021年3月11日。

如果start_date在end_date之后，DATEDIF公式返回一个负数。

YEARFRAC函数

如果您想要计算两个日期之间的差异(年份和年份的分数)，YEARFRAC将为您做这件事。函数的参数是开始日期，结束日期和基础，其中基础是0到4:

基础值

0(或省略)	Us (nasd) 30/360
1	实际/实际
2	实际/ 360
3.	实际/ 365
4	欧洲的30/360

下面是函数本身:
= YEARFRAC (start_date end_date,(基础))

如果你使用YEARFRAC和TODAY()函数，它总是正确的。

净工作日功能

NETWORKDAYS是一个方便的功能，用于管理项目、日程安排或任何需要计算包括假期在内的工作日跨度的时间。

=净工作日(start_date end_date、节假日)

以下是我们最常设置假期时间表的方法。我们创建一个名为Holidays的工作表，其中包含假日的日期和名称，然后:

1. 选择单元格范围$A$2:$A$10。
2. 点击定义的名字．将打开“新名称”窗口。
3. 中输入“节假日”的名字字段。
4. 单击OK。

然后，我们可以在工作簿的任何地方输入一个NETWORKDAYS函数，如下所示:

格式表

在开始使用查找公式之前，让我们讨论一下如何格式化员工表，以便函数和过程能够一致地工作。

每个表都应该有一个主键。在结构化查询语言(SQL)中，一列或一组列是唯一标识符。在Excel中，我们希望主键是标识每个数据行的单列。

如果使用员工信息，键几乎总是employee ID。将它放在每个列出员工的工作表的第一列(最左边)，可以更容易地管理VLOOKUP、INDEX、MATCH和INDEX-MATCH函数。

格式为表格函数

我们喜欢为表和数组命名，以使它们更易于使用。一个快速的方法就是使用Excel格式为表格函数。

1. 确保你的桌子是空的连续的，这意味着列和行之间没有间隙。
2. 从Home选项卡，单击左上角单元格(A1)。
3. 点击格式为表格向下箭头。设计面板将打开。

4. 点击你想要的设计。表表单将打开，显示表范围并指示表是否有表头。您将需要标题，因此如果没有，请返回并重新格式化您的表。

5. 点击好吧．您的表将被自动格式化。
6. 给表起一个对您有意义的名字。现在可以在函数和公式中使用表名。

您还可以使用键盘快捷键来创建表格。

1. 突出显示整个表格。
2. 按Ctrl + T

然后，您可以按照自己喜欢的方式格式化它。

过滤数据

如果您使用Excel工作表，您可能会注意到您的表自动具有筛选器。当您想要分割数据时，过滤器可以极大地节省时间。当您想要验证表中的数据时，它们也很有用。

假设您需要跟踪尚未完成的绩效评估。您有一个报告，其中列出了所有具有评估状态的员工。

您可以按状态对工作表进行排序，并复制和粘贴需要跟踪的工作表，但是通过选择要跟踪的状态，可以很容易地应用筛选器。在这个例子中，我们想要找到所有还没有开始的评估。

1. 点击过滤器下拉图标。

2. 控件上的(选择所有)复选框。

3. 选择没有开始,然后好的。

下面是过滤后的表的样子:

可以根据列中的数据类型以多种组合方式使用多个筛选器。数字过滤器有很多组合，日期也是如此。

假设我们需要一个在2016年1月1日到2018年12月31日之间雇用的人员列表。

1. 点击聘用日期列过滤器。将打开筛选器选项列表。
2. 选择日期过滤器选择。您现在可以得到一个很长的筛选选项列表。

3. 选择之间的并填写日期参数。

你的报告准备好了:

使用透视表

在Excel的所有特性、函数和工具中，数据透视表是最有用的。它们是从数据库、工作表中的表、外部数据文件或外部数据库生成的动态摘要报告。

的数据透视表函数使您能够在几次单击中将任何数据源转换为健壮、灵活的数据摘要。然后，您可以以您所能想象到的任何方式对数据进行切片、汇总和组合以及操作。

我们将向您展示如何按部门创建终止原因报告。您可以获得我们正在使用的数据集的副本Kaggle（镜子）.

下面是如何从任何工作表创建一个报告:

1. 首先，我们点击左上角单元格(A1)在员工数据的连续表中。一个好的数据示例将在A列中包含员工编号(每个人的唯一值)。
2. 我们点击插入菜单。
3. 然后，我们点击数据透视表图标。的创建数据透视表表单将打开。

4. Excel会自动选择我们的整个数据表。
5. 我们选择新工作表来告诉Excel把表格放在哪里。
6. 然后,单击好吧．

7. 将打开一个新选项卡，左侧是一个空白的数据透视表数据透视表字段从右边开始。现在，我们先做家务，重命名标签，以便稍后找到它。

8. 在数据透视表字段形式,拖终止日期到值节，为表提供一个可供计数的数值。

9. 我们也可以拖动部门，员工的名字，位置,开学日期到行部分。

在这一点上，表格将开始成型，但它将是一个混乱的混乱。我们现在就解决这个问题。

10. 单击表中的任意位置，然后单击设计。将出现设计菜单。
11. 单击报表布局。
12. 我们点击以表格形式显示．这开始有意义了，但是我们不需要在Employee Name列中有一个小计。

13. 右键单击员工的名字列，然后点击小计“员工姓名”取消勾选。我们对位置列。

我们现在有了一个简洁的表格，显示了每个部门的解雇人数和原因。

只是需要做一点清理工作。有些列名有点长，令人困惑。我们可以直接在列标题中输入新名称。

再瘦一点旅客:

让我们来看看为什么这个工具被称为数据透视表。

在数据透视表字段形式，我们抓住部门字段，并将其拖到过滤器部分,主将字段从行字段转换为筛选器，我们可以在其中更改报告以显示单个部门或任何组合。

现在，我们来试试另一个主元。右键单击原因字段，悬停在移动，然后点击把“原因”移到开始．

我们现在有一份关于解雇原因的报告。

数据透视表列

如果希望查看每个部门总结的终止原因，可以使用列来显示这些部门。

让我们删除报告的名称和位置以减少混乱，然后将部门拖到Columns部分。我们现在有一个总结报告，但它不是很有用。

让我们改变一下总结数据的方式。在Grand Total单元格中右键单击并选择显示值为,然后总金额的%．

在我们使用Grand Total单元格将数字格式化为不带小数的百分比之后，我们得到了一个报告，该报告告诉我们可能需要进行干预的地方。

我们只是简单介绍了数据透视表的功能。我们希望它能激起你了解更多的欲望。

浏览我们的培训课程看看如何将数据透视表应用到人员分析中。一旦你掌握了他们，当你的团队中有人需要快速回答时，你会发现他们是你最好的朋友。

常见的人力资源公式和指标

虽然有数百个公式和函数可以帮助人力资源完成工作，但只有少数几个脱颖而出，因为它们支持重要的人力资源指标。

福利和员工计划的成本

计算福利(包括所有员工计划)可以帮助我们计算福利成本，并计算员工的总成本，以用于其他内部指标。

每个员工的福利成本

我们用福利成本与平均员工数量的比率来计算福利成本。

benefits = total_cost_of_benefits_and_programs ÷ average_number_of_employees

你可以把这个成本按任何时期进行比较。

许多公司分别计算小时工、受薪员工和高管的比率。

福利和项目占工资的百分比

我们以百分率计算支付的福利。

benefits = cost_of_benefits ÷ total_pay

员工总成本

一旦我们得到了福利比率，我们就可以用它来计算员工的总成本。

Total_employee_cost = (1 + benefits_ratio) × total_pay

收入

在人力资源部门，我们有时会过于关注成本，而忘记展示我们的业务影响。我们建议与整个企业的经理和领导者合作，在进行改变或实施干预时回答最重要的问题。它们是:移动什么?差多少?

每个员工的收入

这些问题的一个快速答案是每个员工的收入。我们从财务报告中获得顶线收入，然后除以员工人数。

Revenue_per_employee = average_number_of_employees_÷ top_line_revenue

任期和人员流动

任期内

您可以使用上面解释过的YEARFRAC函数，在具有Hire Date的员工列表上使用平均函数获得平均任期。

1. 将列表格式化为表格。
2. 在Hire Date旁边创建一个名为Tenure的新列。然后，在顶部单元格中输入公式。
   = yearfrac ([hire_date],今天())
3. 在“数字”菜单组中，使用逗号图标(会计格式)。
4. 在“表设计”选项卡中，选中“总行”复选框。视图将跳转到最下面一行。

5. 在“保留区”列中，选择平均函数。

人才获取指标

招聘成本是一个关键的商业指标。你需要知道雇佣人才需要花费多少时间和成本。然后，你可以将这些成本与你的花费进行比较员工敬业度留存和发展。

是时候填补空缺了

在计算招聘成本时，寻找、聘用、选择和聘用候选人所需的时间是一个关键因素。

一个低效的渠道会导致成本增长失控，其影响远远超过运营招聘职能的成本。你们的人必须填补空缺，而且你们可能会产生加班费。报道此事的人会有额外的压力。

首先，我们定义一个名为“Holidays”的表，将其用作NETWORKDAYS函数参数。

每次租用成本

每次招聘成本包括填补空缺、广告和招聘的直接成本，以及招聘过程中经理和招聘人员的成本。下面是用各自的人力资源公式分析人才获取成本的样本。

成本	公式
填补空缺的费用	工资X天到岗+加班分配
招聘人员有时间寻找、筛选和面试候选人	(招聘人员时薪+福利比)×工作时间
经理有时间面试、评估和选择候选人	(经理工时工资+福利比率)×工时

产额比

为了了解你的人才获取功能的效率和效果，你需要衡量流程中的每个步骤如何运作。只有当你这样做的时候，你才能做出明智的决定，什么是有效的，什么是无效的。

收益率是指进入下一阶段的申请者的百分比。

Yield_ratio = applicants_at_step ÷ applicants_at_previous_step

招聘速度

填补时间和招聘速度是不一样的。填满的时间只给你整个过程的长度。许多公司现在开始衡量这一过程每一步的进展有多快。关键时刻的任何延误都可能导致候选人“鬼影”。

你可以通过收益率跟踪速度。这是公式。

结果呢。我们计算了填充最后一列的总时间。

轮到你了

我们希望这个快速概述能让您了解如何做出数据驱动的决策。我们鼓励你在实践中学习，并应用这些人力资源公式和职能。最终，利用你的发现来指导和调整你的日常运营，并改进你的人力资源战略。