来来来，我们一起学习规划求解

娘说，她要凑数，她让我别问她为什么，原因她不方便告诉我，就像那首歌唱的那样，别问我是谁，请给我答案。

我问她要怎么个凑法，她说她有一组备选的数字，要从备选的数字中凑最接近目标值的数，且该结果不能超过目标值，也就是小于等于目标值的关系，此外，她还要求，备选数字只能用一次，不能多次使用。

另外，目标值不固定，方案要支持目标值变化后也能按相同方法去操作。

我们来看下她的备选数字以及目标值：

【数据源】如下↓

 

问题剖析

首先，这是个最优化组合的问题，可以利用规划求解进行解决。

规划求解是一个加载项，激活规划求解需要执行以下步骤：

 对于EXCEL2010或EXCEL2013：

选择【文件】选项卡，然后选择【选项】

选择【加载项】，单击【转到】按钮，选中【规划求解加载项】前面打勾，然后确定即可

现在单击【数据】选项卡，你会在功能区的右侧看到【规划求解】

 

解决方法

 制作详细步骤如下：

1.  在D3单光格输入求和公式=SUMPRODUCT(A2:A6*B2:B6)，意思是将两个数组的所有元素对应相乘，然后把乘积相加，即A2*B2+A3*B3+A4*B4+A5*B5+A6*B6,如下：（这步的目的是将【备选数字】与【是否选用】关联起来看组合后的求和结果）

2.   在D4单光格输入求差公式=ABS(D3-D1)，既然是最接近的值，那么该值与目标值的差值的绝对值必然是最小的，EXCEL中求绝对值的函数为ABS,如下： （这步的目的是将求和结果与目标值进行比较，求出差值的绝对值）

调出【规划求解】对话框，点击【数据】选项卡下-【规划求解】弹出规划求解对话框， 设置目标选择$D$4单元格，点选最小值，通过更改可变单元格选择$B$2:$B$6单元格，如下图：

【添加】约束条件， $B$2:$B$6选用二进制，二进制的概念就是非0即1，也就是说它只用二个数，要么0，要么1。

这意味着对应的备选数字要么选用，要么不选用，选用也顶多出现1次。 因为任何数乘以0都为0，就相当于不选用。

继续添加约束条件，$D$3单元格必须小于等于$D$1单元格，目的是组合后的数字总和不能超过目标值。如下图所示：

添加完这些约束条件后，我们就可能求解啦，步骤如下图所示：

 

点击【确定】后规划求解就帮我们快速的找到了组合方案，效果如下：

可能有小伙伴们会问，规划求解都有哪些应用场景，其实规则求解有很多用处，比方说，我们可以利用EXCEL规划求解来最优化定价，最大化利润值，最大化产能等等方面。

因为好东西人人都想多多益善，那么规划求解作为数据分析工具的一种，它可以帮助我们调整决策变量，找出解决方案和优化点，使我们最大限度的达到目标。

好了，今天的分享就到这，祝大家天天好心情。

作者：Crystal