夜雨聆风这里有最实用的Excel使用技巧，通过提高Excel技能，可以让你轻松应对工作中的表格处理，提高你的工作效率！欢迎大家Follow关注~~
相信大家都会遇到这样的情形：做销售报表时，每次输入省份都得手动打字——打错了"广东"写成"广车"还要回头改。
更崩溃的是：选了"广东省"，下一列的"城市"栏还得手动输"广州、深圳、东莞……"
数据验证下拉菜单 + INDIRECT函数联动，点一下就能搞定，还能实现"选省自动出市"的动态联动效果。
一、基础篇：快速创建下拉菜单
痛点： 手动输入省份/部门/类别，不仅慢，还容易打错字。一个表格里"广东省"出现"广东"、"广东省"、"广东"三种写法，做数据透视表时哭都来不及。掌握我今天说的方法就可以杜绝这个方面的问题！
操作步骤（3步）：
第1步： 准备好数据源，比如把"广东、广西、福建、江西……"放在一个空白区域（比如F1:F10）
第2步： 选中你要设置下拉菜单的单元格区域（比如A2:A100）→ 数据 → 数据验证 → 数据验证
第3步： 允许→选择"序列" → 来源→框选F1:F10 → 确定
效果： 点击单元格就会出现下拉箭头，点一下就能选省份，再也不用打字了。
进阶：直接输入序列，不用辅助列
如果选项不多，可以不建辅助列，直接在"来源"框里输入：
广东,广西,福建,江西,湖南,四川注意：逗号必须是英文逗号。
二、进阶篇：动态联动下拉菜单
痛点： 选了省份之后，下一列的"城市"还得手动输入。而且不同省份的城市列表不一样——选了广东要出广州/深圳/东莞，选了广西要出南宁/桂林/柳州。
这才是数据验证的杀手锏——二级联动菜单。
实现步骤：
第1步：建立辅助数据区域
把每个省份的城市名放在一起，列标题用省份名称：
F | G | H | I |
|---|---|---|---|
广东 | 广西 | 福建 | 江西 |
广州 | 南宁 | 福州 | 南昌 |
深圳 | 柳州 | 厦门 | 九江 |
东莞 | 桂林 | 泉州 | 赣州 |
珠海 | 北海 | 漳州 | 景德镇 |
注意：F1="广东"，下方是广东的城市；G1="广西"，下方是广西的城市——列标题必须和省份名称完全一致。
第2步：给第一级省份设下拉菜单
选中省份列 → 数据验证 → 序列 → 来源框选F1:I1（省份所在行） → 确定
第3步：给第二级城市设下拉菜单——用INDIRECT函数
选中城市列 → 数据验证 → 序列 → 来源输入：
=INDIRECT(A2)假设A2是你选中的省份单元格。
INDIRECT的工作原理： 当你选了A2="广东"，INDIRECT("广东")就会自动找到名称为"广东"的列（也就是F列），然后把这个列的所有城市作为下拉选项。
核心： INDIRECT把文本转换成引用。=INDIRECT("广东") = F:F，所以下拉菜单会显示F列的所有城市。
三、高级篇：OFFSET+MATCH动态范围
痛点： 上面的方法用了整列作为数据源，如果城市数量不一样（广东有5个城市，广西只有3个），下拉菜单里会出现"空行"。
升级方案： 用OFFSET+MATCH组合，只取有数据的城市。
=OFFSET($F$1, 1, MATCH(A2, $F$1:$I$1, 0)-1, COUNTA(OFFSET($F$1, 1, MATCH(A2, $F$1:$I$1, 0)-1, 10, 1)), 1)拆解：
MATCH(A2, $F$1:$I$1, 0) → 找到省份在标题行的位置（广东=第1列）
OFFSET($F$1, 1, 位置-1, ...) → 从F1往下移1行，再右移N列，定位到城市列表
COUNTA(列表) → 统计城市有多少个（非空单元格数），作为高度
最终结果：只返回有数据的城市，没有空行
效果： 选"广东" → 下拉菜单显示5个城市 ✅ 选"广西" → 下拉菜单显示3个城市 ✅ 没有空行
四、实用场景
场景1：三级联动（省→市→区）
比二级再多一层，实现"选省出市、选市出区"的效果：
辅助区域结构 |
|---|
第一层：省份 → 行标题 |
第二层：城市 → 每个省份占一列，列标题=省份名 |
第三层：区/县 → 每个城市占一列，列标题=城市名 |
公式层层递进：
省份：普通下拉菜单
城市：
=INDIRECT(省份单元格)区县：
=INDIRECT(城市单元格)
场景2：动态部门+职位选择
HR录入表：选了"技术部"→职位下拉只显示"开发工程师、测试工程师、运维工程师" 选了"市场部"→职位下拉只显示"市场专员、品牌经理、广告投放"
和省市联动的原理完全一样，只是把省份换成部门，城市换成职位。
场景3：下拉菜单自动去重
痛点： 数据源可能有重复项，直接做下拉菜单会显示一堆重复值。
方案1（简单）： 先复制数据源 → 数据 → 删除重复值 → 再做下拉菜单
方案2（动态）： 用UNIQUE函数（Excel 2021+）：
=UNIQUE(数据源区域)把去重后的数据作为下拉菜单的来源。
五、常见错误及避坑
错误 | 原因 | 解决方案 |
|---|---|---|
下拉菜单是空的 | 来源区域没选对 | 检查序列来源是否包含数据 |
INDIRECT报错#REF! | 列标题名称和省份不匹配 | 检查省份名称是否完全一致（包括空格） |
下拉菜单有空白选项 | 数据源选了整列有空行 | 用OFFSET+COUNTA精确控制范围 |
联动不生效 | INDIRECT引用的区域超出了工作表 | 确保列标题所在的列都有名称定义 |
无法删除下拉菜单的选项 | 直接删除单元格数据，没改数据验证 | 选中区域→数据验证→全部清除 |
温馨提示： 下拉菜单做好后，如果在数据源区域插入新行（比如新增一个城市），下拉菜单会自动扩展——前提是你的序列来源引用了整列或足够大的范围。
如果是用OFFSET动态范围，新增城市会自动进入下拉菜单，不需要手动调整。
六、Python版（适合大数据量）
import pandas as pd# 读取省份城市数据df = pd.read_excel("省市数据.xlsx")# 实现类似下拉菜单的联动筛选province = "广东省"cities = df[df['省份'] == province]['城市'].tolist()print(f"{province}的城市：{cities}")# 三级联动district_data = { "广州市": ["天河区", "越秀区", "海珠区", "荔湾区"], "深圳市": ["南山区", "福田区", "罗湖区", "宝安区"],}def get_districts(city): return district_data.get(city, [])selected_city = "广州市"print(f"{selected_city}的区：{get_districts(selected_city)}")对于需要批量生成大量联动数据的场景（比如几千个城市），用Excel手动建辅助区域很慢，这个时候用Python一行代码就能搞定。
好了，今天的分享就到这里了，大家如果觉得有用的，欢迎点赞收藏，以防以后要用的时候找不到了，我们下期再见~
下期预告： 条件格式高级玩法——自动标记异常值、突出显示重复项、用公式设置动态格式，一篇文章让你成为Excel"格式化大师"。
附：长期坚持原创不易，如文章能够为大家带来少少帮助的，请大家点赞并转发，以支持我继续分享创作，你的支持将是我的不竭动力！谢谢！
（本文为本公众号原创，未经允许和授权，严禁转载，违者必究）
