📌 阶段二 · 数据管理 · 第5讲 分类汇总、数据有效性 · 约 50 分钟
给入库单装上防错下拉与范围校验，一键汇总各类商品的入库金额
学习目标
- 会给商品编号、供应商编号列设置序列下拉，从源头杜绝录错
- 会用整数范围、文本长度两种有效性规则拦截越界录入
- 知道复制粘贴能绕过数据有效性，会用圈释无效数据复查
- 掌握「先排序、后汇总」的分类汇总流程，会用多级汇总
- 会用定位可见单元格正确复制分类汇总结果
知识点讲解
数据有效性：给单元格立规矩
前面几讲都是「录错了再清洗」，这讲换个思路——录的时候就别让它错。
【数据-数据有效性】（新版本 Excel 叫「数据验证」）：选中目标列，设置允许输入的内容，不符合规则的一律弹窗拦截。它是进销存录入规范化的核心工具：编号下拉选、数量限范围、类别限清单。给入库单装好这三道栏杆，新人来录单也错不了。
整数范围：数量列只收 1~9999
选中入库单数量列-数据-数据有效性：允许选「整数」，数据选「介于」，最小值 1，最大值 9999，确定。之后再输入 0、-5 或 10000，都会被弹出窗口拦下。
有个大坑要记住：数据有效性只拦「手工输入」。从别的单元格复制一个 30000 直接粘贴过来，可以把有效性连同规则一起替换掉，违规值就这么混进来了。所以除了设置规则，还要定期复查（见 tips 的圈释无效数据）。
💡 提示:
- 复查神器：【数据-数据有效性】旁边的「圈释无效数据」，违规值全被红圈标出，改完选「清除验证标识圈」
- 录入列的规则设置范围要选到整列（或预留到未来行数），新增行才不受限
文本长度：编号列 5 位就是 5 位
选中入库单商品编号列-数据有效性：允许选「文本长度」，数据选「等于」，长度填 5。商品编号 WJ001 正好 5 位，手滑打成 WJ0011 或 WJ01 立刻报错。
注意各列位数不同：商品编号 5 位（WJ001），供应商编号 6 位（GYS001），客户编号 5 位（KH001），按实际位数分别设置。文本长度数的是字符个数，数字和字母都算 1 个。
💡 提示:
- 规则错一位，整列全被拦——设置完自己先录一条合法数据验证一下
序列下拉：最好的防错是「只能选」
选中目标列-数据有效性：允许选「序列」，来源框里输入待选项，多个选项之间用英文逗号分隔。比如类别列输入：办公文具,纸品,办公设备；结算方式输入：月结30天,月结60天,货到付款。确定后单元格右侧出现下拉箭头，只能从清单里挑。
进阶：来源框里也可以不打字，直接框选另一个表的区域（比如切到商品档案选编号列 A2:A31），下拉里就是全部商品编号——单据录入时商品编号不用手打，选就行。
再次强调：待选项之间必须用英文逗号，中文逗号会被当成一个长长的选项。
💡 提示:
- 下拉选项文本前后别带空格，否则选项列表里会出现「看起来一样实则不同」的幽灵选项
出错警告与输入法切换
数据有效性对话框还有两个选项卡：
出错警告：样式有「停止/警告/信息」三档。改成「警告」并自己写提示文字（如「请核对供应商编号！」），用户仍可以选择继续录入——相当于半保护状态，适合偶尔确有例外的情况；默认的「停止」则完全拦死。
输入法切换：可以设定进入该列自动切中文或英文。编号列强制英文，避免把 WJ 打成全角「ＷＪ」这种最难排查的错。
💡 提示:
- 「停止」拦死、「警告」提醒，编号列用停止，备注类列可用警告
分类汇总：先排序，后汇总
想知道每个商品累计进了多少货？【数据-分类汇总】一次搞定，但先回答三个问题：按什么分类（分类字段）、汇总什么（汇总项）、怎么汇总（汇总方式：求和/计数/平均值等）。
最重要的前提：必须先按分类字段排序！点分类字段列里任意单元格，先升序排好，再点分类汇总。不排序的话，同一个商品会拆成好几组，汇总会重复出现。
💡 提示:
- 分类汇总是「打组」性质的临时分析，做完不需要时点「全部删除」还原
多级汇总与结果复制
多级汇总（嵌套）：先按主要关键字+次要关键字排序（第 4 讲的多条件排序），第一次按主要字段做分类汇总；第二次按次要字段再做分类汇总时，务必取消勾选「替换当前分类汇总」，否则第一次的结果就被覆盖了。工作表左上角出现 1、2、3、4 层级按钮，点几级就显示几层明细。
复制汇总结果：直接复制会把明细全带上。老规矩：选中汇总区域-定位条件-可见单元格（Alt+;）-复制-粘贴。
隐藏妙用——批量合并相同内容单元格：先排序，对该列做分类汇总（汇总方式选计数），定位新列的空值-合并居中，再「全部删除」分类汇总，最后用格式刷把合并格式刷回原列。供应商名称列要合并展示时就这么干。
💡 提示:
- Alt+; 定位可见单元格，是分类汇总和筛选结果复制的通用钥匙
实战演练
流水会越录越多，靠人眼核对不现实。本讲实战给入库单装上三道防错栏杆（商品编号下拉、供应商下拉、数量范围），再给商品档案的类别列加下拉；最后用分类汇总回答老板的问题：每个商品累计进货金额是多少，哪类商品进货最多。
- 商品编号列挂下拉 — 打开入库单，选中 C 列（商品编号）-数据-数据有效性：允许「序列」，来源框点一下，切到商品档案框选 A2:A31，确定。回到入库单点 C 列任意格，下拉箭头出现，30 个商品编号任选。注意：一个单元格只能挂一条有效性规则，这里以序列下拉为主——下拉本身就是最好的防错，位数问题顺带解决。
- 供应商编号列挂下拉 — 选中 F 列（供应商编号），数据有效性-序列，来源框选供应商档案的编号区域。设置完后测试：点开下拉选 GYS001，能选中的才是对的。
- 数量列设整数范围 — 选中 G 列（数量），数据有效性：允许「整数」、介于 1 到 9999。试输 0 和 5000 验证：0 被拦截、5000 通过。再把出错警告样式改成「警告」，标题写「数量异常」，提示写「数量应在 1~9999 之间，请确认！」。
- 商品档案类别列加序列 — 切到商品档案，选中类别列数据区域，数据有效性-序列-来源输入：办公文具,纸品,办公设备（英文逗号）。以后新增商品类别只能三选一。
- 圈释无效数据复查 — 数据菜单里点「圈释无效数据」，已录流水里不符合规则的老数据全被红圈圈出（尤其检查有没有复制粘贴混进来的异常值），逐个修正后点「清除验证标识圈」。
- 加临时类别列并排序 — 分类汇总要按类别分组，但入库单没有类别列（第 11 讲学 VLOOKUP 后会自动带出）。先在 K1 手工输入表头「类别」，照着商品档案把每行的类别抄过来（本讲先手工，忍一忍）。然后点 K 列任意单元格升序排序，把同类商品排到一起。
- 按类别分类汇总金额 — 点数据区任意单元格-数据-分类汇总：分类字段「类别」，汇总方式「求和」，选定汇总项勾「金额」，确定。左侧出现 1/2/3 层级按钮：点 2 看各类别小计，点 3 看明细。回答：办公文具、纸品、办公设备谁进货最多？
- 复制汇总结果并清理 — 点 2 级视图只显示小计行，选中汇总区域按 Alt+; 定位可见单元格，复制到新表「入库汇总」粘贴。回入库单：数据-分类汇总-全部删除，删掉临时 K 列，把流水重新按日期排回去，Ctrl+S 保存。
在线练习
下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。
配套练习文件:lecture-05.xlsx(见本页底部「附件下载」)。
验收清单
逐项自查,全部通过即完成本讲:
- ☐ 入库单商品编号、供应商编号列都有了下拉
- ☐ 数量列输入 0 会被拦截，出错警告是「警告」样式并带自定义提示
- ☐ 商品档案类别列只能从三个类别里选
- ☐ 用圈释无效数据复查过全部流水，红圈清零
- ☐ 分类汇总前先按类别排了序，汇总没有把同类拆散
- ☐ 汇总小计已复制到「入库汇总」表，且用的是定位可见单元格