2.4 用辅助列或数据透视表解决年龄、工龄分段问题
分析年龄和工龄，需要先进行分段设置后再分析，年龄和工龄的分段分析可分为等距分布分析和不等距分布分析。等距分布是指分段的间隔是一致的，如以5年作为一个分段来分析年龄；不等距分布分析指分段的间隔大小不一，例如按30岁以下、30～40岁、40岁以上来分析年龄。
下面介绍工龄和年龄分段分析方法，其中使用数据透视表可以进行等距分布分析，使用辅助列可以进行等距和不等距分布分析。
1.使用数据透视表进行等距分布分析
以年龄等距分布分析为例，假定以每5年分段分析2020年1月1日以前的员工数据，只进行年龄分段分析，不涉及其他分析。具体操作步骤如下。
STEP 1：在“辅助表”工作表中设置筛选条件并进行高级筛选，见图2-27。
图2-27 高级筛选操作
STEP 2：将2020年1月1日起离职员工的“员工状态”修改为“在职”，并删除对应“离职日期”和“离职原因”内容。在I5单元格输入如下公式，并向下填充公式，见图2-28。
=IF(T5="在职",DATEDIF(H5,"2019-12-31","y"),DATEDIF(H5,U5,"y"))
●图2-28 重新调整员工状态
STEP 3：光标定位在数据表中任意一个单元格，在【插入】选项卡【表格】功能区单击【数据透视表】，在弹出的【创建数据透视表】对话框中直接单击【确定】按钮，见图2-29。
●图2-29 插入数据透视表
这样会生成一个新的工作表，里面有空白数据透视表，见图2-30。
●图2-30 空白数据透视表
STEP 4：将【年龄】字段拖动到【行】区域，将【员工编号】字段拖动到【值】区域，效果见图2-31。
●图2-31 数据透视表设置
STEP 5：在A列数据透视表内容中任意一个单元格单击鼠标右键，在右键菜单中单击【组合】，弹出【组合】对话框后设置见图2-32。
完成后效果见图2-33。
专家解析
在【组合】对话框中设置时，勾选【起始于】复选框，会默认出现最小数字，本例中是最小年龄。如果在后面文本框中输入数字不同于默认的数字N，则复选框被取消，表示小于N的数字会分为一组，本例中输入“21”后，小于21岁的会被分为一组，在数据透视表中显示“<21”。【终止于】操作是同样道理，【步长】代表以等距分布的大小，本例中是以每5年做一个等距分布。
●图2-32 年龄组合设置
●图2-33 年龄分段统计完成后效果图
2.使用辅助列进行不等距分布分析
年龄和工龄的分析，不能仅靠等距分布分析，这样可能分析不出具体的问题，需要结合情况进行不等距分布分析。不等距分布分析推荐采用辅助列的方法，假定工龄以1个月以内、[1-3)个月、[4-6)个月、[6-12)个月、[12-36)个月、[36-96)个月、96个月以上来做不等距分布分析，员工数据以2020年1月1日以前的数据进行操作，而且是仅进行工龄分析，不涉及其他分析，具体操作步骤如下。
STEP 1：在“辅助表”工作表中设置筛选条件并进行高级筛选，见图2-34。
●图2-34 高级筛选操作
STEP 2：将2020年1月1日起离职员工的“员工状态”修改为“在职”，并删除对应“离职日期”和“离职原因”内容。在M5单元格输入如下公式并向下填充，见图2-35。
=IF(T5="在职",DATEDIF(L5,"2019-12-31","m"),DATEDIF(L5,U5,"m"))
●图2-35 重新调整员工状态
STEP 3：在“序列”工作表中增加辅助内容，见图2-36。
●图2-36 “序列”工作表中辅助内容
STEP 4：在“辅助表”工作表中“工龄（月）”列前面插入一列并命名为“工龄分段”，然后在M5单元格中输入公式并向下填充，见图2-37。
=VLOOKUP(N5,序列!$N$2:$O$8,2,1)
STEP 5：插入数据透视表，将【工龄分段】字段拖动到【行】区域，将【员工编号】字段拖动到【值】区域，见图2-38。
●图2-37 插入工龄分段列并编辑公式
STEP 6：光标移动到A4:A10区域的任意一个单元格偏左位置，会变成黑色向右箭头，单击则会选中数据透视区域整行，然后拖动选择区域边框，会把这一行内容向上或向下移动，调整后最终效果见图2-39。
●图2-38 插入数据透视表
●图2-39 工龄分段分析最终效果
专家解析
VLOOKUP(N5,序列!$N$2:$O$8,2,1)公式中是使用VLOOKUP的近似匹配，它的第4个参数为1，在使用VLOOKUP近似匹配时，必须保证它的第2个参数中第1列内容是升序排序。这个公式中N5单元格的值为284，与“序列!$N$2:$O$8”数据区域范围的N列数字对比，它是大于等于96，所以返回96对应的“96个月以上”内容。如果N5为2，则属于大于等于1、小于4这个范围，返回结果为“[1-3)个月”。