Главная | Обратная связь | Поможем написать вашу работу!

Применение фильтров

Лабораторная работа №12. Работа со списками.

Цель: познакомиться с принципами создания базы данных на основе электронных таблиц MS Excel; изучить возможности операций сортировки и поиска данных из списка с помощью фильтров.

Теоретическая справка

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

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

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

Сортировка списков

Необходимость сортировки записей в списках возникает, обычно, для последующего быстрого поиска информации в списке. Существуют два способа сортировки: по возрастанию и по убыванию признака сортировки, которым является один из столбцов списка. Для простой сортировки строк следует активизировать любую ячейку внутри списка и щелкнуть по одному из значков (по возрастанию или по убыванию) на панели инструментов. Excel автоматически определяет границы списка и сортирует строки целиком. Если пользователь сомневается в правильности определения границ списка, то целесообразно выделить сортируемый диапазон и выполнить Данные→Сортировка. В окне "Сортировка диапазона" следует задать признак сортировки (заголовок столбца), а также как сортировать - по возрастанию или по убыванию.

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

В качестве примера рассмотрим таблицу с итогами сессии (Рис.90).

Рисунок 90 - Итоги сессии

Отсортируем таблицу по двум признакам: первичный – группа (по возрастанию), вторичный – фамилия (по алфавиту). Для этого выделим диапазон B2:E17 и выполним Данные→Сортировка. Зададим настройки, как показано в окне "Сортировка диапазона" (Рис.91). В результате получим отсортированную таблицу (рис.92).

Рисунок 91 - Диалоговое окно Сортировка диапазона

Рисунок 92 - Результат сортировки

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

· в выделенный диапазон не включен столбец А – порядковые номера не сортируются;

· сортировка по вторичному признаку (фамилии по возрастанию) означает их расположение по алфавиту только в пределах одинаковых значений первичного признака (номеров групп);

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

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

Поиск записей

Для поиска записей следует обратиться к меню Правка→Найти, в поле "Что" диалогового окна "Найти" ввести образец поиска, а в поле "Область поиска" установить "значения". После этого табличный курсор будет установлен на искомую ячейку. Если ячеек с искомым признаком несколько, то для продолжения нажать кнопку "Найти далее". В начале поиска курсор должен быть установлен в начало списка. Допускается применение масок. Маска – это текстовый шаблон, составленный из обычных и специальных символов. В качестве специальных используются символы? и *. Первый означает любой символ; второй – любой текст. Например, если для рассмотренного выше примера задать поиск информации по маске?е*, то в таблице будут найдены фамилии Непошеваленко И., Дедикова Т. и Немчинов А.

Применение фильтров

Фильтр - это средство для отбора записей в таблице по некоторому критерию. В Excel имеются два типа фильтров: автофильтр и расширенный фильтр. Автофильтр показывает записи, совпадающие с критериями фильтрации, и скрывает не совпадающие. Расширенный фильтр способен сформировать новую таблицу из отфильтрованных записей.

Автофильтр

Для применения автофильтра необходимо выделить любую клетку внутри фильтруемой таблицы и обратиться к меню Данные→Фильтр...→Автофильтр. После обращения в заголовке таблицы должны появиться кнопки для раскрытия списков. Нажатие любой кнопки приводит к раскрытию списка элементов соответствующего столбца таблицы. Выбранный элемент является критерием фильтрации. Строки таблицы, в которых элементы столбца не совпадают с критерием будут скрыты, причем за совпавшими сохраняются их прежние порядковые номера. Выбор второго критерия в другом списке приведет к дополнительной фильтрации записей и т.д. В качестве примера рассмотрим применение автофильтра для таблицы с итогами сессии. На рисунке 93 показаны результаты фильтрации по условию "Оценка по информатике"=5.

Рисунок 93 - Результаты фильтрации

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

Для задания более сложного условия фильтрации необходимо в соответствующем раскрывающемся списке выбрать "[Условие...]" и сформулировать его в открывшемся окне "Пользовательский автофильтр". Окно содержит поля для ввода знаков логических отношений и метки логических операций И и ИЛИ. Например, для отбора записей, соответствующих студентам, получившим по информатике 4 или 5, следует выполнить настройки, как показано на рисунке 94.

Рисунок 94 - Окно Пользовательский автофильтр

Отменить результаты фильтрации можно через Данные→Фильтр... и убрать флажок с меню Автофильтр.

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

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

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

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

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

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

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

В качестве примера рассмотрим условие фильтрации ("Группа"=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. Каковы правила формирования блока критериев в расширенном фильтре?