我一直在处理一个 Excel 工作表，但想不出我需要的最后一个函数。我有宽度 x 长度的尺寸，它们被输入到 B 列和 D 列中，同时还有 A 列中的数量。
数据转换方法是将宽度除以数量，再将长度除以数量，然后将结果放入 E 列和 F 列。
然后在 H 列和 I 列中再次转换这些集合，因此较小的尺寸始终列在第一位，而较长的尺寸排在第二位。
接下来，我通过将结果乘以数量来计算 K 列和 L 列的新尺寸（这可能是第一步，但这些值对于其他功能是必要的，因此这是多余的）。
在 N3 中我有数量的总和，其中宽度和长度的总和在 P3 和 Q3 中。
P4 和 Q4 是平均值，分别用 P3 中的总宽度除以 N3 中的总数量，用 Q3 中的总长度除以 N3 中的总数量。
我正在尝试解决的函数是如何使 P4 中的平均宽度尽可能接近数字 60 而不超出，交换每行的宽度/长度值，直到达到最接近的平均值 60。我尝试过 MAX 与 IF 条件的组合，但没有成功。
请帮忙出出主意！
有问题的表格
A:I 列的公式
J:Q 列的公式
答案1
如果您有新版本的 Excel（365、2021），则可以使用公式列出所有可能的变体并选择最佳变体。
最佳值：
=LET(rno,ROWS($K$3:$K$7),
test,BYCOL(MOD(TRUNC(SEQUENCE(1,2^rno,0)/2^SEQUENCE(rno,,0)),2),
LAMBDA(col,SUM(IF(col;$L$3:$L$7;$K$3:$K$7)/$N$3)),
MAX(IF(test<=60,test)))
最佳变体（0-不交换，1-交换）：
=LET(rno,ROWS($K$3:$K$7),
tab,MOD(TRUNC(SEQUENCE(1,2^rno,0)/2^SEQUENCE(rno,,0)),2),
test,BYCOL(tab,LAMBDA(col,SUM(IF(col,$L$3:$L$7,$K$3:$K$7))/$N$3)),
tmax,MAX(IF(test<=60,test)), XLOOKUP(tmax,test,tab))
您必须记住，在数据中添加每行都会使计算次数增加一倍，因此行数是有限的。
如果您有旧版本的 Excel，则可以使用 Solver 插件。
公式：
T3 : =IF(S3=0, K3, L3)
R8 : =SUM(T3:T7)/N3
求解器参数（波兰语，抱歉，我没有英文版本）：
结果截图：
