Função do Excel para agregar valores médios (a cada segundo) a intervalos de minutos / horas, etc.

Question

Estou um pouco perdido ao tentar encontrar uma solução semelhante à função agregada das tabelas R. Pivot ou a função de agrupamento do Excel não se aplica.

Os dados são uma série de medições tomadas quase a cada segundo durante um dia. A tabela tem colunas para data, hora e valores de medição correspondentes.

Na melhor das hipóteses, os valores devem ser agrupados para cada minuto / hora. Finalmente, resultando em uma tabela mais compacta com o carimbo de tempo reduzido para minutos e um valor médio como agregado dos valores de medição.

Alguma idéia?

    

Answer 1

Nós queremos colocar os dados em uma nova tabela com o somatório correto. Portanto, sem alterações nos dados existentes, criaremos uma nova tabela, digamos em uma nova planilha, com os horários em que temos interesse:

Time | Mean Value
12:00|
12:01|
12:02|
12:03|

... etc. Você pode inserir as duas primeiras vezes e usar a alça de preenchimento na parte inferior direita do destaque da célula para copiá-las, aumentando em um minuto a cada etapa.

Na coluna Valor médio, usaremos uma função AVERAGEIFS para valores médios por critérios. A fórmula será algo como: =AVERAGEIFS($B$2:$B$361,$A$2:$A$361,">=" & $D2,$A$2:$A$361, "<" & $D3)

... onde B2: B361 é o intervalo contendo seus valores, A2: A361 é o intervalo contendo os tempos, D2 é a célula que contém um horário de início (isso seria 12:00 na tabela acima) e D3 é uma célula contendo o próximo registro de data e hora (12:01 na tabela). Isso fará a média de todos os valores em que o registro de data e hora é maior ou igual a cada minuto, mas menor que o próximo minuto.

Você pode usar uma estrutura semelhante para recolher minutos a horas.

Note que esta fórmula fornecerá um # DIV / 0! erro se não houver valores que correspondam ao período de tempo. Você pode corrigir isso incluindo a fórmula inteira em uma função IFERROR , se necessário.