我的天

之前说到了Power Query方案不完美，第2天我又更进了一步，真的老老实实做了两层加权平均。效果挺稳定，比之前那个还稳定，因为之前那个方案查询数据是跨表的方式获取，PQ可以跨表查询数据，但在数据转换的时候，可能会遇到一些问题，导致刷新通过不了，于是你得重新刷新，如果你的表格很多，会让你非常的烦恼。我这一次，我采用的方案是用VBA跨表抓取数据，相对于PQ的跨表来说，VBA的跨表更稳定。在不进行文本拼接的前提下，我对VBA里的SQL操作感觉是要比PQ熟练一些。 VBA抓取的数据很快，不会有卡顿的烦恼，除非VBA直接卡死了。VBA卡死的解决方案就是重新拿出之前备份好的那个重新刷。又或者是卡死了之后再弹出Excel，然后你选择把那个东西恢复，这两种通常都能解决问题。如果依然卡死，那么估计你就得重启电脑了，如果重启电脑还是不行，那就换一台电脑吧。前几个月基本上我每天又或者是每隔几天就会遇到VBA卡死，但近段时间又好像没有再遇到，我严重怀疑这跟win10的系统更新有关，尤其是框架的更新。

用VBA抓取数据，然后把那复制到要进行PQ处理的工作簿里，把需要手工填写的东西补上去，然后PQ就可以很快乐地在工作簿内弯曲折叠生成我想要的样式。如果说有什么事让我觉得我非得在PQ里而不想用VBA解决的，大概就只有文本拼接。在PQ、Python又或者真正的SQL里进行文本拼接都是很大路的货色，但是在Excel的SQL里，你就是没有一个很方便的方式实现，你只能把它输出到数组，然后再折腾半天。老领导要求的表格里，总少不了文本拼接。有可能是拼仓号的，有可能是拼品种的，有可能是拼客户的，也有可能是拼各种组合的。如果要拼这么多的东西，我首先想到的就是在PQ里做一个分组。PQ也是一个很神奇的存在，分组的时候就没有办法给我选择文本聚合，其实文本的聚合的方式就是文本拼接。所以每次你都是得随便选一个求和，然后在高级编辑器里面把求和改成文本拼接。

PQ经常让我绕来绕去绕不出去的还有if的使用。在其它编程语言下，括号逗号解决问题，但是M语言里if这些东西都没有。所以当我用常规的思路去进行我的条件设定的时候怎么都不对，最后我不得不使用自动生成的方式，接着才发现原来自己语法错误。

在PQ里使用加权平均，思路来说很简单，就是数量乘以单价求和后再除以数量之和。如果要分步操作，在分组之前要算出一个总价，分组以后在高级编辑器里面修改公式，用总价除以总数量，但实际上也有不需要分两步的方式，但是需要用到list.zip。其实我对PQ并不算太熟悉，在没有查找加权平均的方案之前，我试过用公式套叠，结果发现不行，在list.sum里面选择两个列，然后相乘实际上是不可以的。list.zip那种方式，让我想起了Python里面的元组操作。要完全搞懂PQ，学习成本非常高，他们的各种玩法套叠简直是到了那种让人眼花缭乱的程度，当然，其实Power Pivot，也就是DAX语言要玩得高深，同样很烧脑。对我这种初级玩家，主要是用来拼接文本的人来说，基本功能能顺利实现也就可以了。最终生成的数据，有一些地方肯定用了加权平均，我手动校核了一下，发现很OK，没有问题，但是我还没有测试过一种先单仓做平均，然后再多仓做平均的数据。这种数据肯定会有，但是今年的前几个月未必一定发生过。

手上的工具多了，用的时候得想想用那个最合适，又或者，联合使用也是个妙招。

花了一个上午的时间，让某个Power Query的查询更进一步，花了一个下午的时间修改级联下拉网站的一些格式问题。无论是哪一个，我总感觉都不够完美。

Power Query的查询，之所以我感觉不完美，倒不是因为我的数据有什么问题，我或者我的解决方案有哪里不妥，而是需要我们填写这一张报表的人，脑子不知道想不知到底是什么。那是一张信息高度集中的报表，但为什么要把那么多信息在一张表上反映呢？里面要有每天的进出数据可以理解，要有每天的库存数据，也可以理解，因为期初库存加上进出的数据，就可以得出每天的库存。每天的库存是有要求的，必须得表达出来，这个可以理解，每天的进出数据具体来自于哪里，要把那写明白，这也可以理解，但是在2024年7月1日即将实施的某个规定里，要把采购价格也写进去，这个我就很不理解。采购价格跟这些进出有什么关系吗？又或者说任何一笔进出数据，一定就能轻易关联到采购价格吗？如果每天变化的只是一个仓，设定表格的人难道觉得采购价格就应该是一个固定值？实际上某天某个仓按照不同的合同，实际上可能有多个采购价格，那个仓严格来说，我是不是要做一个加权平均？某天多个仓发生了变动，有进的也有出的，进的那些仓一个仓有多个采购价格，出的那些仓，哪怕我已经不按先进先出或者价高先出之类的规则，直接把那统一为一个均价，这也意味着存在着很多采购价格。采购价格这个东西，完全没办法从原始凭证里面得到，每天某个品种要填写进出数量，但是涉及仓号那里，进出都填在一起，采购价格那里如果有多个价格就取一个均价，但是这个均价该如何取舍呢？单仓先做一个均价，然后每个仓房按照数量跟价格再做一个加权平均吗？最后出来的那个采购价格代表了什么？算出那个数很不容易，但是算出了那个数，又能怎么着呢？那不代表任何东西。做出那个是完全没有意义的，为什么非得把那个数给算出来呢？硬是要把那个数算出来以后，最终会导致这张报表采购价格那个地方出来的东西是个四不像，用什么资料去验证？其它数据都是确切可以算明白的，但那个地方的数据却只是一个大概差不多的玩意。不知道这么脑残的决定到底是谁做的。想出这么个招的时候，居然没有人去否定他。如果有一天，信息化程度真的可以实现把报表要求的数据都自动的算出来，我举双手双脚赞成，但是还是那句话，加权再加权得到的平均采购价格到底有什么用？又或者对普通人来说，对正常的人来说，根本没办法做那么仔细的加权平均，所以最终我的暴力解决方式是首先算出那个月变动的仓房有哪些，然后指定那些仓房的采购价格，每天有多少个变动仓房就直接把那些仓房的采购价格算术平均。

工作的时间越久，越让我深切地体会到：上面不正常，会让你憋死；下面不正常，会让你气死，横竖都是死。

花了一个早上的时间写了两个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的付费用户，而且是专业版的。可能会好很多，但是，我们在中国，我只能说希望越大失望越大。

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