点击上方蓝字关注我们吧
从2016年的VBA课程开始,我们的目标一直都是“让各行各业的人士都能通过简单编程,高效解决手边的问题”。但是随着课程的不断深入、同学们的胃口越吊越高,杨老师突然发现:自己的公众号似乎越来越追求“新奇有趣”,而距离初学者却似乎越来越远。所以决定,从今天起重接地气,多写一些各路大神不屑一顾的“新手问题” ,具体就从我们最熟悉也最需要的办公自动化开始。
职场如考场,解题有套路!日常办公中,大多数需求都可以归结为不同题型(比如“工作表比对”、“趋势分析”),或者这些题型的组合;对应地,解决思路也可以抽象为固定套路。对于初学者来说,只要掌握了基本的VBA/Python/SQL编程技能,也就是我们《全民一起VBA》、《全民一起玩Python》、《全民一起用SQL》讲解的知识,然后熟悉一下这些套路,日常办公编程当是不在话下。
今天我们就聊聊这样一个常见题型 —— 查找连续范围。
所谓连续范围,就是数据记录中“连续出现”的符合指定条件的记录集。这种需求在各行各业中都很常见,咱们随手就可以举几个例子:
(1) HR:下面是某员工某一月份的出勤表,其中 x 代表当日请假,灰色代表周末非工作日。现在请找出该员工本月请了多少次长假(3天或3天以上视为长假):
(2) 物流:下面是出库清单,请找出出库数量连续3天(或以上)大于1000的日期段,并高亮显示:
(3) 投资:下面是2022年12月的上证指数趋势,请找出该指数的最长连涨期间并高亮:
类似例子举不胜举,比如质检部门希望找出连续3次抽检中次品率始终高于1%的生产线、国际足联希望统计世界排名多年连续下滑的国家队等等 ……
显然,这些任务的共同点就是找到每一个“连续段”的起点与终点。具体怎样做,还是老规矩 —— 先“反思”一下自己怎样观察这种问题。就以前面的连续长假为例,杨老师整理了常人的思考过程,然后直击灵魂、手绘如下:
(特别提醒:超长图片,共计11格小图。另外每张图片左下角画的是大脑,真的不是大肠)
仔细领会这个肉眼判断连续假期的过程,可以发现其中固定的模式是:
(1) 依次查看每个单元格,自动忽略非工作日;
(2) 每次看到一个单元格,如果它是工作日,就进行以下判断:
a) 如果符合“请假”的条件,并且目前大脑中没有记忆任何“连续段起点”,则将这个点记为新连续段的起点,于是记录下这个新起点;
b) 如果符合“请假”的条件,并且目前大脑中有一个“连续段起点”,则认为这个点是当前连续段的一部分,于是将当前请假天数增加1;
c) 如果不符合“请假”的条件,但目前大脑中有一个“连续段起点”,则认为这个点是当前连续段的终点,于是判断这个连续段是否是我们想要的,然后将已经记住的起点号码作废。
这里有一个细节,编程新手可能会感到无从下手:最后一步中,怎样将一个已经记住的起点作废呢?其实很简单,我们指定一个正常起点不可能用到的数字 —— 比如-1 —— 代表“作废”就行。如果想作废一个起点,只要把让“起点=-1”就行。
现在把这个模式写成通用的套路,就是下面的样子。这里我们使用 VBA 代码演示,不过正在学习《全民一起玩Python 办公自动化》的同学也完全可以看懂,使用 python 的循环/判断结构与我们课程中介绍的xlwings 就能写出等价的程序。
想清楚这个套路,我们只要根据具体任务需求,写清楚什么叫做“当前单元格符合条件” 、什么是“发现起点时的操作”、“仍在连续时的操作”、“连续段已结束时的操作” 就可以。
比如对于判断长假这个例子,上面四个“替换项”分别如下:
“当前单元格符合条件” —— 单元格内容为x
“发现起点时的操作” —— 本次请假天数暂定为1
“仍在连续时的操作” —— 本次请假天数增加1天
“连续段已结束时的操作” —— 如果本次请假天数大于等于3,则长假次数增加1次
把这几项替换进去,就是下面的代码和效果:
可以看到,这段不超过20行的VBA程序几乎是直接套用前面的“连续范围识别套路”代码,只不过在每次循环时,使用 Range.Interior.Pattern<>xlLightUp 排除了阴影格式的单元格,也就是“周末非工作日”。这就是套路!
既然是套路,那么我们就看看任务2——“将出库数量连续3天大于1000的日期段高亮显示” —— 能否也用它搞定。
与任务1一样,我们首先列出“连续范围套路”中的4个替换项:
“当前单元格符合条件” —— 单元格数值大于1000
“发现起点时的操作” —— “连续大于1000的天数”暂定为1
“仍在连续时的操作” —— “连续大于1000的天数”增加1
“连续段已结束时的操作” —— 如果“连续大于1000的天数”大于等于3,则将起点到终点的单元格背景色设置为绿色
把这几项替换进去,就是下面的代码:
显然,又是直接代入就搞定!这就是套路的力量!
接下来我们再“套路”一下案例3 —— 找出上证指数最长连涨期间:
与前面一样,首先列出“连续范围套路”中的4个替换项。不过这一次因为涉及到“选出最大值”、“记录最大值的起点与终点”,所以与前面案例的相比,需要多记忆几个变量:
“当前单元格符合条件” —— 本单元收盘价大于上一行的收盘价
“发现起点时的操作” —— “本次连涨天数”暂定为1
“仍在连续时的操作” —— “本次连涨天数”增加1
“连续段已结束时的操作” —— 如果“本次连涨天数”大于之前记录过的“最大连涨天数”,则将本次连涨天数登记为最大连涨天数,并且记下本次连涨的起点与终点。
如此这般,最终代码如下:
最后再次强调:跟武术中的套路一样,编程套路也必须要勤加练习、形成肌肉记忆,否则遇到此类问题时,还是一样无从下手、甚至无法跟自己熟悉的套路对上号。因此请已经读到这里的各位同学再加一把劲,找些数据亲自动手,把文章里的套路“炼化”到自己心中。