一份 .xlsm 把 Excel 玩明白了:录入、反查、批量模糊查询,全在一个文件里

需要原始表格请转发后留下电子邮件地址。

很多 Excel 教程只讲一个技巧。但这份《人力档案输入模板》不太一样：它把「填表 → 存档 → 单人反查 → 批量筛选」做成了一条完整流水线，而且几乎把 Excel 里能玩的东西都串了一遍——数据验证、名称管理器、INDIRECT 级联下拉、批注、表单控件、VBA、VLOOKUP+MATCH、FILTER、FIND、IFS、数组公式、条件格式。

更妙的是：查询表里没有一行 VBA。23 个复选框 + 一条 FILTER 数组公式，就实现了「想查几列就打几个勾」「姓名模糊匹配」「年龄区间」「入职日期时间段」的组合查询。

说明：原需求里写「把步骤和公司列清楚」，结合上下文按「公式」理解（原文件并无「公司」字段）。本文所有公式、单元格地址、VBA 代码均从该 .xlsm 文件中逐项提取核对；示例身份证号与手机号已做脱敏。

一、先把文件说明白：它到底是什么

1. 启用宏
   ：打开时点「启用内容 / 启用宏」，并确认文件仍保存为 .xlsm。禁用宏则「保存」按钮、身份证自动提取全部失效。
2. 确认版本支持动态数组
   ：查询表用到 FILTER、IFS，需要 Excel 365 / 2021 及以上或较新的 WPS；老版本会显示为 #NAME?（文件里保留着 WPS 写入的 _xlfn._xlws.FILTER、_xlfn.IFS 兼容前缀，属于正常现象）。
3. 先备份原始示例数据
   ：工作表未加保护，公式区域可直接覆盖。建议另存副本再动手。

二、第 1 张表「录入」：把 Excel 当成软件界面来用

这张表看起来就是一张员工登记表：A 列是字段名，B/D/F 列填内容。但它其实是一个「表单」——所有输入都被限制、被校验，最后按一次按钮整行写进底表。

2.1 操作步骤（照着点一遍就会）

1. 在 B2 填姓名（唯一必填项，空了会弹窗提示并把光标送回 B2）。
2. F2 民族 / F3 政治面貌 / B4 健康状况 / D5 婚姻状况 / F8 职称 / F9 学历
    都是下拉；D4 身高 限 150–200、F4 体重 限 60–110 的整数。
3. 在 B5 填身份证号。保存时 VBA 会做 18 位校验码验证（兼容 15 位旧号），并自动回填 B3 出生日期、D3 年龄；D2 性别 则是表内公式直接算出来的，边填边显示。
4. B6:F6（户籍所在地）
   、B7:F7（现居住地） 是省 → 市 → 区县三级联动下拉，E、F 列手填乡镇与门牌（单元格批注里写着「乡镇」「村小区门牌号」）。保存时 VBA 用空格把 5 段拼成一个完整地址。
5. 补齐 B8:F10：紧急联系人、电话、职称、毕业院校、专业、学历、开户行、银行卡号、入职日期。
6. 点 B13 附近的「保存」按钮 → 弹窗提示「员工信息已成功保存到数据表的第 N 行！」→ 表单自动清空，光标回到 B2，可以继续录下一个人。

2.2 这张表用到的设置，逐条列清楚

2.3 公式 ①：身份证第 17 位判男女

=IF(MOD(MID(B5,IF(LEN(B5)=18,17,15),1),2)=1,"男","女")

LEN(B5)=18 时取第 17 位，15 位旧身份证取第 15 位；MOD(…,2)=1 为奇数即男。一个 IF 把两种身份证长度都兼容了。

2.4 VBA：按钮背后到底写了什么

按钮是「开发工具 → 插入 → 表单控件 → 按钮」，右键「指定宏」指向 保存员工信息。整个模块约 250 行：1 个主过程 保存员工信息（约 125 行）+ 7 个 Function（其中 2 个是 Private 辅助函数），负责校验与计算。

主过程里最关键的四行（完整源码见文末附录与速查表）：

lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
nextRow = IIf(lastRow >= 2, lastRow + 1, 2)
With wsData
    .Cells(nextRow, "A").Value = wsForm.Range("B2").Value   ' 姓名
    .Cells(nextRow, "B").Value = wsForm.Range("D2").Value   ' 性别
    ...                                                     ' 一直写到 W 列
End With
wsData.Columns("D").NumberFormat = "yyyy-mm-dd"   ' D / W 两列强制日期格式
wsForm.Range("B2,D2,F2,B3,...,F10").ClearContents  ' 清空 29 个录入格（保留 C8）
MsgBox "员工信息已成功保存到数据表的第" & nextRow & "行！", vbInformation

这里有两个很实用的写法值得抄走：一是 Cells(Rows.Count,"A").End(xlUp).Row 定位末行，配合 IIf(lastRow>=2,…) 保证表头永不被覆盖；二是写日期时用 .Value2 = CDbl(birthDate) 并强制 NumberFormat，专门解决 WPS 把日期存成「1973/8/19/周日」文本的问题。

2.5 录入 → 数据表：23 个字段怎么对应

底表字段顺序固定为：A 姓名、B 性别、C 民族、D 出生日期、E 年龄、F 政治面貌、G 健康状况、H 身高、I 体重、J 身份证号码、K 婚姻状况、L 联系电话、M 户籍所在地、N 现居住地、O 紧急联系人、P 联系人电话、Q 职称、R 毕业院校、S 专业、T 学历、U 开户行、V 银行卡号、W 入职日期。

记住这个顺序：后面「反向填充」的 MATCH、「查询」的 FIND(C6,数据!Q2:Q995) 找职称、FIND(E6,数据!T2:T995) 找学历，全都依赖这个列位置。底表一旦插列，查询公式就要跟着改。

三、第 2 张表「反向填充」：一个人，23 个字段一次带出

3.1 操作步骤

1. 点 B2 右侧下拉选姓名（数据验证来源是 =数据!$A$2:$A$40）。
2. B3、D3、F3 … F11 共 23 个单元格自动带出该员工全部档案。
3. B7:F7、B8:F8 是合并单元格，专门放较长的户籍 / 居住地址；B4 与 F11 套了 yyyy/m/d、yyyy"年"m"月"d"日" 自定义格式，所以日期显示得好看。

3.2 公式 ②：VLOOKUP + MATCH（23 格同一个套路）

=VLOOKUP($B$2,数据!$A:$W,MATCH(A3,数据!A1:W1,0),0)

以 B3 为例：$B$2 是被查的姓名（绝对引用，23 个公式锁定同一个人）；数据!$A:$W 是查找区域；MATCH(A3,数据!A1:W1,0) 用左边标签文字去底表表头里找列号，所以公式里没有写死的 2、3、4；最后 0 精确匹配。

D3 只是把 A3 换成 C3，F3 换成 E3，其余不变：

B3：=VLOOKUP($B$2,数据!$A:$W,MATCH(A3,数据!A1:W1,0),0)
D3：=VLOOKUP($B$2,数据!$A:$W,MATCH(C3,数据!$A$1:$W$1,0),0)
F3：=VLOOKUP($B$2,数据!$A:$W,MATCH(E3,数据!$A$1:$W$1,0),0)
……
F11：=VLOOKUP($B$2,数据!$A:$W,MATCH(E11,数据!$A$1:$W$1,0),0)

为什么值得学：新手常写成 VLOOKUP($B$2,数据!$A:$W,4,0)。底表一插列，20 多个公式全部错位。用 MATCH 按「字段名」找列，插列、调序都不用改公式——这正是「反向填充」这个名字的精髓：不是把表单写进表，而是把表反向填回表单。

四、第 4 张表「查询」：打勾就选列，模糊 + 时间段批量筛选

这是全文件最精彩的一页。先看它的三层结构：

4.1 操作步骤

1. 第 1 行打勾
   ：A1:W1 对应 23 个字段各有一个复选框，勾上就输出该列。当前默认勾选 9 项（姓名、性别、民族、出生日期、年龄、身份证号码、联系电话、学历、入职日期）。
2. 第 5–6 行填条件
   ：8 个输入框，留空表示「不限」。姓名、职称、学历支持包含即命中（模糊），年龄、入职日期支持起止时间段。
3. 结果自动出现
   ：第 7 行表头、第 8 行起数据，全部由数组公式溢出，不需要点任何按钮。
4. 换条件 / 换勾选
   ：直接改输入框或勾，结果立即重算。
5. 右侧 M6「简单的公式」 区域是作者留的对照实验：M7:U1000 用一版更短的公式，可以对比两种写法。

4.2 复选框是怎么变成公式能读的东西的

这一步是整套设计的机关。复选框本身不参与计算，它只做一件事：把 TRUE / FALSE 写进链接单元格。

1. 开发工具 → 插入 → 表单控件（窗体控件）→ 复选框，在第 1 行 A:W 每列放一个，标签文字写在第 2 行 A2:W2。
2. 右键复选框 →「设置控件格式」→「控制」→ 单元格链接 分别设为 $A$1、$B$1 … $W$1。
3. 于是 A1:W1 就是一列布尔值：TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,…,TRUE。
4. FILTER 的第 2 参数直接吃这个布尔数组 —— 勾选 = 保留该列。

把「界面交互」翻译成「公式能读的布尔数组」，是 Excel 里非常经典的一招。同理还能用选项按钮、组合框、数值调节钮驱动 CHOOSE / INDEX / OFFSET。

4.3 8 个条件输入框，各自管什么

4.4 三条核心公式（原文，已去掉 WPS 的兼容前缀）

下面公式均按原文件逐字抄录，只是为便于阅读做了换行；Word 版还会按页宽折行。需要直接复制粘贴的单行原文，请用随文附送的《公式与代码速查.md》。

=FILTER(数据!A1:W1,A1:W1,"未选择")

用第 1 行的 TRUE/FALSE 横向筛底表表头，勾几项就出几列；一个都不勾就显示「未选择」。表头与数据用同一个布尔数组，天然对齐、不会错位。

=FILTER(FILTER(数据!A2:W995,A1:W1),
(IFERROR(FIND(C5,数据!A2:A995),0))*(IFERROR(FIND(E5,数据!B2:B995),0))*(IFERROR(FIND(C6,数据!Q2:Q995),0))*(IFERROR(FIND(E6,数据!T2:T995),0))*
IFS(AND(ISBLANK(G5),ISBLANK(I5)),(IFERROR(FIND(C5,数据!A2:A995),0)),AND((G5>=0),ISBLANK(I5)),(数据!E2:E995>=G5),AND(ISBLANK(G5),(I6>=0)),(数据!E2:E995<=I5),AND(G5>0,I5>0),((数据!E2:E995>=G5)*(数据!E2:E995<=I5)))*
IFS(AND(ISBLANK(G6),ISBLANK(I6)),(IFERROR(FIND(C5,数据!A2:A995),0)),AND((G6>=0),ISBLANK(I6)),(数据!W2:W995>=G6),AND(ISBLANK(G6),(I6>=0)),(数据!W2:W995<=I6),AND(G6>0,I6>0),((数据!W2:W995>=G6)*(数据!W2:W995<=I6))))

=FILTER(FILTER(数据!A2:W995,A1:W1),IFERROR(FIND(C5,数据!A2:A995),0)*IFERROR(FIND(E5,数据!B2:B995),0)*IFERROR(FIND(C6,数据!Q2:Q995),0)*IFERROR(FIND(E6,数据!T2:T995),0)*IF(G5="",1,数据!E2:E995>=G5)*IF(I5="",1,数据!E2:E995<=I5)*IF(G6="",1,数据!W2:W995>=G6)*IF(I6="",1,数据!W2:W995<=I6),"无匹配数据")

4.5 逐个拆开讲

① 嵌套 FILTER，一次管两个方向：内层 FILTER(数据!A2:W995, A1:W1) 是「横向」筛选（按列）；外层 FILTER 是「纵向」筛选（按行）。两层叠起来，既是选列又是筛行。

② IFERROR(FIND(条件, 区域), 0) 就是模糊匹配：FIND 找到返回位置数字（≥1，被当作 TRUE），找不到报错 → IFERROR 变 0（被当作 FALSE）。条件留空时 FIND("", 文本) 返回 1，所以空条件不会淘汰任何行——这个细节非常关键，省掉了一堆 IF 判断。

③ 多个条件相乘 = AND：每个条件生成一条 994 行的 0/1 数组，逐元素相乘，只要有一个 0 整行就变 0，外层 FILTER 直接丢掉。这是不用辅助列做多条件筛选的标准写法。

④ 时间段用 IFS 处理四种输入组合：起止都空 / 只填起始 / 只填终止 / 都填。完整版用 IFS + ISBLANK 分四支；简化版用 IF(G5="",1,数据!E2:E995>=G5) 一句顶一支，更短、更好读，还不容易写错。

⑤ 条件格式：A7:L7 N7:XFD7 上有一条公式规则 A$7<>0，用于给非空表头着色；M 列被刻意排除，因为 M6 是「简单的公式」对照区标题。

4.6 完整版 vs 简化版，怎么选

五、第 5 张表「行政区划 」：370 个定义名称

这张表 A1:AM411 是一张字典：按省份分块摆放省、市、区县名单。文件在名称管理器里定义了 370 个名称，例如 省份 → '行政区划 '!$A$2:$A$33、山东省 → '行政区划 '!$R$43:$R$58、济宁市 → '行政区划 '!$F$65:$F$75。

5.1 三级联动的复刻步骤

1. 把省市县名单整理进一张表，每块区域各自命名（名称必须与上一级下拉的文字完全一致，不能有多余空格）。
2. B6:B7 数据验证 → 序列 → 来源 =省份。
3. C6 数据验证 → 序列 → 来源 =INDIRECT($B$6)；INDIRECT 把「山东省」这三个字转成同名区域的引用，于是自动列出该省所有市。
4. D6 来源 =INDIRECT($C$6)，同理列出该市区县。现居住地 B7/C7/D7 完全一样。
5. E、F 列留作手填乡镇与门牌，VBA 用 Join(Array(B6,C6,D6,E6,F6), " ") 拼成完整地址写入 M 列。

最容易踩的坑

：这张工作表的名字是「行政区划 」——末尾有一个空格。所有 370 个名称都写成 '行政区划 '!$A$2:$A$33。一旦重命名工作表或删掉那个空格，三级下拉会集体失效，而且报错很隐蔽。

六、把「各种应用」一次列全

这份文件之所以值得拆，是因为它几乎是一本 Excel 应用索引。按技术点整理如下：

七、照着复刻一遍：12 步清单

1. 新建工作簿，建 5 张表：录入、反向填充、数据、查询、行政区划 （记住末尾空格）。
2. 在「数据」A1:W1 写死 23 个字段标题；把 J、L 列设为文本格式，D、W 列设为 yyyy-mm-dd。
3. 在「行政区划 」填省市县字典，用「公式 → 根据所选内容创建 / 名称管理器」批量定义名称。
4. 搭「录入」表单：字段标签、输入格、数据验证（枚举 / 区间 / 三级 INDIRECT）、6 条批注。
5. 在 D2 写性别公式。
6. Alt+F11 插入模块，粘贴 保存员工信息 与 6 个函数；插入表单按钮，右键指定宏。
7. 插入「保存」按钮后试录一条，确认数据追加到底表末行、表单清空。
8. 搭「反向填充」：B2 设姓名下拉，B3 起按标签写 VLOOKUP+MATCH，合并 B7:F7 / B8:F8，套日期格式。
9. 在「查询」第 1 行插入 23 个复选框，逐个设置单元格链接 $A$1…$W$1，第 2 行写字段名。
10. 在「查询」第 5–6 行排好 8 个条件标签与输入格，E5 加「男,女」下拉。
11. 选中 A7:I7 输入表头 FILTER；选中 A8:I1001 输入批量查询 FILTER（数组公式，老版本需 Ctrl+Shift+Enter）。
12. 补上条件格式 A$7<>0，另存为 .xlsm，启用宏后整体回归测试。

八、避坑清单：原文件里真实存在的 7 个小问题

1. 固定范围写死到 995 行
   ：三条 FILTER 都用 数据!A2:W995。数据超过 995 行不会自动纳入，建议改成整列引用或转成「表格」用结构化引用。
2. 姓名下拉只到 A40
   ：反向填充 B2 来源是 数据!$A$2:$A$40，人一多就选不到，建议改成动态名称或 OFFSET+COUNTA。
3. A8 里有一处疑似笔误
   ：年龄时间段的第三分支写成 AND(ISBLANK(G5),(I6>=0))——按对称逻辑应是 I5。只填「终止年龄」时会走到这一支，把终止入职日期误当条件。M7 简化版没有这个问题，这也是推荐简化版的原因。
4. 空值显示成 0 / 00:00:00
   ：结果区因为整块数组公式 + 自定义数字格式，空单元格会显示 0。可给 FILTER 结果套 IF(…="","",…)，或用自定义格式把 0 隐藏。
5. FIND 区分大小写、怕空格
   ：FIND 对中文没问题，但英文与多余空格会漏匹配；要更宽容可换 SEARCH 并配 TRIM。
6. 底表存在文本日期
   ：数据!D3 是「1973/8/19/周日」文本，会污染时间段比较。VBA 里的 ParseToDate 与保存后的 NumberFormat 就是为了收拾它，导入历史数据时务必先统一成真日期。
7. 隐私与宏安全
   ：底表含身份证、银行卡、电话等敏感信息，示例数据发文章前要脱敏；.xlsm 分发时要提醒对方启用宏，同时确认宏来源可信。

九、写在最后

这份模板最值得学的不是某个函数，而是分层思路：一张表只做一件事——录入负责交互与校验、数据只负责存、反向填充负责单条精确查、查询负责批量筛选、行政区划负责字典。层与层之间只用「单元格地址」和「定义名称」两种最朴素的接口连接，不依赖外部数据库、不依赖加载项。

它的适用边界也很清楚：几十到几百人的小团队、单机使用、无需并发。真正到了上千人、多人同时录入、需要权限与审计的时候，就该把「数据」这一层换成数据库或共享表格，前面的录入与查询思路仍然可以照搬。

如果你只想带走三句话，那就是：用 MATCH 代替写死列号；把控件状态翻译成布尔数组再交给 FILTER；空条件用 IFERROR(FIND("",文本),0) 天然放行。

附录 A｜文件速查卡