求个excel自动算扣钱的函数

Tips:点击图片进入下一页






图看得见么


按这个扣钱的话:

请事假者扣除当日工资。1天扣除全勤奖的20%,3天及以上扣除全部全勤奖;6天及以上的扣发奖金、职务工资、各种补贴的1/2;达到10天的扣发全部奖金、职务工资、各种补贴。

病假1天扣除日工资的20%;3天及以上扣除全部全勤奖;8天及以上的扣发奖金、职务工资、各种补贴的1/2;达到15天的扣发全部奖金、职务工资、各种补贴。


小于6天的没问题,大于等于6天的就死活不成。不知我这个公式哪出问题了


=ROUND(IF(F2<3,N2/D2*F2+P2*0.2*F2,IF(F2>=3,N2/D2*F2+P2,IF(F2>=6,N2/D2*F2+(O2+Q2+R2+S2+T2+U2+V2+W2+X2+Y2)*0.5+P2))),2)

工资模板 改 - 副本.xlsx

2018-5-29 11:20 上传

点击文件名下载附件

12.16 KB, 下载次数: 7


网友评论:


F2>=3改成F2<6

F2>=6改成F2<10

这样啊?

=ROUND(IF(F2<3,N2/D2*F2+P2*0.2*F2,IF(F2<6,N2/D2*F2+P2,IF(F2<10,N2/D2*F2+(O2+Q2+R2+S2+T2+U2+V2+W2+X2+Y2)*0.5+P2))),2)


求问,算上10天及以上的这个if那怎么写?

话说为啥用>=这个就出错啊?


编辑

因为你逻辑上错了...>=6包含了>10的
另外最好不要用这么个公式来搞,后面很难维护的。
推荐把规则计算单独放到一个地方,然后用match、vlookup之类的按条件查找。

哦  就是把扣多少钱单做一个表 ,用match去对?

求问大于等于10天的用if怎么算啊?


=if(f2<3,0到2天,(if<6,3到5天,(if<10,6到9天,10天以上)))
if部分就是这样,计算部分你也有错误,比如N2/D2*F2,这里/D2除0了....
你干脆把表发附件,把条件写清楚....发到论坛或者excelhome等别人给你搞定吧。
公式我自己倒腾不清,但可以给楼主提供个思路
用 IF(条件),1,0 做判断,后面乘这个条件正确时候想执行的规则,然后 +IF(下个条件)
这样可以无限累加规则,维护起来也简单只要每个规则单独修改就行,整体不会动

这办法也不错,比层层嵌套易读多了。


一个单元格写全部格式后期不容易维护,建议分布计算,见附件(具体公式有误,不要照搬)

工作簿1.xlsx

2018-5-29 11:09 上传

点击文件名下载附件

9.13 KB, 下载次数: 31



这个逻辑错了,应该先if10天以上的,然后if6天以上(已经去掉10天以上的),最后判断是否超过3天。要从跟其他判断条件不覆盖的范围开始。
而且你公司的逻辑也挺混乱的,3天以上包括3天,6天以上包括6天,10天以上就不包括10天了
=ROUND(N2/D2*F2+IF(F2>=10,sum(O 2:Y2),if(F2>=6,(O2+sum(Q2:Y2))/2+P2,if(F2>=3, P2,P2*F2/5))))
其实最好是另外弄个四列分别算0-3,3-6,6-10,10+,然后根据事假天数确定引用哪一格的值

求问if这个是要从大往小  从左到右放数么?

不是这个意思,从大到小或者从小到大还是乱序看具体数据。
你不能这样去记规则,应该记得规则是:excel是从左往右算的(不过右边的公式也不能错)
这什么单位,各种补贴加起来要10w了,招人不?
excel的公式怎么才能增加可读性  我经常老文件里的老公式都看不出到底是干啥的了


回家重新修改了一下,应该符合楼主要求了。@xeseed

工资模板 改 - 副本.xlsx

2018-5-30 16:28 上传

点击文件名下载附件

14.22 KB, 下载次数: 8



试试我的:

如果扣除规则理解有误,自行修改速查那张表就好。

工资模板 改.xlsx

2018-5-30 16:35 上传

点击文件名下载附件

