Напоминание о датах

Читатель блога по имени Алексей оставил в комментариях вот такую задачу:

Недавно возникла на первый взгляд простая задача: сделать “напоминалку” дней рождения сотрудников за 2 дня и за 1 день (после того, как коллектив пропустил день рождения одного из сотрудников)

Подобные напоминания о каких-либо сроках всегда очень востребованы - пропустить день рождения сотрудника, срок уплаты процентов по кредиту или срок предоставления отчета очень неприятно.

Давайте разберемся, как в Excel можно реализовать отслеживание таких дат.

Исходные данные и постановка задачи

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

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

Создание фильтра

Для выборки только тех ученых, чьи дни рождения приходятся на завтра и послезавтра, можно воспользоваться условным форматированием:

  1. Выделить данные в столбце "Дата рождения".
  2. На вкладке "Главная" нажать кнопку "Условное форматирование" и выбрать пункт меню "Создать правило..."
  3. В открывшемся диалоговом окне выбрать пункт "Использовать формулу для определения форматируемых ячеек"
  4. Ввести в поле формулы следующее значение:
    =ВЫБОР(ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ($B4);ДЕНЬ($B4))-СЕГОДНЯ();1;1)

    Эта формула определит ячейки, которые нужно отформатировать (механизм действия описан ниже).

  5. Настроить формат отображения ячеек, щелкнув по кнопке "Формат...". Обязательно укажите какую-нибудь заливку для ячейки - мы будем их отфильтровывать именно по цвету.
  6. Нажмите "ОК", чтобы применить внесенные изменения.

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

Фильтр по цвету ячейки в таблице Excel

Фильтрация по цвету ячейки

Для демонстрации подхода формула условного форматирования в файле-примере вместо функции СЕГОДНЯ() содержит ссылку на ячейку B2, в которую можно ввести любую дату.

Сортировка по дням рождения в этом году

Для сортировки по дням рождения в таблицу придется добавить дополнительный столбец "День рождения". Формула в этом столбце будет выглядеть так:

=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ($B4);ДЕНЬ($B4))

В результате в этом столбце появятся даты дней рождения лауреатов Нобелевской премии в этом году. Теперь Вы можете легко отсортировать таблицу по этому столбцу.

Как работает формула для условного форматирования?

  1. =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ($B4);ДЕНЬ($B4)) определяет дату дня рождения, используя текущий год и месяц/день из столбца "Дата рождения".
  2. Вычитая из этой даты СЕГОДНЯ() мы получаем количество дней до дня рождения. Это число может быть и отрицательным, если день рождения в этом году уже был. Нас интересуют значения 1 (день рождения завтра) и 2 (день рождения послезавтра)
  3. Функция ВЫБОР() возвращает из списка значение под определенным порядковым номером - именно это нам и нужно. Первым параметром передаем количество дней до дня рождения, а дальше перечисляем список значений. Первое и второе значение (завтра и послезавтра) задаем равными 1 (или ИСТИНА, но это дольше писать)

В результате функция ВЫБОР() вернет 1 (равносильно ИСТИНА) только если дата дня рождения отстоит от СЕГОДНЯ() на один или два дня вперед. Для всех остальных случаев будет возвращено значение ошибки #ЗНАЧ!, но для условного форматирования важны только единички.

Поделитесь опытом

Приходится ли Вам отслеживать важные даты в Excel? Каким образом Вы организовали этот процесс? Поделитесь своим опытом и идеями, оставив комментарий к этой статье.

Файл-пример

Как обычно, Вы можете "поиграть" с файлом-примером прямо на странице со статьей. Попробуйте изменить дату в ячейке B2 и обратите внимание на изменение цвета заливки ячеек. Скачать файл-пример Вы можете, нажав на небольшую иконку Excel в нижнем правом углу окна.

Другие статьи по теме: