我的天

明明想好了，要早点回宿舍动感单车，但是还是不知不觉的拖到了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里就会让我经常碰壁、非常绝望。

花了大概两天的时间呢，我做出了大部分的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