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

Добавил пользователь Евгений Кузнецов
Обновлено: 04.10.2024

Иногда, при работе с таблицами в Excel возникает необходимость отсортировать данные, например, по алфавиту, по дате… Вследствие чего возникает вопрос, как быстро можно сортировать в Excel.

Как сортировать в Excel?

Сортировка данных в Excel

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

Расположен данный параметр в правой части документа на Панели управления (имеется ввиду Панель функций и кнопок в документе Excel).

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

Как сортировать в Excel по дате?

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

Excel сортировать по возрастанию

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

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

Сортировка дат в порядке возрастания или убывания

Самый простой способ сортировки данных в Microsoft Excel по дате — это сортировка в хронологическом (или обратном хронологическом) порядке. Это сортирует данные с самой ранней или самой поздней датой, начинающейся первой, в зависимости от ваших предпочтений.

Для этого откройте книгу Excel и выберите свои данные. Вы можете сделать это с помощью мыши или трекпада или щелкнув ячейку в диапазоне и нажав Ctrl + A на клавиатуре.

СВЯЗАННЫЙ: Как отсортировать значения в Microsoft Excel

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

Первая дата (под названием вашего столбца) будет самой ранней или последней датой в наборе.

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

Сортировка дат по месяцам или годам

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

Вы можете сделать это с помощью MONTH или YEAR функции. Это определяет номер месяца или года от даты и предоставляет его в отдельном столбце. Затем этот столбец можно отсортировать в порядке возрастания или убывания.

Чтобы ваш MONTH или DATE формула используется для всего набора данных, дважды щелкните маленький зеленый квадратный значок в правом нижнем углу ячейки. Это автоматически продублирует формулу для всех других строк, где она может найти данные слева от нее.

Аналогично, Microsoft Excel будет сортировать по последнему году или месяцу, если вы выберете самый большой или самый маленький вариант.

В зависимости от выбранных параметров, Excel немедленно отсортирует ваши данные, используя созданный вами столбец месяца или года.

Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.

Как отсортировать по алфавиту

Как в Еxcel отсортировать по возрастанию значений

Решение1

Если числовой столбец гарантировано не содержит повторяющихся значений, то задача решается легко:

Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью Сортированный список (ЧИСЛОвые значения) );
Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.

Однако, в реальных задачах числовой столбец может содержать повторы, а так как функция ВПР() в случае наличия повторов всегда выбирает только первое значение сверху (см. статью Функция ВПР() в MS EXCEL ), то этот подход не годится (названия Фруктов будут выведены неправильно).

Поэтому механизм сортировки придется реализовывать по другому.

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

В столбцах D и E разместим таблицу, которая будет динамически сортироваться,

В ячейке Е7 запишем зубодробительную формулу массива :

Данная формула сортирует столбец Объем продаж (динамический диапазон Продажи ) по убыванию. Пропуски в исходной таблице не допускаются. Количество строк в исходной таблице должно быть меньше 1000.

Разберем формулу подробнее:

Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(…))

В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)

Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.

Для наглядности, величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Как видно, сортировка работает.

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

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

Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий:

Еще одна формула массива (+дополнительный столбец). Задача1.1

Сортировку таблицы можно сделать с помощью другой, более простой формулы массива , но нам понадобится дополнительный (служебный) столбец D (см. файл примера лист Пример2 ):

В столбце F содержится отсортированный столбец В (объем продаж). Формула возвращает позицию значения объема продаж. Например, число 86 находится в 5-й строке таблицы.

Для повторов выражение ЕСЛИ(F8=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0) будет возвращать несколько значений: , т.е. число 74 находится в строках 2 и 8.

С помощью функции НАИБОЛЬШИЙ() сначала выводится 2, затем 8 (в разных строках).

Эта формула более наглядна, чем рассмотренная выше в начале статьи, но требует наличия дополнительного столбца.

Сортировка по цвету ячейки и по шрифту

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

Сортировка в Excel по нескольким столбцам

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

Сортировать в случайном порядке

И так тоже можно. Методика похожа на предыдущую

Это всё, что я хотел рассказать о том, как сделать сортировку в Excel . Пишите комментарии с вопросами, будем разбираться вместе. Если материал вам помог – поделитесь им с друзьями!

Динамическая сортировка таблицы в MS Excel

При выполнении некоторых задач в Excel, требуется установить автоматическую сортировку, которая предполагает наличие формул. В зависимости от типа данных в используемом диапазоне динамическая сортировка может быть задана тремя способами:

Если информация в ячейках столбца представлена числами, используются функции НАИМЕНЬШИЙ и СТРОКА. Первая находит наименьший элемент из массива, а вторая определяет порядковый номер строки. Таким образом формируется последовательность. Формула записывается следующим образом: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)).
Когда в ячейках содержаться текст, первая формула не сработает. Для сортировки в этом случае целесообразно применять формулу: =СЧЁТЕСЛИ(A:A;”

Сортировка промежуточных итогов

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

Это отличная функция, но как сделать, чтобы вверху были группы с наибольшей суммой? Ручной труд, конечно, в почете, но есть и другой вариант. Достаточно применить обычную сортировку к сгруппированным данным.

Внутреннее содержимое групп не изменится. Общий итог останется внизу.

Как убрать сортировку в Excel

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

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365, Excel Online и Excel для Mac.

Выберите данные для сортировки

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

В области связанных данных нет пустых строк или столбцов .
Пустые строки и столбцы находятся между областями связанных данных.

Excel определяет, есть ли в области данных имена полей, и исключает строку из записей для сортировки. Разрешение Excel выбирать диапазон для сортировки может быть рискованным, особенно с большими объемами данных, которые трудно проверить.

Чтобы убедиться, что выбраны правильные данные, выделите диапазон перед началом сортировки. Если один и тот же диапазон будет отсортирован несколько раз, лучшим подходом будет присвоить ему имя .

Ключ сортировки и порядок сортировки в Excel

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

Быстрая сортировка данных

Для быстрой сортировки выберите одну ячейку в столбце, содержащем ключ сортировки. Затем выберите способ сортировки данных. Вот как:

Выберите ячейку в столбце, содержащем ключ сортировки.
Выберите Дом .
Выберите Sort & Filter, чтобы открыть раскрывающееся меню параметров сортировки.
Выберите способ сортировки данных. Выберите восходящий или нисходящий порядок.

При использовании сортировки и фильтрации параметры порядка сортировки в раскрывающемся списке изменяются в зависимости от типа данных в выбранном диапазоне. Для текстовых данных возможны следующие варианты: Сортировка от А до Я и Сортировка от Z до А. Для числовых данных: Сортировка от наименьшего к наибольшему и сортировка от наименьшего к наименьшему.

Сортировать несколько столбцов данных в Excel

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

Пример сортировки по нескольким столбцам

В приведенном ниже примере данные в диапазоне от A2 до E12 сортируются по двум столбцам данных. Данные сначала сортируются по имени, а затем по возрасту.

Чтобы отсортировать несколько столбцов данных:

Первый ряд: заголовки столбцов или данные

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

Если первая строка не содержит заголовков, Excel использует букву столбца (например, Столбец D или Столбец E) в качестве вариантов в параметре Столбец диалогового окна Сортировка.

Сортировать данные по дате или времени в Excel

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

По возрастанию: от старейшего к новейшему.
По убыванию: от самого нового к старому.

Пример сортировки по дате

Чтобы выполнить быструю сортировку по дате в порядке возрастания, от самого старого до самого нового:

Если результаты сортировки по дате не получаются, как ожидалось, данные в столбце, содержащем ключ сортировки, могут содержать даты или время, сохраненные как текстовые данные, а не как числа (даты и время являются просто отформатированными числовыми данными).

Смешанные данные и быстрые сортировки

При использовании метода быстрой сортировки, если записи, содержащие текстовые и числовые данные, смешаны вместе, Excel сортирует числовые и текстовые данные отдельно, помещая записи с текстовыми данными в нижней части отсортированного списка.

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

Возможное Сортировка Предупреждение

Сортируйте все, что выглядит как число, как число.
Сортировка чисел и номеров хранится в виде текста отдельно.

Если вы выберете первый вариант, Excel попытается разместить текстовые данные в правильном месте результатов сортировки. Выберите второй вариант, и Excel поместит записи, содержащие текстовые данные, внизу результатов сортировки, как это происходит с быстрыми сортировками.

Сортировать данные по дням недели или по месяцам в Excel

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

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

На изображении выше были выполнены следующие шаги для сортировки выборки данных в диапазоне от А2 до Е12 по месяцам года:

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

Сортировать по строкам, чтобы изменить порядок столбцов в Excel

Как показано с предыдущими опциями сортировки, данные обычно сортируются с использованием заголовков столбцов или имен полей. Результатом является переупорядочение целых строк или записей данных. Менее известный и, следовательно, менее используемый параметр сортировки в Excel — это сортировка по строкам, что приводит к изменению порядка столбцов слева направо на рабочем листе.

One reason for sorting by row is to match the column order between different tables of data. With the columns in the same left to right order, it is easier to compare records or to copy and move data between the tables.

Customize the Column Order

Very seldom, however, is getting the columns in the correct order a straightforward task due to the limitations of the ascending and descending sort order options for values. Usually, it is necessary to use a custom sort order, and Excel includes options for sorting by cell or font color or by conditional formatting icons.

The easiest way of telling Excel the order of columns is to add a row above or below the data table containing numbers that indicate the order of columns from left to right. Sorting by rows then becomes a simple matter of sorting the columns smallest to largest by the row containing the numbers.

Sort by Rows Example

In the data sample used for this series on Excel sort options, the Student ID column has always been first on the left, followed by Name and then Age.

In this instance, as shown in the image above, numbers have been added to the columns to prepare the worksheet to reorder the columns so that the Program column is first on the left followed by Month Start, Name, Age, and Student ID.

Here’s how to change the column order:

In this new row, enter the following numbers left to right starting in column A: 5, 3, 4, 1, 2.
Highlight the range to be sorted. In this example, highlight A2 to E13.
Select Home.
Select Sort & Filter to open the drop-down list.
Select Custom Sort to open the Sort dialog box.
Select Options to open the Sort Options dialog box.
In the Orientation section, select Sort left to right to sort the order of columns left to right in the worksheet.
Select OK to close the Sort Options dialog box.
With the change in Orientation, the Column heading in the Sort dialog box changes to Row.
Select the Sort bydown arrow and choose Row 2. This is the row containing the custom numbers.
Under the Sort On heading, choose Cell Values.
Under the Order heading, choose Smallest to Largest from the drop-down list to sort the numbers in row 2 in ascending order.
Select OK to close the dialog box and sort the columns left to right by the numbers in row 2.
The order of columns begins with Program followed by Month Start, Name, Age, and Student ID.

Use Excel’s Custom Sort Options to Reorder Columns

While custom sorts are available in the Sort dialog box in Excel, these options are not easy to use when it comes to reordering columns in a worksheet. Options for creating a custom sort order available in the Sort dialog box are to sort the data by cell color, font color, and icon.

Unless each column has already had unique formatting applied, such as different font or cell colors, that formatting needs to be added to individual cells in the same row for each column to be reordered.

Читайте также: