Что такое пользовательский порядок сортировки кратко

Обновлено: 17.05.2024

9 .3 Сортировка списков
MS Excel предоставляет различные способы сортировки списков. Можно сортировать строки или столбцы в возрастающем или убывающем порядке. При сортировке строк изменяется порядок их следования, а порядок следования столбцов остается неизменным. Заголовок обычно не сортируется.
Для сортировки всего списка необходимо выделить любую ячейку (но не диапазон) и выполнить команду меню Данные - Сортировка. Excel проведет анализ списка для определения размера списка, определит строку заголовка, исключаемую из сортировки, и выведет на экран диалоговое окно Сортировка диапазона (см. рис.).

Сортировка производится по трем ключам: Сначала по первому, указанному пользователем в поле Сортировать по, затем в пределах строк, у которых эти поля совпадают, будет произведена сортировка по второму ключу, установленному в поле Сортировка. Сортировка по третьему ключу производится только для строк с одинаковыми ключевыми (первым и вторым) полями. В каждом ключе можно установить переключатель По возрастанию или По убыванию. Чтобы заголовки списков не включались в сортировку нужно установить переключатель в положение Подписям. После щелчка на кнопке ОК будет выполнена сортировка списка (см.рис.)
.
Для отмены результатов сортировки нужно сразу выполнить команду меню Правка - Отменить.
Упражнение 18. Откройте рабочую книгу "Списки.xls" и выполните сортировку списка Операции банка в соответствии с технологией, приведенной выше.

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

Если выделить не одну ячейку, а несколько строк и столбцов, то будет сортироваться только эта часть списка.

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

Для сортировки по столбцам нужно нажать в диалоговом окне сортировки кнопку Параметры. Откроется диалоговое окно Параметры сортировки (см. рис.), в котором нужно активизировать переключатель Столбцы диапазона.

В качестве ключей сортировки в этом случае выбираются не столбцы, а строки.

Упражнение 19. Откройте рабочую книгу "Списки.xls" Выполните сортировку списка по столбцам в соответствии с технологией, приведенной выше . Результат сортировки представлен на рисунке.

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

Иногда возникает необходимость применения нестандартного порядка сортировки. Например, сортировку не в алфавитном порядке, а в виде списка: № п.п., Дата операции, № счета, Кредит, Дебет. Для создания пользовательского порядка сортировки нужно выполнить следующие действия:
•Выполнить команду меню Сервис -Параметры.
•Активизировать вкладку Списки.
•В появившейся области Списки выделить Новый Список.
•В области Элементы списка ввести через запятую или на новых строках каждый элемент пользовательского списка (см. рис.).

•Нажать кнопку Добавить.
•После появления пользовательского списка в области Списки нажать ОК.
•В обычном порядке выделяется диапазон списка для сортировки, включается команда Сортировка и в диалоговом окне Сортировка нажимается кнопка Параметры.
•В диалоговом окне Параметры сортировки выбрать необходимый пользовательский порядок сортировки и нажать на ОК (рис.47).

После дальнейших стандартных действий получим отсортированный список по заданному порядку (см. рис.)
.
Упражнение 20 Откройте рабочую книгу "Списки.xls" Выполните сортировку списка в соответствии с рассмотренным пользовательским порядком, используя технологию, приведенную выше.

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

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

Создание пользовательской сортировки в Excel

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

  1. Выделите любую ячейку в таблице Excel, которому необходимо сортировать. В данном примере мы выделим ячейку D2.
  2. Откройте вкладку Данные, затем нажмите команду Сортировка.
  3. Откроется диалоговое окно Сортировка. Выберите столбец, по которому Вы хотите сортировать таблицу. В данном случае мы выберем сортировку по размеру футболок. Затем в поле Порядок выберите пункт Настраиваемый список.
  4. Появится диалоговое окно Списки. Выберите НОВЫЙ СПИСОК в разделе Списки.
  5. Введите размеры футболок в поле Элементы списка в требуемом порядке. В нашем примере мы хотим отсортировать размеры от меньшего к большему, поэтому введем по очереди: Small, Medium, Large и X-Large, нажимая клавишу Enter после каждого элемента.
  6. Щелкните Добавить, чтобы сохранить новый порядок сортировки. Список будет добавлен в раздел Списки. Убедитесь, что выбран именно он, и нажмите OK.
  7. Диалоговое окно Списки закроется. Нажмите OK в диалоговом окне Сортировка для того, чтобы выполнить пользовательскую сортировку.
  8. Таблица Excel будет отсортирована в требуемом порядке, в нашем случае — по размеру футболок от меньшего к большему.

