ФИЛЬТРАЦИЯ (ВЫБОРКА) ДАННЫХ

КРАТКАЯ СПРАВКА

Фильтрация данных в списке — это выбор данных по заданному критерию (условию). Осу­ществляется эта операция с помощью команды Данные, Фильтр. Имеются две разновид­ности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Этап 1. Формирование диапазона условий для расширенного фильтра.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Фильтрация с помощью формы данных

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

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

Для просмотра записей используется полоса прокрутки либо кнопки <Назад> или <Далее>, выводится индикатор номера записи. При просмотре записей возможно их редак­тирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи с помощью кнопки <Вернуть> можно отменить.

Для создания новой записи нажимается кнопка <Добавить>, выполняется заполнение пустых полей экранной формы; для перехода между полями формы используются курсор мы­ши либо клавиша <Таb>. При повторном нажа­тии кнопки <Добавить> сформированная запись добавляется в конец списка. Для удаления теку­щей записи нажимается кнопка <Удалить>. Удаленные записи не могут быть восстановле­ны, при их удалении происходит сдвиг всех ос­тальных записей списка.

С помощью экранной формы задаются критерии сравнения. Для этого нажимается кнопка <Критерии>, форма очищается для вво­да условий поиска в полях формы с помощью кнопки <Очистить>, а название кнопки <Критерии> заменяется на название <Правка>. После ввода критериев сравнения нажимаются кнопки

ЗАДАНИЕ 5

Выберите данные из списка по критерию отбора, используя Автофильтр.

1.     Проведите подготовительную работу — переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных.

2.     Выберите из списка данные, используя критерий:

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

·        вид занятий — л.

3.     Отмените результат автофильтрации.

4.     Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.

5.     Отмените результат автофильтрации.

6.     Выполните несколько самостоятельных заданий, задавая произвольные критерии от­бора записей

ТЕХНОЛОГИЯ РАБОТЫ

1.     Проведите подготовительную работу:

·        переименуйте ЛистЗ—Автофильтр;

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

2.     Выберите из списка данные, используя критерий—для преподавателя — al выбрать сведения о сдаче экзамена на положительную оценку, вид занятий—л. Для этого:

·        установите курсор в область списка и выполните команду Данные, Фильтр, Автофильтр; в каждом столбце появятся кнопки списка;

·        сформируйте условия отбора записей;

·        в столбце Таб. № препод. нажмите кнопку , из списка условий отбора выбери­те al;

·        в столбце Оценка нажмите кнопку, из списка условий отбора выберите Ус­ловие и в диалоговом окне сформируйте условие отбора >2;

·        в столбце Вид занятия нажмите кнопку , из списка условий отбора выбе­рите л.

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

4.     Выберите из списка данные, используя критерий — для группы 133 получить сведе­ния о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь ана­логичной п.3 технологией фильтрации.

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

6.     Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

ЗАДАНИЕ  6

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

Для этого:

1.     Проведите подготовительную работу — переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных .

2.     Скопируйте имена полей списка в другую область на том же листе.

3.     Сформируйте в области условий отбора Критерий сравнения — о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5.

4.     Произведите фильтрацию записей на том же листе.

5.     Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.

6.     Сформируйте в области условий отбора Вычисляемый критерий — для каждого пре­подавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий—л; результат отбора поместите на новый рабочий лист.

7.     Произведите фильтрацию записей на новом листе.

8.     Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.

ТЕХНОЛОГИЯ РАБОТЫ

Проведите подготовительную работу:

·        переименуйте Лист4 — Расширенный фильтр;

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

Этап 1. Формирование диапазона условий по типу Критерий сравнения

·        Скопируйте все имена полей списка (см. рис. 3.35) в другую область на том же листе, например установив курсор в ячейку J1 Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 — имена полей области критерии, J2:O5 — область значений критерия.

·        Сформируйте в области условий отбора Критерий сравнения — о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

·         в столбец Номер группы—точное значение—133;

·         в столбец Код предмета — точное значения — n1;

·         в столбец Оценка — условие — >3

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

·        Произведите фильтрацию записей на том же листе:

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

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

·         в диалоговом окне «Расширенный Фильтр» с помощью мыши задайте параметры, например:

нажмите кнопку <ОК>.

·        Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе, соблюдая технологию п.3 и п.4.

Этап 1. Формирование диапазона условий по типу Вычисляемый критерий.

·        Сформируйте в области условий отбора Вычисляемый критерий — для каждого пре­подавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий — л; результат отбора поместите на новый рабочий лист. Для этого:

·         в столбец Вид занятия введите точное значении — букву л;

·         переименуйте в области критерия столбец Оценка, например, на имя Оценка 1;

·         в столбец Оценка 1 введите вычисляемый критерий, например, вида

=G2>CPЗHA4($G$2:$G$1 7)

где G2 - адрес первой клетки с оценкой в исходном списке,

$G$2:$G$17 — блок ячеек с опенками,

СРЗНАЧ — функция вычисления среднего значении.

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

·        Произведите фильтрацию записей на новом листе:

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

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

·         в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры например:

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

ЗАДАНИЕ 7

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

1.     Проведите подготовительную работу — переименуйте новый лист на Форма и скопи­руйте на него исходную базу данных.

2.     Просмотрите записи списка с помощью формы данных, добавьте новые;

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

4.     Просмотрите отобранные записи.

5.     Сформируйте собственные условия отбора записей и просмотрите их.

ТЕХНОЛОГИЯ РАБОТЫ

1.     Проведите подготовительную работу: переименуйте Лист5 — Формат,

·        выделите блок ячеек исходного списка, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Форма;

·        установите курсор в область списка и выполните команду Данные, Форма.

2.     Просмотрите записи списка и внесите необходимые изменения с помощью кнопок <Назад> и <Далее>. С помощью кнопки <Добавить> добавьте новые записи.

3.     Сформируйте условие отбора — для преподавателя — al выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий — л. Для этого:

·        нажмите кнопку <Критерии>, название которой поменяется на <Правка>;

·        в пустых строках имен полей списка введите критерии:

·        в строку Таб № препод. введите al;

·        в строку Вид занятия введите л;

·        в строку Оценка введите условие > 2.

4.     Просмотрите отобранные записи, нажимая на кнопку <Назад> или <Далее>

5.     Аналогично сформируйте собственные условия отбора записей и просмотрите их.