我的天

我已经不记得自己是如何走上这条写代码的不归路的了，确切地说我已经不记得具体我是哪一天开始研究这个跨表查询的问题。我大概知道顺序是怎么样的，但是具体是哪一天开始我实在已经说不清了。现在我天天都在写，天天都在写VBA、天天都在纠结VBA里面的SQL。我已经不记得这种状态已经持续了多长时间，反正即便是吃饭睡觉，我的脑子里依然想着那个东西。每天就是起床吃早餐然后完成必须做的工作，接着以最快的速度完成打卡，然后一整天都在那里写代码，不管上班还是下班。在这种状态之下上班，那8个小时突然变得好短。因为已经沉迷其中，所以到了下班时间经常停不下来，吃饭时间因此一再拖延。如果不是去饭堂吃，而是自己吃麦片，那就更加是一杯麦片，我根本不知道自己是如何勺到口里的，反正那杯东西我可能得吃一个小时以上。当你完全着迷于做某事的时候，你根本感觉不到其它东西的存在，比如身体的各种反应，比如某些疼痛又或者是饥饿等等。因为我知道我一旦着迷就完全没办法顾及其它事情，所以当我还没迷进去的时候，我就得先把其它必须做的做完了，否则是完全不能自拔的。

之所以出现有现在这个状况，是因为自从我实现了第一个跨表查询的任务以后，我发现一直以来我的很多东西都可以用VBA+ADO+SQL把它们联系起来。那些以前我有数据，但需要这样那样粘贴的玩意现在我可以把它们统一起来通过查询的方式直接获取结果。这样的好处是成功开发以后很省事，但是坏处是这样非常定制化，所以基本没有什么动态可调整的余地，即便设定了很多条件当你想加更多的时候无能为力。还有一个让人觉得挺累的，就是如果我做的是数据透视表，汇总那些事情不是我干的，是系统干的，我管好明细就好，我要什么明细我就搞到什么程度，但如果我得在一个查询结果里面把汇总考虑进去，无论是列还是行，我都得想办法把实现。相对于数据透视表来说，我觉得这种汇总是硬汇总，是设计的人必须得想好的，而且一旦变换某些条件，又得伤筋动骨调试一番。对用户来说，对只看到最终结果的那个人来说，可能会觉得很爽，如果他做过之前那些这样那样的复杂操作，他更加会觉得这实在是太爽了，但如果某个人之前没有经历过那些，他只是直接用这个捷径，从他的角度考虑可能他想知道更多，但是现在已经固定好的方式没办法满足他的要求。所以我还是觉得如果数据是规范的，用Power Pivot的方式把连动起来更靠谱。

PP非常的稳定，尤其是相对于PQ来说，但问题是无论是PP还是PQ，那个写代码的地方都非常不友好。正常的窗口操作基本不会让你挂掉，但是写代码的时候非常有可能直接让你卡死，必须得关掉重来，但是之前写的东西就全部废掉了。现在我之所以会在那里玩VBA，因为实际上我玩的不仅仅是VBA本身，而是VBA延伸出去的SQL。本质上，我在进行类似PQ或者PP的操作，虽然公式少了很多，隐藏技能也就是我至今无法猜透的更多，但就运行效率来说，尤其是对小数据的运行效率来说，VBA+ADO+SQL远远优于PQ和PP。我这里说的小数据是单表不超过1万条，最宽的表不超过30列。

可能，如果我不是用Excel去玩PP和PQ，而是用Power BI去玩，效果会很不一样。

昨天说到库存查询，最后的展示方式是透视表，透视这个东西只要前面做对了，就可以实现，那就只是最后一个步骤而已，但是如何得到透视需要的所有东西呢？在做这个库存查询之前，如果我需要得到某数据在连续日期中的汇总，我会先把那些数据按日期分组，然后选定日期表里面其中一段连续日期，接着用左外的方式连接日期表和数据。但问题是在库存查询中，我最后的结果是透视的，这就意味着被透视的那些字段在没有被透视之前，是各自对应一段一样的日期表，如果最后透视出来的字段有5个，就意味着我有5段一样的日期表，我该怎么表达这个东西呢？

