逆透视:二维表转一维表!你可能会用到!

你好,我是刘卓。欢迎来到我的公号,excel函数解析。逆透视是power query中的功能,非常的好用,可以将表头的信息转化到记录中,常用的就是将二维表转为一维表。今天就来分享下如何用函数做出逆透视的效果。

下图左表是数据源,它是一个二维表,记录的是各位同学各科目的分数。现在的要求是将其转为一维表,结果如右表所示。

为什么要将二维表转为一维表?因为二维表不方便数据处理和分析,用数据透视表的时候也要求数据源是一维表。
-01-

函数法

用函数做这样的转换还是比较难的,一条公式是很难完成的。我们可以分多条公式来完成。

在G2单元格输入下面的公式,按ctrl+shift+enter三键,下拉填充。就将姓名列做好了。

=IFERROR(INDEX(A:A,SMALL(IF(B$2:D$6<>"",ROW($2:$6)),ROW(A1))),"")

此公式可以看做二维数组的万金油公式(一对多查询),由于时间关系,我就不一步步拆解公式了。

对于已经入门的小伙伴,看了我这么久的文章,我相信你肯定会拆解公式了。还未入门的小伙伴也不要着急,慢慢积累,先从基础抓起,学会单个函数的用法。慢就是快。

在H2单元格输入下面的公式,按ctrl+shift+enter三键,下拉填充。就将科目列做好了。此公式用的是“加权”的思想,我之前也分享过。

=IFERROR(INDEX($1:$1,MOD(SMALL(IF(B$2:D$6<>"",ROW($2:$6)/1%+COLUMN(B:D)),ROW(A1)),100)),"")

在I2单元格输入下面的公式,按ctrl+shift+enter三键,下拉填充。就将分数列做好了。此公式在之前的文章《万金油公式在二维数组中的应用》中有详细的拆解步骤,点击蓝色字体查看。

=IFERROR(INDIRECT(TEXT(SMALL(IF(B$2:D$6<>"",ROW($2:$6)/1%+COLUMN(B:D)),ROW(A1)),"r0c00"),),"")


-02-

vba代码法

如果数据源的数据比较多时,转换为一维表后数据就更多了。这样的情况下,用函数来做就比较麻烦了,会很卡,可以选择用pq或vba来做。
下面就来分享下vba代码的方法,其实就是找规律。二维表是如何一步步变为一维表的,找到这个规律,代码就不难。我将代码产生的结果放在sheet2中。点击按钮,即可得到结果。
代码如下:
链接:

https://pan.baidu.com/s/1lJI4XmnNlgBqUKnHBKD6lg

提取码:gkwc
(0)

相关推荐