2021-12
17

VBA合并报表

By xrspook @ 8:47:14 归类于: 烂日记

我花了三天时间(但实际上每天都只有半天时间)把二维报表提取数据成以一维数据表。这有什么难度呢?一开始我也认为好像难度不是很大,但开始琢磨以后我发现事情远远没有我想象中的那么简单。我用的是Excel的VBA。如果操作简单的话可以在Excel的数据层面进行操作,但如果我要进行大量的数据清洗显然为了要追求效率,就必定要用到数组,数组这种东西无非就是不断地叠加循环以及判断。循环和判断我觉得几乎可以这么说,是编程的核心。要实现最终的目标,首先你得有一个非常清晰的逻辑,然后用那些循环和判断实现你的逻辑。当我看到某些人逻辑彻底混乱的时候,我心里就会冒出一句话,他们绝对不会懂得编程。也正是因为这样,所以现在的幼儿教育有时会把编程列为一个非常重要的项目,实际上练习编程就是锻炼逻辑,但是逻辑这种东西又分为了既定的规则以及你的自创招式。自创招式都来源于各种规则,但是不同的编摆方式会有不同的效果。成功编程的话效率极高,是秒杀型的,但是要制造那个秒杀效果的过程可能非常漫长。

我需要处理的那个报表,准确来说是一套报表,但是用的几乎都是一个版式。虽然看上去几乎一样,但是有可能某些细节上会有不同。如果我用了一个靠谱的逻辑去抓取数据,无论什么微调,对我来说都毫无影响。所以首先我要确定我要在报表里抓取什么东西,我要用什么方式去除掉那些我不需要的东西。如果用Excel表格的思路去理解的话,我肯定是先做一个筛选,然后把我不要的删除掉。如果不要的东西在行那里我还可以用关键词筛选掉,但如果那些东西出现在列那里,难道我要先做一个转制,然后再做删除吗?更郁闷的是那个表格的表头有三行。就一个现成的要达到最终效果的报表而言,三行的表头没有任何问题,但是如果你要把它按照数据层面去理解,这就很逆天。就更加不用说这三行的表头里面还有不少合并单元格,但幸好的是当我把单元格拆分了以后,实际上每个单元格里其实都有靠谱数据的。

我的思路是先把我不要的行删除掉,然后把我不要的列再删除掉。在删除行这个问题上一步我就搞定了,但在删除列这个问题上,我花了好几步,首先是删除没有数据的列,其次是删除含有某些关键词的列,也就是那些虽然有数据,但对我来说也没什么作用的列。把可以删除的东西都删除掉以后,我进行了一个二维表转化为一维表的操作,接下来就是在那个加工完的表里加一列存放表名。之前我说过我要处理的是一组样式差不多的表格。上面说的一大通,我只是说针对一个表我需要这么处理,所以在这之前我要有一个把文件夹里所有工作簿里面包含某些关键词的工作表汇总到一个工作簿里的步骤。我觉得这个合并工作表的耗时挺长,相对于后面的数组操作来说,这实在太费时了。之所以会这么耗费时间,是因为实际上Excel需要把工作簿一个个打开,抓取里面的某个工作表,然后关掉,不断地重复这个步骤,不断地开关工作簿会非常耗时。

如果之前我没有花过很多时间在账本的合并数据上,现在突然要我整这个的话,我肯定得花好几倍甚至十几倍的时间,但因为现在我是站在曾经自己的肩膀上的,所以虽然期间有过纠结,但总的来说还算是比较顺利。我考虑过要不要进行这种操作,但过去几年我都一直没这么干,因为要合并这些数据,一年只做一遍,有没有必要开发这样一个VBA脚本去实现呢?当那个报表被国粮局修改了,我又得重新修改脚本适应。所以之前我就在那里纠结到底有没有必要进行这个脚本的开发。

最终我做了,而且还做到了。

2020-11
4

自强不息

By xrspook @ 10:23:24 归类于: 烂日记

到底我自己有多强大?即便我自己也不知道。

在对账这个问题上,貌似我的手上已经有不少的武器。那种东西是我不知道该如何积累回来的,已经成为了我的条件反射。该用什么方式才可以快捷方便而且全面,最好是扩展性也很强的。什么时候应该用搜索或者索引,什么时候应该用数据透视表,什么时候必须得上数据清洗……数据的高级应用,我很多还不知道,但现在,我已经不像从前那样只是蛮力复制粘贴了。

回想起来,10多年前当我第1次接触统计数据的时候,我用的是老员工留下的人工透视法。显然在手抄账的年代,那个东西非常直观。当时,李大姐的那个东西用了好几页纸打印出来,然后粘贴成一大张,我简直震惊了。上面的所有数据都是手填,万一我手贱了呢?每年的日期就那么多了,我们的仓会不断增加,我怎么能保证每个都不错?当时我没有想过可以用什么其他方法可以实现同样的事,我也没想到可以不用那样。怎么才能不人工?

那个时候我还不认识数据透视表。现在我觉得,数据透视表大概是Excel里面性价比最高的东西,那个东西是用来快速得出汇总数据的,但如果原始数据不规范,数据透视表就无从谈起。近些日子我又认识了Power Query。那个东西最擅长的是数据清洗,当然也可以把汇总数据一步到位,但是汇总数据这个活儿,我觉得PQ在处理不规范数据上用处更大,至于汇总数据这种事情,交给数据透视表就挺好,毕竟数据透视表可以随心所欲各种变换,而PQ一定程度上是在做脚本编写。如果只需要某个固定的最终结果,PQ一步到位,但是如果不确定需要的汇总结果,数据透视表更省事。因为数据透视表建立了,低手也能得出结果,但PQ这东西,高手写出来的嵌套公式,低手绝大多数时候都无法参透,即便你耗费很多时间奢望理解。没有一定的基础,PQ就是天书。现在,我仍然需要10年前那种用手工透视的效果,但我用的方法肯定不是人工透视。一年365或366天是固定下来的,但是随着仓数的增多,那将是一个无底洞。万一平房仓需要分堆垛了呢?那更加复杂,别说一张A4纸,即便是10张A4纸拼接起来也未必能表达得了。换句话说,如果10张A4纸拼接起来,我看完前面,再看后面,确定我在填写某一行的时候会不出错吗?

有些人只会延续前人的方法,埋头继续做。一开始的时候,我也是用前人的方法,但做着做着,在不知不觉之中我就改进了,最后得出了一套我自己的方法,我不知道这是不是最优,但我的方法可以让我得心应手,在提升质量的前提下,大大简化之前的操作。当我想让某个固定程序更方便的时候,我会直接写个脚本。现在我用的是VBA,或许以后我会用python。VBA是所有office软件都具备的功能,但python这种东西必须得电脑装软件支持。如果只是一个人使用,完全没问题,但如果数据需要多人协作,显然这样就有点不方便了,因为不同软件的版本可能会有一些意想不到的小插曲。

我在五行之中过日子,但我也必须跳出五行提高境界。

2020-10
13

Excel的高端玩法

By xrspook @ 8:43:14 归类于: 烂日记

数据本身没有问题,如果我们不能让它们确立某种关系,只是因为我们对那个东西还不够了解而已。在Excel里做一件事,你可以通过很多方法,比如说函数,比如说VBA,比如说SQL查询,又或者Power Query或Power Pivot。当然,我这里所说的,主要是针对查询,或者说数据清洗类的东西。如果纯粹是针对单元格的格式化,函数以及Power BI系列以及SQL是没办法做到的。

同样一个数据,用不同的方法都可以得出目标答案,但是哪个会更简便快捷一些呢?函数我觉得挺被动的,尤其是在处理大量数据的时候,效率非常低。因为在处理一些复杂东西的时候通常要用到数组函数,即便不需要用到数据函数本身,其实也在运用着数组函数的变体。而且函数这种东西受Excel本身版本的限制,越是低版本的Excel越是没办法轻而易举地实现某些逼格的功能。于是就出现了你不得不为了某个功能升级Excel,又或者因为你的伙伴升级了Excel,用了一些高端的函数,但是你却看不到,工作就没办法继续下去了。SQL和VBA是两个大杀器,很早以前Excel就已经支持。与其说他们是Office软件的一部分,不如说这两个东西更接近于编程语言。我对Excel里面的SQL不是十分熟悉,因为至今为止,虽然已经折腾了不少网站,但是我从未试过操作数据库。SQL在Excel可以用,但我觉得可能在Access里SQL会用得更顺手一些。比如说如果改变数据源,比如移动文件之后,SQL需要重新连接。若没有VBA的帮助,这是无解的。我不喜欢用SQL的其中一个原因是它会在硬盘的某个位置生成某个数据库。

