Расширенный фильтр

Для применения расширенного фильтра требуется предварительная подготовка, состоящая из двух этапов:

· подготовки вспомогательной таблицы (диапазона) критериев;

· планирования места для размещения результатов фильтрации.

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

В качестве критериев можно использовать содержимое ячеек таблицы или логические выражения с использованием содержимого.

В качестве примера рассмотрим условие фильтрации ("Группа"=154 И "Оценка по информатике">3) ИЛИ ("Группа"=155 И "Оценка по информатике">3).

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

Рисунок 95 - Исходная таблица с результатами расширенного фильтра

В рассмотренном примере блок критериев расположен в диапазоне G1:H3.
Запуск расширенного фильтра выполняется через меню Данные→Фильтр... →Расширенный фильтр. В окне "Расширенный фильтр" следует задать настройки, как показано на рисунке 96.

Рисунок 96 - Диалоговое окно Расширенный Фильтр

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

Варианты индивидуальных заданий

В соответствии с вариантом выберите из таблицы 1 предметную область. Создайте на отдельном листе список, который должен содержать не менее 20 записей. Затем над созданным списком необходимо выполнить следующие действия:

− сортировку;

− поиск информации с помощью автофильтра;

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

Рекомендуется каждое задание выполнять на отдельном листе; листы именовать в соответствии с выполняемым заданием (например, «Сортировка», «Автофильтр», «Расширенный фильтр»).

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

1. Сортировка (табл.2).

2. Автофильтр (табл.3).

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

Таблица 19 – Индивидуальные варианты лабораторной работы

№ В	Предметная область	Пояснения
1-3	Отдел кадров автомобильного завода «ВАЗ» (Фамилия, Имя, Отчество, Отдел, Оклад, Пол, Дата рождения, Возраст, Дата приема на работу)	Поле Возраст необходимо рассчитывать по формуле
4-6	Деканат транспортного факультета (Фамилия, Имя, Отчество, Дата рождения, Группа, Факультет, Предмет, Дата сдачи экзамена, Оценка)	Значения поля Оценка: Отлично, Хорошо и т.д.
7-9	Нагрузка преподавателя транспортного факультета (ФИО, Ученая степень, Должность, Кафедра, Название Предмета, Специальность, Группа, факультет, Вид занятия, Количество часов)	Значения поля Вид занятия: лекции, лабораторные работы, курсовая работа и т.д.
10-12	Продажи автосалона (Менеджер, Клиент, Вид сделки, Товар, Количество, Цена, Сумма, Дата)	Значения поля Вид сделки: поставка, продажа и т.д.
13-15	Поставки автозавода (Дата поставки, Поставщик, Количество поставленной продукции, Способ перевозки, Транспортные издержки на единицу товара, Цена единицы продукции без транспортных издержек, Стоимость перевозимого товара, Общие транспортные расходы)	Значение поля Способ перевозки: ж/д, самолет и т.п. Поле Общие транспортные расходы необходимо рассчитывать по формуле.

Таблица 20 – Индивидуальные варианты лабораторной работы

№ В	Сортировка
	Фамилия, Имя, Дата Рождения
	Отдел, Фамилия, Имя
	Оклад, Фамилия, Отдел
	Фамилия, Имя, Факультет
	Предмет, Дата сдачи экзамена, Фамилия
	Предмет, Оценка, Фамилия
	Кафедра, Должность, ФИО
	Название предмета, Должность, ФИО
	Вид занятия, Название предмета, ФИО
	Менеджер, Клиент, Товар
	Клиент, Товар, Дата
	Дата, Клиент, Количество
	Поставщик, Способ перевозки, Стоимость перевозимого товара
	Способ перевозки, Поставщик, Дата
	Дата, Поставщик, Количество поставленной продукции

Таблица 21 – Индивидуальные варианты лабораторной работы

№ В	Запрос
	Получить информацию о сотрудниках двух конкретных отделов, родившихся в период [ Дата1; Дата2 ] и принятых на работу позднее даты Дата3
	Получить информацию о мужчинах, с окладом ниже значения Оклад
	Получить информацию о женщинах, работающих либо в отделе Отдел1, либо в отделе Отдел2.
	Отобразить информацию о студентах групп Группа1 и Группа2 по предмету Предмет с оценками Хорошо и Отлично
	Найти студентов с двух факультетов Факультет1 и Факультет2, родившихся в период [ Дата1;Дата2 ]
	Найти информацию о студентах, сдававших экзамены по предметам Предмет1 и Предмет2 на оценку Отлично
	Определить читают ли лекции по предмету Предмет на факультетах Фаультет1 и Факультет2 профессора
	Определить, в каких группах читает лекции и ведет лабораторные работы преподаватель Преподаватель
	Найти всех преподавателей с кафедры Кафедра, которые ведут лабораторные работы и практические занятия в группах Группа1 и Группа2
	Найти информацию о деятельности менеджера Менеджер в период [ Дата1;Дата2 ]
	Определить клиентов, покупающих товары Товар1 и Товар2 в количестве больше Количество
	Найти информацию, связанную с покупкой или продажей товаров Товар1 и Товар2 клиентом Клиент на сумму Сумма и выше
	Найти информацию о поставках от поставщика Поставщик в период с Дата1 по Дата2
	Получить информацию о поставках от поставщика Поставщик способом перевозки Способ_перевозки после даты Дата
	Определить какие поставщики использовали способы перевозки Способ_перевозки1 и Способ перевозки2 с общими транспортными расходами меньше Сумма

Таблица 22 – Индивидуальные варианты лабораторной работы

№ В	Запрос
	Найти работников отделов Отдел1 и Отдел2 с окладами выше среднего оклада на предприятии
	Найти информацию о мужчинах из отдела Отдел1 в возрасте от Возраст1 до Возраст2 и о женщинах из отдела Отдел2 в возрасте от Возраст3 до Возраст4
	Найти женщин из отдела Отдел1, родившихся в период [ Дата1;Дата2 ], и мужчин из отдела Отдел2, родившихся в период [ Дата3;Дата4 ]
	Найти информацию о студентах факультетов Факультет1 и Факультет2, сдававших экзамены в период с Дата1 по Дата2
	Определить студентов факультетов Факультет1 и Факультет2, сдававших экзамены по предмету Предмет на оценку Отлично
	Найти информацию о студенте Фамилия, сдавшего экзамен по предмету Предмет на оценку выше средней оценки по этому предмету по вузу
	Отобразить лекционные курсы, которые обеспечивает кафедра Кафедра
	Найти информацию о преподавателях кафедр Кафедра1 и Кафедра2, которые проводят практические занятия и лабораторные работы на факультетах Факультет1 и Факультет2
	Найти дисциплины, изучаемые на факультетах Факультет1 и Факультет2 с максимальным количеством часов, отводимых на практические занятия
	Отобразить информацию о сделках, проведенных менеджером Менеджер, с суммой, превышающей среднюю сумму сделки
	Найти информацию о деятельности менеджера Менеджер по товару Товар в период [ Дата1;Дата2 ]
	Найти поставки от клиентов Клиент1 и Клиент2 на суммы, равные выше средней сумме поставки
	Найти поставки от поставщиков Поставщик1 и Поставщик2 в период от Дата1 по Дата2 на суммы, превышающие среднюю сумму поставки
	Найти поставки способами перевозки Способ_перевозки1 и Способ _ перевозки2 от поставщика Поставщик со стоимостью перевозимого товара от Сумма1 до Сумма2 рублей
	Для способа перевозки Способ_перевозки в период с Дата1 по Дата2 найти поставки с транспортными издержками на единицу продукции ниже средней

Вопросы для самоконтроля

1. Что называется списком в табличном процессоре Excel?

2. Как Excel определяет границы списка?

3. Что такое режим автозаполнения ячеек?

4. Для чего применяется сортировка списков?

5. В каких ситуациях применяется сортировка списков по нескольким признакам?

6. Список состоит из двух полей: фамилии студента и оценке по информатике. Какие из этих полей следует использовать как первичный и вторичный признаки сортировки? Обоснуйте ответ.

7. Как сделать так, чтобы при сортировке списка поле с порядковыми номерами записей осталось неотсортированным?

8. Что такое поиск информации в списке?

9. Что такое маска поиска? Как она записывается?

10. Что такое фильтр? Какие виды фильтров имеются в Excel?

11. Объясните принцип работы автофильтра.

12. Объясните принцип работы расширенного фильтра.

13. Чем расширенный фильтр отличается от автофильтра?

14. Каковы правила формирования блока критериев в расширенном фильтре?

Дата добавления: 2014-10-31