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

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

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

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

sort_custom1.png

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

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

sort_custom2.png

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

sort_custom3.png

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

sort_custom4.png

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

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

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

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

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

sort_custom5.pngsort_custom6.png

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

sort_custom7.png

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

sort_custom8.png

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

sort_custom9.png

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

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


03.09.2013 11:30:37
Спасибо за статью, очень интересная тонкость, видел неоднократно этот настраиваемый список, но не пользовался, теперь возьму на вооружение.
13.05.2014 13:48:41
Уважаемые пользователи!

Помогите, пожалуйста, решить проблему.

Есть 5 файлов в каждом из них одинаковый список к примеру товара, и при добавлении нового товара приходится менять в каждом файле(добавляются в середину или начало или конец списка), и после протягиваются вручную формулы.

Можно ли сделать так, чтоб изменить в одном файле, а в остальных все изменилось автоматически. (добавился новый продукт в то же место и формулы в каждом файле протянулись автоматически)?

Заранее благодарю за ответ.
14.05.2014 09:58:47
Виталий, это комментарии к статье, а не форум. Создайте там тему, опишите проблему, приложите ваши файлы - посмотрим как вам помочь.
22.04.2015 11:13:34
Добрый день. Как можно сделать сортировку, например: 1 столбец сначала пустые потом все даты 2 столбец по алфавиту. или то можно сделать только в 2 этапа?
24.04.2019 15:53:34
Надо еще указать в статье, что если набирать список просто текстом, то сработает ограничение "не более 255 символов".

А если делать импорт значений из указанного диапазона ячеек, то это ограничение преодолевается.
24.01.2020 05:22:52
Огромное спасибо, на вашем сайте нахожу 99% ответов на возникающие вопросы.
30.10.2022 17:58:33
В примере показана сортировка в новой версии Excel. В моем же Excel 2003 нет опции Настраиваемый список. Мне например нужно одним кликом отсортировать большой список, где есть как буквенные так и цифровые значения. Короче, нужно, чтобы я мог перейти от натуральной сортировки (цифры (1,2,3,4) к алфавитной. Чтобы цифры выстроились (от меньшего к большему) в такой последовательности: 1111,222,33,4. Как это выполнить в Excel 2003?
Наверх