UC知道有关excel公式错误的问题，请高手指点！
来源：百度知道 编辑：UC知道 时间：2024/05/16 08:53:43
在做一个投资模板
其中某单元格需7层If套用如下：
ROUND(IF(E3=CEILING('General Assumption'!$G$16,1),-SUM($D53:D53,$D34:D34),IF(E3>CEILING('General Assumption'!$G$16,1),0,IF(E3>'General Assumption'!$G$23,IF(OR('General Assumption'!$G$26=0,E3<='General Assumption'!$G$26),0,IF(OR(MOD(E3,'General Assumption'!$G$26)<>0,'General Assumption'!$G$23='General Assumption'!$G$26),IF(FLOOR('General Assumption'!$G$16/'General Assumption'!$G$26,1)=FLOOR(E3/'General Assumption'!$G$26,1),IF(MOD('General Assumption'!$G$16,'General Assumption'!$G$26),0,'General Assumption'!$G$30/MOD('General Assumption'!$G$16,'General Assumption'!$G$26)),'General Assumption'!$G$25/'General Assumption'!$G$23))),'General Assumption'!$G$30/'General Assumption'!$G$23)+(Investment!$D$50+Investment!$D$54)/'General Assumption'!$G$16)*IF(AND(CEILING('General
其中某单元格需7层If套用如下：
ROUND(IF(E3=CEILING('General Assumption'!$G$16,1),-SUM($D53:D53,$D34:D34),IF(E3>CEILING('General Assumption'!$G$16,1),0,IF(E3>'General Assumption'!$G$23,IF(OR('General Assumption'!$G$26=0,E3<='General Assumption'!$G$26),0,IF(OR(MOD(E3,'General Assumption'!$G$26)<>0,'General Assumption'!$G$23='General Assumption'!$G$26),IF(FLOOR('General Assumption'!$G$16/'General Assumption'!$G$26,1)=FLOOR(E3/'General Assumption'!$G$26,1),IF(MOD('General Assumption'!$G$16,'General Assumption'!$G$26),0,'General Assumption'!$G$30/MOD('General Assumption'!$G$16,'General Assumption'!$G$26)),'General Assumption'!$G$25/'General Assumption'!$G$23))),'General Assumption'!$G$30/'General Assumption'!$G$23)+(Investment!$D$50+Investment!$D$54)/'General Assumption'!$G$16)*IF(AND(CEILING('General
excel不支持这么长的公式,可以考虑把一部分,如MOD放在前一列中计算,再在后一列中引用前一列的值,这样公式会短一点,容易控制,不易出错
公式太长了不好，你看现在不好维护了吧，你的公式大概很少有人看得懂，即使看得懂估计也没那个耐心看
你可以借助其他单元格或其他工作表来转换数据
这样自己容易看懂，有问题了也好维护嘛
我个人认为公式并不是越长越好，也并不表示公式编得越长函数应用水平就越高
我建议你应用另一个工作表来转换数据
excel有自动定位的,大概在第6层的mod吧