如何在Google Sheets中将QUERY与ARRAYFORMULA合并为单个单元格公式

直接用以下公式就能实现无需中间结果，单单元格完成筛选特定人员+计算总打卡时长的需求：

=LET(
  data, QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),
  dates, INDEX(data,,1),
  types, INDEX(data,,2),
  times, INDEX(data,,3),
  ARRAYFORMULA(
    SUM(
      IF(
        types="Checking-in",
        IF(
          OFFSET(types,1,0)="Checking-out",
          IF(
            dates=OFFSET(dates,1,0),
            OFFSET(times,1,0)-times,
            OFFSET(times,1,0)-times+1
          )
        )
      )
    )*24
  )
)

公式说明 #

1. 修正QUERY的字符串拼接错误
   原QUERY里的'"&H1&"'写法错误，会把H1当成固定字符串而非单元格引用。新公式用'"&H1&"'正确将H1中的姓名插入筛选条件，确保只提取目标人员的打卡记录。

2. 用LET简化逻辑提升效率

   • data：存储QUERY筛选后的目标人员打卡数据（日期、打卡类型、打卡时间三列）
   • dates/types/times：分别提取筛选结果中的对应列，避免重复调用QUERY浪费计算资源

3. 保留原时长计算规则
   完全延续你原ARRAYFORMULA的判断逻辑：

   • 仅当当前行是Checking-in、下一行是Checking-out时计算时长
   • 同一天的打卡直接用out时间减in时间；跨天打卡自动加1天（比如当天23:00签到、次日1:00签退，按2小时计算）
   • 最后乘以24将时间差（单位：天）转为小时数

替代方案（无LET函数时可用） #

如果你的Google Sheets版本不支持LET函数，可直接嵌套QUERY（效率稍低但功能正常）：

=ARRAYFORMULA(
  SUM(
    IF(
      INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),,2)="Checking-in",
      IF(
        INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),OFFSET(ROW(),1,0),2)="Checking-out",
        IF(
          INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),,1)=INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),OFFSET(ROW(),1,0),1),
          INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),OFFSET(ROW(),1,0),3)-INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),,3),
          INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),OFFSET(ROW(),1,0),3)-INDEX(QUERY(A1:G, "select B, D, E where C contains '"&H1&"'", 0),,3)+1
        )
      )
    )
  )*24
)

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