如何在Google Sheets中按日期分组计算当日时间跨度小时数？

样本数据 #

需求 #

按C列的**日期（而非日期时间）**分组，计算当日最早与最晚事件之间的间隔小时数（含小数）。A列和B列数据无关。

期望结果 #

对应SQL伪代码 #

SELECT
   number_of_hours(MAX(col_c) - MIN(col_c))
FROM
   my_table
GROUP BY
   DATE(col_c)

尝试过的失败公式 #

使用Google Sheets的QUERY函数时多次报错，尝试的公式包括：

• =query(A:C, "SELECT A, B, C, " & TEXT(C, "yyyy-mm-dd"), 1)：公式解析错误
• =query(A:C, "SELECT A, B, C, " & DATE(C), 1)：公式解析错误
• =query(A:C, "SELECT C, COUNT(B) GROUP BY " & DATE(C), 1)：公式解析错误
• =query(A:C, "SELECT C, COUNT(B) GROUP BY DATE(C)", 1)：无法解析QUERY函数参数2的查询字符串：PARSE_ERROR: 在第1行第33位遇到 "("
• =query(A:C, "SELECT C, DATE '" & YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C) & "'", 1)：公式解析错误
• =query(A:C, "SELECT C, " & DATEVALUE(YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C)), 1)：公式解析错误
• =query(A:C, "SELECT C, DATE(YEAR(C), MONTH(C), DAY(C))", 1)：无法解析QUERY函数参数2的查询字符串：PARSE_ERROR: 在第1行第15位遇到 "("

Google Sheets的QUERY函数不支持直接在查询语句中用DATE()这类日期提取函数分组，可通过以下两种方式实现需求：

方法一：辅助列+QUERY函数 #

1. 新增一列（比如D列），D1输入表头日期，D2输入公式并下拉填充：

   =INT(C2)
   

   INT()函数会提取日期时间的整数部分，即纯日期值。
2. 用QUERY分组计算：

   =QUERY(A:D, "SELECT D, (MAX(C)-MIN(C))*24 WHERE C IS NOT NULL GROUP BY D LABEL D '日期', (MAX(C)-MIN(C))*24 '小时数'", 1)
   

   说明：日期时间相减得到天数，乘以24转换为小时数，LABEL用于设置对应表头。

方法二：无辅助列，数组公式直接实现 #

不想新增辅助列的话，用ARRAYFORMULA结合QUERY生成临时数组处理：

=QUERY(ARRAYFORMULA({INT(C2:C), C2:C}), "SELECT Col1, (MAX(Col2)-MIN(Col2))*24 WHERE Col2 IS NOT NULL GROUP BY Col1 LABEL Col1 '日期', (MAX(Col2)-MIN(Col2))*24 '小时数'", 0)

说明：ARRAYFORMULA({INT(C2:C), C2:C})生成临时数组，第一列是纯日期，第二列是原日期时间；Col1/Col2对应临时数组的列，最后参数0表示临时数组无表头，通过LABEL手动设置。

如果需要自动生成备注列，可扩展公式：

=ARRAYFORMULA({
   QUERY(ARRAYFORMULA({INT(C2:C), C2:C}), "SELECT Col1, (MAX(Col2)-MIN(Col2))*24 WHERE Col2 IS NOT NULL GROUP BY Col1 LABEL Col1 '日期', (MAX(Col2)-MIN(Col2))*24 '小时数'", 0),
   BYROW(QUERY(ARRAYFORMULA({INT(C2:C), C2:C}), "SELECT Col1 WHERE Col2 IS NOT NULL GROUP BY Col1", 0), LAMBDA(date, 
      TEXT(MAX(FILTER(C:C, INT(C:C)=date)), "h:mmpm")&" - "&TEXT(MIN(FILTER(C:C, INT(C:C)=date)), "h:mmpm")&
      IF(COUNTA(FILTER(C:C, INT(C:C)=date))>2, "，因中间时间非极值", "")
   ))
})

该公式会自动匹配当日最早/最晚时间，根据事件数量判断是否添加额外备注。

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