我的天

回到家后我依然控制不住自己，要打开Excel的VBA实现各种功能。

前几天说过，我想把一个一维数组输出到Excel的列单元格。发现即便我用了转置，依然无法实现，于是我硬是把那个一维数组变成二维数组，然后不需要转置就可以把那输出到列单元格。这样我就需要在赋值的时候多写一些字，输出到单元格那里时完全按照一维的大小去设定。后来我在某天尝试之中偶然发现普通的数组的确是可以用一个转置输出到列，教程也是这么说的，但是如果那个数组里面有一个空值，就会导致错误。系统就会显示出现“类型不匹配”。到底这个不匹配是个什么东西？我觉得如果这是python，如果这是在VSCode里，问题会被描述得很精确，实际上就是因为我输出的那个一维数组里面有空值。为什么二维数组里面有空值可以随意输出，但一维数组却不行？这是让我觉得非常无语，因为我就是对同样的数据用过一维数组跟二维数组输出，就内容来说，两个没有任何区别，但问题就是VBA这个奇怪的东西，当你把有空值的一维数组输出到列单元格就会出错。一维数组正常输出到行单元格的时候是不会出错的，而列的时候之所以出错，是因为输出到列必须在外层套一个转置。处理掉那个null对我来说很简单，因为我要输出到列的那个数组是从其它数组里赋值过去的，赋值的时候我多一条判断，如果那是空就直接送一对双引号，也就是让那个元素变成空字符串，而不是null就行。这样的话我就可以在一维数组外面套一个转置，然后输出到列单元格。对所有编程语言来说，null都是一个让人觉得很敏感的东西，很难说得清那到底是什么，所以几乎所有编程语言都有个isnull去判断那个东西是不是null。如果你要对某些字段进行运算，一旦在里面遇到null，你的汇总全部会失败。那种风格跟在Excel里面处理器空的单元格，有没有都无所谓完全不一样。

第一次我是在Power Query里遇到null，当时我就觉得很奇怪，为什么明明应该是有数的，但我连加之后汇总列却没有数？后来发现原来被连加的某些列是空的，这样就导致其它列即便有数，最后汇总也是空。所以我就经常做很无聊的事情，如果我要连加，我就先把那些列全部转为0，然后在整个操作结束以后，再把那些0全部转为空。这些看上去很无聊，多此一举的事情如果不干，根本得不到正确的结果。当然，如果我不是在PQ里可视化操作，我完全可以在汇总列那里用条件语句把之前连加的那些列全部都用公式先判断，然后再加。如果所有这些事情都是在高级编辑器里面完成，显然修改汇总列会比较简单，但如果不想敲键盘，直接选定那些可能被连加的列，然后设为0，最后再把那设为空，从鼠标和人的逻辑操作上来说是比较直观的。

我发现如果要在VBA要进行工作簿open的操作，界面会闪，但如果用的是ADO+SQL的查询，完全没有那种感觉，但实际上ADO+SQL是可以进行写入操作的，所以我到底能不能通过那个东西无感新建文件呢？

昨天说到，我遇到了码农的蓝调，吐槽了那么一番以后，我感觉好些了。之前觉得过不去的那些坎直接忽略掉一些，然后把重心放在另外一些上面，好像我又可以继续开展下去了。周四晚上没做运动，在办公室待到10点多才回宿舍。有单位作业的原因，也有我在纠结SQL的原因。埋怨不能解决问题，除非我不想用这个工具继续干下去，否则我就得想出对策。

常言道，退一步海阔天空。当我在那些点上没办法继续下去的时候，我往后退了一步，从整体上思考我为什么要做？我要做到一个什么效果？其实一开始的时候我并没有很具体的目标，有很多东西我想实现，而且我也知道我应该可以实现，所以杂乱无章的东西，应该从哪里开始？当我折腾了好几天以后我发现在已经做出来基本成熟的东西其实是雷同的。主体思路可以这么说是完全一样的，但是具体实施有一点点条件上的区别。它们的整体思路都是先设定一个日期范围，然后计算出期初库存、期间变化以及期末库存，最后把这三个东西拼起来，从整体上说就是这么简单。这其中主要区别是分组的条件。到底要分组多少个因素？在没有很仔细考虑这个问题的时候，你会觉得这些因素可以随意组合，但实际上把它们随意组合出来的那个效果到底你有没有其实不用的呢？还记得在我做这个之前我就研究过别人怎么把小计合计总计这种东西放在最后。结果原来是它们增加了一些排序的列。明细是1汇总之2，排序的时候先排这个，后面的再继续，这样就保证了汇总一定在最后。当时我不明白为什么那个人要把那个字段叫做排序A。当我自己实操过以后就明白到，因为排序估计是一个系统关键词不能直接用。之所以是A，因为非常有可能还会有B和C。如果你的那个表有小计合计和总计，就得有排序ABC。这样再组合其它的分组条件，你才能最终能让这三个汇总在它们应该有的地方，而不是乱糟糟的随意出现。当然了，之所以有三重的汇总，肯定是因为里面的条件列至少有三个。从技术上，的确能生成这样的表格，但实际上从使用角度考虑其实挺麻烦的，要一层一层选虽然关键词很明显，你把那些“计”选上了，那就是汇总的，你不选那些“计”全部都是明细。当我终于学会了这种明细汇总合并，学会了让它们正确排列以后，我反倒在纠结，乱糟糟一团东西真的很碍眼，当你要找自己想要的，反而得费点眼睛。当我有那个疑惑的时候，我上了个洗手间，蹲在坑上的时候我突然想到，如果我用VBA生成一个明细，然后我那些”计”就不用那么费劲。想生成就生成，不想生成就不用管他。要汇总还是要明细，任君选择。但我马上也明白到。因为现在我那个表格实际上是一个二维表，计算字段挺多，要把它们一个一个拉透视表值那里显然挺费手，而且一般的数据透视表没办法完成字符串拼接。最重要的是我花那么多时间，实际上只是通过一些参数去查询一个大表，最终我得根据查询出来的那些结果还去做数据透视表，我为什么不直接对数据源进行这个操作呢？想到这一点，你会觉得之前的努力好像全部都没有意义了。

然后我又马上明白到，要在一个普通数据透视表上面体现期初库存、变化数和期末库存（横向）是不可能。但是通过这种VBA的高度定制，我可以实现。所以我花时间做这种定制的目标就是能一目了然展示一些我经常想知道，但是以前我只能通过东拼西凑折腾一番才收集得到的信息。这样的好处除了方便以外，还有就是如果这种制作流程完全符合事实，我根本不用担心某一次自己手贱制造错误。要让这种费时费力的定制有意义，首先我得非常明确自己到底要的是什么，而不是一边做一边突然想到好像雷同的我也能做一做。

周四晚上退一步的时候，我还真想清楚了自己到底要的是什么。

周四的傍晚时分，我突然有点码农蓝调的感觉，因为好像无论我怎么整，前面总有无数的奇奇怪怪的问题。这些问题居然没有大路的答案。原因是Excel的SQL已经被阉割到一种没人能说得清的程度了。我就想知道到底Excel里的SQL有什么样的函数，知道有什么函数，知道函数怎用，才能以各种叠加的方式得出我的招数，但问题是人微软自己的手册都没有说清楚到底Excel里的SQL可以怎么个用法？相比之下，Access写清楚了，SQL Server也写清楚了，不同版本的函数不一样，些高版本能轻而易举函数就能实现的功能旧版本也有替代方法。但是Excel里的SQL像一个谜一样。你得不断尝试直到绝望。因为你拿着那个问题去搜索，没有结果，结果都是其它数据库的，虽然都叫做SQL，但差别真的很大。

的确用VBA+ADO+SQL搭配能解决一些小数据的问题，而且速度很快，但为什么微软在这个基础上还要继续整出 Power Query和Power Pivot，因为他们知道在操控数据方面，VBA本身真的有很多限制。当我死磕了一周以后，我发现VBA要死要活折腾半天出来的东西如果在PP里两下就搞定了，而且那还是在可视化的情况之下。至于PP，那是不允许你用不可行的方式去操控的，所以虽然三个都在考验逻辑，但是在Excel的SQL里面，我觉得对我最大的考验是，我明明知道要那么干，我明明知道用其它工具应该怎么干，但是无论如何我在这个Excel VBA里面就干不出来。

我遇到的某些问题，跟SQL没有关系，纯粹是VBA数组的问题。VBA的一元数组，如果要输出的话，它会在一行里输出，但如果你要把这个一维数组在列里面输出，你就得做个转置。我遇到的问题是，即便我已经设定了转制。系统依然说我的类型错误，最后我是怎么干的呢？明明我那个是一维数组就可以实现了，但为了可以顺畅输出，我硬是把那个东西设置为了二维数组，另外一维完全是空的。这样的话在我输出到单元格的时候只给予一列的空间也就是那空的第二维根本不用管他。经过SQL处理生成的记录集，如果要输出到数组，通常是一个二维数组，那个二维数组跟VBA自己的数组又是转置的关系的，那个记录集的数组编码是从0开始的，VBA默认的数组是从1开始的。如果在VBA里把一个字符串打断赋值给数组那又是从0开始的。在python里，默认就是从0开始，什么东西都从0开始了，所以你不需要为长度跟起始数值还有突然间又有个转置之类烦恼。

周四我遇到一个算是逻辑意外的事件。我要筛选某个表里某一字段不包含某个关键词的记录，但问题是那个字段里的东西有关键词也有空，我需要筛选出来的记录是关键词以外的其它字符以及空的。当我where 字段A not like ‘%关键词%’的时候，结果出乎我的意料。因为那个关键词是包含的关系，所以我没有办法精确控制，所以我必须在关键词的前后加上%。这句筛选的结果是字段里所有那个字段的记录都没有被筛选出来。不就是一个包含的关系吗？Excel的SQL里面允许用正则吗？最终我用的方式是在where里面用两句话，一个是not like，筛选到那个字段里没有关键词，但是有其它字符的记录，另外一个是用or的关系搭配一个isnull(字段A)，这到底是什么情况呢？如果在其他地方，一个contain之类的东西就能表达出来，如果允许用正着，正则也能很好表示不包含关键词，Excel的SQL到底允许我用什么工具呢？

SQL in Excel这把刀到底应该怎么玩？？？

周二中午去吃饭的时候，我突然意识到之前我用VBA+ADO+SQL做跨表查询的时候，把条件参数也当作是一个表。起码在PQ和PP方案里是这样的。在python方案里，我没有把条件参数作为第三个表，我只是直接把数据从Excel的单元格里读取，然后赋值给一个变量，在以后的各种比较之中用。 python的变量相对于VBA来说实在是太自由了，除非是列表元组字典之类要先定义，然后再用，其它都是拿起来就干，不管他什么格式，日期也好，字符串也好，数值也好，都无所谓。之前我之所以没有在VBA+ADO+SQL里把几个日期参数作为变量是因为我不知道如何在字符串形式的SQL里加入变量，正如一开始的时候，我不知道该如何让让下一个SQL用上一个SQL的结果，但现在我已经用得很顺畅了，整个流程下来，我要引用好多遍，有些是引用上一行的，有些是引用好久好久之前的，说白了就是双引号加&加变量加&再加双引号，等于是把变量以外的东西用双引号括起来。所以既然上一个SQL查询可以这么用，为什么我的那些固定参数就不可以这么用呢。显然在VBA里，要把某个工作簿某个工作表的某个单元格数据变成变量实在太简单了。唯一的问题可能是Excel读取那个单元格的数据，那个数据的格式可能跟我料想的不太一样。比如有时Excel觉得那是一个字符串，但有时Excel又觉得那是一个日期，明明某个数据就是一个整数，但是Excel识别出来经过SQL之后直接生成的那个，Excel觉得那个东西是日期。我不知道Excel到底是怎么想的，但是Excel的单元格毕竟不是一个数据库，不像PQ那样，进到PQ里面，如果你得在某些列进行运算处理的话，你就得定义它为你的目标格式而不能让系统默认成为它们觉得的样子。

吃过午饭后我就赶紧去验证我的想法，结果如我所料，那些变量的确能直接通过拼接的方式加到 SQL的查询字符串里，但是拼接的方式又有点出乎我的意料。比如单元格数据读取能识别到那是一个日期，那个变量在本地窗口显示，那就是一个日期，因为那个数据左右是有#的，但问题是当我要把那个数据跟SQL查询里从表获取的其它数据比较的时候怎么比怎么不对劲。之前当我把那个条件参数作为一个表跟其它表的某些字段比较的时候，两个字段我都加了CDate这个公式，也就是我强行把他们可能觉得是字符串的东西转成日期，然后对比。把日期变量放到SQL字符串里连接符都对了，但是就是得不到我想要的结果。在调试过程中我看了一下系统识别到的那个字符串，结果发现虽然那个明明是日期，但是连接上去以后居然就变成了一个不知道该如何称呼的东西。不能说是数值，不是日期也不是字符串，所以我在双引号的之前之后又加了#，让这个四不像的日期在系统的解释之后成为真正的日期，然后再跟其它表的日期字段比较。除了日期变量以外，我的参数还有字符串。字符串变量情况跟日期很相似，所以我做的是在双引号的前后加上单引号。这样的话，经过系统的解释这就是一个一本正经的字符串了。为什么我不在读取单元格数据的时候直接在那个变量前后加#或者单引号呢？因为我发现这般操作跟在写SQL的时候再加#或者单引号相比前者耗时更长。这其中的原理我不懂。

最终，当我的跨表查询从三表变成两表，第三个表的那些内容改成直接赋值以后，整个程序的运行时间马上提升了0.3秒（效率提高30%）。这是一个了了不起的成绩。对老手来说，理论上一开始就应该这么干，我一开始也曾经试过，但当时我实在不知道该如何把字符串拼接上去。

VBA是个很成熟的东西，SQL也是一个很成熟的东西，但是当要在Excel的VBA里玩SQL的时候，可直接借鉴的经验真的少之又少，但这种摸着石头过河的感觉挺好，虽然已经让我到达了某种废寝忘食的程度了。

见识过VBA+ADO+SQL的威力以后，我马上觉得估计后好长时间我都会用这个了，虽然相对于python来说真的各种不顺手，但是出来的那种效果和效率实在让人着迷，尤其是如果我需要关联更多表格。

VBA跟python相比，我觉得最明显的区别在于打开一个VBA查询Excel文件，我就可以直接在那里设定参数，然后点一个按钮就可以查询，但如果那是python，我还得先打开Excel文件设定数据，关闭文件，然后运行python。当然，实际上在VBA之前我也有考虑过把参数设置这一条在python里解决。这就意味着我得先设定一些预制参数，然后进行一些判断循环，接着把那个参数固定下来，最后继续进行之前我已经在做的事情。说白了就是把参数设定在文件里面的那些东西变成纯粹通过python输入框完成。这实际上也没有什么很大的问题。

python参数录入到底要依据一个怎么样的程序呢？还有就是我怎么判断输入的那些东西的格式一定符合我参数的要求。首先我想的是应该怎么输入，接下来是可能输入的时候有手误。我想到了以前手机话费充值的时候会如果是什么业务就按什么数字，按完这些数字键以后，再按什么井号键结束。我可以把需要录入的参数分别设定不同的数字。每次输入完毕以后就把所有参数打印出来，然后再次询问到底是要继续修改参数还是结束。如果继续修改参数，就继续按某个某些数字，如果结束就按某些特殊的字符。就逻辑来说，如果一次只修改一个参数的话，出错的概率会低一点，但如果说比较省事，理论上应该可以允许批量输入，参数与参数之间以某些约定的符号分开，但是如果我是批量输入参数的话，我怎么确定到底对应的是什么参数呢？还有一个就是参数之间的分隔符也是一个关注的点。中文输入法和英文输入法默认的全角和半角符号是不一样的。要输入者标准地输入那些分隔符，显然也是一个不太容易的事情。如果出错的几率很高，那么这个输入就是一个非常寂寞的行为了。折腾这么一大轮，好处就是在运行拜访之前，不需要先打开Excel文件输入，但是python的这个参数输入相对于直接在Excel文件里面输入又非常麻烦。所以这个先不打开文件，到底是简化程序了还是纯粹是在用python秀肌肉呢？这个秀肌肉我也说不准到底是肌肉还是骨头。简单来说就是把简单的事情复杂化了。当然，如果python能实现一个直接弹出一个参数窗口，你要修改你就修改，不修改就保留原值，这个就比较合理，但好像暂时为止我都没听说过可以这么干。

从编程者的角度考虑，现在已经能实现功能，但是从使用者的角度考虑，或许我还可以更进一步。