excel学习库

excel表格_excel函数公式大全_execl从入门到精通

Excel怎么把横排数据变成竖排数据?

收到粉丝提问:求解!怎么把横排数据变成竖排数据?

如题,请问怎样能把下图中示例的原始表格变成目标表格,需要将横向排列的数据变成纵向排列,数据量很大

图中的列表形式,直接通过简单复制粘贴来转置行列是得不到想要的效果的,同时,通过函数公式实现的难度也比较大,而且会比较繁琐。

我们可以使用 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...,批量替换为 体重。

通过以上步骤我们就可以将数据整理成所需的格式。如果在操作中遇到问题,欢迎给小兔留言哦~

想了解更多精彩内容,快来关注

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

«    2024年12月    »
1
2345678
9101112131415
16171819202122
23242526272829
3031
控制面板
您好,欢迎到访网站!
  查看权限
网站分类
搜索
最新留言
    文章归档
      友情链接