Что такое пользовательский порядок сортировки
Секреты Excel. Создание своего порядка сортировки
Продолжаем нашу серию практических заметок про табличный процессор Microsoft Excel. Сегодня мы расскажем, как реализовать свой порядок сортировки данных в программе.
Табличный процессор Microsoft Excel умеет не только профессионально производить с данными математические вычисления и строить диаграммы. Работая с таблицей, оформленной как список, Excel может отобрать нужные данные в соответствии с указанными условиями, отсортировать столбец значений. Данные в Excel упорядочиваются по возрастанию или по убыванию в соответствии с типом значений, размещенным в столбце. Если в колонке записаны числа, после сортировки они разместятся от меньшего к большему или наоборот в зависимости от того, какой порядок сортировки был избран. Точно так же сортируются и текстовые данные, они располагаются либо по алфавиту, либо в порядке, обратном алфавитному.
Иногда данные необходимо отсортировать по порядку, который не совпадает с уже существующими. Например, таблицу, содержащую список сотрудников, необходимо упорядочить в соответствии с колонкой «занимаемая должность».
Для решения этой задачи необходимо создать свой порядок сортировки, после чего использовать его для сортировки списка.
Создание собственного порядка сортировки.
Установка параметров сортировки данных.
Выберите из списка собственный, только что созданный порядок сортировки и нажмите «ОК». Полученный список будет отсортирован в соответствии с созданным вами порядком сортировки.
Результат сортировки с использованием своего порядка.
Ваш персональный компьютер
Пользовательский порядок сортировки
Как уже отмечалось, Excel использует свой порядок сортировки, получивший название обычный (стандартный) или по умолчанию. В то же время имеется возможность создания нестандартного порядка сортировки, получившего название пользовательского порядка сортировки. При желании пользователь может создать несколько разных пользовательских порядков сортировки.
Пользовательский порядок сортировки можно создать либо из существующих на листе элементов, либо с помощью непосредственного ввода списка.
Если ряд элементов, который необходимо представить в виде пользовательского порядка сортировки, был набран заранее, то надо проделать следующие действия:
Выделите необходимые элементы на листе.
Выберите команду кнопка Офис, Параметры Excel, кнопка Изменить списки.
Для подключения выделенного списка нажмите кнопку Импорт.
Порядок создания нового списка с помощью непосредственного ввода следующий:
Выберите Новый список из списка Списки, а затем введите данные в поле Элементы списка, начиная с первого элемента.
После ввода каждой записи нажимайте клавишу ENTER.
Нажмите кнопку Добавить после того, как список будет набран полностью.
Чтобы использовать пользовательский порядок сортировки, надо в окне Сортировка диапазона нажать на стрелку поля Порядок и выбрать Настраиваемый список
Пользовательский порядок сортировки
Иногда возникает необходимость применения нестандартного порядка сортировки. Например, сортировку не в алфавитном порядке, а в виде списка: Группа, Фамилия, Имя. Для создания пользовательского порядка сортировки нужно выполнить следующие действия:
– выполнить команду меню Сервис/Параметры;
– щелкнуть на закладке Списки диалогового окна Параметры;
– в поле Списки выбрать Новый список;
– в поле Элементы списка ввести элементы нового пользовательского списка, отделяя их друг от друга запятыми или записывая каждый элемент с новой строки (рис. 3.8);
Рис. 3.8 Вкладка Списки диалогового окна Параметры
– щелкнуть на кнопке Добавить;
– после появления пользовательского списка в поле Списки щелкнуть на кнопке ОК;
– выделить диапазон списка, который нужно сортировать, выполнить команду меню Данные/Сортировкаи в диалоговом окне Сортировка диапазонащелкнуть на кнопке Параметры;
– в диалоговом окне Параметры сортировкивыбрать нужный список, устанавливающий порядок сортировки и щелкнуть на кнопке ОК (рис. 3.9, 3.10);
Рис.3.9 Диалоговое окно Параметры сортировки
Рис. 3.10 Выбор порядка сортировки
Рис. 3.11 Результат сортировки списка
ФИЛЬТРАЦИЯ СПИСКОВ
Под фильтрацией списков понимается выделение из всего списка только тех строк, значения полей которых удовлетворяют заданным критериям. Табличный процессор имеет несколько инструментов с различными функциональными возможностями, которые позволяют выполнять фильтрацию несколькими способами.
Фильтрация списков с помощью инструмента Автофильтр
Чтобы применить инструмент Автофильтр,нужно выполнить операции:
– установить курсор в любую ячейку списка;
– выполнить команду меню Данные/Фильтр/Автофильтр(рис. 3.12), в фильтруемом списке рядом с названиями столбцов появятся кнопки со стрелками раскрывающихся списков (рис. 3.13);
Рис.3.12 Запуск инструмента Автофильтр
На рисунке 3.15 показан результат фильтрации списка по номеру счета со значением 2.
Рис. 3.13 Кнопки раскрывающихся списков
Рис. 3.14Выбор условия фильтрации
Рис. 3.15 Результат фильтрации списка
Последовательно фильтруя список по значениям разных столбцов, можно получить результат по нескольким условиям, которые удовлетворяются совместно.
Рис. 3.16 Диалоговое окно Наложение условий по списку
Результат фильтрации первых 11 значений приведен на рис. 3.17.
Рис. 3.17Результат фильтрации первых 11 значений
Пример 3.1 Требуется определить, какова максимальная сумма прихода по счету 10.
Решение
Выполним команду меню Данные/Фильтр/Автофильтр.
Раскроем список условий в столбце с названием № счета и выберем в нем значение 10.
Раскроем список условий в столбце с названием Приход и выберем в нем элемент Первые 10.
В раскрывшемся окне диалога Наложение условия по списку в группе Показать установим значения 1 и наибольших.
Щелкнем на кнопке ОК. Результат фильтрации приведен на рисунке 3.18.
Фильтрация с применением инструмента
Пользовательский автофильтр
В инструменте Автофильтр в качестве условий фильтрации применяются простые условия равенства по значению в столбце. Однако при решении задач бывает необходимо использовать условия больше (меньше), не равно.
Для решения таких задач в случае несложных условий можно применить инструмент Пользовательский автофильтр. Для того чтобы применить инструмент Пользовательский автофильтр, нужно выполнить операции:
– установить в полях диалогового окна значения условий для фильтрации;
– щелкнуть на кнопке ОК.
Рис. 3.19 Диалоговое окно Пользовательский автофильтр
Пример 3.2 Требуется определить, сколько было приходных one раций с суммами величиной от 1000 до 2500 рублей включительно и счету 10.
Решение
Включим инструмент Автофильтр.
Щелкнем на стрелке в столбце с названием № счета и в раскрывшемся списке выберем элемент со значением 10.
В диалоговом окне Пользовательский автофильтр в верхнем левом списке выберем элемент Больше или равно, а в поле правого списка введем число 1000, в нижнем левом списке выберем элемент Меньше или равно и в поле правого списка введем число 2500, установим переключатель с надписью И.
Таким образом, условию задачи отвечает запись в одной строке списка. Сумма приходной операции равна 1300 руб.
При выборе операций отношения И (ИЛИ) следует руководствоваться следующим:
– если из всего множества записей в исходном списке нужно отобрать подмножество, значения которого определены правой и левой границами (а, b) (замкнутое подмножество), то следует установить операцию отношения И;
– если из всего множества записей в исходном списке нужно отобрать подмножества, значения которых ограничены только справа или слева, то следует устанавливать операцию отношения ИЛИ (рис. 3.20).
Рис. 3.20 Выбор операций отношения
Пример 3.3Требуется получить сведения по операциям клиентов, фамилии которых начинаются с буквы П.
Решение
Включим инструмент Пользовательский автофильтр.
В полях диалогового окна Пользовательский автофильтрустановим параметры фильтрации, как показано на рисунке 3.21.
Результат выборки в соответствии с указанным условием приведен на рисунке 3.22.
Сортировка и фильтрация данных в Excel
Простая и пользовательская сортировка, как настроить и убрать фильтр, “умные таблицы”
Таблицы с огромным количеством числовой и текстовой информации часто встречаются во внутренних документах крупных и мелких компаний. Среди множества строк легко потерять важную информацию из виду. Разработчики из компании Microsoft понимают это, поэтому в программе Microsoft Excel присутствуют опции сортировки и фильтрации данных. Разобраться в них без подсказок достаточно сложно. Попробуем понять, как правильно настраивать сортировку и фильтры в обычных и сводных таблицах.
Обычная (простая) сортировка
Эта опция называется простой, потому что ее несложно использовать даже новичкам. В результате сортировки информация автоматически организуется в установленном порядке. Например, можно составить строки таблицы по алфавиту или упорядочить числовые данные от мелких к крупным.
Быстрая организация данных в таблице возможна благодаря набору инструментов «Сортировка и фильтр». Эта кнопка находится на вкладке «Главная», в правой ее части. Опции сортировки соответствуют формату выбранных ячеек.
Пользовательская сортировка данных
Иногда необходимо построить строки таблицы в определенном порядке, который установлен условиями задачи. Например, возникает нужда в сортировке по нескольким параметрам, а не только по одному. В таком случае стоит обратить внимание на настраиваемую сортировку в Excel.
Дополнительная информация! В случае, если таблица начинается с шапки, нужно поставить галочку в графе, расположенной в правом верхнем углу экрана.
Обратите внимание на пометку «Затем по». Она показывает, что приоритетными для сортировки таблицы являются условия первой строки.
Настройки можно изменить, выбрав одну из ячеек рассортированного диапазона и открыв окно пользовательской/настраиваемой сортировки.
Как настроить фильтр в таблице
Фильтры в Excel позволяют временно скрыть часть информации с листа и оставить только самое необходимое. Информация не пропадает навсегда – изменение настроек вернет ее на лист. Разберемся, как фильтровать строки электронной таблицы.
Внимание! Перейти к фильтрации можно также через вкладку «Данные». На ней располагается большая кнопка «Фильтр» с воронкой.
Как убрать фильтр в таблице
Не обязательно расставлять галочки обратно в меню, чтобы восстановить прежний вид таблицы. Воспользуемся инструментами Microsoft Excel, чтобы отменить результаты фильтрации – есть два способа сделать это.
Как создать «умную таблицу»
Подключение опций сортировки и фильтрации к таблице можно совместить с выбором цветовой темы для нее. Такие таблицы называют «умными». Выясним, как сделать «умным» обычный диапазон ячеек.
Существует еще один метод создания «умной» таблицы:
Как сделать фильтр в Excel по столбцам
Таблицы Excel фильтруются только по строкам. В меню, которое появляется после нажатия на кнопку со стрелкой в шапке столбца, нельзя убрать все галочки, то есть нельзя скрыть целый столбец. Вся информация в диапазоне ячеек важна при фильтрации и сортировке, поэтому отфильтровать один или несколько столбцов не получится.
Сортировка по нескольким столбцам в Excel
Когда говорят о сортировке по нескольким столбцам, подразумевается, что для упорядочивания таблицы применяют усложненные настройки с указанием двух или более столбцов.
Важно! Количество уровней для сортировки ограничено только количеством столбцов или строк в таблице.
Автофильтр
Автоматическая фильтрация строк таблицы возможна с помощью меню фильтров. Эта функция позволяет установить более сложные настройки и создать уникальный фильтр. Набор автоматических фильтров меняется в зависимости от формата ячеек. Применяются текстовые и числовые фильтры.
Рассмотрим опцию «Настраиваемый фильтр». С ее помощью пользователи могут самостоятельно установить нужные настройки фильтрации.
Стоит обратить внимание на пункты И/ИЛИ в окне настройки автофильтра. От них зависит то, как будут применены настройки – вместе или частично.
Срезы
Программа Microsoft Excel позволяет прикрепить к таблицам интерактивные элементы для сортировки и фильтрации – срезы. После выхода версии 2013-о года появилась возможность подключать срезы к обычным таблицам, а не только к сводным отчетам. Разберемся, как создать и настроить эти опции.
Создание срезов
Обратите внимание! Если ваша версия Microsoft Excel старше 2013-го года, составить срез для обычной таблицы будет невозможно. Функция применима только к отчетам в формате сводных таблиц.
Срезы выглядят, как диалоговые окна со списками кнопок. Названия пунктов зависят от того, какие элементы таблицы были выбраны при создании среза. Чтобы отфильтровать данные, нужно кликнуть по кнопке в одном из списков. Фильтрация по нескольким диапазонам данных возможна, если нажать кнопки в нескольких срезах.
Форматирование срезов
Редактирование внешнего вида срезов и их взаимодействия с другими элементами возможно с помощью специальных инструментов. Попробуем изменить цветовую схему.
Если внимательно изучить инструкции по настройке сортировки и фильтров, можно без труда справиться с расстановкой строк в нужном порядке или быстро временно убрать с листа ненужную информацию. Программа оснащена инструментами для работы с обычными и сводными таблицами. Доступны опции сортировки и фильтрации для любых целей – простые и усложненные. Более сложные инструменты позволяют получить более точный результат.
Сортировка в пользовательском порядке
Когда возникает необходимость произвести сортировку в порядке, отличном от алфавитного или числового (например, по дням недели или месяцам года, заданным в виде текста, а не даты), необходимо задать пользовательский порядок сортировки, который должен использоваться для сравнения значений (См. выше). После чего сортировка в таком пользовательском порядке выполняется простым способом:
1. Выделить любую ячейку в списке.
2. Вызвать диалоговое окно Сортировка.
3. Выбрать в списке Сортировать по поле для сортировки (В данном примере – Регион).
4. Выбрать в списке Порядок опцию Настраиваемый список
5. В открывшемся диалоговом окне Списки (См. Рис.11) выбрать ранее созданный вариант пользовательского порядка сортировки (В данном примере – Север, Юг, Запад, Восток).
6. Нажать кнопку ОК в каждом из окон диалога, чтобы произвести сортировку. Записи переставляются в соответствии с выбранным порядком сортировки(См. Рис.12).
Рис. 12. Диалоговое окно Сортировка – опция Настраиваемый список
Рис. 12. Результат пользовательского порядка сортировки
5.Использование Фильтра для поиска записей
Иногда требуется временно скрыть все записи в списке кроме тех, которые удовлетворяют некоторому критерию:
Выделить любую ячейку в списке, затем:
В верхней ячейке каждого столбца появляется раскрывающийся список. Щелкнуть мышью стрелку списка в том столбце, который используется для отбора записей. Появляется список возможных вариантов фильтрации(См. Рис.13).Чтобы отобразить определенную группу записей, надо выбрать нужные критерии в одном или нескольких таких списках.
Пример. Отобразить сведения о январских продажах объемом в 1,000 руб. для всех работников. Для этого следует последовательно установить Фильтр в двух столбцах: выбрать из раскрывающегося списка в столбце Месяц значение Январь, а в столбце Продажа — 1, 000. (См. Рис.14).
|
|
|
|
Рис. 13. Фильтр столбца «Месяц» и команда Настраиваемый фильтр
|
|
Рис. 14. Результат фильтрации в двух столбцах
Excel скрывает все записи, не удовлетворяющие указанному критерию. Чтобы снова отобразить все записи следует нажать кнопку Очистить. Удаление раскрывающихся списков Фильтра производится выключением кнопки Фильтр. Кнопка Применить повторно выполняет повторные операции фильтрации или сортировки в столбце, если в нем были произведены изменения.
6.Создание Фильтра по стандартным условиям
Когда возникает необходимость в отборе записей по диапазону текстовых или числовых значений (для столбцов, содержащих текстовые или числовые данные соответственно) необходимо при установке Фильтра выбрать подменю Текстовые фильтры (См. Рис.13) или Числовые фильтры (См. Рис.15)
Рис. 15. Подменю Числовые фильтры
В этих подменю содержатся различные команды, позволяющие выполнить операции фильтрации содержимого столбцов по самым разнообразным критериям.
7.Создание Пользовательского Фильтра и Расширенного Фильтра
Кроме того, команда Настраиваемый фильтр позволяет выполнить создание т.н. Пользовательского Фильтра, вызывая соответствующее диалоговое окно, независящее от содержимого столбца (текстового или числового)(См. Рис.16).
Рис. 16. Диалоговое окно Пользовательский фильтр
Верхний левый раскрывающийся список позволяет указать оператор отношения, который будет применяться в фильтре. Верхний правый раскрывающийся список позволяет ввести границу для критерия.
При необходимости задания второго диапазона надо установить переключатель И (для записей, удовлетворяющих обоим критериям) или переключатель ИЛИ (для записей, удовлетворяющих хотя бы одному из критериев).
Затем нужно выбрать оператор отношения из нижнего левого списка и границу критерия из нижнего правого списка значений. Таким образом, можно построить достаточно сложный и эффективный Пользовательский Фильтр.
8.Использование Расширенного Фильтра
Для выполнения отбора диапазона ячеек с помощью сложных условий используется инструмент Расширенный Фильтр.
ВкладкаДанные – Группа Инструментов Сортировка и фильтр – кнопка Дополнительно
– диалоговое окно Расширенный Фильтр
Между инструментом Расширенный Фильтр и инструментом Фильтр есть несколько важных отличий:
· Расширенные условия отбора вводятся в отдельный диапазон условий листа над диапазоном ячеек или таблицей, которые требуется отфильтровать.
· В Microsoft Office Excel в качестве источника расширенных условий отбора используется отдельный диапазон условий в диалоговом окне Расширенный фильтр.
Пример. В рассматриваемой задаче определить представителей, реализовавших Книги на сумму в 1000 руб.
(См. Рис.16).
Шаг 1. Вставить не менее трех пустых строк над диапазоном, который будет использоваться как диапазон условий отбора.
Диапазон условий отбора должен включать заголовки столбцов. Для этого скопировать шапку таблицы в строку А1.
Шаг 2.Ввести в строку А2условия отбора, начиная со знака « = ». В данном примере в ячейку D2 ввести = 1000, а в ячейку Е2 ввести =”=Книги ”
Шаг 3.Убедиться, что между значениями условий и таблицей имеется по крайней мере одна пустая строка.
Шаг 4.Вызвать диалоговое окноРасширенный Фильтр.
Шаг 5.В полеИсходный диапазон задать исходный диапазон фильтрации – А6:Е18.
Шаг 6. В полеДиапазон условий задать диапазон условий фильтрации – А1:Е2
Шаг 7.Выставить переключатель в опциюСкопировать результат в другое место
Шаг 8. В поле Поместить результат в диапазон задать диапазон результата – А20:Е31
Шаг 9.Нажать кнопкуОК (См. Рис.17).
Рис. 17. Диалоговое окно Расширенный фильтр – получение результата
9.Использование команды Итоги для организации списков
Инструмент Промежуточные итоги помогает упорядочить список посредством группировки записей с выводом промежуточных итогов, средних значений или другой вспомогательной информации. Он также применяется для вывода итоговой суммы в верхней или нижней части списка и облегчает суммирование числовых столбцов. Кроме того, инструмент Промежуточные итоги отображает список в виде структуры, что позволяет разворачивать и сворачивать разделы простыми щелчками мышью.
Включение промежуточных итогов в список производится следующим образом:
1.Организовать список так, чтобы записи каждой группы следовали в нем подряд. Самым простым способом является сортировка по тому полю, на котором основаны группы. Например, можно отсортировать список по имени Региону.
2. Выделить необходимый блок Списка (См. Рис.18).
Рис. 18. Диалоговое окно Промежуточные итоги
4.Выбрать из раскрывающегося списка При каждом изменении в группу, для которой определяются промежуточные итоги. Это должен быть тот же столбец, по которому сортировался список, — когда значение в группе изменяется, Excel вставляет новую строку и вычисляет промежуточный итог. В данном случае – Регион.
5.Выбрать из списка Операция функцию, которая будет использоваться для вычисления промежуточного итога. Чаще всего применяется функция СУММ (SUM), но имеются и другие варианты — они перечислены в Таблице 1.
6.Выбрать из списка Добавить итоги по столбец или столбцы для расчета промежуточного итога.
В данном случае – Объем. Установка вспомогательных флажков позволяет настроить вид вычисляемых итогов.
7.Нажать кнопку ОК, чтобы включить промежуточные итоги в список. Экран вместе с промежуточными итогами, символами структуры и общей суммой принимает следующий вид (См. Рис.19).
Рис. 19. Результат работы инструмента Промежуточные итоги
Функции | Действия |
Сумма (SUM) | Сложение чисел в группе |
Кол-во значений (COUNT) | Подсчет количества непустых ячеек в группе |
Среднее (AVERAGE) | Среднее арифметическое чисел в группе |
Максимум (МАХ) | Наибольшее число в группе |
Минимум (MIN) | Наименьшее число в группе |
Произведение (PRODUCT) | Результат перемножения всех чисел группы |
Кол-во чисел (COUNT NUMS) | Подсчет количества ячеек с числовыми значениями в группе |
Несмещенное отклонение (STDDEV) | Вычисление стандартного отклонения по выборке |
Смешенное отклонение (STDDEVP) | Вычисление стандартного отклонения по генеральной совокупности |
Несмещенная дисперсия (VAR) | Вычисление дисперсии по выборке |
Смешенная дисперсия (VARP) | Вычисление дисперсии по генеральной совокупности |
Инструмент Промежуточные итоги применяется каждый раз, когда возникает необходимость в перегруппировке записей или изменении вычислений. После завершения работы, если это необходимо, можно нажать кнопку Убрать все в диалоговом окне Промежуточные итоги.
10.Работа в Режиме структуры
Режим структуры, в котором оказывается список после выполнения команды Промежуточные итоги, напоминает Режим структуры в Word и позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле.
Кнопки, расположенные в верхней части левого поля, определяют количество выводимых уровней данных. Кнопки со значками «+» и « —» предназначены для свертки и развертки отдельных групп.
Если итоги, получаемые командой Промежуточные итоги не устраивают пользователя, организовать пользовательский Режим структуры ( с глубиною вложения уровней до 8-ми) можно с помощью инструментов ГруппыИнструментов Структура с Вкладки Данные.
Предварительно Список должен быть отсортирован по ключевому столбцу и вставлены пустые строки для организации пользовательских промежуточных и окончательных вертикальных итогов. (См. Рис.20).
Рис. 20. Таблица, подготовленная для организации Структуры в пользовательском порядке
Для организации первого (внешнего) уровня структуры выделяются все строки и выполняется команда Группировать. (См. Рис.21).Затем организуются группировки каждой вложенной группы (без итоговых строк). Соответственно для изменения уровней структуры используется команда Разгруппировать.
Кнопки справа от Списка и кнопки Отобразить детали и Скрыть детали позволяют представлять список в определенном виде.
Диалоговое окно Настройка, вызываемое кнопкой в правом нижнем углу Группы Инструментов Структура позволяет выполнить ряд соответствующих операций по настройке создаваемой Структуры. (См. Рис.22).
Рис. 21. Организация первого (внешнего) уровня Структуры.