Пересечение интервалов дат excel

Обновлено: 07.07.2024

Здравствуйте! И сразу прошу прощение, за слишком мудрёное название, но оно наиболее полно отражает излагаемый ниже материал.

Я думаю многие из вас сталкивались с необходимостью вычисления пересекающихся интервалов. Но задача с которой я столкнулся на днях — оказалась не столь тривиальной. Но, обо всем по порядку.

Вычисление пересекающихся интервалов в линейном пространстве имен

Если у вас уже есть представление о пересечении интервалов, то пройдите сразу сюда.

Вычисление пересечений временных интервалов (отрезков времени) на прямой линии времени не составляет особого труда. Мы можем условно иметь пять видов временных пересечений.
Обозначим один отрезок времени как "\ \", а другой "/ /"

Смещение вперед по оси времени "/ \ / \"
Смещение назад по оси времени "\ / \ /"
Вхождение " / \ \ / "
Поглощение "\ / / \ "
Совпадение «X X»

Данный запрос вернет первого, второго и третьего пользователей. Всё довольно просто.

Смещение назад "\ \ / /"
Смещение вперед "/ / \ \"

И вот тут мы вспоминаем про отрицания выражений. Если вычислять непересечения намного проще чем пересечения, то почему бы просто не отбросить все непересечения?

Раскрываем скобки (спасибо Yggaz ):

Вуа-ля! Все намного лаконичнее!

Всё это очень прекрасно, и замечательно работает… пока линия времени прямая.

Вычисление пересекающихся интервалов в замкнутых пространствах имен

С вычислениями на линии времени мы разобрались. Так что же такое «замкнутое» пространство имен?

Это такое пространство имен, которое, при исчерпании имён первого порядка, не переходит на новый порядок а возвращается к своему началу.

Линейное пространство имен:

0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,…

Замкнутое пространство имен:

0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4,…

Предположим, что у нас есть таблица с расписанием графика работы (пусть это будет круглосуточный колл-центр):
*минуты «00» опущены для простоты выражений

usrer	start	end
usrer1	0	6
usrer2	6	12
usrer3	12	18
usrer4	18	23

Я работаю с 10 до 19 и хочу знать какие именно работники будут пересекать мой график работы.
Делаем выборку, заданной ранее схеме:

Все отлично! Я получил данные трёх работников, чей интервал пересекается с моим.

Ок, а если я работаю в ночь? Допустим с 20 до 6? То есть начало моего интервала больше его конца. Выборка из таблицы по этим условиям вернет полную ахинею. То есть крах случается, когда мой временной интервал пересекает «нулевую» отметку суток. Но ведь и в базе могут хранится интервалы пересекающие «нулевую» отметку.
С подобной проблемой я столкнулся два дня назад.

Проблема выборки интервалов по линейной методике из таблицы с данными нелинейной структуры была на лицо.
Первое, что мне пришло в голову — это расширить пространство суток до 48 часов, тем самым избавляясь от «нулевой» отметки. Но эта попытка потерпела крах — потому что интервалы между 1 и 3 никак не могли попасть в выборку между 22 и 27(3). Эх! Вот если бы у меня была двадцати-четыричная система счисления, я бы просто убирал знак второго порядка и все дела.

Я предпринял попытку найти решение этой проблемы в интернете. Информации по пересечению интервалов на «линейном» времени было сколько угодно. Но то ли этот вопрос не имел широкого обсуждения, то ли гуру SQL держали решение «для себя» — решения по замкнутой системе нигде небыло.

В общем, поспрашивав на форумах советов от гуру, я получил решение: нужно было разбить пересекающие «ноль» интервалы на две части, тем самым получив два линейных интервала, и сравнивать их оба с интервалами в таблице (которые тоже нужно было разбить на две части, если они пересекают ноль). Решение работало и вполне стабильно, хоть и было громоздким. Однако меня не покидало ощущение, что всё «намного проще».
И вот, выделив пару часов для этого вопроса, я взял тетрадь и карандаш… Привожу то, что у меня получилось:

Суть в том, что сутки — есть замкнутая линия времени — окружность.
А временные интервалы — суть дуги этой окружности.
Таким образом, мы для отрицания непересечений (см. первую часть поста), можем построить пару схем непересечения:

В первом случае мы имеем обычную линейную схему для отрицания непересечений. Во втором одна из дуг пресекает «нулевую» отметку. Есть и третий случай, который можно сразу принять как пересечение ( без отрицаний непересечения ): Оба интервала пересекают «нулевую» отметку, а значит пересекаются по определению. Кроме того, есть еще два случая, когда интервалы (вводимый и взятый из таблицы) «меняются» местами.
Немного наколдовав с базой (где-то даже методом высоконаучного тыка), мне удалось собрать вот такой запрос:

И упрощенный вариант из комментария от kirillzorin:

Запрос вполне работоспособен и, что самое забавное, справедлив для любых замкнутых систем счисления — будь то час, сутки, неделя, месяц, год. А еще, этот метод не использует «костылей», вроде дополнительных полей.

Скорее всего, я изобрёл велосипед. Но ввиду того, что сам я не нашел подобной информации, когда она мне понадобилась — предполагаю, что этот метод не слишком широко освещен. На этом моё повествование заканчивается.

Массив_данных — массив или ссылка на множество ЧИСЛОвых данных, для которых вычисляются частоты.

Массив_интервалов — массив или ссылка на множество интервалов, в которые группируются значения аргумента «массив_данных».

Функция ЧАСТОТА() вводится как формула массива после выделения диапазона смежных ячеек, в которые требуется вернуть полученный массив распределения (частот). Т.е. после ввода формулы необходимо вместо нажатия клавиши ENTER нажать сочетание клавиш CTRL+SHIFT+ENTER.

Количество элементов в возвращаемом массиве на единицу больше числа элементов в массиве «массив_интервалов». Дополнительный элемент в возвращаемом массиве содержит количество значений, превышающих верхнюю границу интервала, содержащего наибольшие значения (см. пример ниже).

Пример

Пусть в диапазоне А2:А101 имеется исходный массив чисел от 1 до 100.

Подсчитаем количество чисел, попадающих в интервалы 1-10; 11-20; . 91-100.

Сформируем столбце С массив верхних границ диапазонов (интервалов). Для наглядности в столбце D сформируем текстовые значения соответствующие границам интервалов (1-10; 11-20; . 91-100).

Для ввода формулы выделим диапазон Е2:Е12, состоящий из 11 ячеек (на 1 больше, чем число верхних границ интервалов). В Строке формул введем =ЧАСТОТА($A$2:$A$101;$C$2:$C$11) . После ввода формулы необходимо нажать сочетание клавиш CTRL+SHIFT+ENTER. Диапазон Е2:Е12 заполнится значениями:

Примечание. Функцию ЧАСТОТА() можно заменить формулой = СУММПРОИЗВ(($A$5:$A$104>C5)*($A$5:$A$104 (См. Файл примера )

Функция частота Вычисляет частоту возникновения значений в диапазоне значений и возвращает вертикальный массив чисел. Функцией ЧАСТОТА можно воспользоваться, например, для подсчета количества результатов тестирования, попадающих в интервалы результатов. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.

Аргументы функции ЧАСТОТА описаны ниже.

Примечание: Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Функция ЧАСТОТА пропускает пустые ячейки и текст.

Пример

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY) . Ее синтаксис прост:

=ЧАСТОТА( Данные ; Карманы )

Обратите внимание, что эта функция игнорирует пустые ячейки и ячейки с текстом, т.е. работает только с числами.

Для использования функции ЧАСТОТА нужно:

заранее подготовить ячейки с интересующими нас интервалами-карманами (желтые F2:F5 в нашем примере)
выделить пустой диапазон ячеек (G2:G6) по размеру на одну ячейку больший, чем диапазон карманов (F2:F5)
ввести функцию ЧАСТОТА и нажать в конце сочетание Ctrl+Shift+Enter, т.е. ввести ее как формулу массива

Во всех предварительно выделенных ячейках посчитается количество попаданий в заданные интервалы. Само-собой, для реализации подобной задачи можно использовать и другие способы (функцию СЧЁТЕСЛИ, сводные таблицы и т.д.), но этот вариант весьма хорош.

Кроме того, с помощью функции ЧАСТОТА можно легко подсчитывать количество уникальных чисел в наборе с помощью простой формулы массива:

В качестве примера подсчета чисел возьмем список с числовыми значениями от 4 до 30 (См. файл примера ).

Будем подсчитывать значения, попадающие в интервал, например, (4;15]. Причем, границы интервала «включает [ ]» и «не включает ( )» будем выбирать из Выпадающего (раскрывающегося) списка.

Дорогие друзья, в предыдущих постах мы рассмотрели, что такое дата и время в Эксель, как разложить дату на составляющие и собрать её обратно из дня, месяца и года. То есть, мы работали с одной датой. Пора нам теперь поработать и с интервалами дат. В этом посте – распространенные функции обработки временных отрезков. Разберитесь в работе этих функций, и вы уже почти на коне!

В посте о сборе данных из компонент, мы рассматривали пример получения даты, отстоящей от заданной на определенное количество месяцев.

Например, чтобы прибавить к дате в ячейке А1 пять месяцев, мы использовали формулу: =ДАТА(ГОД(А1);МЕСЯЦ(А1)+5;ДЕНЬ(А1)) . На самом деле, есть более простой и наглядный способ выполнить эту операцию – используем функцию ДАТАМЕС(Дата ; Количество_месяцев) .

У функции два обязательных аргумента :

Дата – исходная дата, к которой будем прибавлять заданное количество месяцев
Количество месяцев – число месяцев, которое нужно прибавить к дате. Если нужно вычесть месяца – укажите отрицательное число

Приведенный выше пример можно решить с помощью простой формулы: =ДАТАМЕС(А1;5) . Согласитесь, такая запись короче и легче для восприятия.

Часто нужно знать – какой день недели был (будет) в определенную дату. Как бы вы решали такую задачу? Вручную сложно, если нужно обработать несколько десятков, сотен, тысяч дат.

Воспользуйтесь функцией ДЕНЬНЕД(Дата ; Тип) . Она возвращает порядковый номер дня недели и имеет два аргумента :

Дата, для которой нужно определить день недели – обязательный аргумент
Тип – необязательный параметр, который указывает какой день недели считать первым. Например, в странах восточной Европы первый день недели – понедельник, в США – воскресенье. В любом случае, формула может считать первым днем любой день недели. Если аргумент не указан – первым днем считается воскресенье. При записи формулы – Excel выведет подсказку с перечнем возможных параметров

Функция ДЕНЬНЕД в Эксель

Когда вы получили порядковый номер дня недели, можно использовать, например, функцию условия ЕСЛИ для присвоения ему текстового имени, или обработать как-то иначе.

Нет ничего проще, чем определить количество дней между датами. Просто вычтите более позднюю дату из ранней. Например, в ячейке А1 – дата начала работы над проектом, а в А2 – дата сдачи проекта. Тогда количество дней между ними можно посчитать так: =А2-А1 .

Эту же процедуру можно выполнить с помощью функции ДНИ(Конечная дата ; Начальная дата) . Видимой разницы между первым и вторым способами нет, они возвращают одинаковые результаты. Пользуйтесь этими способами ими по ситуации.

Если вам известен некий период, и нужно знать, какую часть календарного года он занимает, используйте функцию ДОЛЯГОДА(Начальная_дата ; Конечная_дата; Базис) . Как видите, у функции 3 агрумента :

Начальная дата – дата старта изучаемого периода – обязательный аргумент
Конечная дата – дата окончания период – обязательный аргумент
Базис – базовые значения длительности года. При введении параметра программа выведет подсказку по выбору этого аргумента.

Например, проект начался 10.08.2015 и закончился 08.05.2016. Чтобы определить долю периода от календарного года, запишем формулу: =ДОЛЯГОДА(«10.08.2015″;»08.05.2016»;1) . Получим результат 0,7432. Отформатируем его в процентном формате и получим 74% года.

Чтобы получить дату последнего дня месяца – используйте функцию КОНМЕСЯЦА(Дата ; Количество_месяцев) . Эта функция возвращает последний день заданной даты, или отстоящей от нее на определенное количество месяцев. Она использует 2 обязательных аргумента :

Дата – базовый день месяца, к которому нужно прибавить месяца и вывести последний итогового месяца
Количество месяцев – сколько месяцев нужно прибавить к дате. Укажите этот параметр равным нулю, если хотите получить последний день месяца, заданного в аргументом «Дата».

Если у вас есть дата, и вам нужно узнать порядковый номер этой недели в году, используйте функцию НОМНЕДЕЛИ(Дата;Базис):

Аргумент «Дата» — это ваша дата, которая принадлежит к искомой неделе (обязательный аргумент)
Базис – необязательный аргумент, указывающий какой день недели считается первым. По умолчанию для функции – это воскресенье.

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

Вам так же может быть интересно:

Одна из типичных задач для пользователя Microsoft Excel. Имеем два диапазона дат вида «начало-конец». Задача состоит в том, чтобы определить пересекаются ли эти диапазоны и, если да, то на сколько дней.

Пересекаются или нет?

Начнем с решения вопроса о том, есть ли пересечение интервалов в принципе? Предположим, что у нас есть таблица рабочих смен сотрудников вот такого вида:

Хорошо видно, что рабочие смены Ярослава и Елены пересекаются, но как это вычислить, не прибегая к построению календарного графика и визуальному контролю? Нам поможет функция СУММПРОИЗВ (SUMPRODUCT) .

Вставим в нашу таблицу еще один столбец с формулой, которая выдает логическое значение ИСТИНА в случае пересечения дат:

На сколько дней пересечение?

интервалы не пересекаются
один из интервалов полностью поглощает другой
интервалы пересекаются частично

Периодически реализацию подобного подхода я вижу у других пользователей с помощью кучи вложенных друг в друга функций ЕСЛИ и т.п.

На самом деле все можно сделать красиво с помощью функции МЕДИАНА (MEDIAN) из категории Статистические.

Если условно обозначить начало первого интервала за Н1 , а конец за К1 , и начало второго за Н2 и конец за К2 , то в общем виде наша формула может быть записана как:

Привет Все! Задача такова: имеем 1 диапазон даты, например, 01.10.10-30.10.10
имеем 2 диапазон даты, например, 15.09.10-15.10.10
Функция должна вернуть их пересечение: 01.10.10-15.10.10
Есть ли в VBA такая стандартная функция. Или может кто городил для себя такое?

Добавлено через 21 минуту
забыл уточнить, что речь про access, а не про excell, где это, вероятно, и есть, не может не быть, иначе снова им позор.

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Пересечение интервалов дат в Excel. Вывод дат пересечения интервалов
Добрый день. Помогите пожалуйста. Мне надо найти совпадения в периодах работы одного и того же.

Запрос с диапазоном дат
Помогите с написанием кода! Через ADOQuery Запрос с диапазоном дат.

Запросы с диапазоном дат
Знает ли кто-нибудь как сделать запрос в ADOQuery, чтобы произвести фильтрацию по заданному.

Операция с диапазоном дат..
Здравствуйте. Подскажите пожалуйста что не так я делаю.. мне необходимо сделать таблицу с.

Насколько я знаю, такой готовой функции в VBA нет, а если бы была, то одинаково хорошо работала бы и в Excel и в Access. Но ничего сложного нет написать её самому.
Что то типа:
Функции действительно нет, пришлось писать свою. DS, DF, S, F - имеют формат дата DS, DF - старт, финиш фильтра, S, F - старт финиш неких событий, причем событие может быть не финализировано, т.е F может быть Null :
Функция замечательно работает, но БОЖЕ УПАСИ НАПИСАТЬ ВОТ ТАК:
напрочь отказывается работать и признавать F за Null, когда она и есть Null. И связанная с этим-же проблема: В форме невозможно для поля F задать условное форматирование на событие когда Null. ПОЛЕ НЕ ФОРМАТИРУЕТСЯ.
Кто знает - ЧТО ЗА ЧУДО - пишите. Очередная блоха Access.

Задача с диапазоном дат
Задача следующая: Необходимо задать сезонную цену в течении года и определять её по заданной дате.

SQL запрос с диапазоном дат
Помогите плиз с такой проблемой, имеется в бд поля вида dd.mm.yyyy , например 03.03.2009 , как .

Копирование файлов с конкретным диапазоном дат
Помогите пожалуйста. Есть каталог 1 в котором куча папок и файлов. Необходимо скопировать из этого.

Сравнение текущей даты с диапазоном дат
Здравствуйте! Подскажите пожалуйста. Как можно реализовать сравнение текущей даты с определенным.

Читайте также: