Question

EXCEL 已知最大值最小值,比率,总数,求分配数的公式

求大神回复~~~~~~EXCEL 已知最大值最小值,比率,总数,求分配数
有一个表, 最大值为B1=1050 最小值为C1=730 总值D1 =10310

如何按E例的比例中把10310平均分配到F例中,要求区间在1050-730之前~

E例
133.75

133.75

133.75

127

118.755

114.3829

94.2803

90.17386

70

70

70

70
133.75 对应的是1050 70 对应的是730
主要是想把10310 分配到12个数~保持最高值为1050 最低值730 对应最高最低值可能会存在多个数~

Answer 1

F1

=IF(E1=MAX(E:E),B$1,IF(E1=MIN(E:E),C$1,E1/(SUM(E:E)-MAX(E:E)*COUNTIF(E:E,MAX(E:E))-MIN(E:E)*COUNTIF(E:E,MIN(E:E)))*(D$1-B$1*COUNTIF(E:E,MAX(E:E))-C$1*COUNTIF(E:E,MIN(E:E)))))  下拉填充

追问

可以搞到是730递增上去1050的吗?90.17对应的数,应该比730大..

追答

公式改为：

=IF(E1=MAX(E:E),B$1,IF(E1=MIN(E:E),C$1,(E1-70)/(SUM(E:E)-MAX(E:E)*COUNTIF(E:E,MAX(E:E))-70*(COUNTA(E:E)-COUNTIF(E:E,MAX(E:E))))*(D$1-B$1*COUNTIF(E:E,MAX(E:E))-C$1*(COUNTA(E:E)-COUNTIF(E:E,MAX(E:E))))+730))

Answer 2

按你的要求，无法实现，举个极端的例子，如图，F1:F12和为12280，大于10310，所以这种方法行不通，需要寻找新的分配方法。

Answer 3

F1：
=IF(E1=MAX($E$1:$E$12),$B$1,IF(E1=MIN($E$1:$E$12),$C$1,ROUND(($D$1-COUNTIF($E$1:$E$12,MAX($E$1:$E$12))*$B$1-COUNTIF($E$1:$E$12,MIN($E$1:$E$12))*$C$1)*E1/(SUM($E$1:$E$12)-COUNTIF($E$1:$E$12,MAX($E$1:$E$12))*MAX($E$1:$E$12)-COUNTIF($E$1:$E$12,MIN($E$1:$E$12))*MIN($E$1:$E$12)),0)))
由于是保留到整数位，分配出的总和可能会与D1值相差1~2左右。需要精确的话，另作调整。

Answer 4

你这是平均分配？根本是无规律好不好

追问

主要是想把10310 分配到12个数~保持最高值为1050 最低值730 对应最高最低值可能会存在多个数~

追答

=IF(ROW()<9,710+ROW()*20,450+ROW()*50)