Início / coding / Perguntas / 79245148
Accepted
user3466413
Asked: 2024-12-03 02:16:53 +0800 CST

Como faço operações básicas de agrupamento de datas no Planilhas Google?

  • 772

Tenho dados que se parecem com estes (exemplo):

Coronel A Coronel B Coronel C
Valor 1 Valor 2 04/01/24 16h15
Valor 3 Valor 4 04/01/24 18h30
Valor 5 Valor 6 05/01/24 14h30
Valor 7 Valor 8 05/01/24 15h30
Valor 9 Valor 10 05/01/24 17:30

Estou tentando fazer algo que, em SQL adequado, deveria ser bem direto: "para cada data Col C , não data/hora , na tabela, encontre o número de horas, incluindo horas decimais, decorridas entre o primeiro e o último evento naquela data". Os valores em Col Ae Col Bnão são relevantes.

O resultado líquido é que quero que isso volte, ou algo semelhante (comente aqui apenas para facilitar a leitura):

Data Horas Comentário
04/01/24 2,25 18h30 - 16h15
05/01/24 3 17h30 - 14h30, porque 15h30 não é um mínimo ou máximo

Em SQL, eu acho que a consulta seria assim, com algum pseudocódigo:

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

Não consigo descobrir como fazer isso no Planilhas Google. Considerando que pensei no problema mais como um problema de banco de dados do que algo que você faria em uma planilha, tentei a QUERYfunção, mas tudo que faço acaba com uma consulta inválida. Estou pensando que o Planilhas Google não permite que você extraia a parte da data de uma data/hora e agregue-a.

Coisas que tentei, mas falhei:

  • =query(A:C, "SELECT A, B, C, " & TEXT(C, "yyyy-mm-dd"), 1): Erro de análise de fórmula
  • =query(A:C, "SELECT A, B, C, " & DATE(C), 1): Erro de análise de fórmula
  • =query(A:C, "SELECT C, COUNT(B) GROUP BY " & DATE(C), 1): Erro de análise de fórmula
  • =query(A:C, "SELECT C, COUNT(B) GROUP BY DATE(C)", 1): Não é possível analisar a sequência de consulta para o parâmetro QUERY da função 2: PARSE_ERROR: Encontrado " "(" "( "" na linha 1, coluna 33
  • =query(A:C, "SELECT C, DATE '" & YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C) & "'", 1): Erro de análise de fórmula
  • =query(A:C, "SELECT C, " & DATEVALUE(YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C)), 1): Erro de análise de fórmula
  • =query(A:C, "SELECT C, DATE(YEAR(C), MONTH(C), DAY(C))", 1): Não é possível analisar a sequência de consulta para o parâmetro QUERY da função 2: PARSE_ERROR: Encontrado " "(" "( "" na linha 1, coluna 15

Então, como posso fazer isso?

date

2 respostas

  1. Best Answer

    Você pode usar:

    =SORT(LET(
   c,TOCOL(C2:C,1),
   d,TEXT(c,"m/d/yy"),
   MAP(UNIQUE(d),LAMBDA(u,LET(
    f,FILTER(c,d=u)*24,
    {u,MAX(f)-MIN(f)})))))
    • 2

  2. Existem alguns problemas ao lidar com datas de planilhas e datas de consulta do Google :

    • Operações aritméticas como difference não são suportadas para objetos de data em query. Isso dificulta o cálculo de diferenças reais.

    • A época do objeto de data da consulta do Google é 1º de janeiro de 1970, enquanto a data inicial na planilha é 30 de dezembro de 1899. A diferença é de 25.569 dias

    • A consulta representa a data em milissegundos a partir da época, enquanto as planilhas a representam em dias a partir de 30 de dezembro de 1899.

    Superamos isso alterando as datas da planilha TO PURE NUMBERs(dias) e então convertendo-as para milli ssegundos de epoch(-25569) e então usando uma consulta nela:

    =LET(
  days2ms,24*60*60*1000,
  diff, "(max(Col1)-min(Col1))/"&days2ms,
  QUERY(
    ARRAYFORMULA((TO_PURE_NUMBER(C1:C5)-25569)*days2ms),
    "Select 
      toDate(Col1),
      "&diff&" 
     group by toDate(Col1) 
     format "&diff&" 'h.m'"))
    Saída:
    a data() quociente(diferença(máx. mín.)86400000())
    04/01/2024 2.15
    05/01/2024 3.0
    • 0

relate perguntas