如何按部门及指定工龄条件筛选出同部门工龄最接近的员工并排序

实现方案 #

适用版本：Excel 365/2021、WPS 最新版（支持动态数组） #

首先约定数据位置：

• 原始员工数据：A2:C11（A列=姓名，B列=部门，C列=工龄），表头位于A1:C1
• 查询条件：E2=待查询部门，F2=目标工龄

在输出区域的左上角单元格输入以下公式即可自动溢出所有符合条件的结果：

=SORT(FILTER(A2:C11,B2:B11=E2,"无符合条件的员工"),ABS(FILTER(C2:C11,B2:B11=E2)-F2),1)

公式说明：

• FILTER(A2:C11,B2:B11=E2,"无符合条件的员工")：筛选出所有所属部门和查询部门一致的员工信息，无匹配数据时返回提示文本
• ABS(FILTER(C2:C11,B2:B11=E2)-F2)：计算筛选出的员工工龄与目标工龄的差值绝对值，作为排序依据
• SORT(...,1)：按照差值绝对值从小到大排序，工龄越接近目标值的结果越靠前

适用版本：旧版Excel（无动态数组功能） #

需要借助辅助列实现：

1. 新增D列作为辅助列，D2单元格输入公式后下拉到所有数据行：

=IF(B2=$E$2,ABS(C2-$F$2),9999)

该公式会为符合部门条件的员工计算工龄差绝对值，不符合条件的赋值为极大值，保证排序时靠后。
2. 在输出区域首行的第一个单元格输入数组公式，按Ctrl+Shift+Enter确认生效，之后右拉3列、下拉到出现空值即可：

=INDEX(A:A,MATCH(SMALL($D$2:$D$11+ROW($2:$11)/10000,ROW(A1)),$D$2:$D$11+ROW($2:$11)/10000,0))&""

效果验证 #

当输入查询部门为HR、目标工龄为6时，输出结果和预期完全一致：

内容的提问来源于stack exchange，提问作者Edwin