Como encontrar os critérios de reunião do último e do primeiro valor em uma série de valores?

Question

Esta é uma amostra da minha entrada:

Então,tenhomeia-noiteemdiassubsequentesegostariadeobteresseresultadoautomaticamente:

Para colocar em palavras: Eu tenho datetimes em uma coluna e gostaria de encontrar o último datetime que é antes da meia-noite de um dia e o primeiro datetime que é após essa meia-noite. Não pode haver nenhum, um ou mais de um datetime para um dia. Eu também preciso encontrar um valor que esteja em uma coluna ao lado da entrada original.

FYI: os dados de entrada são instantâneos do meu medidor de gás. Número na coluna Valor é um total de metros cúbicos gastos antes do tempo na primeira coluna. Preciso encontrar esses valores de borda para calcular os gastos em um dia. Eu sou capaz de fazer o cálculo, mas não consigo encontrar esses valores na entrada.

Obrigado!

    

Answer 1

Você desejará usar a função MATCH . É como HLOOKUP e VLOOKUP , exceto que retorna um índice (1 .. n ) em vez de um valor (e, se você não estiver familiarizado com HLOOKUP e VLOOKUP , provavelmente deve aprender sobre eles). MATCH(D2, $A$2:$A$14) retornará o índice do último valor em $A$2:$A$14 que é menor ou igual a D2 ; ou seja, (o índice de) a última vez antes da hora da meia-noite em D2 . (Isso é na forma de um número inteiro entre 1 e 13, o que pode ser um pouco confuso, mas está tudo bem.) Para evitar a redundância, é útil usar "colunas auxiliares". então eu coloquei =MATCH(D2, $A$2:$A$14) em I2 . Em seguida, insira estas fórmulas:

• E2 → =INDEX($A$2:$B$14, $I2, 1)
• F2 → =INDEX($A$2:$B$14, $I2, 2)
• G2 → =INDEX($A$2:$B$14, $I2+1, 1)
• H2 → =INDEX($A$2:$B$14, $I2+1, 2)

em que usamos INDEX para buscar os valores desejados das colunas A e B .

Limpei sua entrada um pouco, para fins de teste / demonstração:

eaquiestãoosresultados:

Isso aumentará as datas na coluna D anteriores a A2 . Para corrigir isso, adicione outra coluna auxiliar ( J ) e altere G e H :

• J2 → =IFERROR(I2, 1) + 1
• G2 → =INDEX($A$2:$B$14, $J2, 1)
• H2 → =INDEX($A$2:$B$14, $J2, 2)