我的天

之前说到了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，没有问题，但是我还没有测试过一种先单仓做平均，然后再多仓做平均的数据。这种数据肯定会有，但是今年的前几个月未必一定发生过。

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

上周五基本上我已经完成了，自己第一个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的跨表查询。之所以说是大部分，准确来说应该是绝大部分，有一个功能暂时我还做不到的，那就是在分组的时候对字符串进行合并。如果我用的是MySQL，一个group.concat就可以做到了,但问题是SQL in Excel没有这个功能。有些人用一些非常复杂的手段实现这个，但我不确定那个方案是不是只能用在 SQL server上面。 SQL这个东西不同地方用的函数真的很不一样，像是Excel这种明显阉割版的，基本上我已经不抱什么希望了。最坏的打算是把最后那个分组前的记录及输出到一个数组，在数组的层面进行字符串的合并，对这个合并结果做一个字典，然后把之前其它东西已经合并好的东西也输出到数组，分别做两个循环合并到第三个新数组。或许不需要这样，如果我能把已经做好的那个字典作为SQL查询的一部分，我就可以继续以一个左外的方式连接除了这个需要合并字符串的以外的部分了。于是问题的关键似乎又回到了起点。在Excel的VBA里面，我到底能不能把数组作为SQL的数据源呢？这个数组里没有空值。

当我知道VBA里面做汇总可以用字典也可以用SQL以后，我马上觉得我能不能把我之前已经整理好的数组放到SQL里，然后做汇总？但那个时候我没有找到答案明确地告诉我可以这么干，应该怎么操作。通常各种教程里面说到VBA+ADO+SQL的时候，通常都会说怎么把SQL的结果也就是记录集输出到数组，但是从来就没有反过来干。因为别人的脑洞默认是你的SQL是从工作表里面直接取数，而不需要用数组从工作表里面取数，经过某些整理以后再传给SQL，但实际上对我这个半生不熟，只比新手老一点点的人来说，我经常是哪里会用就用哪个，哪个先学到就哪个在前面，前面的部分我已经用数组做完了，接下来的用数组好像有点麻烦，所以我就想后半部分能不能用SQL解决。

很多VBA问题大家经常是数组+字典，SQL+数组也有，但是好像没有先做数组再传入SQL。而之所以要这样，肯定是因为我的SQL水平还不到位。高手是那种你给他任何一个工具，他都能做出你想要的东西的存在，而我是那种做某件事就必须给我恰当的工具，否则我就做不出来的人。情况就有点像某些人写字就必须得用那那支笔，如果不是那支笔的话，他的字就写不漂亮了。在拧螺母这件事上，没有扳手、没有呆头扳手我就很难固定住螺母，但是对高手来说，呆头扳手、活动扳手，甚至你只给他一个钳子，他也依然能把螺母固定好。

最后这个分组字符串，我一定会继续努力的，希望 SQL in Excel有它自己的解决方案。

又花了几乎一天的时间去研究在VBA里使用SQL。上午我主要卡在为什么我运行不了SQL这个东西。最主要的原因是我贴过去的那个例子是2014年的，那个时候的office跟现在的很不一样，所以是不是代码里的某些参数要改过来？4跟8改成12，我的确改了，但还是不行。吃过午饭以后我我把ExcelHome论坛前天大家才写出来的SQL查询复制过去，发现果然就可以了，因为除了12要都改以外，还得改一个Microsoft之后的单词…… 为了这个运行不了我还折腾的一番到底我需不需要装32位的AccessDatabaseEngine.exe，因为我发现自己的电脑是默认的那个是64位的。一开始我还以为自己的Microsoft 365是32位的，因为以前自动安装的的确是32位的，但是当我安装了32位的AccessDatabaseEngine.exe以后，发现还是不行，接着我就去看自己的office，原来已经是对64位了。因为据说64位要比32位快那么一点点，我已经不记得自己到底什么时候换了。换一个office对我来说毫无难度，因为只要登录我的账号，随时都能把那个离线文件下载回来。

总算可以在Excel里运行有SQL语句的VBA。据说要在Excel里运行SQL有三个方法，第1个是用远古的MS Query，这种方案在我用Office 2003的时候尝试过。接着是在新建连接那里输入一大串的SQL语句，这种事情我好像也干过。最后也就是现在主流推荐得最多的在VBA+ADO+SQL。这是我之前完全没有接触过的。你说我完全不懂SQL吧，也不是，实际上我也是有一点点懂的，因为高中的电脑课程里面就有Access，那就是一门电脑课，因为用得不多，所以印象不深刻。相对于其它编程语言，我觉得SQL的单词算是非常简洁了。入门是简单的，但是你要把它玩得很溜，一点都不简单。

从别人的代码语句构造来说，我觉得SQL要比VBA的数组好理解一些，但是当我自己要写的时候发现哪哪都不对。主要是虽然是SQL，但是不同软件里面的限制可以称得上是五花八门。当你搜索出一种SQL写法以后，发现在Excel里面无论如何都不行，最后发现原来是Excel不支持这种玩法。比如我要做一个多条件判断，我肯定毫不犹豫会想到用switch的方法，SQL里面都有case解决方案，但问题是Excel里不行，所以如果是多条件的，你只能不断地嵌套if。因为我多条件最终实现的是一个文本替换，所以我就足足套叠了5层replace才实现了我的功能。

用过VSCode你肯定会觉得Excel VBA最让人吐槽的就是VBE的各种奇奇怪怪的限制。如果你要在VBA里面用SQL语句，你就得用双引号把那圈起来，而且在双引号里面是不允许换行的。这就很让人抓狂了，不能硬换行可以，但编辑器为什么就不能给我一个软的自动换行？视觉上给我换一下行有什么问题吗？当你不得不把那串SQL圈起来，就意味着里面所有成对的符号自动识别一律失效，全部变成了白开水字符串。本来那就是很大串的东西，还没有方法帮你自动确认格式有没有错误。如果可以换行可以缩进，你可能一眼就看出自己在哪里出毛病了，有些配对没配对上，但是因为你只能一笔写到底，所以你怎么可能不犯错呢？犯的这些错误有可能是你不知道规则原来是不允许的。比如如果你只有一层select，字段里有别名，那么group by的时候你就不能用别名，因为group by比select先执行。在某些软件的SQL里，是允许加having语句，从而改变 group by和select的先后顺序，但在Excel里面加having是没有用的。如果你非得要在group by的时候使用别名，那么你就得嵌套select。内层select先别名，外层用group by。这些神神经经的限制实在让人抓狂死了。同样是分组，如果我是在PQ里进行，重命名列这种事情放前面放后面都无所谓，变形改造列这种事情放前面放后面也无所谓。因为重命名、变形、分组这件事情全部可以分步。但是在Excel的这个SQL里面，所有东西都得一次到位。明明可以短句实现的功能不得不写一大串。实际上在Excel以外的SQL写法里，是不是也非得这样？VBA的古板是显而易见的，但是当你见识过他们把SQL阉割限制成这个模样。你就会立马觉得python的自由奔放实在太好了。

死磕得越多，我越发现自己喜欢VSCode+pyhon。

又花了大半个下午的时间，我把python跨表查询版最后的那个小计功能也开发出来了。其实前一天晚上我已经找到了类似的案例，只要按研究透的那个东西，接着往我自己那里套就可以了。我大概明白里面用到的公式到底是干什么用的，但是把它们套起来了以后，我发现用在我的那里无论如何都不对，所以我就在案例里不断套脚手架，不断地做注视去掉东西。最终发现让我失败的原因是我的那个dataframe是没有索引的，这就让我后面折腾了好长一段时间。

要在dataframe里加小计，首先需要对进行小计的项目进行分组处理。前一天我已经了解过，这样分组出来结果就只是那些聚合的数据。这些聚合的数据如果你不需要带入特殊的分组词，那么你跟原数据合并，然后根据你的分组项目名排序，小计就会合体到原来的dataframe里。如果你要加入小计这样的词语，你就得虚拟新增一列以非分组项目为名称的列名，内容就是小计之类的词。这样的分组结果我不知道为什么那个案例最后要设定以分组项目为索引，因为我在折腾那个案例的时候发现做不做这一步出来的结果没区别。

最最关键，让我折腾半天的根本原因是我要加小计的那个dataframe在从Excel读取数据的时候就已经设定了不添加索引。我发现当我去掉了案例的默认索引以后，和我的脚本出现了同样的问题。所以解决方案是先给我的datafame添加一个默认索引，然后再进行上面说到的分组操作，接着把有默认索引的dataframe跟分组结果结合在一起。同时对分组项目排序。分组后的结果有没有默认索引都无所谓，因为合并时都得重置索引。我没有试过如果这个dataframe也自带了默认索引，最后能不能成功合并。纯粹为了探索，我应该了解这个，但因为我运气好，在研究之前就已经得到了我想要的结果，所以我就没有继续下去，接下来我会继续拿那个案例把玩一下。

为什么会在Excel的单元格数据传入pandas的时候就把默认索引禁止掉呢？其实不禁止也完全没有问题。因为在最后把加工过的东西输出的时候，我可以控制不输出。之所以会有这样的习惯，写出这样的控制，是因为我看的第一本用python批处理Excel的书里面是这么写的。在看那本书的时候，我觉得那本书写得一般般，因为他给出了一个例子，然后大概告诉你要实现什么功能，接着就是展示脚本。我觉得起码你得在介绍那个例子的时候，除了源数据本身，也得展示一下你最终的效果是什么。他们还偶尔说不清具体需求是什么，唯有去研究他们的代码，你才知道原来具体他们要干那个。

在一个明细数据表里加入小计这东西是完全可以实现的，但是从数据处理的角度考虑，为什么我要把明细跟汇总合并在一起呢？如果用我的Excel思维去考虑这个问题，我觉得明细表就是，明细表汇总表用透视表表达出来就好了。因为数据透视表是很灵活的，可以用任意的汇总维度去观察同一个源数据。python可以轻松处理Excel的数据，但是到了Excel以后，展示的方式的控制好像python的插件就有点难以直接控制，而要控制这个最好的方法就是通过api，用VBA控制，因为vba是原生office的自带工具。

我发现python批处理Excel脚本的运行速度跟电脑的CPU有很大关系，跟内存大小关系不大。用我办公室的电脑运行，但需不到6秒，用我宿舍的电脑运行大概需要7秒，用我家里的电脑运行大概需要7.5秒。这是在正常的情况下，如果我的电脑正在执行多任务，这些时间就会说不准了。之所以我说这跟电脑的CPU性能有关，因为运行脚本的时候我盯着任务管理器。发现有段时间Excel的CPU会飙升最大40%，虽然维持的时间很短。不同性能的电脑同样CPU，封顶都会飙到40，这就意味着CPU的核数越多，单核的性能越好，那么这个脚本的运行速度就会越快。6系的i5运行6秒，2系的i3运行8秒，是有差距，但经历过Power Pivot得12秒起，python很爽了。

我觉得这个python脚本还有继续改进的空间，继续努力。