Условное соответствие на основе даты и времени
У меня есть набор имен и datetimes на двух листах. Один лист мы назовем LargerSheet, а другой будем называть Sheet1. На LargerSheet каждое имя имеет несколько связанных с ним дат. В Листе 1 каждый день имеет несколько имен, связанных с ним. Цель состоит в том, чтобы выяснить, имеют ли имена в Sheet1 значение совпадающего имени на LargerSheet, так что разница между дат-временем на Sheet1 и datetime, связанным с совпадающим именем на LargerSheet, составляет менее 72 часов. Кандидат, сопоставляющий дату и время с LargerSheet, должен также удовлетворять условию, что он больше, чем дата-время, связанное с именем в Sheet1. Чтобы обработать событие, когда несколько дат совпадают с критериями, идея состоит в том, чтобы смотреть только на первое совпадение. Лист образца можно найти здесь .
Обратите также внимание на то, что раньше я задавал подобные вопросы, но, по-моему, из-за того, как я сформулировал эти вопросы, они более запутанны, чем полезны. Эти вопросы можно найти здесь и здесь .
- Не удается открыть файл Excel 2010 на SharePoint Foundation 2013
- Как составить график данных банковского счета в Excel с таблицей изменений баланса?
- Вычисление среднего числа блоков с переменным размером в столбце?
- Графическая линейная диаграмма Excel для сгруппированных данных?
- Автоматическая сортировка для листа excel
- MS Excel Условный КОНКАТЕНАТ
- Excel: почему «File-> Save As» не помнит последний каталог, в который вы сохранили файл в
- Извлеките все HTML-теги из файла excel
- Пакетное преобразование XLS в XLSX
- Excel - сопоставление значений в столбцах на разных вкладках, а затем вставка в другой столбец, если есть совпадение
- Определить неделю в финансовом году в Excel
- Excel для подсчета количества одновременных сеансов в зависимости от времени начала и окончания
- В Excel, сделав «Стоп-кавычки» в правом столбце вместо самого левого?
Полагаю, что я подошел близко к твоему вопросу, но ему все равно понадобится какая-то настройка. Он использует MS Query для запроса рабочей книги из книги. Это позволяет запускать SQL по листам и выполнять гораздо более сложные вычисления, чем VLOOKUP.
Теперь, на шаги.
1. В ленте на вкладке «Данные» в разделе «Получить внешние данные» нажмите «Из других источников» и выберите «Из запроса Microsoft»,
2. Выберите «Файлы 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:
Лист FinalResult имеет мои результаты.
Мне нужно было сделать одну строку кодирования – инструкцию «if» для столбца «Соответствие» в запросе FirstMatches.