Сортировка в Excel по формату ячейки

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

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

В этой статье

Сортировка небольшого конечного количества уникальных значений полей

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

Сначала создайте таблицу, которая содержит три поля: "Название", "Имя" и "Фамилия". В поле "Название" введите следующие значения для четырех новых записей:

Inside Sales Разуваев

Менеджер по продажам

Вице-президент, отдел продаж

В полях "Имя" и "Фамилия" из четырех записей введите нужные имена.

Теперь откройте таблицу в представлении таблицы.

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

Добавьте в сетку нужные поля, такие как "Имя", "Фамилия" и "Название".

Щелкните правой кнопкой мыши строку поля в первом пустом столбце, выберите "Масштаб"и введите следующее выражение:

IIf ([Title] = "Vice President, Sales", 1, IIf ([Title] = "Sales Manager", 2, IIf ([Title] = "Sales Representative", 3, IIf ([Title] = "Inside Sales Вуалю", 4, 5))))

Название — это имя поля, которое содержит ранжировали значения. Значения, заключенные в кавычках, являются отдельными значениями, храниммими в поле. После него будет ранж, присвоенный ему. Предыдущее выражение присваивает ранг от 1 до 4 четырем разным названиям и присваивает 5 всем остальным. При выполнении сортировки названия, не упомянутые в этом выражении, имеют одинаковый ранг (5).

В строке "Сортировка" для столбца, содержащего выражение, щелкните "По возрастанию".

на вкладке Главная в группе Сортировка и фильтр нажмите кнопку Переключить фильтр.

Функция IIf возвращает числвое значение, соответствующее значению в поле "Название". Например, если в поле "Название" имеется значение "Диспетчерпродаж", назначенное число будет 2. Если поле "Название" не указано в качестве аргумента функции (например, "Окружная пособка"), ему назначено число 5. Затем запрос отсортет эти числа по возрастанию.

Сортировка большого количества уникальных значений полей

Если нужно ранжировать большое количество значений, лучший способ отсортировать данные — создать таблицу подпапок.

Предположим, что таблица "Сотрудники" ссылается на более чем 50 разных заголовков. Сначала потребуется создать таблицу подготовить существующую таблицу для ее подготовки. Затем вы связываете две таблицы и создаете запрос.

Создание таблицы подпапок и изменение существующей таблицы

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

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

– выполнить команду меню Сервис/Параметры;

– щелкнуть на закладке Списки диалогового окна Параметры;

– в поле Списки выбрать Новый список;

– в поле Элементы списка ввести элементы нового пользовательского списка, отделяя их друг от друга запятыми или записывая каждый элемент с новой строки (рис. 3.8);


Рис. 3.8 Вкладка Списки диалогового окна Параметры

– щелкнуть на кнопке Добавить;

– после появления пользовательского списка в поле Списки щелкнуть на кнопке ОК;

– выделить диапазон списка, который нужно сортировать, выполнить команду меню Данные/Сортировкаи в диалоговом окне Сортировка диапазонащелкнуть на кнопке Параметры;

– в диалоговом окне Параметры сортировкивыбрать нужный список, устанавливающий порядок сортировки и щелкнуть на кнопке ОК (рис. 3.9, 3.10);

– в открывшемся Диалоговом окне Сортировкадиапазона щелкнуть на кнопке ОК - выполнится сортировка списка (рис. 3.11).


