Excel情报局
职场联盟Excel
大家好,今天我们来解答一个后台粉丝留言咨询的问题:如何将一维表横向一对多数据转换为纵向一对多数据?这个问题很简单且思路很好,对于使用较新版本的WPS表格或OFFICE EXCEL的用户体验非常良好。
如下图所示:
AB两列表示“省”与“地市”名称,表格布局为典型的一维表。同一个省份名称可能对应多个地市名称,我们总结称之为“横向一对多数据”。最终要转换为右侧表格的模式:列标题分别为各省份名称,每列省份名称下面则显示对应的全部地市名称,我们总结称之为“纵向一对多数据”。实现这样的转换该如何做呢?
文章开头我们就说了,原理非常的简单,使用新版本WPS表格或OFFICE EXCEL的用户会非常容易的解决此类问题,下面我们来具体看步骤。
首先输入UNIQUE函数:
=UNIQUE(A2:A7)
UNIQUE函数是Excel中的一个强大函数,可以快速从数据列中提取唯一值,帮助我们轻松去除重复项。
函数最简化的语法:
=UNIQUE(范围)
其中“范围”是指你需要去除重复项的数据区域。
所以我们使用UNIQUE函数会得到A2:A7区域内的唯一值列表,结果以数组溢出显示:
{"河北";"河南";"山西"}
由于UNIQUE函数会得到A2:A7区域内的唯一值列表且结果显示为纵向的,所以我们要想办法转置为横向的。
我们使用TRANSPOSE函数:
=TRANSPOSE(UNIQUE(A2:A7))
TRANSPOSE函数是Excel中用于将行数据转换为列数据,或将列数据转换为行数据的函数。
所以我们通过TRANSPOSE函数将UNIQUE函数返回的纵向数组转换为横向数组。
至此3列列标题省份已经制作完成。
我们继续输入FILTER函数:
=FILTER(B2:B7,A2:A7=D1)
FILTER函数作用是筛选符合条件的单元格。
语法:
=FILTER(要返回内容的区域,指定的条件,[没有记录时返回啥])
所以我们使用FILTER函数筛选B2:B7区域内的地市数据,当符合A2:A7区域内省份与D1单元格省份相同这个条件时,执行对B2:B7区域内各省份对应的地市名称筛选。
我们得到“河北”对应的所有地市名称,但是直接向右填充公式会返回错误值。
原因就是参数没有加上绝对引用。
所以应该这样做:
=FILTER($B$2:$B$7,$A$2:$A$7=D1)
由于公式向右填充时,第一参数B2:B7与第二参数A2:A7部分要保持固定不变状态,所以要对其加上绝对引用变成$B$2:$B$7与$A$2:$A$7。
这样做向右填充函数公式的时候就正常了。
〖往期知识点精彩超链接点击阅读〗