excel问题求教:每列各取1个数相加为特定值,求各种情况列举
2017-12-05 · 知道合伙人软件行家
在Excel中,这类问题需要用规划求解才有可能得到解。Excel的规划求解在早期版本中,并不是默认安装的,需要在安装选择或安装Excel的全部功能才会安装,且在安装后还需要在选项的加载项选择加载后才能使用。
以本问题为例,可按如下方式(并非唯一方式)来进行规划求解:
在A9:I9中随便输入数字1~8(也可为空,最后是多少由规划求解来得到,并在J9中输入60000(希望得到的结果);
在A10中输入公式:
=INDEX(A1:A8,A9)
即把A1:A8中行号=A9的数字引用到A10,右拉到I10,就在A1:I10中系列选择了一个数据到第10行:A1:I10;
3. 在J10中输入公式:
=SUM(A10:J10)
在J11中输入公式:
=ABS(J9-J10)
4. 数据选项卡中选择“规划求解”
5. 按如下图设置规划求解参数
说明:
目标单元格:J11,即每列一个数与要求的数60000之差的绝对值,目标:最小
可变单元格:A9:I9,即求解要到每列取第几个数(1~8中)
再添加约束条件:
A8:I8——小于或等于8、和A8:I8——大于或等于1,两个约束满足就是保证在第1行与第8行之间取数;
A8:I8——整数——行号只能是整数;
J10<=J9——本约束是否需要是按题主举例所说的大于59000,小于60000而设置的,如果和的结果可以大于60000,则无需此约束;
选择求解方法:Excel规划求解提供了3种方法:非线性、单纯线性、演化。具体选择哪种方法,貌似没有必须的规定。一般线性求解最快,演化最慢,线性能求解所Excel中的公式是线性关系的。其他则要用非线性或演化。求解时不能确定就分别用3种方法求解,能得到解的方法就行。本问题中需要用“演化”才能求解。
还可在“选项”中设置求解最大求解迭代次数或(和)最大求解时间,防止求解太长时间无结果造成死循环。
6. 按“求解”,Excel会花一段时间进行求解,当找到一个比较理想的解后,停止求解。这时可根据求解结果确定是否接受。求解过程截图如下,在下面的状态栏中有循环次数和中间解
7. Excel得到的求解结果: