逆透视就是降维处理,是透视的逆向操作,我们之前讲过两种逆透视的方法:
推荐使用Power Query逆透视,非常方便,就是一键操作。
今天我们介绍一种用公式来实现逆透视的方法:INDIRECT+TEXT组合公式
我们要把4列数据逆透视成一列数据,需要编写什么样的公式来实现?
我们要用到6个函数,来完成组合公式:
我们来做几个小试验,来了解这个函数的具体用法:
在任意单元格输入:
=INDIRECT("R2C1",)
我们可以得到结果是“姓名1”
这是种简写的方式,完整的应该这样写:
=INDIRECT("R2C1",FALSE) 或=INDIRECT("R2C1",0)
以上三种写法都一样的结果,A2单元的R1C1模式的地址就是R2C1。
我们又试了一下这个公式:
=INDIRECT("R2C01",)
结果完全一样,这说明R2C1=R2C01=R02C001
只要是R与C后面的数值相等,得到的结果就相等。
我们在排座位的时候,通常用几排几号来表示具体座位,2排1号,为了方便会简写成201,假设一排最多有99个座位的情况,我们就用01~99这样预留两位数,就够了。我们给这些单元格也可以用同样的方法来排列:
这个公式也很好写:
=ROW(A2)*100+COLUMN(A2)
行号乘以100加上列号就能得到这样的地址,问题是我们要的是R2C01不是201,这要怎么办呢?我们在Excel中有一种障眼法的自定义格式,明明是数值,可以自定义成我们需要的文本的样子,例如100可以显示成100人:
大家可以学习一下这个TEXT函数,与单元格设置里的自定义设置是一样的。
我们就用这个功能把所有的地址转换一下:
=TEXT(ROW(A2)*100+COLUMN(A2),"R0C00")
到这里我们关于R1C1地址的问题就基本了解了。
分成两种情况:
不区分是否为空,直接逆透视成一列:
一种是去掉空,逆透视成一列:
这就涉及到一个单元格位置有效性判定:
=SMALL(ROW($A$2:$D$7)*100+COLUMN($A$2:$D$7),ROW(1:1))
=SMALL(IF($A$2:$D$7<>"",ROW($A$2:$D$7)*100+COLUMN($A$2:$D$7),9^9),ROW(1:1))
如果为空单元格地址就为9的9次方,SMALL就是从小到大排序,ROW()下拉时会逐渐增大。
不去空值直接逆透视的公式组合:
=IFERROR(INDIRECT(TEXT(SMALL(ROW($A$2:$D$7)*100+COLUMN($A$2:$D$7),ROW(1:1)),"R0C00"),),"")
去空值逆透视公式组合:
=IFERROR(INDIRECT(TEXT(SMALL(IF(($A$2:$D$7<>""),ROW($A$2:$D$7)*100+COLUMN($A$2:$D$7),9^9),ROW(1:1)),"R0C00"),),"")
页面更新:2024-04-25
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号