要说Excel中的神公式,我今天介绍的3条公式,基本能够解决多数人的80%以上的大麻烦。有人也许会想这些公式是不是很高级很难,其实不然,这都是些常见的函数,只要我们理解了思路,是可以轻松写出来的。
HR常常需要从身份证号码中提取出每个人的性别信息,而我们都知道身份证号码的长度往往有2种,一种是18位的,另一种则是15位。其中18位身份证号码的倒数第二位数字代表一个人的性别(偶数代表女,奇数代表男);而15位身份证号码则是最后一位数字代表性别信息,同样也是偶数代表女生,奇数代表男生。我们如何快速地将代表性别的数字提取出来并得出性别信息呢?
思路:
1.提取代表性别的数字
=LEFT(RIGHT(B2,1+(LEN(B2)=18)*1))
我们知道:要么是倒数第二位的数字(身份证号码为18位),要么是倒数第一位(15位身份证号码)的数字代表性别,因此我该提取1位还是2位数字,是由身份证号码的长度决定的。因此我们可以得到公式((LEN(B2)=18)*1是将逻辑值true或者false转化为1或者0):
=RIGHT(B2,1+(LEN(B2)=18)*1)
然后我们再在上述公式得到的结果的基础之上,往左边取一位即可得到代表性别的数字了。
2.根据得到的数字判断其奇偶性从而返回性别信息
=MOD(LEFT(RIGHT(B2,1+(LEN(B2)=18)*1)),2)
Mod返回的结果为0或者1,偶数时返回为0,奇数时返回1。接下来我们通过if函数返回性别信息即可。
=IF(MOD(LEFT(RIGHT(B2,1+(LEN(B2)=18)*1)),2),'男','女')
动画演示:
要我说,没有比这个更好的Excel公式了。如下图所示,我们需要将右侧表格中的学生成绩快速地返回到左侧表中,该如何快速地完成这个任务呢?
没错,很多朋友想到了vlookup函数,这的确是一个非常棒的函数,尤其是搭配match函数一起使用时。现在我们就用经典实用的vlookup+match组合来搞定它。
思路:懂点vlookup函数的朋友都知道,完成这个公式并不难,难的是用一条公式完成。他们知道:vlookup函数共计4个参数,在这个例子第一个参数和第二个参数都固定不变,注意引用即可,第四个参数为0,无需变动,唯一麻烦的是第三个参数,每写一列都要更改一次。现在我们用match函数去自动返回A表中的每一列在B表中的位置,公式如下:
=match(B$1,$M$1:$S$1,0)
当公式向右复制时,Excel会自动查找每一科成绩在B表中的位置。然后我们在套上基础的vlookup函数即可。操作步骤:选中A2:G39,在编辑栏输入公式:
=VLOOKUP($A2,$M$2:$S$39,MATCH(B$1,$M$1:$S$1,),)
计算每个人的个人所得税是咱们财务人员或者HR必不可少的工作技能。那么如何快速搞定个人所得税呢?个人所得税的征收规定是这样的:你的税前工资交过扣除五险一金后,工资额大于3500就需要交纳个人所得税了。具体的税率表如下:
具体的计算方式如下:
应纳税所得额 = 税前工资收入金额 - 五险一金(个人缴纳部分) - 起征点(3500元)应纳税额 = 应纳税所得额 x 税率 - 速算扣除数
如下图所示:
这里的税前工资是扣除了五险一金以后的。这里我们用其减掉3500即可得到应缴纳所得税额。然后根据所得税额来计算咱们每一个人的个人所得税。
=B2-3500
我们先根据规定制作如下图的一个表格:
然后根据这个表格通过vlookup函数或者lookup函数返回其对应的税率,然后乘以应缴纳所得税额,最后在通过vlookup函数或者lookup函数返回其对应的速算扣除数即可得到所得税:
税率:=LOOKUP(B2-3500,$F$2:$G$8)或者=VLOOKUP(B3-3500,$F$2:$G$8,2)
速算扣除数:=VLOOKUP(B3-3500,$F$2:$H$8,3)或者=LOOKUP(B2-3500,$F$2:$H$8)
个人所得税公式为:
=LOOKUP(B2-3500,$F$2:$G$8)*(B2-3500)-LOOKUP(B2-3500,$F$2:$H$8)
或者
=VLOOKUP(B3-3500,$F$2:$G$8,2)*(B3-3500)-VLOOKUP(B3-3500,$F$2:$H$8,3)
然而,收入不到税点时,Excel会返回错误值,因此我们需要在外层嵌套一个iferror函数,如下:
=IFERROR(LOOKUP(B2-3500,$F$2:$G$8)*(B2-3500)-LOOKUP(B2-3500,$F$2:$H$8),'')
或者
=IFERROR(VLOOKUP(B3-3500,$F$2:$G$8,2)*(B3-3500)-VLOOKUP(B3-3500,$F$2:$H$8,3),'')
联系客服