Excel 中超强大的文本处理函数 | 您所在的位置:网站首页 › excel根据数据生成函数公式 › Excel 中超强大的文本处理函数 |
Hi,我是偏爱函数公式,爱用 Excel 图表管理仓库的大叔 Mr 赵~ 今天咱们一起学习一个超级强大的文本拆分函数,TEXTSPLIT 函数。这个函数目前仅在 Officel 365 版本中可以使用,它的语法是: =TEXTSPLIT (需要拆分的文本,列分隔符,[行分隔符],[是否忽略空单元格],[是否区分大小写],[出错时填充的值]) 接下来,我们就通过 5 个既常见又实用的文本拆分案例,一起认识它有多强大~ 1、统计人数如下图,B 列的名单是以顿号作为连接符连接的一串姓名,需要统计每行的人数。 在 C3 单元格输入如下公式: =COUNTA(TEXTSPLIT(B3,"、"))首先用 TEXTSPLIT 函数以顿号「、」作为列分隔符拆分当前的名单,生成一行数组; 然后用 COUNTA 函数统计这个数组中有多少个元素,结果就是人数。 2、计算最大连胜次数如下图 B3 单元格一串文本是多轮比赛的成绩,需要统计最大连胜了几次。 在 C3 单元格输入如下公式: =MAX(LEN(TEXTSPLIT(B3,{"负", "平"})))公式中的 {"负","平"},是由字符 "负" 和 "平" 组成的一个常量数组。 我们可以按常量数组的形式将多种分隔符写到一起,再用 TEXTSPLIT 函数拆分文本,结果生成如下图所示的一行数组: 然后用 LEN 函数统计数组每个元素的字符个数,最后用 MAX 函数求出字符个数的最大值,结果就是最大连胜次数。 3、统计缺勤名单如下图 B3 单元格是公司人员名单,C3 单元格是实际参加会议的人员,需要统计缺勤的名单。 在 D3 单元格输入如下公式: =TEXTJOIN("、",,TEXTSPLIT(B3,TEXTSPLIT(C3,"、"),"、",1))公式中的 TEXTSPLIT (C3,,"、") 部分是用 TEXTSPLIT 函数以顿号「、」作为行分隔符,将 C3 单元格的文本拆分成 1 列数组: 然后以这列数组作为列分隔符,以顿号作为行分隔符,用 TEXTSPLIT 函数拆分 B3 单元格的字符串,结果如下图所示: 最后用 TEXTJOIN 函数以顿号作为连接符,连接数组中的每个元素,生成缺勤名单。 4、提取文本中的数字求和比如 B3 单元格中,是一串由货物品称和金额组成的文本,需要提取金额再求和。 在 D3 单元格输入如下公式: =SUM(--TEXTSPLIT(B3,,TEXTSPLIT(B3,".",ROW(1:10)-1,1),1))公式中 ROW (1:10)-1 生成一组 0 到 9 的序列值, 然后用 TEXTSPLIT 函数,分别以小数点作为列隔符,以 0 到 9 的数字作为行分隔符,忽略空白拆分 B3 单元格的文本,结果后成一列货物名称: 再用 TEXTSPLIT 函数,以这列名称作为行分隔符,忽略空白拆分 B3 单元格的文本,结果生成一列数字: 最后用两个负号,将这列数值转化成数值类型,再用 SUM 函数求和,就得到了金额之和,结果如下图 D3 单元格所示: 5、拆分文本成多行多列如下图,B3 单元格的是由姓名和数字连接起来的一串文本,需要将姓名和数字拆分成两列。 在 D3 单元格输入如下公式: =LET(a,TEXTSPLIT(B3,,ROW(1:10)-1,1),HSTACK(a,TEXTSPLIT(B3,,a,1)))公式中 ROW (1:10)-1 生成一组 0 到 9 的序列值, 然后用 TEXTSPLIT 函数,以 0 到 9 的数字作为行分隔符,忽略空白拆分 B3 单元格的文本,生成一列姓名: 再用 LET 函数将这列姓名赋值给变量 a,用 TEXTSPLIT 函数,以这列姓名作为行分隔符,忽略空白拆分 B3 单元格的文本,得出一列数字: 最后用 HSTACK 函数,将得到的姓名和数量连接成多行两列的数组,得到最终结果: 6、写在最后 本文通过以上 5 个案例,展示了 TEXTSPLIT 函数强大的文本拆分功能: 首先用 TEXTSPLIT 函数按指定的分隔符将文本拆分到行和列,结果以数组形式返回,然后与其它函数嵌套使用,应用到实际场景中。 本文来自微信公众号:秋叶 Excel (ID:excel100),作者:赵骄阳 广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。 |
CopyRight 2018-2019 实验室设备网 版权所有 |