Рис.3.9 Диалоговое окно Параметры сортировки


Рис. 3.10 Выбор порядка сортировки


Рис. 3.11 Результат сортировки списка

ФИЛЬТРАЦИЯ СПИСКОВ

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

Фильтрация списков с помощью инструмента Автофильтр

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

Чтобы применить инструмент Автофильтр,нужно выполнить операции:

– установить курсор в любую ячейку списка;

– выполнить команду меню Данные/Фильтр/Автофильтр(рис. 3.12), в фильтруемом списке рядом с названиями столбцов появятся кнопки со стрелками раскрывающихся списков (рис. 3.13);


Рис.3.12 Запуск инструмента Автофильтр

– щелкнуть на стрелке столбца, данные которого являются условием фильтрации, и в раскрывшемся списке выбрать значение для условия фильтрации (рис. 3.14) - выполнится фильтрация по заданному условию (рис. 3.15).

На рисунке 3.15 показан результат фильтрации списка по номеру счета со значением 2.


Рис. 3.13 Кнопки раскрывающихся списков


Рис. 3.14Выбор условия фильтрации


Рис. 3.15 Результат фильтрации списка

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

Элемент Первые 10 в списке условий (рис. 3.14) позволяет отфильтровать заданное число (заданный .процент) наибольших или наименьших элементов в списке. Выбор этого условия выводит диалоговое окно Наложение условий по списку (рис. 3.16).


Рис. 3.16 Диалоговое окно Наложение условий по списку

Результат фильтрации первых 11 значений приведен на рис. 3.17.


Рис. 3.17Результат фильтрации первых 11 значений

Пример 3.1 Требуется определить, какова максимальная сумма прихода по счету 10.

Решение

Выполним команду меню Данные/Фильтр/Автофильтр.

Раскроем список условий в столбце с названием № счета и выберем в нем значение 10.

Раскроем список условий в столбце с названием Приход и выберем в нем элемент Первые 10.

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


Щелкнем на кнопке ОК. Результат фильтрации приведен на рисунке 3.18.


Фильтрация с применением инструмента

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

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

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

– щелкнуть на стрелке столбца, данные которого являются условием фильтрации, и в раскрывшемся списке выбрать элемент Условие - откроется диалоговое окно Пользовательский автофильтр (рис. 3.19);

– установить в полях диалогового окна значения условий для фильтрации;

– щелкнуть на кнопке ОК.


Рис. 3.19 Диалоговое окно Пользовательский автофильтр

Пример 3.2 Требуется определить, сколько было приходных one раций с суммами величиной от 1000 до 2500 рублей включительно и счету 10.

Решение

Включим инструмент Автофильтр.

Щелкнем на стрелке в столбце с названием № счета и в раскрывшемся списке выберем элемент со значением 10.

Раскроем список в столбце с названием Приход и выберем в нем элемент Условие -откроется диалоговое окно Пользовательский автофильтр.

В диалоговом окне Пользовательский автофильтр в верхнем левом списке выберем элемент Больше или равно, а в поле правого списка введем число 1000, в нижнем левом списке выберем элемент Меньше или равно и в поле правого списка введем число 2500, установим переключатель с надписью И.

Щелкнем на кнопке ОК - получим результат фильтрации для заданных условий (рис. 3.19).



Таким образом, условию задачи отвечает запись в одной строке списка. Сумма приходной операции равна 1300 руб.

При выборе операций отношения И (ИЛИ) следует руководствоваться следующим:

– если из всего множества записей в исходном списке нужно отобрать подмножество, значения которого определены правой и левой границами (а, b) (замкнутое подмножество), то следует установить операцию отношения И;

– если из всего множества записей в исходном списке нужно отобрать подмножества, значения которых ограничены только справа или слева, то следует устанавливать операцию отношения ИЛИ (рис. 3.20).

Рис. 3.20 Выбор операций отношения

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

Решение

Включим инструмент Пользовательский автофильтр.

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


Результат выборки в соответствии с указанным условием приведен на рисунке 3.22.

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