当前时间: 2026-04-17 15:03:03
分类:办公文件
评论(0)
Excel函数实战系列 第18期Excel函数实战系列 第18期
小伙伴们,今天继续我们的函数实战系列,今天我们更新到第18期。现有A2:A22数据区域,需把每个非空单元格的内容按分隔符拆分,表格格式不能变。我们刚好聊了2个365新函数TEXTBEFORE和TEXTAFTER,正是这两个函数的拿手好戏,我们正好拿这道题目来练练手。文本拆分,这不是重点,重点是如何拆分后,表格的格式不能改变,把拆分后的内容填充到空白单元格里边去,我们配合365新函数SCAN,来把这个问题搞定。=IFNA(TEXTBEFORE(SCAN(,A1:A22,LAMBDA(X,Y,IF(Y<>"",Y,TEXTAFTER(X,"、")))),"、",,,1),"")SCAN(,A1:A22,LAMBDA(X,Y,IF(Y<>"",Y,TEXTAFTER(X,"、"))))第一次循环,用SCAN函数在A1:A22中循环,初始值也就是LAMBDA的参数X为空,直接把A1的值赋给X。第二次循环,X等于“数据”。Y取值“宋江、宋江”,因为Y<>"",所以还等于Y,把“宋江、宋江”赋值给X。第三次循环,X等于“宋江、宋江”,Y="",所以TEXTAFTER(X,"、"),得到宋江,并赋值给X。第四次循环,X等于“宋江”,Y="",所以TEXTAFTER(X,"、"),错误值,并赋值给X。第五次循环,X等于错误值,因为Y<>"",所以返回Y,并将Y的值“吴用、吴用、吴用”赋给X。第六次循环,X等于“吴用、吴用、吴用”,以此类推...TEXTBEFORE(SCAN(,A1:A22,LAMBDA(X,Y,IF(Y<>"",Y,TEXTAFTER(X,"、")))),"、",,,1)接下来就简单了,用TEXTBEFORE函数提取分隔符前面的数据,注意一点的是,要把第5个参数设置为1,也就是末尾匹配,如果没有分隔符返回自身。最后用IFNA函数把其中的错误值替换为空,就达到想要的结果。IFNA(TEXTBEFORE(SCAN(,A1:A22,LAMBDA(X,Y,IF(Y<>"",Y,TEXTAFTER(X,"、")))),"、",,,1),"")好了,今天的实战也很简单,有兴趣的朋友练习起来吧!
基本
文件
流程
错误
SQL
调试
- 请求信息 : 2026-04-17 15:09:54 HTTP/1.1 GET : https://www.yeyulingfeng.com/a/541751.html
- 运行时间 : 0.097721s [ 吞吐率:10.23req/s ] 内存消耗:4,755.87kb 文件加载:145
- 缓存信息 : 0 reads,0 writes
- 会话信息 : SESSION_ID=ed2be13c168e3916f27b7d2d49248310
- CONNECT:[ UseTime:0.000703s ] mysql:host=127.0.0.1;port=3306;dbname=wenku;charset=utf8mb4
- SHOW FULL COLUMNS FROM `fenlei` [ RunTime:0.000841s ]
- SELECT * FROM `fenlei` WHERE `fid` = 0 [ RunTime:0.004227s ]
- SELECT * FROM `fenlei` WHERE `fid` = 63 [ RunTime:0.000324s ]
- SHOW FULL COLUMNS FROM `set` [ RunTime:0.000704s ]
- SELECT * FROM `set` [ RunTime:0.000259s ]
- SHOW FULL COLUMNS FROM `article` [ RunTime:0.000705s ]
- SELECT * FROM `article` WHERE `id` = 541751 LIMIT 1 [ RunTime:0.000490s ]
- UPDATE `article` SET `lasttime` = 1776409794 WHERE `id` = 541751 [ RunTime:0.000883s ]
- SELECT * FROM `fenlei` WHERE `id` = 64 LIMIT 1 [ RunTime:0.000286s ]
- SELECT * FROM `article` WHERE `id` < 541751 ORDER BY `id` DESC LIMIT 1 [ RunTime:0.002334s ]
- SELECT * FROM `article` WHERE `id` > 541751 ORDER BY `id` ASC LIMIT 1 [ RunTime:0.002077s ]
- SELECT * FROM `article` WHERE `id` < 541751 ORDER BY `id` DESC LIMIT 10 [ RunTime:0.002558s ]
- SELECT * FROM `article` WHERE `id` < 541751 ORDER BY `id` DESC LIMIT 10,10 [ RunTime:0.003110s ]
- SELECT * FROM `article` WHERE `id` < 541751 ORDER BY `id` DESC LIMIT 20,10 [ RunTime:0.001981s ]
0.099378s