我的天

周二中午去吃饭的时候，我突然意识到之前我用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的威力以后，我马上觉得估计后好长时间我都会用这个了，虽然相对于python来说真的各种不顺手，但是出来的那种效果和效率实在让人着迷，尤其是如果我需要关联更多表格。

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

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

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

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

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

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