Google Sheets跨午夜夜班数据匹配公式问题求助

问题概述 #

• 白班（7:00-19:00）数据处理逻辑正常，夜班（19:00至次日7:00）仅午夜前生效，午夜后失效
• 数据通过两个G-form录入：一线数据表单、主管质检首字母标记表单
• 尝试过判断跨午夜并调取前日数据的公式，但未实现预期效果

解决思路与公式示例 #

核心是对次日7:00前的夜班记录，将匹配的日期基准减1，确保关联到前一天夜班对应的质检数据。以下是两种可行方案：

方案1：VLOOKUP结合日期判断 #

假设一线数据的时间列为A列，质检数据的时间标记列为D列、质检标记列为E列：

=IF(A2 < TIME(7,0,0),
   VLOOKUP(DATEVALUE(A2)-1&" "&TEXT(A2,"HH:MM:SS"), 质检数据!$D:$E, 2, FALSE),
   VLOOKUP(DATEVALUE(A2)&" "&TEXT(A2,"HH:MM:SS"), 质检数据!$D:$E, 2, FALSE)
)

方案2：QUERY函数筛选时间区间 #

适用于需要更灵活筛选条件的场景：

=QUERY(质检数据!$A:$E,
   "SELECT E WHERE D >= datetime'"&TEXT(IF(A2<TIME(7,0,0),A2-1,A2),"yyyy-mm-dd HH:mm:ss")&"'
    AND D <= datetime'"&TEXT(A2,"yyyy-mm-dd HH:mm:ss")&"'",
   0
)

注意事项 #

• 确保所有时间列均为带日期的datetime格式，避免因纯时间格式导致的匹配偏差
• 可根据实际表格的列位置、工作表名称调整公式中的单元格引用和范围

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