2021-12
17

VBA合并报表

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

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

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

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

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

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

2021-04
13

新悟汇总

By xrspook @ 8:36:57 归类于: 烂日记

前段时间我看了几页POWER BI的教程,然后我就重新开始了之前我没有完成的事。因为我知道我肯定是要把那个东西搞定。虽然POWER BI的教程只看了几页,我已经对数据的理解有了一个确切的答案。如果可以的话,如果分析软件足够强大的话,又或者说个人电脑足够厉害,我们完全可以利用最基础的原始数据进行各种蹂躏,这样的话,颗粒度是最小的,也能够组合出最多的信息。但很多时候因为最原始的东西信息量实在太大了,有可能电脑吃不消。也有可能是别人问起来的时候还得各种加工处理,比较麻烦,所以就有了汇总数据这种东西。在汇总数据上面做汇总数据,的确能得到某些东西,但这样的话,显然就限制了想象力。因为一开始某些数据已经被汇总起来了,于是里面的某些细节再也不可能被打散。这样做不得不牺牲一些东西。可能那些东西暂时来说没什么必要,但说不准什么时候就会用到。所以可以的话,我还是比较喜欢对最初级的东西进行处理,而不是在汇总的基础上做汇总。在汇总的基础上做汇总,最终只能得出某些目标结果而不能天马行空,我觉得这样挺无聊的,但很多时候大家为了纯粹完成任务,不得不这么干。我觉得如果非得这么干的话,不如写个VBA脚本,全套自动运行没有烦恼。毕竟如果真这么干的话,做的纯粹是条件反射,循规蹈矩得出那些东西我觉得基本上不怎么需要动脑子。脑子应该用在制定程序上面而不是一次又一次重复某些步骤。在重复步骤这个能力上,人类远远比不上机器。我仅仅看了几页的POWER BI教程,已经给我明确了过去我一直都隐约觉得,但是却一直都没有总结出来的东西。现在我证实了我的直觉是正确的。

POWER BI之所以是POWER BI,因为它们可以处理Excel不能处理的东西,百万级的东西可以轻松搞定。从前那些不得不分几个Excel才能得出来的结果只要从那里导入,根本没有底线这个说法。我还记得新冠疫情某段时间英国说他们的统计数据出现的问题,有些数据被漏报了,原因是信息量超过了Excel最大的行数。这也实在太扯淡了吧!他们怎么会不用数据库呢?数据库的底线好像我还没听说过。如果Excel有底线的话,完全可以把数据分在几个Excel文件里,然后通过POWER BI把数据联合起来。所以他们给出那种完全不能让人接受的答案的时候,我在那里呵呵。那种数据级别居然还在用Excel,而没有没有专业的数据库去处理,这实在太让人震惊了,是因为他们只习惯于用Excel吗?大概是因为改数据的时候比较方便吧。毕竟进入数据库进入服务器的东西是会留痕的,但Excel可以随心所欲颠倒黑白,想怎么写就怎么写。

我不知道现在的世界到底有多么的水深火热,反正今年的股市就一直都很不咋地,我的基金被套住了,一开始只套一点,现在越套越多了,于是我也懒得去看到底是怎样。与其把人生耗费在钱生钱上面,不如干一些实实在在的东西。

2020-12
10

必须用一维数据的气泡图

By xrspook @ 9:12:44 归类于: 烂日记

我花了一个下午的时间去琢磨气泡图这东西。的确这种图能最大限度地反映维度,但是我足足纠结了一个下午,才明白,为什么我一直得不到我想要的图,虽然以前我已经用过气泡图。气泡图这种东西,数据必须以一维的方式罗列,起码在Excel里是这样的。虽然最终,气泡图的效果是一个二维甚至多维的图。我不知道在Excel里怎么才可以快捷地生成气泡图,到底哪些数据为X轴?哪些Y轴?如果我还得设置气泡大小,也就是要设置第三维数据的话,难道我只能一个个手动添加?

横坐标是小时,纵坐标是月份,一个是24,一个是12,都不是一个小数目。在数据透视表里面做一个二维的分析很容易就能看清。我一直都想把二维表用气泡图表示,但是我那个二维表无论如何生成的气泡图都是错的,没办法系统生成,也没办法手动达成。最终我纠结了一大轮,把那个二维的表改成了一维,而且经历了非常人肉的手动加工后终于搞定了可能是一开始我想要的气泡图,但显然那个图太恶心了,完全不符合我的要求。最终,我直接用二维表的数据做一个方块图。方块图的颜色当然不是手动加工,而是用条件格式,我选了一个色阶,颜色过度是从绿色到红色,中间是黄色的,相比于单色过渡到,这个比较明显一点。理论上用从白色到深色的色阶估计会最容易让人看懂,但是浅色部分实在太难辨认了。因为单元格的颜色用的是条件格式,是默认渐变,所以效果非常好。因为每个单元格一个数据,如果能把那些方块模糊掉,变成类似热力图的东西,效果可能会更好。但是我真的有必要把那些方格模糊掉吗?如果要做那种模糊,可能PS一下可以做到,但我感觉现在的效果已经很不错。一开始我想到的是用三个填充颜色的单元格作为图例,但我的方格图里除了我选择的那三种颜色以外,还有其它的渐变。所以最终我把条件格式里三个颜色的代码抄写下来,然后插入一个方块图形,填充那个渐变颜色,然后拉出一长条,做图例的效果非常好,简直有点出乎我的意料。

