2023-08
12

瞎写碰壁

By xrspook @ 9:50:38 归类于: 烂日记

明明想好了,要早点回宿舍动感单车,但是还是不知不觉的拖到了9点多。明明想好了回到宿舍就不再开电脑做VBA相关的东西,结果还是忍不住,我满脑子都是那个玩意。所以周四的晚上,我又只是匆匆忙忙我完成了一个30分钟的动感单车课程。理论上周三对我来说是动感单车的大课,但实际上我只上了一节15分钟的Y5强度,虽然强度很够意思,但持续时间太长短。周四晚上,本来我打算好好干一场,结果又被时间所限。

周四晚上11点多我才开始研究把字符串分组连接的数组。思路很简单,就是建立一个字典,如果键是相同的,就把键值通过顿号连接起来。这只需要一个循环就能做到,但我在那里磨叽了好久,最大的原因是我没有踏踏实实地手动进行前几轮的试验,而是大概凭感觉写好就丢给程序,于是一次又一次地被程序报错。有些时候是下标超限。有些时候是出来的结果完全不是我想要的。最终我整出来了,但就好像只是运气,碰对了。最让我觉得自己很无语的是,明明我在里面设置了if,如果这个键跟上一个键的内容是一样的,那么就把这个数值通过顿号连接到上一个键的键值那里,但是我却一直没有写 else,实际上我只需要再写一条,余下的就把这个数值成为这个键的键值。我处理的是非常典型的字典,没有比这更近更简单的了,但是我却在那里兜圈子,我自己也觉得自己很无语。

搞定了这个字典以后,我又试着把之前已经汇总好的表跟这个字典合并起来。思路也是非常简单,因为我只需要在原来的那个表那里加一列。当那个字典里面的键等于这个表某一列的某个数据,那么就把这个键值放在数据所在行最后一列的下一列你。结果我折腾了半天,为什么就是没有结果呢?最后发现我一直被数组的最大值给搞迷糊了。什么是1,什么2。通常来说我喜欢从1开始计数,但实际上记录集直接输出来数组是从0开始的,而且那个行列结构刚好和从单元格获取数据形成数组是一个转置的关系,本来1和2就已经迷糊,然后还得实际上得2变成1。因为我要新增一列,所以应该是加在什么地方呢?大半夜脑子本来就不太好使,再加上这种转置加1就让我更加迷糊。所以我已经不记得系统弹出过多少次下标超限。最后还是那一招,当系统说我超限的时候,我用鼠标看一下参数,果然发现了我进行for循环的那些设置都是乱来的,虽然肯定就是两个循环一次互换的操作,但里面的i,j我乱写,于是我又在那里折腾了好长时间。搞笑的是那个合并的数组的确没有问题了,在用Ctrl+F8运行的时候,我的确看到那个数组的里面已经齐全了我要的东西,但为什么输出到工作表的单元格就是看不到我想要的呢?最终发现又是数组最大行与列的问题。显然我的大脑非常不善于处理这个。因为我需要的那个数据在我单元格输出区域以外的地方,所以工作表里死活看不到。如果我一开始就耐心点的,我就不需要走这些弯路,但是这两个数组方案我都是一开始抱着大概差不多的心态瞎写的。

学习C语言的时候,我没有现在那么好的调试工具,现在的调试工具时的确强大了,尤其是如果我在VSCode里面写python,但就是因为这些东西很强大,它们可以把我的问题描述得很清楚,所以一定程度上我也习惯了大概差不多瞎写,出现什么问题再去改,但是这种习惯如果用在VBE里就会让我经常碰壁、非常绝望。

2023-08
11

完成大部分了

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

花了大概两天的时间呢,我做出了大部分的VBA+ADO+SQL的跨表查询。之所以说是大部分,准确来说应该是绝大部分,有一个功能暂时我还做不到的,那就是在分组的时候对字符串进行合并。如果我用的是MySQL,一个group.concat就可以做到了,但问题是SQL in Excel没有这个功能。有些人用一些非常复杂的手段实现这个,但我不确定那个方案是不是只能用在 SQL server上面。 SQL这个东西不同地方用的函数真的很不一样,像是Excel这种明显阉割版的,基本上我已经不抱什么希望了。最坏的打算是把最后那个分组前的记录及输出到一个数组,在数组的层面进行字符串的合并,对这个合并结果做一个字典,然后把之前其它东西已经合并好的东西也输出到数组,分别做两个循环合并到第三个新数组。或许不需要这样,如果我能把已经做好的那个字典作为SQL查询的一部分,我就可以继续以一个左外的方式连接除了这个需要合并字符串的以外的部分了。于是问题的关键似乎又回到了起点。在Excel的VBA里面,我到底能不能把数组作为SQL的数据源呢?这个数组里没有空值。

当我知道VBA里面做汇总可以用字典也可以用SQL以后,我马上觉得我能不能把我之前已经整理好的数组放到SQL里,然后做汇总?但那个时候我没有找到答案明确地告诉我可以这么干,应该怎么操作。通常各种教程里面说到VBA+ADO+SQL的时候,通常都会说怎么把SQL的结果也就是记录集输出到数组,但是从来就没有反过来干。因为别人的脑洞默认是你的SQL是从工作表里面直接取数,而不需要用数组从工作表里面取数,经过某些整理以后再传给SQL,但实际上对我这个半生不熟,只比新手老一点点的人来说,我经常是哪里会用就用哪个,哪个先学到就哪个在前面,前面的部分我已经用数组做完了,接下来的用数组好像有点麻烦,所以我就想后半部分能不能用SQL解决。

很多VBA问题大家经常是数组+字典,SQL+数组也有,但是好像没有先做数组再传入SQL。而之所以要这样,肯定是因为我的SQL水平还不到位。高手是那种你给他任何一个工具,他都能做出你想要的东西的存在,而我是那种做某件事就必须给我恰当的工具,否则我就做不出来的人。情况就有点像某些人写字就必须得用那那支笔,如果不是那支笔的话,他的字就写不漂亮了。在拧螺母这件事上,没有扳手、没有呆头扳手我就很难固定住螺母,但是对高手来说,呆头扳手、活动扳手,甚至你只给他一个钳子,他也依然能把螺母固定好。

最后这个分组字符串,我一定会继续努力的,希望 SQL in Excel有它自己的解决方案。

2023-08
10

开始VBA+ADO+SQL

By xrspook @ 8:20:30 归类于: 烂日记

几天之前我第一次听说VBA+ADO+SQL可以组成一个非常牛逼的东西。当我听说要在VBA里面运行SQL的时候,我有怀疑过这是不是我以前遇到的那一款?但是在SQL的外面又有一个ADO,这就很不一样,因为ADO这个东西你甚至可以控制只执行一次,然后就自动消失.当然你可你也可以用打开的方式去折腾,但是对低端用户来说,光是执行就可以完成大多数事情。

在学习VBA的时候,首先会告诉你,如果要操作工作簿,就得先把它打开。用完不用了,你还得把它关上,不打开工作簿是没办法进行操作的。同样,在学习用python控制Excel的时候,也是这么个思路。按照正常人的想法,你要获取里面的东西,当然得开门,哪怕实际上你只是进去看一看,你并不改动里面的东西。比如在做查询的时候,实际上你不过是收集里面的资料,你不需要改动里面的任何摆设,把资料收集回来了以后你可以再加工,但是原始的那个东西是不变的。为什么跨表表查询的时候,我首先想到的是Power Query,就是因为那个东西本来就是开发出来做数据清洗的。核心的观点就是不改动原始数据,通过可重复的步骤,你就能得到规范的东西,然后再给其它玩意调用。但是VBA+ADO+SQL彻底颠覆了我过往的认知。因为这三个东西加起来就意味着你要获取工作簿里面的东西,你根本不需要打开它。通过链接的方式,就可以到达那个地方,虽然实际上这样是无法修改文件内容的,但是跟PQ一样,可以对已经获取的东西进行再加工。所以准确来说,虽然做出来的菜是不一样的,但实际上原料还是那些,牛逼的地方在于,那三个东西甚至连文件都不用打开,但是PQ在运行的时候,实际上是要打开文件。如果你的查询设置比较复杂,他还得把同一个源文件一次又一次打开。因为PQ里的查询是并行的,你没办法控制哪个先哪个后,所以当它们抢着打开同一个源文件的时候就会出现错误,就会出现刷新失败,然后说不准什么时候就会告诉你原始的数据格式不对,反正最终结果就是刷新失败,但是当你再次刷新的时候又好了。

VBA这个东西在我听说有PQ和PP之前就已经存在。大家可以通过VBA的方式批量打开文件夹批量合并里面的文件,但问题是合并是合并了,但是却非常不智能,因为最简单的模式只能保证内容都给你贴上去了,但是对不对得上你自己碰运气。比如第一个表跟第二个表某些列的顺序是不一样的,结果你就会拼出个寂寞,但是PQ和PP就不会犯这种错误。因为他们有连接方式这种概念,同时,它们所处理的不是某个单元格,主要操作的是字段,又或者说是列。当然,如果你条件足够,可以精确到记录。我在python里,只要把从Excel读取到的数据赋予pandas,接下来你就可以用 dataframe的方式去处理那些东西。我个人觉得,只要格式化了,只要那个数据框架定下来了,SQL、PP、PQ、python这4个好像不太一样的东西都能干非常类似的活儿。

