Excel实用技巧之怎么能够在一组数字中找到这个值是哪几个数的合计

这是一个老问题了。基本上每年都会有人问起。

这个思路可以用在很多实际问题中。

看题目可能有点绕，但是问题其实很简单：

如果我们给定一个结果，比如152，我们想知道这个结果是由左边这一列数字中的哪几个数合计而成的。

其实就是需要要这么一个结果：

下面介绍一下实现方法。

01

规划求解

这个工作用Excel的规划求解功能很容易完成。（具体规划求解的作用和原理我们在以后为大家介绍，今天只介绍如何使用规划求解完成这个需求）

规划求解是Excel的加载项。缺省情况下，该加载项没有激活。

点击文件，选项：

在对话框中，左侧选择加载项，右边做下面选择Excel加载项：

点击“转到”按钮：

勾选“规划求解加载项”，然后点击确定。

在数据选项卡下，即出现加载项按钮：

02

实现方法

规划求解处理的问题基本上都是一个实际问题的抽象，所以每个问题的解决都是在建立一个模型。

首先，我们在C列（给定的一组数据的右侧列）添加一列，数值为0：

然后找一个空白单元格，例如H2，输入公式：

=SUMPRODUCT(B4:B27,C4:C27)-E2

然后，点击“数据”选项卡下的规划求解，出现对话框：

在顶端设置目标处，将目标单元格设为$H$2，选中目标值，并设为0：

然后设置可变单元格为$C$4:$C$27

然后添加约束。点击中间右侧最上面的按钮“添加”：

按照下图设置：

点击添加，该约束就被添加成功了。继续按照下图设置：

点击添加，又添加成功第二个约束。继续按照下图设置：

点击确定，添加成功最后一个约束：

这里的勾选是缺省的，不用管它

求解方法保持缺省即可：

点击求解按钮。Excel就会开始计算。经过一段时间的计算（对于一般的问题，时间都很快，对于复杂的问题，有可能需要较长时间），弹出对话框：

表示已经找到答案。点击确定，返回Excel：

我们添加的辅助列（可变单元格区域）中有一些已经变成了1，表示对应的数值求和就可以得到我们要的结果。

接下来，我们通过条件格式将给定的数据列表与这个辅助列建立关联。

选定给定的数据列表区域$B$4:$B$27，设置条件格式：

点击新建规则，在对话框中选择“使用公式确定要设置格式的单元格”，并将公式按照下图所示输入，点击格式按钮，设置满足条件的单元格格式：

点击确定。模型创建成功。

使用这个模型时，每次只要修改结果值：

然后在数据选项卡中点击规划求解，然后点击求解（不用重新设置），即可得到结果。

Excel的使用其实非常简单，但是很多人却总是不得其法。不停的搜索各种技巧，看了很多课程，工作起来却没有办法灵活运用。这是因为学习的方法不对，学习的内容不系统，学习的实战性也不强。

其实，每个人的工作场景不同，需要学习的重点也不同。不可能一个技巧就可以应用在所有的场景中。加入E学会，这里有19门课程可以满足不同应用场景的学习需求。

怎么才能快速掌握好用的Excel技巧！

怎么才能做出高大上的专业图表！

怎么才能不用天天加班做数据！

怎么才能成为Excel高手！

如果你正在为这些问题而苦恼，可以了解一下“E学会”Excel学习俱乐部，免费学习19门售价超过3000元的Excel系统学习课程，免费获得商务图表设计手册，并且有专家一对一免费帮你诊断和设计工作表格模板。

目前，双12限时优惠正在进行，在原价999元的基础上，立减600元。只要399元，就可以成为Excel高手。