我群有个问题，看看有几个方法解决

欢欢

皮皮(576177843) 11:16:48
请教大家!如果IF嵌套太多了,有什么办法可以处理
=if(H4="单瓦"*I4="纸片",((E4+2)*(G4+2)*2*2.14*1.05+(F4+2)*(G4+2)*2*2.14*1.05)/10000,if(H4="单瓦"*I4="正常",(E4+F4+6)*(F4+G4+3)*2*2.14*1.05/10000,if(H4="单瓦"*I4="叠盖",(E4+F4+6)*(F4+G4+3+F4)*2*2.14*1.05/10000,if(H4="单瓦"*I4="天地盖",(E4+G4+G4+6)*(F4+G4+G4+3)*2.29*1.05/10000,if(H4="双瓦"*I4="纸片",((E4+2)*(G4+2)*2*3.05*1.05+(F4+2)*(G4+2)*2*3.05*1.05)/10000,if(H4="双瓦"*I4="正常",(E4+F4+6)*(F4+G4+3)*2*3.05*1.05/10000,if(H4="双瓦"*I4="叠盖",(E4+F4+6)*(F4+G4+3+F4)*2*3.05*1.05/10000,if(H4="双瓦"*I4="天地盖",(E4+G4+G4+6)*(F4+G4+G4+3)*3.4*1.05/10000,if(H4="单瓦"*I4="单片",E4*F4*2.14*1.05/10000),if(H4="双瓦"*I4="单片",E4*F4*3.05*1.05/10000),"")
皮皮(576177843) 11:18:17
都是物价上涨惹的祸
皮皮(576177843) 11:18:46
全部单价上涨5%
大家看看，闲来无事

tmtony

我晕, 这么长, 用switch 是否可以

欢欢

=IF((H4&I4="单瓦"&"纸片"),((E4+2)*(G4+2)*2*2.14*1.05+(F4+2)*(G4+2)*2*2.14*1.05)/10000)+IF((H4&I4="单瓦"&"正常"),(E4+F4+6)*(F4+G4+3)*2*2.14*1.05/10000)+IF((H4&I4="单瓦"&"叠盖"),(E4+F4+6)*(F4+G4+3+F4)*2*2.14*1.05/10000)+IF((H4&I4="单瓦"&"天地盖"),(E4+G4+G4+6)*(F4+G4+G4+3)*2.29*1.05/10000)+IF((H4&I4="双瓦"&"纸片"),((E4+2)*(G4+2)*2*3.05*1.05+(F4+2)*(G4+2)*2*3.05*1.05)/10000)+IF((H4&I4="双瓦"&"正常"),(E4+F4+6)*(F4+G4+3)*2*3.05*1.05/10000)+IF((H4&I4="双瓦"&"叠盖"),(E4+F4+6)*(F4+G4+3+F4)*2*3.05*1.05/10000)+IF((H4&I4="双瓦"&"天地盖"),(E4+G4+G4+6)*(F4+G4+G4+3)*3.4*1.05/10000)+IF((H4&I4="单瓦"&"单片"),E4*F4*2.14*1.05/10000)+IF((H4&I4="双瓦"&"单片"),E4*F4*3.05*1.05/10000)             就是+法

tmtony

有道理, 不过,这么长的公式实在有些长, 用单元格命名 会不会更清楚一些.
或者使用一些中间临时单元格,可否让每个公司更短一些?

欢欢

呵呵，谢谢版主支持本贴，可就是没有回答问题的，咳@
=CHOOSE(MATCH(H4&I4,{"单瓦纸片","单瓦正常","单瓦叠盖","单瓦天地盖","双瓦纸片","双瓦正常","双瓦叠盖","双瓦天地盖","单瓦单片","双瓦单片"},),((E4+2)*(G4+2)*2*2.14*1.05+(F4+2)*(G4+2)*2*2.14*1.05)/10000,(E4+F4+6)*(F4+G4+3)*2*2.14*1.05/10000,(E4+F4+6)*(F4+G4+3+F4)*2*2.14*1.05/10000,(E4+G4+G4+6)*(F4+G4+G4+3)*2.29*1.05/10000,((E4+2)*(G4+2)*2*3.05*1.05+(F4+2)*(G4+2)*2*3.05*1.05)/10000,(E4+F4+6)*(F4+G4+3)*2*3.05*1.05/10000,(E4+F4+6)*(F4+G4+3+F4)*2*3.05*1.05/10000,(E4+G4+G4+6)*(F4+G4+G4+3)*3.4*1.05/10000,E4*F4*2.14*1.05/10000,E4*F4*3.05*1.05/10000)

pureshadow

哦买嘎达……
晕死了，

tmtony

正确答案都给你一个人写完了, 我还写什么

109091372

Private Sub Command31_Click()
CurrentDb().Execute "delete * from 表2"
End Sub
Private Sub Command31_Click()
CurrentDb().Execute "delete * from 表2"
End Sub