我的天

花了一个早上的时间写了两个Power Query的方法，主要是用于转换1~4层的标签和第4层对应的具体内容。其实如果有大表，我就是把那个大表分成两片，第1片用方法一处理，第2片用方法二处理，方法一跟方法二重叠的部分就是第4层的标签。

方法一，实际上我是把同一个步骤重复了三遍，分别是取第1第2层，第2第3层和第3第4层。这三个步骤分别对应的就是分类1~3。分类3所包含的内容实际上就是第4层的标签。在研究怎么整这个东西的时候，我只是做了第1第2层，后面那两个重复，我直接复制后修改里面的某些数字，就可以把东西重新定位，然后生成后面两个重复步骤的结果。三个步骤的结果出来了以后合并在一起，就可以直接加工出我想要的json格式。至于方法二，我感觉比方法一还要简单一些，因为实际上就只是做一个步骤而已，但是方法二有一个做超链接的过程，属于有超链接就做，没超链接就不处理。最后json的内容就是把方法一跟方法二的结果全部融在一起，最后一行手动删除一个逗号。

做出这两个PQ方案以后，可以让完全小白的人直接生成json，把相应条目复制到目标json文件，网页接着刷新就可以了。刷新这里可能会遇到浏览器缓存的问题，但这是后话了。PQ方案需要对电脑有要求，准确来说对office的版本有要求， Office 2016以下的可能会有点问题，即便是Office2016专业版也有可能出现某些状况，但我不确定状况一定会发生，因为我很少用那个版本office。虽然可能我一开始接触的Office365是基于Office2016的，但经过这么些年的迭代更新，我不知道现二者在Power Query上有什么差异性，在核心功能上会不会有一些变动。但是这个操作只需要那个处理网页数据的人做一次就可以了，其他人完全不需要涉及，所以即便对office有要求，那么在可以行得通的电脑上操作也就没有问题。主要是Excel数据转json格式的时候需要PQ支持。

Power Query的处理上，我主要在不新增列的情况下直接修改某一列的数据花费时间比较多，比如说在原有的数据上加上一对双引号。如果我要加的不是双引号而是其它乱七八糟的东西，可能我根本不会碰钉子，但因为双引号在PQ里是一个比较特殊的存在，准确来说在所有编程语言里，双引号都是很特别的存在。所以当你要自定义一列，在原来列数据的基础上加上双引号，那么实际上，你在写脚本的时候就得打4个双引号。有些时候你得用4个双引号，有些时候你得用3个双引号，我不知道为什么PQ就是不能让我用反斜杠，如果允许反斜杠的话，我就不会被双引号搞得非常晕了。把那些东西转化为json格式的时候，我必须添加大量的双引号。那个步骤虽然我已经很小心翼翼，但是也不免经常会有各种手贱的操作。另外一个让我手贱的原因是PQ的编辑器不知道为什么会自动给我添加双引号，有可能会给我添加双引号，有可能会给我添加半边括号，反正就是我不想它给我增加的，它老是很自觉不定时增加，于是到最后我不知道为什么出错了，结果发现原来是它给我增加了我不想要的东西。

最终，我花了一个上午实现了我的计划，感觉挺爽的。

花了大概一天的时间整理出一个用来算库存价值的东西，这里我没有使用VBA，是因为我需要一个更稳的方式。之所以不用VBA，因为已经不需要跨文件加入数据了，所有东西都将在一个文件里解决，而且相对于我得用VBA来干掉的那些，这里的数据相对来说很少，所以这一次我用的是Power Query。我有考虑过要不要用Power Pivot，但最终可能我的数据要以普通表格或者是数据透视表的方式表现出来，通过查询生成的东西最终可能要粘贴到经典的纸质版二维表里，数据透视表在这个情况下就不怎么适合复制粘贴，尤其是当我的数据透视表选项里有合并居中的设定。

在这个做这个的过程中，我有考虑过用Excel自带的公式，但无论是经典的lookup还是新函数xlookup效率都太低了，我不知道是我的电脑太渣，还是的确就那么回事。如果用PQ，在一个低端的Excel里，的确可能效果是很糟糕的，但如果我已经把刷新好的数据发给别人，别人即便刷新不出来，数据也都能看到，不影响，但如果用的是高级的公式，可能那里就一团糟了。还记得多年以前，单位有异地储备玉米，对方把到达码头和已经装船发货地数据发给我，用了sumifs，那个时候我用的office是2003的，那个公式我根本没办法使用，全部显示的都是一团糟，所以我不得不为了打开那个文件看到里面的数据又在电脑上装了个WPS。那次之后，我才努力的尝试用office 2016，之所以会跳过2013，是因为2013在数据透视表方面有无可救药的bug。如果是office 2010，高级公式依然打不开，所以现在当我要实现某个功能的时候，我要考虑什么东西会高效一点，什么东西兼容性好一点。VBA的兼容性很好，但是不是人人都敢打开宏文件。因为在以前，宏文件通常都意味着有木马之类的东西。同时，我设定了宏万一某些时候有问题，别人就会只会弹出错误，的不到结果，也会让人很紧张。

这一次我做的文件，可能后面见到的人会很多，他们可能会用不同的电脑，可能是win10，也可能是win11，有可能是office 2021、2019，又或者是Microsoft 365，也有可能是WPS，到底的WPS里面能不能正常打开并使用PQ我不知道，我估计是不行的，但是能不能看到数据呢？我觉得应该可以，但是无法通过修改某些条件刷新出新的东西。

微软的AI据说很厉害，但关键是在中国和俄罗斯用不了，所以那些都是扯淡。前段时间说Excel通过安装插件可以使用Python，但是那个Python处理是需要把数据送到远端的服务器再传送回来的，我感觉最终会跟微软AI的命运差不多。现在的Power Query相对于我第一次在office 2016里看到的那个已经成熟了很多。还记得我是第一次在自己的笔记本电脑 office 2016家庭版里见到的PQ，那个时候那就是个四不像，中文英文各有一点，翻译都不全。有些功能也不知道是我用得不对还是怎么样，反正就会卡住。对照一些经典案例，的确能得到某些结果，但是我却一直都没有经常使用，因为真的不是每个office都兼容那个东西，而且不同版本的office看到的结果和刷新到的效率可能相差很远。

要解决同样的问题，到底用什么样的工具？当我手上的工具只有唯一的时候，就只能选那个，但是当我可以做选择的时候，我会考虑数据大小、运行速度，以及不同windows和不同office下的兼容性。

当我正在潜心研究VBA+ADO+SQL的时候，某一天当我去搜索某个问题的解决方案，发现Excel里将要内置python了。那这个到底是什么样的python呢？星期四的晚上我看到ExcelHome的专业人士进行了进一步的解释。他们的专业人士说的东西，我还是比较认可的。

当我第一次听说Excel里面终于可以用python的时候，我并不太兴奋。为什么会这么说呢？因为是在Excel的某个地方写python，而不是在一个完备的IDE里写。这有什么问题呢？

可以这么说，Excel所有写代码的地方都让人挺不愉快。比如你要写公式，经常的情况是你选了那个，一会车，然后就没有了。如果你要写嵌套的公式，那些括号更加是会把你搞死。写公式的那个地方，你很难分得清全角和半角标点符号，于是你经常被卡在那个地方，然后被卡没了才发现自己在打勾之前没有先复制粘贴到其他地方，所以打那一大堆全部白费了。

在其它编辑器里也很郁闷。PQ有高级编辑器，高级编辑器好像要比公式智能一点点，但是又过于智能了，比如经常会帮助你自动添加一些符号，有可能是双引号，更多时候是逗号或者括号，但那并不是你想要的，你自己会把那补全。在你不知情的情况下，PQ给你添加上去以后，最终就报错了，翻找一大堆后才发现，那里不知道为什么多了一个符号。

PP里面写公式，如果是写度量值，情况还会好一点，因为你可以先做一个公式的校验，校验失败起码就意味着你基本上不用确定提交了，但是校验的时候到底是哪里不对，为什么不对呢？这得凭借你的经验。如果你进入到PP里面，在那个类似于Excel写公式的那个地方写代码，经常遇到的情况是你还没写完，就直接给你报错。因为报错了，你被卡住了，所以还真的漏了一些东西，结果当然是无果了。

相对于PQ和PP来说，VBA算是Excel里面可以写代码比较完善的地方。在VBA里有标识符和关键字，而且可以设置不一样的颜色，但问题是VBA那个编辑器除了会帮你增加空格以外，你别想能帮你进行任何的补全。无论函数名称还是那些你之前已经定义过的变量。被大家吐槽的最多的是一句判断没写完，要到其它地方复制东西，马上弹窗告诉你语法错误。当你运行不通过的时候，会有各种各样的弹窗，但绝大多数情况之下提示都没什么意义，因为没有针对性，你不知道该怎么改。

综上所述，在现有的Excel里面写代码，除非你非常牛逼，一写就对，万一你是那种粗心大意到极点的人，在Excel里写代码会让你非常崩溃。那个大家期待已久的python，据专业人士说，输入的方式是盲打。你别想Excel会给你补全，会给你提示任何错误。而且那个python in Excel是云上的东西，所以你的电脑上可以完全不安装python，数据通过网络云计算然后再返回结果。这就意味着算力最大的障碍就是你和服务器之间到底有多通畅？VBA完全可以脱机使用，那就意味着可以秒杀出结果，但如果我用的是python，需要实现同样的功能，我跟云服务器那边用有几万光年的代沟，甚至根本连不上，那么python那个地方永远都会显示#BUSY#，永远都不会有结果。如果一直挂在那里，或者某一时刻终于连上了，就会把结果反馈回来。我从来不太相信微软的服务器，因为从windows或者office软件系列的更新就可以看出，我们跟微软的服务器有非常深的代沟。现在这个刚刚有了雏形的python，居然是暂时只能用云计算，这样的东西能让人有多少期待呢？如果你不是在中国，如果你在美国，你是一个Microsoft 365的付费用户，而且是专业版的。可能会好很多，但是，我们在中国，我只能说希望越大失望越大。

我还是用好手头上的工具，实现我的小目标好了。

我已经不记得自己是如何走上这条写代码的不归路的了，确切地说我已经不记得具体我是哪一天开始研究这个跨表查询的问题。我大概知道顺序是怎么样的，但是具体是哪一天开始我实在已经说不清了。现在我天天都在写，天天都在写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秒的运行时间对我来说几乎是无感的，我可以接受这种方案。

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

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

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