Как отсортировать данные в 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. Выберите ОК , чтобы закрыть диалоговое окно и отсортировать данные.

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Выделите диапазон ячеек для сортировки. Чтобы следовать приведенному выше примеру, выделите ячейки от G2 до K7.
2. Выберите Домой .
3. Выберите Сортировка и фильтр , чтобы открыть раскрывающийся список.
4. Выберите Пользовательская сортировка , чтобы открыть диалоговое окно «Сортировка».
5. Под заголовком столбца выберите стрелку вниз Сортировать по и выберите Заем , чтобы сначала отсортировать данные по заимствованной дате.
6. Под заголовком «Сортировать по» выберите Значения ячеек . Сортировка основана на фактических данных в таблице.
7. Под заголовком «Порядок сортировки» в раскрывающемся списке выберите «strong> Oldest to Newest» .
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. После сортировки сбросьте цвет шрифта для каждого имени поля.