我的天

星期天的晚上，我觉得自己睡了个寂寞。本来就很晚才去睡觉，再加上睡觉之前我正在想某些很烧脑的事情，所以现在躺在床上，我觉得整个晚上我的脑子里都是那些烧脑的玩意。在迷迷糊糊之中睡觉，我也说不准自己到底是清醒的还是睡着的。在睡梦之中我还在为那个睡觉之前折腾的问题烦恼，但醒了以后，我实在不记得睡梦之中我做了什么方案，所以说在其实在睡觉之前真的不应该努力思考，那样的话不仅仅会很久都睡不着，睡着了以后也会很挣扎。但我读书的时候不会这样，话说回来，读书的时候我就从来没有为某些问题主动这么努力过。如果是老师出的某些习题，通常都会有标准的答案。那个时候我们都不会自找麻烦，故意给自己制造难题，又或者说即便在那个时候我们找到了什么问题，你自己解决不了的，我也会主动去找同学或者直接去找老师。退一步说，我们找的那些答案只要到那是某些习题本上或者卷子上的题目，那些东西绝大多数情况之下都有标准答案，所以找到那个标准答案就好了，有可能是配套的答案也有可能是某些教辅的书籍里面有相关类似的解题说明。

现在我做的那些事情，我感觉没有一个标准答案。不同的人会用不同的方式去实现。不仅仅是实现的工具不一样，即便是一致的工具，也会有不同的实现思路。就我自己的实际情况而言，即便只是我一个人，可能今天跟明天的想法也会有区别，即便我把某个VBA脚本写得我自己满意，但说不准过上一段时间我又会有新的想法，觉得某些地方可以做某些改进。之所以这样，是因为首先我还一直在思考。其次，之所以一开始想得不周全，是因为我根本不知道周全应该是怎么样的，很多方法是参照过来的，可能在其它的编程里面是那么个用法，但什么才是最适合在VBA里实现的，又是另外一回事。比如其实VBA通过api是可以调用Excel自己前端函数，但那一定不是VBA最高效的方式。在VBA里，只要你玩好了判断和循环，对高手来说，就能解决几乎所有的问题。数组才是VBA的核心，但是数组不仅仅是一回事。可能是我一开始学VBA的时候并没有意识到原来数组是那么的复杂。因为在我印象之中，C语言的数组不是这样的。

VBA里的数组千变万化，虽然都用数组去命名，但实际定义赋值使用等等完全不一样，又或者是说它们的确有共性，但是它们的特性会让你觉得它们大概都是一回事的人无数次掉坑里，我就是这样的人。当我掉坑里很多次以后，我才发现原来它们是有很多特性的。

要清楚了解我的工具是做什么的。可以怎么用，然后我才能在用的时候得心应手。什么场合应该用什么样的数组，得到了数组以后，我又可以对那进行什么样的改造都是我应该烂熟于心的。

大概一周之前，我跟网友谈起我在VBA里遇到的烦恼的时候，我的网友说了一句，可能你连怎么VBA里调试都不知道。其实调试我是会的，因为如果我完全不懂调试的话，搞循环就是在数组瞎掰，显然除了碰壁就是碰壁。但是我真的没有系统的学习过在VBA里怎么高效调试。既然知道自己弱在哪里，我就得把这个补回来。现在我的调试要比之前高效那么一点点了。

在AI流行的现在，我还靠我自己写VBA，写那个超级八股又非常奇怪的编程语言，在别人看来非常反人类反潮流，但是当你从不大认识到比较熟悉一种编程语言，当你能得心应手轻而易举实现之前你想做到，但是却无法做到的事情的时候，那种成就感是无法言语的。

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

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

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

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

周二中午去吃饭的时候，我突然意识到之前我用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的跨表查询，接下来我还得在那里逗留多长时间还得有多少bug需要改进说不准，得一边用一边发现问题。做的时候觉得没问题，用着用着就会发现各种问题。比如第一次发现的bug是明明那个ID理论上就只会有一条记录，因为所有和那连接的东西都是左外的，但问题是我这个合并的新手犯了个很无知的错误，在进行左外之前虽然我也已经做了分组，但是除了那唯一的ID以外，我把其它信息也一并分组了，虽然合并的时候指定的是ID，但实际上合并上去的其它性质不一样，即便分组了，合并的数据还是会产生很多行。最后出来的结果就是虽然其它性质的东西并没有体现在最终的合并表上，但是它们却以隐身的方式体现了出来，因为它们的存在，让那唯一的ID出现了好几行。把右边那个表的数累加起来，数据是正确的，但左边的那个ID一样的数据就重复了好几遍，结果只要是左边和右边交互生成的数据全部都乱套了。最终的解决办法很简单，就是在合并之前做最后的分组的时候除了合并的那个关键字以及已经分组聚合的数据，其它东西一律不要存在。

VBA方案中招了，python方案也中招了，但是PP的方案没中招，这在我意料之中，但是PQ的方案居然也没中招，这个是我无法理解的。为什么同样的思路同样的合并方式，PQ就不中招呢？还是说之所以PQ不中招，是因为第一次发现的时候我已经改过来了，只是后来我又忘记了？

周一的早上，当我在上报纸质数据之前做最后核对的时候，又发现了一个bug。汇总数量没有错，但是根据汇总数量生成的百分比不对。拿个计算器按一下就发现不对头，跟其它方式生成的数据相对比也不对头。百分比第二位小数都为0，引起了我的注意。因为正确的结果应该是不都为0。接着我马上想到了自己对其它不是百分比的数据全部都进行了四舍五入到三位小数，但是进度我也四舍五入到了三位小数，但实际上进度是有两位小数的百分数，也就是需要保留的是4位小数。我把最后一位抹掉了，当然出来的结果跟理论上的那个会有差别，而之所以在其他方式没有犯这个错误，是因为其它方式的计算过程中，我没有对进度这个字段进行四舍五入控制。所以解决方法也很简单，我把四舍五入的位数从三位改为四位，又或者是直接取消四舍五入就完了。
VBA是个奇怪的存在，因为实际上它所用的四舍五入并不是经典的四舍五入，而是科学领域常用的四舍六入五成双。我在大一学基础化学的时候第一次听说这个，那时真的让我每次都想半天才算得清这个东西。我毕业以后，去某个单位实习，做完某个检验需要计算结果，那个结果也需要使用四舍六入五成双，每次遇到那种数据我都得慢慢想清楚。VBA这个四舍六入五成双跟一般的Excel算出来的四舍五入效果不一样。这就会导致用脚本生成的数据可能会跟人肉算出来的有差别。程序员们只能用自己的方式重新把一般四舍五入的效果模拟出来，不得不放弃VBA原生就有的四舍五入函数round。我运气好，还没遇到这种问题，而之所以没有这种问题，是因为除了进行乘除法以外，加减法的时候，我是不会遇到小数点三位以上的数据的，所以起码能保证通过加减法出来的东西都不会有这种问题导致的误差。至于乘除法出来的数据，实际上我也只是看一下而已，比如那个进度，上报的进度数据我会在手工纸质的表上用一般的Excel公式生成。

越研究就会越觉得自己无知，那些实际过程中积累的经验比直接从别人那里一二三四学回来的深刻多了。

第一次加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秒，我为什么要做这个拆分呢？

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