Guru do Excel e Power BI

Boa noite a todos.
Estou com um problema aqui e sou um tanto iniciante em excel.
Tenho uma tabela dinâmica no excel, onde é inserido um registro a cada hora.
Nesse registro tenho uma coluna de timestamp no formato dd/MM/yyyy hh:mm:ss hora de inserção do registro. E uma coluna de energiaacumulada.
O que preciso é, criar uma outra tabela fazendo o maximo(energiaacumulada) - minimo(energiaacumulada) POR DIA.
Ou seja nessa nova tabela o vai ter um timestamp por dia e o resultado.
Objetivo, pegar o consumo de energia por dia.
Será que deu para entender?
Agradeço a quem puder me ajudar com esse problema. Obrigado.

Olá Willian,

Anexei uma planilha que mostra como fazer a segunda tabela. Se você tivesse anexado a sua planilha, a solução seria mais de acordo com a sua realidade.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Bom dia Jimmy.
É mais ou menos isso mesmo. Só uma dúvida, a minha tabela de energia, não vai ter um fim específico, ou seja. Pode ser que um dia eu abra ele e o último registro vai estar na célula A17, no outro dia quando for abrir pode estar no A30 e assim por diante. Existe uma forma da minha tabela resultado ser dinamicamente calculada conforme a minha tabela for se populando?
Não sabia dessa forma de linkar o arquivo por aqui, em todo caso, segue um modelo exemplo:
Obrigado.

Olá Willian,

Agora, com a sua planilha, entendi melhor o que quer.

Temos 2 problemas:

PROBLEMA 1) Posso ver que os dados de data/hora não foram digitados. Provavelmente vem de outro sistema, ou resultado de cálculos. Esses valores não estão chegado redondos na planilha. Vamos pegar o exemplo da célula A11 da sua planilha. Temos 06/11/2019 00:00. Mesmo que formatemos para também vermos os segundos teremos 06/11/2019 00:00:00.

Agora transforme essa data em valor com decimais (formatação de número com o ícone de 3 zeros “000”). Verá 43.775,00. Se aumentar para 6 decimais verá 43775,000000 o que faz sentido, porque 43775 é realmente ao correspondente de 06/11/2019, e 000000 quer dizer zero horas.

Agora aumente ainda mais a quantidade de decimais, para 8, por exemplo.Verá 43.774,9999965!! Isso quer dizer que na verdade o que está na célula é QUASE zero horas do dia 06, ou seja, representa um pouco depois das 05/11/2019 23:59:59, mas antes das zero horas do dia 06.

Se fizer esse mesmo processo com 06/11/2019 00:00 digitado, verá que esse problema não ocorre.

Isso atrapalha um pouco a fórmula que pega o mínimo do dia, porque o valor da A11 é visto pela fórmula como sendo dia 05.

Contornei isso acrescentando um valor bem pequeno à data (0,00001), e assim o dia da A11 passou a ser visto como dia 06 mesmo. Para horas diferentes de 00:00, 06/11/2019 04:00 da célula A12, por exemplo, esse problema não afeta nada porque um pouquinho a mais, ou a menos, continua sendo o dia 06.

Apesar de contornado o problema, o ideal é alterar a fonte do dado para que passe a ser enviado corretamente.

PROBLEMA 2) Seu método de cálculo está errado. Pegar o menor do dia e subtrair do maior do dia é uma forma errada de obter o consumo.

Se somar os consumos que calculou para os 6 dias, terá 10417. Agora, usando o mesmo método, calcule o consumo entre o dia 06 e o dia 10. Seria a seguinte operação: 13385-788 = 12597, quase 25% a mais!

A forma correta de fazer essa conta, digamos para calcular o consumo do dia 05 é:
Valor mínimo do dia seguinte (no caso dia 06) menos o Valor mínimo do próprio dia 05
Ou seja, VALOR FINAL menos o VALOR INICIAL
Esquece o valor máximo. Não nos atende. A não ser que passemos a usar o valor máximo de um dia menos o máximo do outro. Ai daria certo, mas nesse caso o consumo não seria de zero horas a zero horas (dia cheio) seria de 20h de um dia às 20h do outro.

Em outras palavras, o valor registrado às zero horas do dia 06, servirá como valor final do dia 05, e também como valor inicial do próprio dia 06.

Nesse conceito, o valor total consumido na sua planilha é o valor das zero horas do dia 11 menos o valor das zero horas do dia 05, que seria 13541 - 788 = 12753.

Antes de continuarmos com o desenvolvimento de fórmula, vamos finalizar esses dois assuntos acima, porque eles estão relacionados, uma vez que o valor de zero horas é fundamental para o cálculo do consumo diário.

Aguardo seus comentários.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Perfeito Jimmy. Sua observação realmente faz sentido. Não tinha percebido esse problema. Sim essa tabela é população através de outro sistema. E da forma como você explicou realmente é o correto. Temos que pegar o mínimo do dia 6 menos o mínimo do dia 5 para chegar ao consumo do dia 5.
Esse planilha que enviei na verdade foi um exemplo rápido que bolei aqui. O meu cenário na verdade é um sistema que popula essa tabela a cada 1 hora, e , pode ser que entre hora arredondada 00:00:00 mas na maioria das vezes pode entrar 00:00:01.

Willian,

Se a macro somar 1 segundo a cada dia/hora, digo no momento de levantar os dias e os mínimos em cada dia (não nos dados em si), creio que o problema está resolvido, pois 05/11/2019 23:59:59, ou 06/11/2019 00:00:01 ficariam ambos no dia 06.

Se eventualmente faltar a medida das zero horas, o consumo do dia anterior vai ficar 1 hora maior, e o do dia da falta vai ficar 1 hora menor, mas o consumo terá sido todo contabilizado.

Não sei se percebeu, mas a fórmula que inicialmente te mandei é matricial. Se acrescentar 24 linhas por dia na massa de dados, em 1 ano terá mais de 8000 linhas. Você disse que desejava que as linhas não tivesses que ser abertas à medida que o dados vem. Assim, as fórmulas teriam que estar nas 8000 linhas, processando todas elas, mesmo que no início estejam quase todas vazias.

Digo isso tudo porque temos que considerar a questão da performance.

Pergunta: o sistema popula diretamente a planilha, ou vai guardando as informações em outro canto, e de vez em quando alguém as passa para a planilha? Se for desta segunda forma, com qual periodicidade?

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Jimmy, agradeço mais uma vez pela sua atenção e concerteza já dei o positivo na resposta.
Voltando ao tópico, talvez eu criei um exemplo muito sucinto e não expliquei de forma mais detalhada.
Bom, eu tenho um sistema que faz a coleta desses dados e guarda em um arquivo do formato dele, não sei como funciona esse arquivo pois é um código fechado desse sistema, acredito que seja uma espécie de banco de dados interno dele.
No excel, é instalado um módulo dele onde eu consigo configurar uma pesquisa, por exemplo. Eu consigo definir por exemplo uma célula de input de data inicio e outra de data fim. E quando faço um refresh, ele me traz os dados desse período.
Porém esse sistema coleta dados de energia acumulada, como no exemplo que te passei, e para calcular o consumo por dia eu preciso fazer esses cálculos. Uma coisa que talvez tenho dado no exemplo de forma reduzida também, e que o período de coleta desses dados é a cada 2 minutos, ou seja 720 registros por dia.
E quando eu faço essa busca, ele popula os dados de acordo com a pesquisa de data inicio e data fim, ou seja, se for um período "pequeno", a tabela retornada é pequena e vice versa, ou seja, essa parte é bem dinâmico e nada fixo.
A periodicidade de busca não é muito definida pois depende muito do operador que fará essa consulta.

Olá Willian,

Agora entendi mais um pouco do contexto.

Me diga, os dados são trazidos para o Excel em períodos, e depois de analisados são apagados, uma vez que continuam no sistema e podem ser trazidos novamente a qualquer momento, ou vão sendo trazidos e permanecem no Excel, sempre se acumulando?

Pelo que falou no primeiro post, teu interesse é obter os dados de consumo diários. O que acha de fazermos assim:

a) você especifica a faixa de datas e o outro sistema traz os dados pra dentro da planilha

b) Você faz as análises que costuma fazer, e quando vezes quiser o consumo diário, você roda uma macro que simplesmente exclui todos os dados do dia, exceto o primeiro (de cada 720, fica só um), e ela já faz aquele pequeno ajuste na hora (somando 1 segundo) pra evitar aquele erro do dia 06 não ser dia 06. Porque mantermos os dados intermediários se não são usados no cálculo do consumo diário?

c) Os dados que ficam são poucos, e o cálculo do consumo diário é uma conta simples do registro do dia seguinte menos o registro do próprio dia.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Perfeito Jimmy,
Os dados são trazido para o excel através da consulta, o usuário pode até salvar o arquivo após a consulta, porém quando for efetuar uma nova consulta os dados são sobrescrevidos pelo resultado da nova consulta, na mesma posição.
A sua solução me parece bem válida, porém não faço idéia de como ficaria essa macro.
Mais uma vez, obrigado pela colaboração.

Willian,

Me manda uma planilha com os dados, o mais próximo possível com a planilha real. Vou olhar e depois trocamos uma ideia de como fazer.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Caro Jimmy, obrigado mais uma vez pela colaboração.
Estou enviando um arquivo exemplo da consulta que fiz aqui, não sei se você conseguirá abrir aí pois a configuração que faço é nesse módulo reportworx express bizviz instalado no excel.
Porém estou sem um sensor aqui que me forneça valores acumulados, então tive que usar um valor random para o acumulado, e nesse exemplo minha coleta está sendo a cada 30 segundos, mas a idéia segue a mesma.
Mais uma vez, obrigado.

Willian,

Segue a planilha para a sua análise. É claro que se os dados não tiverem o primeiro e último dias completos, o consumo apresentado é parcial, certo?

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo" existente acima e a direita de cada mensagem.

Jimmy San Juan

Esqueci de anexar a planilha. Agora segue.