我的天

昨天说到用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

说到就做到的，感觉非常的美妙。

前天说到我应该可以在VBA上更进一步，把数据整理以及最基本的汇总通过VBA一步搞定，结果我昨天真这么做到了。前天也说到VBA的copy让我非常的烦恼，但在我印象之中，以前我的烦恼并没有这么明显，之所以这样是因为以前粘贴时我不是直接粘贴单元格的内容，而是先把单元格的内容赋值给数组，然后处理数组，然后再贴回单元格。copy看上去很简单，但是在单元格的层面直接赋值好像无论如何都没有任何效果。把所选区域赋值给数组，相对好理解一些，但是要把数组再贴回单元格，相对于python的解决方案来说，VBA就很不人道了。因为我习惯了用python的方风格，所以回到Excel，之所以贴来贴去贴半天都得不到结果，就是因为我在数组复制内容到单元格的时候并没有设定单元格区域。不设定那个区域，永远都是贴了个寂寞。除非你使用Excel自己的那个方案，那个特殊粘贴的方案。我觉得这挺奇怪，既然我用特殊粘贴的方案可以不选定区域，为什么我直接粘贴数组就必须得这样呢？情况就像数组一开始就得设定好，得多大。当然有些数组是不需要事先设定长度，比如如果那个数组传入的数据是一个被打断的字符串。我不知道为什么被打断的字符串就不需要这么干。VBA数组一旦设定了大小，很难改变的。长度如果一开始长了，但最终你没用那么多，我也知道了最终的长度是多少，今天我的解决方法是先把这个二维的数组转置，在保留原数据的基础上限定最终的长度，然后我再把数组转置回来。之所以这么绕，是因为VBA只允许修改最后一个维度的长度。如果你是一个二维的数组。第1个维度是行，第2个维度是列，所以我的方式是行列转置，再转回来，但如果数组是三维的或者多维的，中间的某些要改，显然就不能这样了，但如果你执意要这么干的话，估计可以再建一个数组，设定好目标长度，通过套叠循环的方式把前一个数组的数据赋值到下一个数组里。但是为什么非得这样干呢？如果要限定数据输出的话，你只要限定数据输出的单元格是你最终的范围就可以了。但实际上，像我这种得到了这个数组之后，还得加工，还得在这个基础上汇总，我的数组就不能有空行。比如这一次我要在得出的数组的基础上再进行一个多条件的单汇总，如果我的数组里面有空行，最终得到的字典将出现一个键的内容为空，键值为0的玩意。我也不想这么折腾但是不这么折腾，真的没法玩，当然了，在输出字典的时候依然可以先做一个判断，让键是空的内容不输出。

字典这个概念，我首先是在学习python的时候了解到的。在学习python字典的时候，我就曾经用字典做过字母或者单词的数量计算。现在，如果我要进行前面我说到的多条件单汇总，我就需要用到字典。

至于在VBA里怎么用字典法实现我的目标，下回分解。

习惯了用VSCode写python以后回到VBA，感觉怎么整怎么都不对。首先相对于python来说，VBA的编写规则好像有点八股，因为那些专有名字总是老长。有一些让我想不明白的是某些东西为什么就不能先存储在某个地方，往后在别的地方调用？比如复制，理论上是很简单的事情，就只是从这里到那里而已。在python里，你喜欢怎么整就怎么整，但实际上你要用VBA控制Excel，做一个复制，远远没有你想象中的那么简单。对python来说，数据是没有格式的，没办法决定这一串数字到底是什么颜色、什么字体、大小、背景是什么，但是Excel的单元格有这些东西，你要复制一堆单元格到别的地方他默认就会把所有格式都带上，但实际上你不过想贴一个纯粹的文本或者数字而已。因为这样你还得在复制粘贴的时候用上其它东西去强调说明，而一旦有这个强调的操作，这就是一个特殊的复制粘贴。复制英文是copy，如果你直接把一堆单元格copy到另外一堆单元格，就会把所有格式都一起带过去，如果你不想干这种事你只想要数据本身，你就得把你原来的那部分单元格select，然后对select的部分做copy，最后在其它需要粘贴的地方再用特殊的粘贴方式。所有人都觉得最直观的方式点copy，当你要实现最纯粹粘贴的时候却无法做到。

