Google Sheet多行列筛选指定日期考勤缺勤数据技术咨询

数据结构回顾 #

• 员工姓名：B9:B40
• 考勤日期行：F3:3，每日占用5列（如F3:J3对应2024/6/19，K3:O3对应2024/6/20）
• 考勤记录：F9:DZ，A标记缺勤，P标记出勤

方案1：筛选指定日期的缺勤员工、对应时段及总时长 #

假设你在G1单元格手动输入目标日期（如2024-06-19），使用以下公式可直接输出符合条件的员工信息：

=LET(
  target_date, G1,
  name_list, B9:B40,
  date_row, F3:3,
  attendance_data, F9:DZ,
  // 定位目标日期对应的5列范围
  start_col, MATCH(target_date, date_row, 0),
  end_col, start_col + 4,
  daily_attendance, INDEX(attendance_data,,SEQUENCE(1,5,start_col)),
  // 定义每日的5个时段名称，可根据实际修改
  time_slots, {"时段1","时段2","时段3","时段4","时段5"},
  // 逐行处理每个员工的考勤数据
  BYROW(HSTACK(name_list, daily_attendance), LAMBDA(row,
    LET(
      emp_name, INDEX(row,1),
      daily_atts, INDEX(row,2:6),
      // 筛选该员工当日的缺勤时段
      absent_slots, FILTER(time_slots, daily_atts="A"),
      absent_hours, COUNTA(absent_slots),
      // 仅输出有缺勤记录的员工信息
      IF(absent_hours>0, HSTACK(emp_name, absent_hours, TEXTJOIN(", ",TRUE,absent_slots)), "")
    )
  ))
)

公式说明 #

1. LET函数封装变量，提升可读性和计算效率
2. MATCH定位目标日期在F3:3中的起始列，自动扩展为当日5列的考勤范围
3. BYROW逐行遍历员工数据，结合FILTER提取该员工当日的所有缺勤时段
4. TEXTJOIN将多个缺勤时段合并为易读文本，COUNTA统计缺勤总时长（每时段计1小时）
5. 仅输出有缺勤记录的员工，避免空行干扰

方案2：简化版——仅提取指定日期的缺勤员工及总时长 #

如果只需要员工姓名和缺勤总时长，可使用更简洁的公式：

=LET(
  target_date, G1,
  start_col, MATCH(target_date,F3:3,0),
  daily_range, INDEX(F9:DZ,,SEQUENCE(1,5,start_col)),
  FILTER(
    HSTACK(B9:B40, BYROW(daily_range, LAMBDA(r, COUNTIF(r,"A")))),
    BYROW(daily_range, LAMBDA(r, COUNTIF(r,"A"))>0)
  )
)

原公式问题说明 #

你之前尝试的=FILTER(B9:B,F3:3=DATE(),F9:DZ="A")无法生效的原因是：

• F3:3=DATE()返回的是一行布尔值，而F9:DZ="A"是多行多列布尔值，两者维度不匹配
• FILTER要求所有条件的维度与筛选范围（B9:B为单列）对齐，因此需要通过BYROW或INDEX调整条件维度

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