В этой статье рассматриваются различные методы сортировки данных в Excel. Информация, которая может быть найдена в разных разделах этой статьи, содержит инструкции о том, как:
- Сортировка одной колонки с использованиемСортировка и фильтр или горячие клавиши
- Сортировка нескольких столбцов
- Сортировать по датам или времени
- Сортировка по дням недели, месяцам или другим пользовательским спискам
- Сортировать по строкам - переупорядочить столбцы
Выбор данных для сортировки
Прежде чем данные могут быть отсортированы, Excel должен знать точный диапазон, который должен быть отсортирован, и обычно Excel отлично подходит для выбора областей связанных данных, поэтому, когда он был введен, выполнялись следующие условия:
- В области связанных данных не осталось пустых строк или столбцов.
- Пустые строки и столбцы были оставлены между областями связанных данных.
Excel будет довольно точно определять, имеет ли область данных имена полей и исключает строку из записей, подлежащих сортировке. Однако выбор Excel для выбора диапазона может быть опасным - особенно при большом количестве данных, которые трудно проверить.
Чтобы убедиться, что выбраны правильные данные, выделите диапазон перед началом сортировки. Если один и тот же диапазон нужно сортировать повторно, лучшим подходом является присвоение ему имени.
Сортировка ключа и порядок сортировки в Excel
Для сортировки требуется использование ключа сортировки и порядка сортировки. сортировать ключ - данные в столбце или столбцах, которые вы хотите отсортировать; он идентифицируется заголовком столбца или именем поля. На изображении выше возможные ключи сортировки - это идентификатор студента, имя, возраст, программа и месяц. В быстром сортировке щелчок по одной ячейке в столбце, содержащем ключ сортировки, достаточно, чтобы указать Excel, что такое ключ сортировки.
Для текстовых или числовых значений две опции для порядка сортировки являются восходящими и нисходящими.
При использовании Сортировка и фильтр на Главная вкладке ленты, параметры порядка сортировки в раскрывающемся списке будут меняться в зависимости от типа данных в выбранном диапазоне.
Быстрая сортировка с использованием Sort & Filter
В Excel быстрый сортировку можно выполнить с помощью Сортировка и фильтр на Главная вкладку ленты.
Шаги к выполнению быстрого сортирования:
- Нажмите на клетка в столбце, содержащем ключ сортировки.
- Нажми на Главная вкладку ленты.
- Нажмите Сортировка и фильтр чтобы открыть раскрывающееся меню опций сортировки.
- Выберите один из двух вариантов для сортировки в по возрастанию или же нисходящий порядок.
Сортировка нескольких столбцов данных в Excel
В дополнение к быстрому сортировке на основе одного столбца данных функция пользовательского сортировки Excel позволяет сортировать по нескольким столбцам, задавая несколько ключей сортировки. В многоколоночных сортировках ключи сортировки идентифицируются путем выбора заголовков столбцов в диалоговом окне «Сортировка».
Как и в случае быстрого сортировки, ключи сортировки определяются путем идентификации заголовков столбцов или имен полей в таблице, содержащей ключ сортировки.
Сортировка по нескольким примерам столбцов
В приведенном выше примере для сортировки данных в диапазоне H2 в L12 на двух столбцах данных - сначала по имени, а затем по возрасту.
- Выделите диапазон сортируемых ячеек.
- Нажми на Главная вкладку ленты.
- Нажми на Сортировка и фильтр на ленте, чтобы открыть раскрывающийся список.
- Нажмите на Пользовательская сортировка в раскрывающемся списке, чтобы Сортировать диалоговое окно.
- Под колонка заголовок в диалоговом окне, выберите название из раскрывающегося списка, чтобы сначала отсортировать данные по столбцу Имя.
- Сортировать по параметр остается установленным для Ценности - поскольку сортировка основана на фактических данных в таблице.
- Под Порядок сортировки заголовок, выберите От Z до A в раскрывающемся списке для сортировки данных Name в порядке убывания.
- В верхней части диалогового окна нажмите Добавить уровень чтобы добавить второй вариант сортировки.
- Для второго ключа сортировки, под колонка заголовок, выберите Возраст из раскрывающегося списка для сортировки записей с дублирующимися именами столбцом «Возраст».
- Под Порядок сортировки заголовок, выберите Самый большой и наименьший из раскрывающегося списка для сортировки данных возраста в порядке убывания.
- Нажмите Хорошо в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.
В результате определения второго ключа сортировки в приведенном выше примере две записи с одинаковыми значениями для название поля были дополнительно отсортированы в порядке убывания, используя Возраст поле, в результате чего запись для ученика Уилсона, 21 года, была до записи для второго Уилсона в возрасте 19 лет.
Первая строка: заголовки столбцов или данные
Диапазон данных, выбранных для сортировки в приведенном выше примере, включал заголовки столбцов над первой строкой данных.
Excel обнаружил, что в этой строке содержатся данные, которые отличались от данных в последующих строках, поэтому он предположил, что первая строка является заголовками столбцов и скорректировала доступные параметры в Сортировать чтобы включить их.
Один критерий, который Excel использует для определения того, является ли первая строка заголовками столбцов, является форматированием. В приведенном выше примере текст в первой строке представляет собой другой шрифт, и он отличается от данных в остальных строках.
Если первая строка не содержит заголовков, Excel использует букву столбца, такую как столбец D или столбец E, как выбор в колонка вариант Сортировать диалоговое окно.
Excel использует такую разницу, чтобы определить, является ли первая строка строкой заголовка, и это хорошо подходит для правильного выбора, но это не является непогрешимым. Если Excel делает ошибку, диалоговое окно «Сортировка» содержит флажок - У моих данных есть заголовки - это можно использовать для переопределения этого автоматического выбора.
Сортировка данных по дате или времени в Excel
Помимо сортировки текстовых данных в алфавитном порядке или чисел от наибольшего до наименьшего, параметры сортировки Excel включают значения даты сортировки. Доступные заказы сортировки, доступные для дат, включают:
- По возрастанию - от старости до новейшей;
- По убыванию - от самых старых до самых старых.
Диалоговое окно «Быстрая сортировка и сортировка»
Поскольку даты и время представляют собой только форматированные числовые данные, для сортировки по одному столбцу, например Дата заимствования в нашем примере метод быстрой сортировки может быть успешно использован. Для сортировки, включающей несколько столбцов дат или времени, необходимо использовать диалоговое окно Сортировка - так же, как при сортировке нескольких столбцов числа или текстовых данных.
Сортировать по дате Пример
Чтобы выполнить быстрый сортировать по дате в порядке возрастания - от старости до новейшего - для примера на изображении выше, будут выполняться следующие шаги:
- Выделите диапазон сортируемых ячеек.
- Нажми на Главная вкладку ленты.
- Нажми на Сортировка и фильтр на ленте, чтобы открыть раскрывающийся список.
- Нажмите на Пользовательская сортировка в раскрывающемся списке, чтобы Сортировать диалоговое окно.
- Под колонка заголовок в диалоговом окне, выберите Заимствованные из раскрывающегося списка, чтобы сначала отсортировать данные по столбцу Имя.
- Сортировать по параметр остается установленным для Ценности - поскольку сортировка основана на фактических данных в таблице.
- Под Порядок сортировки заголовок, выберите От старых к новинкам в раскрывающемся списке, чтобы отсортировать даты
- Нажмите Хорошо в диалоговом окне, чтобы закрыть диалоговое окно и отсортировать данные.
Если результаты сортировки по дате не выглядят так, как ожидалось, данные в столбце, содержащем ключ сортировки, могут содержать даты или время, хранящиеся в виде текстовых данных, а не как числа (даты и время - это только данные с форматированными числами).
Смешанные данные и быстрые сортировки
При использовании метода быстрой сортировки, если записи, содержащие текстовые и числовые данные, смешиваются вместе, Excel сортирует число и текстовые данные отдельно, помещая записи с текстовыми данными в нижней части отсортированного списка.
Excel также может включать заголовки столбцов в результатах сортировки, интерпретируя их как еще одну строку текстовых данных, а не как имена полей для таблицы данных.
Возможное предупреждение о сортировке
Если используется диалоговое окно Сортировка, даже для сортировки по одному столбцу, Excel может отображать сообщение, предупреждающее вас о том, что оно обнаружило данные, хранящиеся в виде текста, и дает вам выбор:
- Сортируйте все, что похоже на число, как число.
- Сортировка номеров и номеров, сохраненных в виде текста отдельно.
Если вы выберете первый вариант, Excel попытается поместить текстовые данные в правильное местоположение результатов сортировки. Выберите второй вариант, и Excel поместит записи, содержащие текстовые данные, в нижней части результатов сортировки - так же, как и быстрые сортировки.
Сортировка данных по дням недели или месяцам в Excel
Вы также можете сортировать данные по дням недели или месяцам года, используя тот же встроенный пользовательский список, который Excel использует для добавления дней или месяцев на рабочий лист, используя дескриптор заполнения. Этот список позволяет сортировать по месяцам или месяцам в хронологическом порядке, а не в алфавитном порядке.
Как и в случае с другими параметрами сортировки, сортировка значений по пользовательскому списку может отображаться по возрастанию (с воскресенья по субботу / январь по декабрь) или по убыванию (с субботы по воскресенье / декабрь по январь).
На изображении выше были выполнены следующие шаги для сортировки образца данных в диапазоне H2 в L12 по месяцам года:
- Выделите диапазон сортируемых ячеек.
- Нажми на Главная вкладку ленты.
- Нажми на Сортировка и фильтр на ленте, чтобы открыть раскрывающийся список.
- Нажмите на Пользовательская сортировка в раскрывающемся списке, чтобы Сортировать диалоговое окно.
- Под колонка заголовок в диалоговом окне, выберите Месяц начался из раскрывающегося списка для сортировки данных по месяцам года.
- Сортировать по параметр остается установленным для Ценности - поскольку сортировка основана на фактических данных в таблице.
- Под Порядок сортировки заголовок, нажмите стрелку вниз рядом со значением по умолчанию От А до Я возможность открыть раскрывающееся меню.
- В меню выберите Пользовательский список для открытия Пользовательские списки диалоговое окно.
- В левом окне диалогового окна щелкните один раз в списке: Январь февраль март апрель… для его выбора.
- Нажмите Хорошо для подтверждения выбора и возврата к Сортировать в диалоговом окне.
- Выбранный список -Январь февраль март апрель - будет отображаться под порядок заголовок.
- Нажмите Хорошо закрыть диалоговое окно и отсортировать данные по месяцам года.
По умолчанию пользовательские списки отображаются только по возрастанию порядок в Пользовательские списки диалоговое окно. Для сортировки данных в порядке убывания с использованием пользовательского списка после выбора нужного списка, чтобы он отображался под порядок заголовок в диалоговом окне «Сортировка»:
- Нажмите стрелку вниз рядом с отображаемым списком, например Январь февраль март апрель…, чтобы открыть раскрывающееся меню.
- В меню выберите параметр настраиваемого списка, который отображается в порядке убывания, например Декабрь, Ноябрь, Октябрь, Сентябрь …
- Нажмите Хорошо для закрытия диалогового окна и сортировки данных в порядке убывания с использованием настраиваемого списка.
Сортировка по строкам для изменения столбцов в Excel
Как показано в предыдущих параметрах сортировки, данные обычно сортируются с использованием заголовков столбцов или имен полей, и результатом является переупорядочение целых строк или записей данных. Менее известный и, следовательно, менее используемый параметр сортировки в Excel - сортировка по строке, что приводит к изменению порядка столбцов слева направо на листе
Одной из причин сортировки по строке является сопоставление порядка столбцов между различными таблицами данных. С столбцами в том же порядке слева направо проще сравнивать записи или копировать и перемещать данные между таблицами.
Настройка порядка столбцов
Однако редко бывает, что столбцы в правильном порядке являются простой задачей из-за ограничений в порядке возрастания и убывания порядка сортировки для значений. Как правило, необходимо использовать собственный порядок сортировки, а Excel включает в себя параметры сортировки по цвету ячеек или шрифтов или условным символам форматирования.
Самый простой способ сообщить Excel порядок столбцов - добавить строку выше или ниже таблицы данных, содержащей числа 1, 2, 3, 4.., которые указывают порядок столбцов слева направо. Сортировка по строкам становится простым делом сортировки столбцов с наименьшей по величине строкой, содержащей числа.
После сортировки добавленная строка чисел может быть легко удалена.
Сортировать по примерам строк
В образце данных, используемом для этой серии в вариантах сортировки Excel, Студенческий билет столбец всегда был первым слева, а затем название а затем обычно Возраст.
В этом случае, как показано на изображении выше, столбцы были переупорядочены, так что программа столбец сначала слева, а затем Месяц начался, название, так далее.
Следующие шаги были использованы, чтобы изменить порядок столбцов на то, что показано на изображении выше:
- Вставьте пустую строку над строкой, содержащей имена полей.
- В этой новой строке введите следующие цифры слева направо, начиная с столбца H: 5, 3, 4, 1, 2.
- Выделите диапазон От H2 до L13.
- Нажми на Главная вкладку ленты.
- Нажми на Сортировка и фильтр на ленте, чтобы открыть раскрывающийся список.
- Нажмите на Пользовательская сортировка в раскрывающемся списке, чтобы Сортировать диалоговое окно.
- В нижней части диалогового окна нажмите Опции.. для открытия Параметры сортировки диалоговое окно.
- В разделе «Ориентация» этого второго диалогового окна нажмите Сортировка слева направо для сортировки порядка столбцов слева направо на листе.
- Нажмите Хорошо для закрытия этого диалогового окна.
- С изменением ориентации колонка заголовок в Сортировать диалоговое окно изменится на Строка .
- Под Строка заголовок, выберите сортировать по строке 2 - строка, содержащая пользовательские номера.
- Сортировать по параметр остается установленным для Ценности .
- Под Порядок сортировки заголовок, выберите Самый маленький до самого большого из раскрывающегося списка для сортировки чисел в строке 2 в порядке возрастания.
- Нажмите Хорошо закрыть диалоговое окно и отсортировать столбцы слева направо по номерам в строке 2.
- Порядок столбцов должен начинаться с программа с последующим Начало месяца, имя, и т.п.
Использование пользовательских параметров сортировки Excel для изменения порядка столбцов
Как упоминалось выше, в то время как пользовательские сортировки доступны в диалоговом окне «Сортировка» в Excel, эти параметры нелегко использовать, когда речь идет о переупорядочении столбцов на листе. Опции для создания пользовательского порядка сортировки, доступные в диалоговом окне Сортировка, предназначены для сортировки данных по цвету ячеек, цвету шрифта и значку.
Если в каждом столбце уже не было применено уникальное форматирование, например, разные цвета шрифтов или ячеек, это форматирование должно быть добавлено к отдельным ячейкам в той же строке для каждого переупорядоченного столбца.
Например, чтобы использовать цвет шрифта для изменения порядка столбцов на изображении выше:
- Нажмите на каждый имя поля и изменить цвет шрифта для каждого, например, красный, зеленый, синий и т. д.
- В диалоговом окне «Сортировка» установите Сортировать по вариант Цвет шрифта .
- Под порядок, вручную установите порядок цветов полей чтобы соответствовать желаемому порядку столбцов.
- После сортировки, сбросить цвет шрифта для каждого имени поля.
