Как в excel отсортировать по дате рождения

Как сделать сортировку в excel по дате

Видео: Excel 2007. Фильтрация и сортировка ячеек

Есть несколько способов сортировки дат в Excel.

Здесь рассмотрим, как сделать сортировку в Excel по дате рождения, по месяцам, т.д.

В Excel даты хранятся в виде порядковых номеров, п. э. отсортированные даты будут стоять в списке сначала по году.

Например, все даты 2010 года. А среди этих дат будет идти сортировка по месяцам, затем дням. Затем, все даты 2011 года, т.д.

Для примера и сравнения, мы скопировали даты столбца А в столбец В.

В столбце B мы отсортировали даты по условию «Сортировка от старых к новым».

Здесь произошла сортировка по годам, а месяца идут не подряд.

Но, если нам важнее отсортировать даты по месяцам, например – дни рождения сотрудников или отсортировать даты в пределах одного года, периода, тогда применим формулу.

Сортировка дат в Excel по месяцам.

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

Выделяем ячейку С8. На закладке «Формулы» в разделе «Библиотека функций» в функциях «Текстовые» выбираем функцию «Текст».

Появившееся диалоговое окно заполняем так.Нажимаем «ОК». В ячейке С8 появилась такая формула.

Обратите внимание. Если будете писать формулу вручную, то формат дат нужно писать в кавычках – «ММ.ДД».

Копируем формулу вниз по столбцу.

Теперь отсортируем даты функцией «Сортировка от A до Я» — это сортировка по возрастанию.

Функция «Сортировка и фильтр» находится на закладке «Главная» в разделе «Редактирование».

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

(Пока оценок нет)

Источник: j4web.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: www.planetaexcel.ru

Сортировка по дате в excel — от меньшего к большему

Microsoft Office Excel часто используется в небольших фирмах для формирования бухгалтерской отчетности. Такой документ обязательно содержит даты переводов со счетов и суммы. Иногда необходимо отсортировать данные, чтобы понять, когда самый большой объем выплат для предприятия с целью планирования расходов на будущие периоды. Сегодня разберемся, как работает в excel сортировка по дате.

Обычная сортировка

Чтобы в excel сделать сортировку по дате, необходимо провести некоторую подготовительную работу, поскольку редактор все даты распознает в виде специального кода, который представляет собой числовое значение. Если напрямую воспользоваться одноименным инструментом, то сортировка не работает. Рассмотрим небольшую таблицу с днями перевода средств со счетов предприятия.

Для начала дополним таблицу несколькими столбцами.

1. Добавим нумерацию строк, чтобы можно было в любой момент вернуться к исходному массиву. Быстрее всего это сделать при помощи маркера автозаполнения. Ставите единицу в первой строке и тянете специальный квадратик в углу ячейки вниз.

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

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

1. В итоге получилось следующее:

Если результат не удовлетворил, то можно вернуться к исходной таблице при помощи первого столбца, воспользовавшись тем же порядком действий.

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

Сложная сортировка

Задать несколько условий для сортировки можно через отдельную опцию из выпадающего списка.

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

Отсортируем данные исходной таблицы по дате и месяцу. Окно настройки будет выглядеть следующим образом:

В результате сложной сортировки получился следующий массив:

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Источник: mir-tehnologiy.ru

Как сделать сортировку по дате в Excel в порядке возрастания

Каждая транзакция проводиться в какое-то время или период, а потом привязывается к конкретной дате. В Excel дата – это преобразованные целые числа. То есть каждая дата имеет свое целое число, например, 01.01.1900 – это число 1, а 02.01.1900 – это число 2 и т.д. Определение годов, месяцев и дней – это ничто иное как соответствующий тип форматирования для очередных числовых значений. По этой причине даже простейшие операции с датами выполняемые в Excel (например, сортировка) оказываются весьма проблематичными.

Сортировка в Excel по дате и месяцу

На примере таблицы изображенной ниже на рисунке мы покажем, в какой способ ее подготовить для комфортной сортировки дат. Наш список транзакций с датами охватывает диапазон ячеек B1:C15.

В первую очередь создадим столбец, в котором пронумеруем все транзакции. Считайте это действие как хороший тон для любого вида сортировки. Ведь в такие случаи при неправильной перетасовки данных мы всегда имеем возможность вернуться в первоначальный вид таблицы. Для этого:

1. В ячейке A1 введите название столбца «№п/п», а ячейку A2 введите число 1. После чего наведите курсор мышки на маркер курсора клавиатуры расположенный в нижнем правом углу квадратика. В результате курсор изменит свой внешний вид с указательной стрелочки на крестик. Не отводя курсора с маркера нажмите на клавишу CTRL на клавиатуре в результате чего возле указателя-крестика появиться значок плюсик «+».
2. Теперь одновременно удерживая клавишу CTRL на клавиатуре и левую клавишу мышки протяните маркер вдоль целого столбца таблицы (до ячейки A15).

В результате чего столбец автоматически заполниться последовательностью номеров транзакций от 1 до 14.

Полезный совет! В Excel большинство задач имеют несколько решений. Для автоматического нормирования столбцов в Excel можно воспользоваться правой кнопкой мышки. Для этого достаточно только лишь навести курсор на маркер курсора клавиатуры (в ячейке A2) и удерживая только правую кнопку мышки провести маркер вдоль столбца. После того как отпустить правую клавишу мышки, автоматически появиться контекстное меню из, которого нужно выбрать опцию «Заполнить». И столбец автоматически заполниться последовательностью номеров, аналогично первому способу автозаполнения.

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

1. Ячейки D1, E1, F1 заполните названиями заголовков: «Год», «Месяц», «День».
2. Соответственно каждому столбцу введите под заголовками соответствующие функции и скопируйте их вдоль каждого столбца:

• D1: =ГОД(B2);
• E1: =МЕСЯЦ(B2);
• F1: =ДЕНЬ(B2).

В итоге мы должны получить следующий результат:

Наша таблица готова и предоставляет возможность выполнять многостороннюю сортировку транзакций по датам. Сначала выполним тестовую сортировку, чтобы убедиться в том, как все работает.

Допустим мы хотим выполнить сортировку дат транзакций по месяцам. В данном случае порядок дней и годов – не имеют значения. Для этого просто перейдите на любую ячейку столбца «Месяц» (E) и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка по возрастанию».

Теперь, чтобы сбросить сортировку и привести данные таблицы в изначальный вид перейдите на любую ячейку столбца «№п/п» (A) и вы снова выберите тот же инструмент «Сортировка по возрастанию».

Как сделать сортировку дат по нескольким условиям в Excel

А теперь можно приступать к сложной сортировки дат по нескольким условиям. Задание следующее – транзакции должны быть отсортированы в следующем порядком:

1. Года по возрастанию.
2. Месяцы в период определенных лет – по убыванию.
3. Дни в периоды определенных месяцев – по убыванию.

Способ реализации поставленной задачи:

1. Перейдите на любую ячейку исходной таблицы и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».
2. В появившемся диалоговом окне настраиваемой сортировки убедитесь в том, что галочкой отмечена опция «Мои данные содержат заголовки». После чего во всех выпадающих списках выберите следующие значения: в секции «Столбец» – «Год», в секции «Сортировка» – «Значения», а в секции «Порядок» – «По возрастанию».
3. На жмите на кнопку добавить уровень. И на втором условии заполните его критериями соответственно: 1 – «Месяц», 2 – «Значение», 3 – «По убыванию».
4. Нажмите на кнопку «Копировать уровень» для создания третьего условия сортирования транзакций по датам. В третьем уровне изменяем только первый критерий на значение «День». И нажмите на кнопку ОК в данном диалоговом окне.

В результате мы выполнили сложную сортировку дат по нескольким условиям:

Для сортирования значений таблицы в формате «Дата» Excel предоставляет опции в выпадающих списках такие как «От старых к новым» и «От новых к старым». Но на практике при работе с большими объемами данных результат не всегда оправдывает ожидания. Так как для программы Excel даты – это целые числа безопаснее и эффективнее сортировать их описанным методом в данной статье.

Источник: exceltable.com

Как в excel отсортировать по дате рождения

Возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число диапазоне от 1 (январь) до 12 (декабрь).

Дата_в_числовом_формате — это дата, месяц которой необходимо найти. Даты должны вводиться с использованием функции ДАТА или как результат других формул и функций. Например, следует использовать ДАТА(2008,5,23) для 23-го мая 2008 года. Проблемы могут возникнуть, если даты вводятся как текст.

9. DA_Ugly , 08.11.2006 22:06

Akina Нашел “корявый” способ: заменить все “.” на “,” потом отсортировать. Желаемый результат получается, но через раз . Записал макрос: Sub Макрос1() Columns(“A:A”).Select Selection.Replace What:=”.”, Replacement:=”,”, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Он нефига не работает Добавление от 08.11.2006 22:16: цитата: Akina: Так же как и месяц вытаскивали. сказать имена функций, или самостоятельно догадаешься? Спасибо, догадался. Но хочется решить без добавления столбцов.

10. Vic_K , 08.11.2006 22:27

DA_Ugly Да-а-а, даже слова кончились допустим в столбце А у вас даты, в столбец В вы записываете формулу =День(Ах) в столбец С =Месяц(Ах) в столбец D = Год(Ах), где х-номер строки. Далее растягиваете формулы на весь диапазон и получаете свои даты разделёнными на три столбца, затем выделяете этот диавазон и нажимаете копировать перемещаетесть ещё правее и делаете правый клик и выбираете пункт меню “Специальная вставка” ставите точку напротив слова “Значения”. т.е. вы вместо формул скопируете значения ячеек и этот диапазон сортируете стандартной сортировкой, там как раз по трём столбцам можно. Он нефига не работает И это правильно, т.к. строки сортируются по другим правилам нежели числа числа 1,2 и 11 отсортируются 1,2,11, а строки 1,11,2, объяснять почему не буду, уж не обессудьте. Далее на кой было лепить макрос, если в екселе есть нормальная функция заменить, доступна в меню “Правка” Источник: forum.ixbt.com Сортировка в Excel Сортировка в Excel применяется для визуализации данных и их упорядочивания, что существенно облегчает восприятие информации, представленной в табличной форме. Необходимость в этой функции возникает при работе с бухгалтерскими ведомостями, списками складских запасов и строительными сметами. Часто проблемой может стать вопрос расположения чисел от большего к меньшему или наоборот. На самом деле критерии для упорядочивания информации в Экселе бывают разными: дата, время, цвет ячеек или тип шрифта. Чаще всего при изучении приемов работы с электронными таблицами рассматриваются примеры, где представлены списки сотрудников или товаров, так как сортировать по алфавиту на практике приходится очень часто. Рассортировать в программе можно по двум различным параметрам. Как отсортировать по алфавиту Поскольку в большинстве таблиц числовые параметры привязаны к какому-либо тексту – фамилии или названию товара то удобно расположить по алфавиту. Рассмотрим особенности работы с этим инструментом в редакторе Еxcel на примере таблицы «Содержание питательных веществ во фруктах и овощах». Для того чтобы отсортировать названия овощей и фруктов по алфавиту необходимо выделить первый столбец, кликнув мышью по его заголовку. Далее требуется перейти на панель инструментов «Редактирование» и открыть вкладку «Главная», на которой расположена специальная кнопка «Сортировка и фильтр». Для упорядочивания названий в первом столбце от А до Я или наоборот, достаточно выбрать соответствующую команду сортировки в выпадающем меню. Если в таблицу Еxcel будут добавлены данные о других продуктах сработает автоматическая сортировка. Как в Еxcel отсортировать по возрастанию значений Простое распределение по возрастанию в программе Эксель осуществляется аналогичным образом, что и по алфавиту. После выделения нужного столбца в верхней части главного окна на панели задач «Главная» в разделе «редактирование» выбирается кнопка «Сортировка и фильтр», имеющая дополнительное меню. В открывшемся списке необходимо выбрать подходящий вариант. Когда данные в таблице Эксель нужно упорядочить от большего к меньшему, их следует отсортировать по убыванию, в противном случае выбирается пункт «Сортировка по возрастанию». Если диапазон данных состоит из двух и более столбцов, при сортировке на экране должно появиться диалоговое окно для выбора дальнейших действий. Если пользователю необходимо отсортировать по возрастанию данные во всей таблице, то следует указать «автоматически расширить выделенный диапазон», во втором случае данные будут упорядочены только в выбранном столбце. Как сделать сортировку по дате В таблицах, где в хронологическом порядке отражаются определенные операции, их часто требуется отсортировать по дате проведения. Поскольку дата это сочетание целых чисел, представленных определенным образом, для правильного осуществления сортировки необходимо, чтобы был выбран соответствующий формат ячеек. Если формат указан неправильно, программа не сможет распознать значения дат, поэтому упорядочить их не получится. Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий: Выделить любую ячейку в столбце с датами, которые требуется упорядочить. Во вкладке «Главная» найти кнопку «Сортировка и фильтр», навести на нее курсор и выбрать одно из предложенных действий в выпадающем списке. Это может быть распределение от новых к старым, когда в начале списка будут находится наиболее поздние даты и привязанные к ним значения, либо наоборот от старых к новым. Сортировка по цвету ячейки и по шрифту В случае если определенный диапазон табличных данных отформатирован с использованием шрифта различных цветов или заливки, пользователь может рассортировать строки по цвету, в который окрашена часть ячеек. Упорядочить данные можно также по набору значков, при создании которых применялось условное форматирование. В любом случае это можно сделать следующим образом: Курсором мыши нужно выбрать одну из ячеек с данными в нужном столбце. На вкладке «Главная» в функциональной группе «Редактирование» следует найти кнопку «Сортировка и фильтрация» и выбрать в дополнительном меню команду «Настраиваемая сортировка». В открывшемся окне сначала указать столбец, в котором нужно упорядочить данные, а потом тип сортировки. Это может быть цвет ячейки, цвет шрифта или значок условного форматирования. В зависимости от выбранного вида сортировки в группе порядок необходимо отметить нужный значок либо оттенок заливки или шрифта. Последний параметр, который нужно указать – это последовательность расположения (сверху или снизу). Поскольку определенный порядок значков или цветов в редакторе не предусмотрен, его нужно создать самостоятельно. Для этого следует необходимо нажать кнопку «Добавить уровень» и затем повторить указанные шаги для каждого цвета или значка отдельно, исключая те, которые не нужно включать в сортировку, Сортировка в Excel по нескольким столбцам Если возникла необходимость отсортировать данные в редакторе Эксель по двум или более столбцам, следует так же, как и в предыдущем случае выбрать диапазон данных и открыть окно «Настраиваемая сортировка». Далее в первой группе следует отметить заголовок столбца, данные в котором нужно упорядочить в первую очередь. Вторая группа остается без изменений, а в третьей необходимо указать желаемый тип сортировки. Для указания критериев сортировки для второго столбца нужно добавить еще один уровень. В итоге количество уровней будет соответствовать числу столбцов, по которым необходимо отсортировать данные. Динамическая сортировка таблицы в MS Excel При выполнении некоторых задач в Excel, требуется установить автоматическую сортировку, которая предполагает наличие формул. В зависимости от типа данных в используемом диапазоне динамическая сортировка может быть задана тремя способами: Если информация в ячейках столбца представлена числами, используются функции НАИМЕНЬШИЙ и СТРОКА. Первая находит наименьший элемент из массива, а вторая определяет порядковый номер строки. Таким образом формируется последовательность. Формула записывается следующим образом: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Когда в ячейках содержаться текст, первая формула не сработает. Для сортировки в этом случае целесообразно применять формулу: =СЧЁТЕСЛИ(A:A;” Как убрать сортировку в Excel Для отмены единичной сортировки диапазона данных достаточно нажать кнопку «Отменить ввод» в левом углу экрана. Бывает так, что изменения в файле были сохранены и отменить действие невозможно. Как убрать сортировку в этом случае? Если после сложных манипуляций с таблицей, ее нужно будет вернуть в первоначальный вид, перед выполнением сортировки следует специально создать дополнительный столбец, в котором будет отражена нумерация строк. После завершения сложного анализа числовых и текстовых данных, представленных в таблице, чтобы отменить все проделанные операции достаточно будет установить сортировку по созданному столбцу. Источник: compone.ru Отправьте друзьям: 22.03.2020 Отобразить скрытые листы в excel 22.03.2020 Excel выбрать уникальные значения Добавить комментарий Отменить ответ Сохранить моё имя, email и адрес сайта в этом браузере для последующих моих комментариев. Adblock detector