革命性升级!超越透视表与PQ,Groupby蜕变超级逆透视神器

遗忘美丽 2024-07-22 11:42:54

透视我们知道,可以把一个一维表转换为一个二维表。而逆透视是把一个横向的二维表转换为一维表,如下图所示。

以前曾分享过利用透视表进行逆透视的方法,需要启用Alt d p组合键,过程有点复杂。但用Groupby公式可以轻松完成。

=GROUPBY(A2:B11,C2:H11,IF({1,0},SUM,TOCOL(C2:H2)),,0)

这个公式有点奇怪,第3个参数IF部分看上去摸不清头脑,想解释清楚也并不容易。

Groupby第3个参数一般是用简写的函数形式,它会按这个函数进行运算。如下图所示用SUM进行分类求和。(因没重复的,所以结果和原表一致)

=GROUPBY(C2:D8,E2:E8,SUM,3,0)

如果借用if{1,0}或choose把函数和一个字符组合成一个新数组,这个文本就会变成单独的一列,类似给sum加上标识。

=GROUPBY(C2:D8,E2:E8,IF({1,0},SUM,"A"),3,0)

而如果后面是一个垂直数组,则会加多个标识。

=GROUPBY(C2:C17,E2:E17,IF({1,0},SUM,{"A";"B";"C"}),3)

需要注意的是一定要是用分号分隔的垂直数组,这也是为什么一定要用tocol把标题转换成列的形式。

=GROUPBY(A2:B11,C2:H11,IF({1,0},SUM,TOCOL(C2:H2)),,0)

如果第2个参数恰好是多列,则会和文本一一对应。

=GROUPBY(C2:D8,E2:G8,IF({1,0},SUM,{"A";"B";"C"}),3,0)

这就是这个公式的表层原理,你看明白了吗?其实这里面还要更深层的原理,怕说太多同学们会迷糊,理解到这层就够了。

虽然公式理解了,估计还有部分同学还有个疑问,PowerQuery本来就有逆透视功能,一步就完成了,标题却说脚踢PQ,这又是什么意思?

哈哈,当然简单的踢不了,复杂的就可以了,最近WPS中的groupby函数又升级了,支持用choose实现多行标题的逆透视,这点PQ就不容易完成了。上示例:

=GROUPBY(A3:B11,C3:H11,CHOOSE({1,2,3},SUM,TOCOL(SCAN("",C1:H1,LAMBDA(x,y,IF(y="",x,y))),0,0),TOCOL(C2:H2)),0,0)

注:公式中用scan函数填充年份后的空单元格

0 阅读:1

遗忘美丽

简介:自由自在