copy本身很简单，是我们理解的很简单，但是对Excel的设计者、对VBA所控制的东西来说，copy实际上一点都不简单。昨天有段时间我曾经想过，能不能在某个单据格式改造的时候直接在VBA里做分组，然后得出汇总。但是我又非常明白，VBA本身是没有类似pandas的那种dataframe的东西。PQ和PP都有类似dataframe的东西，所以你不需要很努力一个一个数据去处理，不需要考虑其中的数组，你只需要操控好行和列的盘曲折叠就可以了。但是在VBA里，效率最高的一定是数组，数组没办法给你判别行还是列。在增加数据的情况下，你还不能在行业里增加，你必须把行列转置，然后在列那里增加，最后再把它转回行，这个东西很绕。虽然行列转制这种东西在pandas跟PQ里都有，但是在增加行或者增加列的时候，远远没有VBA的数组那么绕，于是你就可以花更多的时间去考虑我到底要怎么折腾那些东西，而不是在数那些东西到底有多少行多少列，我要增加什么，该如何插入、得插入多少。

C语言考试的时候我也是这么被C语言的指针搞得迷迷糊糊。如果我可以把C语言的指针玩得很溜，可能我对VBA的数组操作就不会有太多槽点了。可以肯定的是，当年的我跟现在的我对数组操作都比较畏惧……

因为不想用VBA去加工数据，所以我临时干的是用PQ做汇总数据，然后用VBA把得出来的数据精确地按照需要的格式生成文件。虽然实际上这样不过是点一下变成多点几下而已，但我觉得如果我能在VBA层面就完成了汇总，我就不需要 PQ运行的那几秒钟。我抠门的不是鼠标多点几下，而是刷新PQ那几秒钟，所以我还会在VBA上努力的。

花了两天时间，又做了另外一个PQ的跨文件查询。这一次思路我感觉相对比较简单。某一个大表基本已经满足了所有需求，唯一需要查询的是某个类别的合同号，另外一个类别的合同号在大表里已经有，所以需要做的只是研究该怎么分组。分组这个东西也是个学问。东西一旦分组就可以节省很多，然后你再把大表里面的列删减到你需要的范围，又能节省很多。这些东西节省下来必定意味着效率提高，最后我只把最终的版本输出，其它脚手架查询仅限连接。删除列有两种方式，一个是直接删除，另外一个是在分组的时候直接不理睬，这就意味着分组出来以后那些不被理睬的列被删除了。

一开始之所以想做这个跨文件查询，是因为很早很早以前，准确来说大概是2017年的时候我就考虑过应该用什么方式把客户跟数量，再把同一天的多个客户数量连接起来。这个东西思路简单，但以我当时的知识是没办法做到的。我现在用PQ、PP或者python都可以实现。首先是让客户数量已经称量单位无缝连接，接着是把一天内多个客户数量通过分组结合在一起。我觉得PQ的分组挺奇怪。文本没有给出聚合的方式，但实际上通过生成代码后修改公式，实际上可以把文本聚合，为啥就是不让可视化一步到位呢？？？同样，明明某些数字是可以用计数的方式表达出来，但是聚合的界面就只有行计数，对某些列来说那又是不可选的，但实际上同样先选择sum或者average生成代码以后，把那个东西改为count就能实现功能，没有任何问题，所以为什么在可视化生成的时候，就是不给我这样的选择呢？分组这个东西本来是很自由很牛逼的，但我估计很多人会被PQ这种阉割的可视化逼退，觉得可能那样做不到。

在这两天的研究中，有两个东西耗费了我比较多时间。一个是有时间区间的查询，比如说一段时间之内是第一个合同号，另外一个时间段是另外一个合同号。这就意味着你要查询某天对应的合同号，首先你得先知道某天是落在了哪个时间范围之内，然后在找到相应的合同号。有人在知乎上说这样的解题思路是先对那些一段时间内的合同号生成一段连续的日期，然后再对需要索引的时间以及合同号做日期以及客户的索引。这样的思路没有问题，但是这太劳民伤财了吧。如果需要被用作索引的合同号很多，那得浪费很多连续日期做索引。直接的解题思路应该是先以客户单因素做合并查询。查询的结果将是一批时间段和合同号，然后用新增列的方式再次定位需要索引出合同号的日期的时间段。筛选的结果将是一个列表，最后只需要把列表深化出来。需要注意的是深化出来的列表可能包含不只一个合同号，这样就需要做文本聚合。每次说到深化，PQ总会有两个可视化的选项，一个人是直接列出结果，另外一个是聚合，但是聚合的那个地方除非是数字，否则就只有计数，但实际上跟之前的分组一样，完全可以把文本用分隔符连接起来。