在解决这个多段一模一样的连续日期之前，我首先攻克的是累计数。在SQL里面实现累计数有好几种方式，但问题是不是每一个都适合在Excel的VBA里实现，比如窗口函数over在Excel里面就是不支持的，虽然窗口函数的效率是最高的。对同一个表进行子查询可以实现累计，但问题是这样生成的累计无法用在下一步的透视里使用，接着我在另外一个方案里面看到了笛卡尔积的方法，笛卡尔积得出来的结果跟子查询完全一样，但笛卡尔积出来的结果可以用在下一步的透视里，而且同样的数据，笛卡尔积的运行效率比子查询高（可能是我测试的数据少？）。这个累计数的问题，我在Power Pivot里也研究过。我觉得PP的解决方案跟子查询有点类似。在计算累计数的时候，PP的效率很高。在用PP实现累计数之前，我曾经用Power Query实现累计数。我的PQ解决办法是先生成变化数，然后按日期把变化数累加起来。接下来用日期表跟这个数据左外连接，如果不是天天都有变化数，那么这个合并后的累计肯定会有空行，这个时候再用向下填充的方式补全。用这样的方法在PQ里的确是可以计算出每天的库存，但是效率非常低，所以在那以后，当我要做累计库存，我会直接放弃PQ选择PP。现在我已经掌握了在VBA里用SQL的方法实现。在筛选的日期不多，以及最后被透视出来的字段不多的情况下，效率挺高。比如最终我只需要展示一个月的数据。运行时间通常不会超过0.5秒。我个人觉得1秒是一个分水岭，如果1秒以上才出结果的话，我觉得这需要等待，尤其是运行时间超过2秒，我觉得那得优化了。低于0.5秒的运行时间对我来说几乎是无感的，我可以接受这种方案。

在累计数可以实现之后我要继续研究怎么按照条件需求把日期表捆绑上去。折腾了好长一段时间，都是没什么结果，于是我就去吃午饭了，在去吃午饭的路上，我突然想到，要把这个日期表扩充开来，实际上我不就是要做一个按条件笛卡尔积吗？所以我需要进行左外连接左边的部分，在这个情况下就不是一个普通的日期表，而是被透视项和日期表进行笛卡尔积的东西。笛卡尔积这个东西，如果数据很多会是一个噩梦，甚至会让电脑崩溃，所以所有教程都会告诉你，如果你要的不是这种东西，尽量不要做这种操作。但思前想后，我发现我正是需要这种东西。吃过午饭后，我赶紧去测试我的想法，果然，用笛卡尔积的结果再加后续的操作，我就能生成我需要的东西，并最终能以透视的方式展示出来。

我感觉现在当我把一些最基础的东西用熟练了以后，渐渐地我体会到了一些其实你明明知道，但是你却完全没有料到可以这么用的方法。

既然人可以通过某些逻辑得到某些结果，那么我应该可以按照这些逻辑生成一些自动化的方法，在这个时候，我最讨厌例外情况。

花了两天时间，又做了另外一个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变成了画板。

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

花了一整天的时间去做python版本的发跨表查询，最终还真的被我做到了，唯一有点小瑕疵的就是出入进度里我还没有实现批次小计。我基本已经想好需要一个什么样的数据处理，但是怎么把那些东西结合起来我还没有一个成熟的思路，估计会有一些类似的案例，我可以参考一下。

之所以这个脚本居然用了一整天，是因为最后我算了一下字数，居然有4000多字。因为我用的是VSCode，所以实际上敲代码已经很高效了，因为几乎所有符号都会配对出现，一旦有手误的地方基本上都会提醒，但是有些东西是VSCode不会提醒我的，比如我要用到pandas各种格式的数据，有些事不能直接用print打印出来的，必须要用个循环才能把那些东西展示出来。如果我用python处理Excel，我会用xlwings进去，然后用pandas。之所以用xlwings，因为这个东西支持Excel的版本比较多，老一点的插件有的只支持xlsx有些不支持xlsb，有些仅仅支持xls，而我选择的这个xlwings基本上我用的Excel格式都支持了。现在我主要用xlsx和xlsb。因为做这个python查询之前，我已经在PQ和PP上做了两遍，所以我完全知道自己要做些什么，但具体那些东西该如何实现，应该用什么样的表达式去实现，是我花时间的地方。

昨天之前我对pandas里面的dataframe并不太熟悉。我知道那个结构很类似于Excel的表格，但实际上用起来又跟表格不太一样。我觉得那个东西最好的地方就是不像PQ那样，经常被高手套叠起来，于是你想半天都搞不懂那到底是什么。作为一个不是python新手的人，我还是喜欢用短语句完成，然后再连接起来。自学python让我觉得最成功的地方在于我喜欢用for循环，而且还非常习惯使用套叠循环。如果在我大二学C语言的时候，我也能把循环玩得这么溜。估计我就不会为冒泡法这种东西发愁了。

虽然花了一整天时间，才终于把这个东西整出来，但我个人感觉还是比较愉快的，尤其是敲代码的过程，因为相对于在Microsoft 365的PQ或PP的编辑器里面敲代码，VSCode里敲代码实在太爽了。PP会经常提醒我公式错误，因为PP那个你看不到的循环实在会把你整得很惨。PQ里我不过是删了个文字，不知道为什么它会自动给我加半边括号，所以搞了半天，我都搞不懂自己到底哪里错了，最后发现不知道为什么，多了个括号，又或者在调整某些语句的时候多了或少了个逗号。英文和中文逗号在编程的世界里是有区别的，在VSCode里可以看得很清楚，但在PQ的高级编辑器里。因为字体太小，你还没办法快捷变大，非常折磨我这种眼睛不太好的老人家。

高中的时候我的数学老师经常一节课就只讲一道题，他会用很多方法把那道题解出来。现在，我也喜欢上了这种别人可能非常难理解的行为。