Как отсортировать данные в Excel

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

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

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

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

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

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

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

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

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

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

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

1. Выберите ячейку в столбце, содержащем ключ сортировки.
2. Выбрать Главная.
3. Выбрать Сортировать Фильтр открыть раскрывающееся меню параметров сортировки.
4. Выберите, как вы хотите отсортировать данные. Выберите восходящий или нисходящий порядок.

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

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

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

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

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

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

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

1. Выделите диапазон ячеек для сортировки. В этом примере выбраны ячейки с A2 по E12.
2. Выбрать Главная.
3. Выбрать Сортировать Фильтр открыть выпадающий список.
4. Выбрать Выборочная сортировка открыть диалоговое окно сортировки.
5. Поставьте чек рядом с Мои данные имеют заголовки.
6. Под заголовком столбца выберите Сортировать по стрелка вниз и выберите имя из раскрывающегося списка, чтобы сначала отсортировать данные по столбцу Имя.
7. Под заголовком «Сортировать по» оставьте настройку как Значения ячейки. Сортировка основана на фактических данных в таблице.
8. Под заголовком «Заказ» выберите стрелку вниз и выберите От Z до A сортировать данные имени в порядке убывания.
9. Выбрать Добавить уровень добавить второй вариант сортировки.
10. Под заголовком столбца выберите Затем стрелкой вниз и выбрать Возраст сортировать записи с повторяющимися именами по столбцу Возраст.
11. Под заголовком Заказ выберите От самого большого до самого маленького из раскрывающегося списка, чтобы отсортировать данные возраста в порядке убывания.
12. Выбрать Хорошо закрыть диалоговое окно и отсортировать данные.

В результате определения второго ключа сортировки, показанного в примере ниже, две записи с одинаковыми значениями для поля «Имя» сортируются в порядке убывания с использованием поля «Возраст». Это приводит к записи для студента Уилсона Дж., 21 год, до записи для Уилсона П., 19 лет.

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

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

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

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

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

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

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

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

Диалоговое окно «Быстрая сортировка и сортировка»

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

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

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

1. Выделите диапазон ячеек для сортировки. Чтобы следовать приведенному выше примеру, выделите ячейки от G2 до K7.
2. Выбрать Главная.
3. Выбрать Сортировать Фильтр открыть выпадающий список.
4. Выбрать Выборочная сортировка открыть диалоговое окно сортировки.
5. Под заголовком столбца выберите Сортировать по стрелка вниз и выберите Заимствованные сначала отсортировать данные по заимствованной дате.
6. Под заголовком «Сортировать по» выберите Значения ячейки. Сортировка основана на фактических данных в таблице.
7. Под заголовком Порядок сортировки выберите Старейшая Новейшая из выпадающего списка.
8. Выбрать Хорошо в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.

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

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

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

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

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

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

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

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

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

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

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

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

1. Выделите диапазон ячеек для сортировки.
2. Выбрать Главная.
3. Выбрать Сортировать Фильтр открыть выпадающий список.
4. Выбрать Выборочная сортировка открыть диалоговое окно сортировки.
5. Под заголовком столбца выберите Начало месяца из раскрывающегося списка, чтобы отсортировать данные по месяцам года.
6. Под заголовком «Сортировать по» выберите Значения ячейки. Сортировка основана на фактических данных в таблице.
7. Под заголовком Заказ выберите стрелка вниз рядом с опцией по умолчанию от А до Я, чтобы открыть раскрывающееся меню.
8. выберите Пользовательский список открыть диалоговое окно «Пользовательские списки».
9. В левом окне диалогового окна выберите Январь февраль март апрель.
10. Выбрать Хорошо чтобы подтвердить выбор и вернуться к диалоговому окну «Сортировка».
11. Выбранный список (январь, февраль, март, апрель) отображается под заголовком Заказа.
12.  Выбрать Хорошо закрыть диалоговое окно и отсортировать данные по месяцам года.

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

1. Выберите стрелку вниз рядом с отображаемым списком, например Январь февраль март апрель открыть раскрывающееся меню.
2. В меню выберите параметр пользовательского списка, который отображается в порядке убывания, например Декабрь, ноябрь, октябрь, сентябрь.
3. Нажмите Хорошо сортировать данные в порядке убывания, используя пользовательский список.

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

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

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

Настройте порядок столбцов

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

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

Как только сортировка завершена, добавленную строку чисел можно легко удалить.

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

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

В этом случае, как показано на рисунке выше, в столбцы были добавлены числа для подготовки рабочего листа к переупорядочению столбцов, чтобы столбец «Программа» находился слева сначала, а затем «Начало месяца», «Имя», «Возраст» и «Идентификатор студента».

Вот как изменить порядок столбцов:

1. Вставьте пустую строку над строкой, содержащей имена полей.
2. В этой новой строке введите следующие цифры слева направо, начиная со столбца A: 5, 3, 4, 1, 2.
3. Выделите диапазон для сортировки. В этом примере выделите A2 до E13.
4. Выбрать Главная.
5. Выбрать Сортировать Фильтр открыть выпадающий список.
6. Выбрать Выборочная сортировка открыть диалоговое окно сортировки.
7. Выбрать Параметры чтобы открыть диалоговое окно «Параметры сортировки».
8. В разделе «Ориентация» выберите Сортировать слева направо сортировать порядок столбцов слева направо на листе.
9. Выбрать Хорошо закрыть диалоговое окно «Параметры сортировки».
10. При изменении ориентации заголовок столбца в диалоговом окне «Сортировка» меняется на «Строка».
11. Выберите Сортировать по стрелка вниз и выбрать Ряд 2. Это строка, содержащая пользовательские номера.
12. Под заголовком «Сортировать по» выберите Значения ячейки.
13. Под заголовком Заказ выберите Наименьший к самому большому из выпадающего списка, чтобы отсортировать числа в строке 2 в порядке возрастания.
14. Выбрать Хорошо закрыть диалоговое окно и отсортировать столбцы слева направо по номерам в строке 2.
15. Порядок столбцов начинается с «Программа», затем «Начало месяца», «Имя», «Возраст» и «Идентификатор студента».

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

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

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

Например, чтобы использовать цвет шрифта для изменения порядка столбцов:

1. Выберите каждое имя поля и измените цвет шрифта для каждого. Например, измените Program на красный, Month Start на зеленый, Name на синий, Age на оранжевый, и Student ID на фиолетовый.
2. В диалоговом окне «Сортировка» выберите Сортировать по и выбрать Ряд 2.
3. Под сортировкой по заголовку выберите Цвет шрифта.
4. Под заголовком Порядок вручную установите порядок цветов имен полей в соответствии с желаемым порядком столбцов.
5. После сортировки сбросьте цвет шрифта для каждого имени поля.