首先是Power Query版本,接着是Power Pivot版本,第三个是python版本,现在我做的是VBA+ADO+SQL的版本。SQL in Excel是一个非常屌丝的东西,相比于其它软件里面的SQL,Excel里可用的公式我个人觉得会让人非常抓狂,是阉割版的SQL。本来就很难找到一个标准的说法告诉人你SQL in Excel到底有什么可以怎么用,当你用其它方式套在Excel里发现用不了的时候,才知道原来根本不能这样。对我这个新手来说,我只是大概知道SQL可以这样,应该这么整,但是套了一个Excel的条件以后就变成了大概应该是这样的事情实际上不行。

一天到晚都在为代码纠结。反而让我日子过得很实在,虽然运动和打游戏的时间被严重侵占了。

2023-08
9

终生Excel学校

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

当我还是个学生的时候,虽然有电脑课,但实际上基本上没有教会我什么关于Excel方面的知识,最牛逼的那个估计得算是高级筛选,仅此而已。我觉得学生时代用得比较多的是Word,Excel可以说完全是一片空白。因为不懂,所以畏惧。那个全部都是表格的Excel到底可以做些什么呢?其实直到大学毕业,我的Excel都挺水的。能用,但是就只是把它当做是一个普通的表格而已,有什么高端好处,其实那个时候我并不知道。Word也可以画表,为什么我要在Excel里面做,不在Word里面做呢?工作了以后更加不会有人会教你这到底是什么情况。他们只需要你把东西做出来,至于你用什么方法,你是怎么做的,即便他们教你,你可能也会很嫌弃,因为他们用的是那种纸质表格的思路。

直到有一天不知道为什么,我突然认识了数据透视表,我已经彻底不记得我为什么会突然认识这个东西。然后我的世界彻底的不一样了。几乎可以这么说,数据透视表让我成为了真正的“表姐”。迷上数据透视表,才算是我认识Excel的零点。在那之前,我也会用一些公式,也会做一些合计,但是相比于后来我说感知的那个天地。之前我所做的那些感觉是幼儿园小班的事情。我认识了数据透视表以后,我算是刚刚上Excel小学了。

还记得高中的时候,我的某个同桌非常喜欢Excel,因为她爸爸是一个会计,所以估计她看他爸做表看多了。对我来说,Excel很神奇,高中时候同学就已经迷恋的东西,几乎10年以后我才刚刚不知怎么的入门了。

先是数据透视表,然后是Excel的公式,但相对于Excel五花八门不断更新的公式来说,我还是比较喜欢性价比极高的数据透视表,也几乎是同期,我喜欢上了ExcelHome。我不知道那个东西到底是怎么发展出来的,反正我一开始认识他们的时候,是个论坛,现在这个论坛还在依然,有非常多的高手。 ExcelHome里非常牛逼的专业人士会出书,出各种 office相关的教程。看过他们的书,也看过别人的书以后,我觉得他们真的很靠谱。当然不是所有都一定靠谱,但基本上你认住那几个人,只要确定那本书是他们主笔的,基本上不会出乱子,即便有些时候可能会吹水,但是起码能保证他们说的话你能懂,他们给你的方式你照着做,能得到一样的结果。如果你得不到同样的结果,他们会清楚地告诉你,是因为什么版本什么参数的原因造成的。看到他们的书买就行了。到现在为止,我依然觉得他们的书如果要入门的话直接入手Excel三剑客——数据透视表、公式、VBA。虽然VBA那本可能你用不上,但之所以为什么叫你顺便带上,是因为如果三剑客捆绑销售,而且又遇上促销,不买VBA只买那两本的价格可能比三本一起买还要贵。买的时候或许会觉得 VBA可能永远都用不着,甚至那本书都不需要拆封。但说不准什么时候,你想学习一下呢?那本书的好处在于不仅仅直接把案例丢给你告诉你源数据是什么,目标是什么,怎么去做。前面很大一部分的章节他们给你说清楚了那个东西是什么、原理是什么、可以做些什么。也就是说最基础的东西他们都告诉你了,以后再有什么超级牛逼的操作,就只是在使各种招数的组合拳而已。内功心法学好了,招式什么的只要懂得个套路就可以打出来。

我觉得让我走得越来越远的其中一个很重要原因是虽然现在我做的事情,我已经可以顺畅地做出来。经过多年的验证,没有问题,但是我不满足于现状。有没有一些更准确高效的方式呢?之所以有这种追求,最根本的原因是因为我懒。既然我有这个时间,既然我有这个需求,为什么我不趁着这个大好机会学习更多的东西呢?

