Tenho esta tabela simples de dados no Excel e preciso de uma fórmula para descobrir o valor total de hoje. Portanto, seria uma soma de todos os valores que ficam entre 00h00 e 23h59 para a data de HOJE(). Como posso fazer isso?
Para (possivelmente) complicar um pouco as coisas, não consigo adicionar mais colunas à planilha de origem (imagem abaixo). Toda a matemática é feita em uma folha separada. Portanto, não posso adicionar outra coluna a cada linha para adicionar alguns números auxiliares/etapas intermediárias. Idealmente, tudo seria uma grande fórmula que posso inserir em uma célula de uma planilha separada.
Dados de exemplo:
Responder1
A primeira coisa a descobrir é se suas datas são texto ou números formatados pelo Excel para serem exibidos como datas. É uma distinção muito importante e você deseja a última, pois permite usar mais fórmulas do Excel incorporadas. Alguns testes possíveis são
- =ÉTEXTO(A2)
- =ÉNÚMERO(A2)
- Altere a formatação da célula para geral e veja se o conteúdo exibido muda
Para efeitos desta solução, suas datas serão consideradas números. Caso contrário, há muitas perguntas/respostas sobre como converter a data do texto em números/formato Excel.
ENTENDENDO HORA/DATA NO EXCEL
O Excel armazena a data como um número inteiro. É a contagem do número de dias desde 0 de janeiro de 1900. Portanto, 1 é 1º de janeiro de 1900. (Acredito que o ano de referência seja 1905, mas posso estar errado). Portanto, todas as datas no Excel são simplesmente números inteiros com alguma formatação especial aplicada para exibi-las da maneira que estamos acostumados a ver. O tempo, por outro lado, é armazenado em% de um dia ou, se preferir, em decimal de um dia. 0,5 representa meio dia ou almoço. Portanto, se você precisar apenas encontrar a data de uma data mista, será necessário retirar apenas a parte inteira. inversamente, se você precisar apenas de tempo, basta retirar a parte decimal.
Opção 1 SUMPRODUTO
SUMPRODUCTé uma função regular que executa cálculos semelhantes a array. Como tal, você deseja limitar a referência de intervalo a aproximadamente seus dados e não usar referências completas de coluna/linha. Se você acabar fazendo isso mais tarde, poderá sobrecarregar sua máquina com cálculos excessivos e inúteis em células vazias.
=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)
Nota TODAY()é uma função volátil. Isso significa que ele recalcula sempre que algo na planilha muda, mesmo que isso não afete a função. Uma função não volátil (a maioria das funções regulares) só é recalculada quando ocorre uma alteração que as afeta. Como resultado, se você copiar uma função volátil para muitas células, poderá travar o computador toda vez que algo em uma planilha for alterado/inserido.
Opção 2 - SOMASE
SUMIFSé uma fórmula regular na qual você pode usar referências de colunas completas sem se preocupar e minha opção preferida sobre como lidar com essa situação. Esta fórmula assume a forma de:
SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)
então, no seu caso, você deseja verificar o início e o final do dia, para que sua fórmula tenha que condicionar as verificações e fique parecida com a seguinte.
=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)