高分求设计编写Excel表格算量公式,内详!

现有一个Excel表格,属于煤质化验后的计量统计表,内容如图片所示。 现有公式不够完善,求重新设计编写或者完善公式,实现以下目的,如公式编写完美可加分。

结算价:
现有公式为:
=IF(ISBLANK(F4),0,(F4-IF(H4<=10,0,FLOOR((H4-10)/0.1,1)*20)-IF(J4<=0.9,0,FLOOR((J4-0.9)/0.01,1)*20)-IF(K4>=75,0,FLOOR((75-K4)/5,1)*20)-IF(L4>=16,0,FLOOR(16-L4,1)*20)))

结算数量:
现有公式为:
=ROUNDDOWN((100-G4)/(100-8)*E4,1)

金额:
现有公式为
=M4*N4

求完善以上公式或者重新编写,并实现以下目的:
1.灰(Ad) <=10,不扣款;10<=灰<=10.5之间,每超0.1扣2元;灰>10.5每超0.1扣3倍,即6元;
2.硫(Std) <=0.8,不扣款;0.8<=硫<=0.85之间,每超0.1扣2元;硫>0.85,每超0.1扣3倍,即6元;
3.挥发份(Vd) <=38,不扣款;38<=挥发份<=38.5之间,每超1扣2元;挥发分>40,每超1扣3倍,即6元;
4.粘结(GR.I) >=75,不扣款;65<=硫<=75之间,每少1扣2元;粘结<65,每少1扣3倍,即6元;
5.Y值 >=15,不扣款;13<=硫<=15之间,每少1扣2元;Y值<13,每少1扣3倍,即6元;
PS:受该表格使用的环境和条件限制,需要做到前面数值往表格里一填写即可出后面的结果,另外也算是帮别人忙,所以如能有一劳永逸的办法最好。
补充图片

1. 在表的右侧没有数据的地方(假设是S,T列)
S1~S5存放扣款项目:灰(Ad),硫(Std),挥发份(Vd) ,粘结(GR.I),Y值
T1~T5存放扣款额,需要在T1~T5输入公式:
T1=IF(H4<=10,0,if(H4<=10.5,FLOOR((H4-10)*20,1),FLOOR((10+(H4-10.5)*60),1)))
T2=IF(J4<=0.8,0,if(J4<=0.85,FLOOR((J4-0.8)*20,1),FLOOR((10+(J4-0.85)*60),1)))
T3=IF(I4<=38,0,if(I4<=40,FLOOR((I4-38)*2,1),FLOOR((4+(I4-40)*6),1)))
T4=IF(K4>=75,0,if(K4>=65,FLOOR((75-K4)*2,1),FLOOR((20+(65-K4)*6),1)))
T5=IF(L4>=15,0,if(L4>=13,FLOOR((15-L4)*2,1),FLOOR((4+(13-L4)*6),1)))

那么在H4~L4输入数值后,T1~T5会自动计算出扣款数。

现在输入结算价公式:
=IF(ISBLANK(F4),0,(F4-T1-T2-T3-T4-T5))
结算数量和金额的公式应该不会有错。

如果不需要在表格中显示S1:T5,执行隐藏操作。

公式较为繁琐,楼主可以把公式带进去试一试,如果计算有误,也可能只是输入疏忽的错误,公式和思路不会有错。

另外,如果楼主不只是计算一家单位的,而是多家单位,建议在每项扣款的旁边都加上一列,超标扣款。这样只要在第四号扣款单元格输入公式,往下拖,就可以得到其他单位的扣款值。追问

求详说计算多家单位的具体操作步骤
[email protected]

温馨提示:答案为网友推荐,仅供参考
第1个回答  2012-02-20
建议你 在你原本表格 的旁边 建一个 价格换算表,就不用这么复杂了。
公式我想了很久没想出来,自己也建了一个Excel 用你发的图片的 数值 想公式。。。。。。。自己 想了很久。
你可以在你表格的边上建一个 附表。 因为我们沉降的数值换算 非常复杂, 我们都是把需要打印的表格 作为一个存档, 再新建一个Excel 作为换算文档使用, 用一个简易的表格单独记录需要换算的数值。 把单独的Ad Std Vd GR.I Y 的 值 分五个单元格计算, 再列出单独的单元格统计金额 把 换算出来的 就是你需要的 标准数值。 直接复制 粘贴到 需要打印的 单元格即可,换算附表的公式 比你要求的公式简单多了 ,我就不多说了。。 。。。。。

这个办法虽然笨一点, 但是非常实用。其实使用起来 也很简单,方便。。
第2个回答  2012-02-20
=F3-IF(AND(H3>10,H3<=10.5),INT((H3-10)/0.1)*2,IF(H3>10.5,10+INT((H3-10.5)/0.1)*6,0))-IF(AND(I3>0.8,I3<=0.85),INT((I3-0.85)/0.01)*2,IF(I3>0.85,10+INT((I3-0.85)/0.01)*6,0))-IF(AND(J3>38,J3<=40),INT(J3-38)*2,IF(J3>40,4+INT(J3-40)*6))-IF(AND(K3>=65,K3<75),INT(75-K3)*2,IF(K3<65,20+INT(65-K3)*6,0))-IF(AND(L3>=13,L3<15),INT(15-L3)*2,IF(L3<13,INT(13-L3)*6,0))

有一点没太搞清楚,比如第一条,是超过10.5以后 10到10.5之间的扣2元 还是 6元追问

10.5以内,每超0.1扣2元,超过10.5以后,每超0.1扣三倍即6元。

追答

=F3-IF(AND(H3>10,H310.5,10+INT((H3-10.5)/0.1)*6,0))-IF(AND(I3>0.8,I30.85,10+INT((I3-0.85)/0.01)*6,0))-IF(AND(J3>38,J340,4+INT(J3-40)*6))-IF(AND(K3>=65,K3=13,L3<15),INT(15-L3)*2,IF(L3<13,4+INT(13-L3)*6,0))

追问

为何公式最后计算得出的是负值?

追答

你F3里边的数太小的话 或者其中有空值的时候 有这种可能

追问

使用你的公式怎么计算都是负值,求解

追答

=IF(AND(F3"",H3"",I3"",J3"",K3"",L3""),F3-IF(AND(H3>10,H310.5,10+INT((H3-10.5)/0.1)*6,0))-IF(AND(I3>38,I340,4+INT(I3-40)*6))-IF(AND(J3>0.8,J30.85,10+INT((J3-0.85)/0.01)*6,0))-IF(AND(K3>=65,K3=13,L3<15),INT(15-L3)*2,IF(L3<13,4+INT(13-L3)*6,0)),"")

本回答被提问者采纳
相似回答
大家正在搜