fórmula de excel para calcular a média de blocos consecutivos de 24 horas?

Question

Microsoft Excel 2010, arquivo de entrada de milhares de linhas de dados por hora, portanto, existem 24 valores (linhas) por dia.

Vamos supor que meus dados iniciem em B1 e suponhamos que eu coloquei meu valor médio para o dia 1 em C1. O conteúdo da célula C1 será = MÉDIA (A1: A24), até aí tudo bem.

A questão é o que eu preciso colocar na célula C2 para fazer com que o conteúdo da célula C2 se torne a média de A: 25 até A: 48 de tal maneira que eu possa copiar a mesma fórmula abaixo da coluna C e preenche apropriadamente C3 como média de A: 49 até A: 72 e assim por diante?

    

Answer 1

Uma variação na abordagem do Excellll .....

Experimente esta fórmula em C1 copiada para baixo

=AVERAGE(OFFSET(A$1,(ROWS(C$1:C1)-1)*24,0,24))

.... ou ... uma desvantagem dos itens acima é que pode ser difícil entender o que a fórmula está realmente fazendo: essa abordagem colocará as fórmulas explícitas AVERAGE em cada célula:

Cole esta fórmula em C1 e copie para baixo

="=AVERAGE(A"&(ROWS(C$1:C1)-1)*24+1&":A"&ROWS(C$1:C1)*24&")"

Isso criará todas as fórmulas como texto ...

Agora converta-os em fórmulas reais fazendo isso

Selecione a coluna > clique com o botão direito > Copiar > clique com o botão direito > Colar especial > Valores > OK > Texto para colunas > Concluir

    

Answer 2

Se puder funcionar em seu contexto, considerarei uma Tabela Dinâmica para fazer isso.

Supondo que suas horas estejam no formato adequado de data e hora do Excel (se não, crie uma nova coluna e faça uma fórmula para transformá-la em data e hora):

• Selecione A1 para B5000 (ou até onde você acha que irá eventualmente)
• Crie uma tabela dinâmica e coloque-a na coluna C
• Adicione A-Hours na área Row Labels
• Agrupe por dias (clique com o botão direito em uma data, selecione Grupo e escolha Dias)
• Adicione B-Data na área Values e defina-a como média em vez de soma.

Esta solução é:

• simples de implementar
• fácil de entender
• flexível (pode ser movido, filtrado, resumido por mês, etc.)
• à prova de erros / sem preocupações: se estiver faltando uma linha ou de alguma forma quebrar o deslocamento, ela ainda calculará suas médias diárias corretamente com base nos dados reais, em vez de confiar nas suposições do número de linha. Você pode até adicionar uma Contagem extra na área de valores apenas para confirmar que você tem 24 valores todos os dias.