Условное сопоставление на основе даты и времени

Question

У меня есть набор имен и дат на двух листах. Один лист мы назовем LargerSheet, а другой - Sheet1. На LargerSheet каждое имя имеет несколько временных дат, связанных с ним. На Листе 1 каждая дата-время имеет несколько имен, связанных с ней. Цель состоит в том, чтобы выяснить, имеют ли имена в Sheet1 совпадающее значение имени в LargerSheet, так что разница между датой-временем в Sheet1 и датой-временем, связанным с совпадающим именем в LargerSheet, составляет менее 72 часов. Кандидат, совпадающий с датой и временем на LargerSheet, также должен удовлетворять условию, что оно больше, чем дата / время, связанное с именем на Sheet1. Для обработки события, когда несколько дат и времени соответствуют критериям, идея состоит в том, чтобы смотреть только на первое совпадение. Образец листа можно найти здесь.

Также обратите внимание, что я задавал подобные вопросы раньше, но я считаю, что из-за того, как я сформулировал эти вопросы, они скорее сбивают с толку, чем помогают. Эти вопросы можно найти здесь и здесь.

Answer 1

Я полагаю, что уже близко подошел к ответу на ваш вопрос, но для этого все же потребуется некоторая настройка. Он использует MS Query для запроса рабочей книги из рабочей книги. Это позволяет запускать SQL для листов и выполнять гораздо более сложные вычисления, чем VLOOKUP.

Теперь перейдем к шагам.

1. На ленте на вкладке «Данные» в разделе «Получение внешних данных» нажмите «Из других источников» и выберите «Из запроса Microsoft».



2. Выберите «Файлы Excel»



3. В Select Workbook перейдите на свою страницу. Также измените на Все файлы, так как они не обновили код для новых расширений файлов.



4. Далее вы выбираете листы, с которых вы хотите запросить. Не беспокойтесь, если он выдаст ошибку, если вы попадете в редактор Microsoft Query, как показано ниже.



Теперь вы можете начать строить этот запрос так же, как в Access, или переключиться в представление SQL и написать запрос. Это та кнопка SQL на изображении выше. Редактор отстой, так что скопируйте вставить в SSMS или N++. Когда закончите, нажмите кнопку «Выход из двери», которая называется «вернуть данные».

Для представленной проблемы, вот 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

Добавьте данные в новый рабочий лист, чтобы получить следующие результаты:



По какой-то причине строка 2, похоже, не соответствует запросу, поскольку на самом деле она занимает 75 часов, но в этот момент я списываю это на некоторые проблемы с преобразованием даты и времени в Excel в SQL (MS Query) и обратно.

PS, этот ответ был также размещен в моем блоге, на случай, если кто-то думает, что я скопировал его без указания авторства. Я просто не хотел быть помеченным для ссылки на мой собственный блог. Вы можете связать это, если найдете это.

Answer 2

Я бы использовал Power Query Add-In для этого.

Для многих сценариев вам не нужно писать строку кода, вы просто щелкаете мышью в интерфейсе Power Query. На каждом шаге он показывает вам результат вашего преобразования данных, что делает продуктивным тестирование / отладку.

Я создал прототип решения, которое вы можете загрузить и попробовать - его: «Демонстрация Power Query - условное сопоставление на основе Datetime интервалов.xlsx» в моей демонстрационной папке OneDrive:

http://1drv.ms/1AzPAZp

На листе FinalResult есть мои результаты.

Мне пришлось сделать одну строку кода - оператор if для столбца Matches в запросе FirstMatches.