vba 是否有一个 Excel 函数来查找数字的组合

Is there an Excel Function to find a combination of numbers

提问by C-Love511

I am not familiar with all of excel's functions and data tools, though I am familiar with excel. My problem is that I have a number on a spreadsheet which is a combination of many different numbers on a list.

我对excel的所有功能和数据工具并不熟悉，但我对excel很熟悉。我的问题是我在电子表格上有一个数字，它是列表中许多不同数字的组合。

For example:

例如：

A list contains: 100, 200, 250, 500, and 1000, and the number I need to explain is: 800

The answer would be 500, 200, 100.

一个列表包含：100、200、250、500和1000，我需要解释的数字是：800

答案是 500, 200, 100。

Not hard to do in my head for a short list with simple numbers, but I'm dealing with over 1500 currency cells ($xxxx.xx) which make a total (and not all are used, so SUMis useless)--I need to understand which numbers were used to create that total (Which isn't a formula, it's a hard-coded number).

用简单的数字在我的脑海中做一个简短的列表并不难，但我正在处理超过 1500 个货币单元格 ($xxxx.xx)，它们总计（并不是所有的都被使用，所以SUM没用）——我需要了解哪些数字用于创建该总数（这不是公式，而是硬编码数字）。

THE QUESTION: Is there a function or VBA that will systematically combine numbers in a given range until it determines which numbers can be added together to make the total?

问题：是否有一个函数或 VBA 可以系统地组合给定范围内的数字，直到它确定可以将哪些数字加在一起以形成总数？

I just want to know before I dig in and start writing a brute-force algorithm myself.

我只是想在我深入研究并开始自己编写蛮力算法之前知道。

========

========

EDIT: The solution I created, attached below

编辑：我创建的解决方案，附在下面

Link to the file I created to do this: https://drive.google.com/file/d/0B8nE67gSOkewWXR2WnRuQTc2MEU/view?usp=sharing

链接到我为此创建的文件：https: //drive.google.com/file/d/0B8nE67gSOkewWXR2WnRuQTc2MEU/view?usp=sharing

Thank you to everyone who contributed!

感谢所有做出贡献的人！

采纳答案by C-Love511

Okay, figured out the solution that worked best for me:

好的，找出最适合我的解决方案：

First, I found some vba that lets you create infinite strings of binary (rather than the 9 bits Excel has built in). I then used this code to create a UDF for this purpose... Since I was working with chunks of 20-40 "bits", this was absolutely necessary.

首先，我发现了一些 vba 可以让你创建无限的二进制字符串（而不是 Excel 内置的 9 位）。然后我使用此代码为此目的创建了一个 UDF...因为我正在处理 20-40 个“位”的块，所以这是绝对必要的。

Second, I made a counter loop which increased by 1, then changed the binary string to reflect the new number. (1,10,11,100,101,110,111, etc.)

其次，我做了一个增加 1 的计数器循环，然后更改二进制字符串以反映新数字。（1、10、11、100、101、110、111等）

Third, I wrote a formula that breaks the binary string apart, and assigns each 1 or 0 to the corresponding number in the cell next to it. (Just using the LEN(),RIGHT(), and MID()functions to recognize 1s and 0s).

第三，我写了一个公式，将二进制字符串分开，并将每个 1 或 0 分配给它旁边单元格中的相应数字。（只使用LEN()，RIGHT()和MID()功能认识1和0）。

Fourth, I multiplied each value by the 1 or 0 next to it, and then compared the sum of all multiplied numbers to the target value I was looking for.

第四，我将每个值乘以它旁边的 1 或 0，然后将所有相乘数字的总和与我正在寻找的目标值进行比较。

100% of the time, if given clean data, this finds a solution, if one exists. (Mostly time is a factor though, since this is an exponential function, so the more bits you have, the longer it takes to cycle through them)

100% 的情况下，如果给定干净的数据，这会找到解决方案（如果存在）。（虽然大多数时间是一个因素，因为这是一个指数函数，所以你拥有的位越多，循环它们所需的时间就越长）

This ran at about 3 million combinations in 4 minutes, give or take depending on a few factors

这在 4 分钟内运行了大约 300 万个组合，取决于几个因素

I redid the worksheet, and doubled the speed by having 5columns, each incremented by one, and having the counter increment by 5 (rather than 1).

我重新编写了工作表，并通过有5列（每列增加 1）并使计数器增加 5（而不是 1）使速度加倍。

