一个有趣的题目,VLOOKUP秒杀

先看下图,A列是行政区代码,B列是对应的行政区名称。
需要根据A~B列的信息,在C列和D列分别填充对应省名以及市区名称:
思考五分钟,看看能不能找出规律……
……
好了,咱们来看看A列不同级别行政区划代码的分布规律:
省级:
每个省份代码的后四位是0000,例如广东省代码为440000
市级:
每个省份下属的城市,开头两位与所在省份代码相同,后两位是0。
例如广州市代码为440100,韶关市代码为440200
区县级:
每个城市下属的区县,开头四位与所在城市代码相同。
例如荔湾区代码为440103,越秀区代码为440104。
规律找出来了,你能想到使用什么公式吗?
先来看获取省份名称的公式:
C2输入以下公式,向下复制。
=IF(MOD(A2,10000),VLOOKUP(A2-RIGHT(A2,4),A:B,2,0),'')
接下来咱们简单说说公式的意思,先看RIGHT(A2,4)部分,作用是从A2行政区代码中提取出最后四位。
然后使用A2减去后四位,目的得到后四位是0的省级行政区代码。
再来看VLOOKUP(A2-RIGHT(A2,4),A:B,2,0)部分。
用相减后得到的省级行政区划代码作为VLOOKUP函数的查询参数,以A~B作为查询区域,返回第2列中的省级名称。
最外层的IF和MOD是什么意思呢?
MOD(A2,10000),计算A2除以10000后的余数,440000除以10000,商是44,余数是0,说明A2后四位都是0,也就是属于省级代码。
当A2属于省级代码时,IF函数返回空文本,否则就返回VLOOKUP函数的运算结果。
提示:IF函数的第1参数等于0时,相当于是逻辑值FALSE,IF函数返回第三参数的结果。如果IF函数的第1参数是不等于0的任意数值时,相当于是TRUE,IF函数返回第二参数的结果。所以公式中没有使用MOD(A2,10000)>0这样的表示方式,而是直接将大于0给省略掉了。
再来看获取市级名称的公式:
D2输入以下公式,向下复制。
=IF(MOD(A2,100),VLOOKUP(A2-RIGHT(A2,2),A:B,2,0),'')
这个公式和提取省份名称的公式非常相似。
先使用RIGHT函数提取出A2右侧两位数字,然后使用A2减去右侧两位数字,得到后两位是0的市级行政区划代码。
接下来使用VLOOKUP函数,以相减后得到的市级区划代码,在A~B列中查询,并返回第二列对应的市级名称。
而最外层的IF和MOD部分,先使用MOD函数计算A2除以100后的余数,如果区划代码后两位都是0,则余数不为0,说明A列是市级以下的区划代码,IF函数返回VLOOKUP函数的计算结果,否则返回空文本。
好了,今天的内容就是这些吧,祝大家一天好心情!
图文制作:赵中山
编辑整理:祝洪忠
(0)

相关推荐