Сортировка дней рождений
Во многих компаниях принято отмечать дни рождения сотрудников или поздравлять с ДР клиентов, предлагая скидки именинникам. И тут же возникает проблема: если в компании ощутимо большое количество сотрудников/клиентов, то сортировка их списка по дате рождения дает не совсем желательный результат:
Поскольку Microsoft Excel воспринимает любую дату как числовой код (количество дней с начала века до текущей даты), то сортировка идет, на самом деле, по этому коду. Таким образом мы получаем на выходе список по порядку «старые-молодые», но из него совсем не видно у кого в каком месяце день рождения.
Способ 1. Функция ТЕКСТ и дополнительный столбец
Для решения задачи нам потребуется еще один вспомогательный столбец с функцией ТЕКСТ (TEXT), которая умеет представлять числа и даты в заданном формате:
В нашем случае формат «ММ ДД» означает, что нужно отобразить из всей даты только двузначные номер месяца и день (без года).
Теперь простая сортировка по вспомогательному столбцу (вкладка Данные — Сортировка) как раз и даст нужный результат:
Вуаля!
Для полноты ощущений можно добавить к отсортированному списку еще автоматическое отчеркивание месяцев друг от друга горизонтальной линией. Для этого выделите весь список (кроме шапки) и выберите на вкладке Главная команду Условное форматирование — Создать правило (Home — Conditional formatting — Create Rule). В открывшемся окне выберите нижний тип правила Использовать формулу для определения форматируемых ячеек и введите следующую формулу:
Эта формула проверяет номер месяца для каждой строки, и если он отличается от номера месяца в следующей строке, то срабатывает условное форматирование. Нажмите кнопку Формат и включите нижнюю границу ячейки на вкладке Границы (Borders). Также не забудьте убрать лишние знаки доллара в формуле, т.к. нам нужно закрепить в ней только столбцы.
После нажатия на ОК к нашей таблице добавятся симпатичные разделительные линии по месяцам:
Способ 2. Сводная таблица с группировкой
Этот способ вместо дополнительных столбцов и функций задействует супермощный инструмент Excel — сводные таблицы. Выделите ваш список и на вкладке Вставка (Insert) нажмите кнопку Сводная таблица (Pivot Table), а затем ОК в появившемся окне. Перетащите поле с датой в область строк — Excel выведет на листе список всех дат в первом столбце:
Щелкните правой кнопкой мыши по любой дате и выберите команду Группировать (Group). В следующем окне убедитесь, что выбран шаг группировки Месяцы и нажмите ОК. Получим список всех месяцев, которые есть в исходной таблице:
Теперь, чтобы увидеть у кого именно из сотрудников день рождения приходятся на конкретный месяц, перетащите поле с именем сотрудника и бросьте его под поле с датой в область строк:
Задача решена, и не нужно возиться с формулами. Единственный минус этого варианта в том, что Excel не умеет группировать столбцы с пустыми ячейками, т.е. вы должны иметь полностью заполненный датами столбец в исходной таблице.
Ну, вот — можно идти собирать деньги с коллег на очередной тортик или закупать подарки для любимых клиентов 
Ссылки по теме
- Хитрости сортировки
- Как Excel на самом деле работает с датами
- Как сделать всплывающий календарь в ячейке листа Excel
Каждая транзакция проводиться в какое-то время или период, а потом привязывается к конкретной дате. В Excel дата – это преобразованные целые числа. То есть каждая дата имеет свое целое число, например, 01.01.1900 – это число 1, а 02.01.1900 – это число 2 и т.д. Определение годов, месяцев и дней – это ничто иное как соответствующий тип форматирования для очередных числовых значений. По этой причине даже простейшие операции с датами выполняемые в Excel (например, сортировка) оказываются весьма проблематичными.
Сортировка в Excel по дате и месяцу
На примере таблицы изображенной ниже на рисунке мы покажем, в какой способ ее подготовить для комфортной сортировки дат. Наш список транзакций с датами охватывает диапазон ячеек B1:C15.
В первую очередь создадим столбец, в котором пронумеруем все транзакции. Считайте это действие как хороший тон для любого вида сортировки. Ведь в такие случаи при неправильной перетасовки данных мы всегда имеем возможность вернуться в первоначальный вид таблицы. Для этого:
- В ячейке A1 введите название столбца «№п/п», а ячейку A2 введите число 1. После чего наведите курсор мышки на маркер курсора клавиатуры расположенный в нижнем правом углу квадратика. В результате курсор изменит свой внешний вид с указательной стрелочки на крестик. Не отводя курсора с маркера нажмите на клавишу CTRL на клавиатуре в результате чего возле указателя-крестика появиться значок плюсик «+».
- Теперь одновременно удерживая клавишу CTRL на клавиатуре и левую клавишу мышки протяните маркер вдоль целого столбца таблицы (до ячейки A15).
В результате чего столбец автоматически заполниться последовательностью номеров транзакций от 1 до 14.
Полезный совет! В Excel большинство задач имеют несколько решений. Для автоматического нормирования столбцов в Excel можно воспользоваться правой кнопкой мышки. Для этого достаточно только лишь навести курсор на маркер курсора клавиатуры (в ячейке A2) и удерживая только правую кнопку мышки провести маркер вдоль столбца. После того как отпустить правую клавишу мышки, автоматически появиться контекстное меню из, которого нужно выбрать опцию «Заполнить». И столбец автоматически заполниться последовательностью номеров, аналогично первому способу автозаполнения.
Теперь пришла очередь для создания вспомогательных столбцов, которые существенно помогут качественно сортировать список транзакций. Необходимо выполнить несколько простых действий:
- Ячейки D1, E1, F1 заполните названиями заголовков: «Год», «Месяц», «День».
- Соответственно каждому столбцу введите под заголовками соответствующие функции и скопируйте их вдоль каждого столбца:
- D1: =ГОД(B2);
- E1: =МЕСЯЦ(B2);
- F1: =ДЕНЬ(B2).
В итоге мы должны получить следующий результат:
Наша таблица готова и предоставляет возможность выполнять многостороннюю сортировку транзакций по датам. Сначала выполним тестовую сортировку, чтобы убедиться в том, как все работает.
Допустим мы хотим выполнить сортировку дат транзакций по месяцам. В данном случае порядок дней и годов – не имеют значения. Для этого просто перейдите на любую ячейку столбца «Месяц» (E) и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка по возрастанию».
Теперь, чтобы сбросить сортировку и привести данные таблицы в изначальный вид перейдите на любую ячейку столбца «№п/п» (A) и вы снова выберите тот же инструмент «Сортировка по возрастанию».
Как сделать сортировку дат по нескольким условиям в Excel
А теперь можно приступать к сложной сортировки дат по нескольким условиям. Задание следующее – транзакции должны быть отсортированы в следующем порядком:
- Года по возрастанию.
- Месяцы в период определенных лет – по убыванию.
- Дни в периоды определенных месяцев – по убыванию.
Способ реализации поставленной задачи:
- Перейдите на любую ячейку исходной таблицы и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
- В появившемся диалоговом окне настраиваемой сортировки убедитесь в том, что галочкой отмечена опция «Мои данные содержат заголовки». После чего во всех выпадающих списках выберите следующие значения: в секции «Столбец» – «Год», в секции «Сортировка» – «Значения», а в секции «Порядок» – «По возрастанию».
- На жмите на кнопку добавить уровень. И на втором условии заполните его критериями соответственно: 1 – «Месяц», 2 – «Значение», 3 – «По убыванию».
- Нажмите на кнопку «Копировать уровень» для создания третьего условия сортирования транзакций по датам. В третьем уровне изменяем только первый критерий на значение «День». И нажмите на кнопку ОК в данном диалоговом окне.
В результате мы выполнили сложную сортировку дат по нескольким условиям:
Для сортирования значений таблицы в формате «Дата» Excel предоставляет опции в выпадающих списках такие как «От старых к новым» и «От новых к старым». Но на практике при работе с большими объемами данных результат не всегда оправдывает ожидания. Так как для программы Excel даты – это целые числа безопаснее и эффективнее сортировать их описанным методом в данной статье.
Сортировка по дате в Excel.
Смотрите также (дд.мм.гггг) нужно отсортироватьДопустим, у вас код 32, РТС вернет ЛОЖЬ, а специальной вставке (надо эту ячейку, потом (СОРТИРОВКА И ФИЛЬТР (Enterом вставлять или на букве столбцаDATEDIFФункция формат, т.д. Читайте Ивановой. затем – «Сотрудники». функцию «Сортировка от
текст. Так записаннуюСортировка по дате в по дню и
таблица имеет две код 160 если бы в поставить галочку). Может выделите ваш столбец — Настраиваемая сортировка копировать название месяца с датами, выделится(РАЗНДАТ), чтобы найтиDATE об этом вКак сделать и как Все эти данные старых к новым» дату Excel воспринимает Excel месяцу (в порядке колонки. Имя первая
Владимир ячейке А2 стояла в этом дело? с датами, сециальная
— Порядок - и вставлять). столбец ( даты). количество дней («d»)
(ДАТА) принимает три статье «Как скопировать
работать со сводной будут размещены в — вверху столбца как текст, иполучится, если даты наступления дня рождения). колонка и дата: Всё через умножение дата — тогдаBootleg80 вставка — умножить. Настраиваемый список) уВариант 2 Нажать на пиктограмму
между прошедшим и
аргумента: год, месяц в Excel». таблицей, читайте в одном столбце. Получилась будут ранние даты,
не будет сортировать будут написаны вOrmandiore рождения вторая колонка. получилось.
ИСТИНА: Спасибо!А вот теперь меня почему-то неПредлагаю воспользоваться функцией «Сортировка и Фильтр»Выбрать следующим днём рождения.
и день.Чтобы в Excel рассчитать статье «Принцип составления такая таблица на к низу столбца по дате. правильном формате. В: в двух соседних
Колонка даты имеетwebleywebleyА почему я сортируйте… срабатывает.
«Фильтр» и смотреть «от Я к=DATEIF(B2,C2,»d»)Мы использовали функцию количество дней до сводной таблицы Excel» новом листе. будут новые даты,Преобразование текста в дату ячейках может быть столбцах получить день формат ДД. ММ.
: У меня столбец: Скопируйте один из выделил в первомBootleg80
Bootleg80 каждый месяц отдельно. А» или «от=РАЗНДАТ(B2;C2;»d»)DATEDIF вашего дня рождения,
тут.Теперь можно сортировать данные
т.д. Excel установлен формат «Дата», и месяц от ГГГГ, установленный в В получился через этих пробелов, выделите файле »даты» ,: А могли бы: Коллеги,
Ирина Я к А».Урок подготовлен для Вас
(РАЗНДАТ), чтобы найти следуйте инструкции ниже:Фильтр по дате в в любом порядкеПолучится так.описано в статье а значение в даты рождения (формулы меню формат ячейки.
excel-office.ru
Как рассчитать в Excel количество дней до дня рождения
умножение, а столбец столбец в котором зашел в формат подробней объяснить?
- Помогите, пожалуйста!: все Выстроит по датам командой сайта office-guru.ru
- количество полных летВыделите ячейку Excel. – сортировка отЕще один способ «Преобразовать текст в
- этой ячейке написаны =день (А2) иЧто делаем. Создаем С — нет… их надо удалить, — тыкнул ДАТА
Я в ячейку
Нужно отсортировать данныеТатьяна теплостанская
- и месяцам иИсточник: http://www.excel-easy.com/examples/days-until-birthday.html («у») между датойA2Можно в таблице
- старых к новым,сортировки дат в дату Excel». в другом формате, =месяц (А2) соответственно, третью колонку копированиемПодскажите как можно отсортировать
- нажмите Ctrl+H, вставьте — а Вы написал 1, нажал
- по дате.: Задайте Формат ячейки годам. Но будетПеревел: Антон Андронов рождения и сегодняшними введите дату поставить фильтр. Тогда от новых кExcelВсё, установили формат например – текстом.
- где А2 - второй колонки. Форматируем людей в excel этот пробел из говорите, что это правой кнопкой -
Если выбираю сортировку
столбца - "Дата"
неудобно из-за того,Автор: Антон Андронов
днём.
рождения.
можно будет фильтровать
office-guru.ru
Как в экселе отсортировать список людей по дням рождения?
старым, т. д.– использовать сводную в ячейках всех Тогда Excel увидит ячейка с датой третью колонку в по датам рождения, буфера в поле не дата а скопировать от А до типа 17.03 или что по годам
Имеется список около 700ФормулаВыделите ячейку даты по месяцам,Например, мы отсортируем таблицу. дат «Дата». Теперь в ячейке текст, рождения) ) формат ДД. ММ. но нужно что Найти, поле Заменить текст?далее выделил столбец Я — он 17 мар. Введите выстраиваются. Это Вы,
человек и вручную
РАЗНДАТ(A2;B2;»у»)B2 дням. Нужно убрать людей, у которыхУ нас такая можно их сортировать. а не дату.выделить таблицу. меню Выделяем колонки от бы учитывался только оставьте пустым иХочу разобраться… с »датами», нажал сортирует все 1
в этот столбец
скорее всего, знаете. становится не реальновозвращает значение 32.
и введите функцию галочки у слов
дата рождения была таблица с перечнемО других способах Визуально можно проверить данные — сортировка третьей к первой. месяц но не нажмите Заменить всеBootleg80 специальная ставка умножить потом двойки , даты: день. месяц,Вариант 1
перемещать строчки сЕсли 32 полных годаTODAY «Выделить все» и в прошлом году. сотрудников и датами сортировки смотрите в формат даты так. — сортировать по Так и не год. Заранее спасибо
Сортировка по дате
Владимир: И еще вопрос:
— и ничего…
а мне нужно а в другой
Можно добавить первую людьми в нужном прошло с момента(СЕГОДНЯ), чтобы вернуть поставить галочки у Нажимаем на стрелку рождения их детей, статье «Сортировка в
А теперь посмотрим на
по столбцу месяц
иначе. Затем нажимаем за ответ!: Нет там пробелов.Как убрать лишний
Могли бы сделать с 11 января столбец — год, колонку (столбец) и порядке. А сам вашего дня рождения
сегодняшнюю дату. нужных дней, месяцев,
у строки «Названия внуков, родителей, т.д. Excel».
дату в ячейке по возрастанию, затем кнопку сортировки (а/яАлексей матевосов (alexm)
Как Вам посоветовали, пробле в ММВБ на моей файле по 30 декабря.
Тогда всё отлично вбить месяцы, затем эксель сортирует только (другими словами, вы
Самое трудное в расчёте т.д. строк». В появившемся
Выделяем таблицу с шапкой.Сортировка по дате в А18. по столбцу день со стрелкой) в: Решить поставленную задачу
сделать спец. вставку / РТС -
— я былФайл прикладываю! сортируется- и по отсортировать по этой дату целиком, а уже отметили свой количества дней доПро другие возможности фильтра диалоговом окне выбираем
На закладке «Вставка»
Excel.Здесь написана дата (значение)
по возрастанию панели инструментов. Готово. можно по-разному, средствами через умножение, так а то он бы очень признателен…Надеюсь на помощь!
дате в месяце колонке, но это мне нужно чтобы 32-ой день рождения), вашего рождения – читайте в статье функцию «Фильтр по в разделе «Таблицы»
Выделяем столбец. Нажимаем текстом, п.ч. дописалиАнатолий евсюков Третью колонку можете самого Excel или и делайте. не воспринимает цифрыВладимирwebley и по месяцам. минут 15 надо
сначала стояли январские значит в следующий найти следующий день «Фильтр в Excel». дате». Из появившегося нажимаем на кнопку
на кнопку «Сортировка букву «г», хотя: В соседней ячейке удалить. VBA.webley — а думает,
: Сортировка по дате: потому что этоКстати, предусмотренная для
затратить. Полностью месяцы именинники, потом февральские, день рождения вы рождения. Формула нижеВ Excel есть
planetaexcel.ru
Как отсортировать данные по дням рождения
списка выбираем – функции «Сводная таблица». и фильтр» на формат самой ячейки записать функцию Числовое- VladВ данном случае: Вот тут я
что это тест прошла успешно. не дата, а ячеек типа Дата печатать не придется,
мартовские и так будете отмечать 32 выполняет этот трюк. функция «Заполнить», которая «В прошлом году».
Появившееся поле заполняем закладке «Главная», или стоит – «Дата». значение даты. Скопировать: СОРТИРОВАТЬ ДАТУ ПО целесообразно решить средствами не соглашусь -webleywebley
текст в Excel настраиваемая так как будет далее… + 1 ==DATE(YEAR(A2)+DATEDIF(A2,B2,»y»)+1,MONTH(A2),DAY(A2)) помогает быстро заполнитьПолучились такие данные. В так. вызываем контекстное меню Дата в ячейке данную ячейку по МЕСЯЦУ Excel, так как
есть там пробелы,: функция =ЕЧИСЛО(A2) в: умножить — это
Excel 2007 сортировка дат дней рождения
Введите в любую сортировка (в т. появляться месяц поАлексt 33 летие.=ДАТА(ГОД(A2)+РАЗНДАТ(A2;B2;»y»)+1;МЕСЯЦ(A2);ДЕНЬ(A2))
столбцы, ячейки, пустые прошлом году датаСначала ставим галочку у правой мышкой. Выбираем А18 расположена в всем ФИО. ОтсортироватьИмеется таблица с фамилиями задача будет использована причем разные: ММВТ вашем первоначальном файле
такой пункт в ячейку 1, скопируйте ч. по месяцам) первым набранным буквам: Щелкните ЛК мышиДалее используем функциюПояснение:
строки, скопировать формулы, рождения только у слов «Дата рождения», из появившегося окна ячейке слева, как по данному столбцу и датами рождения
всего раз.
На чтение 6 мин Опубликовано 15.01.2021
Даты добавляются в любой бизнес-отчет, будь это бухгалтерский документ или таблица другого характера. Для быстрого изучения информации необходимо сортировать строки и столбцы. Благодаря инструментам сортировки пользователи могут увидеть нужную информацию в большой базе данных, не прокручивая ее и не просматривая каждую строку. Экономия времени – серьезная причина изучить сортировку и фильтрацию по датам Microsoft Excel. Выясним, как правильно работать с сортировочными инструментами и типами фильтрации.
Содержание
- Сортировка с одним условием (по возрастанию/убыванию)
- Сортировка с несколькими условиями (по годам/месяцам/дням)
- Автоматический или настаиваемый фильтр по дате
- Как убрать фильтры с таблицы
- Заключение
Сортировка с одним условием (по возрастанию/убыванию)
Этот метод сортировки по дате – самый простой. Для него требуются только опции панели инструментов. Составим таблицу, где важную роль играют даты. Следует указать полное обозначение – день, месяц и год. Сортировка с наибольшей вероятностью сработает правильно, если информация указана в таком формате.
- Выбираем одну из заполненных ячеек и переходим на вкладку «Главная». Справа вверху появится кнопка «Сортировка и фильтр» – нажмите ее, чтобы открыть меню.
- В появившемся списке есть два пункта сортировки по датам. Строки можно разместить в порядке от старых к новым или от новых к старым. Представим, что нам требуется порядок от ранних дат к поздним. Выбираем пункт «От старых к новым».
- Теперь даты в таблице расположены в порядке от 2017-го года до 2019-го года.
- Попробуем поставить записи в обратном порядке. Снова выбираем любую ячейку из диапазона и запускаем «Сортировку от новых к старым» из опций сортировки. Строки меняются местами – сейчас объемы продаж за 2019 год находятся выше.
Сортировка с несколькими условиями (по годам/месяцам/дням)
В некоторых таблицах необходимо рассортировать данные по дате, но не целиком, а по отдельным дням. Например, годы в результате должны идти по возрастанию, а некоторые месяцы и дни – по убыванию. С помощью функций Excel можно рассортировать данные в таком порядке, не затрачивая слишком много времени на перемещение строк вручную. Воспользуемся функцией настраиваемой сортировки.
Обратите внимание! Чтобы расположить информацию в соответствии со сложными условиями сортировки, необходимо вынести годы, месяцы и дни в отдельные столбцы. Приходится так поступать, потому что настраиваемый сортировщик учитывает заголовки столбцов как критерии. Пример деления даты на компоненты перед сложной сортировкой:
- Нужно выбрать любую ячейку в таблице и открыть меню сортировки на вкладке «Данные». Кликаем по пункту «Настраиваемая сортировка» — откроется диалоговое окно.
- Проверяем, что в графе «Мои данные содержит заголовки» стоит галочка. Эта опция исключает шапку таблицы из сортировки и помогает предотвратить ошибки. Далее заполним первый уровень сортировки: согласно условиям, нужно выбрать столбец «Год» и порядок «По возрастанию».
- Нажимаем кнопку «Добавить уровень», чтобы установить сортировку по дополнительным условиям. В итоге диалоговое окно должно содержать три уровня для годов, месяцев и дней. Выбираем в списках нужные значения и нажимаем «ОК».
- Строки встали в нужном порядке. Дни и месяцы идут по убыванию в рамках года, потому что сортировка по годам была первична. Об этом говорят надписи «Затем» на уровнях настраиваемой сортировки, относящихся к месяцам и дням.
Сортировка по столбцам в таблицах с датами невозможна. Программа блокирует эту опцию в разделе «Параметры» диалогового окна настраиваемой сортировки.
Автоматический или настаиваемый фильтр по дате
Microsoft Excel способен не только сортировать данные по присвоенным им датам, но и фильтровать строки по годам, дням и месяцам. Выясним, как правильно пользоваться автоматическими фильтрами по дате.
- Форматируем диапазон ячеек с помощью опции «Форматировать как таблицу». Эта кнопка находится на вкладке «Главная», нужно нажать ее после выделения таблицы. Выбираем любой стиль из меню и жмем «ОК» в диалоговом окне – ячейки окрасятся в указанные цвета, а в шапке появятся кнопки для настройки фильтров.
- Нажимаем кнопку со стрелкой в столбце с датами. Программа уже считала формат данных, поэтому в меню находятся фильтры по дате.
- Выбираем любой фильтр для проверки. К примеру, в таблице есть данные за 2020-й год, поэтому выберем опцию «В прошлом году». Кликаем по этому пункту списка.
- На странице остается одна строка, соответствующая условиям. Количество данных может быть любым, если это соотносится с выбранным фильтром.
- Воспользуемся более сложным фильтром. В конце списка есть пункт «Настраиваемый фильтр» — кликните по нему, чтобы задать условия. Необходимо выбрать тип фильтрации и указать рамки значений.
- Все строки, кроме оказавшихся в диапазоне значений фильтра, исчезают с экрана.
Как убрать фильтры с таблицы
Отменять действия – не лучший вариант, это может привести к потере важных изменений. Для отключения фильтров и сортировочных инструментов стоит пользоваться теми же инструментами. Взглянем на предыдущую отфильтрованную таблицу и попробуем удалить из нее фильтр. Нажмите кнопку с воронкой в заголовке столбца. В списке находится пункт «Удалить фильтр с <название>». Кликните по нему, и фильтрация будет отменена – исчезнувшие ячейки вернутся на лист.
Если таблица отформатирована, можно убрать из нее кнопки для настройки фильтров:
- Выберите любую ячейку в диапазоне и откройте меню «Сортировка и фильтр» на вкладке «Главная».
- Кликните по пункту «Фильтр» – он подсвечен оранжевым.
Кнопки со стрелками пропадут, но останется цветовая тема. Заменить ее можно через раздел «Форматировать как таблицу». Меню фильтров не появится снова после применения новых настроек.
Заключение
Сортировка таблиц Microsoft Excel по годам, месяцам и дням проводится в простом и усложненном формате. Второй вариант хорош тем, что строки можно поставить в нужном порядке, и инструмент будет ориентироваться на несколько условий. Стоит также помнить о фильтрах для ячеек в формате «Дата». Фильтрация позволяет увидеть данные в нужном пользователю временном диапазоне.
Оцените качество статьи. Нам важно ваше мнение:
Если даты на листе упорядочены неправильно, вы можете их отсортировать. Например, можно выполнить сортировку от наиболее ранних к наиболее поздним.
Вот как можно отсортировать даты:
-
Перетащите указатель вниз по столбцу, чтобы выделить нужные даты.
-
На вкладке Главная щелкните стрелку на кнопке Сортировка и фильтр и выберите команду Сортировка от старых к новым или Сортировка от новых к старым.
Примечание: Если вы не получили ожидаемых результатов, то, возможно, в столбце содержатся даты, сохраненные в текстовом формате, а не в формате даты. В статье Преобразование дат из текстового формата в формат даты поясняется, что нужно сделать в этом случае.