第 19 章

实战二——HR 人事数据管理系统

第19章：HR人事系统——考勤、工资、绩效全自动化

HR工作有三个核心时间黑洞：每月初的考勤统计、每月中旬的工资计算、每个季度的绩效评估。这三块工作体量大、精度要求高、容错率低。本章提供从考勤到月度汇报的完整Excel+AI解决方案，覆盖20个真实场景案例。

HR的Excel痛点

先正视问题所在：

**考勤：**打卡系统导出的数据格式混乱，需要大量手工处理才能变成可分析的考勤报表。节假日、调休、半天假的计算逻辑复杂，容易漏计或错计。
**工资：**每个员工的工资组成不同（基本工资、绩效、各类补贴），加班费计算规则复杂，个税计算公式年年有变化，任何一个环节出错都会引发员工投诉和信任危机。
**绩效：**KPI评分涉及多维度、不同权重，手工计算容易出错；评分结果敏感，员工会仔细核对，必须准确。
**数据管理：**员工信息分散在多个文件里，入职、离职、调岗的记录更新不及时，导致工资计算或报表的基础数据出错。

ℹ️ Note

**本章策略：**把所有重复性的数字处理工作交给Excel公式和VBA自动化；把需要判断和表达的工作（分析异常原因、写绩效反馈）交给AI；HR的时间集中在真正需要人判断的事情上。

考勤统计自动化（5个案例）

1考勤表结构设计——用AI优化

好的考勤表结构是后续所有自动化的基础。如果结构设计错了，所有公式都要重写。用AI在搭建之前就把结构设计好。

我是一家200人公司的HR，需要用Excel设计月度考勤统计表。背景：
- 打卡系统每天导出原始数据：员工姓名、日期、上班打卡时间、下班打卡时间
- 公司规定：早9:00上班，18:00下班，9:10后算迟到，17:50前算早退，18:30后算加班
- 员工有全天假、半天假、调休等不同假期类型
- 月末需要汇总每人：出勤天数、迟到次数、早退次数、加班小时数

请帮我设计：
1. 原始打卡数据表的理想字段（为后续公式计算优化）
2. 月度考勤汇总表的字段
3. 假期记录表的字段
4. 三个表之间的关联关系

2自动计算出勤/迟到/早退/加班

有了规范的打卡数据，这四项指标全部可以用公式自动计算，不需要手动核对。

核心公式组
    假设：C列=上班打卡时间（如 08:55:30），D列=下班打卡时间，E列=日期
    是否迟到（9:10后打卡）：
    =IF(TIMEVALUE(TEXT(C2,"HH:MM:SS"))>TIME(9,10,0),"迟到","正常")
    迟到分钟数：
    =IF(C2>TIME(9,10,0),(C2-TIME(9,10,0))*1440,0)
    加班小时数（18:30后下班才算加班）：
    =IF(D2>TIME(18,30,0),(D2-TIME(18,30,0))*24,0)
    月度迟到次数汇总：
    =COUNTIFS(打卡表!B:B,A2,打卡表!F:F,"迟到",打卡表!E:E,">="&DATE(YEAR(G1),MONTH(G1),1))

3工作日应出勤天数（NETWORKDAYS排除节假日）

计算应出勤天数，不能只用月总天数减去周末，还要排除法定节假日。NETWORKDAYS函数是专门处理这个问题的。

NETWORKDAYS应用
    基础用法：=NETWORKDAYS(月开始日期, 月结束日期, 节假日列表)
    例：4月应出勤天数（排除劳动节等节假日）
    假设节假日在Sheet"节假日"的A列：
    =NETWORKDAYS(DATE(2026,4,1), DATE(2026,4,30), 节假日!A:A)
    含调休补班（这些天本来是周末但要上班）：
    需要在节假日表里区分"放假日"和"调休上班日"，
    NETWORKDAYS会自动排除周末，但调休上班日需要单独处理。

4考勤异常自动标注（条件格式）

月末考勤汇总表里，用条件格式让异常数据"跳出来"，HR不需要逐行检查，只需要关注有颜色的记录。

5AI帮你建立考勤统计模板

如果你是从零开始建考勤系统，让AI给你一个完整的模板方案，比自己摸索快得多。

工资条自动生成（5个案例）

工资计算为什么必须用公式，不能手算

工资条是法律文件，一旦发出就很难更正。手算一个100人团队的工资，即便是熟练的HR也需要1-2天，而且错误率不低。用Excel公式把整个计算链搭好，每月只需要更新源数据，5分钟出结果，错误率接近零。

1工资计算公式（基本工资+绩效+补贴-扣款）

工资计算链
    应发工资 = 基本工资 + 绩效奖金 + 餐补 + 交通补贴 + 加班费 - 事假扣款 - 迟到扣款
    加班费（法定）：
    - 平日加班：小时工资 × 1.5 × 加班小时数
    - 休息日加班：小时工资 × 2 × 加班小时数
    - 法定节假日加班：小时工资 × 3 × 加班小时数
    日工资（用于计算事假扣款）：
    = 月基本工资 / 该月应出勤天数

2个税计算（2024年最新税率表+VLOOKUP）

中国个人所得税采用超额累进税率，月应纳税所得额 = 应发工资 - 五险一金个人部分 - 5000元起征点 - 专项附加扣除。税率分7档，用VLOOKUP配合速算扣除数计算最方便。

月应纳税所得额（元）	税率	速算扣除数（元）
不超过3,000	3%	0
3,001 - 12,000	10%	210
12,001 - 25,000	20%	1,410
25,001 - 35,000	25%	2,660
35,001 - 55,000	30%	4,410
55,001 - 80,000	35%	7,160
超过80,000	45%	15,160

个税公式（假设应纳税所得额在I列）
    税率表在Sheet"税率表"，A列是档位上限，B列税率，C列速算扣除数：
    应纳税所得额 = MAX(应发工资 - 五险一金个人 - 5000 - 专项附加, 0)
    税额 = MAX(应纳税所得额,0) * VLOOKUP(MAX(应纳税所得额,0),税率表!A:B,2,1) - VLOOKUP(MAX(应纳税所得额,0),税率表!A:C,3,1)

3五险一金计算

五险一金的计算基数是缴费基数（不低于当地最低工资标准，不高于当地社平工资的3倍），各城市费率略有不同。以上海2024年为例：

五险一金计算（以上海为参考）
    缴费基数 = MAX(MIN(工资, 上限), 下限)（上下限每年调整，存放在设置表）
    养老保险个人：缴费基数 × 8%
    医疗保险个人：缴费基数 × 2% + 3元（门急诊）
    失业保险个人：缴费基数 × 0.5%
    住房公积金个人：缴费基数 × 7%（各公司比例不同）
    五险一金个人合计 = 以上各项相加
    建议：把费率和上下限放在单独的"设置表"里，
    每年只改设置表，公式不需要动。

4工资条拆分——每人一张（VBA方案）

工资表是汇总格式，但发给员工的工资条是每人一张。用VBA把工资表自动拆分成工资条，是最省时的方案。

5AI审查工资计算逻辑

工资发出前，让AI帮你做最后一道检查——不是让AI算数字，而是让它审查你的计算逻辑是否合理，发现可能被忽略的问题。

我的工资计算公式体系如下（用文字描述，不是具体数字）：
- 应发工资 = 基本工资 + 绩效（岗位工资×绩效系数）+ 餐补500 + 加班费（平日1.5×时薪，休日2×时薪）- 事假扣款（日工资×事假天数）- 迟到扣款（50元/次，3次以内免扣）
- 日工资 = 基本工资 / 应出勤天数
- 时薪 = 基本工资 / （应出勤天数×8）
- 五险一金：按缴费基数（MAX(MIN(工资,24675),6960)）计算
- 个税：应发-五险一金个人-5000-专项附加，结果为正则按税率表计算
- 实发工资 = 应发 - 五险一金个人 - 个税

请帮我：
1. 指出这个计算链中可能存在的逻辑问题或法律风险（如加班费基数是否合规）
2. 哪些边界情况我可能没有考虑到（如月中入职/离职的计算、全月缺勤的情况）
3. 有什么常见的Excel工资计算陷阱我应该特别注意

绩效管理表（4个案例）

1KPI评分表结构设计

2加权评分自动计算（SUMPRODUCT）

SUMPRODUCT是绩效评分的首选函数，可以一次性计算多个维度的加权分数。

SUMPRODUCT绩效计算
    假设：B2:E2是4个维度的评分，权重在B1:E1（总和为1）：
    加权总分 = =SUMPRODUCT(B1:E1, B2:E2)
    如果权重在单独的设置行，好处是改权重不需要改公式：
    =SUMPRODUCT($B$1:$E$1, B2:E2)
    加权后的绩效等级（S/A/B/C/D）：
    =IFS(F2>=90,"S",F2>=80,"A",F2>=70,"B",F2>=60,"C",TRUE,"D")

3绩效等级自动分配（IFS+RANK）

绩效强制分布要求：S级不超过10%，A级不超过20%，B级不超过40%，C级不超过20%，D级不超过10%。需要先按分数排名，再根据名次确定等级。

4AI生成绩效反馈文字

绩效反馈文字要既有针对性又让员工愿意接受，是很多管理者头疼的工作。AI可以根据具体数据生成有结构的反馈草稿。

请帮我为以下员工撰写季度绩效反馈（HR用于绩效面谈的参考文字）：

员工情况：
- 姓名：李明，销售部门，入职2年
- 绩效等级：B（总分76分）
- 各维度：销售额完成率72%（目标100%，较上季度下降8%）；新客户开发3家（目标5家）；客户满意度88分（表现良好）；团队协作92分（被同事和跨部门高度评价）

反馈要求：
1. 200字以内
2. 先肯定优势（团队协作和客户满意度）
3. 清晰指出改进方向（销售额和新客户开发不足的具体原因探讨）
4. 结尾给出下季度的具体改进建议
5. 语气：支持性而非批评性，激励为主

员工信息管理（3个案例）

1员工档案表设计（数据验证+下拉）

2生日/合同到期提醒（DATEDIF+条件格式）

关键公式
    距生日还有多少天（本年度）：
    =DATEDIF(TODAY(), DATE(YEAR(TODAY()),MONTH(D2),DAY(D2)), "D")
    注意：如果生日已过则显示到明年
    合同到期天数：
    =DATEDIF(TODAY(), E2, "D")（E列是合同到期日期）
    条件格式：合同到期天数3离职率/人员变动分析



## 招聘跟进表（3个案例）


  1候选人漏斗管理

招聘漏斗和销售漏斗逻辑一样：简历投递→筛选通过→面试安排→面试通过→Offer发出→接受入职。每个阶段的数量和转化率，决定了你需要多少份简历才能招到一个人。




---

---

  2面试安排表



---

---

  3录用通知自动生成（AI辅助）



## HR月度汇报自动化（综合案例）


每月月底，HR需要向管理层汇报人力资源状况。把数据整理好后，用AI生成汇报文字草稿，是最省时的方案。


### 月度HR报告的标准结构


1. **人员概况**：月末在职人数、本月新增/离职人数、各部门人员分布
2. **招聘进展**：当前在招岗位数、本月新增简历数、本月录用人数、关键岗位进展
3. **考勤摘要**：整体出勤率、迟到/早退汇总、加班情况（有无超时风险）
4. **薪资成本**：本月薪资总额、与预算对比、社保公积金总额
5. **绩效进展**（如有）：本季度绩效评估完成情况、各等级分布
6. **下月重点**：关键招聘岗位、合同到期处理、节假日考勤安排等


**✅ Tip**

> **本章总结：**HR工作的自动化核心是建立"三张核心表联动"——员工信息表（基础数据源）、考勤汇总表（月度计算）、工资计算表（月度输出）。三表通过员工ID关联，任何数据更新自动传递。AI在考勤异常分析、绩效反馈撰写、月度汇报生成三个环节发挥最大价值。


  [
    上一章
    ← 第18章：销售数据看板
  ](/books/ai-excel/ch18-sales)
  [
    下一章
    第20章：财务运营分析 →
  ](/books/ai-excel/ch20-finance)

---

本章评分

4.6 / 5 (10 评分)