做数据分析这些年我手里翻得最多的数据文件之一就是行政区划的Excel表。无论是给业务系统做地区维度映射、给报表做区域汇总还是在地图可视化之前给地址匹配经纬度都绕不开一套干净、完整、字段一致的行政区划数据。网上能搜到各种“免费下载行政区划Excel版”的帖子但真正打开文件之后才发现有的缺省市区级有的旧版区划代码没更新有的干脆把街道和村居混在一张表里拿过来想直接用基本不现实。这篇东西我不打算只告诉你“去哪下载”因为那只是第一步。我更想带你过一遍拿到原始行政区划Excel之后怎么用Excel自身功能和Python做二次清洗怎么把区划代码和名称变成可统计、可关联、可入库的标准字段顺便把我在实操中踩过的一些坑说清楚。内容从入门到进阶都有小白照着步骤能跑通老手也可以直接跳到自己关心的段落看细节。1. 行政区划Excel数据到底解决什么问题1.1 一套标准行政区划数据长什么样先说规格。一份合格的行政区划Excel版至少应该包含这几个字段行政区划代码、省级名称、市级名称、区县级名称以及可选的“城乡分类代码”和“备注”。行政区划代码是国家统一的统计用代码通常由12位数字或6位数字组成前两位是省级中间两位是市级再往后两位是区县级最后几位往下细分到乡级和村级。我在实际项目里最常用的是两套粒度到区县6位或12位以及到乡镇街道9位或12位。如果需要做门店经营分析、物流配送区域划分粒度到区县基本够用如果做街道级别的网格化管理那就得再往下取一层。很多人下载行政区划数据之后问的第一个问题是“Excel里这一列代码前面是0怎么不见了”因为Excel默认把数字列处理成数值格式6位区划代码里像“110101”这种开头是0的输入或导入后会被自动截成110101前面的0不显示其实不对应该是“110101”整体保留。所以拿到任何区划代码表第一件事就是把代码列设成文本格式或者用TEXT函数强制补零。这也是为什么我强调不要直接去网上拷贝粘贴而是要用结构化方式导入和处理才能保住字段类型。1.2 为什么偏偏要Excel版而不是数据库或JSON市面上其实有很多行政区划数据是JSON、CSV或者SQL格式提供的。那为什么大家还是满世界找“Excel版本”核心原因是Excel的门槛和通用性。业务同事不会打开CSV时指定编码格式也没法直接看JSON里的嵌套结构但几乎所有人双击就能打开xlsx文件用筛选、透视表、VLOOKUP就能办事。Excel版数据还能直接作为数据源接入Power BI、Tableau或者各类报表平台不需要额外写转换脚本。所以在企业内部协作场景里一份编排清楚的xlsx比JSON要接地气得多。另外Excel支持的“多层Sheet”结构很适合行政区划数据。我习惯拆成四个Sheet省级清单、市级清单、区县级清单、乡镇街道级清单每个Sheet字段统一。这样做有一个好处数据透视表可以分开做VLOOKUP关联时范围清晰不会因为整表都堆在一起导致查找范围混乱。纯CSV就做不到这种物理分层一个文件只放一张表横向对比起来就不方便。2. 行政区划数据的获取与初检2.1 免费下载渠道怎么找才靠谱标题打着“免费下载”四个字的搜索时能跳出一堆网盘链接。我的经验是可以下载但不要直接信。最稳妥的路径是去官方公开渠道找每年发布的“统计用区划代码和城乡划分代码”一般以文本或Excel附件形式发布字段规范程度很高。每年都会更新区划调整比如撤县设区、街道合并之后旧代码会失效新代码会补充这直接影响数据统计口径。下载的时候优先选当年发布的版本别用三年前的老文件。如果你需要的是带经纬度或者带邮政编码的增强版那就只能依赖第三方开源数据集了。这类数据大多由开发者维护免费可用但你要注意两个问题一是更新是否及时二是字段名是否稳定。我的习惯是把第三方数据作为“参考”把官方代码作为“基准”两边一比对差异部分手工确认。千万别在业务系统里直接拿第三方数据当唯一事实来源后续数据对不上时很难排查。2.2 拿到文件后第一时间核对的三个关键点第一看代码位数。每级区划代码的位数是否一致如果同一列里有6位有9位有12位说明文件拼接了多级数据先得拆开。第二看名称是否带“省”“市”“区”“县”后缀。有的数据源名称是“北京市”有的数据源是“北京”这影响匹配——后续做多表关联时后缀不一致会导致VLOOKUP或者相同字段关联大面积产生匹配不到。第三看是否有重复行。同一代码出现两次大概率是某一年代码变更后新旧并存处理时要保留最新值。2.3 数据标准化规则建议拿到手先做一次如果你准备把这份Excel当作长期基础数据强烈建议做一次标准化哪怕原文件已经够用。区划代码统一转成文本并补足前导0。名称字段统一去空格、去全角空格去掉首尾空白。增加“上级代码”字段便于从区县级向市级、省级聚合时做树形计算。增加“状态”字段标记“现行”、“已撤销”、“新增”做历史回溯时不会乱。把每级数据拆分到独立Sheet并给每个Sheet加筛选按钮和表头锁定。这套规则我应用过多次建好了基本就是一劳永逸。后续不管是Excel函数统计还是Python读取都不需要每次都重新处理。3. Excel内处理行政区划数据的关键操作3.1 两列查重怎么找出名称相同但代码不同的记录行政区划Excel最容易出现的问题是“名称相同、代码不同”。比如同名的街道在不同区县下各有一个或者某区划改名后旧名称依然留在文件里。手动一行一行看眼睛都会花掉我建议直接用COUNTIFS函数做两列联合查重。假设A列是省级名称B列是市级名称C列是区县名称D列是区划代码。现在要检查“同一个省市区县”是不是有重复。在旁边E列写COUNTIFS(A:A,A2,B:B,B2,C:C,C2)结果大于1的就是多行重复。再把E列筛选成大于1就能一行一行处理。如果想进一步找出“名称相同但代码不同”可以再加一列IF(AND(E21,COUNTIFS(A:A,A2,B:B,B2,C:C,C2,D:D,D2)0),代码冲突,正常)这样冲突记录会直接标出来。我用这个方法清理过一个几万行的乡镇级数据几分钟就把藏在里头的几百条冲突揪出来了。3.2 多条件筛选与SUMIFS统计实战行政区划数据最常见的统计场景是按省级区域汇总销售数据。如果业务明细表里有一列是区划代码你想要按“省份年份”汇总规模可以不用数据透视表直接用SUMIFS公式硬算。比如销售明细在Sheet“订单”里A列省份B列年份C列销售额。想要得到“某省某年”的销售额SUMIFS(订单!C:C,订单!A:A,某省,订单!B:B,2024)如果要做多条件筛选而不是汇总Excel 365里的FILTER函数很好用。比如筛选出“省级XX且市级YY”的全部区县FILTER(区县表!A:D,(区县表!A:AXX)*(区县表!B:BYY),无匹配)老版本没有FILTER可以用高级筛选功能录制宏或者用透视表配合切片器也能达到一样效果。关键在于Excel里处理区划筛选、汇总这类操作核心不是背公式而是先保证数据列规范不然公式套上去全是#N/A。3.3 用数据透视表做区域层级汇总数据透视表在行政区划数据上的价值是它的“分组汇总”能力。比如你手里有全国各区县的常住人口数据字段包括省份、地市、区县、人口。想把数据从区县粒度聚合到地市级再聚合到省级只需要插入数据透视表把省份拖到行区域地市拖到省份下面人口拖到值区域。Excel会自动生成层级结构不需要写任何公式。透视表还有一种高频用法统计每个省份下有多少个区县。把省份放到行区域把区划代码放到值区域值字段设置成“计数”马上就能得到数量。这个操作在数据质量核验阶段特别实用——一比对各区县数量是否和官方公报一致不一致就说明数据缺失或者重复了。需要注意的是透视表默认对文本字段做计数对数值字段做求和。如果你的区划代码被Excel误认为数字透视图表里它可能会被求和这没有意义。所以在做透视之前记得先确认区划代码列是文本类型。我这里处理的方法是用‘000’ 前缀把它转成文本或者直接用Power Query把列类型设为文本再载入。3.4 让数据自动变背景色条件格式实战有内容自动变背景这个需求处理行政区划表时特别有用。你希望某个单元格一旦有内容整行或某些列自动加背景色这样空值就能一眼暴露。第一步选中希望应用格式的数据区域。第二步开始选项卡里点“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”。第三步写公式。如果希望“当A2非空时整行变浅灰”公式是$A2然后在格式里设置填充色。这样整行只要A列有值就会变色A列为空的行保持原样。反向操作也常用把区划代码为空的行标红。公式改成$D2就能把所有缺代码的记录标成红色方便后续补录。条件格式不会改变数据内容只是视觉标记对于动辄上千行的区划表来说比人工看靠谱得多。4. 用Python批量读写行政区划Excel4.1 pandas读取Excel别只在Excel里点来点去行政区划Excel文件一旦涉及多Sheet、上级代码关联、历史版本对比纯手工在Excel里操作容易出错。我的选择是用pandas写一次性脚本处理。pandas读取Excel的基础操作很简单但有几个参数直接影响结果。import pandas as pd df pd.read_excel( 行政区划.xlsx, sheet_name区县级, dtypestr, # 强制所有列按文本读入防止区划代码丢0 keep_default_naFalse, # 不把空单元格自动解释成NaN保留原文 ) print(df.head())dtypestr是我最强调的参数。pandas默认会推断类型6位区划代码一旦被推断成int64前面的0就丢了后面再补非常麻烦。用了dtypestr读进来就是纯文本处理起来可控很多。4.2 数据清洗与补全Python做一次Excel少忙一年读取之后常用的清洗操作包括去掉名称字段首尾空格、把重复记录标记出来、按上级代码补全缺失层级。比如你拿到了只有区县级代码和名称的Excel想反推出市级和省级前提是你手上有一张完整的“代码-层级-名称”映射表然后做两次merge# 假设df是只有区县级代码的数据code_map是全量代码表 df[市级代码] df[区县代码].str[:4] 00 df[省级代码] df[区县代码].str[:2] 0000 df df.merge( code_map[[代码, 名称]], left_on市级代码, right_on代码, howleft, suffixes(, _市) ).rename(columns{名称: 市级名称})这种写法比在Excel里写一堆VLOOKUP要直观得多。而且脚本是可复现的源文件更新后重新执行一遍就行手工在Excel里做的话下个月再来一份新数据又得重新折腾。4.3 批量写入Excel并保持多Sheet结构清洗完之后最理想的结果是输出一个干净的多Sheet Excel文件。用pandas的ExcelWriter很容易实现with pd.ExcelWriter(行政区划_清洗版.xlsx, engineopenpyxl) as writer: df_province.to_excel(writer, sheet_name省级, indexFalse) df_city.to_excel(writer, sheet_name市级, indexFalse) df_district.to_excel(writer, sheet_name区县级, indexFalse) df_town.to_excel(writer, sheet_name乡镇街道级, indexFalse)engineopenpyxl这一步对xlsx格式是必需的需要对已有样式或Sheet做追加时也用它。写入时加indexFalse不然会多出一列无意义的索引。输出之后先别急着关闭文件再用Python读一遍检查Sheet数量和首行数据确认没问题再分发给同事。4.4 Python处理Excel的边界什么时候该用Excel函数Python处理Excel虽然强但不是所有场景都合适。比如在业务部门临时要一份筛选报表领导只要点一下筛选就能继续看数这时候不要绕道Python直接在Excel里教他筛选更快。再比如条件格式、数据验证、颜色标记这类可视化格式pandas写不出来还是得靠Excel本身或者openpyxl去设置。我把这个边界总结成一句话需要一次性清洗、批量合并、跨版本比对时用Python需要日常看数、人工校验、做漂亮展示时用Excel。两者搭配着来比一味追求“全自动化”更高效。5. 行政区划Excel与常用工具联动5.1 Excel点转SHP让区划数据走地图可视化很多做GIS的人都有这种需求手里一份行政区划Excel里面有区划名称和经纬度坐标想把它变成点图层再叠加到底图上。常用的GIS工具支持从Excel读入点数据但导入时经常会遇到字段类型不符合预期的问题。经纬度列如果是文本格式坐标可能没法被正确识别为数值导致点落到奇怪的位置。我的做法是先在Excel里把经纬度列用“分列”功能强制转成数值或者用VALUE函数包一层。VALUE(trim(A2))转换完确认没有#VALUE!错误再另存为xlsx导入GIS工具。导入时注意坐标系选择一般用常用的地理坐标系经纬度是基于WGS84还是其他椭球体要弄清楚不然点位会有几百米的偏移。此外GIS工具要求点数据的x是经度、y是纬度千万别把列对应反了这种错误最隐蔽看起来能显示实际位置全错。5.2 Excel导入数据库这是数据联查的前提行政区划Excel在很多项目里不是终点是基础资料表。我经常要做的事是把它导入MySQL或者PostgreSQL然后业务表通过区划代码关联它来做联查统计。导入之前建议先把Excel里所有的表头改成英文字段名因为导入工具对中文表头兼容性参差不齐。这里给一个MySQL导入的思路先用pandas把Excel读取出来再批量生成INSERT语句。import pymysql conn pymysql.connect(hostlocalhost, userroot, password***, databasetest) cursor conn.cursor() for idx, row in df_district.iterrows(): cursor.execute( INSERT INTO t_district (code, province, city, district) VALUES (%s, %s, %s, %s), (row[区划代码], row[省级名称], row[市级名称], row[区县名称]), ) conn.commit()导入完要做的第一件事不是急着联查而是跑几条核对SQL例如统计每个省份的区县数量是否和Excel透视表一致。这能及时发现导入过程中编码错乱、换行符吃掉字段这类问题。5.3 从Excel转到Markdown表格写文档不再复制粘贴粘贴乱写技术文档时经常需要把行政区划Excel里的部分数据贴进Markdown表格。直接复制单元格再粘贴到Markdown编辑器里格式往往一塌糊涂。我推荐两种方式。小范围数据用在线表格转换工具把Excel区域复制进去点一下转换成Markdown格式大范围或者要自动化时用pandas直接输出成Markdown表格print(df_district.head(20).to_markdown(indexFalse))这样得到的表格干净、对齐写进文档不用再手工调格式。反过来从Markdown表格转换回Excel也常见。写技术方案时同事给了一个Markdown表格想要转成xlsx用pandas的read_markdown配合ExcelWriter就能实现。这种转换类的活儿用Python脚本做一遍以后还能复用。5.4 下载Excel文件后的Mac版注意事项如果你是Mac用户双击xlsx文件默认会用Numbers打开这会导致部分函数公式或条件格式显示不一致。我建议装一个Microsoft Excel for Mac或者至少用在线版的Excel来兼容。另外Mac上Excel的快捷键和Windows不完全相同比如筛选快捷键是组合键不同习惯了Windows操作的人刚切过去有点难受。处理行政区划数据这种需要频繁做筛选、去重、分列的场景我建议主力数据清洗还是放在Windows环境或者直接用Python脚本Mac上的Excel适合做展示和轻量修改。6. 行政区划Excel高频问题排查实录6.1 Excel不能复制粘贴怎么破处理区划数据时经常从网页或其他Excel文件复制内容结果粘贴过去没反应或者只粘贴成纯文本、格式全丢。这种情况多数是Excel的剪贴板状态出问题了。第一步先按一次Esc键取消某些插件或剪贴板霸屏状态。第二步检查Excel设置里“高级”选项卡下“剪贴板”相关选项确保“粘贴时显示粘贴选项按钮”是开启状态。第三步如果还是不行彻底退出Excel再重启一般能恢复。如果是跨软件复制比如从浏览器复制表格到Excel建议先用“选择性粘贴”里的“文本”或“Unicode文本”过渡一下格式问题后补。6.2 每次打开Excel就进安全模式加载项被禁用了有段时间我每次打开Excel都会弹“上次启动失败是否以安全模式启动”点否也照样进。后来排查发现是一个旧版插件导致启动时崩溃。解决方法是在“文件”-“选项”-“加载项”里把非官方的加载项全禁用然后逐个启用测试。常见的问题是加载项被禁用后某些按钮消失比如“Power Query”选项卡没了你加载Excel文件时就少了数据清洗入口。要恢复就在“COM加载项”里勾选对应项。这里提醒一句网上有些“加载项合集”来源不明装了之后可能导致Excel反复出问题能不用就不用。6.3 文件打开提示密码保护怎么办下载到的行政区划Excel如果带密码保护可以使用“打开密码”功能里的“只读推荐”方式试试直接打开。有些文件只是设置成“建议只读”并不是真的加密可以直接用“另存为”后去掉只读属性。如果真的遇到加密文件且没有密码不要随便下载来路不明的破解工具我试过一次文件没解开电脑倒是中了全家桶。正确做法是找文件的原始发布者要密码或者换一个数据源。行政区划数据本身是公开资料正规渠道提供的基本不需要密码。6.4 导入Excel时区划代码精度丢失这是最容易翻车的一类问题。Excel帮你把6位数字格式的区划代码在视觉上缩短了或者导入数据库时被自动转成了浮点数。处理方式有几种。Excel内输入代码前把目标列设置成文本用撇号前缀强制文本比如输入110101导入数据库时用LOAD DATA并且给字段设置成CHAR类型。最保险的是在Excel里加一列辅助列用TEXT函数把数值列转成文本列TEXT(A2,000000)这样无论原来A列的数值长什么样都会被格式化成6位文本。这个方法在处理“身份证号”“邮政编码”“区划代码”时都是同样的套路。6.5 Excel快速定位CtrlG在长表里找数据区划Excel动辄几千行手工滚动查找效率太低。用快捷键CtrlG可以调出“定位”对话框输入单元格地址直接跳转。比如想跳转到AQ列的中间位置输入AQ500后回车就行。定位还有一个功能很好用选中整个数据区域后按CtrlG点击“定位条件”-“空值”Excel会一下子选中区域内所有空白单元格你可以在同一个位置输入“待补充”并按CtrlEnter批量填充。这个操作在处理缺失的区划名称时效率极高。7. 数据更新与长期维护的几点体会行政区划数据有一个很反直觉的特点表面上每年只更新一次但某几个县区的代码和隶属关系可能在当年就发生变化。我做业务报表时遇到过这种情况上半年用的区划表和下半年对不齐分析结果出现了“凭空消失”的区域。后来养成一个习惯每次拿到新发布版本都会做一次“版本差异对比”看看新增了哪些代码、撤销了哪些代码、名称有没有调整。这个对比用Excel的VLOOKUP或Python的merge都可以做关键是要把旧版本存档不能直接覆盖。另一个体会是行政区划Excel一定要保留一份“原始版”和一份“工作版”。原始版是下载下来不动的底稿工作版可以在上面改格式、加字段、做关联。这样就算工作版被折腾坏了重新从原始版来一遍也就几分钟的事。我见过很多人直接在一个下载下来的文件上改来改去最终改到数据错乱再想回头已经找不到原文件了。最后再分享一个小技巧给区划Excel加上“最后核对日期”这个标记字段。不管是自己用还是发给同事别人拿到文件时能直观看到这份数据是否还有效。我自己维护的区划Excel里会在文件名的末尾加上版本日期比如“行政区划_区县级_2025v1.xlsx”。数据这种事版本管理做好了能省掉后面一堆数据口径争吵。