如何找出excel中和最接近某个值的单元格-规划求解
文章目录
1 实现目标
给定一组值和一个求解目标值,找出这组值中哪几个的和最接近目标值,这里的最接近可以根据需要设置成大于等于或小于等于。同样,方法也可以扩展成哪几个值的乘积最接近目标值等。
2 准备工作
首先,打开excel中的规划求解功能,我这里用的是office 365订阅,应该是当前的最新版本,其它版本的excel或者wps可以自行搜索如何打开这个功能。
打开excel,文件->选项->加载项,在右边的页面中选excel加载项,点击转到
,在弹出的窗口中选择"规划求解加载项",确定。
此时在excel 上方的选项卡中选择数据,然后在右边就能看到规划求解功能了。
3 解决问题
基本思路是:我们给每一个值匹配一个二进制数,当选用这个值时,相应的二进制值设为1,当不选用这个值时,相应的二进制值设为0,然后利用sumproduct
函数对这些"值和二进制的乘积"求和,使这个和最接近我们的目标值即可。
3.1 输入原始数据
在A列输入所有的原始数据,例子中取了20个1-100之间的随机数。
3.2 在目标单元格处用sumproduct求和
这里假设B列设置相应二进制值(后续步骤进行设置),那么在任意位置进行sumproduct
求和,例子中就近选取B21单元格,我们的目标就是让B21的值最接近目标值。
3.3 规划求解设置
打开规划求解功能,按下面的步聚设置参数。
参数设置
设置目标选取B21单元格,可变单元格选取B1:B20,通过改变这里的0,1值来确定是否选用A列中对应的值。最大值最小值这里根据需求选择,当你需要选择和最接近且不大于目标值
的单元格,那就选最大值,反之亦然。
添加约束
这里要添加的约束有2个,一个是让B列只生成0,1两个二进制数,另一个就是让B21不大于或不小于目标值(根据需要),假设我们是要找出和不大于且最接近666的单元格,添加2个约束:
选择求解方法
一般选单纯线性规划
就行了,速度快,另外2个根据需要选择。
到些就设置完成了,点击求解即可生成结果,结果中B列的值如果是1,说明选用A列相应的值,例子中最终最接近666的和是660
4 其它
本文中的方法也可以扩展到其它类似的问题,比如找到乘积最接近目标值的单元格,只是实现上需要做点改动,可以添加1个C列,当B列的值是0是,C列取1(不影响乘积),当B列的值是1时,C列的值取A列的值,然后B21的值就等于C列所有值的乘积,求解方法上需要选择演化。
版权声明 本博客使用CC BY-NC-SA 4.0许可协议(创意共享4.0:保留署名-非商业性使用-相同方式共享)。