Как разделить дату рождения по столбцам в excel
Даты добавляются в любой бизнес-отчет, будь это бухгалтерский документ или таблица другого характера. Для быстрого изучения информации необходимо сортировать строки и столбцы. Благодаря инструментам сортировки пользователи могут увидеть нужную информацию в большой базе данных, не прокручивая ее и не просматривая каждую строку. Экономия времени – серьезная причина изучить сортировку и фильтрацию по датам Microsoft Excel. Выясним, как правильно работать с сортировочными инструментами и типами фильтрации.
Сортировка с одним условием (по возрастанию/убыванию)
Этот метод сортировки по дате – самый простой. Для него требуются только опции панели инструментов. Составим таблицу, где важную роль играют даты. Следует указать полное обозначение – день, месяц и год. Сортировка с наибольшей вероятностью сработает правильно, если информация указана в таком формате.
- Выбираем одну из заполненных ячеек и переходим на вкладку «Главная». Справа вверху появится кнопка «Сортировка и фильтр» – нажмите ее, чтобы открыть меню.
- В появившемся списке есть два пункта сортировки по датам. Строки можно разместить в порядке от старых к новым или от новых к старым. Представим, что нам требуется порядок от ранних дат к поздним. Выбираем пункт «От старых к новым».
- Теперь даты в таблице расположены в порядке от 2017-го года до 2019-го года.
- Попробуем поставить записи в обратном порядке. Снова выбираем любую ячейку из диапазона и запускаем «Сортировку от новых к старым» из опций сортировки. Строки меняются местами – сейчас объемы продаж за 2019 год находятся выше.
Сортировка с несколькими условиями (по годам/месяцам/дням)
В некоторых таблицах необходимо рассортировать данные по дате, но не целиком, а по отдельным дням. Например, годы в результате должны идти по возрастанию, а некоторые месяцы и дни – по убыванию. С помощью функций Excel можно рассортировать данные в таком порядке, не затрачивая слишком много времени на перемещение строк вручную. Воспользуемся функцией настраиваемой сортировки.
Обратите внимание! Чтобы расположить информацию в соответствии со сложными условиями сортировки, необходимо вынести годы, месяцы и дни в отдельные столбцы. Приходится так поступать, потому что настраиваемый сортировщик учитывает заголовки столбцов как критерии. Пример деления даты на компоненты перед сложной сортировкой:
Сортировка по столбцам в таблицах с датами невозможна. Программа блокирует эту опцию в разделе «Параметры» диалогового окна настраиваемой сортировки.
Автоматический или настаиваемый фильтр по дате
Microsoft Excel способен не только сортировать данные по присвоенным им датам, но и фильтровать строки по годам, дням и месяцам. Выясним, как правильно пользоваться автоматическими фильтрами по дате.
Как убрать фильтры с таблицы
15
Если таблица отформатирована, можно убрать из нее кнопки для настройки фильтров:
Кнопки со стрелками пропадут, но останется цветовая тема. Заменить ее можно через раздел «Форматировать как таблицу». Меню фильтров не появится снова после применения новых настроек.
17
Заключение
Сортировка таблиц Microsoft Excel по годам, месяцам и дням проводится в простом и усложненном формате. Второй вариант хорош тем, что строки можно поставить в нужном порядке, и инструмент будет ориентироваться на несколько условий. Стоит также помнить о фильтрах для ячеек в формате «Дата». Фильтрация позволяет увидеть данные в нужном пользователю временном диапазоне.
При обработке данных даты в Excel вам часто нужно будет сделать запрос на разделение даты, месяца, года на три разных столбца или перенести дату, месяц, год рождения с числа на букву. Чтобы заняться любовью Этот мост очень прост, мы будем использовать основные функции Excel, такие как функция ГОД, функция ДЕНЬ и объединение функций времени для преобразования времени в текст. В следующей статье показано, как разделить день, месяц и год на 3 отдельных столбца и преобразовать время в буквы.
- Как преобразовать последовательность чисел в даты в Excel
- Как посчитать количество дней в Excel
- Как разделить тысячи запятыми в Excel
1. Разделите день, месяц и год в Excel на 3 отдельных столбца.
Вводим даты в таблицу Excel. Чтобы выделить дату в отдельный столбец в столбце «Дата», в ячейке C2 введите формулу = ДЕНЬ (B2) и нажмите Enter.
Тогда мы получим результат в виде отдельной даты. Перетащите результаты первой ячейки в оставшиеся ячейки в столбце «Дата», чтобы отобразить результаты.
В столбце Месяц введите формулу = МЕСЯЦ (B2) и также нажмите Enter, чтобы выполнить формулу.
Результат в первой ячейке также вытягивает остальные ячейки, чтобы отобразить полный столбец «Месяц».
Затем в первой ячейке столбца введите формулу = ГОД (B2) и нажмите Enter, чтобы продолжить.
И результаты также будут разделены в столбце Год, как показано.
2. Преобразуйте дату, месяц и год из числа в Excel.
В результате мы получим количество дней прописью, как показано ниже.
Операция разделения времени в Excel на отдельные столбцы не представляет сложности для пользователей. Вам просто нужно ввести формулу ДЕНЬ, МЕСЯЦ и ГОД для каждого столбца времени отдельно.
Во многих компаниях принято отмечать дни рождения сотрудников или поздравлять с ДР клиентов, предлагая скидки именинникам. И тут же возникает проблема: если в компании ощутимо большое количество сотрудников/клиентов, то сортировка их списка по дате рождения дает не совсем желательный результат:
Поскольку Microsoft Excel воспринимает любую дату как числовой код (количество дней с начала века до текущей даты), то сортировка идет, на самом деле, по этому коду. Таким образом мы получаем на выходе список по порядку "старые-молодые", но из него совсем не видно у кого в каком месяце день рождения.
Способ 1. Функция ТЕКСТ и дополнительный столбец
Для решения задачи нам потребуется еще один вспомогательный столбец с функцией ТЕКСТ (TEXT) , которая умеет представлять числа и даты в заданном формате:
В нашем случае формат "ММ ДД" означает, что нужно отобразить из всей даты только двузначные номер месяца и день (без года).
Теперь простая сортировка по вспомогательному столбцу (вкладка Данные - Сортировка) как раз и даст нужный результат:
Для полноты ощущений можно добавить к отсортированному списку еще автоматическое отчеркивание месяцев друг от друга горизонтальной линией. Для этого выделите весь список (кроме шапки) и выберите на вкладке Главная команду Условное форматирование - Создать правило (Home - Conditional formatting - Create Rule) . В открывшемся окне выберите нижний тип правила Использовать формулу для определения форматируемых ячеек и введите следующую формулу:
После нажатия на ОК к нашей таблице добавятся симпатичные разделительные линии по месяцам:
Способ 2. Сводная таблица с группировкой
Этот способ вместо дополнительных столбцов и функций задействует супермощный инструмент Excel - сводные таблицы. Выделите ваш список и на вкладке Вставка (Insert) нажмите кнопку Сводная таблица (Pivot Table) , а затем ОК в появившемся окне. Перетащите поле с датой в область строк - Excel выведет на листе список всех дат в первом столбце:
Щелкните правой кнопкой мыши по любой дате и выберите команду Группировать (Group) . В следующем окне убедитесь, что выбран шаг группировки Месяцы и нажмите ОК. Получим список всех месяцев, которые есть в исходной таблице:
Теперь, чтобы увидеть у кого именно из сотрудников день рождения приходятся на конкретный месяц, перетащите поле с именем сотрудника и бросьте его под поле с датой в область строк:
Задача решена, и не нужно возиться с формулами. Единственный минус этого варианта в том, что Excel не умеет группировать столбцы с пустыми ячейками, т.е. вы должны иметь полностью заполненный датами столбец в исходной таблице.
Ну, вот - можно идти собирать деньги с коллег на очередной тортик или закупать подарки для любимых клиентов :)
Разделить дату на три столбца - день, месяц и год с формулами
Разделить дату на три столбца - день, месяц и год с текстом в столбец
Быстро разделить дату на день, месяц, год с помощью Kutools for Excel
Преобразование даты только в месяц и год или только месяц и день с помощью Kutools for Excel
Разделить дату на три столбца - день, месяц и год с формулами
В Excel вы можете использовать приведенные ниже простые формулы для извлечения дня, месяца или года только из ячейки даты.
1. Выберите ячейку, например, C2, введите эту формулу = ДЕНЬ (A2) , Нажмите Enter, В день опорной ячейки извлекается.
2. И перейдите к следующей ячейке, например D2, введите эту формулу = МЕСЯЦ (A2) , Нажмите Enter чтобы извлечь месяц только из ссылочной ячейки.
3. Щелкните следующую ячейку, E2, введите эту формулу = ГОД (A2) , и нажмите Enter key извлекается год ссылочной ячейки.
4. Затем выберите ячейки дня, месяца и года, в данном случае C2: E2, и перетащите маркер автозаполнения вниз, чтобы заполнить формулы в нужные ячейки. Смотрите скриншот:
Наконечник: В приведенных выше формулах A2 - это ячейка даты, которую вы хотите разделить на день, месяц и год.
Быстрое разделение одной ячейки на столбцы или строки на основе разделителя
Разделить дату на три столбца: день, месяц и год с текстом в столбец
В Excel вы также можете использовать функцию Text to Column, чтобы разделить дату столбца на три столбца с днем, месяцем и годом.
1. Выберите столбец даты, исключая заголовок, если он не имеет заголовка, и щелкните Данные > Текст в столбцы. Смотрите скриншот:
2. Проверьте разграниченный вариант в Мастер преобразования текста в столбцы - шаг 1 из 3, см. снимок экрана:
3. Нажмите Далее> идти на Шаг 2 из 3, и проверьте Другое вариант только в Разделители раздел и введите разделитель / в следующее текстовое поле. Смотрите скриншот:
4. Продолжайте нажимать Далее> , чтобы перейти к Шаг 3 из 3, и щелкните, чтобы выбрать ячейку для вывода разделенных данных.
5. Нажмите Завершить, а столбец даты разделен на три столбца с днем, месяцем и годом.
Быстро разделить дату на день, месяц, год с помощью Kutools for Excel
Если у вас есть Kutools for Excel, всего за 3 шага, вы можете быстро разделить ячейки даты на день, месяц и год в отдельной ячейке с его Разделить клетки утилита.
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите даты, которые хотите разделить, нажмите Kutools > Текст > Разделить клетки.
2. в Разделить клетки диалог, проверьте Разделить на столбцы флажок, затем установите флажок Другое in Разделить на раздел, тип / в следующее текстовое поле.
3. Нажмите Ok и выберите ячейку для размещения разделенных данных, затем щелкните OK.
Теперь даты разделены на день, месяц и год.
Чаевые： Если вы хотите разделить дату в порядке дня, месяца, года, вы можете сначала отформатировать ячейки как дд / мм / гггг, а затем применить утилиту «Разделить ячейки».
Преобразование даты только в месяц и год или только месяц и день с помощью Kutools for Excel
В другом случае, если вы хотите преобразовать дату только в месяц и год или в месяц и год, как показано на скриншотах ниже, вы можете использовать Kutools for ExcelАвтора Применить Дата Fорматинг утилита для быстрого выполнения этой работы.
Преобразование даты только в месяц и день Преобразование даты только в год и месяц
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите дату, необходимую для преобразования, и нажмите Kutools > Формат > Применить форматирование даты. Смотрите скриншот:
2. Затем в Применить форматирование даты диалоговое окно, выберите 03/2001 чтобы преобразовать дату только в месяц и год, и выберите 3/14 чтобы преобразовать дату только в месяц и день, и вы можете просмотреть результаты в предварительный просмотр панель. Смотрите скриншот:
3. Нажмите Ok or Применить, и все даты преобразуются в нужный вам формат.
Читайте также: