目录
背景:
业务上很多数据需要将数据按照相同组,从竖向排列转为横向排列,即一维表转多维表,具体如下:
原数据形式:
期望整理成的数据形式:
目前的解决方案:
1.数据透视表:
如果值为数字,则直接用数据透视表即可,但是现有数据表不支持显示文字,虽然可用Power Pivot等进行配置,但是学习成本高,不适用类型较多的情况。参考文章:excel中,数据透视表值区域如何显示文本-百度经验 (baidu.com)
2.公式:
某些情况可用OFFSET函数进行转换,但是此函数逻辑略复杂,反正我没完全理解。参考文章:Excel中利用OFFSET函数解决很多个分块的一次性转置问题(非纯手动)
3.使用Power Query编辑器的【透视列】功能:
本案例数据应该不适用这个功能,而且Power Query几乎是另一个软件了,学习成本很高。参考文章:Excel同一名称对应的多个数据调整为横向显示【一维二维转换案例】 - 知乎 (zhihu.com)
4.Python
说实话只是想简单整理一下,犯不着学一门编程语言,除非处理大数据。而且理论上有这种需求的业务也不涉及大数据,因为多维表不符合数据库建构思想。参考文章:利用python将excel中的大量分组竖行数据转为横行数据
简单的方案:
思路:
设置排序辅助列,然后筛选每个次序,最后依次复制粘贴
步骤1:创建辅助排序列
使用公式或自动填充,为同组数据加上排列序号,注意把数据较多的组放在前面(可用数据透视表的排序-其他排序选项)。
参考文章:Excel函数 -名称相同序号自动递增(用于重复值排序)
步骤2:筛选序号
依次选择组内顺序
步骤3:复制粘贴
依次将对应次序的筛选结果数据贴入新表格
注意步骤1时一定要把数据较多的组放在前面,才能保证粘贴后行标题可以对应上
步骤4:删除多余字段
删除列标题、组内顺序等用于核对数据的辅助列,整理排版
注意:如果步骤1没有把数据较多的组放前面,或有其他特殊情况(没遇到过),可能导致粘贴时对应关系混乱,可用公式进行横向检查
总结:
理论上此类数据场景不符合数据库构建的思想,Excel数据透视不能显示文字也是基于此原因,但是业务上确实有此需要,而且处理起来超级磨人,其他解决方法的学习成本也很高。
如果数据量较大,或者例行重复工作,可以使用Python方案,或者可以研究下Excel脚本/WPS宏。