非365版Excel中带近似匹配的双因素查找方法问询

需求说明 #

• 处理受控准入工地的考勤数据，核心目标：给定员工姓名、指定时间，找到该员工在受控区域内（排除非受控地点如Lobby）最接近指定时间的刷卡记录，并获取对应的时间值及进出状态
• 原始数据包含列：姓名、Badge scan time（刷卡时间，Excel日期格式）、scan location（格式如"Controlled IN"/"Controlled OUT"/"Lobby"）
• 数据已按刷卡时间升序排序
• 已有基础方法：
  • 可拼接姓名与刷卡地点类型做匹配
  • 用=INDEX(BadgeTime,MATCH(TRUNC(DATE($B$3,$C$3,$D$3)+$F5,6),BadgeTime,1),1)结合辅助列实现指定时间的近似查找

核心挑战 #

需实现姓名+受控区域双条件下的近似时间查找（取≤指定时间的最近记录），而非常规双因素精确匹配。

示例验证 #

查询2021-11-01 06:45的Harmony Song → 匹配到Controlled IN对应的时间值44501.275115
查询2021-11-01 06:30的Harmony Song → 跳过Lobby记录，匹配到Controlled OUT对应的时间值44501.269965

公式解决方案 #

假设数据范围 #

• 姓名列：$A$2:$A$1000
• 刷卡时间列：$B$2:$B$1000（Excel日期数值格式，如44501.275115）
• 刷卡地点列：$C$2:$C$1000
• 指定查询姓名：$F$2
• 指定查询时间：$G$2（需为Excel可识别的日期格式）

方案1：兼容旧版Excel的数组公式

输入公式后按 Ctrl+Shift+Enter 确认：

=MAX(($A$2:$A$1000=$F$2)*ISNUMBER(SEARCH("Controlled",$C$2:$C$1000))*($B$2:$B$1000<=$G$2)*$B$2:$B$1000)

• 逻辑拆解：
  1. $A$2:$A$1000=$F$2：精准匹配目标员工
  2. ISNUMBER(SEARCH("Controlled",$C$2:$C$1000))：筛选所有受控区域记录（自动排除Lobby等非受控地点）
  3. $B$2:$B$1000<=$G$2：限定记录时间不晚于指定查询时间
  4. MAX函数提取符合所有条件的最大时间值，即最近的刷卡记录

方案2：Excel 365/2021+ 动态数组公式

无需按组合键，直接输入即可：

=MAX(FILTER($B$2:$B$1000,($A$2:$A$1000=$F$2)*ISNUMBER(SEARCH("Controlled",$C$2:$C$1000))*($B$2:$B$1000<=$G$2),""))

• 逻辑拆解：
  • FILTER先筛选出满足三个条件的所有刷卡时间
  • MAX取筛选结果的最大值，得到最近记录
  • 无匹配记录时返回空字符串""

扩展：获取对应进出状态 #

若需同时得到该记录的IN/OUT状态，可配合INDEX+MATCH：

=INDEX($C$2:$C$1000,MATCH(MAX(($A$2:$A$1000=$F$2)*ISNUMBER(SEARCH("Controlled",$C$2:$C$1000))*($B$2:$B$1000<=$G$2)*$B$2:$B$1000),$B$2:$B$1000,0))

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