|
VBA秒解 | 月底报销要凑1000块发票，要求加起来刚好1000块，我太难了！徐军泰 Excel表格学院 你有没有遇到过这样的场景？ 月末要报销发票，你到处收集了一堆金额各异的的士票、加油票，最后要凑个整数（比如要报销1000块）才能报销。 这不很让人抓狂吗？几十张发票，我怎么知道哪些加起来刚好等于1000！于是，你一张张把金额记在Excel里，然后…… 然后，就没有然后了。 正好，今天我们30天实战营学员也问到一个类似的问题：从一列数据中找出几个数，他们的和正好等于3206（前提是确实存在解）。 以此为例，我们来看如何解决这类问题，我们权且称这种问题为“凑数”。 01 数据源构造与思路
我们简单构造一个数据源。如上图所示，D6:D13区域为凑数的数据源，现在要从中间取几个数，使他们的和恰好等于D4单元格（128）。 如果128是由几个数构成，几个数是确定的，比如3个数，那相对容易解决。但如果你也不知道128是由几个数组成的，甚至有多种可能，那就稍微复杂一些。 我们先来看简单一点的情况，比如我们知道128由3个值构成。这3个值有可能是D6:D13中的任何一个值。 因此，我们要想办法从数据源中随机提取3个数，如果刚好这3个值加起来就等于128，那就满足我们的条件。如果不等于128，那就继续提取数据，直到满足条件。
如何随机地把数据从数据源中提取出来，这个我们在《100节Excel实战课》第16、17节讲Excel抽奖模型的时候有讲过，具体可以学习这套课程。 我们在E列E6:E13区域用RAND函数生成一组随机数，然后在F6:F8单元格用INDEX+MATCH+LARGE函数从数据源随机提取3个值。 提取后，要进行判断了，如果不满足3个数之和等于128，就继续提取直到获得最终的结果。 02 用VBA实现自动计算 显然，这是一个计算量很大的过程，整个提取过程是随机的，你也不知道什么时候能刚好把那3个数取出来。 所以，我们这里用VBA来实现这个过程，以下是我编写的VBA代码。 我们来试试完成自动凑数，需要多少时间？ 可以看到，这次运行只花了1.9秒就找出了答案。当然每次运行找到答案的时间也是随机的，快的时候零点几秒，慢的时候3、4秒，但基本上我试了几次，平均不2秒的时间就够了。 大家可以想象一下，如果我们靠自己手动来凑数的话要花多长时间。这就是VBA的优势——让计算机帮我们自动执行。 上面我们解决了已知数据个数的情况的计算，但是假如我们不知道到底128是由几个数构成的，那又该怎么处理？ 03 问题的升级 在数据个数不确定的情况下，我们可以看数据源，理论上，极限可能就是构成128的个数最小为1个，最大为8个。当然根据数据大小观察，可能不需要8个数、最多4、5个数就够了。 因此，我们将数据源及公式稍作调整。
首先，我们这次在8个单元格中随机提取数据，但是提取的时候加入判断——如果RAND随机数大于0.5，就返回提取的数值本身，如果小于0.5就返回0。 由于RAND结果是随机的，大于和小于0.5的数量平均下来机会接近，因此大多数情况下，F列中提取的结果中，0和非0值数量相当。但是F列中能取到几个值的可能都有。 现在，对VBA代码稍作修改后，可以运行。当F列提取值的个数和数字相加结果恰好为128同时满足的时候就结束程序，得到要的结果。 这个过程的计算量相比前面就要大很多了，因此凑数的时间也一定会多出不少。 
我试着运行了两次，用时分另是2.71秒和11.44秒，还是挺快的。 总体上，大家可以对比，用计算机快速凑数，毕竟是计算机速度，与我们人脑计算，优势是非常大的。
以后，涉及到凑数的问题，要报销发票的时候，你就可以用VBA来实现了。 怎么样，今天的案例你学会了吗？希望对大家有所帮助。 有同学问我们的VBA课程和训练营什么时候上线，我计划8月份先上线VBA入门课，大概10来节的样子。然后就上线VBA实战营，通过实战案例教大家快速解决工作中的批量处理问题，如——
我会从我自己长期实践积累中精选实用VBA案例，为大家详细讲解，面向实战快速掌握VBA的应用，关键是学会用VBA解决工作是常见问题的处理。 |
|
|
