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. После сортировки сбросьте цвет шрифта для каждого имени поля.

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

Думаю, всем известно, как выполнить сортировку по столбцу в алфавитном порядке или по возрастанию / убыванию. Это делается одним нажатием кнопки А-Я (A-Z) и Я-А (Z-A) в разделе Редактирование (Editing) на вкладке Главная (Home) либо в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data):

Однако, сортировка в Excel имеет гораздо больше настраиваемых параметров и режимов работы, которые не так очевидны, но могут оказаться очень удобны:

• Сортировка по нескольким столбцам
• Сортировка данных в Excel по заголовкам строк и столбцов
• Сортировка в произвольном порядке (по настраиваемому списку)

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

Я покажу Вам, как в Excel сортировать данные по двум или более столбцам. Работа инструмента показана на примере Excel 2010 – именно эта версия установлена на моём компьютере. Если Вы работаете в другой версии приложения, никаких затруднений возникнуть не должно, поскольку сортировка в Excel 2007 и Excel 2013 работает практически так же. Разницу можно заметить только в расцветке диалоговых окон и форме кнопок. Итак, приступим…

1. Кликните мышью по кнопке Сортировка (Sort) на вкладке Данные (Data) или по кнопке Настраиваемая сортировка (Custom sort) на вкладке Главная (Home), чтобы открыть диалоговое окно Сортировка (Sort).
2. В появившемся диалоговом окне, нажимая кнопку Добавить уровень (Add Level), установите нужное количество столбцов, по которым будет выполняться сортировка.
3. В выпадающих списках Сортировать по (Sort by) и Затем по (Then by) выберите столбцы, по которым нужно произвести сортировку данных. Например, Вы планируете, как провести выходные, и просматриваете список отелей, предложенный туристическим агентством. Вы хотите отсортировать этот список сначала по географическому признаку (столбец Region), затем по системе питания (столбец Board basis) и в конце концов по цене (столбец Price), как это показано на рисунке ниже:
4. Жмите ОК и смотрите, что получилось:
   • В первую очередь отсортированы значения в столбце Region – в алфавитном порядке.
   • Затем упорядочены значения в столбце Board basis так, что отели, предлагающие сервис «Всё включено» (AI) – сгруппированы в начале списка.
   • И, наконец, по стоимости в столбце Price данные расположены в порядке возрастания.

   

Сортировать данные по нескольким столбцам в Excel оказалось совсем не сложно, правда? Однако, в диалоговом окне Сортировка (Sort) кроется значительно больше возможностей. Далее в этой статье я покажу, как сортировать по строке, а не по столбцу, и как упорядочить данные на листе в алфавитном порядке по заголовкам столбцов. Вы также научитесь выполнять сортировку данных нестандартными способами, когда сортировка в алфавитном порядке или по значению чисел не применима.

Сортировка данных в Excel по заголовкам строк и столбцов

Я полагаю, что в 90% случаев сортировка данных в Excel выполняется по значению в одном или нескольких столбцах. Однако, иногда встречаются не такие простые наборы данных, которые нужно упорядочить по строке (горизонтально), то есть изменить порядок столбцов слева направо, основываясь на заголовках столбцов или на значениях в определённой строке.

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

Нам нужно отсортировать этот список фотокамер по наиболее важным для нас параметрам. Для примера первым делом выполним сортировку по названию модели:

1. Выбираем диапазон данных, которые нужно сортировать. Если нам нужно, чтобы в результате сортировки изменился порядок всех столбцов, то достаточно выделить любую ячейку внутри диапазона. Но в случае с нашим набором данных такой способ не допустим, так как в столбце A перечисляются характеристики камер, и нам нужно, чтобы он остался на своём месте. Следовательно, выделяем диапазон, начиная с ячейки B1:
2. На вкладке Данные (Data) нажимаем кнопку Сортировка (Sort), чтобы открыть одноимённое диалоговое окно. Обратите внимание на параметр Мои данные содержат заголовки (My data has headers) в верхнем правом углу диалогового окна. Если в Ваших данных нет заголовков, то галочки там быть не должно. В нашей же таблице заголовки присутствуют, поэтому мы оставляем эту галочку и нажимаем кнопку Параметры (Options).
3. В открывшемся диалоговом окне Параметры сортировки (Sort Options) в разделе Сортировать (Orientation) выбираем вариант Столбцы диапазона (Sort left to right) и жмём ОК.
4. Следующий шаг – в диалоговом окне Сортировка (Sort) под заголовком Строка (Row) в выпадающем списке Сортировать по (Sort by) выбираем строку, по значениям которой будет выполнена сортировка. В нашем примере мы выбираем строку 1, в которой записаны названия фотокамер. В выпадающем списке под заголовком Сортировка (Sort on) должно быть выбрано Значения (Values), а под заголовком Порядок (Order) установим От А до Я (A to Z).

В результате сортировки у Вас должно получиться что-то вроде этого:

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

Наша задача – повторить описанные выше шаги 1 – 3. Затем на шаге 4 вместо строки 1 выбираем строку 4, в которой указаны розничные цены (Retail Price). В результате сортировки таблица будет выглядеть вот так:

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

Надеюсь, теперь стало ясно, как работает сортировка по строке в Excel. Но что если наши данные должны быть упорядочены не по алфавиту и не по возрастанию / убыванию?

Сортировка в произвольном порядке (по настраиваемому списку)

Если нужно упорядочить данные в каком-то особом порядке (не по алфавиту), то можно воспользоваться встроенными в Excel настраиваемыми списками или создать свой собственный. При помощи встроенных настраиваемых списков Вы можете сортировать, к примеру, дни недели или месяцы в году. Microsoft Excel предлагает два типа таких готовых списков – с сокращёнными и с полными названиями.

Предположим, у нас есть список еженедельных дел по дому, и мы хотим упорядочить их по дню недели или по важности.

1. Начинаем с того, что выделяем данные, которые нужно сортировать, и открываем диалоговое окно Сортировка (Sort), точно так же, как в предыдущих примерах – Данные > Сортировка (Data > Sort).
2. В поле Сортировать по (Sort by) выбираем столбец, по которому нужно выполнить сортировку. Мы хотим упорядочить наши задачи по дням недели, то есть нас интересует столбец Day. Затем в выпадающем списке под заголовком Порядок (Order) выбираем вариант Настраиваемый список (Custom list), как показано на снимке экрана ниже:
3. В диалоговом окне Списки (Custom Lists) в одноимённом поле выбираем нужный список. В нашем столбце Day указаны сокращённые наименования дней недели – кликаем по соответствующему варианту списка и жмём ОК.

Готово! Теперь домашние дела упорядочены по дням недели:

Замечание: Если Вы планируете вносить изменения в эти данные, помните о том, что добавленные новые или изменённые существующие данные не будут отсортированы автоматически. Чтобы повторить сортировку, нажмите кнопку Повторить (Reapply) в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data).

Как видите, сортировка данных в Excel по настраиваемому списку – задача вовсе не сложная. Ещё один приём, которому мы должны научиться – сортировка данных по собственному настраиваемому списку.

Сортировка данных по собственному настраиваемому списку

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

Повторите шаги 1 и 2 из предыдущего примера. Когда откроется диалоговое окно Списки (Custom Lists), в одноимённом столбце слева нажмите НОВЫЙ СПИСОК (NEW LIST) и заполните нужными значениями поле Элементы списка (List entries). Внимательно введите элементы Вашего списка именно в том порядке, в котором они должны быть расположены в результате сортировки.

Нажмите Добавить (Add), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК.

Вот так выглядит наш список домашних дел, упорядоченных по важности:

Подсказка: Для создания длинных настраиваемых списков удобнее и быстрее импортировать их из существующего диапазона. Об этом подробно рассказано в статье Создание настраиваемого списка из имеющегося листа Excel.

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

И вот, наконец, наш список домашних дел упорядочен в наивысшей степени логично, сначала по дням недели, затем по важности 🙂

На сегодня всё. Спасибо за внимание!