VBA这个东西强大到任何你想到想不到的东西都可以控制,无论是数据本身还是说单元格的格式,一律通杀,它甚至可以让Excel自杀,又或者让你的系统自杀。VBA用得好不好直接决定了某个脚本的运行效率。是对初级用户来说,VBA的学习成本实在是高,除非你从来不打算要建立自己的规则而纯粹只是用别人的东西。

至于Power BI系列的Power Query和Power Pivot现在我仍然处在甚至还不能说入门的阶段,我只是稍微了解了一点这两个东西。在数据清洗和建立关系的时候,它们实在太强大了。但是要使用这两个东西,Excel的版本就必须有要求。所以这也导致了不少免费用户直接绕过这两个强大的东西。我也不知道为什么自己在使用Excel高级函数几乎还没入门的情况下,我就去折腾M语言。我觉得那个东西一定程度上颠覆了我对数据的理解。Power Query对数据的处理方式就像通过各种蹂躏就能得出你想要的东西,其间你没有修改原数据,所以实际上在写M语言的时候就像是手工编写一个宏,而那个宏要比一般的VBA简洁很多。之所以简洁,一定程度是因为那是在高级套用的前提下。Power Query里玩的数据转换实际上是在折叠、删除以及扩充,一定程度上就像是在用类似于递归或者迭代的方式。

别人把时间耗在应付考试上,我把时间耗在折腾自己上。

2020-09
19

我喜欢Excel

By xrspook @ 20:53:41 归类于: 烂日记

Excel的一般公式,我比较熟练,一些高级公式的叠加,我需要找教程套用,但起码我知道那是可以做到的。一般的数据透视表,是我一直以来用得相对来说最顺溜的东西,至于高级的数据透视表,也就是超级数据透视表我几乎不了解它的高级用法。在数据的筛选查询方面,之前我用的是公式,而近期,我知道了有Power Query这种神器。在这之前,我已经知道可以SQL语言查询。去年我开始系统学习了Excel VBA。这让我大大提升了某些工作的效率。当然这是非常有针对性的。对我来说,要开发一个VBA脚本需要好些时间,并不是一写就能用的那种类型,期间要经过不少修改。所以其实总的来说,对Excel的了解我还是比较全面的。

也正是因为有这样的经历,所以当我遇到某些综合性的问题的时候,当别人把目光主要集中在某个他们很熟悉的版块的时候,我会凭借我的直觉找问题,而不局限于他们觉得出问题的那个地方。比如在把SQL查询跟VBA结合的时候,别人会把精力放在SQL查询有没有写错上面。SQL有没有写错,其实我根本没看,对我来说那些东西太长了,看不懂,而且那个人写的VBA脚本缩进很有问题,看得我很郁闷,所以我就更加没有心情在那里琢磨。那既然能计算出一个正确答案,说明那个查询语句应该没什么问题。也正是因为写脚本的人的那堆东西格式比较混乱,所以我有理由怀疑那是拼凑起来的脚本,因为居然在脚本的开头连变量的定义都没有。为什么VBA里没有进行规范的变量定义,后面也居然可以照样使用呢?这让我有点惊讶,毕竟这是个VBA,不是python。C语言里,如果不先进性变量定义,后面根本用不了。在我记忆之中,VBA的变量在使用之前是需要先定义的。最终我发现是那个人的脚本之所以出错,是因为某些语句的套用搞错了,为什么他会把那个东西放在里?我觉得大概是因为他没有明白他一开始做的那个with是什么意思。但如果你问我为什么他把那堆东西套在里面会出错,而且是某些地方出错,不是全部出错,我回答不出来。理论上这种错误能在恰当的调试中体现出来,但实际上,VBA的调试句子我还用得不算很熟练。或者你会说,这是因为我的VBA学习还不够系统化,但我觉得我已经用了学习VBA最靠谱的那本书了。可以肯定的是,一些很基础的调试方式我还没掌握,如果我学会了那些东西,我可以大大提升我的调试效率,把错误定位得更精准。VBA脚本这种东西,我觉得最根本的是必须得理解。如果纯粹是各种套用,基础功能的确可以快速实现,但是当遇到的问题比较综合的时候,就会出现一些他们完全料想不到的状况。那种状况有可能与脚本本身的内容无关,与脚本的结构有关。

