我需要根据其他单元格中的计算得出单元格范围内的最大值

我需要根据其他单元格中的计算得出单元格范围内的最大值

我在 Excel 中有一个单元格区域,我正在使用一个计算来尝试确定该区域中的最大值,但我不确定如何做到这一点。

  1. 我需要首先查看范围内的每个值是否大于 x。

  2. 如果一个值大于X,那么我需要根据该值进行计算,看看它是否接近N。

  3. 从结果中选择我的范围内最接近 N 但不超过 N 的最大值。

我试过了: =MAX(IF(B2D2F2/CRange+J2<=0.75,CRange))

但得到 0。我有截图

在此处输入图片描述

答案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,那么您的整个操作可以在数学上真正变得轻松和严格,为您提供一个多年来更容易编写和维护的公式。 当你升职时交给别人。 或者如果其中只有一两个发生变化。 如果它们都可能发生变化,那么当然,这不适用!)

相关内容