2022-11
19

PQ显身手

By xrspook @ 10:11:43 归类于: 烂日记

我也说不准什么时候问题会向我袭来,比如周五的上午,我正在做着普通的事情,突然就被一个同事叫去了,问我某个关于固定资产的Excel文件到底是什么情况。在我印象之中,我折腾过那个东西,但是具体做了什么,我还真想不起来,因为那是一个非常定制的效果,所以我在那里做了什么我实在不知道,因为完全是根据需求去做某些设定的。明细数据一看就知道肯定就是一开始的表格,但除了那个表格以外,后面的东西是手动输入的还是关联的,我就真不记得了。用的人说那个是关联的,但实际上又好像看不出什么苗头。其中一个肯定是自动生成的,因为那是数据透视表,数据透视表之后,有一个打印的表格。那个东西到底是干什么用的呢?打印的表格里又分了两个超级表。后来在折腾另外一个表格部分数据透视列的时候,我才想起打印的那个表格当年是我用Power Query设定过的。跟数据透视表比起来,那个东西更适合用来做列表类的东西,因为原始数据的编号是空值,所以如果有某两条记录完全一致,数据透视表估计就会把他们组合起来。所以当年我就想出了一个用其中一个超级表作为条件录入另外的那个大表作为数据筛选。这样的好处是除了明细表格以外,其它表格的数据都是查询生成的,虽然他们的表达形式不一样,甚至可以说是定制式的。

至于后来盘点账之类的东西,实际上就是实物账的某一列要进行透视拆分。他们当时根本就没想过可以怎么实现这个功能。无论是之前的使用者还是现在的接收者,都搞不懂实物账跟盘点账有什么不一样。如果只是要把一列透视,自我感觉应该是很简单的,因为以前我就经常用PQ比做这种事,但现在用起来又好像不是我想象中的那样。结果发现之所以这样,是因为以前我用的那些通常都要对数据进行合并。无论是求和类的还是计数类的,通常以求和类的居多,现在用的是不聚合的功能。最终我想出增加一列。然后以那一列不聚合符号作为透视列的依据,然后我再把那些空的单元格全部用空白填充。这样的话就做到了原始数据在一列里有三个分类,目标数据把那个分类分成了三列,最后的效果要在三列对应的单元格上打上勾。效果就是原始数据某一列全部都有三个分类的其中一个,目标数据每一条数据对应的某个分类都有一个勾。说很容易,做我感觉也是很容易的。要实现这个功能,会有一点点绕。用人的思路考虑就是把一个一维表变成一个二维表,实际上也正是在干这种事情,但因为三个分类的对应单元格显示的不是某个数字,而是某个符号。一般的数据透视表不可能实现,经过一些高级设定后Power Pivot可以实现。如果你要用数据透视表表达课程表,一般的数据透视表是无法实现的,因为数据区域是一个标量,只能是数字,不可能是文字,但是在PP跟PQ可以做到。

我的同事的那种烦恼,在我们的这个单位,大概也就只有我能解答了。也不是说我们这个单位的人的能力不行,但是我估计他们不会在Excel的这两个隐藏高级技能上曾经狠下苦功。

2022-10
14

继续挣扎

By xrspook @ 8:59:29 归类于: 烂日记

又是投篮的第2天,我又没有做任何的运动,依然是坐在那里,整个晚上都把心投在Power Pivot上。但这一次我没有上一次那么好运,可以解决困扰了我很长时间的问题,整整一个晚上我都毫无收获。增长的只是我越来越不知道自己在做什么。但跟前两天相比,我没有一直都耗在那里,而是给自己划定一个底,超过那个时间我就回宿舍洗澡睡觉。在睡觉之前我拿出架子上的另外一本关于Power BI的书,之所以我说那是Power BI,因为那本书大半本都在说Power Query,有部分章节是说Power Pivot,然后是二者的融合。但话说回来,我又不能说那是Power BI,因为那本书是基于Excel而不是Power BI的。

就公式参数的复杂程度来说,我感觉PQ要比PP麻烦很多,尤其是虽然你看上去PQ的界面就只有那么几个按钮,但实际上要发挥 PQ的所有潜能,所有东西都必须在高级编辑器里面完成,尤其是一些非常高效的操作。有些结合的步骤,或许你觉得高级的步骤结合起来跟分步没什么区别,的确对一些一般操作来说,这是可以做到的,但是某些步骤必须层层堆套,而且公式里面的参数有很多的时候,让人感觉脑子就不好使了,从表到列到记录到具体数据。以前我曾经死磕过PQ,觉得非常强大,也非常烧脑。经过一堆操作以后,的确可以做到想要的效果了,但问题是其实那样的效率并不高。所以最终我没有采用PQ的方式,而是直接自己写VBA。相对于 PQ而言,我觉得VBA返倒更符合一直以来我学习其它编程语言的思路,比如说一开始接触到的C又或者后来接触到的Python。但和Python相比,VBA里面有很多默认的公式套路,但起码VBA里的公式套路还可以理解,而不像PQ那样复杂到让你觉得望而生畏。

PP让我觉得很烦恼的地方是可能它就那么几个函数,但是它的组合方式却是千变万化的。你可以用同样的代码在不同的地方放置,但效果完全不同,你也可以写完全不一样的代码实现同一个效果。当然,更多时候你想用一堆代码实现某个效果,但是别说出效果,你通常会被卡在公式审核那一步。我觉得PP让我绝望的地方在于我还没办法以它的思路分步执行,我无法预判如果我这么写的话会达到什么效果。当然这只是一个初学者的烦恼,当我把所有规则都烂熟于胸之后,我觉得这一切都不再是问题。PP我觉得是一个我必须越过去的坎。因为那跟大数据有那么一点关系,虽然这又跟大家说经常听说的那种大数据有很大区别。对我来说,几千上万条数据已经是大数据了。当这些数据经过这种盘曲折叠以后,最终的量可能是数倍甚至以次方的形式增加。我小小的脑袋瓜当然不可能预判出这种事情。

大概我现在的这种烦恼,我有一种方式能稍微缓解一点,就是放下自己手头上要解决的问题,纯粹只投入各本教材中最经典的简单案例。当我把那些都融会贯通了以后,再开始DIY我的方案。

2022-10
13

纠结迷茫中

By xrspook @ 9:09:44 归类于: 烂日记

我好像知道自己在做什么,我知道我要什么结果,但是我却好像完全没有头绪我正在做些什么。这句话看上去好像很矛盾。以前,当我用Power Pivot的时候,没有这个感觉,但是当我把《DAX权威指南》越看越多以后,我的茫然感在不断增加。这其中一个很重要的原因是他们在书里主要讲原理,虽然里也有很多例子,但并不是以实例出发。在DAX里,计值上下文是很重要的东西,其中又包括了筛选上下文以及行上下文,筛选上下文对整个模型起作用,行上下文只能迭代单表。这是两句看上去很绕的话,同样让人觉得很类似,但完全不一样的还有度量值和计算列。度量值这个东西,我个人感觉挺飘渺,在没有报表的前提下,我简直不知道该如何考究那个东西,但实际上度量值这个玩意最终无非是某些聚合。以数据透视表的逻辑去考虑,只能放在数据透视表右下角的值里面,所以归根到底那就是某个计算的东西,但是它有可能是某个文字吗?这是有可能的。但到那个时候估计就是不是求和,而是一个计数了。比如说如果你返回东西是一个表,那么估计最终你就只能顶多在外面再套一个函数计算表的行数,然后把那个结果表达出来,但显然你要的不是这个表的行数,而是要这个表本身。但是话又说回来,度量值最终理论上不应该返回这种东西。

以前我觉得计算列很简单,一般的Excel里面也经常会用到,那跟用超级表没什么区别。但是当我知道得越来越多以后,我简直不知道该如何碰那个东西了。因为好像怎么整都不对劲。之所以这样,是因为如果这只是一个Excel的表的话,我知道用什么东西能实现我的功能,但是到了PP里,所有东西都乱套了。但实际上之所以发生这种事,倒不是因为PP真的有问题,相比于度量值,我在计算列上碰到的问题少很多。但我为什么要用PP呢?因为我需要得到的那些结果是单纯的表无法给我的。从一个表查询另外一个表的数据,这个我可以做到,但是通过度量值生成的那个表,再以那个表为基础查询原始表的数据再互相结合这个又该如何做到呢?以前我觉得PP可以把很多表的各种关系联系起来。这样用很方便了。相比于从前的Excel只有用SQL连接,文件换了一个电脑行不通,因为数据库本身是存在第一台电脑的某个地方。

大概这是学习必然会经历的某个境界吧。当我扛过了这个烦恼,我就能重获新生。但在此之前,我不知道我还得挣扎多长时间,还有一个就是实际上我是不是不应该从那本《DAX权威指南》开始看起呢?我应该先找一些比较低端说人话的教程,慢慢去让自己接受这个体系?

学习过好几门计算机语言,也学习过不少理工科的东西,我觉得学习DAX是我这辈子以来学得最纠结迷茫的。

2022-10
11

填充单价

By xrspook @ 9:49:59 归类于: 烂日记

工作出现了些许空余的时光,于是我赶紧继续开始看我的《DAX权威指南》。筛选上下文、行上下文这种东西实在让人非常抓狂。有些时候我是完全理解那种关系到底是什么,但实际上当用起来的时候又好像各处碰钉子。

一步步的学习DAX这没有任何问题,但学着学着自然而然我就会往自己的问题上面靠,于是心就飞走了,然后虽然已经学了100多页,但是感觉我还是找不到思路。我不知道如何在Power Pivot里面把两个日期扩充为一个日期列表。于是我就折腾了一番,这个在Power Query里实现我100%肯定是轻而易举就能做到的。以前我好像做过。支持我使用的方法是修改人家的高级例子,从前只是一步一步按部就班来。以日期扩充之后的表为基础查询模型里的计算库存不是不可以,但是翻来覆去非常耗时间。从前在PQ里计算库存,在情况不那么复杂的情况下弯曲折叠各种填充已经很耗费人力物力,而这种库存的计算实际上在DAX里面是秒杀完成的。这也就是为什么当年我放弃了继续走PQ这条路计算库存以及库存费用。在PP里,计算库存其实我早就已经搞出来了,虽然实际上那个筛选条件我至今都没搞懂。现在的问题是那个阶梯费用表达把我难住了。从简单的几个时间区间生成一堆连续日期单价对PQ来说实在太简单了,但为什么这种事情用DAX去表达却那么的难呢?所以实际上我觉得这个问题跟库存的计算一样,我还没想到那个方法该如何表达,也就是说理论上要做到这个也是很容易的。

如果我的单价用PQ实现,我的库存用PP去实现,最终我只需要很笨地把两边的数复制一下,然后在另外一个地方做一个很弱智的合并,显然这样也能曲线救国,以我现在的知识储备可以这般做到,但显然这不是我想要的效果。这个问题在近期我又开始纠结之前,已经困扰了我一年有多。

虽然不知道最后的那个灵感什么时候才会到来?但我知道那一定会到的。

就在这篇东西口述出来后,那个晚上我什么都没干,没有运动,也没做其它别的,一心就扑在PP里实现单价这个问题上。接近晚上10点,这个问题好像终于有了破解的苗头。用的是LASTNONBLANK函数,网上教程用的是LASTNONBLANKVALUE,但PP里没有这个东西,为什么呢?Power BI里面有但Excel里没有???

LASTNONBLANK ( <列名>, <表达式> ):为表的每行计值表达式,返回结果不为空的最后一个列值。

LASTNONBLANKVALUE ( <列名>, <表达式> ):为表的每行计值表达式,返回结果不为空的最后一个列值所对应的表达式的值。虽然被归为迭代函数,但与 LASTNONBLANK 不同的是,LASTNONBLANKVALUE 的<表达式>参数在筛选上下文中计值,这是由于存在一个隐式的 CALCULATE 将行上下文进行了转换。

https://www.powerbigeek.com/dax-functions-lastnonblank/?f=1
https://www.powerbigeek.com/dax-functions-lastnonblankvalue/?f=1

以我现在的水平我是搞不懂他俩到底什么情况,但Excel里没有我也没办法,把LASTNONBLANKVALUE的东西转化为适用于LASTNONBLANK,这又折腾了好些时间。其实这个东西说白了就是为了向下填充,如果这个地方空白,就用这一列对上一个有数的那个东西向下填充。当然,其实这个东西一开始的意图不是填充,是为了找到最后一条记录,这种东西要准确,通常默认跟时间关联。

虽然道路是曲折的,但我总算在单价这个问题上发现了我觉得应该很简单的实现方式。

2022-09
30

Power Pivot上瘾

By xrspook @ 8:52:56 归类于: 烂日记

对上一次晚上10点之前单位就结束所有业务,晚上11点之前我已经完成了所有事已经不知道是多久以前的事了。晚上11的时候我一个人坐在宿舍在书桌前,关了空调,开着风扇,一边等头发干,一边在看Power系列的书。通常在那个时候我都不会看技术类的书,因为非常有可能越看越精神,然后我就睡不着了。绝大多数情况下,在那个场合,我会看小说,但不知道为什么,前天晚上我选择了拿下一本已经被我放在书架上冷落好久的Power纸质书。那本书里面主要是说Excel里面的Power Query和Power Pivot。一本书要讲好这两个强大的东西,尤其是Power Query,显然不可能讲得那么透彻,但实际上我只是想看PP。办公室我正在看的那本叫《DAX权威指南》,那本书跟我放在宿舍里那本薄薄的2合1来说高端太多了,但是权威指南准确来说是一本翻译的书,我觉得原书的作者以及翻译的人都很用心,所以看上去并没有感觉太别扭,但因为里面所引用的事例全部都是英文的。示例里面用的是英文,但实际上翻译的时候有些地方用英文,有些地方用中文,虽然都很简单,翻来覆去都那几个单词,但是你还是得有一些反应时间,但是宿舍的那本书,完全是中国人写的,里面以一问一答的方式开展教学。一方面我觉得这挺弱智的,另一方面我觉得因为他们的软件以及他们的示例都是基于中文,这样的话我就可以快速阅读很多东西,因为这不是一本翻译的书,所以在某些表述上我会觉得比较正常。如果某一天我也要去教学,我也会用一些比较通俗的话来解释那些比较深奥的东西,但我又明白那些看上去很深奥的东西,实际上是术语,所以正规翻译得那样,但有些时候只要把问题讲清楚,让不懂的人搞懂就可以了。机器有机器的语言,但要把某些东西教会别人,还是得说人话。最高境界当然是用通俗的语言把机器的思路讲清楚讲明白,但显然这不是一件容易的事。

还有一件很尴尬的事,就是《DAX权威指南》非常牛逼,书本配套了大量素材,但问题是学习的时候我应该用什么软件呢?我应该怎么使用那些素材呢?如果是Excel教程,他们会告诉你这本书是基于什么版本的Excel,然后你找那个版本就好了,但是这个东西你可以在Excel的PP里运行,当然了,只有某些版本的Excel才自带这个东西,有些即便自带了,却有一些模棱两可的感觉,好像部分翻译了,但是又翻译得不太到位。在我印象之中,家庭学生版的Office2016有PQ,但是界面有些是中文,有些是英文,后来不知道从什么渠道我才知道理论上Office2016的家庭学生版是没有power系列的。后来当我用上了Office365就再也没有遇到过中英文对半这个情况,而且好像相对而言PQ的启动速度会更快。

Power系列的工具原生在Power BI里出现,但是除了Power BI,其实Excel里某些东西也都能用,我用的是Microsoft365,但PQ很正常,但PP会出现卡顿,当你要调试某个东西的时候,总感觉那不是一个靠谱的窗口。当高手们要在Excel里面使用PP,估计都会装个DAX studio。studio装上后写代码爽快多了,但问题测试度量值要看报表效果,根本没办法在studio里做,所以还是得把studio的代码贴到Excel里。这样显然就很不科学。PQ里有高级编辑器,你在里面敲代码就行了,既然PP也需要这种东西,为啥我就不能在某个地方敲代码呢?如果它内置了一片可以用来敲代码的编辑器的话,我也就不需要跳到studio里实现了。《DAX权威指南》可能是基于Power BI的,但实际上没有明确指定学习这套东西你得用某个软件。光看不练会让人很迷茫,感觉自己看懂了,但实际上用的时候一团糟。

一旦上瘾了,我就完全停不下来了。办公室看,宿舍看,家里也看,幸好从前买的书够多,可以到处放一本。

© 2004 - 2023 我的天 | Theme by xrspook | Power by WordPress