综合实战：员工信息管理表 —

📥 本章配套练习册（数据已录入、写完公式自动判分）：点击下载综合实战-员工信息管理表-练习.xlsx

难度：⭐⭐⭐⭐ | 建议用时：90 分钟 | 前置知识：全部 16 章

本章目标

用一个贴近真实工作的"员工信息管理表"场景，把 16 个函数全部用一遍。能独立完成本章，说明你已经真正掌握了这些函数。

⚠️ 涉及 TODAY() 的题目，答案按"今天 = 2026/7/28"计算。

一、场景背景

你是公司的人事专员，手里有一张员工信息总表。老板会随时问你：“技术部平均工资多少？““E004 是谁？““谁的绩效最高？““张三工龄几年了？"——用公式把这些问题全部做成自动计算，数据一变结果自动更新。

二、练习数据准备

新建工作表，录入下表（录入前先把 E 列设为"文本"格式，防止身份证号变形）：

	A	B	C	D	E	F	G
1	工号	姓名	部门	入职日期	身份证号	基本工资	绩效分
2	E001	张三	销售部	2020/3/15	110105199003078834	8000	85
3	E002	李四	技术部	2018/7/1	110105198812156543	12000	92
4	E003	王五	销售部	2023/11/20	110105199505232211	7500	58
5	E004	赵六	技术部	2019/5/8	110105199201304422	11500	76
6	E005	孙七	人事部	2022/9/12	110105199311127755	7000	88
7	E006	周八	销售部	2021/6/25	110105199404186688	9000	91

再准备一个查询区（I 列）：

	I
1	要查的工号
2	E004
3	E009

三、任务清单（请先独立完成）

第一关：文本与日期（RIGHT / MID / DATEDIF / TODAY）

题号	任务	作答位置	用到函数
1	从身份证号提取每人出生年份（4位），向下填充	H2	MID
2	从身份证号提取每人出生月份（2位）	I2（换个位置也行）	MID
3	从工号提取数字编号部分（如 E001 → 001）	J2	RIGHT
4	计算每人到今天为止的工龄（整年），向下填充	K2	DATEDIF+TODAY
5	计算张三入职到今天共多少个月	L2	DATEDIF

第二关：逻辑判断（IF）

题号	任务	作答位置	用到函数
6	绩效评级：≥90"A”、≥75"B”、≥60"C”、其余"D”，向下填充	M2	嵌套IF
7	工龄 ≥5 年的显示"资深员工”，否则显示"普通员工”（引用 K 列结果）	N2	IF

第三关：查找引用（VLOOKUP / XLOOKUP / INDEX+MATCH / IFERROR）

题号	任务	作答位置	用到函数
8	根据 I2 的工号查出姓名	J2区域→改用 P2 等空白列均可	VLOOKUP
9	根据 I2 的工号查出基本工资	同上	VLOOKUP
10	查 I3（E009，不存在），用 IFERROR 显示"查无此人”	同上	IFERROR+VLOOKUP
11	用 XLOOKUP 根据工号查姓名（365/2021 用户）	同上	XLOOKUP
12	用 XLOOKUP 向左查找：根据姓名"王五"查工号	同上	XLOOKUP
13	用 INDEX+MATCH 完成第8题同样的查找（老版本方案）	同上	INDEX+MATCH
14	双向查找：查"赵六"的"基本工资"（行、列都用 MATCH）	同上	INDEX+MATCH×2

第四关：统计分析（COUNTIF / SUMIF / COUNTIFS / SUMIFS / MAX / ROUND）

题号	任务	作答位置	用到函数
15	销售部有多少人	空白区	COUNTIF
16	技术部工资总额	空白区	SUMIF
17	绩效大于等于 80 的有几人	空白区	COUNTIF
18	销售部且绩效 ≥80 的人数	空白区	COUNTIFS
19	技术部且绩效 ≥75 的工资总额	空白区	SUMIFS
20	全公司最高绩效分	空白区	MAX
21	技术部的平均工资（SUMIF÷COUNTIF），保留 2 位小数	空白区	+ROUND
22	绩效 80 分及以上员工的工资总额（提示：单条件也可以用 SUMIFS 或 SUMIF）	空白区	SUMIF

四、任务讲解（做完再看）

第1题：`=MID(E2,7,4)`

身份证第 7 位开始的 4 位是出生年。向下填充后：1990 / 1988 / 1995 / 1992 / 1993 / 1994。

第2题：`=MID(E2,11,2)`

月份在第 11~12 位。张三 → 03。

第3题：`=RIGHT(A2,3)`

工号统一为"字母+3位数字"，右边 3 位就是编号 → 001（文本）。

第4题：`=DATEDIF(D2,TODAY(),"Y")`

早日期（入职）在前，TODAY() 在后，“Y” 取整年。结果：张三 6、李四 8、王五 2、赵六 7、孙七 3、周八 5。

第5题：`=DATEDIF(D2,TODAY(),"M")`

“M” 是总整月数：6 年 ×12 + 4 个零头月 = 76 个月。

第6题：`=IF(G2>=90,"A",IF(G2>=75,"B",IF(G2>=60,"C","D")))`

从高到低三层筛。结果：张三 B、李四 A、王五 D、赵六 B、孙七 B、周八 A。检查王五 58：三层全落空 → D ✓。

第7题：`=IF(K2>=5,"资深员工","普通员工")`

直接引用第4题算好的 K 列。资深：张三（6）、李四（8）、赵六（7）、周八（5）✓；王五（2）、孙七（3）是普通员工。 注意 5 年整也算资深（>= 包含等于），周八正好 5 年。

第8题：`=VLOOKUP(I2,$A$2:$G$7,2,FALSE)`

工号在首列 ✓，姓名是区域第 2 列。I2 = E004 → 赵六。区域一定要 $ 锁定。

第9题：`=VLOOKUP(I2,$A$2:$G$7,6,FALSE)`

基本工资在区域第 6 列（A=1…F=6）→ 11500。

第10题：`=IFERROR(VLOOKUP(I3,$A$2:$G$7,2,FALSE),"查无此人")`

E009 不存在 → VLOOKUP 报 #N/A → IFERROR 兜底 → 查无此人。

第11题：`=XLOOKUP(I2,$A$2:$A$7,$B$2:$B$7,"查无此人")`

与第8题同结果（赵六），还顺手内置了兜底。

第12题：`=XLOOKUP("王五",$B$2:$B$7,$A$2:$A$7)`

查找列 = 姓名 B，返回列 = 工号 A（向左！）→ E003。

第13题：`=INDEX(B2:B7,MATCH(I2,A2:A7,0))`

经典组合，结果与 VLOOKUP 一致 → 赵六。老版本 Excel 用这个。

第14题：`=INDEX(B2:G7,MATCH("赵六",B2:B7,0),MATCH("基本工资",B1:G1,0))`

行 MATCH 在姓名列找赵六 → 4；列 MATCH 在表头 B1:G1 找"基本工资" → 5；B2:G7 第4行第5列 = F5 → 11500。注意这次查找列是 B 列（姓名），所以 INDEX 区域从 B 列开始选，保证行列对齐。

第15题：`=COUNTIF(C2:C7,"销售部")` → 3

第16题：`=SUMIF(C2:C7,"技术部",F2:F7)` → 12000+11500 = 23500

第17题：`=COUNTIF(G2:G7,">=80")` → 85、92、88、91 → 4

第18题：`=COUNTIFS(C2:C7,"销售部",G2:G7,">=80")` → 张三 85、周八 91 → 2

第19题：`=SUMIFS(F2:F7,C2:C7,"技术部",G2:G7,">=75")` → 李四 92✓（12000）+ 赵六 76✓（11500）= 23500

第20题：`=MAX(G2:G7)` → 92（李四）

第21题：`=ROUND(SUMIF(C2:C7,"技术部",F2:F7)/COUNTIF(C2:C7,"技术部"),2)`

23500 ÷ 2 = 11750.00。条件和 ÷ 条件数 = 条件平均，ROUND 修到 2 位小数。

第22题：`=SUMIF(G2:G7,">=80",F2:F7)`

绩效列当条件区域，工资列当求和区域：张三 8000 + 李四 12000 + 孙七 7000 + 周八 9000 = 36000。（条件与求和不同列，必须三参数写法。）

五、参考答案速查表

题号	公式	结果
1	`=MID(E2,7,4)`	1990（张三）
2	`=MID(E2,11,2)`	03
3	`=RIGHT(A2,3)`	001
4	`=DATEDIF(D2,TODAY(),"Y")`	6 / 8 / 2 / 7 / 3 / 5
5	`=DATEDIF(D2,TODAY(),"M")`	76
6	`=IF(G2>=90,"A",IF(G2>=75,"B",IF(G2>=60,"C","D")))`	B / A / D / B / B / A
7	`=IF(K2>=5,"资深员工","普通员工")`	张三、李四、赵六、周八=资深
8	`=VLOOKUP(I2,$A$2:$G$7,2,FALSE)`	赵六
9	`=VLOOKUP(I2,$A$2:$G$7,6,FALSE)`	11500
10	`=IFERROR(VLOOKUP(I3,$A$2:$G$7,2,FALSE),"查无此人")`	查无此人
11	`=XLOOKUP(I2,$A$2:$A$7,$B$2:$B$7,"查无此人")`	赵六
12	`=XLOOKUP("王五",$B$2:$B$7,$A$2:$A$7)`	E003
13	`=INDEX(B2:B7,MATCH(I2,A2:A7,0))`	赵六
14	`=INDEX(B2:G7,MATCH("赵六",B2:B7,0),MATCH("基本工资",B1:G1,0))`	11500
15	`=COUNTIF(C2:C7,"销售部")`	3
16	`=SUMIF(C2:C7,"技术部",F2:F7)`	23500
17	`=COUNTIF(G2:G7,">=80")`	4
18	`=COUNTIFS(C2:C7,"销售部",G2:G7,">=80")`	2
19	`=SUMIFS(F2:F7,C2:C7,"技术部",G2:G7,">=75")`	23500
20	`=MAX(G2:G7)`	92
21	`=ROUND(SUMIF(C2:C7,"技术部",F2:F7)/COUNTIF(C2:C7,"技术部"),2)`	11750
22	`=SUMIF(G2:G7,">=80",F2:F7)`	36000

六、毕业自测清单

下面 16 句话，每句都能不假思索地写出公式，你就真正毕业了：

显示今天日期 → =TODAY()
求区域最大值 → =MAX(区域)
保留两位小数 → =ROUND(数字,2)
取右边 4 个字符 → =RIGHT(文本,4)
从第 7 位取 4 个字符 → =MID(文本,7,4)
大于等于 60 显示及格 → =IF(A1>=60,"及格","不及格")
出错显示空白 → =IFERROR(公式,"")
数某部门人数 → =COUNTIF(部门列,"销售部")
汇总某部门工资 → =SUMIF(部门列,"销售部",工资列)
数部门+性别两条件 → =COUNTIFS(部门列,"销售部",性别列,"男")
多条件汇总工资 → =SUMIFS(工资列,部门列,"销售部",性别列,"男")
算工龄 → =DATEDIF(入职日,TODAY(),"Y")
按键查值 → =VLOOKUP(键,区域,列号,FALSE)
新版查找 → =XLOOKUP(键,查找列,返回列,"兜底")
找位置 → =MATCH(键,单列,0)
按位置取值 → =INDEX(列,MATCH(键,键列,0))

七、下一步学习建议

你已经掌握了 Excel 函数的核心骨架，接下来可以按这个顺序进阶：

AVERAGE / SUM / COUNT / LEN / LEFT —— 看一眼就会的基础函数
TEXT —— 日期和数字的格式化输出（如把日期显示成"2026年7月"）
EOMONTH / EDATE / WORKDAY —— 日期计算进阶（月末、N个月后、工作日）
数据透视表 —— 不用函数的汇总神器，和 SUMIFS 互为补充
FILTER / UNIQUE / SORT（365 动态数组）—— 新一代数据处理
LET / LAMBDA —— 把复杂公式写得像程序一样清晰

恭喜完成全部课程！🎉