Сортировка дней рождений

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

sort-birthdates1.png

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

Способ 1. Функция ТЕКСТ и дополнительный столбец

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

sort-birthdates2.png

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

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

sort-birthdates3.png

Вуаля!

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

sort-birthdates4.png

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

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

sort-birthdates5.png

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

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

sort-birthdates6.png

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

sort-birthdates7.png

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

sort-birthdates8.png

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

Ну, вот - можно идти собирать деньги с коллег на очередной тортик или закупать подарки для любимых клиентов :)

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


06.11.2015 09:47:26
Всем, доброго дня.

А подскажите как сделать выделение границ в условном форматировании не простой? а жирной линией.

PS: Excel 2013
06.11.2015 10:16:50
Там где формат - можно выбрать любую линии любого цвета и жирноты :)
10.11.2015 11:21:40
В 2013 Excel там нет жирных линий в меню условного форматирования.
Но вариант нашелся методом от противного.
Сначала закрашиваем все жирными линиями а в условном форматировании ставим не "<>" а "=" и тогда у нас только жирные линии будут разделять даты по месяцам.
06.11.2015 15:23:05
Способ группировки дат по Дням, Месяцем и Годам в стандартной сводной таблице – также достаточно простой и быстрый способ сортировки по месяцам

В сводной появляются три поля вместо одной даты, ставим Месяц как самую левую колонку и видим все дни рождения отсортированные по месяцам независимо от года (поле Год ставим правее для информации)
07.11.2015 22:42:39
Спасибо за прием, Николай. Но за Колю обидно - для всех остальных сотрудников указаны полные имена, а последний Николай - один из самых возрастных сотрудников - указан как "Коля". Дискриминация, однако;)
11.11.2015 12:00:30
Николай в списке тоже есть :)
28.05.2016 08:11:29
Приветствую. Помогите пожалуйста в решении следующей проблемы. Просмотрел много уроков, но путаницы в голове стало по функциям и их применению стало еще больше.
Есть таблица с повторяющимися датами (около 300 значений)  в течение одного (будущего) месяца. Планируя будущий месяц, даты заполняю постепенно (т.е. есть пробелы в столбце). Дата указывается в формате: дд.мм.гггг чч:мм
Мне необходимо подсчитать сколько раз повторяется каждая дата (рабочий день) в месяце, что бы равномерно распределить будущую нагрузку на день.

Вопрос: при помощи какой функции можно подсчитать и вывести в отдельный столбец (лист) данные с 1 по 28-31 день  по которым можно будет построить график для наглядности. Как задать сравнение для каждого дня месяца (дд.мм) с возможностью автоматической смены номера месяца и соответственно дней в месяце.  Благодарю за помощь.
Подскажите, что может быть не так? Делаю как указано в первом способе с дополнительным столбцом, но в ячейке высвечиваются не цифры , а буквы ММ ДД.Делал все как рассказывали в видео.
Наверх