
收到粉丝提问:求解!怎么把横排数据变成竖排数据?
如题,请问怎样能把下图中示例的原始表格变成目标表格,需要将横向排列的数据变成纵向排列,数据量很大。

图中的列表形式,直接通过简单复制粘贴来转置行列是得不到想要的效果的,同时,通过函数公式实现的难度也比较大,而且会比较繁琐。
我们可以使用 Excel 的 Power Query 功能来快速将横排数据整理成三列竖排数据。
步骤一:加载数据到 Power Query
打开包含数据的 Excel 工作表。选择数据范围(包括列标题),例如 A1:K4。转到 数据 选项卡。
在“获取和转换数据”组中,点击【来自表格/区域】,在弹出的创建表对话框中,点击确定,将数据加载到Power Query。


步骤二:拆分和整理数据
1、选择需要逆透视的列
初始数据中会看到列标题如下:分组、人员、体重、人员2、体重3、人员4、体重5,以此类推。
在 Power Query 编辑器中,需要选中所有 人员 和 体重 列(如人员、体重、人员2、体重3等列),可以通过按住 Shift键进行多选,确保选中所有人员和体重列(不包括分组列)。

2、执行逆透视列
选中所有的人员和体重列后,右键点击这些列的标题,选择“仅逆透视选定列”。这将把选中的多列数据转化为两列:属性和 值。
例如,操作后数据结构可能如下所示:

3、重命名和整理列
A. 理解“属性”和“值”列
“属性”列:包含了“人员”和“体重”的标签(如“人员2”、“体重3”等)。
“值”列:对应的是实际的数据(即人员的姓名和体重)。
B. 替换与更改数据类型
选择“属性”列,右键点击,选择 替换值。在弹出的窗口中,将 人员2 替换为 人员,将 体重3 替换为 体重。重复此操作,直到所有展现为 “人员” 和 “体重”。

4. 透视列
接下来,添加索引列,再右键点击 “属性” 列,选择 “透视列”。

在弹出的窗口中,值列选择“值”,展开高级选项,选择“不要聚合”然后点击 确定。
这将为每个分组生成多行,得到正确组合 分组、人员 和 体重。

5. 删除空白行
得到的数据中包含了空白值(null),需要对这些值进行处理。
先选中“人员”列,点击 转换 > 填充,选择 向下填充。
这将向下填充 null 值,使得每个“人员” 的值与对应的“体重”相关联。删除索引列。
然后,点击“体重”列的筛选按钮,选择“删除空”,即可将空白行删掉。

步骤三:加载数据回到 Excel
在 Power Query 编辑器的主页选项卡中,点击左侧的 关闭并加载 按钮。就会将处理后的数据加载回 Excel。最终结果如下:

上述步骤二中的替换,需要一次又一次地使用“替换值”功能,但这会比较繁琐。在数据量大的时候会很费时,我们可以创建自定义列来批量替换,具体步骤如下:
在添加列选项卡中,点击 自定义列。接着,在弹出的窗口中,输入新列名称(例如:新属性),在 自定义列公式 中输入以下公式:
if Text.Contains([属性], "人员") then "人员"
else if Text.Contains([属性], "体重") then "体重"
else null
点击确定,即可批量将 人员2,人员4,人员6...,批量替换为 人员,将 体重3,体重5,体重7...,批量替换为 体重。


通过以上步骤我们就可以将数据整理成所需的格式了。如果在操作中遇到问题,欢迎给小兔留言哦~
想了解更多精彩内容,快来关注