Настраиваемая сортировка в excel. Сортировка данных в Excel по строкам и столбцам с помощью формул

Как в Excel сортировать по строке, по столбцу и по настраиваемому списку

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

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

Однако, сортировка в 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). В итоге сортировки таблица будет смотреться вот так:

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

Читайте также  Почему ноут не видит телефон. Почему компьютер или ноутбук не видит телефон через USB? Что делать?

Надеюсь, сейчас стало ясно, как работает сортировка по строке в 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.

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

Читайте также  Настройка роутера асус rt n16. Настройка ASUS RT-N16 и прошивка для Wi-Fi роутера на русском языке

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

На сейчас всё. Спасибо за внимание!

Оцените качество статьи. Нам принципиально ваше мнение:

Хитрости сортировки

Сортировка – функция Excel до боли знакомая и привычная почти всем. Тем не наименее, есть несколько необычных и увлекательных случаев ее применения.

Случай 1. Сортировка по смыслу, а не по алфавиту

Представим очень всераспространенную ситуацию: есть таблица, в которой находится столбец с заглавием месяца (январь, февраль, март…) или дня недельки (пт, вт, ср…). При обычный сортировке по данному столбцу Excel располагает элементы по-алфавиту (т.е. от А до Я):

А хотелось бы, естественно, получить на выходе обычную последовательность с января по декабрь либо с пн. по вторник. Такое можно просто воплотить с помощью особенной сортировки по пользовательскому списку (custom list sorting)

Выделите таблицу и нажмите огромную клавишу Сортировка на вкладке Данные (Data – Sort). Раскроется диалоговое окно, в котором необходимо задать поле (столбец) сортировки и в крайнем раскрывающемся перечне выбрать тип сортировки Настраиваемый перечень (Custom List):

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

Если подходящего перечня (например, месяцев, но на английском) нет, то его можно ввести в правое поле, выбрав опцию Новый перечень (New List):

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

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

  • должности (директор, замдиректора, управляющий департамента, начальник отдела…)
  • воинские звания (генерал, полковник, подполковник, майор…)
  • сертификации (TOEFL, ITIL, MCP, MVP…)
  • клиентов или товары по значимости лично для вас (виски, текила, коньяк, вино, пиво, лимонад…)
  • и т.д.

Случай 2. Сортировка текста и чисел одновременно

Предположим, что в нашей таблице есть столбец с кодами различных запчастей и агрегатов для каров (part number). При этом большие детали в сборе (например, коробка передач, движок, управляющее управление) обозначаются чисто цифровым кодом, а маленькие детали, которые в их входят – кодом с добавлением уточняющего номера через, допустим, точку. Попытка отсортировать таковой перечень обыденным образом приведет к ненужному результату, т.к. Excel сортирует раздельно числа (номера больших агрегатов в сборе) и раздельно текст (номера маленьких деталей с точками):

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

Чтобы это воплотить, необходимо временно добавить еще один столбец к нашей таблице, в котором перевоплотить все коды в текст с помощью функции ТЕКСТ:

Если потом произвести сортировку по этому столбцу, то Excel спросит о том, как ему сортировать числа и текст:

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

Ссылки по теме

Оставьте комментарий