В этой статье я покажу вам, как в Excel сортировать данные по нескольким столбцам, в алфавитном порядке по заголовкам столбцов и по значениям в любой строке. Вы также узнаете, как сортировать данные нестандартными способами, когда алфавитная или числовая сортировка не применима.
Думаю, все знают, как сортировать по столбцу по алфавиту или по возрастанию / убыванию. Это делается одним щелчком по кнопкам AZ и ZA в разделе «Правка» вкладки «Главная» или в разделе «Сортировка и фильтр» вкладки «Данные):
Однако сортировка в Excel имеет гораздо больше настраиваемых параметров и режимов работы, которые не так очевидны, но могут быть очень удобными:
- Сортировать по нескольким столбцам
- Сортировка данных в Excel по заголовкам строк и столбцов
- Сортировать в случайном порядке (для пользовательского списка)
Сортировка по нескольким столбцам
Я покажу вам, как сортировать данные по двум или более столбцам в Excel. Работа инструмента показана на примере Excel 2010 — это версия, установленная на моем компьютере. Если вы работаете в другой версии приложения, у вас не должно возникнуть проблем, потому что сортировка работает в Excel 2007 и Excel 2013 примерно одинаково. Единственное различие можно увидеть в цвете диалоговых окон и форме кнопок. Итак, приступим…
- Нажмите кнопку «Сортировка» на вкладке «Данные» или кнопку «Пользовательская сортировка» на вкладке «Главная», чтобы открыть диалоговое окно «Сортировка).
- В появившемся диалоговом окне, нажав кнопку «Добавить уровень», установите необходимое количество столбцов, по которым будет производиться сортировка.
- В раскрывающихся списках Сортировать по и Затем выберите столбцы, по которым вы хотите отсортировать данные. Например, вы планируете выходные и просматриваете список отелей, предложенных туристическим агентством. Вы хотите отсортировать этот список сначала по географическому региону (столбец «Регион»), затем по системе снабжения (столбец «База вкладок») и, наконец, по цене (столбец «Цена»), как показано на следующем изображении:
- Нажмите ОК и посмотрите, что произошло:
- Значения в столбце «Регион» сортируются первыми в алфавитном порядке.
- Затем мы отсортировали значения в столбце Pension Base так, чтобы отели All Inclusive (AI) были сгруппированы вверху списка.
- Наконец, данные сортируются в порядке возрастания по значению в столбце Цена.
Сортировка данных по нескольким столбцам в Excel оказалась совсем несложной, правда? Однако диалоговое окно «Сортировка» предлагает гораздо больше возможностей. Позже в этой статье я покажу вам, как сортировать по строкам, а не по столбцам, и как сортировать данные на листе в алфавитном порядке по заголовкам столбцов. Вы также узнаете, как сортировать данные нестандартными способами, когда алфавитная или числовая сортировка не применима.
Сортировка данных в Excel по заголовкам строк и столбцов
Я считаю, что в 90% случаев Excel сортирует данные по значению в одном или нескольких столбцах. Однако иногда нет таких простых наборов данных, которые необходимо отсортировать по строкам (по горизонтали), то есть переупорядочить столбцы слева направо в зависимости от заголовков или значений столбцов в данной строке.
Ниже приведен список камер, предоставленный региональным представителем или загруженный из Интернета. Список содержит множество функций, спецификаций и информацию о ценах и выглядит следующим образом:
Мы должны отсортировать этот список камер по наиболее важным для нас параметрам. Например, первый шаг — отсортировать по названию модели:
- Выберите диапазон данных для сортировки. Если вам нужно изменить порядок всех столбцов в результате сортировки, просто выберите любую ячейку в диапазоне. Но в случае с нашим набором данных этот метод недействителен, поскольку в столбце A перечислены характеристики камер, и нам нужно, чтобы он оставался на месте. Поэтому выбираем диапазон, начиная с ячейки B1:
- На вкладке «Данные» нажмите кнопку «Сортировка», чтобы открыть диалоговое окно с таким же именем. Обратите внимание на параметр «Мои данные имеют заголовки» в правом верхнем углу диалогового окна. Если в данных нет заголовков, галочки быть не должно. В нашей таблице есть заголовки, поэтому оставим этот флажок и нажмем кнопку Параметры).
- В открывшемся диалоговом окне «Параметры сортировки» в разделе «Ориентация» выберите параметр «Сортировать слева направо» и нажмите «ОК.
- Следующий шаг — в диалоговом окне «Сортировка» под заголовком «Строка» в раскрывающемся списке «Сортировать по» выберите строку, по которой будут сортироваться значения. В нашем примере мы выбираем строку 1, которая содержит названия камер. В раскрывающемся списке под заголовком Сортировать по вам нужно выбрать Значения, а под заголовком Порядок установить от A до Z).
В результате сортировки у вас должно получиться что-то вроде этого:
В нашем примере сортировка по заголовкам столбцов не имеет серьезного практического значения и делается только для того, чтобы показать вам, как это работает. Точно так же мы можем отсортировать наш список камер по строке, содержащей размер, разрешение, тип датчика или любой другой параметр, который мы считаем наиболее важным. Сделаем еще одну сортировку, на этот раз по цене.
Наша задача — повторить предыдущие шаги 1 — 3. Затем на шаге 4 вместо строки 1 выбрать строку 4, содержащую розничную цену. В результате сортировки таблица будет выглядеть так:
Обратите внимание, что данные отсортированы не только в выбранной строке. Меняются местами все столбцы, но данные не смешиваются. Другими словами, приведенный выше снимок экрана представляет собой список камер, отсортированных от самых дешевых до самых дорогих.
Надеюсь, теперь понятно, как работает сортировка строк в Excel. Но что, если наши данные нужно отсортировать не по алфавиту или по возрастанию / убыванию?
Сортировка в произвольном порядке (по настраиваемому списку)
Если вам нужно организовать данные в особом порядке (не в алфавитном порядке), вы можете использовать настраиваемые списки, встроенные в Excel, или создать свои собственные. Используя встроенные настраиваемые списки, вы можете сортировать, например, дни недели или месяцы года. Microsoft Excel предлагает два типа таких готовых списков: с краткими именами и с полными именами.
Допустим, у нас есть список еженедельных дел по дому, и мы хотим отсортировать их по дням недели или по важности.
- Начнем с выделения данных для сортировки и открытия диалогового окна Сортировка, как и в предыдущих примерах — Данные> Сортировка).
- В поле Сортировать по выберите столбец, по которому вы хотите отсортировать. Мы хотим отсортировать наши действия по дням недели, то есть нас интересует столбец «День». Затем в раскрывающемся списке под заголовком Порядок выберите параметр Пользовательский список, как показано на следующем снимке экрана:
- В диалоговом окне «Пользовательские списки» в одноименном поле выберите нужный список. Наш столбец «День» содержит сокращенные названия дней недели — выберите соответствующий вариант в списке и нажмите «ОК.
Готовый! Теперь работа по дому отсортирована по дням недели:
Примечание. Если вы планируете вносить изменения в эти данные, помните, что любые новые существующие данные, которые вы добавляете или изменяете, не будут сортироваться автоматически. Чтобы изменить порядок сортировки, нажмите кнопку «Повторно применить» в разделе «Сортировка и фильтр» вкладки «Данные).
Как видите, сортировать данные в Excel в настраиваемый список совсем несложно. Еще один трюк, который нам нужно изучить, — это отсортировать данные в нашем настраиваемом списке.
Сортировка данных по собственному настраиваемому списку
В нашей таблице есть столбец Priority — он содержит приоритеты действий. Чтобы использовать его для организации ваших еженедельных занятий от более важных к менее важным, выполните следующие действия.
Повторите шаги 1 и 2 из предыдущего примера. Когда откроется диалоговое окно «Настраиваемые списки», в одноименном столбце слева щелкните «НОВЫЙ СПИСОК» и введите необходимые значения в поле «Элементы списка». Тщательно внесите элементы в свой список в том порядке, в котором они должны быть расположены в результате сортировки.
Нажмите «Добавить», и созданный вами список будет добавлен к уже существующим. Затем нажмите ОК.
Вот как выглядит наш список домашних дел, отсортированный по важности:
Совет: для создания длинных настраиваемых списков проще и быстрее импортировать из существующего диапазона. Это подробно описано в статье Создание настраиваемого списка на основе существующего листа Excel.
С помощью настраиваемых списков вы можете сортировать по нескольким столбцам, используя разные настраиваемые списки для каждого столбца. Для этого выполните ту же процедуру, что и для сортировки по нескольким столбцам в предыдущем примере.
И, наконец, наш список дел по дому отсортирован в высшей логической степени: сначала по дням недели, затем по важности
это все на сегодня. Спасибо за внимание!