如何用EXCEL解决计算机背包问题
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
七步成诗：如何用 EXCEL 解决“背包问题”
了解计算机的人都知道，背包问题是计算机较难解决的数学问题之一。 所谓背包问题，是个形象的比喻：某人有个背包和已知数量的物品，背包只 能装下限定的重量，每个物品重量不同。如何组合物品才能刚好装到背包指定的 重量。（另外背包问题讨论的价值最大化，在这里暂不讨论。） 该问题也可以是：财务人员要在一堆发票中，寻找到若干张发票加起来的值 为一指定值。如果能快速解决此问题，将使工作效率大大提高。 有些人说不就是随意拿几张发票，把金额加起来，多试几次不就行了？（专 业术语回溯法） 有些人说把所有发票金额可能性组合起来，每种组合先求解，选取正确的结 果也是可以的吧？（专业术语递归法） 此问题看似简单，其实不然。 下面来举个小例子来分析其数学原理及难点： A 公司向客户 B 公司开了九张发票并向申请收款，这九张发票金额分别为 1521 元、1314 元、559 元、2120 元、5602 元、498 元、1120 元、379 元。客户 由于资金问题只打来了 6075 元，由于客户相关人员出差联系上不，因此 A 公司 财务只能靠自己来算：这 6075 元是哪几张发票之和？ 其数学原理很简单：把所有的发票进行排列组合，算出所有组合的结果。也 就是求出一个数组的所有子集。 此案例中有九张发票，每张发票在组合过程中存在两种状态：不将其组合和 将其组合。这两种状态我们用 0 和 1 来表示。也就是说我们可以用二进制来表示， 那么 9 张发票组合状态便是 000000000-111111111 的范围中。 二进制的 111111111 等于十进制的 511。也就是说财务人员可能最多要试算 511 次后才能得出结果。 如果是十张发票呢？答案是 1023 次。 如果是十一张发票呢？答案是 2047 次。 如果是十六张发票呢？答案是 65535 次。这绝对是你想不到的工作量！ 网上有很多人在 EXECL 求助上提问此问题，但我没有发现正确答案。 很多网友说可以用规划求解来解决这个问题。实践之后发现这个答案完全是 在误人子弟。（规划求解是在特定的几个数字之间，而背包问题是不定数字） 因此，我用了两天时间才思考出最单简的解决方法：（之前甚至想到用 C 语言或 VBS 编程来求解，但是由于感觉还是太麻烦就果断放弃了） 下面我来说下如何用 EXCEL 来解决此问题。 由于篇幅限制，我以四张发票为例。发票金额分别为 13206、6542、7862、 6321，另指定一个求合值 27389 作为我们的目标。
13206
9
1001
1 0 0 1 13206 0
0 6321
19527
10
1010
1 0 1 0 13206 0 7862 0
21068
11
1011
1 0 1 1 13206 0 7862 6321
27389
12
1 1 0 0 13206 6542 0 0
19748
13
1101
1 1 0 1 13206 6542 0 6321
在对应的格子里。这也就
因此快速填充 15 改为 9 位）用到 =MID($B4,C$2,1) 是所有金额的可能性组合
行数。每一行代表 的公式：
一种组合。 DEC2BIN(A4,4)
1
0001
0 0 0 1 0 0 0 6321
6321
2
0010
0 0 1 0 0 0 7862 0
7862
3
0011
001 1 0
1111（通过二制转 制位数限定在 用到的公式为=MID(数 第五步、将上面发票金额 第七步、就可以
十进制公式 四位，（若是九 值，起始位,位数);
和对应的
找出我们要的
BIN2DEC，算出为 张发票，也要将 例如：C4 单元格中的 二进制数值栅格相乘。填 特定值了。
十进制 15）， 二进制的位数
公式。
具体算法如下表所示：
第三步、将 4 位二进制
按位数 第一步、列出所有 第二步、转换成 填进对应的单元格中， 第四步、填写发票金额
的组合数量
二进制
我称之为栅格化
1 2 3 4 13206 6542 7862 6321
第六步、求出 每种组合的金
四张发票排列的 由于是四张发
额之和.
上限是二进制 票，所以将二进
0 7862 6321
14183
4
0100
0 1 0 0 0 6542 0 0
6542
5
0101
0 1 0 1 0 6542 0 6321
12863
6
0110
0 1 1 0 0 6542 7862 0
14404
7
0111
0 1 1 1 0 6542 7862 6321
20725
8
1000
1 0 0 0 13206 0 0 0
2015 年 10 月 03 日于上海浦东 陈东昇 QQ406828204
26069
14
1110
1 1 1 0 13206 6542 7862 0
27610
15
1111
1 1 1 1 13206 6542 7862 6321
33931
分析并结论： 一、以上方法算出结果仅需要两分钟，比编程还要快； 二、EXCEL 中，二进制最多只有 9 位，所以当超过 9 张发票时，进制转换的公式要改多只有 65536 行。因此一次性最多只能操作 16 位数字的组合。不过这 很容易解决：如 1..同一表格里做多列操作；2.分批次操作；
了解计算机的人都知道，背包问题是计算机较难解决的数学问题之一。 所谓背包问题，是个形象的比喻：某人有个背包和已知数量的物品，背包只 能装下限定的重量，每个物品重量不同。如何组合物品才能刚好装到背包指定的 重量。（另外背包问题讨论的价值最大化，在这里暂不讨论。） 该问题也可以是：财务人员要在一堆发票中，寻找到若干张发票加起来的值 为一指定值。如果能快速解决此问题，将使工作效率大大提高。 有些人说不就是随意拿几张发票，把金额加起来，多试几次不就行了？（专 业术语回溯法） 有些人说把所有发票金额可能性组合起来，每种组合先求解，选取正确的结 果也是可以的吧？（专业术语递归法） 此问题看似简单，其实不然。 下面来举个小例子来分析其数学原理及难点： A 公司向客户 B 公司开了九张发票并向申请收款，这九张发票金额分别为 1521 元、1314 元、559 元、2120 元、5602 元、498 元、1120 元、379 元。客户 由于资金问题只打来了 6075 元，由于客户相关人员出差联系上不，因此 A 公司 财务只能靠自己来算：这 6075 元是哪几张发票之和？ 其数学原理很简单：把所有的发票进行排列组合，算出所有组合的结果。也 就是求出一个数组的所有子集。 此案例中有九张发票，每张发票在组合过程中存在两种状态：不将其组合和 将其组合。这两种状态我们用 0 和 1 来表示。也就是说我们可以用二进制来表示， 那么 9 张发票组合状态便是 000000000-111111111 的范围中。 二进制的 111111111 等于十进制的 511。也就是说财务人员可能最多要试算 511 次后才能得出结果。 如果是十张发票呢？答案是 1023 次。 如果是十一张发票呢？答案是 2047 次。 如果是十六张发票呢？答案是 65535 次。这绝对是你想不到的工作量！ 网上有很多人在 EXECL 求助上提问此问题，但我没有发现正确答案。 很多网友说可以用规划求解来解决这个问题。实践之后发现这个答案完全是 在误人子弟。（规划求解是在特定的几个数字之间，而背包问题是不定数字） 因此，我用了两天时间才思考出最单简的解决方法：（之前甚至想到用 C 语言或 VBS 编程来求解，但是由于感觉还是太麻烦就果断放弃了） 下面我来说下如何用 EXCEL 来解决此问题。 由于篇幅限制，我以四张发票为例。发票金额分别为 13206、6542、7862、 6321，另指定一个求合值 27389 作为我们的目标。
13206
9
1001
1 0 0 1 13206 0
0 6321
19527
10
1010
1 0 1 0 13206 0 7862 0
21068
11
1011
1 0 1 1 13206 0 7862 6321
27389
12
1 1 0 0 13206 6542 0 0
19748
13
1101
1 1 0 1 13206 6542 0 6321
在对应的格子里。这也就
因此快速填充 15 改为 9 位）用到 =MID($B4,C$2,1) 是所有金额的可能性组合
行数。每一行代表 的公式：
一种组合。 DEC2BIN(A4,4)
1
0001
0 0 0 1 0 0 0 6321
6321
2
0010
0 0 1 0 0 0 7862 0
7862
3
0011
001 1 0
1111（通过二制转 制位数限定在 用到的公式为=MID(数 第五步、将上面发票金额 第七步、就可以
十进制公式 四位，（若是九 值，起始位,位数);
和对应的
找出我们要的
BIN2DEC，算出为 张发票，也要将 例如：C4 单元格中的 二进制数值栅格相乘。填 特定值了。
十进制 15）， 二进制的位数
公式。
具体算法如下表所示：
第三步、将 4 位二进制
按位数 第一步、列出所有 第二步、转换成 填进对应的单元格中， 第四步、填写发票金额
的组合数量
二进制
我称之为栅格化
1 2 3 4 13206 6542 7862 6321
第六步、求出 每种组合的金
四张发票排列的 由于是四张发
额之和.
上限是二进制 票，所以将二进
0 7862 6321
14183
4
0100
0 1 0 0 0 6542 0 0
6542
5
0101
0 1 0 1 0 6542 0 6321
12863
6
0110
0 1 1 0 0 6542 7862 0
14404
7
0111
0 1 1 1 0 6542 7862 6321
20725
8
1000
1 0 0 0 13206 0 0 0
2015 年 10 月 03 日于上海浦东 陈东昇 QQ406828204
26069
14
1110
1 1 1 0 13206 6542 7862 0
27610
15
1111
1 1 1 1 13206 6542 7862 6321
33931
分析并结论： 一、以上方法算出结果仅需要两分钟，比编程还要快； 二、EXCEL 中，二进制最多只有 9 位，所以当超过 9 张发票时，进制转换的公式要改多只有 65536 行。因此一次性最多只能操作 16 位数字的组合。不过这 很容易解决：如 1..同一表格里做多列操作；2.分批次操作；