我的天

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

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

又是一个下班之后继续干，干到直接忘记写blog。如果不是这么着迷的话，理论上我会先烧水，把麦片泡着，然后打开语记开始口述blog，接着开吃，但如果我过于着迷，泡麦片的时候我会在那里写代码，吃麦片的时候也在那里写代码，吃完麦片以后往杯子里加水，等待水凉的时候继续写代码。可能那杯水我得花好几十分钟才喝完（一边喝一边写代码），也可能等我喝完的时候已经过去了一两个小时，又或者是我根本就不记得喝了。之所以要加一杯水，因为本来吃完麦片就得喝水，如果我不马上洗杯子，东西干在那里很不好洗，如果有水泡着就会容易些。所有习惯，只要遇上着迷于写代码这种事，全部都会变成渣渣，除了显示屏上的东西，身边的一切我都完全没办法放任何的注意力，除非是内急之类。写代码这种事对我来说通常是一发不可收拾的，完全停不下来，所以我没办法预测我得花多少时间，我知道我的目标是什么，但开始的时候通常我不知道具体该怎么做，就更加不好说得耗多久。之所以会这样，因为写代码不是我的强制工作，我不靠那个赚钱，没人要求我必须得那么干，纯粹出于个人喜欢。

光是写出来还不行，当我觉得效率很低的时候我会不断地在那里挣扎，到底有没有其它方法呢？还有没有其它的途径实现这种功能？

遇到问题，光在那里想，是不会有结果的，但只要干，埋头苦干，方法就会自己涌出来。大概是几天前，我不知道在哪里见过意思差不多的话，对我来说，写代码的时候会这样。如果我用的是python，因为我的目标很简单，所以绝大多数情况下都有现成的解决方案，只是那个方案是不是高效，能不能再提高一点而已。但如果我用的VBA+ADO+SQL，情况就很不一样的，因为VBA里的SQL被严重阉割，虽然理论上没什么东西是SQL解决不了，解决不了就输出到VBA的数组继续解决。我第一个接触的代码是CSS，然后是HTML。有段时间我迷恋于花俏的网页特效，有些联合CSS+HTML能实现，但有些不得不用javascript。当年我无数次想开始学习javascript，但一直都没开始。后来，我的大学课程里有C语言，我还考了二级C语言的等级证书。上机是满分通过的，但笔试70不到，所以我那张证书上只是合格而不是优秀。如果能重来，我会做到优秀的。但实际上，优秀不优秀又有什么意义呢，招聘的时候那不过是别人一扫而过的一张纸，甚至不会在别人脑子里留下任何记忆，但为了“优秀”那两个字我就得在上面付出很多，因为考证的考试范围大于大学课程的教学范围。

从我开始接触互联网，我的各种兴趣就没有停止过，写代码这事也一直贯穿其中，CSS、HTML、C语言、php、python、R语言、DAX、M语言、VBA、SQL，其实我也尝试过java，因为那是安卓的基础，或许还有一些我漏了没列出来。或许，码农是我天生的隐藏属性。

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

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

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

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

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

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

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

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

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

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