告别小白,每天5分钟!Excel基础练习30天打卡计划(第9天)
- 2026-11-09 17:49:04
哈喽，小伙伴们，第九天打卡啦！👋
不知不觉我们已经坚持了一周多，从最基础的表格搭建，到公式计算、排序筛选、数据透视表、图表制作、条件格式、查找替换，再到昨天的文本处理——你的Excel技能树正在茁壮成长！给自己点个大大的赞！
今天我们来学习一个让表格变得“智能”的功能——数据验证（有效性）进阶用法。在第1天的练习中，我们简单用过数据验证来限制销售数量不能为负数。今天，我们要把它玩出花来：制作下拉菜单、实现二级联动、设置输入提示和出错警告，让表格既好用又防错！
📅 练习背景设定
继续使用 “销售练习-我的名字.xlsx” 文件。今天我们会在“1月销售记录”工作表中，对现有的表格进行升级，添加各种数据验证，让这个销售表格变得更加“智能”和“防呆”。
✍️ 今日练习题目（第9天）
核心技能：序列下拉菜单、二级联动下拉菜单、自定义输入提示、自定义出错警告、圈释无效数据、复制数据验证。
请打开文件，跟着我一起操作：
第一部分：基础回顾——限制数值范围
在第1天的练习中，我们给销售数量（D列）设置了数据验证，限制只能输入大于0的整数。我们先来回顾并优化一下。
检查现有验证：
选中 D4:D100 区域。
点击 “数据” 选项卡 → “数据验证”（有些版本叫“数据有效性”）。
查看“允许”下拉菜单，确认是 “整数”，数据是 “大于”，最小值是 0。
优化提示信息：
切换到 “输入信息” 选项卡。
勾选 “选定单元格时显示输入信息”。
标题输入：“数量填写提示”
输入信息输入：“请输入大于0的整数，例如：50”
点击确定。
测试效果：
点击D列任意单元格，会出现一个黄色的提示框，友好地告诉用户怎么填。这样别人用你的表格时就不会乱填了！
第二部分：制作下拉菜单——让产品名称规范录入
现在C列（产品名称）是手动输入的，容易出现“笔记本”写成“笔记”、“笔本”等不规范的情况。我们给它做一个下拉菜单，让用户只能从预设的选项中选取。
准备选项列表：
我们可以在另一个地方列出所有可选的产品名称。比如在Z列（或其他空白列）输入：
笔记本中性笔文件夹订书机笔筒计算器
也可以直接把这些选项写在数据验证的设置框中（用英文逗号分隔）。
设置数据验证：
如果用单元格区域：点击右侧的图标，选中刚才输入的Z1:Z6（或你存放选项的位置），再点回来。注意要按F4锁定区域（比如
=$Z$1:$Z$6）。如果直接手动输入：在来源中输入
笔记本,中性笔,文件夹,订书机,笔筒,计算器（注意用英文逗号分隔）。选中 C4:C100 区域。
“数据” → “数据验证”。
在“设置”选项卡中，“允许”选择 “序列”。
“来源” 输入框：
确保 “提供下拉箭头” 已勾选。
切换到“输入信息”选项卡，设置提示：“请从下拉列表中选择产品”。
点击确定。
测试效果：
点击C列任意单元格，右侧会出现一个下拉箭头，点击就能选择产品。再也输不乱了！
第三部分：进阶玩法——二级联动下拉菜单
这是今天的重头戏！比如我们想实现：先选“产品类别”，再选该类别下的“具体型号”。一级选“笔记本”，二级只能选“轻薄本、游戏本、商务本”；一级选“中性笔”，二级只能选“黑色、红色、蓝色”。
1.准备数据源：
在E1输入“笔记本”，在E2:E4输入：轻薄本、游戏本、商务本。
在F1输入“中性笔”，在F2:F4输入：黑色、红色、蓝色。
在G1输入“文件夹”，在G2:G4输入：A4、A5、透明款。
这样每个类别作为一列，下面是对应的型号。
A1输入 “类别”，B1输入 “型号”。
A2:A4依次输入：笔记本、中性笔、文件夹。
在B2:B4，与笔记本同行输入：轻薄本,游戏本,商务本（这三个要放在一个单元格里，用英文逗号分隔？不，更好的方式是分开列）。
在一个新工作表（比如命名为“数据源”）中，构建如下结构：
更规范的做法：
2.设置一级下拉菜单（产品类别）：
回到“1月销售记录”工作表，在C列旁边插入一列，作为“产品类别”（比如新C列，原C列往后移）。
选中新C列（产品类别）的数据区域。
数据验证 → 序列 → 来源选择
笔记本,中性笔,文件夹（或引用存放类别的单元格区域）。确定。
3.设置二级下拉菜单（具体型号）：
解释：INDIRECT函数会把C4中的文本（比如“笔记本”）转换成单元格引用。它会在当前工作簿中查找名为“笔记本”的区域（也就是我们在数据源中定义的E列）。
选中D列（具体型号）的数据区域。
数据验证 → 序列。
关键步骤：在“来源”中输入公式：
=INDIRECT(C4)但这里有个问题：INDIRECT要求“笔记本”是一个已定义的名称，而不是单纯的列标题。所以我们需要先定义名称。
定义名称：
回到“数据源”工作表。
选中E1:E4（笔记本类别下的所有型号，包括E1标题吗？不包括标题，只选型号）。
点击 “公式” 选项卡 → “定义名称”。
在“名称”输入框中输入：笔记本（注意：必须和一级菜单的选项文字一模一样）。
确定。
同样，选中F1:F4，定义名称为 “中性笔”。
选中G1:G4，定义名称为 “文件夹”。
2.重新设置二级下拉菜单：
回到“1月销售记录”，选中D列数据区域。
数据验证 → 序列 → 来源输入：
=INDIRECT(C4)确定。
3.测试二级联动：
在C列选择“笔记本”，然后点击D列下拉箭头，只能看到“轻薄本、游戏本、商务本”。
换选“中性笔”，D列下拉菜单自动变成“黑色、红色、蓝色”。
完美！这就是经典的二级联动下拉菜单！
第四部分：自定义出错警告——防错的最后一道防线
除了输入提示，我们还可以设置当用户输入不符合规则时，弹出明确的警告信息。
为销售额（F列）添加范围限制：
销售额应该是正数，而且不能太大。我们限制在0到10000之间。
选中 F4:F100。
数据验证 → 设置选项卡 → “允许”选择 “小数” → “数据”选择 “介于” → 最小值输入 0.01，最大值输入 10000。
2.设置出错警告：
切换到 “出错警告” 选项卡。
样式：选择 “停止”（最严厉，阻止输入）。还有“警告”（可以选否继续）和“信息”（只提示，不阻止）。
标题：“输入错误”
错误信息：“销售额必须在0.01到10000之间，请检查！”
点击确定。
3.测试效果：
在F列输入
-100或20000，按回车，Excel会弹出你自定义的警告框，拒绝输入，直到输入合法数值。
第五部分：圈释无效数据——事后检查
如果表格中已经存在不符合数据验证规则的数据，可以用“圈释无效数据”快速找出。
制造一个无效数据：
在D列（销售数量）手动输入一个负数，比如
-5。因为之前设置了大于0的限制，现在这个单元格其实是不合法的，但因为是之前输入的，没有被阻止。
圈释无效数据：
选中D列数据区域。
点击 “数据” 选项卡 → “数据验证” 旁边的下拉箭头 → 选择 “圈释无效数据”。
瞬间，所有不符合规则的单元格（包括刚才输入的-5）会被红色椭圆圈起来，一目了然！
清除圈释：
同样点击下拉箭头，选择 “清除验证标识圈”。
第六部分：复制数据验证
如果你已经设置好某一行或某一列的数据验证，想快速复制到其他区域，不需要重新设置。
方法一：复制粘贴：
复制已经设置好验证的单元格 → 选中目标区域 → 右键 → “选择性粘贴” → 选择 “验证” → 确定。
方法二：拖动填充柄：
选中已设置验证的单元格，向下拖动填充柄，验证规则会被复制。
今日小贴士
INDIRECT函数：这是实现二级联动下拉菜单的核心。它的作用是把文本转换成单元格引用。但注意，INDIRECT是易失性函数，当工作簿有其他变化时会重新计算，不过对下拉菜单来说影响不大。
下拉菜单太长怎么办：如果下拉菜单的选项太多，可以用“来源”引用整列，比如
=$Z:$Z，但这样会显示很多空行。更好的做法是使用“表格”功能，或使用动态名称（OFFSET+COUNTA）。三级联动：学会了二级联动，三级联动也是一样的原理，用INDIRECT引用二级菜单的值作为名称即可。
数据验证不支持字体颜色：下拉菜单的字体颜色无法单独设置，但可以通过条件格式来美化。
保护工作表：如果不想让用户修改数据验证的设置，可以在“审阅”选项卡中保护工作表。
恭喜你！今天你学会了数据验证的进阶用法，你的表格已经具备了“智能输入”的能力。下拉菜单让录入变得规范，二级联动让选择更精准，输入提示和出错警告让用户不会犯错。这些技巧在制作模板、收集数据时非常实用！
明天预告：保护工作表与单元格锁定。如何让别人只能编辑你指定的区域？如何隐藏公式？如何设置打开密码？明天的内容让你的表格既开放又安全！
记得保存文件，我们明天见！有任何问题评论区留言~ 👋
加群领取今日练习文件
