【Excel神技】用SUMPRODUCT函数3秒搞定身份证年龄段统计，职场人必学！
在Excel中如何根据身份证号码快速统计各年龄段人数？使用SUMPRODUCT函数配合身份证号计算年龄是最专业的解决方案！本文将详细讲解从身份证提取年龄到分段统计的全流程操作，包含18岁以下、18-35岁、36-55岁及56岁以上四个典型年龄段的统计公式，并附赠可直接套用的模板公式。无论你是HR做人员分析，还是市场部做用户画像，这套方法都能让你的工作效率提升10倍！
一、身份证号码的年龄计算原理
中国大陆18位身份证号码的第7-14位代表出生日期（格式：YYYYMMDD），例如510102199005076215表示1990年5月7日出生。在Excel中计算年龄需要三个关键步骤：
- 使用MID函数提取出生年月日
- 用DATEDIF函数计算当前年龄
- 通过SUMPRODUCT实现多条件统计
二、具体操作步骤详解
1. 从身份证提取出生日期
假设身份证号在A列，在B2单元格输入公式：
=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))
该公式会返回标准日期格式如"1990/5/7"
2. 计算当前年龄
在C2单元格使用DATEDIF函数：
=DATEDIF(B2,TODAY(),"y")
注意：TODAY()会自动获取当天日期，计算结果为周岁年龄
3. 年龄段分段统计
建立年龄段划分标准（案例数据在A1:A100区域）：
| 年龄段 | 统计公式 |
|---|---|
| 18岁以下 | =SUMPRODUCT((C2:C100<18)*1) |
| 18-35岁 | =SUMPRODUCT((C2:C100>=18)*(C2:C100<=35)) |
| 36-55岁 | =SUMPRODUCT((C2:C100>=36)*(C2:C100<=55)) |
| 56岁以上 | =SUMPRODUCT((C2:C100>56)*1) |
三、进阶技巧：一步到位统计法
如果不需要显示中间计算结果，可以使用组合公式直接统计。以下公式假设身份证号在A2:A100区域：
=SUMPRODUCT((DATEDIF(DATE(MID(A2:A100,7,4),MID(A2:A100,11,2),MID(A2:A100,13,2)),TODAY(),"y")<18)*1)
四、常见问题解决方案
Q1：公式报错#VALUE!怎么办？
可能原因及解决方法：
1. 存在非身份证数据 → 添加IFERROR函数容错
2. 身份证号包含空格 → 先用TRIM函数清理
3. 区域包含空单元格 → 限定有效数据范围
Q2：如何统计其他年龄段？
只需修改SUMPRODUCT中的条件参数，例如统计30-40岁人数：
=SUMPRODUCT((年龄列>=30)*(年龄列<=40))
Q3：15位旧身份证如何处理？
15位身份证第7-12位是出生日期（YYMMDD），需要调整公式：
=IF(LEN(A2)=15,DATE("19"&MID(A2,7,2),MID(A2,9,2),MID(A2,11,2))
五、实际应用案例演示
某企业人力资源部需要分析员工年龄结构，原始数据包含872名员工的身份证信息。使用本方法后：
- 18岁以下：0人（符合劳动法规定）
- 18-35岁：417人（占比47.8%）
- 36-55岁：398人（占比45.6%）
- 56岁以上：57人（占比6.6%）
分析结论：企业年龄结构呈现"橄榄型"分布，中年员工占主体，需要加强年轻人才引进。
六、注意事项
- 确保Excel日期系统设置为"1900年日期系统"
- 大陆身份证号长度应为15位或18位
- 统计前建议先使用数据验证检查身份证有效性
- 大数据量（超过1万行）时建议改用Power Query处理
通过本文介绍的方法，你可以轻松应对各种基于身份证的年龄统计需求。SUMPRODUCT函数的优势在于可以避免使用数组公式（无需按Ctrl+Shift+Enter），且计算效率更高。建议将常用年龄段公式保存为Excel模板，下次使用时只需替换数据源即可快速生成统计结果。
上一篇