Сортировка дней рождений
Во многих компаниях принято отмечать дни рождения сотрудников или поздравлять с ДР клиентов, предлагая скидки именинникам. И тут же возникает проблема: если в компании ощутимо большое количество сотрудников/клиентов, то сортировка их списка по дате рождения дает не совсем желательный результат:
Поскольку Microsoft Excel воспринимает любую дату как числовой код (количество дней с начала века до текущей даты), то сортировка идет, на самом деле, по этому коду. Таким образом мы получаем на выходе список по порядку "старые-молодые", но из него совсем не видно у кого в каком месяце день рождения.
Способ 1. Функция ТЕКСТ и дополнительный столбец
Для решения задачи нам потребуется еще один вспомогательный столбец с функцией ТЕКСТ (TEXT), которая умеет представлять числа и даты в заданном формате:
В нашем случае формат "ММ ДД" означает, что нужно отобразить из всей даты только двузначные номер месяца и день (без года).
Теперь простая сортировка по вспомогательному столбцу (вкладка Данные - Сортировка) как раз и даст нужный результат:
Вуаля!
Для полноты ощущений можно добавить к отсортированному списку еще автоматическое отчеркивание месяцев друг от друга горизонтальной линией. Для этого выделите весь список (кроме шапки) и выберите на вкладке Главная команду Условное форматирование - Создать правило (Home - Conditional formatting - Create Rule). В открывшемся окне выберите нижний тип правила Использовать формулу для определения форматируемых ячеек и введите следующую формулу:
Эта формула проверяет номер месяца для каждой строки, и если он отличается от номера месяца в следующей строке, то срабатывает условное форматирование. Нажмите кнопку Формат и включите нижнюю границу ячейки на вкладке Границы (Borders). Также не забудьте убрать лишние знаки доллара в формуле, т.к. нам нужно закрепить в ней только столбцы.
После нажатия на ОК к нашей таблице добавятся симпатичные разделительные линии по месяцам:
Способ 2. Сводная таблица с группировкой
Этот способ вместо дополнительных столбцов и функций задействует супермощный инструмент Excel - сводные таблицы. Выделите ваш список и на вкладке Вставка (Insert) нажмите кнопку Сводная таблица (Pivot Table), а затем ОК в появившемся окне. Перетащите поле с датой в область строк - Excel выведет на листе список всех дат в первом столбце:
Щелкните правой кнопкой мыши по любой дате и выберите команду Группировать (Group). В следующем окне убедитесь, что выбран шаг группировки Месяцы и нажмите ОК. Получим список всех месяцев, которые есть в исходной таблице:
Теперь, чтобы увидеть у кого именно из сотрудников день рождения приходятся на конкретный месяц, перетащите поле с именем сотрудника и бросьте его под поле с датой в область строк:
Задача решена, и не нужно возиться с формулами. Единственный минус этого варианта в том, что Excel не умеет группировать столбцы с пустыми ячейками, т.е. вы должны иметь полностью заполненный датами столбец в исходной таблице.
Ну, вот - можно идти собирать деньги с коллег на очередной тортик или закупать подарки для любимых клиентов :)
А подскажите как сделать выделение границ в условном форматировании не простой? а жирной линией.
PS: Excel 2013
Но вариант нашелся методом от противного.
Сначала закрашиваем все жирными линиями а в условном форматировании ставим не "<>" а "=" и тогда у нас только жирные линии будут разделять даты по месяцам.
В сводной появляются три поля вместо одной даты, ставим Месяц как самую левую колонку и видим все дни рождения отсортированные по месяцам независимо от года (поле Год ставим правее для информации)
Есть таблица с повторяющимися датами (около 300 значений) в течение одного (будущего) месяца. Планируя будущий месяц, даты заполняю постепенно (т.е. есть пробелы в столбце). Дата указывается в формате: дд.мм.гггг чч:мм
Мне необходимо подсчитать сколько раз повторяется каждая дата (рабочий день) в месяце, что бы равномерно распределить будущую нагрузку на день.
Вопрос: при помощи какой функции можно подсчитать и вывести в отдельный столбец (лист) данные с 1 по 28-31 день по которым можно будет построить график для наглядности. Как задать сравнение для каждого дня месяца (дд.мм) с возможностью автоматической смены номера месяца и соответственно дней в месяце. Благодарю за помощь.