Когда у вас есть большие объемы данных, они могут быть перегружены, если они неправильно отсортированы в вашей книге. Изучите различные методы сортировки данных в Excel, чтобы повысить продуктивность работы и упростить управление электронными таблицами.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Microsoft 365, Excel Online и Excel для Mac.
Выберите данные для сортировки
Прежде чем данные можно будет отсортировать, Excel должен знать точный диапазон, который необходимо отсортировать. Excel будет выбирать области связанных данных, если данные соответствуют следующим условиям:
- В области связанных данных нет пустых строк или столбцов.
- Пустые строки и столбцы находятся между областями связанных данных.
Excel определяет, есть ли в области данных имена полей, и исключает строку из сортируемых записей. Разрешение Excel выбирать диапазон для сортировки может быть рискованным, особенно с большими объемами данных, которые трудно проверить.
Чтобы убедиться, что выбраны правильные данные, выделите диапазон перед началом сортировки. Если один и тот же диапазон будет сортироваться неоднократно, лучше всего дать ему имя.
Ключ сортировки и порядок сортировки в Excel
Сортировка требует использования ключа сортировки и порядка сортировки. Ключ сортировки - это данные в столбце или столбцах, которые вы хотите отсортировать, и определяется заголовком столбца или именем поля. На изображении ниже возможные ключи сортировки: идентификатор учащегося, имя, возраст, программа и месяц начала.
Быстрая сортировка данных
Чтобы выполнить быструю сортировку, выберите одну ячейку в столбце, содержащем ключ сортировки. Затем выберите способ сортировки данных. Вот как:
- Выберите ячейку в столбце, содержащем ключ сортировки.
- Выбрать Главная.
- Выберите Сортировка и фильтрация, чтобы открыть раскрывающееся меню параметров сортировки.
- Выберите способ сортировки данных. Выберите восходящий или убывающий порядок.
При использовании сортировки и фильтра параметры порядка сортировки в раскрывающемся списке меняются в зависимости от типа данных в выбранном диапазоне. Для текстовых данных доступны следующие параметры: Сортировка от А до Я и Сортировка от Я до А. Для числовых данных доступны параметры Сортировка от меньшего к большему и Сортировка от большего к меньшему.
Сортировка нескольких столбцов данных в Excel
В дополнение к выполнению быстрой сортировки на основе одного столбца данных функция пользовательской сортировки Excel позволяет выполнять сортировку по нескольким столбцам, определяя несколько ключей сортировки. При сортировке по нескольким столбцам ключи сортировки определяются путем выбора заголовков столбцов в диалоговом окне «Сортировка».
Как и в случае с быстрой сортировкой, ключи сортировки определяются путем идентификации заголовков столбцов или имен полей в таблице, содержащей ключ сортировки.
Сортировка по нескольким столбцам Пример
В приведенном ниже примере данные в диапазоне от A2 до E12 сортируются по двум столбцам данных. Данные сначала сортируются по имени, а затем по возрасту.
Чтобы отсортировать несколько столбцов данных:
- Выделите диапазон ячеек для сортировки. В этом примере выбраны ячейки от A2 до E12.
- Выбрать Главная.
- Выберите Сортировка и фильтрация, чтобы открыть раскрывающийся список.
- Выберите Пользовательская сортировка, чтобы открыть диалоговое окно «Сортировка».
- Поставьте галочку рядом с В моих данных есть заголовки.
- Под заголовком Столбец выберите стрелку вниз Сортировать по и выберите Имя из раскрывающегося списка, чтобы сначала отсортировать данные. по столбцу Имя.
- Под заголовком «Сортировать по» оставьте значение Значения ячеек. Сортировка основана на фактических данных в таблице.
- Под заголовком «Порядок» щелкните стрелку вниз и выберите от Z до A, чтобы отсортировать данные имени в порядке убывания.
- Выберите Добавить уровень, чтобы добавить второй вариант сортировки.
- Под заголовком Столбец выберите Затем с помощью стрелки вниз и выберите Возраст, чтобы отсортировать записи с повторяющимися именами по столбцу Возраст.
- Под заголовком Порядок выберите От большего к меньшему из раскрывающегося списка, чтобы отсортировать данные о возрасте в порядке убывания.
- Выберите OK, чтобы закрыть диалоговое окно и отсортировать данные.
В результате определения второго ключа сортировки, показанного в примере ниже, две записи с одинаковыми значениями поля Имя сортируются в порядке убывания с использованием поля Возраст. Это приводит к рекорду для студента Уилсона Дж., 21 год, до рекорда Уилсона П., 19 лет.
Первая строка: заголовки столбцов или данные
Диапазон данных, выбранный для сортировки в приведенном выше примере, включал заголовки столбцов над первой строкой данных. Эта строка содержит данные, которые отличаются от данных в последующих строках. Excel определил, что первая строка содержит заголовки столбцов, и скорректировал доступные параметры в диалоговом окне «Сортировка», чтобы включить их.
Excel использует форматирование, чтобы определить, содержит ли строка заголовки столбцов. В приведенном выше примере заголовки столбцов выделены шрифтом, отличным от шрифта данных в остальных строках.
Если первая строка не содержит заголовков, Excel использует букву столбца (например, столбец D или столбец E) в качестве вариантов выбора в параметре «Столбец» диалогового окна «Сортировка».
Excel использует эту разницу, чтобы определить, является ли первая строка строкой заголовка. Если Excel допускает ошибку, диалоговое окно «Сортировка» содержит флажок «Мои данные имеют заголовки», который отменяет этот автоматический выбор.
Сортировка данных по дате или времени в Excel
В дополнение к сортировке текстовых данных по алфавиту или числам от большего к меньшему, параметры сортировки Excel включают сортировку значений даты. Доступные порядки сортировки, доступные для дат, включают:
- В порядке возрастания: от старых к новым.
- По убыванию: от новых к старым.
Диалоговое окно «Быстрая сортировка и сортировка»
Даты и время, которые отформатированы как числовые данные, такие как Дата заимствования в приведенном выше примере, используйте метод быстрой сортировки для сортировки по одному столбцу. Для сортировки, включающей несколько столбцов дат или времени, используйте диалоговое окно «Сортировка» таким же образом, как и для сортировки нескольких столбцов числовых или текстовых данных.
Сортировать по дате Пример
Чтобы выполнить быструю сортировку по дате в порядке возрастания, от самой старой до самой новой:
- Выделите диапазон ячеек для сортировки. Чтобы следовать приведенному выше примеру, выделите ячейки с G2 по K7.
- Выбрать Главная.
- Выберите Сортировка и фильтрация, чтобы открыть раскрывающийся список.
- Выберите Пользовательская сортировка, чтобы открыть диалоговое окно «Сортировка».
- Под заголовком столбца выберите стрелку вниз Сортировать по и выберите Заимствованные, чтобы сначала отсортировать данные по заимствованной дате.
- Под заголовком «Сортировать по» выберите Значения ячеек. Сортировка основана на фактических данных в таблице.
- Под заголовком «Порядок сортировки» выберите из раскрывающегося списка С самого старого по новый.
- Выберите OK в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.
Если результаты сортировки по дате не соответствуют ожидаемым, данные в столбце, содержащем ключ сортировки, могут содержать даты или время, сохраненные в виде текстовых данных, а не в виде чисел (даты и время представляют собой просто отформатированные числовые данные).).
Смешанные данные и быстрая сортировка
При использовании метода быстрой сортировки, если записи, содержащие текстовые и числовые данные, смешиваются вместе, Excel сортирует числовые и текстовые данные отдельно, помещая записи с текстовыми данными в конец отсортированного списка.
Excel также может включать заголовки столбцов в результаты сортировки, интерпретируя их как еще одну строку текстовых данных, а не как имена полей для таблицы данных.
Предупреждение о возможной сортировке
Если используется диалоговое окно «Сортировка», даже для сортировки по одному столбцу Excel может отображать сообщение, предупреждающее вас о том, что он обнаружил данные, хранящиеся в виде текста, и дает вам возможность:
- Сортировать все, что выглядит как число, как число.
- Сортировать числа и числа, хранящиеся в виде текста, отдельно.
Если вы выберете первый вариант, Excel попытается разместить текстовые данные в правильном месте результатов сортировки. Выберите второй вариант, и Excel поместит записи, содержащие текстовые данные, в нижнюю часть результатов сортировки, как и при быстрой сортировке.
Сортировка данных по дням недели или месяцам в Excel
Вы также можете сортировать данные по дням недели или месяцам года, используя тот же встроенный настраиваемый список, который Excel использует для добавления дней или месяцев на лист с помощью маркера заполнения. Эти списки позволяют сортировать по дням или месяцам в хронологическом порядке, а не в алфавитном порядке.
Как и в случае с другими параметрами сортировки, значения сортировки по пользовательскому списку могут отображаться в порядке возрастания (с воскресенья по субботу или с января по декабрь) или в порядке убывания (с субботы по воскресенье или с декабря по январь).
На изображении выше были выполнены следующие шаги для сортировки выборки данных в диапазоне от A2 до E12 по месяцам года:
- Выделите диапазон ячеек для сортировки.
- Выбрать Главная.
- Выберите Сортировка и фильтрация, чтобы открыть раскрывающийся список.
- Выберите Пользовательская сортировка, чтобы открыть диалоговое окно «Сортировка».
- Под заголовком столбца выберите Начало месяца из раскрывающегося списка, чтобы отсортировать данные по месяцам года.
- Под заголовком «Сортировать по» выберите Значения ячеек. Сортировка основана на фактических данных в таблице.
- Под заголовком «Порядок» выберите стрелку вниз рядом с параметром по умолчанию от A до Z, чтобы открыть раскрывающееся меню.
- Выберите Пользовательский список, чтобы открыть диалоговое окно Пользовательские списки.
- В левом окне диалогового окна выберите Январь, Февраль, Март, Апрель.
- Выберите OK, чтобы подтвердить выбор и вернуться в диалоговое окно «Сортировка».
- Выбранный список (январь, февраль, март, апрель) отображается под заголовком Заказ.
- Выберите OK, чтобы закрыть диалоговое окно и отсортировать данные по месяцам года.
По умолчанию пользовательские списки отображаются только в порядке возрастания в диалоговом окне «Пользовательские списки». Для сортировки данных в порядке убывания с использованием пользовательского списка после выбора нужного списка, чтобы он отображался под заголовком «Порядок» в диалоговом окне «Сортировка»:
- Нажмите стрелку вниз рядом с отображаемым списком, например Январь, февраль, март, апрель, чтобы открыть раскрывающееся меню.
- В меню выберите вариант пользовательского списка, который отображается в порядке убывания, например, Декабрь, Ноябрь, Октябрь, Сентябрь.
- Нажмите OK, чтобы отсортировать данные в порядке убывания с помощью пользовательского списка.
Сортировка по строкам для изменения порядка столбцов в Excel
Как показано в предыдущих вариантах сортировки, данные обычно сортируются с использованием заголовков столбцов или имен полей. Результатом является переупорядочивание целых строк или записей данных. Менее известная и, следовательно, менее используемая опция сортировки в Excel - сортировка по строке, которая приводит к изменению порядка столбцов на листе слева направо.
Одной из причин сортировки по строкам является соблюдение порядка столбцов в разных таблицах данных. Когда столбцы расположены в одинаковом порядке слева направо, проще сравнивать записи или копировать и перемещать данные между таблицами.
Настроить порядок столбцов
Однако очень редко получение столбцов в правильном порядке является простой задачей из-за ограничений параметров сортировки по возрастанию и убыванию для значений. Обычно необходимо использовать пользовательский порядок сортировки, и Excel включает параметры сортировки по ячейке или цвету шрифта или по значкам условного форматирования.
Самый простой способ сообщить Excel порядок столбцов - добавить строку выше или ниже таблицы данных, содержащую числа, указывающие порядок столбцов слева направо. Сортировка по строкам становится простой задачей сортировки столбцов от меньшего к большему по строке, содержащей числа.
После сортировки добавленный ряд чисел можно легко удалить.
Сортировка по строкам Пример
В примере данных, используемом для этой серии по параметрам сортировки Excel, столбец «Идентификатор учащегося» всегда был первым слева, за ним следуют «Имя» и затем «Возраст».
В этом случае, как показано на изображении выше, числа были добавлены к столбцам, чтобы подготовить рабочий лист к изменению порядка столбцов, чтобы столбец «Программа» был первым слева, за которым следовали «Начало месяца», «Имя», «Возраст», и студенческий билет.
Вот как изменить порядок столбцов:
- Вставьте пустую строку над строкой, содержащей имена полей.
- В этой новой строке введите следующие числа слева направо, начиная со столбца A: 5, 3, 4, 1, 2.
- Выделите диапазон для сортировки. В этом примере выделите от A2 до E13.
- Выбрать Главная.
- Выберите Сортировка и фильтрация, чтобы открыть раскрывающийся список.
- Выберите Пользовательская сортировка, чтобы открыть диалоговое окно «Сортировка».
- Выберите Параметры, чтобы открыть диалоговое окно «Параметры сортировки».
- В разделе «Ориентация» выберите Сортировать слева направо, чтобы отсортировать порядок столбцов на листе слева направо.
- Выберите OK, чтобы закрыть диалоговое окно «Параметры сортировки».
- При изменении ориентации заголовок столбца в диалоговом окне «Сортировка» меняется на «Строка».
- Выберите Сортировать по стрелке вниз и выберите Строка 2. Это строка, содержащая пользовательские номера.
- Под заголовком «Сортировать по» выберите Значения ячеек.
- Под заголовком Порядок выберите От меньшего к большему из раскрывающегося списка, чтобы отсортировать числа в строке 2 в порядке возрастания.
- Выберите OK, чтобы закрыть диалоговое окно и отсортировать столбцы слева направо по числам в строке 2.
- Порядок столбцов начинается со слов «Программа», за которыми следуют «Начало месяца», «Имя», «Возраст» и «Код студента».
Используйте параметры пользовательской сортировки Excel для изменения порядка столбцов
Хотя пользовательские сортировки доступны в диалоговом окне «Сортировка» в Excel, эти параметры не так просто использовать, когда дело доходит до изменения порядка столбцов на листе. Параметры для создания пользовательского порядка сортировки, доступные в диалоговом окне Сортировка, включают сортировку данных по цвету ячейки, цвету шрифта и значку.
Если к каждому столбцу уже применено уникальное форматирование, например, другой шрифт или цвет ячейки, это форматирование необходимо добавить к отдельным ячейкам в той же строке для каждого столбца, который нужно изменить.
Например, чтобы использовать цвет шрифта для изменения порядка столбцов:
- Выберите имя каждого поля и измените цвет шрифта для каждого. Например, измените Program на красный, Month Start на зеленый, Name на синий, Age на оранжевый и Student ID на фиолетовый.
- В диалоговом окне «Сортировка» выберите Сортировать по и выберите Строка 2.
- Под заголовком Сортировка выберите Цвет шрифта.
- Под заголовком Порядок вручную установите порядок цветов имен полей в соответствии с желаемым порядком столбцов.
- После сортировки сбросить цвет шрифта для каждого имени поля.
