编写 Excel 电子表格公式

编写 Excel 电子表格公式

我需要一个 Excel 公式,能够根据单个单元格的值找到最有效的产品组合。

例如,我有如下产品清单:400/220/150/110/72/60。我需要一个公式,根据另一个“单元格”的值应用列表中的项目。

从列表中获取的组合项目需要使用列表中最少的项目数,将其加起来等于最接近但大于“单元格”值的数字。

因此,如果“单元格”的值为 660,公式需要知道正确的答案不是 6 x 110,而是 1 x 400 (+) 1 x 220 (+) 1 x 60 = 680

我该如何写公式?

答案1

我认为如果你能用公式来限定你的要求会有所帮助,例如

假设你已经对项目 a,b,c,d,e,f 进行了排序,其中 a > b > c > d > e > f

然后使用目标单元格 x,您需要找到 m、n、o、p、q、r(所有正整数),以便

一个 + n硼 + 氧碳+磷d + qe + rf >= x

y = m+n+o+p+q+r, where y < y(mi,ni,oi,pi,qi,ri)  for all combinations of mi,ni,oi,pi,qi,ri

或许还有其他标准

一个 + n硼 + 氧碳+磷d + qe + rf <= 1.1 * x

在某些方面,这与我在这里回答的路线优化问题类似(Excel 公式用于优化路线,其中每一行代表一个可以做不同事情的不同“站点”),但增加了复杂性,因为您可以多次访问一条路线,这意味着位数学技巧不再适用。要强制执行此操作,您需要为所有可能的组合生成答案,其中任何单个 m、n... 或 r 的最大值是 QUOTIENT(x、m 或 n 或...r)+1。对于您的示例,解决方案空间将跨越 m、n、o、p、q、r = {0..2、0..4、0..5、0..7、0..10、0..12}。您可以使用 MOD(QUOTIENT(ROW()-2,(n+1) 的变体在 excel 中生成这样的表格(○+1)...(r+1)),m+1)。然后计算每行/组合的总数并将其与目标值进行比较。在相邻列中计算项目数 y,并先按总数进行多重排序,然后按项目数进行多重排序。

相关内容