将 Excel 多列数据合并为一维表的三种方法,你 Pick 哪一个
消息来源:baojiabao.com 作者: 发布时间:2024-05-17
今天,我们的分享跟数据结构有关。
数据结构的优化,是数据分析处理的基础性工作。
有时,我们拿到的表格,它的数据结构,并不利于后续的数据处理工作。
这时候,我们就需要对数据结构,进行适当的调整、优化。
比如,我们有时候会遇到下图左侧所示的数据结构,这样的结构,适合阅读,但后续分析工作会很不方便。
所以,我们就需要将其转换为右侧所示的结构。
那么,我们要如何完成这种数据结构的转换呢?
复制粘贴?这可是要加班的节奏啊!
不过不用急,解决的方法还是不少的。
函数法
我们可以通过 IF、SMALL、RIGHT、TEXT、ROW、COLUMN、INDIRECT 等函数的组合,来实现我们想要的效果:
我们先解决姓名列,在 G2 单元格输入公式:
=INDIRECT(TEXT(RIGHT(SMALL(IF($A$2:$D$6<>"",ROW($2:$6)*10+COLUMN($A:$D)*10001),ROW(A1)),3),"r0c0"),FALSE)
注意:这个是数组公式,需要同时按【Ctrl+Shift + 回车键】来完成输入。
部门列在 F2 单元格输入公式:
=INDEX($A$1:$D$1,1,MATCH(1,MMULT(TRANSPOSE(--($A$2:$D$6=G2)),ROW($A$1:$A$5)^0),0))
注意:这也是数组公式,需要同时按【Ctrl+Shift + 回车键】来完成输入。
这样,我们就达到了想要的效果。
这种方法,所用的函数比较多且公式复杂,特别是在数据量较多的情况下,由于是数组公式,运算量会很大,会有卡顿现象。
对于这两个公式,我们今天就不展开讲解了,因为会占用大量的篇幅。
下面,是更好的方法!
错位引用法
❶ 在 A7 单元格输入公式,然后向右、向下拖拽;
=B2
❷ 将 A2:A21 的数据复制到 G2:G21 中,注意,在粘贴的时候要使用选择性粘贴 - 数值来进行。
❸ 这时候,姓名并不是首尾相连的,中间还夹杂着很多 0,我们按下【Ctrl+G】组合键,打开定位功能,单击定位条件,选择「常量」,将数字以外选项的勾都去了,单击确定。
❹ 这时候,内容为 0 的单元格,已经全部被选中,鼠标移动到任意一个 0 值的单元格上,依次单击:
鼠标右键 - 删除 - 下方单元格上移 - 确定,完成删除 0 值单元格的操作,并作适当的格式调整。
对于部门列,依旧使用上述公式来完成,即:
=INDEX($A$1:$D$1,1,MATCH(1,MMULT(TRANSPOSE(--($A$2:$D$6=G2)),ROW($A$1:$A$5)^0),0))
注意:这个是数组公式,需要同时按【Ctrl+Shift + 回车键】来完成输入。
数据中夹杂 0 值的原因,是因为,我们的原始数据源中,有空单元格存在:
这种方法,相较于公式法来说已经简单了很多,但依旧不适用于处理大量的数据。
那么有没有一种快捷、高效又能从容应对大量数据的方法呢?
答案是肯定的,那就是我们的 Power Query。
Power Query 法
❶ 鼠标定位到数据区域中的任意单元格(本例为 A1:A6),点击「Power Query」-「从表 / 范围」,在弹出的对话框中勾选「表包含标题」,单击确定;
❷ 此时,会打开 Power Query 的主界面。
单击第一列的标题,按住【Shift】,再次单击最后一列的标题,这样我们可以快速的选中全部列;
❸ 单击「转换」-「逆透视列」,在下拉列表中选择「逆透视列」,完成数据结构的转换;
❹ 这时,我们看到,相同的部门并没有集中在一起,并且「部门」和「姓名」两列,对应的的标题分别是「属性」和「值」。
这是 Power Query 默认的标题名称,并不是我们想要的。
我们分别将「属性」重命名为「部门」,「值」重命名为「姓名」,然后单击姓名右侧的下拉按钮,选择升序或降序,对部门列进行排序,以便将相同的部门集中在一起。
❺ 单击文件 - 关闭并上载至,在弹出的对话框中,显示方式选择 - 表。
位置的话,这里我们选择,放在现有工作表的 F1 单元格处,当然你也可以根据自己的需要,选择新建工作表;
❻ 最后,可以根据自己的需要对其进行格式、字体等的进一步调整、美化。
很高效有没有?
最后偷偷告诉你,用这个方法转换出来的表,虽然没有用任何函数,但同样可以动态更新哦!
本文来自微信公众号:秋叶 Excel (ID:excel100),作者:大表弟,编辑:竺兰
2023-01-06 14:37:46相关文章
- 美国法院裁定阿里须为Squishmallows玩具侵权案答辩
2023-12-28 19:59:34
- 小米汽车传员工3700人 雷军称小米汽车不可能卖9万9
2023-12-28 19:41:57
- 国家新闻出版署:认真研究《网络游戏管理办法(草桉徵求意见稿)》关切 实行前进一步完善
2023-12-28 19:14:56
- 天猫新规可以无条件申请“仅退款”了?淘宝天猫又离狗多多零元购近了一步
2023-12-28 18:57:55
- 印度以打击金融犯罪为由逮捕了两名 vivo 高管
2023-12-26 16:49:01
- 在国外微信收不到国内信息?微信和WeChat将被拆分
2023-12-15 10:40:15
- 苹果iPhone15 系列手机发布最新消息 预计上市发布时间9月
2023-08-06 23:21:02
- 华为将发布鸿蒙HarmonyOS4操作系统 功能五大升级支持设备清单
2023-08-06 23:17:37
- 整治自媒体网红账号 400万粉丝网红发布擦边视频被无限期封禁
2023-07-12 09:56:09
- 网传微信文件传输助手是真人是真的吗?微信官方回应
2023-06-27 15:53:32
- 电信移动送手机成了“信用购”?你上了运营商的贷款套路了吗?
2023-06-12 17:18:55
- 中国电信广东地区崩了无信号 客服回应已在核实处理
2023-06-08 15:39:04
- 消息称小米新能源汽车价格表正讨论定价区间:双版本不同配置,高配或超 35 万元
2023-03-06 12:56:03
- 华为因制裁被传或分拆剥离手机业务? 内部人士回应:可能性不大.
2023-03-05 23:26:41
- OPPO正式发布安第斯智能云,让终端更智能
2023-02-24 16:02:27
- 华为与OPPO签订全球专利交叉许可协议 包括5G蜂窝通信专利
2023-02-24 16:02:26
- 老蛙将推MINI镜头新品:目前未知具体规格 官宣将于12月20日发布
2023-02-24 16:02:26
- 首发全新35mm定制光学系统 努比亚Z50性能同样强悍
2023-02-24 16:02:25
- Redmi K60屏幕细节曝光:全系标配2K护眼柔性直屏+5000mAh大容量电池
2023-02-24 16:02:25
- OPPO Find N2今天发 合金金属折叠屏更轻了
2023-02-24 16:02:24