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

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

Для простых условий используют автофильтр, для использования которого сначала выделяют область списка или весь список, причем обязательно с заголовками столбцов (при этом имена столбцов списка EXCEL преобразует в имена полей записей базы данных). Затем используют команду Данные, Фильтр, Автофильтр, которая в строке заголовков таблицы отображает кнопки с раскрывающимися списками значений.

Рассмотрим применение автофильтра на примере прайс-листа компьютерных фирм, который приведен в таблице 11.

Таблица 11 – Фрагмент прайс-листа компьютерных фирм

Тип	Cache, kb	ОЗУ	HDD	Видео	Примечание	Цена, USD
Pentium 133				1 Mb, 14"	Intel Triton, Quantum
Pentium 133				1 Mb, 14"	Intel Triton, Quantum
Pentium 166				1 Mb, 14"	Intel Triton, Quantum
Pentium 166 ММХ				1 Mb, 14"	Intel Triton, Quantum
Pentium 166 ММХ				2 Mb, 15"	Intel 430TX, Quantum
Pentium 200 ММХ				1 Mb, 14"	Intel Triton, Quantum
Pentium 200 ММХ				2Mb, 15"	Intel 430TX, Quantum
Pentium 233 ММХ				2 Mb, 15"	Intel 430TX, Quantum
Pentium II 233				4 Mb, 15"	Intel 430LX, VA AGP Quantum
Pentium II 300				4 Mb, 15"	Intel 430LX, VA AGP Quantum

Автофильтр предполагает использование критериев поиска типа сравнение двух типов: по точному или шаблонному значению и по условию oтбopa.

Точное значение для сравнения выбирается из раскрывающегося списка для указанного поля, формируемого EXCEL при выполнении команды Данные, Фильтр, Автофильтр.

При выборе сравнения по условию задается критерий oтбopa, состоящий из двух предложений, связанных между собой логической связкой И либо ИЛИ. При этом каждое предложение имеет стандартную структуру:

< реляционный оператор > < значение >.

Тип реляционного оператора выбирается из предлагаемого перечня (больше, больше или равно, равно, содержит, начинается с и т.д.).

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

Для задания сложного условия для значений элементов одного из столбцов в команде Автофильтр выбирается строка (Условие...), в которой можно ввести два условия для одного столбца и обьединить их логическими функциями И/ИЛИ. Задание условий Пользовательского автофильтра для прайс-листа компьютерных фирм, приведенного в таблице 11, показано на рисунке 32, а результаты выполнения такого фильтра приведены в таблице 12.

Рисунок 32 - Образец задания условий Пользовательского автофильтра в MS Excel

Таблица 12 – Результаты выполнения Пользовательского автофильтра в MS Excel

Тип	Cache	ОЗУ	HDD	Видео	Примечание	Цена, USD
Pentium 166 ММХ				1 Mb, 14"	Intel Triton, Quantum
Pentium 166 ММХ				2 Mb, 15"	Intel 430TX, Quantum
Pentium 200 ММХ				1 Mb, 14"	Intel Triton, Quantum
Pentium 200 ММХ				2Mb, 15"	Intel 430TX, Quantum
Pentium 233 ММХ				2 Mb, 15"	Intel 430TX, Quantum

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

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

Таблица 13 - Образец обработки прайс-листа компьютерных фирм с диапазоном критериев для команды Расширенный фильтр в MS Excel

	A	B	C	D	E	F	G
	Тип	Cache	ОЗУ	HDD	Видео	Примечание	Цена, USD
				>1200			<900
	Фрагмент базы данных прайс-листа компьютерных фирм
	Тип	Cache	ОЗУ	HDD	Видео	Примечание	Цена USD
	Pentium 133				1 Mb, 14"	Intel Triton, Quantum
	Pentium 133				1 Mb, 14"	Intel Triton, Quantum
	Pentium 166				1 Mb, 14"	Intel Triton, Quantum
	Pentium 166 ММХ				1 Mb, 14"	Intel Triton, Quantum
	Pentium 166 ММХ				2 Mb, 15"	Intel 430TX, Quantum
	Pentium 200 ММХ				1 Mb, 14"	Intel Triton, Quantum
	Pentium 200 ММХ				2Mb, 15"	Intel 430TX,Quantum
	Pentium 233 ММХ				2 Mb, 15"	Intel 430TX, Quantum
	Pentium II 233				4 Mb, 15"	Intel 430LX, VA AGP Quantum
	Pentium II 300				4 Mb, 15"	Intel 430LX, VA AGP Quantum

Значения условий фильтрации, размещенные в одной строке диапазона, или области, критериев, объединяются логической функцией И, а значения условий в разных строках связываются функцией ИЛИ. Диапазон условий должен отделяться от исходного списка по крайней мере одной пустой строкой. Для примера прайс-листа компьютерных фирм на рисунке 33 показано формирование диапазона условий фильтрации, размещенных на одном листе с исходным списком. При этом диапазон критериев расположен на рисунке вверху списка на одном рабочем листе с исходным списком фильтрации.

После ввода условий в область критериев маркер выделения следует пометить в одну из ячеек списка и вызвать окно команды Расширенный фильтр (рисунок 33).

Рисунок 33 - Образец окна команды Расширенный фильтр в MS Excel

Результат выполнения условий фильтра для прайс-листа (таблица 11) приведен в таблице 14.

Таблица 14 – Результаты фильтрации прайс-листа компьютерных фирм с помощью Расширенного фильтра в MS Excel

Тип	Cache	ОЗУ	HDD	Видео	Примечание	Цена, USD
Pentium 166 ММХ				2 Mb, 15"	Intel 430TX, Quantum
Pentium 200 ММХ				2Mb, 15"	Intel 430TX, Quantum
Pentium 233 ММХ				2 Mb, 15"	Intel 430TX, Quantum

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

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

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

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

Сначала вычислим среднее значение цены в какой-либо ячейке вне списка, например в A3 (рисунок 3). Введем в ячейку A3 формулу вычисления среднего значения цены по столбцу G: =CP3HAЧ(G6:G15), которая для данного примера дает значение средней цены 649,9. Если столбцу цен (G6:G15) присвоить имя блока Цена, то формула вычисляемого критерия получит следующий вид: =СРЗНАЧ(Цена).^[3] Для задания вычисляемого критерия в ячейку А1 введем заголовок Цена выше средней, а в ячейку А2 введем следующую формулу: =G6>$A$3, где G6 - первая ячейка столбца G, содержащего цены компьютеров. После выполнения команды Расширенный фильтр (при этом в поле Диапазон условий следует ввести ссылку на ячейки $А$1:$А$2, содержащие критерии oт6oрa записей списка) список будет содержать строки с ценой выше средней, как показано в таблице 15.

Таблица 15 – Список компьютеров, цена которых выше средней в прайс-листе исходного списка

Тип	Cache	ОЗУ	HDD	Видео	Примечание	Цена (USD)
Pentium 200 ММХ				2Mb, 15"	Intel 430TX, Quantum
Pentium 233 ММХ				2 Mb, 15"	Intel 430TX, Quantum
Pentium II 233				4 Mb, 15"	Intel 430LX, VA AGP Quantum
Pentium II 300				4 Mb, 15"	Intel 430LX, VA AGP Quantum

Другой способ задания вычисляемого критерия не требует предварительного вычисления средней цены в отдельной ячейке прайс-листа. В ячейке А1 (рисунок 3) удалим заголовок, сделав ее пустой, а в ячейку А2 введем формулу: =G6>CP3HAЧ($G$6:$G$15), которая отфильтрует ячейки столбца G со значениями больше средней цены в прайс-листе.