点击下方 ↓ 关注,每天免费看Excel专业教程 置顶公众号或设为星标 ↑ 才能每天及时收到推送 大家好,我是李锐,根据小助手收集到的工作问题和需求反馈,我会陆续整理一些常用的Excel模板,帮助大家切实可行的提高工作效率。 这些Excel模板提供限量免费下载(名额100个),便于大家工作中使用,阅读教程时也可以同步练习和操作。 请联系小助手获取模板,今天的模板编号是20231129 今天介绍根据身份证自动查询省市县区地址的模板。解决方案使用了Excel函数公式,这种解决方案比技巧法最大的优势在于,当数据源改变时,结果可以同步自动更新,无需其他额外操作。 从下面的示意图动图演示中可以看到,当B列添加身份证信息的时候,后续几列自动查询出来对应的省、市、县(区),动图演示如下图所示。 上图中,黄色区域都是Excel函数公式自动计算生成的,这样做好的模板用起来十分方便。 【Excel模板】 根据身份证自动查询省市县区地址: 首先看一下数据源中表格初始的样子,如下图所示。 首先查询省份: 这里用到的公式如下: =IF(B2="","",INDEX(行政区划!B:B,MATCH(--LEFT(B2,6),行政区划!$E:$E,))) 公式示意图如下图所示: 然后查询市: 这里用到的公式如下: =IF(B2="","",INDEX(行政区划!D:D,MATCH(--LEFT(B2,6),行政区划!$E:$E,))) 公式示意图如下图所示: 最后查询县(区): 这里用到的公式如下: =IF(B2="","",INDEX(行政区划!F:F,MATCH(--LEFT(B2,6),行政区划!$E:$E,))) 公式示意图如下图所示: 公式原理解析: 首先要了解,身份证号码的前六位是行政区划代码,这里面包含了省、市、县(区)信息; 然后从国家官方网站下载一张行政区划代码表,这样才能用Excel公式根据身份证前六位的行政区划代码进行匹配,从中调取对应的信息; 这个表格我已经下载好了,需要的同学联系小助手获取即可 最后借助index+match查询组合,调取对应的省、市、县(区)信息,返回到你想要的位置。 这些函数在二期特训营里面都讲解过,对于他们的语法还不熟悉的同学,可以去系统学习视频教程。 这些常用的经典excel函数公式技巧可以帮你在关键时刻解决困扰,有心的人赶快收藏起来吧。 除了本文内容,还想全面、系统、快速提升Excel技能,少走弯路的同学,请搜索微信公众号“跟李锐学Excel”点击底部菜单的“知识店铺”或下方扫码进入。 希望这篇文章能帮到你!怕记不住可以发到朋友圈自己标记。 >>推荐阅读 << (点击蓝字可直接跳转) 最有用最常用最实用10种Excel查询通用公式,看完已经赢了一半人 老学员随时复学小贴士 由于有的老学员是6年前购买的课程,因买过的课程较多或因时间久忘记从哪里听课,所以专门将各平台的已购课程入口统一整理至下图。 1、搜索微信公众号“跟李锐学Excel”点击底部菜单“已购课程”,即可查看到你在各平台的已购课程,方便大家找到并随时复学课程。 2、课程分销推广的奖金也是由此公众号转账至大家的微信钱包(关注后可自动收钱,进入你的微信零钱,在微信支付有转账记录),老学员可以进“知识店铺”点击底部按钮“推广赚钱”或者“我的”-“推广中心”查询到推广奖励明细记录,支持主动提现。 此外,里面还有小助手的联系方式,有问题或学习需求可以留言反馈,助手在24小时内回给到回复。 按上图↑识别二维码,查看详情 如果你的朋友也用到Excel,请把这个公众号推荐给Ta:) ▼ ↓↓↓点击“阅读原文”进知识店铺 全面、专业、系统提升Excel实战技能 |
|