为什么我要纠结,必须得反映这个数据呢?我也不知道,但是我就是想反映出来,因为正是因为这些现象,搞得我挺痛苦。不只是我一个人痛苦,单位的其他人也挺痛苦。这样的作业模式,让我们单位的收入保持增长,但是我甚至都有点怀疑我们是不是必须得用这种方式。数据出来以后,简直让人有点无语。去年和今年,单位有作业的日子里有60%以上是晚上18点以后才结束的,而其中有20%,更加是在晚上21点以后,让人觉得相当郁闷的是,作业基本都集中在下午和晚上,尤其是晚上,晚上的作业时间跨度是最广的,量也是最大的,但绝大多数时候,我们就只安排一个帮助。如果我们确定我们的经营就必须得这样,那么在人员配置上、在人员安排上,我们就必须做改变,而不能再拿出别的单位都不这样的借口来推搪。

我在把那些人视而不见,或者凭感觉猜测,又或者根本没意识到的东西摆上台面。

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
23

我实现这些

By xrspook @ 10:16:14 归类于: 烂日记

渐渐地我越发有点明白自己想要的到底是什么。之所以让我纠结了半天,不知道该如何计算的某些数据,实际上是因为两种性质的东西混搭在一起了。我不知道别人是否真的搞得清,他们是有意糊弄我们的吗?是因为他们知道我们算不清这个,所以这么搞吗?这个问题很困扰我,是因为那个东西把正常的批次跟没有写明批次但要先进先出的混搭在一起。有明确批次的数量计算是不分仓号的,因为通常那意味着好几个仓进同一批货,开始计费的日期定在第1个仓刚开始进货的那天。所以,如果同批次还有其他仓,最后一个仓进货的时候可能已经是3天后,那个仓的免堆期等于要减去三天。如果某个客户所有东西都这么弄,不会有烦恼,因为无论进货还是出货,肯定会带入批次。

在Power Query的世界里,这就意味着要以批次和日期分组。各种仓号数据可以全部加在一起,无所谓。但同时,这个客户又有另外一种情况。如果他进货不是用船,而是用车,每天几千几千吨进货的话,我们跟他们签订的合同是按每天为一个批次。一个批次到达免堆期以后开始计费。现在我们的系统总的来说,根本没分清这种批次,计费完全是人肉计算的。但实际上,这种没有批次的批次,理论上也应该自动带入,入库的时候以日期为批次。所以有可能发生同一个批次有几个仓出库的时候,要以先进先出进行批次划分。进货的时候,一个批次可能有几个仓,但是出货的时候肯定是指定的。所以在这里要以仓好为分类,然后在对入库批次分割数量。比如某一个仓里第1个日期批次用完了再开始用第2个,第2个没用完,就留到以后继续分割。据说用SQL和Power Pivot能解决这个先进先出的问题。但貌似,我看到的Power Pivot的例子没有看到我想要的最终结果,也就是把那个出库数量自动分割成入库批次。先进先出是非常经典的会计问题,只不过我们的人从来都是我觉得我要指定这个,没有用过这些规则,当然也就没有思考该怎么自己动手去计算。一开始没有批次,用先进先出的规则,入库的时候自动添加批次,出库的时候按照入库的批次风格,最终得出来的数据,跟之前一开始就已经有批次的东西可以用同样的规则继续演算。

对我来说,有两个点我没想明白。首先,要怎么在PQ里以先进先出的规则,对出库数量进行批次分割呢?第二,因为实际上我们得到的信息是一个入库日期和出库日期。而这些日期通常都是不连续的,在不连续的日子里也要计算每天库存,到达一定时间之后,要每天计算堆存费。所以,该怎么在不连续的日子里插入一些日期,然后向下填充信息呢?之前我想到的办法是先建立一个日期索引。然后把有数据的东西合并到索引里,这就意味着有些日期可能是没有数据的。接着,把这些东西透视展开,把数据为空的那一列删掉,余下的东西向下填充,然后再把零数据替换为空,接下来再用一个逆透视恢复。这个方法比较笨。但可以一次性实现N个批次的插入和填充,至于速度如何,估计跟电脑的性能有关。我总觉得高手一定不需要用到这种先透视再逆透视的方法。他们是怎么插入日期,然后向下填充数据的呢?

想清楚了这些,离我想要的最终结果就会又近了一些。

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