12.94 KB, 下载次数: 13

工资模板 改.xlsx


随便填,试数据好看而已
8000的高温补贴,去地幔么

写注释?

公式怎么写注释
单元格右键插入批注

没市价会错的

其实可以花2个小时学一学vba,就再也不用看那可读性差的函数了……


几个if加加减减就晕的能2小时学vba?另外excel有问题的把附件扔excelhome一般很快就有人帮你解决了
没事干自己倒腾了一遍:

IF(事假<=3,1,0)*(全勤奖*0.2*事假)+IF((事假>3,事假<=6),1,0)*(全勤奖)+IF((事假>6,事假<10),1,0)*(要扣的那一大堆*0.5)+IF(事假>10,1,0)*(要扣的那一大堆)
vba
case
用IF或者VLOOKUP函数就可以的还vba就太麻烦啦。

你的这个表不对楼主路吧?我看了一下,速查的内容是不变的啊?多人怎么处理?
多人的话复制带公式的单元格直接黏贴不就好了?速查那张表做好之后又不需要改的。

你速查表的基础值是不变的啊?每个人的工资奖金什么的都应该是不一样的吧

那是(被发现了)...

其实最好的办法还是用IF()函数嵌套判断,不过要做得严密,公式大约要一尺长。要方便的话还是在第一张表后面做几个辅助列,简化IF函数的表达。

全在剥壳梦中啊!

等会,这速查表是每个人都得做一遍么?

偷懒了~详见32楼说明

其实不要速查表,你把Z2格的公式改为如下就好:

=IF(F2>D2,N2,F2/D2*N2)+IF(F2=0,0,IF(F2<3,P2*0.2,P2))+IF(F2<6,0,IF(F2<10,SUM(O2,Q2:Y2)/2,SUM(O2,Q2:Y2)))   <---更正一下

前面用速查表的方式其实根本没必要,这个计算规则又不难。

@xeseed

求问这个  f2  d2都是啥,我套里边发现算出数来大的吓人

你把上面的公示copy了填到Z2不就能看到了吗?

F2、D2都是单元格啊,F2是实际请假天数,D2是每月应上班天数。

数大的惊人?是啊,你们交通补贴一万,通信补贴一万一,能不惊人吗?怪我咯


哦 对了 我换了个表



换成真的钱数用这公式也多到吓人啊

工资模板 改 - 副本.xlsx

2018-6-1 11:12 上传

点击文件名下载附件

12.16 KB, 下载次数: 4



编辑

我知道你的问题了,你的F2这一格,目前的单元格格式是文本,你把它改成数字,就好了。


哦  对了,还真是
求问这个 F2>D2,N2,   怎么理解呢?

IF(F2>D2,N2,F2/D2*N2)   就是  n2/d2*f2   吧

如果你前面设了每月应出勤天数是22,实际这人请假23,按F2/D2*N2的公式,卡的基本工资扣完之外还多扣他一天日薪,这不合理。应该是实际请假天数大于应出勤天数的话,扣完为止。

这一条就是做个判断,避免扣到人家要倒贴钱的。
画个流程图先把逻辑理顺了再来
啥时候进化了?

应出勤22天 请假23天是输入数据错误咯  根本不需要你去鉴别啊

要是一次操作上千行的表格你也一行行确认?

这个还不如请假这一列直接做个条件格式
这不就是条件函数吗?就算你做条件格式,你不用手动筛选判断?能强过公式里面直接处理了?
建议你做好列表 用vlookup

所有项目都加一个系数 然后用vlookup获得的各个系数的实际值

这样方便你后期调整 系数 如果一旦哪天领导改了要扣30%你就不用重新做了

哦  也是,不过一般也不会出现这种情况哈哈

顺便求个病假的

病假1天扣除日工资的20%;3天及以上扣除全部全勤奖;8天及以上的扣发奖金、职务工资、各种补贴的1/2;达到15天的扣发全部奖金、职务工资、各种补贴。

求问这到15天的咋写?  前边的我到写出来了

=ROUND(IF(g2>D2,N2,N2/D2*g2*0.2)+IF(g2=0,0,IF(g2<3,0,p2))+IF(g2<8,0,SUM(O2,Q2:Y2)/2),2)




突然发现我之前发的那个表格不对,重做并丰富了一下,在黄色单元格能修改判断的天数和扣款的系数,能简单修改,全扣的扣款范围是o列-x列,这个想修改需要加整一列所以我没实现,应该符合楼主要求了。

工资模板 改 - 副本 (2).xlsx

2018-6-1 15:32 上传

点击文件名下载附件

14.76 KB, 下载次数: 0


哦 对了,  IF(F2<3,P2*0.2,P2) 这块应该是 IF(F2<3,P2*0.2*f2,P2)

=ROUND(IF(F2>D2,N2,N2/D2*F2)+IF(F2=0,0,IF(F2<3,P2*0.2*F2,P2))+IF(F2<6,0,IF(F2<10,SUM(O2,Q2:Y2)/2,SUM(O2,Q2:Y2))),2)


你如果是事假和病假一定要分别计算,需要补充规则。

因为事假多于6天就扣奖金、职务工资、各种补贴的1/2,10天全扣;病假分别是8天和15天全扣,如果一个人请了6天事假,8天病假,你是把他“奖金、职务工资、各种补贴”各扣一次50%,等于全扣,还是合起来只扣一次50%?

又或者一个人事假2天(3天起扣50%),病假5天(8天起扣50%),分别都过了“扣50%”门槛的一半,加起来扣不扣50%?


这个规则必须说清楚。
建议你把表格这么设计,不是做成“事假扣除”和“病假扣除”两列,而是做成“基本工资扣除”、“职务工资扣除”、“全勤奖扣除”、“绩效1扣除”。。。等,和前面各列项目对应的扣除列,然后分别计算每列的扣储值,求和就是总计应扣。

IF(F2<3,P2*0.2*f2,P2)是指如果2天扣40%的安全奖?我按照你的要求重新改了下表格,自动判断事假、病假需扣款金额并判断事假病假重复扣款金额(这里默认病假事假扣除奖金各50%的情况下为全扣款),可以简单在第三行修改扣款比例和扣款日期区间,在实出勤数列加入了格式判断自动判断事假病假总和是否超过实出勤数,在事假和病假扣款比例(O3 Q3 X3 Z3这四个,O3需要考虑事假2天×2的情况)相加不超过200%的情况下公式应该不会出错。表格如下,你可以借鉴一下:

工资模板 改 - 副本 (2).xlsx(20.19 KB, 下载次数: 1)2018-6-1 23:31 上传点击文件名下载附件





就是跟扣工资一样  按天数算

我来研究一下这个方法

确实 华点啊,如果2个都请是扣重了
也只能往上边反应了,同时请的比较少 吧

病假1天扣除日工资的20%;3天及以上扣除全部全勤奖;8天及以上的扣发奖金、职务工资、各种补贴的1/2;达到15天的扣发全部奖金、职务工资、各种补贴。

求问这到15天的咋写?  前边的我到写出来了,写15的时候说我输入函数太多不给过,不知咋回事

=ROUND(IF(g2>D2,N2,N2/D2*g2*0.2)+IF(g2=0,0,IF(g2<3,0,p2))+IF(g2<8,0,SUM(O2,Q2:Y2)/2),2)


=IF(G2>D2,N2,G2/D2*N2)*20%+IF(G2<3,0,P2)+IF(G2<8,0,IF(G2<15,SUM(O2,Q2:Y2)/2,SUM(O2,Q2:Y2))) 吧?没问题啊...

你的公式有点问题:

=ROUND(IF(g2>D2,N2,N2/D2*g2*0.2)+IF(g2=0,0,IF(g2<3,0,p2))+IF(g2<8,0,SUM(O2,Q2:Y2)/2),2)
第一处红色这里应该N2*20%才对;
第二处红色这里,小于三天不扣,满三天全扣,应该是IF(G2<3,0,P2)就好,不用嵌套IF( )。
第三处红色IF这里整个乱了?不是应该IF(G2<8,0,IF(G2<15,SUM(O2,Q2:Y2)/2,SUM(O2,Q2:Y2)),跟事假那个同样格式,只是时间由6、10天改为8、15天吗?小于8天为0,8-14天扣一半,超过15天扣那一大堆的全部。