Примеры условий отбора расширенного фильтра

Практическое занятие №16

Фильтрация. Команда Расширенный фильтр.

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

План занятия:

1. Фильтрация списка с помощью расширенного фильтра

Примеры условий отбора расширенного фильтра.

3. Самостоятельная работа.

Фильтрация списка с помощью

Расширенного фильтра

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

Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки.

· Откройте файл Фильтрация.

· На Листе3 создайте и заполните таблицу приведенную ниже. Лист3 переименуйте в Расш.фильтр

· Столбцы Стоимость-у.е. и Стоимость в грн. вычисляются по формулам:

­ Стоимость-у.е. вычисляется по формуле := Кол-во * Цена-у.е.

­ Стоимость в грн. вычисляется по формуле := Стоимость-у.е. * Курс нацбанка

· Отформатируйте столбец Стоимость-у.е. в денежный формат $, а столбец Стоимость в грн. в денежный формат грн.

 

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

· Скопируйте из списка заголовки фильтруемых столбцов Кол-во и Цена-у.е.

· Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора J3,K3.

· Введите в строки под заголовками условия требуемые критерии отбора:

 

Кол-во	Цена-у.е.
>=4
	>=850

 

· Укажите любую ячейку в таблице.

· Выберите команду Данные – Фильтр -- Расширенный фильтр. Откроется диалоговое окно Расширенный фильтр, в котором Excel заполнил поле Исходный диапазон

· Введите в поле Диапазон условий ссылку на диапазон условий отбора J3:K5, включающий заголовки столбцов. Чтобы убрать диалоговое окно Расширенный фильтр на время выделения условий отбора, нажмите кнопку свертывания диалогового окна.

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

· Нажмите кнопку ОК.

· Используя приведенные ниже примеры диапазонов условий, отфильтруйте данную таблицу, результаты поместите под диапазонами условий.

Примеры условий отбора расширенного фильтра.

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

Наименование

Epson*

Canon*

Mustek*

Пример 2. Для следующего диапазона условий будут отобраны строки, содержащие Ксерокс в столбце Категория, Canon * в столбце Наименование и имеющие цену больше 300 $.

Категория	Наименование	Цена-у.е.
Ксерокс	Canon*	>300

Пример 3. Для следующего диапазона условий будут отобраны строки, удовлетворяющих одному из нескольких условий, наложенных на разные столбцы, содержащие либо Факс * в столбце Наименование, либо Туби в столбце Диллер, либо имеющие цену меньше 100.

 

Наименование	Диллер	Цена-у.е.
Факс*
	Туби
		<100

 

Пример 4. Для следующего диапазона условий будут отобраны строки, содержащие Сканер в столбце Категория, Mustek * в столбце Наименование, Туби в столбце Диллер и имеющие цену меньше 120 $. А также строки содержащие C* в столбце Наименование и имеющие цену меньше 300 $.

Категория	Наименование	Диллер	Цена
Сканер	Mustek *	Туби	<120
	C*		<300

 

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

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

Вывести: Цены выше среднего

=Е4>СРЗНАЧ($E$4:$E$24)

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

При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? Или #ЗНАЧ. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

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

Самостоятельная работа

· Перейдите на новый лист.

· Создайте таблицу Сотрудники и заполните ее. (Не менее 20 записей).

· Имена полей таблицы:

­ Фамилия

­ Имя

­ Дата приема на работу

­ Дата рождения

­ Пол

­ Оклад

­ Возраст

Например:

· Используя приведенные ниже диапазоны условий, отфильтруйте данную таблицу, результаты поместите под диапазонами условий.

a) Вывести только тех сотрудников, фамилии которых начинаются с А, Г, Н.

b) Вывести только тех сотрудников, фамилии которых начинаются с Г, Б, Л и чей возраст превышает 35 лет.

c) Вывести только тех сотрудников, у которых либо оклад больше или равен 500 грн., либо возраст больше 50.

d) Вывести только тех сотрудников, пол которых мужской и возраст меньше 40.

e) Вывести только тех сотрудников, заработная плата которых больше среднего оклада.

Контрольные вопросы:

1. Как отфильтровать список, используя Расширенный фильтр?

2. Как используется возвращаемое формулой значение?