相对来说,Excel里我用得最弱的是高级公式的套用。如何用一个非常复杂的公式解决一些高端的问题是我一直以来都不大上心,或者说记得不够好的部分。非常复杂的公式,尤其是数组公式,虽然能解决一些神一般的问题,但问题是,其实那些公式需要耗费大量资源,所以在处理大数据的时候,非常有可能出状况。我是一个实用主义者,能做到某个功能,但是做起来的效率不高不好,我为什么要选择那种只是看上去很炫酷的方式呢?情况就像用VBA解决同一问题的时候,如果只是在工作表层面处理和先用内存数组处理再在工作表层面表达,效率千差万别。

Excel对我来说,除了要最终结果,过程也得追求高效和方便。

2020-08
21

懒精自动化

By xrspook @ 8:24:58 归类于: 烂日记

对别人来说,做某件事就是从系统上读取某些数字,抄在纸上,然后不断地敲计算器,得出某些数据,然后再按照某些格式整理成别人要的样子。之前我也是这么干的,但是我觉得,这实在是太累人了。我要做的不仅仅是读取系统上的数据,我做的是读取我自己的数据,然后跟系统上的做比对。做的东西必须是最小单位。明细对好了,后面的汇总是水到渠成的事。我觉得,汇总数据这种事,规则我定下来,结果应该是自动生成的,管好我的明细数据,汇总数据就不会出乱子。汇总数据出来了以后,还得按照某些格式整出来,这其实是一个填坑的过程,但是应该怎么填呢?逻辑的思路就是,如果有数据就展示出去,如果没有数据,就为空。这是主要思路,但某些细节问题其实也是需要考虑的,比如说数据与数据之间的标点符号:逗号,分号,句号,冒号等等。没有数据的时候,那个地方是空,但是在连接两个数据之间,有些人默认带入了标点符号,于是就会出现一个神经质的状态。数据与数据之间有一大堆莫名其妙的标点符号,正常情况下,那些东西都不应该存在。按照人的思路,那些东西根本不可能存在,但为什么有那些东西呢?显然是那个做判定的人没有把标点符号写到判定里。什么时候应该逗号,什么时候应该句号,当没办法判定哪个才是最后一个的时候,标点符号怎么办?其实真的不能判定吗?肯定是可以的,当某个元素的序号等于这个数组的最后一位,显然那个东西就是最后一个了。在Excel的世界里。尤其是不考虑VBA,只考虑前台的公式,这个逗号的判定在我现在的能力范围之内,的确有点难,所以遇到这种情况,我干脆每个数据给它一行,每行数据的末尾,不加任何标点符号。

昨天我突然意识到,Excel的公式编写是可以插入空格和回车的,当然,空格和回车要加在恰当的地方。比如加在数据连接的地方,不要加在某个函数里。又或者其实加在某个函数里面也是可以的,但是如果你在表达字符串的地方加入了很多空格,最终的结果肯定会哭。也正是因为回车的加入,让我的公式看上去更整洁了,我不用纠结不断地眼睛急转弯。复制粘贴也变得相当的容易。的确复制粘贴很简单,但是粘贴得多了,每一行的数据框架都是一样,只是修改了里面的部分内容就会让我自然而然想到使用循环。如果Excel的公式里有for或者while这种循环该多爽。换句话说,为什么就不能有循环呢?之所以这样,大概是因为Excel,没想过你要么在某个单元格里面展示一堆合并起来的字符串。这种字符串的合并,理论上更应该出现在Word或者普通的txt里面。

我首先写了个索引,然后写了个数据,接着对数据进行数据透视表,最后写了个很长的公式。根据数据透视表和数据透视表以外的一组数据,生成了两段字符串。字符串里面所有数字的格式全部都严格按照我需要的模样输出。这样做的确实现了我想要的功能,但我感觉还是有点麻烦,如果写个VBA,加入数组,这些东西很容易就能表达出来。从前前台看来,也会干净利落很多,但问题是,加入VBA的话。Excel的保存格式就得改变。对小白来说,VBA是个洪水猛兽。他们不知道该不该允许宏这个东西。如果我是他们,我不了解这个东西。这个东西的确有安全隐患,我肯定不会主动打开,即便我知道这个东西可能很强大。

开源的东西用起来得花一些步骤。组装好的东西用起来的确用很爽,但问题是,万一里面有猫腻呢?归根到底,还是自给自足、自娱自乐比较好。

© 2004 - 2022 我的天 | Theme by xrspook | Power by WordPress