Учите КОМПЬЮТЕР вместе с нами!

%25D0%2597%25D0%25B0%25D1%2581%25D1%2582%25D0%25B0%25D0%25B2%25D0%25BA%25D0%25B0.jpegАктуальная задача для любого коллектива – не пропустить день рождения каждого сотрудника. Стандартная сортировка списка сотрудников в Excel по дате их рождения не решает поставленной задачи, поскольку такая сортировка выстраивает их в хронологическом порядке, с учетом не только дня и месяца, но и года рождения.В результате такой сортировки мы будем видеть, кто из сотрудников старше или моложе. А вот, чтобы сказать, у кого скоро будет день рождения – для этого придется вручную просматривать от начала до конца весь список, что неудобно.

Видеоурок по данной теме “Как отсортировать сотрудников по дням рождения в Excel” можно найти на нашем канале “Учите компьютер вместе с нами”.

Идеальная сортировка для такого случая – это упорядочивание людей по возрастанию их месяца рождения, а в пределах каждого месяца – по дате. Этим мы сейчас и займемся.Но прежде всего, какой вид будут иметь исходные данные? Пусть, в колонке “А” располагается порядковый номер сотрудника, в колонке “В” – его фамилия и инициалы, а в колонке “С” – дата рождения, как показано на рисунке ниже.%25D0%2594%25D0%25B0%25D0%25BD%25D0%25BD%25D1%258B%25D0%25B5%2B%25D0%25BE%2B%25D1%2581%25D0%25BE%25D1%2582%25D1%2580%25D1%2583%25D0%25B4%25D0%25BD%25D0%25B8%25D0%25BA%25D0%25B0%25D1%2585.jpgЧисленность сотрудников может быть любой, до нескольких сотен или даже десятков тысяч человек.Первоочередная наша задача – это выделить из даты рождения каждого сотрудника отдельно месяц и день. Для этого будем использовать вспомогательные колонки “D” и “E“.Введем в ячейку D2 формулу =МЕСЯЦ(C2), а в ячейку Е2 – формулу =ДЕНЬ(C2).Далее, выделяем ячейки D2:Е2 и за правый нижний угол растягиваем введенные формулы вниз, до конца списка. В результате мы получим таблицу вида:%25D0%259C%25D0%25B5%25D1%2581%25D1%258F%25D1%2586%2B%25D0%25B8%2B%25D0%25B4%25D0%25B5%25D0%25BD%25D1%258C%2B%25D1%2580%25D0%25BE%25D0%25B6%25D0%25B4%25D0%25B5%25D0%25BD%25D0%25B8%25D1%258F.jpgНу а теперь, заключительный штрих. Нам осталось выполнить сортировку всех сотрудников по двум последним колонкам.Для этого, выделяем область сортировки, протягивая левой кнопкой мыши по заголовкам колонок А:Е. Затем переходим во вкладку “Данные” и в секции “Сортировка и фильтр” нажимаем кнопку “Сортировка“.%25D0%259A%25D0%25BD%25D0%25BE%25D0%25BF%25D0%25BA%25D0%25B0%2B%25D0%25A1%25D0%25BE%25D1%2580%25D1%2582%25D0%25B8%25D1%2580%25D0%25BE%25D0%25B2%25D0%25BA%25D0%25B0.jpgВ появившемся окне “Сортировка” из выпадающего списка “Сортировать по” выбираем поле “Месяц“. Далее нажимаем кнопку “Добавить уровень” и в списке “Затем по” выбираем поле “День“.Все остальные параметры сортировки оставляем без изменений и нажимаем кнопку “ОК“. В результате, получим как раз то, что и требовалось:Как видим, теперь все сотрудники упорядочены по возрастанию месяца их рождения. Если в течение месяца день рождения наступает сразу у нескольких человек, тогда они упорядочены по возрастанию дней.Зная текущую дату, теперь не сложно определить, у кого из них скоро будет день рождения и в какой последовательности.

Как сообщалось на нашем сайте ранее, с помощью VBA в приложении Microsoft Excel можно имитировать эксперимент по выбрасыванию игральной кости.

10001104.11.2015 Скачать пример

Во многих компаниях принято отмечать дни рождения сотрудников или поздравлять с ДР клиентов, предлагая скидки именинникам. И тут же возникает проблема: если в компании ощутимо большое количество сотрудников/клиентов, то сортировка их списка по дате рождения дает не совсем желательный результат:

Поскольку Microsoft Excel воспринимает любую дату как числовой код (количество дней с начала века до текущей даты), то сортировка идет, на самом деле, по этому коду. Таким образом мы получаем на выходе список по порядку “старые-молодые”, но из него совсем не видно у кого в каком месяце день рождения.

Для решения задачи нам потребуется еще один вспомогательный столбец с функцией ТЕКСТ (TEXT), которая умеет представлять числа и даты в заданном формате:

В нашем случае формат “ММ ДД” означает, что нужно отобразить из всей даты только двузначные номер месяца и день (без года).

Теперь простая сортировка по вспомогательному столбцу (вкладка Данные – Сортировка) как раз и даст нужный результат:

Вуаля!

Для полноты ощущений можно добавить к отсортированному списку еще автоматическое отчеркивание месяцев друг от друга горизонтальной линией. Для этого выделите весь список (кроме шапки) и выберите на вкладке Главная команду Условное форматирование – Создать правило (Home – Conditional formatting – Create Rule). В открывшемся окне выберите нижний тип правила Использовать формулу для определения форматируемых ячеек и введите следующую формулу:

Эта формула проверяет номер месяца для каждой строки, и если он отличается от номера месяца в следующей строке, то срабатывает условное форматирование. Нажмите кнопку Формат и включите нижнюю границу ячейки на вкладке Границы (Borders). Также не забудьте убрать лишние знаки доллара в формуле, т.к. нам нужно закрепить в ней только столбцы.

После нажатия на ОК к нашей таблице добавятся симпатичные разделительные линии по месяцам:

Способ 2. Сводная таблица с группировкой

Этот способ вместо дополнительных столбцов и функций задействует супермощный инструмент Excel – сводные таблицы. Выделите ваш список и на вкладке Вставка (Insert) нажмите кнопку Сводная таблица (Pivot Table), а затем ОК в появившемся окне. Перетащите поле с датой в область строк – Excel выведет на листе список всех дат в первом столбце:

Щелкните правой кнопкой мыши по любой дате и выберите команду Группировать (Group). В следующем окне убедитесь, что выбран шаг группировки Месяцы и нажмите ОК. Получим список всех месяцев, которые есть в исходной таблице:

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

Ссылки по теме

Используемые источники:

  • https://videolections.blogspot.com/2019/10/excel_25.html
  • https://www.planetaexcel.ru/techniques/6/232/