点击上方蓝色文字关注我们吧
大家好!更新完《Python实战篇 数据分析》,杨老师总算每天能够有心情到Q群里多看几眼。于是今天一早就看到了VBA群中的这个问题:
随机匹配两组名单,俗称“乱点鸳鸯谱”,确实是实际工作中经常遇到的问题:
其实即使不用VBA,这个任务做起来也一点不难,正如上面“西木#”同学说的,添加一列随机数,然后按随机数重排序就可以:
(上图第4步特别说明:因为排序之后,Excel会重算 Rand 公式数值,所以排序后C列的随机数值也发生了变化,但并不影响排序效果)
不过对于学习VBA的同学来说,把Office中的所有手工操作都自动化,是一件天经地义的事情。毕竟保不齐哪一天我们需要批量乱点一百张鸳鸯谱,或者需要把四列、五列数据乱序匹配。这种情况下如果还是使用手工操作,我们就要在每一列后面手工插入随机数,麻烦程度可想而知了 :
更何况,作为“乱序配对”的主持者,说不定我们还留有一点私心呢:
考虑到这些需求,用VBA实现乱序重排和匹配就非常有价值了。
VBA怎样随机重排呢?当然是使用《全民一起VBA 提高篇》第20回介绍的“随机数”功能。如果再配合第16回里介绍的“使用Range.Cells遍历Range中全部单元格”,那么只需10行代码就能完成对一列单元格(比中本例B列“鸳组”)的乱序重排:
这段代码的含义并不复杂,这里简单的介绍一下:
首先,随机重排的一个常用思路,就是在一列数据中随机选取两个单元格,然后把它们相互交换位置。
不过一次交换只能随机改变2个单元格的内容,所以我们需要把这个过程反复执行多次,让每个单元格都有很大的机会被交换一次,就能实现非常好的“洗牌”效果。
总结一下,这个思路其实就是三个环节:
1. 随机找到两个单元格
2. 交换这两个单元格的内容
3. 重复上述步骤若干次
有了思路,直接对应写成代码就可以了:
1. 随机找到两个单元格。
如我们要将 B3:B8 也就是“鸳组”这一列打乱,那么使用 Range("B3:B8").cells 就可以得到这个范围内全部单元格,总个数是 Range("B3:B8").cells.count 。
所以我们可以调用随机数函数 Rnd() 生成一个0-1之间的随机小数,接下来按照《提高篇》中的介绍的公式,用 int(Rnd()*Range("B3:B8").cells.count)+1 ,就可以得到一个随机整数,取值范围最小为1,最大为单元格的总个数。
假设这个数字是x,那么Range("B3:B8").cells(x) 就是这一次随机读到的单元格。如此再执行一遍,就可以再次随机找出一个单元格。
2. 交换单元格内容
随机找出了两个单元格,接下来的事情就是“交换二者的值”。这可以说是程序设计初学者最早掌握的基本技巧之一了 —— 直接定义一个中间变量,然后重复读写就可以:
3. 重复上述过程 N 次
不用解释,当然是循环!
所以再看这个完整的代码,是不是觉得很简单呢?
不过上面的代码只是打乱了一列数据(B3:B8),假如我们希望打乱任意多列又该怎么办?答案仍然很简单:我们可以把“打乱一列数据”的代码做成一个函数或者子过程。这样,即使我们想打乱10列,也只需调用10次这个函数就可以。下面就是杨老师做的示例,可以一次性把B到F列的数据都打乱重排。其中“随机重排”这个子过程能够通用在大多数类似需求中,感兴趣的同学可以抄下来直接做成自己的工具库:
最后还有一个问题,相信很多同学一直在等待答案 —— 怎样在随机重排时夹带点私货,比如保证楚留香和赵敏一定在一起呢?
很简单,重排结束之后,写两行代码找到赵敏和楚留香各自所在行号,然后将赵敏同行的男生跟楚留香做一个交换就搞定。至于具体的作弊代码,就留给感(ai)兴(re)趣(huo)的同学自己研究吧 ……