Лабораторная работа № 5. «Фильтрация записей в базах данных»

Лабораторная работа № 5

«Фильтрация записей в базах данных»

Цель работы: изучение возможностей отбора нужной информации в базах данных MS Excel

Краткие теоретические сведения

Фильтрация данных в базе данных (списке) MS Excel - это выбор данных по заданному критерию (условию), позволяющий в удобной для пользователя форме выделять нужные записи из списка. При этом остальные записи просто скрываются (не удаляются).

 

Осу­ществляется эта операция с помощью команды Данные, Фильтр.

Имеются две разновид­ности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр.

 

Автофильтрация

 

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

Рис. 1

По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

· все - выбираются все записи без ограничений;

· первые 10 - данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис. 2) выбрать определенное количество наи­больших или наименьших элементов списка, которые необходимо отобразить;

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

· условие — выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис. 3).

Рис. 2

 

Рис. 3

 

Условие для отбора записей по конкретным значениям в определенном столбце мо­жет состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ.

 

Каждая часть условия включает:

· оператор отношения: = (равно), < > (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;

· значение, которое может выбираться из списка или содержать шаблонные символы *,?.

 

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

 

Отмена результата фильтрации и возврат к исходному состоянию списка производят­ся повторным вводом команды Данные, Автофильтр.

 

 

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

 

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

Расширенный фильтр позволяет:

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

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

· показывать в отфильтрованных записях не все столбцы, а только указанные;

· объединять оператором ИЛИ условия для разных столбцов;

· для одного столбца объединять операторами И, ИЛИ более двух условий;

· создавать вычисляемые критерии;

· выводить только уникальные значения.

 

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух ти­пов критериев для фильтрации записей списка:

· критерий сравнения;

· вычисляемый критерий.

 

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

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

Технология использования расширенного фильтра состоит из двух этапов:

этап 1 - формирование области критериев поиска;

этап 2 - фильтрация записей списка.

 

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

Критерий сравнения формируется при соблюдении следующих требований:

· состав столбцов области критериев определяется столбцами, по которым задаются ус­ловия фильтрации записей;

· имена столбцов области критериев должны точно совпадать с именами столбцов ис­ходного списка;

· ниже имен столбцов располагаются критерии сравнения типа:

§ точного значения;

§ значения, формируемого с помощью операторов отношения;

§ шаблона значения, включающего символы * и (или)?.

Правила формирования множественного критерия:

− Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И.

− Если условия записаны в нескольких строках, то они считаются связанными усло­вием ИЛИ.

 

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (число, текст, дата, логическая константа), операторов отношения.

 

 

2) Этап 2. Фильтрация записей расширенным фильтром.

После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 4).

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

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

Рис. 4

 

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

 

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

Порядок выполнения работы

 

Задание 1. Провести отбор информации с использованием автофильтра.

Задание 2. Провести отбор информации с использованием расширенного фильтра.

 

Выполнение задания 1

Работа с командами Данные - Фильтр – Автофильтр.

 

2.1.1. Отбор студентов ФРЭ

Выполняем команды:

а) скопировать лист БД с другим именем, например, с именем БД1 (чтобы сохранить исходный список);

б) поставить курсор в поле базы данных нового листа;

в) выполнить команды Данные=>Фильтр=>Автофильтр (рис. 5). На метках столбцов должны появиться открывающие кнопки (рис. 6);

г) щелкнуть по кнопке столбца Факультет;

д) в открывшемся списке выбрать ФРЭ;

е) нажать Оk. В базе данных остались только записи о студентах ФРЭ, остальные - скрыты (рис. 7).

 

Рис. 5

Рис. 6

 

Рис. 7

 

2.1.2. Отбор студентов ФРЭ, обучающихся по заочной системе

Для выполнения:

а) щелкнуть по кнопке Форма обучения (рис. 7);

б) в открывшемся окне выбрать з;

в) щелкнуть по Оk. В списке остались только студенты-заочники ФРЭ (рис. 8).

Рис. 8

 

 

2.1.3. Отбор студентов ФРЭ, сдавших второй раздел курса (р2)

Для выполнения:

а) щелкнуть по кнопке столбца Раздел курса (см. рис. 8);

б) выбрать р2;

в) щелкнуть Оk. В списке остались только студенты имеющие оценку по р2 (рис.9).

Рис. 9

 

