Excel情报局
职场联盟Excel
十月一小长假也不让人在家过的安生,这不小李就遇到了一个棘手的问题,不过幸亏小李最近在『Excel情报局』公众号里面学了几个超级实用的新函数,最终还是凭借自己的知识储备解决了。
如下图所示:
A2单元格内包含了若干组水果名称与销量的组合数据,所有字符之间没有明显的分隔符号,现在我们想要将A2单元格数据进行提取,水果名称显示在B列,而其对应的销量数据则显示在C列。
因为我们上几期的文章/视频中讲解过了这样一个技巧(点击下方文字查看):
今天这个思路正好就用上了。
首先我们可以先以所有的任意数值(销量)作为分隔符号,对A2单元格的内容进行拆分操作,就可以实现将所有的水果名称拆分到行的目的。
因为分隔符号为数值(销量),而数值组成的所有可能性不外乎0~9这10个数字,那么既然这样,我们可以利用获取行号的函数ROW函数:
=ROW(1:10)-1
ROW函数可以获取指定单元格或者单元格区域的所在行号信息。
利用ROW(1:10)获取第1~10行的各行行号1~10,结果以数组自动溢出显示,然后在ROW(1:10)的基础上减去1,则数组内的每个元素都会依次减去1,得到新的数组溢出结果0~9。
我们以数组溢出结果{0;1;2;3;4;5;6;7;8;9}为分隔符号,用TEXTSPLIT函数进行拆分到行:
=TEXTSPLIT(A2,,ROW(1:10)-1,TRUE)
TEXTSPLIT函数是用于拆分数据的函数。
函数基本参数:
=TEXTSPLIT(待拆字符,[列分隔字符],[行分隔字符],[是否忽略空],[是否区分大小写],[出错时返回什么])
列分隔字符或行分隔字符可以只用任意一个,也可以两个参数同时使用。并且允许同时有多个不同的行分隔符或列分隔符。
第一参数A2是要进行拆分的单元格,第二参数行列隔符号省略,第三参数用ROW(1:10)-1作为拆分到行的行分隔符号,第四参数TRUE代表忽略拆分出来的空值留存数据。
这样所有的水果名称就成功的保留在了B4:B8区域内了。
提取销量仍然是这个原理。
因为我们将所有的水果名称提取保留在了B4:B8区域,所以我们可以用这些水果名称依次作为行分隔符号,反向将A2单元格数据拆分到行,拆分出来的自然就是销量数据了:
=TEXTSPLIT(A2,,B4:B8,TRUE)
一些经验较少的小伙伴很自然的就用“B4:B8”作为了TEXTSPLIT函数的第三参数行分隔符号,虽然可以正常拆分出销量数据,但是还是存在一定的弊端。
弊端就是当我们A2单元格数据源内添加新的水果名称及销量的时候,B:C列的输出结果就会返回错误,原因就是第三参数“B4:B8”是一个固定范围,不会随着A2单元格的变动及时变化。
我们可以使用“B4#”代替“B4:B8”作为TEXTSPLIT函数的第三参数:
=TEXTSPLIT(A2,,B4#,TRUE)
这样当我们A2单元格数据源内添加新的水果名称及销量的时候,B:C列的输出结果就会返回正确值。
这里的#是指以B4单元格开始的“溢出区域”。因为公式返回区域B4:B8就是TEXTSPLI动态数组公式的溢出区域。如果数据源A2增加了一些新的内容,B:C列的溢出区域会自动扩展,包含新增加的水果名称及销量,但原公式第三参数如果仍坚持用B4:B8,就不会跟着变化。
如果我们在公式中使用#,公式将随着源数据和溢出区域一起变化。
使用这个#时需要注意一点:
这个#只能跟在溢出区域的左上角起始单元格引用的后面,只有这种引用才表示整个的溢出区域。如果引用的是个别的单元格(不管在不在溢出区域),都表示这一个单元格的引用。
其实重复学习Excel,刚开始是挺难的,但重复的次数多了总会成为习惯,先从坚持一星期开始,你会爱上那种脑子又累,又无限充实的感觉。回顾关键内容,善用图片表达,学会建立联系,拓展深度广度,浓缩关键概念,应用到行动中,善于归纳总结,尝试进行分享。
〖往期知识点精彩超链接点击阅读〗