SAP项目之Excel函数：判断批次号中是否包含小写字母(CODE函数、LET函数、FILTER函数、SEQUENCE函数)

业务背景

SAP实战项目上，期初库存导入时，使用开发程序进行BAPI MIGO 561生成期初库存。因为物料主数据启用了批次管理，所以程序会自动生成流水批次，程序也允许Excel导入指定批次(字母+数字+字符各种混合，位数不大于10位)。

问题描述

上线后仓库提出，MIGO发料的时候，选择期初库存批次，MIGO就是显示批次对应的库存不存在，但是MMBE或MB52都可以查到库存。

报错原因

BAPI或IDoc在进行批次创建的时候，如果输入的批次号中有小写字母，BAPI或IDoc是可以直接输入小写字母的，存后台表MCHB批次库存表也是小写字母。但是MIGO前台过账的时候，是不允许输入带有小写字母的批次号的。你输入小写字母的批次号，回车后，SAP系统自动将小写字母变更大写。尝试MIGO 311批次转批次也不行。

最终结论(点击可以查看具体信息)

SAP官方也没有给出好的解决方案，居然是要用原来的BAPI程序把导入进SAP系统的批次冲销掉。

后续操作

需要把SAP后台表MCHB中的所有批次库存导出，然后使用Excel公式去判定是否存在小写的字母，然后把这些小写字母对应的批次冲销掉，然后用大写的字母重新导入。

Excel操作

具体Excel如何判断导出的所有批次库存中是否存在小写字母的方法如下：

涉及Excel公式

LEN函数

LEN 返回文本字符串中的字符个数。

=LEN(A1）

SEQUENCE函数

SEQUENCE 函数可在数组中生成一系列连续数字，例如，1、2、3、4。

在以下示例中，使用 =SEQUENCE(4,5) 创建了高 4 行，宽 5 列的数组。

MID函数

MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

CODE函数

返回文本字符串中第一个字符的数字代码。 返回的代码对应于本机所使用的字符集。

Excel CODE函数就是把单元格对应的内容转化位ASCII码。

百度百科：ASCII (American Standard Code for Information Interchange)：美国信息交换标准代码是基于拉丁字母的一套电脑编码系统，主要用于显示现代英语和其他西欧语言。它是最通用的信息交换标准，并等同于国际标准 ISO/IEC 646。ASCII第一次以规范标准的类型发表是在1967年，最后一次更新则是在1986年，到目前为止共定义了128个字符。

从上面ASCII表上面可以获得：

大写字母A到Z：65到90

小写字母a到z：97到122

关键：就是用上面这个ASCII码来判定单元格内容字符是字母大写还是小写。

FILTER函数

FILTER 函数可以基于定义的条件筛选一系列数据。

在以下示例中，我们使用公式 =FILTER(A5:D20,C5:C20=H2,"") 返回包含“苹果”（如单元格 H2 中所选定）的所有记录，如果没有“苹果”，则返回空字符串 ("")。

用于返回多个条件的 FILTER

在此示例中，我们使用乘法运算符 (*)，以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值：=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")。

用于返回多个条件并排序的 FILTER

在此示例中，我们配合使用之前的 FILTER 函数和 SORT 函数，以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值，然后对 Units 进行降序排序：=SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)

在此示例中，我们配合使用 FILTER 函数和加法运算符 (+)，以返回数组范围 (A5:D20) 中包含“苹果”或位于东部区域的所有值，然后对 Units 进行降序排序：=SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1)。

请注意，这些函数都不需要绝对引用，因为它们仅存在于一个单元格中，并将其结果溢出到相邻单元格。

学习总结：FILTER函数针对于多个 且 条件 用星号 * 连接。针对于多个 或 条件 用加号 + 连接。

LET函数

LET 函数会向计算结果分配名称。 这样就可存储中间计算、值或定义公式中的名称。 这些名称仅可在 LET 函数范围内使用。 与编程中的变量类似，LET 是通过 Excel 的本机公式语法实现的。

若要在 Excel 中使用 LET 函数，需定义名称/关联值对，再定义一个使用所有这些项的计算。 必须至少定义一个名称/值对（变量），LET 最多支持 126 个对。

提升性能 如果你在某公式中多次编写同一表达式，Excel 之前会多次计算出结果。 而借助 LET，你可按名称调用表达式，Excel 也只计算一次。

轻松阅读和撰写 不用再记住特定范围/单元格引用是指什么、你的计算在执行什么操作，也不用再复制/粘贴相同的表达式。 借助可声明和命名变量的能力，你可为自己和公式使用者提供有意义的上下文。

CHAR函数

返回对应于数字代码的字符。使用 CHAR 可将从其他类型计算机上的文件中获取的代码页数字转换为字符。

Number 为用于转换的字符数字代码，介于 1 和 255 之间。使用的是当前计算机字符集中的字符。

CONCAT函数

CONCAT 函数合并来自多个区域和/或字符串的文本，但它不提供分隔符或 IgnoreEmpty 参数。 

CONCAT 替换 CONCATENATE 函数。 但是，为了与早期版本的 Excel 兼容，CONCATENATE 函数将仍然可用。 

Excel函数解释

A1单元格=BOtECk123

批次号：BOtECk123 

其中存在t和k小写字母，大量的批次号如何快速提出小写字母？从而判定批次号中存在小写字母。

具体步骤如下：

第1步 LEN函数输出A1单元格字符长度

=LEN(A1)

输出：9

第2步 SQUENCE函数输出一个数组

=SEQUENCE(LEN(A1))

输出：

1
2
3
4
5
6
7
8
9

第3步 MID函数按数组输出A1单元格每个字符

=MID(A1,SEQUENCE(LEN(A1)),1)

输出：

B
O
t
E
C
k
1
2
3

第4步 CODE函数输出字符对应的ASCII码

=CODE(MID(A1,SEQUENCE(LEN(A1)),1))

输出：

66
79
116
69
67
107
49
50
51

第5步 LET函数通过ASCII码把小写字母码提取了，BN是自定义变量名称

=LET(BN,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),FILTER(BN,(BN>96)*(BN<123),""))

输出：

116
107

第6步 CHAR函数把筛选出的小写字母ASCII码转化为字母

=CHAR(LET(BN,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),FILTER(BN,(BN>96)*(BN<123),"")))

输出：

t
k

第7步 CONCAT函数把转化后的小写字母进行合并显示

=CONCAT(CHAR(LET(BN,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),FILTER(BN,(BN>96)*(BN<123),""))))

输出：tk

最终输出：B列 = tk 就是批次号 BOtECk123 输出的小写字母，如果输出为空，表示批次号不存在小写字母。

可以直接拷贝第7步代码，更改对应的单元格即可，但是有一点，如果批次号中没有小写字母就会报错，所以我们再加一个IF函数判断下。

IF函数判断错误的公式结构=IF(ISERROR(第7步公式),"",第7步公式）

条件1：如果批次号中没有小写字母公式报错了，就输出空值

条件2：如果批次号中有小写字母，直接输出第7步的结果（所有的小写字母）

最终公式=IF(ISERROR(CONCAT(CHAR(LET(BN,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),FILTER(BN,(BN>96)*(BN<123),""))))),"",CONCAT(CHAR(LET(BN,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),FILTER(BN,(BN>96)*(BN<123),"")))))

Done.

Created 10th November, 2024