Valor de pesquisa dentro de um intervalo em torno de um determinado valor de pesquisa

Question

Eu tenho 2 bancos de dados com os quais estou trabalhando no Excel. São testes de velocidade da Internet que separam o endereço inserido pelo usuário e o teste de velocidade real em dois bancos de dados. A tabela de endereços e a tabela de teste de velocidade não possuem um identificador único para vinculá-los (whoops!), Mas eles têm carimbos de data e hora.

Gostaria de usar os carimbos de data / hora como um identificador. Como faço uma pesquisa que testa valores de carimbo de tempo que, por exemplo, ocorrer 15 minutos antes ou depois do valor de pesquisa.

    

Answer 1

Teria sido útil se você tivesse fornecido alguns dados de amostra. Eu adivinhei e fabricamos alguns dados que podem se assemelhar aos seus, e esperamos que a explicação de como as fórmulas funcionam permita que você adapte isso à sua estrutura de dados.

O principal objetivo da sua pergunta é como encontrar horas em uma coluna que ocorrem dentro de 15 minutos das horas em outra coluna. As colunas Carimbo de data / hora na tabela abaixo são realmente datas-horas completas, mas formatadas para mostrar apenas as horas e minutos.

AfórmuladoarraynoE3é:

=IFERROR(INDEX(D$3:D$52,MATCH(1,(1*(24*60*ABS(B3-D$3:D$52)<=$H$3)),0)),"")

Deve ser introduzido com CTRL Deslocar Enter e depois preenchido.

Veja como funciona: A expressão interna ABS(B3-D$3:D$52) produz uma matriz das diferenças absolutas entre o tempo em B3 e todos os tempos na coluna D. O resultado é um número em que a parte decimal representa o número fracionário de dias entre os dois dados. Multiplicar por 24 * 60 converte isso para minutos, e a desigualdade verifica se esses valores são < = o número de minutos em H3 (inicialmente 15, mas mais sobre isso depois).

Neste ponto, a expressão (24*60*ABS(B3-D$3:D$52)<=$H$3) produz uma matriz de valores True / False correspondendo a se B3 está dentro de 15 minutos dos tempos na coluna D. O multiplicamento por 1 converte os valores True / False para 1 e 0. / p>

Agora o MATCH () encontra a posição do primeiro 1 no array, e INDEX () produz o tempo correspondente a partir da Coluna D. Finalmente IFERROR () produz um espaço em branco (ao invés de # N / A) se não houver tempo correspondente dentro de +/- 15 minutos foi encontrado.

Esta fórmula em F3: =IFERROR(INDEX(C$3:C$50,MATCH(E3,D$3:D$50,0)),"") simplesmente procura o tempo da Coluna E na Coluna D e retorna a velocidade correspondente da Coluna C.

Por fim, essa fórmula no G3: =IF(SUMPRODUCT(1*(24*60*ABS(B3-D$3:D$52)<=$H$3))>1,"Yes","") verifica erros. Ele resume a matriz de 1s e 0s na expressão interna e produz "Sim" se a resposta for > 1. Isso significa que houve 2 ou mais vezes dentro de 15 minutos do tempo na coluna B. A função MATCH () só encontrará o primeiro, que pode não estar correto. A maneira de contornar isso é reduzir o valor em H3 até que o "Sim" desapareça de uma determinada linha. Nesse ponto, o tempo na Coluna E é o horário mais próximo do horário na Coluna B.

Espero que você tenha achado isso útil.

Notas:

1. Gerei tempos aleatórios para as Colunas B e D, portanto, há algumas vezes na Coluna B que não têm tempo de correspondência (+/- 15 minutos) na Coluna D. Se seus dados não tiverem correspondência, você poderia remover os wrappers IFERROR.

2. Presumivelmente, seu banco de dados está crescendo, então você pode usar referências de colunas inteiras (por exemplo, D: D) nas fórmulas.