计算机公式⼤⼩写,Excel使⽤⼤⼩写区分函数进⾏字符匹配、
查、运算
Excel对⼤⼩写不怎么敏感,当然对于我们总是使⽤汉字来查询,也经常会忽略这个问题,毕竟⼤⼩写数要是针对英⽂字母,在实际⼯作中,可能会因为这种英⽂⼤⼩写不作区分的问题,导致数据计算错误。例如:物料编码,物料编码中通常是英⽂字母与数字的组合,如果出现A1与a1,在⼤部分的Excel函数中,都会被认为是A1=a1。
Excel使⽤⼤⼩写区分函数进⾏字符匹配、查、运算
如果,刚好A1与a1分别代表两种物料,那么你在统计的时候就会当成是⼀种物料来统计,会出现很尴尬的情况。
今天的这篇⽂章就是来介绍4个能够区分⼤⼩写的Excel函数:
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"
我们知道了这四个函数能够区分⼤⼩写,还要知道该如何应⽤到实际的⼯作中,总的来说有三个⽅⾯的应⽤:
Excel使⽤⼤⼩写区分函数进⾏字符匹配、查、运算
字符匹配
字符替换、字符查、字符统计、字符串拆分等等,字符替换使⽤SUBSTITUTE⾮常⽅便,查使⽤FIND,字符统计与字符床拆分就要花些时间来研究⼀下。
字符串拆分主要是定位,通过对某个字符的位⼦的确定,拆分字符串,有个经典组合:
TRIM+MID+SUBSTITUTE+REPT+ROW+LEN
这个组合在经典函数组合专栏⾥有详细的介绍,可以到我的专栏⾥了解⼀下。
字符统计需要将字符串拆成单个的字符,通常会⽤到这样⼀个公式:MID(A2,ROW(INDIRECT("$1:"&LEN(A2))),1)
这个公式的含义是将A2单元格内的字符串拆分成单个字符。
接下来的4种统计⽅法⾥都要⽤到这个组合:
Excel使⽤⼤⼩写区分函数进⾏字符匹配、查、运算
我们先来看⼀个不使⽤区分⼤⼩写函数时的统计结果:=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("$1:"&LEN($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
excel vlookup函数怎么用
CODE函数与上⾯三个函数不同,CODE只对⾸字母起作⽤,就是说CODE函数只能⼀个字符⼀个字符的转换编码,不适⽤于字符串。把
D12单元格中的字符串拆分成单个字符后,对每个字符⽤CODE函数解码,然后于F12的解码值进⾏对⽐,统计TRUE的数量。
=SUM((CODE(MID(D$12,ROW(INDIRECT("$1:"&LEN(D$12))),1))=CODE(F12))*1)
字符匹配中都⽤到了拆分字符组合,基本原理差不多,前三种可以⽤于字符串匹配,第四种只能单个字符匹配。
⼯作中我们⽤到最多的是VLOOKUP函数,⽤来查数据⾮常⽅便,但是VLOOKUP函数对⼤⼩写不敏感:
Excel使⽤⼤⼩写区分函数进⾏字符匹配、查、运算
我们在使⽤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函数稍微逊⾊⼀些,不能够直接使⽤公式得到查结果,需要⽤在辅助列上,话说回来,辅助列也是解决问题的⽅法之⼀,善⽤辅助列,可以简化公式。
运算
运算与查的情况很接近,不⼩⼼也会出现统计错误:
Excel使⽤⼤⼩写区分函数进⾏字符匹配、查、运算
我们直接使⽤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函数进⾏相关的统计。
通过上⾯的介绍,相信你如果遇到⼤⼩写区分问题,肯定也能到,对应的解决⽅案。

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。