不要再Ctrl+V了!一列数据转多列,这2招轻松搞定! |
您所在的位置:网站首页 › 怎么将一行数据拆分成多列 › 不要再Ctrl+V了!一列数据转多列,这2招轻松搞定! |
大家好,我是潜伏在很多 Excel 交流群里,时不时冒个泡的小爽~ 在其中一个群里,我看到这样一个问题:如何批量整理标题和链接? 我简单整理了一下(如下表),大致的需求就是:将左表整理成右表的形式。 ▲ 为方便演示,仅展示部分数据如果是你,你会怎么做呢? 群内的小伙伴各自发表了自己的看法: 用错位筛选?手机号码都有 1 开头,这个方法可行! 借助单元格引用的方法?这个方法可行! 上面的方法,都是很不错的方法。 唯一的缺点就是:一旦我们新增或者修改数据源内容,所有操作需要再重新操作一遍。 所以本文跟大家分享两种方法,目的是在新增或者修改数据源时,能够动态更新数据,下面就跟我一起看看吧~ ❶ Index 函数索引法 ❷ PowerQuery M 函数法 Index 函数索引法 我们先来看看具体的操作~ 如下图,在 E3 单元格输入如下公式: =INDEX($B$2:$B$11,ROW(A1)*2-1) 在 F3 单元格中输入如下公式: =INDEX($B$2:$B$11,ROW(A1)*2) 只需两个函数公式,就搞定! 下面我们简单说一下公式原理~ INDEX 函数说明:INDEX 函数可以返回指定的行与列交叉处的单元格引用。 =INDEX(区域,行数,[列数],[区域数]) 案例中公式如下: =INDEX($B$2:$B$11,ROW(A1)*2-1) 公式中,数据区域为$B$2:$B$11,姓名列就在绿色区域内 1,3,5,……的位置上。 所以我们需要构造等差序列 1,3,5……,对应的公式如下: =ROW(A1)*2-1 ROW(A1)表示行数为 1,ROW(A1)*2-1=1*2-1=1。 ROW(A2)表示行数为 2,ROW(A2)*2-1=2*2-1=3。 ROW(A3)表示行数为 3,ROW(A3)*2-1=3*2-1=5。 同理,我们要得到手机号,需要构造等差序列 2,4,6…… 等差公式为=ROW(A1)*2,所以整个函数为: =INDEX($B$2:$B$11,ROW(A1)*2) 👉 思路总结: 通过 Row 函数,构造等差序列 1,3,5……,用 Index 函数索引就可以得到姓名列,通过使用 Row 函数构造等差序列 2,4,6……,用 Index 函数索引即可得到手机号。 到此,Index 函数的方法就介绍完了,下面我们来介绍一个 PowerQuery 的 M 函数法,一定要耐心看下去! PowerQuery M 函数法PowerQuery M 函数法 我们先来看看具体的操作步骤: ▋第一步:将数据源导入 PQ 编辑器中 选中表格区域-选择【数据】选项卡-【自表格/区域】-出现创建表对话框,按住【确定】按钮。 此时表格就导入 PQ 编辑器啦~ ▋第二步:将数据列深化为列表选中数据列 鼠标右键数据列-选择【深化】或者选择【转换】选项卡下【转化为列表】; PS:深化和转换为列表的目的都是:将当前表中的某列数据提取出来形成列表,方便后续进行操作。 此时就变成列表啦↓↓↓ ▋第三步:每两条信息拆解为一个 List 单击 ,fx 在右边的编辑栏中添加公式,公式如下: = List.Split(数据列,2) 此时数据就被拆分成两个元素一条信息啦~ ▋第四步:将拆分后的 List 转化为 Table 这一步要用到 Table.FromList 函数。 公式如下: = Table.FromList(自定义 1,each _,{"姓名","手机号"}) 操作动图: ▋第五步:实时更新 利用 PQ 做法,我们可以做到实时更新~ 下面我们来简单介绍一下案例中所涉及的 M 函数。 ❶ List.Split:列表拆分 = List.Split(list,pagesize) =List.Split(列表,每次拆几个) Split 是分开的意思,List.Split 的意思就是将列表按照每 N 个拆开,形成单独的 List。 我们的数据是每隔 2 个元素为一个完整的信息,所以需要用 List.Split 函数进行拆分。 ❷ Table.FromList:从列表转换到表 =Table.FromList ( list, 可选 对列表的处理方式 as list , 可选-对应的标题,可选-null 的默认值,可选-额外的值处理方式) 拆分完后,我们需要将 List 转为 Table,所以需要用到 Table.FromList 函数。 = Table.FromList(自定义 1,each _,{"姓名","手机号"}) 第一参数:是一个 List,这里的自定义 1 也就是上一步骤 List.Split 拆分后的列。 第二参数:对上一步骤的操作,由于这里没有需要处理的,所以直接写 each _。 第三参数:返回表后的列名,列名为{"姓名","手机号"}。 👉 思路归纳: 将表中的数据列深化为列表,是因为每 2 个元素为一条信息,所以可以用 List.Split 函数将每 2 个元素拆分成一个 List。 最后用 Table.FromList 函数将拆分后的 List 转换为 Table。 到这里,M 函数的做法也就讲完了~ 总结一下 本文介绍了两种整理错位数据的方法: ❶ Index 函数索引法最常见的做法,这是 Index 函数的经典用法之一,通过构造有规律的索引值进行索引。
❷ PQ 的 M 函数做法,需要认真研究。 利用深化将查询表中的某列数据提取出来形成列表;利用 List.Split 函数对列表进行拆分,使得每 2 个元素形成一个 list;利用 Table.FromList 将 List 转换为 Table。 这两种方法在新增或者修改数据源时,都能够动态更新数据! 👉 考考你: 案例中是每两条元素作为一个信息,如果是三条元素,大家会怎么做呢? 哪里可以跟秋叶一起学 Excel?↓↓↓ 限时福利——加入秋叶《3 天 Excel 集训体验营》,有大神带你学习表格飞速排版、数据高效整理、图表美化设计……!!和志同道合的小伙伴一起交流进步~ 秋叶《3 天 Excel 集训体验营》 课程原价 99 元 但只要你是秋叶 Excel 的读者 就能限时 1 元秒杀!! 仅需 3 天 你就可能成为 Excel 高手! 赶紧扫码抢课吧!! 优惠名额有限,先到先得! 现在扫码报名 还能免费领《35 个函数使用手册》! *广告 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |