在 Excel 当中,有一项功能很有意思,它的名称叫【分列】,但它能做的事情可不仅仅只是分个列,有些看上去和分列功能八竿子打不着关系的疑难问题,也可以用分列来解决。
如果要提名 Excel 当中最喜欢管闲事的功能,那就非它莫属了!
Excel 中的【分列】功能位于【数据】选项卡下「数据工具」组中,如下图所示。
当将鼠标移动悬停到【分列】命令时,会有该命令的提示说明。
分列:将单列文本拆分为多列。
例如,您可以将全名列分隔成单独的名字列和姓氏列。
您可以选择拆分方式:固定宽度或者在各个逗号、句点或其他字符处进行拆分。
可能你还不熟悉该命令的提示说明,所以一起先来看看它的基本用法吧。
01
按照分隔符将单列数据分隔成多列
分列的最基本用法就是将一列以固定分隔符分隔的数据进行分列,比如下面 A 列数据是以 \ 分隔符分隔的,我们可以用分列功能将其按照 \ 分隔符分成多列。
操作步骤
❶ 先选中要分列的数据,在【数据】菜单栏下选择【分列】,默认选择「分隔符号」,然后点击【下一步】。
❷ 勾选「其他」,在后面的输入框中键入 \ ,再点击【下一步】。
❸ 默认设置,点击【完成】。
02
按照固定宽度分列
我们都知道,身份证号的第 7 位到第 14 位数字是出生日期。
那么如果我们需要从一段 18 位的身份证号中提取出生日期,也可以利用分列功能快速实现。
如果有一列身份证号码,提取出生日期可以按照固定宽度对身份证号码分列。
操作步骤
❶ 先选中数据列,在【数据】菜单栏下选择【分列】,选择「固定宽度」,点击【下一步】。
❷ 在「数据预览」下建立分列线,具体操作办法上面有提示,这里是在第 6 位数字和第 7 位数字间以及第 14 位数字和第 15 位数字间建立了分列线,然后点击【下一步】。
❸ 在我们画好线的这三列中,对于我们不要的第一列和第三列,在「数据预览」中分别选中它们,然后勾选上面的「不导入此列(跳过)」。
❹ 选中中间的出生日期列,勾选上面的「日期」,在「目标区域」里选择要生成新列的首个单元格位置,点击【完成】。
好啦,以上介绍的都是分列的基本用法,在 Excel 长期的发展过程中,众多 Excel 高手还挖掘出了分列的很多另类用法,用于解决某类特殊的问题,可以起到意想不到的效果,下面跟我一起看看吧~
03
用分列解决 SUM 函数结果总是为 0 的问题
当数字以文本形式存储时,求和公式无法对其进行求和,结果总是显示 0。这时候可以用分列功能快速地将文本数字转换为以数值存储的形式,这样 SUM 函数就可以计算出正确的结果。
04
用分列解决 VLOOKUP 函数返回#N/A 的问题
18 位身份证号码的前 6 位数字对应的是我国的行政区划代码,如下图所示,用公式查找对应的行政区划名称时,结果却返回#N/A。
我们输入的公式为
=VLOOKUP(LEFT(C2,6),A:B,2,0)
C2 单元格内的身份证号码的前 6 位是 110101,对应的明明是 A3 单元格的行政区划代码,可结果却找不到,返回#N/A。
这是因为 A 列的行政区划代码是以数值形式存储的,而 LEFT(C2,6)函数返回的是文本形式的 110101,VLOOKUP 函数查找时按照对应的格式查找,即数值格式来查找,没有找到文本形式的 110101,所以返回#N/A。
想解决这个问题,也可以用分列功能快速地将 A 列以数值形式存储的内容转换为以文本形式存储。转换之后,VLOOKUP 函数就能按照对应的格式查找到对应的结果了。
当然,除了使用分列功能,我们也可以通过修改函数公式为 1*LEFT(C2,6),强制将 LEFT(C2,6)函数返回的文本数据转换为数值形式,从而查找到正确的值。
05
用分列解决日期、时间格式无法识别的问题
在创建含有日期字段的数据透视表时,数据表面上看上去都是日期,但是却无法将日期字段分组。如下图所示,【分组字段】命令是灰色的不可选。
遇到这种情况,我们也可以搬出分列功能,将日期字段列进行分列。
同时设置分列后的格式为日期格式,再对数据透视表进行刷新,就可以用【分组字段】功能了。
看到了没,在各种不同的场合灵活地运用分列功能,可以化繁为简。
下次,如果再遇到格式问题,不妨用用分列功能。
小 E 来帮大家总结一下这个爱管闲事的【分列】都可以做什么吧~
❶ 按照分隔符号进行数据拆分
❷ 按照固定宽度进行数据拆分
❸ 规范数据格式
比如:用分列将以文本存储的内容转换为以数值存储
用分列将以数值存储的内容转换为以文本存储
用分列将以文本存储的日期转换为以日期格式存储
先别急着走,你如果喜欢小 E,请按下面步骤把小 E 设置为★星标订阅号吧!
秋叶 Excel
◆ ◆ ◆
在秋叶 Excel 中,我们特意制作了「精华文章分类宝典」供您查阅。宝典分类里,有近百篇详尽的教学文章,随时随地为你解决问题。
进入公众号,点击菜单栏中的【快速学习】,就能找到它啦。
作者:Excel 研究院-水星钓鱼
联系客服