❌

阅读视图

发现新文章，点击刷新页面。

手贱惹的祸

✇我的天
xrspook

当年今日

VBA可以对普通的Excel文件用SQL查询，虽然我已经用的是超级表，理论上单元格的数据格式是一致的，但实际上这不是一个标准化的数据库，我没有办法在一开始的时候就对每个数据进行声明，所以在数据处理过程中就会出现这样那样说不准为什么的事情。

星期一的下午我跟往常一样做了些普通操作，最后当我用VBA生成一个汇总数据的时候发现弹出一个“标准表达式中数据类型不匹配”的警告。看到这个东西，我的第一反应是肯定是获取的数据里面有一些不规范的东西，比如说某一列通常是数字的，但是却出现了文字，但实际上我翻查了全部源数据都没有发现这个玩意。没办法之下，我只能做一个脚手架，一个一个的去排除，最后发现问题出在where里。where里有一个“性质某某某”的限定条件，问题就出现在那个地方，只要把这一句删掉，VBA是可以正常运行的，至于结果对不对不知道，反正能运行，然后我又回到了这一条的上一条结果，发现where里面的那个条件是没有问题的，所以这到底是什么问题呢？

然后我又想起，在进行普通操作的时候，我好像在某列数值的单元格里发现了一个汉字，说不准为什么那里会有一个汉字，但一个汉字足以影响那个单元格的类型。为什么我深有这种体会，因为如果某一列全部都是数字那么 VBA+ADO+SQL通常都会默认那是一个数值，但只要那一列里面有一个文字，所有东西都会被识别为文本。对数字进行聚合是理所当然没有问题的，但如果对文本进行这种操作，我不敢想象会出现什么东西。当然了，把文本作为分组条件，一点问题都不会有。

我感觉自己的VBA程序是健壮的，因为我已经用了他一年多了，之前从来没有出现过这种问题。有段时间经常会出现这样那样说不准什么问题的问题，但是过了一段时间之后，那些问题又自动消失了，所以我更相信那是有段时间windows更新导致某些框架不稳定导致了那段时间的意外。除非我对源数据做了更改，又或者是出现了某些意外，否则不会报这样的错误。

接着我又记起周一下午某个基本操作的时候，我好像发现在那个超级表的下面有一个“她”字。那个东西在不连续的单元格里，不是超级表的一部分，但关键是如果我用SQL获取数据，那肯定也会被纳入其中。看到那个莫名其妙的“她”字以后我已经把那删掉了，所以我看到的那个原始数据表格没有问题，但只是看上去没有问题。

折腾了好长时间未果，之后，我不得不重新翻出前一天晚上的源数据。然后手动把周一更新过的东西全部都贴回去。再去用VBA汇总，一切正常。在贴回去之前我首先用VBA测试了一下汇总没有问题，然后我才开始贴，贴了之后也没有问题。如果这个东西没有问题，也就是周一之前这个表格是没有问题的，但不知道周一进行了什么操作，导致了问题的出现。倒退以后重新贴数据没有问题，的确这个汇总也算保住了，但是我却一直放心不下，找不出问题的原因下一次依然会手忙脚乱。

吃过晚饭后我重新翻出有有问题的那个源数据。我的猜测是，因为数值列里面出现了一个文本，虽然我已经把文本删掉了，但是那个文本已经影响了那列单元格的类型，最终导致VBA弹出错误提示，虽然那个错误提示并不是出现在VBA调试发现的那一列。我的做法是在不修改VBA的前提下，把有问题源数据超级表下面的所有行全部删除，然后保存，再次运行vba，源数据通过了，可以正常运行。通过这样的操作，就能排除错误，非常有可能意味着我上面的猜测是对的。然后，我故意在数据列超级表外的单元格写一个字，然后保存，VBA汇总挂了。我把那个字删除，保存，VBA依然挂。但是当我把写过字的那一行删除，VBA汇总好了。这再一次验证了我的猜想。

这种事情该如何避免呢？首先不要手误，不要乱填。手误乱填这种事过去那么多年都从来没有发生过，为什么就发生了呢？到底是我的问题，还是另外一个人的问题？如果要避免这个事情，最好我在SQL引用源数据的时候就直接就限定为超级表范围，而不要把超级表所在的所有列都含进去。无论是哪一点，都是可以实现的。限定超级表的范围不太难，但关键是人手贱的这个行为，这一次出现在某个不知道为什么的单元格，下一次如果覆盖掉超级表的一个老数据呢？要避免这种人的失误非常难，但是人为什么会犯这种弱智到极点的失误呢？而且是犯了还毫不知情。

但总算这一次，我找出了可能的原因。

升级VBA抓取方案

✇我的天
xrspook

当年今日

无论是SQL方案还是数据透视表方案，我都是用4个类似的脚本，一款做出来了以后，调整部分的内容，生成其它三款，所以一开始的时候抓取数据我有4个宏，其实里面的内容大多相同。汇总数据，我也有4个宏，SQL有4个，数据透视表也有4个。因为SQL跟数据透视表作用是相同的，所以它们分别搭配4个抓取数据的宏各自组成两个文件。就实现功能来说，这两个文件从形成的那一刻起，已经可以起作用，但是我能不能更进一步呢？

数据抓取用的是最基本的VBA，就只是把数据区域选了一下，然后去掉表头，最后搭配我想要的表头输出。我没有在那个地方就进行筛选，因为之前已经说过，VBA自带的AutoFilter功能不太好用。一开始我没想过要用数据透视表，如果到了SQL，一句where可以把正向的反向的或的且的，想怎么加就怎么加，只是一句话的事情而已。既然在一开始数据抓取单元格层面那么难做筛选，那么我到SQL里做筛选就可以了。后来，因为我又做了数据透视表方案，数据透视表方案可以对字段进行筛选，但关键是如果我抓取了那个数据里面不含有我要排除的内容，又或者我抓取的数据全部字段都要被排除掉，无论是哪一款，都会出错，所以如果用同样的抓取方式，到了数据透视表的那个宏里面，我就需要进行复杂的循环和判断。嵌套一层又一层的公式，再加一层又一层的判断。虽然也能实现我想要的东西，但那样做很麻烦。在做出数据透视表方案的那天晚上，我就在想，在一开始的抓取的宏里面，我能不能直接把筛选这个步骤给做了呢？

要在数组里面进行数据筛选，想想都知道肯定可以实现，但是我想到需要嵌套那些数字就觉得很烦，所以我就翻出了多年以前我用来合并某些数据表的宏。在那里我发现自己用的是把全部数据都粘贴到一起，然后做一个行的删除。当时做的行删除很简单，只要匹配一个字段就行了，我现在的行删除，需要匹配的字段可能会有很多，所以我就把那些需要删除的字段都先放在一个数组里面，然后再利用之前那一天从网上抄回来的那招Application.match方案。在一句if里面就能实现查找某个元素在不在某个数组里,如果在的话就把行删掉。之前写的那个宏的确就这么简单，因为需要删掉的那个部分隔好多行才会出现一次。现在我需要处理的那些数据，说不准我需要删掉的那些行是隔一些才出现，还是下一个就又得删掉，所以在删掉之后，我又赶紧做了一个减法操作，让程序重新测试那一行到底需不需要删除。

在一开始数据抓取阶段，我就把数据范围确定了，把需要例外的数据全部删除，所以后面的SQL跟数据透视表我就可以轻松地直接进行操作，尤其是数据透视表，操作变得简单明了。

因为一开始我不是大神，我只能摸着石头过河，逐渐的磨练自己。

VBA里搞数据透视表

✇我的天
xrspook

当年今日

因为我知道我要实现的那个功能，SQL可以做，数据透视表也可以做。就写代码的熟练程度来说，SQL我肯定更熟悉，VBA的数据透视表有很多参数，我搞不懂到底是什么，反正要实现那个功能，通常是录制一个宏，然后看着办，有需要的数据保留下来，不需要的数据直接删掉。录制的宏通常都很啰嗦，里面有非常多没有必要存在的东西。在不了解数据透视表在VBA里的参数的前提下，先进行一个录制显然是比较靠谱的步骤。但有些东西靠录制是录制不出来的，比如某些字段我需要进行筛选，我只知道有些东西是不能出现的，但我没办法确定可以出现的是什么，所以那一堆不能出现的东西都是反参数。在录制宏的时候，你只能看到什么就反选什么，但关键是这个数据源跟那个数据源的那些参数是不一样的。不一样我只能设定一堆反参数，只要它们是其中一个，就不能显示，但实际上这样的反参数让VBA的数据透视默认不出现你就得兜一个大圈，套上好几层公式实现。最终，在我调试的时候发现的确那些嵌套的公式能把那些反参数都排除在外，但如果数据源里所有数据都是反参数的一部分，那么就会报错，于是我又得在外面加一层捕捉错误的判断。真的是非常折腾。为什么之前我考虑的是SQL而不是数据透视表，反参数是其中一个点，另外一个点是排序。在SQL里，使用自定义序列排序是非常简单的事情，但是数据透视表的字段该如何排序呢？兜了一大圈我发现也就只能真的在Excel里面增加自定义序列，但如果我想用即弃呢，于是还得出了一招VBA先增加自定义序列，排序完以后再把自定义序列删除掉。这个操作在ExcelHome的教程里有，他们教的是在VBA里面，对单元格或者数组排序，不是针对数据透视表的，但实际上原理一样。

这个增自定义序列和减自定义序列到底是怎么确认呢？原来Excel还会对自定义序列给一个序号，所以在增自定义序列的时候，你就得把这个序号记下来，在减的时候把那个序号写上去。我不知道为什么其他人说数据透视表的自定义序列好像怎么排都不是自己想要的效果，但就我个人的经验来说，只要你在Excel里增加了自定义序列，当你刷新数据，默认对某个已经自定义过的字段进行升序，那就是你定义的那个顺序，不需要在设置里面搞一大通。但实际上我也搞不懂，手动设置里很麻烦的东西到底是什么。为什么数据透视表里面的排序就不能像普通表格排序那样那么的直观。我要以什么字段排序、以什么标准排序是系统默认的还是自定义序列。当然，数据透视表里还得考虑一个问题，就是有可能是套叠了多个汇总条件的，如果你自定义的是最后一层条件，首先限制你的是前面的那些所有条件，所以你想最后的那个自定义序列完全按照你的想法排列，你只能把它放到最前面。数据透视表跟SQL比起来，我感觉运行速度会慢一点，可能因为我里面判断设置的东西比较多，所以需要闪那么一下才能结束，但是SQL虽然我已经有意识地关注结尾这个问题，但测试频繁以后，SQL是会出现一些莫名其妙的事故，当你把所有东西关掉再打开就正常了。相比之下，数据透视表不会这么神经。

用不同的方法实现同样的事情，得出一样的结果，这种感觉很好。在探索这个的过程中，会让我体会到二者的优劣，以后选择的时候，我就可以更有底气地数出1234。

不完整的错

✇我的天
xrspook

当年今日

上一篇说到了数据汇总的问题。这个周一我就是按照上周五设定的那个步骤去做。在做的过程中，几乎没有发现什么问题，但是当我做完所有，一个个表格验证的时候却发现不知道为什么有些表格 SQL抓取的数据不完整，VBA从原始表格筛选、抓取的数据没有问题，但关键是SQL从本地的文件里提取到的那些数据不完成。第一次发现这个问题的时候，我看到的是为什么汇总数不一致。当我把SQL回退到第1步的时候发现第1步获取的数据就已经不完整。明明有50行数据，实际上只能提取到42行，重复多次依然是那个效果，但是偶然当我把文件关掉重开以后又好了。所以这个有时发生，有时不发生，到底是什么情况呢？当我打开VBA文件，一个一个测试的时候，发现前几个还好，后面就会出状况，可能是数据不完整，也可能是弹出一些莫名其妙的错误，但只要你把所有Excel都关掉，再重新打开又没有问题了，但是在测试几个以后，又会出现这样这样那样的状况。用VBA+ADO+SQL整理输出数据我已经实施过很多遍，之前从来没有遇到过这种神奇的状况。最后当我打开VBA脚本，无意之间拉到最后，居然发现cnn没有close，也没有初始化。cnn是个非常牛逼的东西，但是那个玩意也要耗费巨大的资源，在我出现数据状况的时候，我没有观察过我电脑的性能到底如何了，会不会CPU或者内存甚至二者都有点状况了。因为一次又一次的验证数据就意味着我得一次又一次调用cnn，光是打开又不关闭，最后就会出现奇奇怪怪的事情。当我把所有脚本都加上了结尾以后。从头到尾10个表以上的数据，一次性搞完，期间不会出现状况，所以多么神经质的行为才会导致了这种弱智事情呢？以前我倒真的从未试过这样。有过这样的经历以后就让我明白到cnn打开和关闭都必须是一个闭环，在一个宏里就得实现到位。如果某个宏被卡住了，半路停在那里，估计那个cnn是不正常的，当我又再次启动其它，只会让错误不断积累，最终导致崩溃，又或者是得不到我想要的东西。

