如何找出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列所有值的乘积，求解方法上需要选择演化。