Conditional Matching Based on Datetime

Question

Eu tenho um conjunto de nomes e datetimes em duas planilhas. Uma folha que chamaremos de LargerSheet e a outra, chamaremos Sheet1. Em LargerSheet, cada nome tem vários datetimes associados a ele. Na Sheet1, cada datetime possui vários nomes associados a ela. O objetivo é descobrir se os nomes na Planilha1 têm um valor de nome correspondente em LargerSheet, de forma que a diferença entre o datetime na Planilha1 e o datetime associado ao nome correspondente em LargerSheet seja menor que 72 horas. O candidato correspondente datetime no LargerSheet também deve satisfazer a condição de que é maior que o datetime associado ao nome na Planilha1. Para lidar com o evento em que vários datetimes correspondem aos critérios, a ideia é observar apenas a primeira correspondência. A folha de amostra pode ser encontrada aqui .

Note também que eu já fiz perguntas semelhantes antes, mas acredito que pela maneira como escrevi essas perguntas, elas são mais confusas do que úteis. Essas perguntas podem ser encontradas aqui e here .

    

Answer 1

Acredito que cheguei perto de responder sua pergunta, mas ainda precisaria de alguns ajustes. Ele usa o Microsoft Query para consultar a pasta de trabalho da pasta de trabalho. Isso permite que você execute SQL nas planilhas e realize cálculos muito mais complexos que o VLOOKUP.

Agora, para as etapas.

1. Na faixa de opções, na guia Dados, na seção Obter dados externos, clique em “De outras fontes” e selecione “Do Microsoft Query”



2. Selecione “Arquivos do Excel”



3.EmSelecionarpastadetrabalho,navegueatésuapágina.AlteretambémparaTodososArquivos,poiselesnãoatualizaramocódigoparaasnovasextensõesdearquivo.



4. Em seguida, selecione as planilhas das quais deseja consultar. Não se preocupe se ocorrer um erro, contanto que você chegue ao editor do Microsoft Query conforme mostrado abaixo.



Agora,nesteponto,vocêpodecomeçaracriaressaconsultacomofarianoAccessoupodealternarparaaexibiçãoSQLegravaraconsulta.ÉessebotãoSQLnaimagemacima.Oeditoréumadroga,entãocopieecolenoSSMSouN++.Quandoterminar,aperteobotão"door exit", chamado "return data".

Para o problema apresentado, aqui está o SQL:

SELECT 'S1'.DATES , 'S1'.NAMES , MAX('LS'.DATES) 
FROM 'C:\Admin\StackExchange\vlookup multiple results.xlsx'.'Sheet1$' 'S1'
LEFT OUTER JOIN 'C:\Admin\StackExchange\vlookup multiple results.xlsx'.'LargerSheet$' 'LS'
ON 'S1'.NAMES = 'LS'.NAMES
WHERE ('S1'.DATES - 'LS'.DATES) > -3 and ('S1'.DATES - 'LS'.DATES) < 3
group by 'S1'.DATES, 'S1'.NAMES

Adicione os dados a uma nova planilha para obter os seguintes resultados:  



Por algum motivo, a linha 2 parece não corresponder à consulta, pois, na verdade, ela é de 75 horas, mas neste ponto, escrevo isso para algum problema com a conversão de data e hora do Excel para SQL (MS Query) e de volta.

P.S., esta resposta também foi postada no meu blog, no caso de alguém achar que copiei sem atribuição. Eu só não queria ser marcado por ligar para o meu próprio blog. Você pode vinculá-lo se encontrá-lo.

    

Answer 2

Eu usaria o suplemento de consulta de energia para isso.

Para muitos cenários, você não precisa escrever uma linha de código, basta clicar na interface do usuário do Power Query. Em cada etapa, você mostra o resultado de sua transformação de dados, fazendo testes / depurações produtivos.

Eu criei uma solução de protótipo que você pode baixar e tentar - a sua: "Demonstração do Power Query - Correspondência condicional baseada em intervalos de data e hora.xlsx" na pasta de demonstração do OneDrive:

link

A folha FinalResult tem meus resultados.

Eu tive que fazer uma linha de codificação - a instrução "if" para a coluna de correspondências na consulta FirstMatches.