大过节的让我提取这个,幸亏我早有准备......

职场   职场   2024-10-06 08:37   河北  


Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能
Super Excel Man
用1%的Excel基础搞定99%的职场问题
做一个超级实用的Excel公众号
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,刚开始是挺难的,但重复的次数多了总会成为习惯,先从坚持一星期开始,你会爱上那种脑子又累,又无限充实的感觉。回顾关键内容,善用图片表达,学会建立联系,拓展深度广度,浓缩关键概念,应用到行动中,善于归纳总结,尝试进行分享。


 

知识点精彩超链接点击阅读

VLOOKUP的新搭档HSTACK函数,实现逆向查询
一个小小的VSTACK函数,就能实现多工作表合并
VLOOKUP最新用法,提取混合内容中的手机号
带超链接的VLOOKUP函数,让查询体验起飞
最新方法来了:按指定的次数重复数据
合并工作表,用两个函数就搞定啦
比VLOOKUP还好用的多条件查找
点击谁就筛选谁,实现筛选自动化
以“数字”作为分隔符,进行分列
点击谁,就对谁自动条件求和
更多实用内容请在号内历史文章搜索 

Excel情报局
Excel表格爱好者,分享一些日常的积累。做一个职场人都能看懂的表格技巧公众号。多学一个Excel小技巧,会让你在职场中多一分底气与自信。(同名视频号)
 最新文章