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

职场计划有古哥 2024-03-31 01:30:04

在某工厂进行信息化升级时,需要先对存储在表格中的原始数据进行整理,并将其导入至信息化软件系统中。在这个数据整理阶段,对于基础表格——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)

效果如下图所示:

0 阅读:0

职场计划有古哥

简介:感谢大家的关注