工厂信息化升级实战:巧解无规律物料编码,动态提取字母组合

古哥计划
古哥计划

创作者俱乐部成员

在某工厂进行信息化升级时,需要先对存储在表格中的原始数据进行整理,并将其导入至信息化软件系统中。在这个数据整理阶段,对于基础表格——BOM物料清单中的物料代码,需要执行特殊处理步骤:识别首个字母出现的位置,并将该包含字母及之后的所有信息截取出来,然后移至新的单元格中。

手工模拟的数据如下:

观察数据

通过分析上图所示数据,我们注意到首个字母出现的位置并无明显规律,它可能随机分布于单元格内的任何位置,无论是起始处还是结尾处均有可能出现。此外,首个字母的大小写属性也不固定,既可能是大写字母,也可能是小写字母。

面对这种缺乏规律的数据,仅依靠常规的文本提取函数,如LEFT、RIGHT等单一功能,难以实现有效提取。因此,我们需要寻求其他的解决方案。在没有更优的方法之前,采用“暴力”通配符搜索策略不失为一种可行的办法。这种方法的基本思路是:逐一对单元格内容进行26个大写字母和26个小写字母的遍历查找,找出每个字母首次出现的位置,其中位置索引值最小的那个数字即代表首个字母出现的位置。

生成字母

为了实现对单元格内容中26个大写字母和26个小写字母的逐个查找,首先需要通过一个公式或步骤一次性生成这些大小写字母列表。一旦生成该列表,即可利用FIND函数来查找每个字母在单元格内容中的位置。录入以下公式:

=CHAR({64,96}+SEQUENCE(26))

将大小写字母自动列出

函数释义:

CHAR 函数在 WPS中的作用是将给定的 ASCII 码数值转换成对应的字符。ASCII 码 64 对应大写 A,ASCII 码 96 对应小写 a。

SEQUENCE(26) 生成一个从 1 到 26 的整数序列,长度为 26。

{64,96} 是一个由两个数值构成的数组,当它与 SEQUENCE(26) 结合时,通过数组运算符(默认情况下,WPS中直接加法可以实现数组间的对应元素相加),实际上会产生两个新的序列:

第一个序列是 64 加上 1 到 26 的序列,生成的是从 ASCII 码 65 到 90,这恰好对应了大写字母 A 到 Z。

第二个序列是 96 加上同样的 1 到 26 的序列,生成的是从 ASCII 码 97 到 122,这正好对应了小写字母 a 到 z。

因此,整个函数的结果会是一个包含52个元素的数组,前26个元素为大写字母,后26个元素为小写字母。效果如下图所示:

判断位置

获得这个包含52个元素(涵盖全部大写字母和小写字母)的数组结果后,我们可以将其与 FIND 函数结合使用,在相应的单元格内查找并确定目标字母的位置。

录入以下函数:

=FIND(CHAR({64,96}+SEQUENCE(26)),B3)

函数释义:

逐一对单元格内容进行26个大写字母和26个小写字母的遍历查找,当单元格内容中包含某个字母时,返回该字母所在的位置;若不存在,则返回一个表示错误的值,表明该字母不在单元格内容中。实际效果如下图所示:

屏蔽错误

当使用FIND函数进行判断时,对于未在文本中出现的字母,FIND函数会返回错误值。为妥善处理这种情况,可以巧妙地TOCOL函数的第2参数“3”来屏蔽错误值,并把这个元素组合转成一列。录入以下函数:

=TOCOL(FIND(CHAR({64,96}+SEQUENCE(26)),B3),3)

效果如下图所示:

通过上图可以明显看到,最小数值是5,外层再次嵌套函数就可以返回数字5:

录入函数:

=MIN(TOCOL(FIND(CHAR({64,96}+SEQUENCE(26)),B3),3))

提取数据

得知首个字母出现的确切位置后,我们可以通过使用MID函数来实现信息提取。提取时,指定起始位置为首个字母的位置(本例中为5),而提取的长度可以设定为一个较大的数值,如99,这样一来,就能够成功识别并截取首个字母及其之后的所有信息,进而将这部分内容复制并移动至新的单元格中,从而实现了我们的目标。

不过,请注意,"99" 这个数值应根据实际情况选择合适的长度,确保能覆盖到首个字母后面的所有内容。

录入以下函数并向下填充:

=MID(B3,MIN(TOCOL(FIND((CHAR({64,96}+SEQUENCE(26))),B3),3)),99)

效果如下图所示:

数组公式

在整理数据的过程中,是由一个项目小组专门负责,随着每天有新的数据不断加入,为避免手动填充公式导致的错误问题,这时可以将原有公式更新为动态数组公式。如此一来,公式便能够根据数据量的变化自动进行动态扩展,从而适应新增的数据内容。

录入以下公式一键填充:

=DROP(REDUCE("",TOCOL(B3:B300000,3),LAMBDA(X,Y,VSTACK(X,MID(Y,MIN(TOCOL(FIND((CHAR({64,96}+SEQUENCE(26))),Y),3)),99)))),1)

效果如下图所示:

307 一招GET!借助通配符解决表格数据汇总难题:SUMIF函数实例详解

306 一键生成:RANDARRAY 函数在数据分组与数学作业个性化定制中的妙用

305 实战演练:10种创新策略解锁WPS表格函数在成绩查询中的高效应用

304 步步详解:运用WPS函数整合工厂报表,实现全年度产品数据深度挖掘

303 运用WPS新函数实现工厂产销存报表的智能化整合与数据分析

302 BOM转换策略:树型BOM与父件子件BOM的优缺点分析及其相互转换方法

301 深入理解订单齐套率计算方法及应用实践案例解析

300 物料需求运算表优化实战:从“卡顿”到“流畅”的转变

299 WPS表格自动化编号:升序与累计编号的实用方法

298 输入法配合WPS表格公式,轻松实现虚拟数据批量生产:助力讲师快速培训演示

297 探究二维与一维报表的奥秘:掌握三种实用转换策略,应对庞大数据量挑战

296 从理论到实践:WPS 内测SCAN函数在销售金额稳定性评估中的神奇运用

295 新旧交织,效率升级:WPS表格新函数REDUCE、BYROW等在复杂业务场景下的灵动

294 WPS内测版新功能揭秘:“SCAN”迭代函数的运用与实践

293 借助WPS动态数组公式实现智能扩展填充与数据转换自动化

292 MRP原理课后的表格制作练习题目:技能与实战双丰收

运用WPS/Lambda及Scan函数实现动态数组累计需求分析 No291

巧用WPS AI,为上百名女员工打造3·8女神节专属祝福与精美卡片 290

从单一公式到复杂逻辑:通过实战案例揭示PMC面试中数据分析与解决问题的能力区分 No 289

MRP多阶需求运算报表-WPS表格版本 (8 No 288

MRP多阶需求运算报表-WPS表格版本 (7) No 287

MRP多阶需求运算报表-WPS表格版本 (6) No 286

MRP多阶需求运算报表-WPS表格版本 (5) No 285

MRP多阶需求运算报表-WPS表格版本 (4) No 284

MRP多阶需求运算报表-WPS表格版本 (3) No 283

MRP多阶需求运算报表-WPS表格版本 (2) No 282

MRP多阶需求运算报表-WPS表格版本 (1) No 281

巧用WPS中UNIQUE与SUM函数,一步解决跨门店商品库存成本合计问题 No 280

利用XLOOKUP快速实现培训未参加人员的扣分 NO 279

浙江省
浏览 111
收藏
3
分享
3 +1
7
+1
全部评论 7
 
QQ
打卡
· 河南省
回复
 
亂雲飛渡
· 广东省
回复
 
魏朝先
=REPLACE(B3,1,FIND(@TEXTSPLIT(B3,SEQUENCE(10)-1,,1),B3)-1,)
· 广东省
回复
古哥计划
古哥计划

创作者俱乐部成员

感谢您的支持! 这时候与SUBSTITUTE 的优势就体现出来了
· 浙江省
回复
 
leejun
=TEXTAFTER(B2,TEXTBEFORE(B2,INDEX(TEXTSPLIT(B2,SEQUENCE(10)-1,,1),1)))
· 美国
回复
古哥计划
古哥计划

创作者俱乐部成员

不错的思路,感谢您的支持!
· 浙江省
回复