如果这个是别人逼我的话,我一定会很反感,但是自学这种事情我是真心喜欢的。

2023-08
8

在Excel VBA里折腾SQL

By xrspook @ 8:54:30 归类于: 烂日记

又花了几乎一天的时间去研究在VBA里使用SQL。上午我主要卡在为什么我运行不了SQL这个东西。最主要的原因是我贴过去的那个例子是2014年的,那个时候的office跟现在的很不一样,所以是不是代码里的某些参数要改过来?4跟8改成12,我的确改了,但还是不行。吃过午饭以后我我把ExcelHome论坛前天大家才写出来的SQL查询复制过去,发现果然就可以了,因为除了12要都改以外,还得改一个Microsoft之后的单词…… 为了这个运行不了我还折腾的一番到底我需不需要装32位的AccessDatabaseEngine.exe,因为我发现自己的电脑是默认的那个是64位的。一开始我还以为自己的Microsoft 365是32位的,因为以前自动安装的的确是32位的,但是当我安装了32位的AccessDatabaseEngine.exe以后,发现还是不行,接着我就去看自己的office,原来已经是对64位了。因为据说64位要比32位快那么一点点,我已经不记得自己到底什么时候换了。换一个office对我来说毫无难度,因为只要登录我的账号,随时都能把那个离线文件下载回来。

总算可以在Excel里运行有SQL语句的VBA。据说要在Excel里运行SQL有三个方法,第1个是用远古的MS Query,这种方案在我用Office 2003的时候尝试过。接着是在新建连接那里输入一大串的SQL语句,这种事情我好像也干过。最后也就是现在主流推荐得最多的在VBA+ADO+SQL。这是我之前完全没有接触过的。你说我完全不懂SQL吧,也不是,实际上我也是有一点点懂的,因为高中的电脑课程里面就有Access,那就是一门电脑课,因为用得不多,所以印象不深刻。相对于其它编程语言,我觉得SQL的单词算是非常简洁了。入门是简单的,但是你要把它玩得很溜,一点都不简单。

从别人的代码语句构造来说,我觉得SQL要比VBA的数组好理解一些,但是当我自己要写的时候发现哪哪都不对。主要是虽然是SQL,但是不同软件里面的限制可以称得上是五花八门。当你搜索出一种SQL写法以后,发现在Excel里面无论如何都不行,最后发现原来是Excel不支持这种玩法。比如我要做一个多条件判断,我肯定毫不犹豫会想到用switch的方法,SQL里面都有case解决方案,但问题是Excel里不行,所以如果是多条件的,你只能不断地嵌套if。因为我多条件最终实现的是一个文本替换,所以我就足足套叠了5层replace才实现了我的功能。

用过VSCode你肯定会觉得Excel VBA最让人吐槽的就是VBE的各种奇奇怪怪的限制。如果你要在VBA里面用SQL语句,你就得用双引号把那圈起来,而且在双引号里面是不允许换行的。这就很让人抓狂了,不能硬换行可以,但编辑器为什么就不能给我一个软的自动换行?视觉上给我换一下行有什么问题吗?当你不得不把那串SQL圈起来,就意味着里面所有成对的符号自动识别一律失效,全部变成了白开水字符串。本来那就是很大串的东西,还没有方法帮你自动确认格式有没有错误。如果可以换行可以缩进,你可能一眼就看出自己在哪里出毛病了,有些配对没配对上,但是因为你只能一笔写到底,所以你怎么可能不犯错呢?犯的这些错误有可能是你不知道规则原来是不允许的。比如如果你只有一层select,字段里有别名,那么group by的时候你就不能用别名,因为group by比select先执行。在某些软件的SQL里,是允许加having语句,从而改变 group by和select的先后顺序,但在Excel里面加having是没有用的。如果你非得要在group by的时候使用别名,那么你就得嵌套select。内层select先别名,外层用group by。这些神神经经的限制实在让人抓狂死了。同样是分组,如果我是在PQ里进行,重命名列这种事情放前面放后面都无所谓,变形改造列这种事情放前面放后面也无所谓。因为重命名、变形、分组这件事情全部可以分步。但是在Excel的这个SQL里面,所有东西都得一次到位。明明可以短句实现的功能不得不写一大串。实际上在Excel以外的SQL写法里,是不是也非得这样?VBA的古板是显而易见的,但是当你见识过他们把SQL阉割限制成这个模样。你就会立马觉得python的自由奔放实在太好了。

死磕得越多,我越发现自己喜欢VSCode+pyhon。

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