简介：这是一份面向数据处理与系统开发者的中国省、市、区三级联动Excel数据资源，由作者andyhebear整理发布。包内提供全国省市区XML数据、用户账号申请表模板以及可直接运行的测试程序，配合NPOI等.NET组件，便于在Excel或Web应用中实现“选省见市、选市见区”的联动下拉效果，适用于数据录入、统计分析、表单设计等场景。资源包共21个文件，大小2.1MB，主要包括9个DLL（NPOI、IRobotQ系列等Excel读写与自动化组件）、2个EXE（演示与测试程序）、1个XLS数据表、2个XML配置文件及6个PDB调试符号等，结构清晰，侧重程序化生成和处理Excel数据。已有3934人学习下载，覆盖全国省市区的行政区域数据，资源提供了从数据准备、编程接口到功能验证的完整链路。开发者可直接调用或改造这套工具集合，快速嵌入自己的Excel模板或C#项目中，避免重复收集和整理基础数据，也能作为学习Excel联动实现原理的参考样例。

1. 省市区三级联动 Excel：先搞清楚这份表帮你省了什么

做订单台账、客户档案、供应商信息表的时候，最烦的就是地址录入。业务员在"省"这一栏写"北京市"，下一行写"北京"，再下一行写"市辖区"，到了透视汇总阶段，光清洗地址就够你加两天班。省市区三级联动 Excel 解决的就是这个问题：在一张录入表里，省是一个下拉列表，选完省之后，市的下拉列表自动只剩这个省的城市，选完市之后，区的下拉列表自动只剩这个市的区县。它本质上是一份预置了数据验证、命名区域和明细数据源的表单模板，适合做数据汇总、地址库维护、门店信息采集这类重复性录入场景。你不需要懂 VBA，甚至不需要理解 INDIRECT 函数，拿到就能录。但如果你想在模板上加减字段、增加县以下片区，或者同事反馈"下拉怎么没了"的时候能自己解决，本文会把这张表的原理、参数和几个典型坑一次讲透。

2. 数据源与表结构：拿到文件后先查四件事，再谈使用

2.1 工作表与字段：明细表、辅助表、录入表分别放什么

一份做得规矩的三级联动 Excel，打开后不会只有一个工作表。哪怕资源里只给你一张"看起来能用"的表，它的背后也至少拆成了三层，理解这三层，你才知道改动时该动哪里。

明细表是地基。它的字段一般就三列：省、市、区。注意是一区一行，也就是"北京市 / 北京市 / 东城区"这样的粒度。有些版本会额外多一列行政区划代码，这列有没有不影响联动，但它能帮你去重和校验。录入模板表则是业务人员眼睛看得到的东西，字段通常比明细表更宽，比如后面跟着联系人、电话、详细地址。三级联动只控制前三列，后面那些字段是各自独立的普通单元格。

拿到文件先别急着填数据，按 2.2 的清单把地基检查一遍，能省掉后面九成的奇怪问题。

2.2 数据源检查清单：去重、空行、编码前导零与名称冲突

我给业务部门处理过太多来源不明的模板，发现 90% 的"联动失效"其实不是联动公式坏了，而是数据源本身不干净。检查清单就四件事。

第一，查空行。选中有数据的列区域，按 Ctrl+G 打开定位条件，选择"空值"，看明细表里有没有断档的空白行。如果 A 列第 100 行到第 150 行之间是空的，后面定义名称的"连续区域"就会少算一段，市下拉列表会莫名其妙缺城市。发现空行，右键删除整行，别留空行。

第二，查重复。这里说的重复不是市名重复，而是要按"省+市+区"整体去看。数据菜单里选"删除重复值"，勾选省、市、区三列一起删。单独拿区名去重会误删，因为全国叫"城关镇""朝阳区"这种名字的地块太多了。删完之后，你还需要重点排查一个细节：区划代码列是文本格式还是数值格式。如果代码是数字且以 0 开头（比如补齐到五位的自定义编码），单元格会被 Excel 自动吞掉前导零，看起来变成三位数。解决方式是把代码列先设成文本格式，再重新粘贴数据。

第三，查排序。明细表最好按省、市、区三级排序，保证同一个省的城市行是连续的，同一个市的区行也是连续的。为什么要连续？因为后面 3.3 节里批量定义名称的逻辑，依赖"同一个省的行范围是连续的一块"，不连续就要遍历筛选，复杂度上升一个量级。排序方式：在明细表加一个简单排序，主要关键字选省，次要关键字选市，第三关键字选区。

第四，查名称残留。按 Ctrl+F3 打开名称管理器，看看里面有没有重复的名称，或者明显不属于这套数据的陈旧名称。名称管理器是联动的心脏，残留名称多了，INDIRECT 解析时会套用到错误的那一个。看到名字可疑的，先看它引用到哪里，不是这份表在用的就删掉。

2.3 为什么用"全量明细 + 命名区域"而不是纯公式

很多第一次接触数据验证的人会问：为什么不用 FILTER 或者 LOOKUP 动态生成列表，非要预置一堆名字？这里有个硬约束：Excel 的数据验证"序列"来源，只接受"直接引用一个区域"或者"引用一个名称"，它不接受数组公式的结果，也不接受类似 =FILTER(...) 的返回值。在 Excel 365 里 FILTER 可以动态溢出，但把它塞进数据验证来源照样报错，WPS 的老版本更别想。

那为什么不给每个单元格单独写 =OFFSET(...) 这种公式来定位列表？理论上可以，实际上每个单元格的数据验证公式都要按它的行号调整，写起来非常痛苦，文件公式一多，打开速度和输入体验都会变差。用"全量明细表 + 预定义名称"的方案，好处是三层的关注点彻底分离：明细表管数据，名称管理器管查找关系，录入表只管下拉展示。将来要加新区域，只需改明细表和名称，录入表的公式一行不用动。

这里顺便对比一下另一种常见实现：辅助列 + OFFSET 动态区域。它的确能在不改名称的情况下自动扩容，但代价是有几百行辅助公式，且下拉展开时会卡顿。预置名称的方案在文件体积和响应速度上反而是最优解，代价是维护者需要懂一点名称管理器的操作。这份资源已经把名称预置好了，你要做的是理解它，而不是推翻重造。

3. 联动实现拆解：数据验证、INDIRECT 与命名区域的三角关系

3.1 一级下拉：数据验证的"序列"来源怎么写

三级联动的第一级是最简单的：省这一列的下拉，序列来源指向"省份列表"。选中录入表的 A 列数据区域，点"数据"菜单里的"数据验证"（有的版本叫数据有效性），在"允许"里选"序列"，然后在"来源"框里输入=省份。

这里有个关键参数：如果来源直接写=省份，说明"省份"是名称管理器里一个已存在的名称，它指向去重后的省级名单。如果你不想依赖名称，也可以直接在来源框写=区划!$L$2:$L$35这种引用地址，效果一样，但后面做动态扩容时不如名称灵活。

还要注意两个选项。"忽略空值"建议勾上，这样用户先不填省，后面再补录也不会被拦；"提供下拉箭头"必须勾上，否则单元格旁边那个小三角不显示，业务同事会以为模板坏了。另外，在"出错警告"页签里，把样式从"停止"改成"警告"或干脆关掉，因为这种模板经常有先用 Excel 录一部分、再从外部系统导回来覆盖的情况，严格拦截反而制造麻烦。

3.2 二级与三级下拉：INDIRECT 如何按选中文本找名称

二级下拉开始有"联动"的味道了。假设录入表第一行是标题，第二行开始录数据，A2 选的是省名，那么 B2 的数据验证来源应该写成：

=INDIRECT(A2)

理解这句公式是理解整张表的关键。INDIRECT 把它括号里的文本当作另一个引用去解析。A2 里的值是"北京市"，那么 INDIRECT("北京市") 就会去找名称管理器里名叫"北京市"的命名区域，这个区域指向明细表里所有属于北京市的市列表，于是 B2 的下拉就自动变成了北京的各区或各直辖市的对应行政区。省名一变，市下拉跟着换，这就是"联动"的实质。

第三级同理，C2 的数据验证来源是：

=INDIRECT(B2)

当 B2 的值是"东城区"，INDIRECT 就去找名为"东城区"的名称。注意这里有个隐藏的坑：全国同名区县很多，"朝阳区"在北京有，在长春也有。如果名称管理器里两个"朝阳区"都定义了，后定义的会覆盖先定义的，删选根本就乱了。这一点在第 4.2 节单独展开。

在实际模板里，A2、B2 这种单元格引用会随行号自动调整。比如第 10 行录数据时，B10 的验证来源自动变成=INDIRECT(A10)，这就是相对引用的好处——你只做一行，往下填充或者复制整行，联动逻辑跟着走。

3.3 名称管理的三个硬规则与批量生成思路

要自己维护名称，先记住三个硬规则。

第一，名称不能以数字开头，不能用空格和括号。中文可以，汉字开头的名称没问题，但"110101"这种就不能直接当名称用。如果你习惯用区划代码做名称，得在前面加字母前缀，比如s110101。

第二，名称在工作簿内必须唯一。同名区县这个问题前面提过，实际做法是给名称加前缀或后缀。比如把"朝阳区"的定义改成"北京朝阳区""长春朝阳区"，B2 下拉显示的值与名称必须完全一致，否则 INDIRECT 找不到。所以一旦决定用拼接名，录入表里市的显示文本也要跟着改成拼接名，用户看到的就是"北京市朝阳区"这种完整写法。

第三，名称引用的区域最好是连续矩形。用筛选或 SpecialCells 生成的多块区域虽然也能定义名称，但作为下拉数据源时可能只显示第一块，行为很怪。批量定义名称的正确姿势是：明细表按省排序，同一省的行连续，然后循环每一省，把它的第一行和最后一行算出来，再定义名称。这里给一段可以跑的 VBA 片段，原理是遍历明细表的 A 列，找每个省份出现的首个行号和最后一个行号：

Sub BuildCityNames() Dim ws As Worksheet Dim dict As Object Dim key As String Dim i As Long, lastRow As Long Dim firstRow As Long, lastR As Long Set ws = ThisWorkbook.Worksheets("区划") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Set dict = CreateObject("Scripting.Dictionary") ' 先用字典收集所有省份名称 For i = 2 To lastRow key = ws.Cells(i, 1).Value If Not dict.Exists(key) Then dict.Add key, 0 Next i ' 为每个省定义名称，范围是该省在城市列对应的连续行 For Each key In dict.Keys firstRow = 0 lastR = 0 For i = 2 To lastRow If ws.Cells(i, 1).Value = key Then If firstRow = 0 Then firstRow = i lastR = i End If Next i ThisWorkbook.Names.Add Name:=key, RefersTo:="=区划!$B$" & firstRow & ":$B$" & lastR Next key End Sub

逻辑说明：第一段遍历全省份，把去重后的省名放进字典；第二段对每个省再次线性扫描明细表，记录它第一次出现的行号和最后一次出现的行号，形成连续区域，然后定义成工作簿级名称。做好省一级，市一级的结构完全一致，只需把 A 列换 B 列、B 列换 C 列，按市名去循环扫描。参数说明：ws.Cells(ws.Rows.Count, "A").End(xlUp).Row是定位 A 列最后有数据的一行，比写死行号稳健；RefersTo里的字符串是真正的名称引用，必须带感叹号和绝对引用。

这段代码不是给你日常用的，日常使用资源里预置的名称就够了。但当你要把这份表从 334 个地级市扩展到自己添加自定义片区时，重刷一遍名称只需要几秒，比手动在名称管理器里改几百条引用强得多。

4. 避坑与排查：下拉失效的六个常见现场与对应解法

4.1 Excel 加载项被禁用：下拉箭头点了没反应的第一个怀疑对象

现象：文件明明有数据验证，但打开后单元格旁边没有下拉箭头，点单元格也不出列表；或者弹一句"无法找到命名区域"。原因：文件是从网上下载或邮箱转发过来的，被 Windows 标记为从网络获取，Excel 默认进入受保护视图，加载项和宏被禁用，连名称引用也可能被拦截。解决分两步。第一步，关掉文件，在文件夹里右键这个文件，选"属性"，在"常规"页签勾选"解除锁定"，再重新打开。第二步，把文件所在文件夹加入受信任位置：文件 → 选项 → 信任中心 → 信任中心设置 → 受信任位置，添加文件夹。文件夹内的文件不会再有加载项被禁用的问题。如果公司电脑更新策略后频繁出现禁用，检查"开发工具"菜单下的"COM 加载项"和"Excel 加载项"里有没有被勾掉的项目，重新勾上并重启 Excel。

4.2 市或区下拉一片空白：名称不存在与同名覆盖

现象：省下拉正常，选完省之后，市下拉展开是空的；或者某个市下面的区下拉只剩一两个选项，明显不对。原因基本两种。第一种，录入表里显示的省名和名称管理器里的名称差一个字，比如单元格是"北京市"，定义名称时写成了"北京"，INDIRECT 找不到任何东西，下拉就空了。第二种，同名覆盖，全国那么多"朝阳区""城关镇"，后面的定义把前面的覆盖掉了，于是某个市底下只剩最后定义那一批。解决：按 Ctrl+F3 打开名称管理器，先看名称是否存在，再看名称引用的地址范围对不对。区一级如果撞名严重，把录入表第三列的值改成"市名+区名"的拼接形式，对应名称也用拼接名，重跑一遍 3.3 的宏，问题立刻消失。要提醒的是，改动后数据验证来源要同步改成指向新名称，不然 INDIRECT 还是找旧名字。

4.3 复制粘贴后联动失效：数据验证不是单元格内容

现象：做好的模板让别人填，同事把已有行整行复制到下面新行，结果新行没有下拉，或者下拉选项还是复制源行那一套。原因：粘贴有两种情况。如果用的是"粘贴值"，数据验证根本不会被带过去，因为验证是单元格属性，不是内容。如果整行复制，验证会跟过去，但相对引用没调整到位时，INDIRECT 还指着原来的行，选了新省，市下拉还是旧省的城市。解决：规范动作是"复制 → 选择性粘贴 → 验证"，这样只会把数据验证规则粘贴过去，内容不带。更省事的做法是模板交付前往下预留二三十行，全部套好验证，让业务人员只在预设范围内填。如果已经乱了几行，最快的修复是选中坏掉的区域，重新执行一次数据验证设置，从上一行格式里用格式刷？格式刷不刷验证，这事很多人试过，明确告诉你不行，老老实实重新应用验证，或者用 5.2 节的一键宏批量刷。

4.4 WPS 与 Excel 表现不一致：兼容性处理三板斧

现象：公司有的人用 Excel，有的人用 WPS。同一份文件，Excel 里下拉正常，WPS 里市下拉空白；或者反过来，WPS 正常，发到 Excel 就弹"文件损坏"。原因：两套软件对名称引用和数据验证来源的解析策略有差异，尤其是 INDIRECT 跨表看名称的宽容度不同。解决三板斧。第一，所有名称都定义成工作簿级，不要定义成工作表级，工作表级名称在另一张表里用 INDIRECT 解析经常翻车。第二，数据验证的来源统一写=名称这种短写法，拒绝写=区划!$B$2:$B$16这种带路径的长地址，WPS 对长地址的兼容性更差。第三，文件另存为 xlsx 统一格式，不要用 xls，老格式在 WPS 和 Office 之间来回切最容易出怪问题。如果对方只有 WPS 且是较老版本，最稳妥的是在 WPS 里把数据验证重做一遍，公式逻辑不变。

4.5 排序、筛选与插行导致错乱：位置绑定与内容绑定的差别

现象：录入表收集了几百行数据后，你按"省"排序，排完发现有的行市和区对不上了。原因：数据验证是绑定在单元格位置上的，排序时如果只选了其中一列排序，其他列的验证不会跟着对应的行换位，于是 A 列的前 30 行是北京的数据，B 列却跑来了上海的数据。筛选不会改变数据的物理位置，但筛选隐藏行后，复制可见区域再粘贴，很容易把隐藏行的内容一起带出来。插行则更直接：在中间插入一行，新行没有验证，下面原有行的验证引用可能被 Excel 自动调整，指向偏移的行，肉眼很难察觉。解决：排序前必须全选整个数据区域，用"数据 → 排序"，不要只选单列；插行后立刻从相邻行用选择性粘贴把验证补过去。这里透露一个习惯：凡是涉及区域位置变动的操作，都先在名称管理器里看一眼引用范围有没有被"智能调整"改得乱七八糟，顺手改回来。

5. 进阶玩法：动态扩容、一键刷新与搜索式录入

5.1 只加数据不重做名称：结构化引用与引用范围放宽

三级联动最被人诟病的就是"加了新区域还要改名称"。其实有两招可以不碰名称管理器。第一招，省份列表用 Excel 的"表格"功能（Ctrl+T）制作。具体操作是把去重后的省份名单转成一个结构化表格，然后在名称管理器里把"省份"这个名称的引用改成=Table1[省名]这种结构化引用。以后在表格末尾新增省份，下拉列表会自动吞掉新行，不需要手动扩展名称范围。第二招，针对已经固定下来的省市，把名称的引用范围改得比你实际需要的大。比如"北京市"名称现在指向=区划!$B$2:$B$20，你直接改成=区划!$B$2:$B$100，多出来的空单元格不会在下拉里制造大量空白项——只要在数据验证里勾选了"忽略空值"，空白引用就不会显示成选项。这样以后给北京加新城区，只需在明细表 B 列按顺序追加，名称自动覆盖。两个技巧用下来，日常维护基本告别 Ctrl+F3 了。

5.2 一键批量重设下拉：用一段小 VBA 把验证刷到任意区域

业务部门把模板拿来，经常需要一次性给几千行套上三级验证，手动逐列设置能点出腱鞘炎。这时候可以跑一段 VBA，把当前选区按列重新应用验证规则：

