Excel 对大小写不怎么敏感，当然对于我们总是使用汉字来查询，也经常会忽略这个问题，毕竟大小写数要是针对英文字母，在实际工作中，可能会因为这种英文大小写不作区分的问题，导致数据计算错误。例如：物料编码，物料编码中通常是英文字母与数字的组合，如果出现 A1 与 a1，在大部分的 Excel 函数中，都会被认为是 A1=a1。
如果，刚好 A1 与 a1 分别代表两种物料，那么你在统计的时候就会当成是一种物料来统计，会出现很尴尬的情况。
今天的这篇文章就是来介绍 4 个能够区分大小写的 Excel 函数：
- CODE：返回文本字符串中第一个字符的数字代码
- EXACT：比较两个文本字符串，如果它们完全相同，则返回 TRUE，否则返回 FALSE
- FIND：在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值
- SUBSTITUTE：在文本字符串中用 new_text 替换 old_text
简单来说这四个函数可以区分大小写：
CODE(“A”)=CODE(“a”)=FALSE
EXACT(“A1”,”a1”)=FALSE
FIND(“A1”,”a1”)=#VALUE!
SUBSTITUTE(“A1”,”a1”,””)=”A1”
我们知道了这四个函数能够区分大小写，还要知道该如何应用到实际的工作中，总的来说有三个方面的应用：
字符匹配
字符替换、字符查找、字符统计、字符串拆分等等，字符替换使用 SUBSTITUTE 非常方便，查找使用 FIND，字符统计与字符床拆分就要花些时间来研究一下。
字符串拆分主要是定位，通过对某个字符的位子的确定，拆分字符串，有个经典组合:
TRIM+MID+SUBSTITUTE+REPT+ROW+LEN
这个组合在经典函数组合专栏里有详细的介绍，可以到我的专栏里了解一下。
字符统计需要将字符串拆成单个的字符，通常会用到这样一个公式：
MID(A2,ROW(INDIRECT(“$1:”&LEN(A2))),1)
这个公式的含义是将 A2 单元格内的字符串拆分成单个字符。
接下来的 4 种统计方法里都要用到这个组合：
我们先来看一个不使用区分大小写函数时的统计结果：
\=COUNT(0/(MID(D$12,ROW(INDIRECT(“$1:”&LEN(D$12))),1)=F12))
我们直接使用 MID 拆分组合来写条件，然后用 COUNT 统计字符个数，结果是大写与小写字母的统计数量是一样的，整明 COUNT 对大小写不敏感。
1、EXACT+COUNT
这个公式的含义是把 D12 单元格中的字符串拆分成单个字符，然后使用 EXACT 函数一个一个的与 F12 进行比较，统计结果是 TRUE 的个数。
\=COUNT(0/EXACT(MID($D$12,ROW(INDIRECT(“D$12))),1),F12))
2、FIND+COUNT
这个公式的含义是把 D12 单元格中的字符串拆分成单个字符，组成一个数组，然后使用 FIND 函数与 F12 进行比对，统计比对的结果。
\=COUNT(0/FIND(F12,MID(D$12,ROW(INDIRECT(“$1:”&LEN(D$12))),1)))
3、SUBSTITUTE+COUNT
这个思路就有点不同，不使用大小写区分函数的那个组合与这个很相近，区别是这个组合使用 SUBSTITUTE 函数用空格替换掉了 F12 的字符，然后把这个新的字符串拆分成单个字符，然后统计这个字符串数组里面的空格的个数。
\=COUNT(0/(MID(SUBSTITUTE(D$12,F12,””),ROW(INDIRECT(“$1:”&LEN(D$12))),1)=” “))
4、CODE+SUM
CODE 函数与上面三个函数不同，CODE 只对首字母起作用，就是说 CODE 函数只能一个字符一个字符的转换编码，不适用于字符串。把 D12 单元格中的字符串拆分成单个字符后，对每个字符用 CODE 函数解码，然后于 F12 的解码值进行对比，统计 TRUE 的数量。
\=SUM((CODE(MID(D$12,ROW(INDIRECT(“$1:”&LEN(D$12))),1))=CODE(F12))*1)
字符匹配中都用到了拆分字符组合，基本原理差不多，前三种可以用于字符串匹配，第四种只能单个字符匹配。
查找
工作中我们用到最多的是 VLOOKUP 函数，用来查找数据非常方便，但是 VLOOKUP 函数对大小写不敏感：
我们在使用 VLOOKUP 函数查找得到的结果是不正确的，对 A1 查找得到的是 a1 对应的结果，所以必须使用区分大小写函数，才能得到正确的结果。
1、EXACT+LOOKUP
我们用的是经典的二分法公式，EXACT 函数直接比较数据表中的 ID 列于目标表格中对应的 ID，作为区分条件，然后用 LOOKUP 查询对应的数据表中数值列中对应的结果。
\=LOOKUP(1,0/(EXACT(数据[ID],[@ID])), 数据[数值])
2、FIND+LOOKUP
同样的，使用 FIND 比对数据，作为 LOOKUP 查询的条件，查找结果。
\=LOOKUP(1,0/FIND([@ID], 数据[ID]), 数据[数值])
3、SUBSTITUTE+INDEX+MATCH
SUBSTITUTE 函数到 ID 列中替换掉目标 ID 字符串，然后用 MATCH 函数对空值进行匹配，然后用 INDEX 查找对应的数值。
\=INDEX(数据[数值],MATCH(“”,SUBSTITUTE([@ID], 数据[ID],),))
4、CODE+TEXTJOIN
CODE 函数只能对单个字符起作用，所以不那么灵便，最好的使用方法是做辅助列，相当于把原有的 ID 列全部解码成数字组成的新 ID，然后就可以使用 VLOOKUP 函数，通过新的 ID 进行匹配查找，这里也用到了把字符串拆分成单个字符的组合，还用到了 TEXTJOIN 这个新函数，用 “-” 将每个字符的数字编码连接起来。
\=TEXTJOIN(“-“,TRUE,CODE(MID(A3,ROW(INDIRECT(“1:”&LEN(A3))),1)))
区分大小写查找的四种组合中，CODE 函数稍微逊色一些，不能够直接使用公式得到查找结果，需要用在辅助列上，话说回来，辅助列也是解决问题的方法之一，善用辅助列，可以简化公式。
运算
运算与查找的情况很接近，不小心也会出现统计错误：
我们直接使用 SUNIFS/COUNTIFS 函数进行统计，得到的结果都是两两相同，整明大多数的计算类的函数都对大小写不敏感，包括 SUM、COUNT、SUMPRODUCT
1、EXACT+SUM/COUNT/SUMPRODUCT
三种组合都很好理解，就是使用 EXACT 比对结果作为计算条件
\=SUM(数据[数值]*EXACT(数据[ID],F21))
\=COUNT(0/EXACT(数据[ID],F21))
\=SUMPRODUCT(数据[数值]*EXACT(数据[ID],F21))
2、FIND+SUM/COUNT/SUMPRODUCT
FIND 的组合中需要解决不匹配就出现错误值的情况，需要使用 IFERROR 来过滤掉错误值，这样才能得到想要的结果。
\=SUM(数据[数值]*IFERROR(FIND(F29, 数据[ID]),0))
\=COUNT(0/FIND(F29, 数据[ID]))
\=SUMPRODUCT(数据[数值]*IFERROR(FIND(F29, 数据[ID]),0))
3、SUBSTITUTE+SUM/COUNT/SUNPRODUCT
SUBSTITUTE 函数用空值，替换目标 ID，让后让替换后的 ID 列与空值作比较，根据比较结果计算相关的数值。
\=SUM(数据[数值]*(SUBSTITUTE(数据[ID],F37,)=””))
\=COUNT(0/(“”=SUBSTITUTE(F37, 数据[ID],)))
\=SUMPRODUCT(数据[数值]*(“”=SUBSTITUTE(数据[ID],F37,)))
4、CODE
与查找中的用法相同，作为辅助列，可以使用 SUMIFS/COUNTIFS 函数进行相关的统计。
若有收获，就点个赞吧
0 人点赞
