返回目录:excel表格制作
面试的老师一共给了下面的几个老师:
考题1:超额累计提奖计算(函数题)(本期文章详述) 难度:中等
考题2:数据透视表(操作题)(下期文章详述) 难度:入门
考题3:图表制作(操作题)(下期文章详述) 难度:入门
最近,最近一直在找工作,今天面试了一个单位,HR竟然考我的Excel操作水平,并且第一个题目的难度还是不小的,幸好呢小必老师在很早之前给大家研究过这个问题,写了两篇文章 ,这两篇文章分别是:
关于阶梯电价、阶梯销售提奖的计算,HR与财务都要学会的套路
个人所得税的计算,算起来其实很简单的……
有兴趣的小伙伴可以打开上面的文章 再次查找一下,下面小必老师就今天面试时HR给的Excel考题给大家做介绍并做相关的延伸。小伙伴们看一下自己会不会计算呢?
案例:公司的业务人员采取的是销售底薪+提成的薪资结构。具体的提奖比例如下表所示:
比如张三的5月的销售业绩是1500,那么他的提奖计算公式为:
=600*2%+600*3%+300*4%=12+18+12=42.
对于这类问题呢,实际上与个税计算的方法是一样的。是一个阶梯形的提奖,或者也叫超额累计的提奖方法。如果使用IF函数去解决这个问题,公式将是十分地繁琐并且不利用修改与维护,所以采取一种简单的方法,先测出阈值与速算扣除数。
本级速算扣数=上一级最高应纳税所得额×(本级税率-上一级税率)+上一级速算扣除数
阈值:(读音:Yu)又叫临界值,是指一个效应能够产生的最低值或最高值,这里指上一个级上的最高值。以最新的个税表为例来计算速算扣除数。第1级的阈值与速算扣除数是0。
所以,根据上面的阈值的定义与整片扣除数的规则,我们就可以得到下面的一张对应关系表。C4单元格中的公式就可以写成:=C3*(C2-B2)+B4,确定后向右填充。如下图所示:
以上的准备工作做好了就可以进行下面的计算了。
01
超额提奖计算
如下图所示,是业务人员5月的一个销售状况,依据以上的考核规则计算每个人的5月份的提奖应该是多少?
在E2单元格中输入公式:
=ROUND(MAX(D2*{2,3,4,5,6,7}%-{0,6,18,36,60,90},0),2)
按Enter键完成后向下充。结果如下图所示:
知识小卡片
公式中的{2,3,4,5,6,7}与{0,6,18,36,60,90}如何快速地录入呢,这个很简单,首先大家在空白单元格中输入公式:=B3:G3,然后按F9键,公式就完成了={0,6,18,36,60,90},然后再将等号删即可(数据源为已经准备的区域)。这种引用的方式也叫常量引用,是一种在Excel中经常用到的方法。
在个税计算的时候大家都知道,根据个税可以反推出税前的工资。那么同样,在这类的销售提奖的题目中也可以进行反推。(以下部分不属于考试题目)
02
反推
根据上面的计算的结果反推这个人的5月的销售业绩是多少?如下图所示:
在F2单元格中输入公式:
=ROUND(MIN((E2+{0,6,18,36,60,90})/({2,3,4,5,6,7}%)),0)
按Enter键完成后向下填充。结果如下图所示: