工时分配率的计算公式 制造费用预算表( 四 )


附加条件
ROUND(真值,2)【表示保留两位小数,四舍五入】
ROUND(MAX((SUM($应发工资所在列$应发工资起始数所在行数:INDEX(应发工资所在列:应发工资所在列,ROW()))-$月份*5000-SUM($社保所在列$社保起始数所在行数:INDEX(社保所在列:社保所在列,ROW()))-SUM($公积金所在列$公积金起始数所在行数:INDEX(公积金所在列:公积金所在列,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-0,2)
ROUND(MAX((SUM($R$16:INDEX(R:R,ROW()))-$B16*5000-SUM($S$16:INDEX(S:S,ROW()))-SUM($T$16:INDEX(T:T,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-0,2)
ROUND(MAX((SUM($应发工资所在列$应发工资起始数所在行数:INDEX(应发工资所在列:应发工资所在列,ROW()))-$月份*5000-SUM($社保所在列$社保起始数所在行数:INDEX(社保所在列:社保所在列,ROW()))-SUM($公积金所在列$公积金起始数所在行数:INDEX(公积金所在列:公积金所在列,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-SUM(个税所在列$个税起始数所在行数:INDEX(个税所在列:个税所在列,ROW()-1)),2)
ROUND(MAX((SUM($R$16:INDEX(R:R,ROW()))-$B17*5000-SUM($S$16:INDEX(S:S,ROW()))-SUM($T$16:INDEX(T:T,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-SUM(U$16:INDEX(U:U,ROW()-1)),2)
测试条件
IF(应发工资-5000>0,真值,0)
IF(应发工资-5000>0,ROUND(MAX((SUM($应发工资所在列$应发工资起始数所在行数:INDEX(应发工资所在列:应发工资所在列,ROW()))-$月份*5000-SUM($社保所在列$社保起始数所在行数:INDEX(社保所在列:社保所在列,ROW()))-SUM($公积金所在列$公积金起始数所在行数:INDEX(公积金所在列:公积金所在列,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-0,2),0)
IF(R16-5000>0,ROUND(MAX((SUM($R$16:INDEX(R:R,ROW()))-$B16*5000-SUM($S$16:INDEX(S:S,ROW()))-SUM($T$16:INDEX(T:T,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-0,2),0)
IF(应发工资-5000>0,ROUND(MAX((SUM($应发工资所在列$应发工资起始数所在行数:INDEX(应发工资所在列:应发工资所在列,ROW()))-$月份*5000-SUM($社保所在列$社保起始数所在行数:INDEX(社保所在列:社保所在列,ROW()))-SUM($公积金所在列$公积金起始数所在行数:INDEX(公积金所在列:公积金所在列,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-SUM(个税所在列$个税起始数所在行数:INDEX(个税所在列:个税所在列,ROW()-1)),2),0)
IF(R17-5000>0,ROUND(MAX((SUM($R$16:INDEX(R:R,ROW()))-$B17*5000-SUM($S$16:INDEX(S:S,ROW()))-SUM($T$16:INDEX(T:T,ROW())))*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-SUM(U$16:INDEX(U:U,ROW()-1)),2),0)
实发工资:实发工资=应发工资-社保-公积金-个税
2、表格调整和多样化使用到这里,整篇文章都结束了,如果有多样化的调整,是可以根据自己的使用习惯来的,我比较喜欢多年内预测,所以,我的表格基本上就是引用和自动计算的,比如在月份上输入的excel公式,当然了,第二年的也是存在公式的 。
基本的 *** 就到这里了,希望大家都能 *** 出属于自己的工资计算表,下期再见 。

推荐阅读