gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 88 гостей онлайн
Диапазон таблицы Excel. Как сравнить даты в Excel.

Как сравнить даты в Excel.

      Нам нужно в Excel сравнить даты в разных периодах, найти одинаковые даты, посчитать, сколько одинаковых дат совпадают в определенных периодах.
      Например, мы составляем график отпусков или график отгрузки товара, оплаты счетов, график тренингов, работы, т.д. Нам среди всех этих дат нужно выявить даты, которые пересекаются, накладываются друг на друга. 
      Как сравнить даты в Excel.
      У нас такой список с датами отпусков сотрудников.
Как сравнить даты в Excel.У Ивановой и Петрова в периоде дат есть общие дни, т.е. в какие-то дни в отпуске будут сразу два сотрудника. Нам нужно выявить у кого из сотрудников идет накладка периода отпуска и на сколько дней.
       Сначала узнаем, у кого есть в периоде дат накладка дат.
В ячейке D2 пишем формулу.
 =СУММПРОИЗВ((B2<=$C$2:$C$4)*(C2>=$B$2:$B$4))>1
В этой формуле мы сравниваем дату из ячейки В2 (начало периода) с датами столбца С (окончание периодов). А, затем, сравниваем дату из ячейки С2 (окончание периода) с датами из столбца В (начало периодов).
Копируем формулу вниз по столбцу. Получится так.
Слово «Истина» говорит о том, что в этом периоде есть даты, которые повторяются в другом периоде.
Слово «Ложь» говорит о том, что дат, которые в этом периоде нет в других периодах дат.
      Другой способ сравнения дат в Excel смотрите в статье "Как сравнить два столбца в Excel на совпадение". Этим способом выделяются даты во втором столбце, отличающиеся от дат в первом столбце. Столбцов для сравнения может быть больше.
      Как посчитать в Excel, сколько дней в периодах дат пересекаются, сколько одинаковых дат в периодах.
Для этого нужно определить, пересекаются периоды дат полностью, частично или не пересекаются. В ячейке Е2 пишем такую формулу.
 =МЕДИАНА(B2;C2+1;C3+1)-МЕДИАНА(B2;C2+1;B3)
Функция «МЕДИАНА» находится в статистических формулах.
Копируем формулу вниз по столбцу. Для большей визуализации выделяем условным форматированием слова «Истина» - наложение периодов дат. Получилась такая таблица.
Здесь идет сравнение верхней даты в таблице с нижней, п.э. в третьей строке (у Петрова) стоит «0», п.ч. период Петрова сравнивается с периодом Сидорова.
      Как настроить ячейки столбца D, чтобы ячейки окрашивались при определенных условиях, смотрите в статье "Условное форматирование в Excel". Мы для этого выбрали функцию условного форматирования - "Содержит текст" и написали, какое слово нужно окрашивать "Истина".
      Проще выявить количество дней совпадений в периодах дат можно, сделав отдельно табличку с двумя периодами. Например, так.
Даты можно сортировать по году, по месяцам, например, по дню рождения, по месяцам. Как сортировать даты, читайте в статье "Как сделать сортировку в Excel по дате".