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

Question

现有一个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：受该表格使用的环境和条件限制，需要做到前面数值往表格里一填写即可出后面的结果，另外也算是帮别人忙，所以如能有一劳永逸的办法最好。
补充图片

Answer 1

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]

Answer 2

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

这个办法虽然笨一点， 但是非常实用。其实使用起来 也很简单，方便。。

Answer 3

=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)),"")

本回答被提问者采纳