回答by Tetlanesh

You can use SOLVERin excel to get the result.

您可以SOLVER在excel中使用以获得结果。

You can activate it in ADD-INSand it should show up in DATAtab.

您可以在其中激活它ADD-INS，它应该显示在DATA选项卡中。

You set up your spreadsheet like this:

您可以像这样设置电子表格：

In one column You have list of numbers You want to check Next column is all zeroes (0) Third column is First*Second (for example 100 * 0) so in the beginning its zero for all rows

在一列中您有数字列表 您要检查 下一列是全零 (0) 第三列是 First*Second（例如 100 * 0），因此在开始时所有行的零

Than You add summary of third column and it should also be zero. Example how this data can look like:

比您添加第三列的摘要，它也应该为零。此数据的示例如下：

100 0   0
200 0   0
500 0   0
50  0   0
60  0   0
80  0   0
120 0   0
90  0   0
TOTAL   0

Now You run solverform datatab and You get interface that You have to feed parameters:

现在您运行solver表单data选项卡并获得您必须提供参数的界面：

Goal value is the CELL with the sum of all multiplications You are looking for exact value (type in 800)

目标值是具有所有乘法之和的 CELL 您正在寻找精确值（输入 800）

By changing cels: select range of zeroes in second column

通过更改 cels：在第二列中选择零范围

Add thre additional restriction (addbutton): range of zeroes have to be >= than 0and <= 1and intso we only have 0 and 1 as possible results (you have to reselect range every time you add another limitation)

添加THRE附加的限制（add按钮）：零的范围必须>= than 0和<= 1和int，所以我们只有0和1为可能的结果（你必须重新选择添加其他限制范围内的所有时间）

Now press solveand after some time (depending on scale of your data sets ranging from seconds to many minutes) it will change some of the zeroes to 1 indicating which numbers where used to produce Your result.

现在按下solve，一段时间后（取决于您的数据集的规模，从几秒钟到几分钟不等）它会将一些零更改为 1，指示哪些数字用于产生您的结果。

If there are many possible outcomes it will choose one that he found without indicating that there are more, but running it again may produce different result.

如果有多种可能的结果，它会选择他找到的一个，但不会表明还有更多，但再次运行可能会产生不同的结果。

Here is the result I got:

这是我得到的结果：

100 1   100
200 0   0
500 1   500
50  0   0
60  0   0
80  1   80
120 1   120
90  0   0
TOTAL   800

回答by Amen Jlili

Help is on the way.

帮助正在路上。

You can paste this function into a module and adjust it to your needs.

您可以将此功能粘贴到模块中并根据需要进行调整。

Function GetCombination(CoinsRange As Range, SumCellId As Range) As String
Dim Nb As Integer
Dim Com As String
Dim Sum As Double
Dim r As Range
Set r = CoinsRange
Sum = SumCellId.Value
For Each cell In r.Cells
If Sum / cell.Value >= 1 Then
Com = Com & Int(Sum / cell.Value) & " of " & cell.Value & "  "
Sum = Sum - (Int(Sum / cell.Value)) * cell.Value
End If
Next
GetCombination = Com
End Function

Preconditions:

先决条件：

1. Coins or bills must be in descending order

1. 硬币或纸币必须按降序排列

My End result:

我的最终结果：

回答by Patrick

I've been working on a windows application to do this. I'm close to a solution that I think will satisfy most people's needs.

我一直在开发一个 Windows 应用程序来做到这一点。我接近一个我认为可以满足大多数人需求的解决方案。

The number of combinations is the issue for most algorithms, so the key is to ignore as many non-viable combinations as possible.

组合的数量是大多数算法的问题，所以关键是尽可能多地忽略不可行的组合。

25 numbers in a list is approx 33 million combinations. 50 numbers in a list is a million millions of combinations. So, doing this in vba is probably not a viable option for most people, and solver won't deal with this very well either.

列表中的 25 个数字大约是 3300 万个组合。列表中的 50 个数字是一百万个组合。因此，对于大多数人来说，在 vba 中执行此操作可能不是一个可行的选择，并且求解器也无法很好地处理此问题。

Brute force doesn't work because there are too many combinations if you're doing a list of more than 2 to 3 dozen numbers.

蛮力是行不通的，因为如果您要列出超过 2 到 3 打数字的列表，则组合太多。