答案1
我承认我还没明白你是如何得到 0 的。但是如果你想要你所描述的方法,你可以使用以下公式。它使用你示例中的单元格(当人们给出良好的示例设置时,这真是太好了!):
=1/ MAX( IF( CRange>$L$2,($B$2*$D$2*$F$2/CRange+$J$2<=$L$4), 0) * ( ($B$2*$D$2*$F$2/CRange+$J$2) - $J$2) / ($B$2*$D$2*$F$2) )
首先,IF()
上面看到的测试会找到任何不满足大于或等于 L2 条件的值,如果不满足,则用零替换它们,如果它们 >=L2,则用第二个条件 (<=0.75) 的结果替换它们。您的条件得到满足或不得到满足,将创建一个数组0,0,FALSE,FALSE,FALSE,TRUE,TRUE...
供 Excel 使用(不要担心,因为 Excel 会将这些零视为 ,FALSE
反之亦然(在大多数情况下,包括这个,它自己都会这样做,当您要强制它这样做时,它总是会这样做)。
此时将其乘以范围 ( CRange
),您将始终获得范围内的最大值,而不是最符合您条件的值(因此是 777,700,而不是 212,600)。这是因为乘法(此处)或IF()
公式中的结果将直接来自范围的值,而 777,700 是它们的最大值。
但是,如果您将(此处)与 0.75 (L4) 进行测试的值数组相乘,则数组相乘的结果将返回一个由零和测试成功的值组成的数组。然后,您可以从成功的值开始进行数学反推。
谁会在乎那些成功但不是值MAX()
的,对吧?不是你!首先,我选择通过减去 0.5(J2)来“恢复”值,只是为了让余数在将来更容易阅读。你可以在乘以数组之后这样做,但它会增加括号级别,如果立即完成,似乎更容易理解。你决定,但这是我的建议。此时,所有非 0 值仍然处于相同的关系中,因此MAX()
仍然会选择正确的值。
最后,对于要选择的功能材料MAX()
,我除以 B2、D2 和 F2 中的三个因子。仍然是从最高到最低的关系,所以MAX()
仍然选择正确的一个。
一旦它选择了它,还有一件事要做。“恢复”并不完全,因为选择的值实际上是倒数(“一除以你真正想要的值”)。所以最后一步是=1/
最开始的最外层位。这将返回列表中的值。
老实说,它不太可能是列表中的精确值,因为经过数学运算后,它可能为 211,600.000000002,而对于很多事情来说,这可能很重要。在这种情况下,您绝对可以简单地将其四舍五入。更确切地说,对于这种需要,您可能希望使用,TRUNC()
但它不会询问保留多少位小数(此处为 0),而是保留多少位数字,而您并不总是真正知道。INT()
当任何结果都可能为负数时不应使用,但如果它永远不会给出负值结果,那就没问题了。否则,MROUND()
是您最好的舍入函数,而不是三个ROUNDxxxx()
函数。老实说,考虑到误差有多大,您在选择时实际上只需要担心可能的负值结果。一旦完成,您就大功告成了。
(通常,这样的事情来自其他方式,其中某种历史记录导致不变但仍在使用的值,例如 B2、D2、F2、J2 和 L4 中的值。 (L2 看起来好像总是可以改变的。)如果这是真的,并且这些值不会改变,如果只是将过去在纸上完成的内容输入 Excel,那么您的整个操作可以在数学上真正变得轻松和严格,为您提供一个多年来更容易编写和维护的公式。 当你升职时交给别人。 或者如果其中只有一两个发生变化。 如果它们都可能发生变化,那么当然,这不适用!)