横向一对多转为纵向一对多,太实用了!

职场   职场   2024-11-06 14:45   河北  


Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能
Super Excel Man
用1%的Excel基础搞定99%的职场问题
做一个超级实用的Excel公众号
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。


这样做向右填充函数公式的时候就正常了



其实重复学习Excel,刚开始是挺难的,但重复的次数多了总会成为习惯,先从坚持一星期开始,你会爱上那种脑子又累,又无限充实的感觉。回顾关键内容,善用图片表达,学会建立联系,拓展深度广度,浓缩关键概念,应用到行动中,善于归纳总结,尝试进行分享。


 

知识点精彩超链接点击阅读

VLOOKUP的新搭档HSTACK函数,实现逆向查询
一个小小的VSTACK函数,就能实现多工作表合并
VLOOKUP最新用法,提取混合内容中的手机号
带超链接的VLOOKUP函数,让查询体验起飞
最新方法来了:按指定的次数重复数据
合并工作表,用两个函数就搞定啦
比VLOOKUP还好用的多条件查找
点击谁就筛选谁,实现筛选自动化
以“数字”作为分隔符,进行分列
点击谁,就对谁自动条件求和
更多实用内容请在号内历史文章搜索 

Excel情报局
Excel表格爱好者,分享一些日常的积累。做一个职场人都能看懂的表格技巧公众号。多学一个Excel小技巧,会让你在职场中多一分底气与自信。(同名视频号)
 最新文章