我的天

第一次加VBA+ADO+SQL里跨表查询到一个表之后，发现运行时间只需要0.12秒。那个时候我就觉得如果我要完成之前已经在PQ、PP和python的那个方案，在VBA里估计只需要很短的时间，有可能是一秒以内实现，但最长时间我感觉不会超过三秒。之所以有这种感觉是因为之前我用VBA的时候，如果我使用的是数组，使用的是数据最后一次性的打印出来，中间不显示，通常运行时间不会超过一秒。如果我不是用数组操作，而是在折腾单元格，比如做一些打印格式的转换，时间可能会长一点。那个时间的长短跟我电脑正在处理的任务多少有关，如果电脑比较空闲，时间会很短，但如果电脑正在运行其他的东西，比如FireFox里打开了N个网页，那么这个运行时间可能会长一点，甚至会超出我的想象。最终，当我把整个之前的那跨表查询方案在VBA里实现的时候，我办公室的那台电脑大概需要1.3秒。这个1.3秒是不确定的，有时可能需要1.4甚至1.5秒，但是也有可能1.2秒就可以。

当我在进行了一些数据的优化，比如当那个字典完全赋值给数组以后，就把字典关掉，又或者是减少一些变量，比如在我用SQL进行查询的时候，因为有些步骤太长，我的脑子又转不过来，所以我一个步骤跨了好多段。多段实际上是一路到底，后面不需要引用步骤中的数据，在写下一段的时候，我就不再定义一个新的变量，而直接沿用上一个变量名。连续三段，都是为了得出最后一个答案，在我优化之前，每一段我都会用一个新的变量名。仅仅是在使用完字典以后就把它关闭掉这个操作就让我的运行时间马上提升了0.2秒。我不知道为什么，效果居然如此明显，字典是个非常高效的东西，但原来之所以这么高效，非常有可能是因为它占用了资源。虽然释放字典这个操作基本上是在整个程序的最后部分，但依然能明显提速。当我慢慢地研究一行又一行的合并变量名以后。整个程序的运行时间有可能在一秒之内。通常是1.1秒，如果我同一个时间刷新多次的话，非常有可能会出现0.9秒。但就使用而言，你怎么可能就为了那个0.9秒，把它刷多次呢。因为刷新多次，实际上也花了好几秒，这是完全没有意义的。写这个VBA就是为了让你打开文件，输入相关参数以后进行查询，一次能查到的那个时间才是意义所在。PQ输入参数，点击刷新，第1次的刷新时间肯定是最长的，在成功的刷了第1次以后，继续刷，时间你会觉得明显缩短。所以的确最后这个VBA文件有可能在我的同一台电脑上刷出0.9秒，但在我的能力范围之内，我顶多能改到在那台电脑上首次刷新小于1.1秒。

运行效率这个东西在不同的电脑上效果是不一样的，在我做python方案的时候，我就已经明显感觉到了。python方案的运行时间大概6秒。在我家的电脑上大概需要7-8秒，在我那个不插电源的笔记本电脑上需要10秒。这个让我挺惊讶，因为笔记本电脑购买的时间比我家台式机组装的时间晚起码5年以上。之前我就试过，在笔记本电脑上插电和不插电压片，结果发现插电的时候性能会明显飙上去，CPU的使用率会飙起来。据我观察，在python方案的时候，CPU的使用程度要比内存大，运行PQ的时候刚好反过来。所以电脑的CPU越好，python方案的运行时间会越短。宿舍那台神舟miniPC5的运行时间大概跟我家里的电脑差不多。VBA的方案我没有在宿舍的电脑测试过，也没在笔记本电脑上测试过，但家里的那台电脑，我感觉运行时间有点不稳定，有可能会超过两秒，但有可能会是1.3秒。为什么会这么不稳定呢？VBA的那个脚本，我同事的运行时间通常能在一秒以内，曾经试过0.8秒，她办公室的电脑购买时间大概比我的晚两年。就我俩办公室的电脑来说，差距大概是0.1秒。对运行时间得大概6秒左右的python来说，她只需要5秒多一点。

