Сортировка и фильтрация данных

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

Правила сортировки. Общие правила

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

Общие правила сортировки
Поскольку при сортировке Microsoft Excel автоматически определяет связанный диапазон данных, сортируемый диапазон не должен иметь пустых столбцов. Наличие пустых строк допускается, но не рекомендуется.

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

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

Порядок сортировки

По возрастанию
При сортировке по возрастанию используется следующий порядок.

Числа
Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

Буквенно-цифровая сортировка
При сортировке алфавитно-цифрового текста сравниваются значения по знакам слева направо. Например, если ячейка содержит текст "Дом100", она будет поставлена после ячейки, содержащей запись "Дом1", и перед ячейкой, содержащей запись "Дом12".
Текст, в том числе содержащий числа, сортируется в следующем порядке: 0 1 2 3 4 5 6 7 8 9 (пробел) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

Буквенно-цифровая сортировка
Апострофы (') и дефисы (-) игнорируются с единственным исключением: если две строки текста одинаковы, не считая дефиса, текст с дефисом ставится в конец.
Если предназначенный для сортировки столбец содержит как числа, так и числа с текстом (например, 1, 1а, 2, 2а), все они должны быть отформатированы как текст. В противном случае после сортировки первыми будут располагаться числа, а за ними числа с текстом.

Логические значения
Логическое значение ЛОЖЬ ставится перед значением ИСТИНА.

Значения ошибки
Все значения ошибки равны.

Пустые значения
Пустые значения всегда ставятся в конец.

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

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

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

Простая сортировка
Название команды в меню зависит от типа сортируемых данных (числа, текст или календарные данные).

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

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

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

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

Настройка сортировки по формату ячеек
После выбора всех необходимых цветов (значков) нажмите кнопку ОК.

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

Сортировка по нескольким столбцам
Сортировку можно осуществлять по нескольким столбцам для группировки данных с одинаковыми значениями в одном столбце и последующего осуществления сортировки другого столбца или строки в этих группах с одинаковыми значениями. Например, если взять столбцы "Отдел" и "Сотрудник", сначала можно отсортировать строки по столбцу "Отдел" (для группировки всех сотрудников одного отдела), а затем по имени (для расположения имен сотрудников в каждом отделе в алфавитном порядке). Можно одновременно осуществлять сортировку по 64 столбцам.

Сортировка по нескольким столбцам
Выделите одну любую ячейку в диапазоне сортируемых данных.
Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная (см. рис.11.6) и выберите команду Настраиваемая сортировка или нажмите кнопку Сортировка группы Сортировка и фильтр вкладки Данные (см. рис.11.7).

Сортировка по нескольким столбцам
В окне Сортировка в раскрывающемся списке Столбец выберите название столбца, по данным которого будет выполняться сортировка. В раскрывающемся списке Сортировка выберите признак сортировки (значение, цвет ячейки, цвет шрифта или значок ячейки). Затем щелкните по стрелке раскрывающегося списка Порядок и выберите направление сортировки или цвет (значок).

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

Сортировка по нескольким столбцам
После выбора всех необходимых уровней сортировки нажмите кнопку ОК.

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

Настройка сортировки по нескольким столбцам
В появившемся предупреждении установите переключатель сортировать в пределах указанного диапазона и нажмите кнопку Сортировка.

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

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

Установка фильтра
Фильтры можно установить для любого диапазона, расположенного в любом месте листа. Диапазон не должен иметь полностью пустых строк и столбцов, отдельные пустые ячейки допускаются.
Выделите одну любую ячейку в диапазоне, для которого устанавливаются фильтры.
Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите команду Фильтр (рис.11.12) или нажмите кнопку Фильтр группы Сортировка и фильтр вкладки Данные (см. рис.11.7)

Установка фильтров
После установки фильтров в названиях столбцов таблицы появятся значки раскрывающих списков (ячейки строки A1:F1 в таблице на рис).

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

Установка фильтров
Фильтры автоматически устанавливаются при оформлении "таблицы" и автоматически удаляются при преобразовании "таблицы" в обычный диапазон.

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

Использование фильтра
Заголовки строк листа, выбранных из таблицы с помощью фильтра, отображаются синим цветом. На значках раскрывающихся списков в названиях столбцов, по которым была произведена выборка, появляется особая отметка. В строке состояния окна Excel в течение некоторого времени отображается текст с указанием количества найденных записей и общего количества записей в таблице. Например, в таблице на рис. произведен отбор данных по столбцу B (2009) и столбцу F (4,8).

Таблица с отобранными данными с использованием фильтра
Количество столбцов, по которым производится выборка, не ограничено.

Отбор по значению
Можно выбрать строки по одному или нескольким значениям одного или нескольких столбцов.
Щелкните по значку раскрывающегося списка столбца, по которому производится отбор.
Отметьте флажками значения, по которым производится отбор. Нажмите кнопку ОК.

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

Отбор по формату
Можно выбрать строки по цвету ячейки, цвету текста или значку одного или нескольких столбцов.
Щелкните по значку раскрывающегося списка столбца, по которому производится отбор.
Выберите команду Формат по цвету, а затем в подчиненном меню выберите цвет ячейки, цвет шрифта или значок. Нажмите кнопку ОК.

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

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

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

Отбор по условию для дат
При использовании условий: До, После или Между установите значение условия в окне Пользовательский автофильтр. Значение условия можно выбрать из списка или ввести с клавиатуры. Можно также щелкнуть по кнопке Выбор даты и выбрать значение даты из календаря.

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

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

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

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

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