Google Sheets 三维动态查找:匹配近15分钟有效时间的对应日期时间
Google Sheets 三维动态查找数组公式实现咨询
表格结构说明
- 同一表格文件包含两个工作表标签:「Lookup」、「Database」
- 已创建对应测试表格供参考
核心需求
- 在「Lookup」标签的B2单元格编写数组公式,支持整列自动填充,为匹配的机器从「Database」标签第2行提取符合条件的对应日期时间
筛选条件
- 优先执行该匹配判断:「Database」第2行的日期时间需为最接近
NOW()返回值的数值,匹配到对应列后再执行下一条校验 - 对应机器所在行的该列数值需大于0:若满足条件1的首个列在对应机器行的数值为0,则依次向后校验后续列,直至找到对应机器行中数值大于0的列
测试说明
- 测试时假设
NOW()取值为"2021/10/2 14:37:00",参考表格已标注该取值下的预期输出结果
已验证可用优化公式
感谢Player0提供的可行方案,最终可用公式支持将NOW()匹配基准更新为实际动态值,按15分钟粒度匹配,公式如下:
=ARRAYFORMULA(IFNA(VLOOKUP(A2:A,QUERY(SORT(SPLIT(FLATTEN(IF(Database!B3:N7<>"", Database!A3:A7&"×"&IF((Database!B2:N2-CEILING(NOW(),"00:15"))>-0.166678240741021,Database!B2:N2, "0")&"×"&ROW(Database!A3:A7)&"×"& ABS(Database!B2:N2-VALUE(CEILING(NOW(),"00:15")))&"×"&Database!B3:N7)), "×"), 3, 1, 4, 1), "where Col2 <> 0 and Col5 <> 0 and Col2 is not null format Col2 'dd/mm/yyyy hh:MM:ss'"), 2, 0)))
内容的提问来源于stack exchange,提问作者Rylan Grose
相关产品推荐
相关产品推荐

