Excel情报局
职场联盟Excel
大家好,今天我们来讲一种VLOOKUP函数在职场应用中的特殊场景:面对中英文符号不一致的干扰,VLOOKUP函数如何破局?下面我们来分三个小例子具体说说。
有时候我们在录入数据时存在不规范的现象,举个最简单的例子,比如我们在录入括号的时候,英文半角状态下的括号“(女)”与中文全角状态下的括号“(女)”,其实是不一样的,中文全角状态下的括号显得占位比较大一些,而英文半角状态下的括号就显得占位比较小一些。
原因就是半角输入法下,汉字占一个字符,数字,标点都是半个字符,显得比较紧凑。全角输入法下,汉字,数字,以及标点符号都是占用一个字符。显得长度比较分散。
两者的概念差异还是容易分辨的。但是肉眼状态下观察却容易看错。
如下图所示:
我们想要通过D2单元格中的姓名,在数据源A1:B4中查找到对应的销量是多少,并将结果显示在E2单元格中。
我们通常这样输入函数公式:
=VLOOKUP(D7,A7:B9,2,0)
VLOOKUP(要找谁,在哪个区域的首列查找,返回第几列的内容,[匹配方式])
结果返回的却是错误值。
追究其原因就是:数据源中A3单元格内容“李四(女)”中的括号是英文半角状态下的括号,而VLOOKUP函数的查询值D7单元格,其内容“李四(女)”中的括号为中文全角状态下的括号,两者不一致,导致内容虽然肉眼观察感觉一样,但仔细观察与实质并不相同。最终导致VLOOKUP函数返回错误值。
解决这一问题的关键在于如何将D2单元格中查找值“李四(女)”的括号由中文全角改为英文半角状态。
在Excel中,ASC函数可以将双字节字符转化为单字节字符,也就是全角转半角。WIDECHAR函数可以将单字节字符转换为双字节字符,也就是半角转全角。
我们以ASC函数为例。
=ASC(text)
ASC函数语法具有以下参数:
text文本:必需。文本或对包含要更改文本的单元格的引用。如果文本不包含任何全角字母,则不会对文本进行转换。
比如我们想要将D2单元格中查找值“李四(女)”的括号由全角转换为半角,可以这样输入函数公式:
=ASC(D2)
我们看到返回的结果“李四(女)”就和数据源中A3单元格内容“李四(女)”一致了。
所以我们只需要对VLOOKUP函数的第一参数查找值嵌套ASC函数就可以了:
=VLOOKUP(ASC(D2),A2:B4,2,0)
最终即可返回正确查找值销量“800”。
上面讲的例子是数据源为半角括号,而查找值为全角括号。那么如果反过来,数据源为全角括号,而查找值为半角括号的话,直接输入VLOOKUP函数公式:
=VLOOKUP(D7,A7:B9,2,0)
仍然会返回错误的值销量。原因和上面的是一样的。
在上面讲解ASC函数的参数的时候,第一参数是这么描述的:“文本或对包含要更改文本的单元格的引用”。
所以说ASC函数的参数还可以支持对单元格的引用。
也就是说我们通过输入函数公式:
=ASC(A7:A9)
将A7:A9区域中所有的中文全角括号转换为英文半角括号。返回结果为数组溢出。
所以我们只需要对VLOOKUP函数的第二参数查找区域嵌套ASC函数就可以了:
=VLOOKUP(D7,ASC(A7:B9),2,0)
最终即可返回正确查找值销量“800”。
如果数据源和查找值中各既有中文全角和英文半角括号呢?
直接输入VLOOKUP函数:
=VLOOKUP(D12,$A$12:$B$14,2,0)
仍旧返回错误值。
要解决很简单:
=VLOOKUP(ASC(D12),ASC($A$12:$B$14),2,0)
只需将分别将VLOOKUP函数的第一参数和第二参数都嵌套上ASC函数即可。
其实重复学习Excel,刚开始是挺难的,但重复的次数多了总会成为习惯,先从坚持一星期开始,你会爱上那种脑子又累,又无限充实的感觉。回顾关键内容,善用图片表达,学会建立联系,拓展深度广度,浓缩关键概念,应用到行动中,善于归纳总结,尝试进行分享。
〖往期知识点精彩超链接点击阅读〗