携号转网之前,这些内容很重要

Excel之家ExcelHome
2019-06-05 07:45:00 字数 2156

小伙伴们好啊,今天老祝和大家分享一个数据查询的技巧。

如下图所示,B列是一组手机号码,需要判断对应的运营商是移动、联通还是电信。

咱们知道,根据手机号码的前三位可以区分运营商,例如,移动号段是134-139、150-152、157-159、187、188和147,

联通号段是130-132 155、156和186,

电信号段是133、153和189。

要解决本例问题,第一步是想办法提取出手机号码的前三位。

这里咱们使用LEFT函数来完成,LEFT函数的作用是从字符串的第一个字符开始,提取指定个数的字符。

=LEFT(B2,3)

LEFT(B2,3),就是提取B3单元格中的前三个字符。

第二步,建立一个号段和运营商的对照表:

有了手机号码的前三位,并且有了这个对照表,问题就好解决了。咱们可以使用VLOOKUP函数,以LEFT函数提取出的手机号码前三位作为查询值,在对照表中进行查询就可以了,一起来试试:

C2单元格输入公式:

=VLOOKUP(LEFT(B2,3),F:G,2,0)

结果怎么样呢:

公式看着没有问题啊,出现错误值的原因究竟在哪里呢?

其实,这里面有两个小问题:

第一个小问题:LEFT函数得到结果是文本结果“137”,并不是能直接运算的数值137。

这个小问题在文本类的函数中都有存在,比如TEXT函数、RIGHT函数、SUBSTITUTE函数等等,结果都是文本型的。

另一个小问题:咱们在对照表中使用的号段是数值型的,而VLOOKUP函数在查询数据时是区分数据格式的,会把文本“137”和数值137看成不同的两个内容。

两个小问题,凑到一起就变成了大问题。

知道了问题的根源,咱们就可以对症下药了,如何把文本型的数字变成数值型的呢?

只要经过一次简单的运算,文本型数字就可以变成数值型。运算的方式可以是乘以1、除以1、加0或是减0,也可以使用两个减号--,美其名曰减负运算。

回到咱们的题目当中,来给公式做个小手术:

=VLOOKUP(1*LEFT(B2,3),F:G,2,0)

本例的题目看着很简单,但是包含了好几个容易忽略的知识点。光说不练假把式,你也试试吧。

练习文件在此:

https://pan.baidu.com/s/10y7bFwXJtGzNQcv4W3yqUQ

提取码: 3cka

图文制作:祝洪忠

ExcelHome云课堂,我们的职场加油站