Excel人力资源：人员结构分析看板

Excel人力资源：人员结构分析看板数据驱动决策，从一张花名册到全自动分析仪表盘致每一位想让HR数据开口说话的管理者，每到季度末，你是不是又要交《人员结构分析报告》了？先从系统导出员工花名册，手动数男女各多少人、各部门多少人、各学历分布多少人。然后打开PPT，把统计结果做成饼图、柱状图、折线图。最后发现花名册里有几个人的入职日期格式不对，前面的统计全得重来一遍。这不是分析，是数数。真正的分析应该是：花名册更新了，所有图表和指标自动刷新，你只需要盯着那些异常的数字——比如这个月流失率突然翻倍了、某个部门司龄结构严重断层——去思考背后的原因和对策。这份指南就是帮你搭建这样一套系统。所有公式和图表设置都是可复现的，所有示例数据都是模拟的。⚠️隐私声明本指南所有示例数据均为模拟生成，不涉及任何真实个人信息。在实际使用中，请确保员工数据的使用符合《个人信息保护法》及你所在公司的数据安全规定。建议对包含敏感信息的Excel文件设置打开密码，并限制访问权限。目录模型总览：从花名册到仪表盘员工花名册：所有分析的唯一数据源结构分析：性别、学历、职级分布司龄与年龄结构分析招聘渠道效果分析人员流动月度趋势离职原因分析生日与转正自动提醒人力成本与预算对比一键导出汇报数据第1章模型总览：从花名册到仪表盘整套模型的工作表结构和数据流向：员工花名册（你维护）

↓

┌───┼───┐

↓↓↓

结构流动成本

分析分析分析

↓↓↓

└───┼───┘

↓

汇总仪表盘（图表+指标卡片）

↓

导出数据源（给PPT）核心原则：你只维护“员工花名册”一张表。新增员工加一行，离职员工标记“离职”和离职日期。其余所有分析工作表的数据全部通过公式从花名册自动抓取。花名册更新了，全部分析结果自动刷新。需要新建的工作表：员工花名册结构分析（性别/学历/职级/年龄/司龄）流动分析（入职/离职/月度趋势）招聘分析（渠道效果）提醒清单（生日/转正）成本分析（人力成本与预算）仪表盘（汇总图表）导出数据（给PPT的源数据）第2章员工花名册：所有分析的唯一数据源2.1字段设计新建“员工花名册”工作表，将数据区域转为表格（Ctrl+T），命名EmployeeMaster。列标题输入方式说明A员工编号手动唯一标识，文本格式B姓名手动C性别下拉男/女D出生日期手动格式1990-05-15E学历下拉高中及以下/大专/本科/硕士/博士F职级下拉初级/中级/高级/资深/专家G部门下拉销售部/研发部/市场部/财务部/人事行政部H岗位手动I入职日期手动J转正日期手动入职日期+试用期月数K员工状态下拉试用期/在职/离职L离职日期手动仅离职员工填写M离职原因下拉薪资偏低/发展受限/家庭原因/工作环境/被动离职/其他N招聘渠道下拉Boss直聘/智联招聘/内推/猎头/校招/其他2.2辅助列（公式自动计算）列标题公式O年龄=DATEDIF([@出生日期],TODAY(),"Y")P司龄（年）=IF([@员工状态]="离职",DATEDIF([@入职日期],[@离职日期],"Y"),DATEDIF([@入职日期],TODAY(),"Y"))Q司龄段=IFS([@司龄（年）]<1,"<1年",[@司龄（年）]<3,"1-3年",[@司龄（年）]<5,"3-5年",[@司龄（年）]<10,"5-10年",TRUE,"≥10年")R年龄段=IFS([@年龄]<25,"25岁以下",[@年龄]<30,"25-30岁",[@年龄]<35,"30-35岁",[@年龄]<40,"35-40岁",TRUE,"40岁以上")2.3数据录入规范日期统一格式：整列保持YYYY-MM-DD格式。不要在日期列中混入文本。下拉菜单：用数据验证→序列设置，序列来源可以写在参数表中，方便统一修改。离职员工：不删行。将员工状态改为“离职”，填上离职日期和离职原因。保留离职员工数据是分析流失率的基础。空白单元格：公式列不能有空值，否则统计可能出错。尚未发生的事件（如未离职员工的离职日期）留空即可，不要填“无”或“-”。第3章结构分析：性别、学历、职级分布新建“结构分析”工作表。这张表展示公司人力结构的静态画像。3.1核心统计指标（顶部卡片）指标公式在职总人数=COUNTIFS(EmployeeMaster[员工状态],"在职")试用期人数=COUNTIFS(EmployeeMaster[员工状态],"试用期")平均年龄=ROUND(AVERAGEIF(EmployeeMaster[员工状态],"在职",EmployeeMaster[年龄]),1)平均司龄=ROUND(AVERAGEIF(EmployeeMaster[员工状态],"在职",EmployeeMaster[司龄（年）]),1)女性占比=COUNTIFS(EmployeeMaster[性别],"女",EmployeeMaster[员工状态],"在职")/在职总人数本科及以上占比=COUNTIFS(EmployeeMaster[学历],"本科",EmployeeMaster[员工状态],"在职")+COUNTIFS(EmployeeMaster[学历],"硕士",EmployeeMaster[员工状态],"在职")+COUNTIFS(EmployeeMaster[学历],"博士",EmployeeMaster[员工状态],"在职")再除以在职总人数3.2分布统计表性别分布：性别人数占比男=COUNTIFS(EmployeeMaster[性别],"男",EmployeeMaster[员工状态],"在职")=B2/在职总人数女=COUNTIFS(EmployeeMaster[性别],"女",EmployeeMaster[员工状态],"在职")=B3/在职总人数学历分布：行标签改为“高中及以下/大专/本科/硕士/博士”，公式逻辑同性别分布。职级分布：行标签改为各职级，公式逻辑同上。部门分布：行标签改为各部门，公式逻辑同上。3.3图表性别、学历分布：饼图或环形图。选中分布数据→插入饼图→添加数据标签（显示百分比和类别名称）。部门人数分布：条形图。横轴人数，纵轴部门。按人数降序排列，让条形图从上到下越来越短，视觉上主次分明。职级分布：柱状图。横轴职级（从初级到专家），纵轴人数。形状能直观展示职级金字塔是否合理——如果中高级占比远大于初级，说明梯队有断层。第4章司龄与年龄结构分析在同一张“结构分析”工作表的下方或新建一个区域。4.1司龄段分布表司龄段人数占比<1年=COUNTIFS(EmployeeMaster[司龄段],"<1年",EmployeeMaster[员工状态],"在职")1-3年=COUNTIFS(EmployeeMaster[司龄段],"1-3年",...)3-5年同上5-10年同上≥10年同上4.2司龄结构判断在表格旁边添加文字分析：=IF(占比<1年>40%,"⚠️新员工占比过高，关注培训和文化融合",

IF(占比3-5年<15%,"⚠️中坚力量偏少，关注核心人才保留",

IF(占比≥10年>30%,"团队经验丰富，稳定性好","结构正常")))解释：新员工（<1年）占比超过40%，说明公司扩张快或流失率高，需要关注入职培训和团队融合。3-5年司龄是业务骨干的黄金期，占比低于15%说明中坚力量薄弱，存在断层风险。4.3图表司龄分布用瀑布图或柱状图。柱状图简单直观。如果需要在一次汇报中同时看到司龄和部门两个维度，可以用堆叠柱状图：横轴为部门，每个部门的柱子上按司龄段分色堆叠，一眼看出哪个部门新人多、哪个部门老员工多。第5章招聘渠道效果分析新建“招聘分析”工作表。5.1渠道统计招聘渠道在职人数离职人数留存率Boss直聘=COUNTIFS(EmployeeMaster[招聘渠道],"Boss直聘",EmployeeMaster[员工状态],"在职")=COUNTIFS(EmployeeMaster[招聘渠道],"Boss直聘",EmployeeMaster[员工状态],"离职")=在职人数/(在职人数+离职人数)各渠道逐行统计。5.2渠道效果解读只看入职人数不够。某渠道入职10人但半年内离职7人，和另一个渠道入职5人全部在职，前者性价比远不如后者。留存率是判断渠道质量的关键指标。5.3图表用双轴图：柱状图显示入职人数，折线图显示留存率。两个指标放在同一张图里，渠道的效果（入职多）和效率（留得住）同时呈现。第6章人员流动月度趋势新建“流动分析”工作表。6.1月度入职与离职统计在花名册中，入职日期和离职日期是精确到天的。我们需要按月汇总。月份入职人数离职人数净增期末人数流失率2026-01公式公式=C2-B2期初+本月净增=本月离职/期初人数2026-02...............入职人数公式：=SUMPRODUCT((TEXT(EmployeeMaster[入职日期],"YYYY-MM")=$A2)*1)离职人数公式：=SUMPRODUCT((TEXT(EmployeeMaster[离职日期],"YYYY-MM")=$A2)*1)SUMPRODUCT在这里比COUNTIFS更灵活，因为可以直接对TEXT函数转换后的结果做条件判断。流失率公式：=离职人数/期初人数期初人数=上月期末人数。6.2月度趋势图一张组合图同时展示入职人数（柱状图，绿色）、离职人数（柱状图，红色）、流失率（折线图，深蓝色，使用次坐标轴）。绿色柱子和红色柱子并排排列，净增是正是负一目了然。流失率折线用次坐标轴，刻度与人数分开，趋势不会被人数波动掩盖。6.3年初至今累计在表格右侧增加累计列：累计入职=各月入职逐月累加累计离职=各月离职逐月累加年度累计流失率=累计离职/年初人数第7章离职原因分析7.1离职原因分布统计离职原因人数占比薪资偏低=COUNTIFS(EmployeeMaster[离职原因],"薪资偏低")发展受限同上家庭原因同上工作环境同上被动离职同上其他同上7.2交叉分析：离职原因与司龄单纯看离职原因只能知道“因为薪资走的人最多”。如果加上司龄维度：看司龄<1年的离职原因集中在什么、1-3年的集中在什么、3年以上的集中在什么。不同司龄段离职原因通常是不同的。新人（<1年）离职多为“工作环境、与预期不符”；骨干（3-5年）离职多为“发展受限、薪资偏低”。针对不同群体制定不同的保留策略，比泛泛地“全员加薪”更有效。操作方法：在离职原因统计表旁增加一列筛选条件，用COUNTIFS加司龄段条件即可：=COUNTIFS(EmployeeMaster[离职原因],"薪资偏低",EmployeeMaster[司龄段],"3-5年")7.3图表离职原因分布用条形图（横条），按人数降序排列。交叉分析用堆叠条形图：横轴为司龄段，每个司龄段的条形用不同颜色堆叠展示各离职原因的人数。第8章生日与转正自动提醒新建“提醒清单”工作表。8.1本月生日提醒员工编号姓名部门出生日期本周生日公式公式公式公式条件判断筛选本月生日的公式逻辑：用FILTER函数（Excel365）或先建辅助列再筛选。辅助列：=IF(MONTH([@出生日期])=MONTH(TODAY()),"本月生日","")然后筛选该列为“本月生日”的行，或直接用FILTER一次性提取：=FILTER(EmployeeMaster,(MONTH(EmployeeMaster[出生日期])=MONTH(TODAY()))*(EmployeeMaster[员工状态]="在职"),"本月无生日员工")本周生日的条件格式：在生日提醒表中，增加一列判断：=IF(AND(出生日期在本周范围内,员工状态="在职"),"🎂本周","")对本列应用条件格式，包含“本周”的整行填充浅黄色。8.2转正提醒逻辑同上，将出生日期替换为转正日期。增加一列：=IF([@转正日期]<=TODAY()+7,"7天内转正",IF([@转正日期]<=TODAY()+30,"本月转正",""))条件格式：7天内转正标红色，本月转正标黄色。8.3放在仪表盘首页生日和转正提醒不需要独立成页。在仪表盘顶部用一两个小卡片展示“本月生日X人”“待转正X人”即可。详细信息放在提醒清单表中，需要时再查看。第9章人力成本与预算对比新建“成本分析”工作表。9.1数据表结构月份预算实际差异差异率累计预算累计实际累计差异2026-01=C2-B2=D2/B2=SUM(B$2:B2)=SUM(C$2:C2)预算和实际数据需要手动填入，或从薪酬系统导出后粘贴。9.2图表月度预算vs实际对比图：两条折线——预算用虚线（灰色），实际用实线（主题色）。两条线靠在一起时成本控制良好，实际线大幅偏离预算线时一目了然。累计差异面积图：横轴月份，纵轴累计差异（可正可负）。用面积图展示，正差异（超支）填充红色，负差异（节约）填充绿色。这一页是给财务和老板看的核心成本总览。如果缺少实际的人力成本数据：先用花名册中的在岗人数乘以各职级平均薪酬估算。虽然不是精确数值，但能提供一个趋势判断。正式报告中使用薪酬系统数据替换即可。第10章一键导出汇报数据新建“导出数据”工作表。这张表是给PPT或其他汇报材料提供结构化数据源。10.1设计思路导出表不是给人看的，是给PPT数据链接用的（参考《PPT图表数据动态链接与自动更新》中的链接机