在薪酬模块的数据分析中,我们经常要对层级和岗位的薪酬数据进行各个分位值的计算,但是由于公司架构的变动,我们层次和岗位也都会变动,一旦这些做了变动,我们如何快速的自动能调整各个层级的分位值数据呢,以前我们的方法是对原始的数据表进行数据透视表,然后在透视表中进行筛选,再做数据的各个分位值计算
比如下面是对各个职级做数据透视表,然后再按照职级进行分类,再通过PERCENTILE的函数来算各个职级的分位值数据。
但是这种方法有一个缺点,一旦原始数据表进行了更新,那数据透视表也会对应的进行更新,数据透视表进行更新后,在透视表里的数据位置就会进行改变,对应的中位值数据就会出现问题。
那如何解决这个问题呢,就是说不管我的层级数据怎么进行改变,我的各个分位值的数据都会随着原始的数据进行变化。
我们先来看下面这张表
这是一个比较简单的各个职级的薪酬数据,我们需要求每个职级的各个分位值数据,然后要求如果我的职级人数增加了,对应的分位值也要跟着做变化。
我们先来讲一下思路,以下面这个表为例,首先对应的是 G列的各个职级,我们让G列的职级数据去D列中找对应的职级,如果职级一样,就显示E列的数据,如果职级不一样就显示空值,这样我们就会获得三个职级的三列数据,就是每个职级对应的薪酬
要实现这个功能,我们用到的是IF函数,具体如下: =IF($D:$D=$G$2,$E:$E,"")
如果D列数据等于G列数据,然后就把对应的数据显示在L列,那我们要求经理的中位值,只需要用PERCENTILE函数去取L列的数据即可,函数如下
就可以获得各个分位值的数据,即使在D列和L列数据增加的情况下,各个职级的分位值数据都会自动的进行变化,动画图如下: