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

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

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

  • Excel: Как получить динамическую формулу, которая может отображать общее количество уникальных значений в диапазоне?
  • Разделить ячейку Excel последним разделителем
  • Как идентифицировать ячейки в столбце A, содержащие любые значения в столбце B
  • Можно ли суммировать весь столбец без установки явных границ ячеек в Excel?
  • Конвертировать несколько валют в usd без использования vlookup
  • Заполните большой диапазон формулой в Excel, без перетаскивания мышью, чтобы расширить
  • Условное форматирование для выделения всей строки
  • Вероятная причина искажения файлов excel
  • Как оптимизировать функцию VBA в Excel
  • Как изменить формат даты с dd-mm-yyyy на dd-mm-yy в строке формул MS-EXCEL?
  • Excel: сочетание клавиш для перемещения (своп) столбца влево или вправо?
  • Как импортировать (и правильно просматривать) Китайский файл CSV с кодировкой UTF-8 с Excel 2011 на Mac OS X
  • 2 Solutions collect form web for “Условное соответствие на основе даты и времени”

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

    Теперь, на шаги.

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

    Вкладка «Данные» - из других источников - из запроса ms

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

    Выбрать файлы excel

    3. В разделе «Выбрать книгу» перейдите на страницу. Также измените на All Files, так как они не обновили код для новых расширений файлов.

    Перейдите к своей книге

    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 часов awa, но в этот момент я пишу это, чтобы решить проблему с преобразованием даты и времени Excel в SQL (MS Query) и обратно.

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

    Для этого я бы использовал надстройку Power Query.

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

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

    http://1drv.ms/1AzPAZp

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

    Мне нужно было сделать одну строку кодирования – инструкцию «if» для столбца «Соответствие» в запросе FirstMatches.

    Давайте будем гением компьютера.