写程序可以很快，但是调试却非常耗时间。这大概是所有码农都必须面对的事情，但实际上更多的人只顾写，只顾实现，而不考虑全盘，不尽可能地用全面数据测试，最终的结果就是使用的时候出现各种各样的未知情况。我不知道其他人到底是如何调试的，反正我真觉得调试的过程比写脚本更费神，因为要考虑所有的情况，哪怕某些条件可能非常极端，几乎不会碰到，但即便那样，一个健壮的程序应该依然能捕捉到那个错误，然后给出对应的反馈。比如我抓取不到数据了，我就应该弹框告诉人家我抓不到，因为有些操作的抓取数据以后才能进行，所以既然能判断抓不到数据，后面的也就不用继续了。

调试程序是一个很磨人的过程，这个过程重复多了，人自然而然就会向完美靠拢，即便我们一定不能成为完美的那个。

VBA里奇怪的筛选与粘贴

✇我的天
xrspook

当年今日

上周五我尝试纯粹用VBA的方式对某些数据先进行筛选，然后保留在粘贴板，又或者是把那些东西输出。前提是对某些数据进行筛选复制的时候，我首先必须得有个条件。理想很完美，现实很骨感。

VBA本身就自带一个叫做autofilter的函数，那个东西可以对选定的单元格区域进行筛选，你可以正向选择，正向的时候可以多选，可以把需要选择的内容组成一个数组，实现多选。你也可以进行反选，但反选的时候你却不能把反选的条件建立成一个数组进行同样的操作。这些都只是针对一次筛选而言的，如果同一片区域多次叠加筛选，第一次你筛选的是第1列，第二次筛选的是第2列，我感觉如果第三次和第四次你反选的是第2列估计也行，但关键是我需要选择的那个数据是一个作死的不规范有两行甚至三行的标题栏，但是autofilter这个函数默认输出的东西就含有标题栏。本来我的数据范围是不规范的，如果我一开始就把前面三个标题栏给去掉。那么在我进行autofilter的时候，就会从纯粹的数据开始，显然那就不是我想要的东西了。因为哪怕最终筛选的数据是空，也会默认带入那个标题栏。所以你就搞不懂为什么步骤都对，但结果就不对。

autofilter之后要进行一个特殊粘贴，那个东西是只对可见的数据复制。就普通人的思维而言，我复制了可见的数据，那么理论上我就可以算出它有多少行，如果是0我就直接不输出了，如果大于0，那么我就可以输出，但实际上特殊粘贴又不可以用一般的技术判定到底筛选的结果是不是0。虽然也能判断有没有，但需要绕一个圈去实现这种功能，所以VBA为什么有那么神奇的思路呢？筛选可以进行，但是你不能对反向的数据批量进行处理，你也不能把特殊筛选结果直接保存为某个东西。当你觉得你大概可以把可见的部分保存到一个新的区域，然后你就可以去头去尾之类，但实际上当你再去查看那个新区域的时候发现原来是保存了个寂寞而已，那种神经性质跟那个可见区域是一样的。我不知道以前的人到底是怎么忍耐VBA这些奇怪脾性的，因为在接触这个之前我就已经接触过pandas。pandas的数据分为两个，一个是标题，另外一个是数据。输出的时候你可以都输出，你也只可以只输出其中的一部分。在使用pandas的时候，我没有解决过一些我的实际问题，我都是按照书本上的例子进行操作的，所以到底在我使用的过程中会不会也遇到一些像VBA这么神经，明明觉得可以，但实际上又不可能直接实现的事情不知道。