运行一个跨表查询，VBA+ADO+SQL这套方案就只需要一秒，其实已经很快了。如果我把那个VBA根据输出表格的类型拆分为两个，我觉得运行时间能进一步的提升。毕竟其实输出的4个表格通常不会一起用到，但是如果现在需要一秒，单独一个也需要0.6秒，我为什么要做这个拆分呢？

接下来我会继续研究一下还能怎么改进，研究完以后再把这个发给专业同事，询问一下改进意见。

花了大概两天的时间呢，我做出了大部分的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有它自己的解决方案。

几天之前我第一次听说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的条件以后就变成了大概应该是这样的事情实际上不行。

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

又花了几乎一天的时间去研究在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。

昨天说到用VBA做多条件单汇总的分组，要怎么个做法呢？

VBA这个东西其实我并不太熟悉，我没有系统学习过，或者说其实是系统过的，但只是皮毛，而且已经是多年以前了，之后用得不多，所以忘记得差不多了。所以现在我只是需要用什么的时候就去研究。在开始之前实现我的目标之前，我也先去搜索了，得出大概两个方案，一个是用字典，另外一个是用SQL。就思路来说，我感觉SQL比较直接，因为那调用的是列的信息，各种聚合会非常方便。我不知道VBA里面的SQL到底是怎么个执行法。因为2003或者以前的office，如果要做跨表查询就得用SQL。我已经不记得那些跨表查询到底只是一个工作簿里面不同表，还是直接可以跨不同的工作簿，但可以肯定的是，如果某Excel文件里运用了SQL，那么这个Excel文件一旦移动，比如发给了别人或者是U盘拷贝到另外一台电脑就会查询失败。之所以这样是因为实际上Excel在电脑的某个地方存储了与SQL有关的某些东西。虽然你把Excel文件挪走了，但是存储在你电脑里面的某个东西还在原来的地方，那是一个绝对地址。不管那个到底是绝对地址还是相对地址，简而言之就是实际上你只能挪走了一部分的东西，另外一部分还在那个地方，但即便你那么高难度，那个电脑深处的东西也挪走了。即便把它像放在另外一台电脑相对位置一样的地方，但还是识别不出来。所以最终你只能放弃那个查询，然后贴同样的代码，在另外一台电脑上重新建一个，这个操作很逆天，非常不方便。正是因为我知道这个，所以虽然在合并查询上SQL很方便，但是不到迫不得已我不会这么干。如果我真要这么干，我就不用Excel，我用了Access。以前的 Office 2003专业版都是自带Access。说起数据库的操作，在那个地方用SQL就很自然。

想到分组的时候，我当然有考虑过SQL，但是我知道以前的Excel对这个很不友好。所以别人做的某些方案，SQL版本干的事情很简单，但前面一大堆的参数引用会让你眼花缭乱。相对而言，字典是VBA的原生，所以说VBA一定能很好支持，因为VBA的字典是提升VBA性能的一个重要体现。我觉得不仅仅是在VBA，在python里，用字典和不用字典做同样的查询，效果差别也非常明显。我想要的多条件单汇总要怎么做字典呢？我首先想到的当然是把条件都合并起来作为键，然后把要汇总的那个作为键值。这就保证了前面那些条件一定是唯一的，后面的键值做累加就行了。当然，你也可以不把前面所有条件都合并成键，你只需要把一些特征条件合并就行。把多条件合并起来，做一个循环，用分隔符把它们连起来就可以了。耗费我比较长时间的是，我建立了这个字典以后，我就需要把键和键值都输出重新组合成一个数组，那就是我想要的多条件单汇总结果。但问题是怎么才能把合并起来的键重新分解输出呢？我研究了半天。理论上被我连接起来的那些条件放的键实际上是一个字符串，而且里面有规律的分隔符。我利用分隔符就能把它重新打开，然后我新建一个数组接收这些被打断的字符串。这个接收了打断字符串的数组外加字典的键值，合并起来再复制给新的数组就是我想要的东西。思路是这样的，但是当我要执行的时候，我却一直卡在那个地方。关键就我在定义这个接受打断字符串数组的时候加了括号。为什么其它数组在定义的时候就得在名称后面加括号，但是这种接收字符串的数组在定义的时候就不能加呢？这个是我搞不懂的。事实是不加我就可以实现功能，加了就会说类型错误。这种接收打断字符串的东西不应该称作数组？我已经不记得到底我是从哪里学回来的这一招接收打断字符串形成数组的这种做法。生成一个表格标题的时候，这样做非常简单高效。

先把多条件合并成一个字符串，然后再把这个字符串打断。这种方案我在网上也看到了，别人也是怎么干，但是别人之所以没有像我这样卡住，是因为把字符串打断的时候他们并不是赋值给数组，而是直接在单元格里输出了，于是他们也就没有定义数组名称时加了个括号就卡住的烦恼。

最终，我实现了自己的目标用VBA接收源数据，然后进行加工处理，做一个多条件单汇总，最终在一个固定格式的表格里输出并另存为一个新文件。整个过程你只需要在VBA所在的文件里面按一下初始化按钮，然后把需要整理的数据贴进去，然后再按一个相应的生成文件按钮。文件生成的过程可以说是秒杀，几乎不需要时间。生成的文件会自动保存在VBA文件的相同目录。这个VBA文件你可以点保存，整理后的文件内容也会在VBA文件的相应页面输出。如果你需要查询，直接可以在那里看，如果你根本不想看，这个VBA文件你甚至不需要按保存。因为如果粘贴源数据前内容是空的，下一次你再进来的时候就不需要初始化，所以步骤就更为简单，下一次你只需要做粘贴以及点一下生成。明明转换这些数据很简单，VBA可以秒杀完成，为什么PQ就得转那么好几秒呢？之所以在做这个事情上我不做python方案，是因为你还得把源数据贴到Excel里面，保存关闭，再按python生成。这就意味着。虽然你只打开关闭了Excel文件一次，但是python在运行的时候又得把Excel文件打开关闭一次，这样就得耗双倍的开关时间。如果数据量很大，VBA很慢，又或者那得进行多个文件的拼接才能取得一个很大的数据，汇总的数据超过了VBA的处理范围，那么可能我会选择python方案，但现在，顶多每次就几百条数据，难度是非常低的，高效完成是我最看重的，因此VBA是最佳选择。

用过VSCode以后，当我在Excel的VBA里编写代码，真的非常痛苦，因为当你一句话没写完，就要在其它地方复制某些东西过去的时候，就会给你弹窗报错。为什么你非得给我弹窗呢？你给我下面波浪红线不好吗？又或者当我要运行的时候，你才告诉我那个不对不行吗？我写一半就不写了，并不是因为那是我写错了，而是因为我需要做其它复制粘贴，又或者是切换个窗口做一下别的事情。如果能在VSCode面直接编写VBA，然后能直接在Excel里面调试运行，不需要把代码贴回VBA那么折腾该多好。

我可以忍受VBA的各种规则，但是我真的不喜欢Excel自带的VBE。

VBA多条件单汇总字典方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

'crr（i, 1 To 7）为数组，多行7列，前6列为条件，最后1列为数据，需要汇总
Set dic = CreateObject("scripting.dictionary") '建立字典
    For i = 1 To UBound(crr)
        x = crr(i, 1)
        For j = 2 To 6
            x = x & "|" & crr(i, j) '合并分组依据
        Next
    dic(x) = dic(x) + crr(i, 7)
    Next
Dim err, frr() As Variant
ReDim frr(1 To dic.Count, 1 To 7) '新汇总数组
i = 1
For Each x In dic.Keys
    err = Split(x, "|") '打碎分组依据
    For j = 0 To 5
        frr(i, j + 1) = err(j)
    Next
    frr(i, 7) = dic(x)
    i = i + 1
Next