【Excel模板】年底了，赠送九宫人才盘点模板及模板制作方法

年底了，赠送各位HR一个九宫人才盘点模，并介绍模板制作方法。获取模板的方法见文末

01

九宫人才盘点模板介绍

九宫人才盘点（9-box grid)是一个企业人力资源常用的工具，用于全方位评估企业各级人才，对人才进行辨识，从而让高潜力人才付出水平。

九宫人才盘点从概念上是简单的，就是对企业的各级人才从两个维度进行评价，

然后将这两个维度各自分为3个等级，总共分为9个宫格，自动识别每个人属于哪一个宫格：

尽管概念简单，但是实现起来并不是很容易的（实际上，只要涉及到具体的输出格式要求，都不是特别简单的事情）。因此，我们制作了这个模板赠送给大家。

我们赠送的模板就可以根据源数据，自动形成九宫格，并且可以选择不同部门进行分析。

获得模板的办法见本文末

02

制作方法

为了建立从源数据到结果数据的自动化过程，我们使用了Power Query。这种工作方法，我们称之为“Excel工作的标准模式”，可以极大的简化我们的工作，提高工作效率，将我们从繁重的，重复性的手工操作中解放出来。

本模板的建立使用了Power Query，Power Pivot和透视表函数。如果大家不了解，可以参加本公众号相关文章。

选中源数据区域的任意单元格，在“数据”选项卡中点击“从表格”：

数据被导入到Power Query编辑器中：

在“添加列”选项卡中点击“自定义列”，并在对话框中输入公式：=if [通用能力] <= 30 then 1 else if [通用能力] <= 60 then 2 else 3

这个公式将通用能力分为3个等级

点击确定，添加成功

再次点击“自定义列”，输入公式：=if [领导力] <= 30 then 1 else if [领导力] <= 60 then 2 else 3

这个公式将领导力分为3个等级

点击确定后，添加成功。

接下来删除原来的通用能力和领导力两列，将后添加的两列修改列名为通用能力和领导力：

点击“主页”选项卡中的“关闭并上载至”：

在对话框中，选择“仅创建链接”，并且勾选“将此数据添加到数据模型”：

点击确定，完成加载。

接下来，我们使用Power Pivot做出中间结果。

点击Power Pivot选项卡，点击“管理”，

在主页选项卡中，点击“数据透视表”，

在弹出的对话框中保留缺省的选择不变，点击确定，创建透视表。

在Power Pivot选项卡中，点击度量值，点击新建度量值：

输入公式：

=Concatenatex('talent',[员工],"

")

点击确定，创建成功度量值。

将透视表布局按如下方式设置：

至此，其实我们已经设置成功了这个九宫格。但是这里有一个缺陷，就是如果源数据有变化，或者选择不同的部门时，行高会自动调整。这样就会导致格式发生变化（通过数据透视表选项可以设置列宽和其他格式不变，但是行高无法设置），这样的结果不符合我们的要求。因此，我们创建了一个新的用于展示的工作表：

然后点击左上角第一个宫格，输入：=，表示要输入公式，然后到透视表所在的工作表，点击对应的透视表的单元格：

这个操作实际上是在这个单元格中输入公式：

=GETPIVOTDATA("[Measures].[员工们]",Sheet4!$C$3,"[talent 1].[通用能力]","[talent 1].[通用能力].&[1]","[talent 1].[领导力]","[talent 1].[领导力].&[1]")

这是一个透视表公式，如果你不熟悉，可以暂时不用管它，我以后会为大家介绍这个公式。

通过同样的方法可以将这个九宫格和透视表建立关联。

我们的九宫格就完成了。

END