Пользовательская сортировка в Excel

Обновлено: 20.11.2024

Используйте настраиваемый список для сортировки или заполнения в заданном пользователем порядке. Excel предоставляет встроенные списки дней недели и месяцев года, но вы также можете создать свой собственный список.

Чтобы понять пользовательские списки, полезно посмотреть, как они работают и как хранятся на компьютере.

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

Excel предоставляет следующие встроенные настраиваемые списки, дни недели и месяцы года.

Встроенные списки

Вс, Пн, Вт, Ср, Чт, Пт, Сб

Воскресенье, понедельник, вторник, среда, четверг, пятница, суббота

Январь, февраль, март, апрель, май, июнь, июль, август, сен, октябрь, ноябрь, декабрь

Январь, февраль, март, апрель, май, июнь, июль, август, сентябрь, октябрь, ноябрь, декабрь

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

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

Пользовательские списки

Высокий, средний, низкий

Большой, средний и маленький

Север, юг, восток и запад

Старший менеджер по продажам, региональный менеджер по продажам, менеджер по продажам отдела и торговый представитель

Настраиваемый список может соответствовать диапазону ячеек, или вы можете ввести список в диалоговом окне "Настраиваемые списки".

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

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

Введите значения напрямую

Выполните следующие действия, чтобы создать собственный список, введя значения:

Для Excel 2010 и более поздних версий нажмите "Файл" > "Параметры" > "Дополнительно" > "Общие" > "Редактировать пользовательские списки".

Для Excel 2007 нажмите кнопку Microsoft Office > Параметры Excel > Популярные > Основные параметры для работы с Excel > Редактировать пользовательские списки.

В поле "Пользовательские списки" нажмите "НОВЫЙ СПИСОК", а затем введите записи в поле "Записи списка", начиная с первой записи.

Нажимайте клавишу Enter после каждого ввода.

Когда список будет готов, нажмите "Добавить".

Элементы списка, которые вы выбрали, появятся на панели пользовательских списков.

Дважды нажмите "ОК".

Создать собственный список из диапазона ячеек

Выполните следующие действия:

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

Во всплывающем окне «Пользовательские списки» убедитесь, что ссылка на ячейку выбранного вами списка элементов отображается в поле «Импортировать список из ячеек», а затем нажмите «Импорт».

Элементы списка, которые вы выбрали, появятся на панели настраиваемых списков.

Дважды нажмите "ОК".

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

Выполните следующие действия:

Следуйте предыдущим инструкциям для отображения диалогового окна "Редактировать настраиваемые списки".

В поле "Пользовательские списки" выберите список, который хотите удалить, и нажмите "Удалить".

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

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

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

Нужна дополнительная помощь?

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

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

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

Создать собственный список

<р>1. Выберите любую ячейку в столбце B (диапазон B2:B9) и на ленте выберите Главная > Сортировка и фильтр > Пользовательская сортировка.

<р>2. В окне "Сортировка" в раскрывающемся списке "Порядок" выберите "Пользовательский список" и нажмите "ОК".

<р>3. В окне Пользовательские списки (1) выберите НОВЫЙ СПИСОК в меню Пользовательские списки и (2) введите имена в нужном вам порядке (сначала женские от А до Я, затем мужские от А до Я). (3) Нажмите «Добавить», чтобы добавить список.

<р>4. В результате новый список будет добавлен в меню слева. Нажмите OK, чтобы использовать его в пользовательской сортировке.

<р>5. Это вернет вас в окно сортировки. Новый настраиваемый список имен выбирается в раскрывающемся списке Порядок. Нажмите OK, чтобы отсортировать данные в соответствии со списком.

В результате значения в столбце B теперь сортируются по созданному вами пользовательскому списку.

Импорт пользовательского списка

Вместо того, чтобы вводить элементы настраиваемого списка непосредственно в окно настраиваемого списка, вы также можете импортировать список из диапазона ячеек в рабочей книге. Допустим, у вас есть пользовательский список, отсортированный по столбцу D (диапазон D2:D9), показанный ниже, и вы хотите импортировать его и использовать для пользовательской сортировки.

<р>1. На ленте выберите «Файл» > «Параметры».

<р>2. В параметрах Excel выберите «Дополнительно» и в разделе «Общие» нажмите «Редактировать настраиваемые списки».

<р>3. В окне "Настраиваемые списки" щелкните значок стрелки рядом с полем "Импорт", чтобы выбрать диапазон для нового пользовательского списка.

<р>4. Выберите диапазон с отсортированными элементами для списка (D2:D9) и щелкните значок стрелки, чтобы вернуться на экран пользовательских списков.

<р>5. В разделе «Пользовательские списки» появился новый список имен. Нажмите «Импорт» и «ОК».

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

<р>6. Теперь перейдите в раздел «Главная» > «Сортировка и фильтр» > «Пользовательская сортировка» и выберите новый список в поле «Порядок», как показано в разделе выше.

поиск меню

Урок 18. Сортировка данных

Введение

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

Типы сортировки

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

Сортировка листа

Сортировка диапазона ячеек

Чтобы отсортировать лист:

В нашем примере мы отсортируем форму заказа футболки в алфавитном порядке по фамилии (столбец C).

Выбор ячейки C2

Нажатие команды "По возрастанию"

Отсортированный рабочий лист

Чтобы отсортировать диапазон:

В нашем примере мы выберем отдельную таблицу в форме заказа футболок, чтобы отсортировать количество футболок, заказанных в разные даты.

Выбор диапазона ячеек A13:B17

Нажатие команды "Сортировка"

Выбор столбца для сортировки

Нажимаем ОК

Отсортированный диапазон ячеек

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

Небольшая опечатка в ячейке A18, вызывающая неправильную сортировку

Пользовательская сортировка

Иногда вы можете обнаружить, что параметры сортировки по умолчанию не могут сортировать данные в нужном вам порядке. К счастью, Excel позволяет создавать настраиваемый список для определения собственного порядка сортировки.

Чтобы создать пользовательскую сортировку:

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

Выбор ячейки D2

Нажатие команды "Сортировка"

Выбор пользовательского списка. из поля Заказ:

Создание пользовательского списка

Нажмите OK, чтобы выбрать собственный список

Нажмите "ОК", чтобы отсортировать лист

Таблица отсортирована по размеру футболки

Чтобы отсортировать по формату ячейки:

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

Выбор ячейки E2

Нажатие команды "Сортировка"

Выбор сортировки по цвету ячейки

Выбор цвета ячейки для сортировки

Лист, отсортированный по цвету ячейки

Уровни сортировки

Если вам нужен больший контроль над сортировкой данных, вы можете добавить несколько уровней к любой сортировке. Это позволяет сортировать данные по нескольким столбцам.

Чтобы добавить уровень:

В приведенном ниже примере мы отсортируем лист по номеру классной комнаты (столбец A), а затем по фамилии (столбец C).

Выбор ячейки A2

Нажатие команды "Сортировка"

Нажав кнопку "Добавить уровень"

Рабочий лист отсортирован по номеру классной комнаты и фамилии

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

Изменение приоритета сортировки для столбца

Вызов!

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

Изображение: Wachiwit/Shutterstock

Windows: обязательно к прочтению

Сортировка в Excel проста; вы буквально выбираете опцию, а Excel делает все остальное. Иногда, если Excel не уверен, сколько столбцов включить в сортировку, он спросит, но в остальном сортировка — одна из самых простых задач, которые вы будете выполнять в Excel. Я говорю о сортировке по возрастанию и убыванию, которые не всегда будут адекватными. Например, что, если вы хотите отсортировать по дням недели: понедельник, вторник, среда и т. д.? Простая сортировка не поможет.Вот почему Excel включает функцию пользовательской сортировки. Вы можете создать уникальный порядок сортировки, например дни недели. В этой статье показано, как создать пользовательскую сортировку, если у вас нестандартный порядок сортировки.

Я использую рабочий стол Microsoft 365 в 64-разрядной системе Windows 10, но вы можете использовать более ранние версии. Excel Online запустит существующую настраиваемую сортировку, но вы не сможете создать ее онлайн. Для вашего удобства вы можете скачать демонстрационные файлы .xlsx и .xls. В этой статье предполагается, что у вас есть базовые навыки работы с Excel, но даже новичок должен быть в состоянии следовать инструкциям для достижения успеха.

Как сортировать по терминам в Excel

Пользовательская сортировка определяется вами. Например, вам может понадобиться отсортировать футболки по размерам: маленькие, средние, большие и очень большие. Или вы можете сортировать температуры по холодным, теплым и горячим. Обычная сортировка не может справиться с такими требованиями. В этом случае я буду называть эти элементы сортировки «терминами». К счастью, с помощью функции сортировки можно легко определить пользовательскую сортировку, которая обрабатывает термины.

Существует два способа запустить пользовательскую сортировку:

На вкладке "Главная" нажмите "Сортировка" в группе "Редактирование" и выберите "Пользовательская сортировка".
На вкладке "Данные" нажмите "Сортировка" в разделе "Сортировка и сортировка". Группа фильтров.

Теперь давайте возьмем надуманный пример. Я делаю это потому, что он настолько уникален, и у многих из вас будут особые требования к сортировке, которые никому, кроме вас, непонятны. На рисунке A показан простой набор данных регионов. Вы можете сортировать по алфавиту, как по возрастанию, так и по убыванию. Но давайте вместо этого предположим, что вам нужен определенный порядок сортировки: юго-запад, центр, северо-запад. Ни восходящая, ни нисходящая сортировка не будут работать. Юго-запад, Центральный и Северо-запад — это условия сортировки.

Рисунок А

Чтобы создать эту сортировку, сделайте следующее:

Нажмите в любом месте набора данных.
Перейдите на вкладку "Данные", а затем нажмите "Сортировка" в разделе "Сортировка". Группа фильтров. (Этот маршрут требует на один клик меньше, чем вкладка "Главная".)
В появившемся диалоговом окне выберите «Регион» в раскрывающемся списке «Сортировать по», поскольку вы хотите отсортировать значения по региону.
Оставьте сортировку по значениям ячеек по умолчанию.
В раскрывающемся списке "Порядок" выберите "Пользовательская сортировка". Откроется новое диалоговое окно, в котором можно создать пользовательскую сортировку (список).
В элементе управления "Записи списка" введите Юго-запад, Центральный, Северо-запад – условия сортировки в том порядке, в котором вы хотите их отсортировать (рис. A).
Нажмите «Добавить», чтобы переместить новый список в элемент управления «Пользовательские списки» слева (рис. Б).
Нажмите «ОК», чтобы вернуться в диалоговое окно «Сортировка», в котором теперь будет отображаться новый список в элементе управления «Порядок» (рис. C).
Нажмите "ОК", чтобы отсортировать набор данных.

Рисунок Б

Рисунок C

Как видно на рисунке D, набор данных теперь сортируется по пользовательскому списку. Сначала сортируется Юго-Запад, затем Центральный, а затем Северо-Западный. Пользовательские сортировки не всегда имеют смысл, но полезно знать, как это работает.

Рисунок D

Чтобы повторно использовать сортировку, нажмите «Сортировка» и выберите «Пользовательская сортировка» в раскрывающемся списке «Порядок». Выберите список в элементе управления «Пользовательские списки» и нажмите «ОК».

Возможно, вам интересно, может ли набор данных иметь более одной пользовательской сортировки. Да, оно может. Давайте создадим вторую пользовательскую сортировку, которая сортирует значения персонала в следующем порядке: Роза, Джун, Джеймс, Марта, Люк, Марк. Для этого повторите приведенные выше инструкции до шага 3 и выберите «Персонал» в раскрывающемся списке «Сортировать по». На шаге 6 введите Rosa, June, James, Martha, Luke, Mark, как показано на рис. E. Нажмите «Добавить», а затем дважды нажмите «ОК». На рисунке F показаны результаты. Вы можете добавить столько пользовательских сортировок, сколько вам нужно.

Рисунок Д

Рисунок F

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

Оставайтесь с нами

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

Еженедельный информационный бюллетень Microsoft

Будьте инсайдером Microsoft в своей компании, прочитав эти советы, рекомендации и памятки по Windows и Office.

Читайте также: