我的天

每次都定下一个小目标，然后去实现。结果发现一天多一点的时间居然就能搞定一个问题，这种进度有点出乎我意料，因为之前的那些问题让我挣扎了好长一段时间，起码有两三天，之所以后来进度加快了，大概是因为我明了了我要做什么，但是尽管是这样，还是会遇到很多奇奇怪怪的问题。

这周初我解决的是批量生成月度核对表。我把这个任务分成两个步骤，一个是查询到底要生成多少，接着就是把查询到的结果生成文件，但是那个结果跟文件又不一定是完全对应的，根据不同条件可能10条查询结果最终会生成9个表。因为实际上某些条件是需要合并才能得到我想要的文件。在怎么设定条件，如何进行循环方面，我纠结了好长时间，几乎可以这么说，上个周末我一直在做各种尝试，为的就是最终实现这个目标。

在完成了批量生成月报以后，接下来我要做的是生成某个仓的分仓台账。相对于之前的月度核对表，这个单仓台账相对而言条件是固定的，而且必定只生成一个文件。同样我首先做的也是做一个查询，查询一下这个仓到底有多少条记录是可以生成分仓台账的，我又要生成具体哪一条。有些是无法自动实现的，因为实际情况是某些仓某些筛选条件是不一样的，但实际上应该反映在同一个分仓台账里。这个时候就需要手动合并一下条件。这种例外的事件不确定会在什么时候发生，所以必须给手动留有余地。之所以分步骤，其实一个很重要的原因是其实有时并不是为了生成分仓台账，只是要查询一下这个仓的情况。批量生成月度核对表，我花了好几天的时间，但是生成分仓台账，我只花了一天不到。

接着，我研究的是库存查询。在不同的条件之下进行库存查询，最后的结果是以一个透视表的方式展现出来，根据不同的查询条件透视的项目不一样。虽然我想到透视的项目是不一样的，但实际上在我研究的过程中，我先在单一的条件上做尝试，当单一的条件生成的数据没有问题以后再把它扩充到动态条件。因为有了之前月度核对表的锻炼，所以动态条件该怎么做我是有点底的。

SQL最基本的查询语句基本上我已经比较熟悉，这一次库存查询最后一步需要做一个透视。透视这个东西是我之前没有尝试过的，虽然我是Excel数据透视表的超级粉丝，但是在SQL里面控制这个东西，我还是很不在行的。所以到底什么条件可以控制，可以控制到什么程度我是不知道的。教程通常都只是最简单的那些，用上面的数据你重复100遍都不会出什么幺蛾子，但是在实际情况下你会有更多需求。比如当我要控制被透视列的排序的时候发现好像在Excel的SQL里无法做到。即便我在透视之前那一步已经排好序了，但是透视的时候依然是我行我素。让我比较挣扎的是，在透视之前我已经通过分组合并计算出被透视的列的合计数了，但是透视之后合计混在了那一堆被透视的字段中间。最后我已经想到不计算合计，在SQL里面生成透视以后输出到数组，我在数组里面做合计。就在我几乎要放弃的时候，原来合计可以通过在透视的select里用一个聚合函数实现，这样的话透视之后的表格就是先是条件列，然后是合计，接着是那些被透视的列。虽然合计不是放在我想要的最后面，但起码放在了最前面。

库存查询研究过程中让我纠结的问题是什么，明天继续。

实现功能，我觉得只是最基本的要求。接下来我会考虑到底有什么是可以优化的，比如如果我做了很多个判断，我能不能少做一些，把那些判断合并起来或者里面我做了不少循环，循环通常是为了赋值，赋值完以后我有没有立即就把那个数组或者字典之类的释放。我不知道那些东西释放跟不释放效率会不会相差很多，但可以肯定的是，如果我在一个脚本里同一个数组或者字典的名字被重复用多次，而在使用之前我又没有重新定义，在上一个数组或者字典使用完毕之后，把它清除干净会让我安心很多。

一开始要实现某个功能的时候，我都是写完一大堆，然后发现另外一个功能可能就是在那一大堆里面改一点点，然后就可以实现了，我就会把那一大堆复制粘贴，然后改一点点，发现的确是可以的，但是当这个脚本真的要放出来的时候，我就得合并同类项，能不能把这两个东西通过以某个判断把它们合并在一起呢？如果那两段话的差别就只是某个条件判断不一样，我能不能把那个条件判断放在前面，然后把判断结果赋值给一个变量接着后面就可以在同一段话里面引用那个变量了。这样的话从脚本的体积来说可以缩短很多，起码可以减肥一半，甚至更多。

一直以来我都想不通，如果我要从一个工作簿里复制某个工作表到另外一个新的工作簿。那么我是应该把整个工作表复制过去呢，还是说在新的那个工作簿里面新建一个工作表，然后把原来那个的内容复制到那边。在生成这篇blog的时候，其实我依然没有做过这种尝试，到底哪一个的效率会高一点呢？我选择的是直接把工作表复制过去。当我全部复制完毕以后，因为每个工作簿新建的时候都会有一个默认的工作表，我最后的步骤就是把默认的那个工作表删掉。但是让我想不明白的是，如果我不是在VBA里操作，而是直接操作Excel，我在某个已经打开的工作簿的工作表的表名那里点右键之后选择在新的工作簿里面建立这个工作表的副本，那么新的工作簿里是不会有默认空白工作表的。这是不是证明，如果我在新建工作簿里面设定某些参数可以不让那个默认空白的工作表生成呢？因为如果这样的话，我就不需要在后面再做一个删除的操作了。从数据上说，删不删除都无所谓，因为那个是空白的，但是对完美主义者来说，总觉得有那么一个空表实在很碍眼。

当我终于实现了批量生成文件以后，我发现批量生成文件的速度很慢。慢到大概每个表都需要一秒钟，如果要批量生成9个表就得9秒钟甚至更多。在考虑如何缩短时间的时候，我马上想到我把那些批量生成的表全部都放在一个工作簿里，事情就变成了批量生成一个工作簿里面的N个工作表，而不是生成N个工作簿。接着我发现这样的确能提高一点速度，如果要生成9个表的话，时间会从大概9秒钟下降到6秒钟，但是我觉得还是不行。于是我就开始研究我这个生成的过程到底有没有什么地方可以改进。因为之前我的策略是生成9个工作簿，所以我肯定是从原始的文件里先把自带格式的工作表复制到新的工作簿，然后再把新的数据粘贴到新的工作簿，然后保存。同样的思路，用在一个工作簿的N个工作表里，效率很低。所以接下来我做的就是新建一个工作簿，然后把带有格式的工作表复制过去。接着在新的工作簿里面复制里面带有格式的工作表，改名，接着在新的工作表里赋值。这样最大的区别就在于在多次复制工作表的时候，我不再需要跨工作簿了。这么一个不跨文件的操作，直接让批量生成的时间从6秒下降到2秒，效果非常明显。我觉得两秒也几乎基本上是个极限了，因为即便我用最简单的循环，基本上不在工作表里面录入什么数据也需要接近2秒的时间，现在我粘贴了那么多数据，还有就是生成这些数据之前还进行了N步操作，整个过程下来不到三秒，我觉得完全可以接受。

追求更快更好是从来没有尽头的。

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

现在我做的那些事情，我感觉没有一个标准答案。不同的人会用不同的方式去实现。不仅仅是实现的工具不一样，即便是一致的工具，也会有不同的实现思路。就我自己的实际情况而言，即便只是我一个人，可能今天跟明天的想法也会有区别，即便我把某个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的时候，可直接借鉴的经验真的少之又少，但这种摸着石头过河的感觉挺好，虽然已经让我到达了某种废寝忘食的程度了。