2.1.4. Отбор студентов-заочников ФРЭ, имеющих оценку 5 по р2

Для выполнения:

а) щелкнуть по кнопке столбца Оценка (рис. 9);

б) выбрать Оценка 5;

в) щелкнуть Оk. Появится результат, изображенный на рис. 10.

 

Рис. 10

 

2.1.5. Восстановить исходную базу данных

Для восстановления исходной базы данных нужно щелкнуть по кнопкам столбцов в порядке, обратном только что проделанному (т.е. столбцы Форма обучения, Раздел курса, Оценка, Факультет). Или выполнить команды Фильтр - Отобразить все.

 

Выполнение задания 2

 

Работа с командами Данные => Фильтр => Расширенный фильтр.

 

2.2.1. Провести отбор студентов МФ, имеющих оценки 4 и 5

1) Скопировать строку заголовков в область Критерии фильтрации (рис. 11).

Для выполнения:

• Создать копию листа «БД » со списком, назвать Фильтр. Поставить курсор мыши в ячейку А1 и, зажав левую клавишу мыши, протащить ее по первой строке до ячейки G1. Строка с наименованием столбцов оказалась выделенной;

• щелкнуть по пиктограмме Копировать;

• поместить курсор мыши в ячейку А19 (первую ячейку, которая отведена для диапазона критериев);

• щелкнуть по пиктограмме Вставить. В строке 19 появится новая строка наименований столбцов (рис. 11).

2) В строки 20 и 21 введем критерий фильтрации:

• в столбце Факультет запишем МФ (ячейка Е20). В этой же строке в столбце Оценка, запишем 4 (ячейка G20). Форма обучения - з (в С20).

• в следующей строке в столбце Факультет запишем МФ (ячейка Е21), в столбце Оценка - запишем 5 (ячейка G21), Форма обучения - з (в С21). В итоге получим результат, изображенный на рис. 11;

 

Рис. 11

3) Провести фильтрацию:

• Выполнить команды Данные - Фильтр - Расширенный фильтр. Появится

окно рис. 12;

Рис. 12

• Ввести в окне Исходный диапазон $А$1:$G$16;

• в окне Диапазон критериев ввести $А$19:$G$21;

• выбрать опцию Фильтровать список на месте;

• Оk. В результате получаем результат, изображенный на рис. 13.

 

Рис. 13

 

2.2.2. Провести отбор студентов МФ, имеющих оценки ниже среднего балла

Для выполнения:

1) Создать копию листа Фильтр, Назвать ее Фильтр 1;

2) Повторить п.2 из предыдущего задания;

3) Поскольку в столбце Оценка (ячейка G19) будет введен вычисленный критерий, столбец нужно переименовать. Введем в ячейку G19 новое название Оценка1;

4 ) Определить вычисляемый критерий:

а) поместить курсор в ячейку Н20;

б) ввести в Н20 формулу =СРЗНАЧ($G$2:$G$16);

в) ввести в строку 20 критерий фильтрации:

• в ячейку Е20 слово МФ;

• в ячейку G20 (столбец Оценка 1) формулу =G2>$Н$20 (получаем результат, изображенный на рис. 14);

5) Активизировать Расширенный фильтр:

а) выполнить команду Данные =>Фильтр=>Расширенный фильтр;

б) в открывшемся диалоговом окне (см. рис. 12) указать Исходные данные $А$1:$G$16;

в ) ввести Диапазон критериев $А$19:$G$20;

г) выбрать вид обработки Фильтровать список на месте, Оk. В результате получаем таблицу, изображенную на рис. 15.

 

Рис. 14

 

Рис. 15

 

 

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

 

1) В чем заключается фильтрация данных в списке MS Excel?

2) Какие средства фильтрации баз данных существуют в MS Excel?

3) Какие критерии отбора данных при автофильтрации предусмотрены в MS Excel?

4) Перечислите основные функциональные возможности расширенного фильтра баз данных в MS Excel.

5) Какие этапы включает в себя технология использования расширенного фильтра в MS Excel?

6) Какой критерий фильтрации называют множественным?

7) Какие правила формирования множественного критерия фильтрации вы знаете?

8) Какие требования предъявляются к критериям сравнения расширенного фильтра MS Excel?

9) Что представляет собой вычисляемый критерий расширенного фильтра MS Excel?

 

ЗАДАНИЕ