周五的下午，折腾了一番以后，我的目标数据最终可以复制到粘贴板，但是在VBA那个脚本结束之前，我就也得把那粘贴出来，否则当那个结束以后，剪贴板的内容就没有了。为什么会这样呢？不是说当我把数据复制到了剪贴板，而我又把Excel关掉，软件会问我要不要清空剪贴板的数据吗？但显然现在Excel都没关闭，我只是关闭了那个脚本，但我剪贴板却什么也没有了。

周五在回家的路上，我又努力地想了想这个问题。最后决定，我没有必要这么折腾自己获取剪贴板数据。反正全体数据不多，我直接对那个整体数据进行加工处理就行。加工处理的方法是首先从我的目标数据那里通过VBA获取我要的部分，然后输出到指定的位置，接着通过ADO+SQL进行数据处理。准确的来说，就是一个分组聚合、添加汇总以及排序。当然其实最后这个ADO+SQL的操作我也可以换成数据透视表，但如果那样的话，最后的排序我要让那完美的按照我想要的方式，我就只能先在Excel添加一个自定义的序列。无论是SQL还是数据透视表，分类汇总和排序都一定会比在VBA里用数组方便非常多。

思考一个问题的时候，有时可能我们有点过于钻牛角尖了，退一步，可能会有一个更清晰的思路。

office系的SQL为啥不能文本拼接？！

✇我的天
xrspook

当年今日

花了几乎一天的时间去研究什么把Access VBA里的自定义函数移植到Excel的VBA里面。大家都是VBA，大家都是 office家庭的，听上去好像没什么难度，但实际上前人已经碰壁阵亡，确定这是不可能的，我只是在做垂死的挣扎。经过这么多年office的发展，在数据格结构上，会不会只有那么一点改进呢？毕竟即便是在Excel里，如果我用的是VBA+ADO+SQL，实际上我是把数据以数据库形态进行SQL的加工。于是我就想，万一他们的数据格式是一样的，万一Excel已经进化了那么一点点呢。但现实告诉我，虽然都是VBA，虽然都是自定义函数，但是因为他们操作的是SQL，所以出来的效果完全不一样。

SQL的语法结构非常类似，无论你用的是什么类型的数据库，但在一些细节上，大家的处理是有区别的，我觉得Excel里面和Access里SQL最大区别在于因为我在Excel里面SQL用的是ADO的方式，所以这就意味着虽然我写的是SQL的语法，但实际上那是以字符串的名义存在的东西。在Excel VBA的数据格式里，我写的结构化语言全部都是字符串，但是在Access里，在SQL的查询界面里，那个东西不是字符串。我没有认真看某些单词有没有高亮，因为那是特殊字段又或者是保留字段。当我直接把Access VBA里的那个自定义模块挪到Excel VBA里，发现打开记录集的方式根本不一样，语法不一样。因为在Access里本来就是一个数据库，但在Excel VBA的ADO里是通过一些特殊的语句打开那个记录集的。

回到一开始，为什么我得这么折腾呢？因为一直以来我都发现，从来没有一个人能在Excel VBA+ADO+SQL的模式之下在分组聚合的时候把文本以某些字符去重连接成字符串。要实现这个功能，只能最后把结果输出，然后在VBA里通过字典的处理，再把那些合并好的东西与其它东西结合在一起形成一个新的数组，最后往单元格里面输出，而不能像其它SQL查询结果那样直接就在单元格里全部输出。先输出到字典，然后再用字典合数组合并的难易程度跟那个数据最终的查询结果复杂程度有关。在高端的数据库里，文本聚合连接有直接的函数可以做到，比如在MySQL里面直接group_concat就可以做到，在其它专业数据库里，那个函数的名字各有不同，但都能实现同一个效果，就是把字符聚合拼接。在Power Query里，他们没办法在窗口界面让你实现这个，但可以在高级编辑器里面通过text.combine的方式实现这种功能。在Power Pivot里，concatenatex也能实现这种文本的拼接。让人觉得非常无语的是，都到了Microsoft 365时代，Access这个东西依然是office大家族的一部分，但这种肯定有需求的东西居然没有一个官方函数实现，但你又可以通过在模块里用自定义函数的方式达成。Excel的VBA里不能秒生成这种东西，但在函数层面textjoin+unique+filter可以。为什么就不能在Excel VBA支持的SQL里面出现这个文本拼接的官方函数呢？如果他们真觉得没有必要的话，为什么Power Bi的软件就可以实现呢？我不知道Power Bi软件是一开始就能实现，还是后面慢慢进化出来实现的，反正我第1次看到Power Bi相关软件的时候，他们已经能实现了。

一整天的挣扎下来好像没什么进展，但我在这些问题上又仔细思考了一番。

我还是比较喜欢VBA+ADO+SQL

✇我的天
xrspook

当年今日

我觉得编程会让人上瘾，尤其是当你实现了自己的目标以后，你就会有很多想法，比如之前我已经做过，而且已经实现了东西，能不能更进一步，再改进一些，让程序跑得更快一点？一开始的时候，只要能实现某个功能就可以了，无论用的是什么方法。在这个初级阶段，我是不会考虑别人到底行不行的，反正我行就可以，但是当自己包里面的工具越来越多以后。到底要选择什么工具，也会变成我一个纠结的地方，虽然有些工具已经很成熟了，肯定能实现我的效果，但是我还会想有没有更快捷的方式呢？

我已经不记得我是什么时候开始认识Power Query了，大概是在office2016的时候吧。那个时候我觉得那个东西可以做文本拼接太厉害了，而且厉害之处就像是跟数据透视表一样，当你的原数据发生了变动，刷新一下结果就出来了，但实际上那只是教程的效果，你完全按照教程这么干，的确能出结果。还记得几年前当我要算某些库存的时候，我用了一些很笨的方法。为了要实现区间日期里面的累计库存我用了一些非常耗费电脑的步骤。本来数据的量就不小，又外加要实现这样的效果，所以真的得算上很长时间才终于得到结果。那个很长时间意味着可能要等5分钟以上，在等待的过程中，我都怀疑自己的电脑是不是死机了。后来我也有算累计库存，但大概我已经不用一开始的那些方法了。我也有试过在VBA里计算累计库存。如果是在其它软件下的SQL里，计算累计数可以有很直接的方法，因为他们有现成的函数可以套用，但是在VBA里面的SQL，貌似至今为止，我尝试成功的也就只能硬着头皮做一个笛卡尔积。如果数据量比较大，那将是一个噩梦。噩梦归噩梦，数据还是能算出来的，如果我只是算一个月的库存，顶多就是几秒钟的事，通常情况下如果业务量不大，一秒就差不多了，但是如果要算一年的数据，那就要跑上几十秒。在VBA层面需要跑几十秒，而如果在PQ里我简直不敢想象得多久。

试过VBA，试过PQ，在PQ里我知道我要什么，它的透视和逆透视功能让我省掉很多麻烦，但这两个便捷功能也会默认带出一些意想不到的反效果，比如默认透视的是来源去向，万一筛选区间只有入没有出，但后续处理又默认有出入，这就会卡住。Excel 的SQL里，透视就是最后一步，所以如果中途要实现这种功能只能通过添加条件字段，手动添加字段的好处是不会有PQ透视法的那种透视不出来后面没法干。就可控程度来说，VBA更容易，能把多个操作在一步里秒杀实现，比如修改某个字段的数据和增加某个字段，我就可以把它们在一步里实现，外加同时搞个什么排序。这些步骤在PQ里面，如果不是高级玩家用嵌套的方式，也就只能一步一步慢慢来。我不知道，PQ里面嵌套一步到位跟一步一步慢慢来到底效率差了多少。估计这会有运行时间的差别，但到底差别了多少，这个我没有研究过，因为我还没到的那种可以混搭在一起，一步到位的水平。处理同样的数据，使用类似的步骤，PQ就是比VBA要慢，我也不知道到底慢在哪里，为什么会那么慢？其实数据量不大，但关键是PQ载入的时候很容易出错，但那个出错到底是什么，没人说得清，因为上一次刷新不行，下一次刷新可能又可以了。在VBA里，除了去年年末的某段时间，我经常出现这样那样的奇怪现象，其它时候基本上行就行，不行就是不行。不会出现同一个数据，同一个宏，前一次可以，后一次不行。在PQ里可能得转上半分钟以上的事情，在VBA里非常有可能0.5秒以内就解决了。以前做字幕的时候，我就知道人的反应时间通常是0.3秒，如果一个VBA脚本只需0.3秒就能结束战斗，对普通人来说，那就是眨眼的事而已。

以前我没想过要这么干，以前想着怎么方便怎么来，但是当VBA有点上瘾了以后，我逐渐的把之前用PQ处理的东西全部都用VBA的方式再整了一遍。出来的效果非常好，干净利落快如闪电。让我觉得舒服的是VBE界面是被我调整过的，调整过VBE的布局和颜色，但是在PQ里，那个小得要死的高级编辑器字体实在让我看得很不舒服，但通常某些高端的功能只能在那里敲代码，所以这就很痛苦。

不把某些事完成，心里总会一直念惦记着，把这些事情都干完了，我就可以好好睡觉。

进一步优化和debug

✇我的天
xrspook

当年今日

又花了整整一天的时间去改进之前的两个转换程序，一个是用PQ写的，另外一个是用VBA写的。之前以现有的数据进行测试，没有发现问题，但实际上今天再去纠结，还是有个问题，就是当业务类别为轮换，出库的时候损耗的计算方式。损耗应该放在商品粮的账本，这个没有问题，之前也是这么处理的，但是商品粮的账本还有一个。储备粮油转入，这个东西就应该包含损耗和销售两方面的数据。之前只包含了销售的数据，忽略了损耗的那一部分。同样，在储备粮的账本，在转作商品粮油的数据那里也应该包含商品粮账本里面的损耗数据。这个东西平时做的时候一定会记得，因为单仓数据如果处理不到位无法清零，但是当要考虑的事情有很多的时候，就忘记了。在做这个程序的时候，我就已经考虑到这种损耗是一个很特殊的情况，但是我却没有进一步的考虑到这个东西特殊到要一变成三，通常情况下，一变二就可以了。

除了这个问题，以我现有的数据，基本上那两个程序都能运行出我想要的效果，但实际上，今年到现在为止，单位产生的那些数据还有一些业务类型没有包含进去，那些业务类型有些我可能会用到的，有些我是几乎用不到，但我用不到，不代表其他人也一定不会用到，所以从大的层面考虑，我还要把那些东西都考虑进去。

之前无论是在PQ还是VBA，某些字段的生成实际上是条件筛选，有可能是一个条件，也有可能是多个条件，那些条件里面会有很多个情况。在PQ里做条件筛选，还有个填写界面，但是在VBA里就纯粹靠iif的不断套叠。首先你得知道怎么套叠，然后当你套到一定程度的时候，自己也会被套进去，比如数着数着括号就对不上了，什么逗号双引号之类的偶尔也会制造幺蛾子。使用这种套叠可以实现我想要的效果，但是真的非常虐，而且一旦要进行数据维护，那简直就是个深渊，所以首先我想到的是要不要做另外一个索引的表，通过左外连接的方式指定某些字段必须匹配，然后就能获得我想要的新增字段。从可维护性来说，这样非常好，从代码的实现来说，这也很方便，但是后来我还是决定不在VBA里面实现这种左外的索引和直接在原始的表格里面就索引数据得出一个大表，然后再用大表进行后续的整理，因为要处理的大表其实数据不多，一年肯定不超2000条。之所以要这么干，首先是因为我考虑到可能使用这套方案的人会更容易接受这种直观生成的大表，他们可以直接核对数据，如果觉得不对，可以进行手动更改，但如果我把那个东西做在了VBA层面，程序运行不出来，或者运行出来的效果不是大家想要的，那么需要结果的那个人肯定不知道该怎么办。这种直接通过Excel的索引，先得出一个大表的方式，同样也会让PQ的程序不那么复杂，不需要搞那么多条件筛选。虽然PQ的条件筛选有界面，可以下拉选择，但需要选择的东西多了，很容易就会选错。