Sub ReApplyValidation() Dim cell As Range Dim f1 As String ' 用法：先选中需要设置的录入区，比如 A2:C500，再运行本宏 For Each cell In Selection.Cells With cell.Validation .Delete ' 按列判断该写入哪一级下拉来源 If cell.Column = 1 Then f1 = "=省份" ElseIf cell.Column = 2 Then f1 = "=INDIRECT(A" & cell.Row & ")" ElseIf cell.Column = 3 Then f1 = "=INDIRECT(B" & cell.Row & ")" End If .Add Type:=xlValidateList, Formula1:=f1 .IgnoreBlank = True .InCellDropdown = True End With Next cell End Sub

逻辑说明：遍历选区的每个单元格，先删掉旧验证，再按列号判断是省、市还是区，分别写入对应的序列来源。二级和三级来源里的cell.Row是将当前行号拼进 INDIRECT 公式，保证每一行的联动引用指向自己这一行。参数说明：xlValidateList表示序列型数据验证；Formula1就是序列来源；IgnoreBlank=True允许空值；InCellDropdown=True显示下拉箭头。如果你要刷的范围很大，几万行也不怕，这种循环还能接受。注意：选区里不要包含标题行，标题行没有联动语义，被刷上验证反而碍事。

5.3 记不全区名也能录：VLOOKUP 辅助搜索与自动回填

省市区三级联动要求用户知道"哪个区属于哪个市"，现实里业务人员经常只记得"朝阳区""长安区"这种地名，不知道它隶属哪。与其逼着他们翻列表，不如在模板右侧做一个搜索辅助区。做法是在明细表里加一列辅助列，写=A2&B2&C2，把省市区拼接成一个长文本。模板右侧预留几个单元格，H1 输入要搜的关键字，下面用公式提取第一条匹配的记录：

=IF(COUNTIF(区划!$D:$D,"*"&$H$1&"*")=0,"没有匹配",INDEX(区划!$A:$A,MATCH("*"&$H$1&"*",区划!$D:$D,0)))

这个公式返回匹配到的省名，配合另外两个把 B 列和 C 列同样取出来的公式，就能展示完整的"省-市-区"。逻辑说明：COUNTIF 先判断有没有匹配，没有就直接提示；有的话用 MATCH 以通配符方式找到第一匹配行，INDEX 取出该行对应列的值。注意它的边界：通配符匹配命中的是第一条，同名区县可能命中错的那条，所以这个辅助区只能用于快速定位，正式入库还是要在三级下拉里核对一遍。如果你确实需要"按省过滤后再搜区"，老版本 Excel 需要数组公式，按 Ctrl+Shift+Enter 确认，新版本可以直接回车，公式结构是MATCH(1,INDEX((区划!$A$2:$A$5000=$E$1)*(ISNUMBER(SEARCH($H$1,区划!$C$2:$C$5000))),0),0)，这属于加餐，数据量小的时候体感差异不大。

6. 交付前先跑一遍验证：让三级联动在别人手里也不翻车

模板做出来，自己电脑上试了三遍都觉得没问题，发给业务部门半小时后就有人喊"下拉没了"。这种事我遇到过不止一次。与其事后救火，不如交付前强制走一个五分钟的验证流程。

第一步，清空重选。把 A、B、C 三列的内容全部清掉，重新在 A 列选一个省，看 B 列下拉是否只剩这个省的城市；选完市再看 C 列，确认区对应。这一步能筛掉大部分名称失效问题。

第二步，随机抽检。闭上眼在省列表里随便点 3 个省，每个省选 3 个市，每个市选 2 个区，核对选出来的区和市是真的隶属关系。抽查的命中率比全量测试高得多，因为易错的恰好是那些名字接近的节点。

第三步，名称体检。Ctrl+F3 打开名称管理器，确认名称数量符合预期，引用范围没有指向空行。看到莫名其妙的残留名称，直接删掉，别犹豫。

第四步，跨软件测试。如果团队里有人用 WPS，把文件另存一份副本用 WPS 打开，走一遍第一步和第二步。没有条件测 WPS 的话，至少确认文件后缀是 xlsx，并且所有名称是工作簿级。

第五步，空白兜底。在录入表最后一行往下多预留二十行，提前套好验证，确认新行也有下拉。这样业务人员自己往下填的时候不会发现"到第 300 行就加不了数据了"。

这套流程跑完，模板交出去基本不会挨骂。我自己的习惯是每次发模板之前，还会顺手清一下名称管理器里的备份名称，把引用范围统一一遍，再把"出错警告"从"停止"改成"警告"。从那以后，凡是经我手出去的省市区三级联动表，几乎没有因为联动问题被退回来过。希望帮到你。

本文还有配套的精品资源，点击获取