第二个让我耗的比较多时间的是怎么替换某一列的多个内容，而不需要先建立一个条件列，然后删除原列，最后再改条件列的名称为原列名称。这样的思路一点问题都没有，但是既然我可以在原列上修改，为什么我要这么折腾分三步呢？要说到这个就得好好研究用作替换的那个公式。玩好了替换的公式可以自己列的内容自己换、其它列的内容决定自己列怎么换，也可以换一遍又一遍嵌套着换。

上周折腾PQ主要是以实现功能为主，这一次除了实现功能以外，我也对代码进行了整理，比如最大程度简化代码，把步骤的名称变得更人性化等等。

两天的时间就把一天到晚都得做的事情直接规范化了，虽然这其中的某些细节还是无法做到，但起码那是极少数的特例，所以两天的努力，我感觉已经解脱了95%以上的日常工作。

我觉得自己已经迷上了PQ，沉迷于那种盘曲折叠的状态，一发不可收拾。我不知道为什么，如果要生成某个表格的话，我首先想到的是PQ，大概是因为出来的东西相对于PP来说PQ更像个表格，但是PQ又不是一个普通的表格。虽然到现在为止，我还没搞清楚到底限制PQ性能的到底是什么。还有另外一个让我很抓狂的是我在PQ的高级编辑器里面写了好多东西，除了进到里面复制粘贴以外，我还可以通过什么手段获取那些脚本呢？若某一天我换上了个低版本的office，可能用不PQ，所以我可能需要用Power Bi来代劳，那个时候如果得把脚本一点一点贴过去就实在太痛苦了，有没有一个脚本导出的方式呢？虽然实际上我问这个问题是因为其实我也没搞清楚如果我在某个Excel文件里面设置了宏，我该用什么方法把那个脚本复制出来。毕竟实际上Excel就是一个幌子，脚本才是最核心的部分，同样的疑惑，我在PP上也有。所以，微软到底是怎么想的呢？实际上无论是PQ、PP还是VBA，最终都是用某些脚本去控制Excel本身。把那些脚本在这个Excel可用，在另外的Excel也可以。我自己用VBA的习惯，就是喜欢把脚本都丢在某一个下文件里，那个东西里面什么都没有，只有脚本。当我打开这个文件，我再打开其它文件，我就可以在其它Excel文件上运行我想要的脚本。所以实际上那些脚本是些独立的东西，我甚至可以觉得，如果脚本编辑器能单独放出来，把文件拖进去以后就可以直接展示结果，有可能是直接把最终数据展示出来，也有可能是生成一个文件。对于小型的图表直接展示数据显然是最方便的，但是对一些比较长篇的东西，直接生成一个结果文件才是我们想要的，但是Excel没有把这个脚本控制器单独放出来。根据我自己的运用习惯，我觉得把PQ、PP跟VBA一起单独，变成一个就像Word、Excel、PowerPoint之类的独立 office套件完全说得过去。但显然，可能微软并不想这么干，因为一旦这样把高深的东西都暴露出来，很快就会被抄袭。因为实际上这些功能并不是office这三剑客独有的，其它编程软件也能实现。还有另外一点就是显然三剑客不是给普通人用的，因为对一些很新很新的手来说，Excel甚至不是用公式的，而是用来单元格加单元格加单元格获取结果的，还有就是把单元格设置成手动彩虹的颜色。如果那里用的是条件格式的色带我觉得很靠谱，但是某一天当我看到某人Excel设置成手动彩虹的时候，我彻底震惊了，他们直接把Excel变成了画板。

不是人人都会着迷于编程，但因为我的算力不好，我老会粗心大意，叫我一个一个单元格叠加，加这加着我就点漏了。所以我得让机器帮我做重复的工作，而我则躲在远远的控制端负责发号逻辑命令。