最后，事实证明我的这个做法是合理的，我把需要考虑的因素全部都考虑进去用全面的测试数据都模拟过以后，发现两个程序都能满足我的要求。当然了，在最终成功之前，我经历了不知道多少debug。你永远都不知道你会被什么卡住，又或者在什么地方被卡住，但被卡的次数多了，你就会觉得这很正常，继续死磕就行。

怎么就null了呢

✇我的天
xrspook

当年今日

周三的下午做了一个决定，周三的晚上可以说10点半开始，我觉得整个人都崩溃了。过了12点去睡觉的时候，我感觉整个人毫无睡意，躺在床上，翻来覆去，无论如何睡不着。所以刚刚躺下，我就又重新打开了微信，发出了一段的吐槽，但即便这样还是不解恨。接下来，我甚至都不知道自己是几点睡着的。满脑子都是工作上吐槽的东西，我感觉周三的晚上我就没怎么睡着过，但居然小米手环说我也有接近一个半小时的深睡时间。我不知道那个是怎么算出来的，但估计也睡着了一些时候，因为当我再次翻来覆去的时候，脑子里的东西好像少了一些，也变化了一些内容。我感觉周四一整天肯定会非常糟糕，但实际上却没有想象中的那么难。早上起床的时候不太糟糕，白天的时候也不太糟糕。周四要解决20级消消乐，搞到中午1点30才终于结束，午睡的时候好像也是迷迷糊糊，没怎么睡着，但起码脑子不会有那种大脑缺氧集中不了精神，做不了事的感觉。之所以这样啊，大概是因为周四早上吐槽完以后，我又进行了一番PQ的奋战，把最后的那个记账凭证部分也搞出来了。

其实前一天，我已经把另一个部分的记账凭证搞出来了。晚上洗澡又或者说睡觉的时候，我在考虑最后的这部分该怎么操作，所以其实整个思路其实都已经有了，最后就只是如何实施而已。最后的那个部分能实现我想要的效果，但是有一个我想不明白，当我添加某个条件列的时候，为什么在某个条件之下，明明我已经设定了数据，但是当我保存出去刷新的时候却没有数据？那个东西不知道为什么会默认把我设定的数据变成null，于是那里莫名其妙空了。第一次遇到的时候，我觉得是不是我手误，但是当我好几次都遇到，尤其是周四早上已经修正了一次，又遇到的时候我就觉得这大概率不是我的问题。但是为什么其它条件就不会出现这种状况，唯独在损耗这个条件判断上就会出现这种问题呢？但不是每个条件判断损耗的时候都会这样。我当然希望这只是因为我没有保存导致的，但不可能每次都是我没有保存出现这种问题，另外一个我觉得不是我问题的原因是如果条件判断最后出的结果我没有填写的话，那个地方应该是空的，当我发现刷新出来的数据少了一些东西进去看的时候，发现那里填写了null，我不可能在那里填写这种东西。Power Query在这个问题上到底有什么毛病呢？

PQ方案出来了以后我发现几乎没有需要文本合并的部分，所以这个方案完全可以用VBA替代。最大的区别我感觉是再用 PQ的时候，我在添加条件选择的时候会很方便，如果要在VBE里面写一大串的SQL代码，如果要体现回车还挺麻烦，但不会车和退格会造成一些编写和阅读上的困难。如果我是在一个数据库软件里写SQL，而不是在VBA里，以标准格式写SQL，没有这种烦恼，回车退格什么的东西都是很标准化的。但关键是要我在VBE里面写SQL，那个SQL就像变成了纯文本一样。因为在这个转化方案里需要用到大量的条件判断。想清楚那些条件判断，结果就出来了。我在PQ里面进行条件判断编写不需要用自定义模式，直接有一个条件判断的界面，在那里很容易就能实现功能。因为那个界面就像一个多条件的筛选框。以前我从来没试过这样，这一次添加条件列帮了我不少忙。

因为人已经恢复了平静，我也就不想继续吐槽了。

❌