Фильтрация списка

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

Excel предоставляет пользователю два способа фильтрации: с помощью автофильтра и с помощью расширенного фильтра.

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

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

когда условие отбора должно одновременно применяться к ячейкам двух и более столбцов;

когда к ячейкам одного столбца необходимо применить три и более условий отбора:

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

Чтобы обработать таблицу с помощью автофильтра, необходимо вначале выбрать в качестве активной любую ее ячейку. После этого в меню Данные \ Фильтр (Data\Filter) выполнить команду Автофильтр (AutoFilter). Как только команда будет выполнена, в

168

первой строке таблицы рядом с именем каждого поля появятся кнопки со стрелками. Нажатие любой из этих кнопок приводит к открытию соответствующего набора строк. Каждая строка набора представляет собой одно из неповторяющихся значений ячеек выбранного столбца. Дополнительно к значениям в каждом наборе будут присутствовать строки с именами [Все] ([АН]), [Первые 10...] ([Тор 10...]) и [Условие...] ([Custom...]).

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

Если для фильтрации по определенному полю выбрать строку [Первые 10...] ([Тор 10...]), то на экране появится диалоговое окно Наложение условия по списку (Тор 10 AutoFilter), в котором пользователь имеет возможность изменить условие: выбрать от 1 до 500 наибольших или наименьших элементов этого поля.
После этого па экране монитора отображается выбранное количество записей, отвечающих заданному условию.

Чтобы отфильтровать список по двум и более значениям, встречающимся в столбце, необходимо выбрать строку [Условие...] ([Custom...]). В появившимся диалоговом окне Пользовательский автофильтр (Custom AutoFilter) можно задать это условие отбора. Для этого используются операторы сравнения (>, =,

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

Последовательно нажимать кнопки с синими стрелками (т.е. кнопки полей, по которым была выполнена фильтрация) и в открывающихся при этом наборах строк выбирать строку [Все] ([АИ]).

Выполнить команду Показать все (Show All) меню Данные \ Фильтр (Data \ Filter).

Отказаться от применения Автофильтра. Для этого следует еще раз выполнить команду Автофильтр (AutoFilter) меню Данные \ Фильтр (Data \ Filter). Все кнопки автофильтра, которые были в первой строке списка, исчезнут, и на экране высветится таблица в ее первоначальном виде.

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

Диапазон критериев оформляется следующим образом: в первой строке записываются (или копируются) имена полей списка, для которых задаются условия отбора, а во второй и последующих строках вводятся непосредственно сами условия отбора.

С использованием рис. 13.1 рассмотрим некоторые примеры задания условий отбора (диапазонов критериев) расширенного фильтра.

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

169

поле "Должность" значение "Инженер-программист", в поле "ФИО сотрудника" - "Сидоров М.А.", а в поле "Дата получения премии" - все даты позже 15.02.97. В этом случае диапазон критериев будет выглядеть следующим образом:

	А	С	F
55	ФИО сотрудника	Должность	Дата получения премии
56	Сидоров М.А.	Инженер- программист	> 15.02.97

Обратите внимание на следующее:

1. Диапазон критериев начинается с 55 строки (отделен от списка рис. 13.1 одной пустой строкой).

2. Все условия отбора размещены в одной строке. Тем самым задается их взаимодействие по схеме "И" (требуется одновременное их выполнение).

Для соединения условий отбора по схеме "ИЛИ" необходимо каждое из них разместить в отдельной строке:

	А	С	F
59	ФИО сотрудника	Должность	Дата получения премии
60	Сидоров М.А.
61		Инженер-программист
62			>15.02.97

При задании оформленного таким образом диапазона критериев из списка будут выбраны все записи, содержащие либо значение "Сидоров М.А." в столбце "ФИО сотрудника", либо "Инженер-программист" в столбце "Должность", либо имеющие значение ">15.02.97" в столбце "Дата получения премии". Именно так задаются разные условия отбора на несколько полей списка одновременно.

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

	А	С
63	ФИО сотрудника	Размер премии
64	Петров С.А.	>300
65	Иванов А.С.	>300

из списка будут отобраны записи, содержащие значения "Петров С.А." и "Иванов А.С." в столбце "ФИО сотрудника" и имеющие размер премии более 300 рублей.

170

Пример 2. Три и более условий отбора накладываются на содержимое одного столбца списка.

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

Например, для рассматриваемого списка следующий диапазон критериев:

	А
67	ФИО сотрудника
68	Линьков С.А.
69	Петрова И.В.
70	Липецких АЛ.

задает отбор записей, содержащих в столбце "ФИО сотрудника" значения либо "Линьков С.А.", либо "Петрова И.В.", либо "Липецких А.Л.".

Пример 3. В условии отбора используется возвращаемое формулой значение.

В этом случае диапазон критериев и указываемая в нем формула должны удовлетворять следующим требованиям:

формула может иметь в своем составе несколько функций и зависеть от нескольких полей списка, но должна обязательно иметь в качестве результата логическую величину ИСТИНА (True) или ЛОЖЬ (False);

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

вычисляемый критерий должен иметь имя поля, не совпадающее ни с одним из имен полей списка;

формула должна ссылаться хотя бы на одно поле списка;

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

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

	А
72	Всего руб.
73	=E2+G2>800

заданный для списка на рис. 13.1, позволяет найти фамилии сотрудников, которые имеют сумму оклада и премии более 800 рублей.

Обратите внимание, что формула помещена под именем поля ("Всего руб."), не совпадающим ни с одним из имен полей списка на рис. 13.1. В качестве имени поля можно использовать любой уникальный текст.

171

Приведем примеры задания формул в критериях для списка на рис. 13.1, выполняющих сравнение содержимого ячеек в пределах записи:

=E2=G2

=E2

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

Более сложные критерии используют ссылки на ячейки нескольких записей списка или на ячейки вне списка, например: =Е2=$В$72 - сравнение значений ячеек в поле "Оклад (руб.)" с значением внешней по отношению к списку ячейки $В$72.

Логические функции И, ИЛИ, НЕ расширяют возможности вычисляемых критериев. Например, формула =И(С2="Инженер";Р2>1.02.98) в критерии задает отбор записей, содержащих в поле "Должность" значение "Инженер" и в поле "Дата получения премии" - значение дат позже 1.02.98.

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

Активизировать одну из ячеек списка.

Выполнить команду Расширенный фильтр (Advanced Filter) меню Данные \ Фильтр (Data\ Filter).

В ноле Диапазон критериев (Criteria Range) появившегося диалогового окна Расширенный фильтр (Advanced Filter) указать ссылку на данный диапазон критериев.

Нажать на ОК.

Замечания.

При задании критериев строчные и прописные буквы не различаются.

При задании критериев можно использовать символы "*" и "?" в соответствии с правилами оформления шаблонов: "*" - любая последовательность символов,"?" - один символ в заданной позиции.

Отфильтрованные записи можно поместить в любое место текущего рабочего листа. Для этого в диалоговом окне Расширенный фильтр (Advanced Filter) нужно установить переключатель в положение Скопировать результат в другое место (Copy to Another Location) и в тюле Поместить результат в диапазон: (List Range:) указать место размещения отфильтрованных записей списка.

172

168 :: 169 :: 170 :: 171 :: 172 :: Содержание

Содержание раздела