Лабораторная работа 5. Фильтры и формы Фильтры

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

Можно указывать фильтр разными способами:

1. Через автофильтрацию, указывая либо нужные значения полей, либо задавая простейшие условия выборки.

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

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

Использование автофильтра

В пункте Фильтр меню Данные выбирают команду Автофильтр. Затем:

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

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

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

• чтобы отобразить строки, удовлетворяющие одновременно двум условиям, вводят оператор и значение сравнения в первой группе полей, нажимают кнопку И, а затем вводят второй оператор и значение сравнения во второй группе полей;

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

Для удаления фильтра из списка выполняют одно из следующих действий:

• чтобы удалить фильтр для одного столбца списка, нажимают на кнопку со стрелкой, а затем выбирают из развернувшегося списка Все;

• чтобы удалить фильтры для всех столбцов списка, выбирают пункт Фильтр в меню Данные, а затем — команду Показать все;

• чтобы удалить автофильтр из списка, выбирают пункт Фильтр в меню Данные, а затем — команду Автофильтр.

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

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

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

2. Ввести в строки под заголовками условий требуемые критерии отбора.

3. Установить курсор на ячейке в списке и выбрать пункт Фильтр в меню Данные, а затем — команду Расширенный фильтр.

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

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

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

В условия отбора расширенного фильтра может входить несколько условий:

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

Студент

Белов

Батурин

Сушкин

б) накладываемых одновременно на несколько столбцов. Для этого условия вводятся в ячейки, расположенные в одной строке диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах группы 98ИС, сдавших экзамен по физике на 4 или 5 и по культурологии – на 5.

Группа	Физика	Культурология
98ИС	>3	5

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

Физика	Культурология
3
	3

г) сложное условие отбора, накладываемое на несколько столбцов. Для этого вводят его составные части в отдельные строки диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие данные о студентах групп 98ИС и 98ВТ1, сдавших экзамен по физике на 4 и 5.

Группа	Физика
98ИС	>3
98ВТ1	>3

Виды условий отбора

При настройке автофильтра или вводе условий отбора в диапазоне условий расширенного фильтра можно использовать перечисленные ниже элементы условий:

1. Последовательности символов. При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Например, при вводе условия «Пет» будут отобраны строки с ячейками, содержащими фамилии Петров, Петеров и Петухов. Чтобы получить точное соответствие отобранных значений заданному образцу, например, текст, следует ввести условие: =''=текст''.

2. Знаки подстановки. Используются для того, чтобы отобрать строки с ячейками, содержащими последовательность символов, в некоторых позициях которой могут стоять произвольные символы. Знак подстановки эквивалентен одному символу или произвольной последовательности символов. Например, знак ? заменяет любой символ в той же позиции, что и сам знак (так, д?м задает поиск строк «дом« и «дым»); знак * задает любую последовательность символов в той же позиции, что и сам знак (так, *ино задает поиск строк «Люблино» и «Выхино»)

3. Значения сравнения. Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения. Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобрать строки, имеющие значения ячеек большие или равные 1000, введите условие отбора >=1000 ниже заголовка «Количество». Отметим, что строчные и прописные буквы при фильтрации данных не различаются.

Формы

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

Вставка записей выполняется с помощью кнопки Добавить. Вводятся поля новой записи, используя клавишу TAB для перемещения к следующему полю (для перемещения к предыдущему полю используйте сочетание клавиш SHIFT+TAB). Чтобы добавить запись в список, нажмите клавишу ENTER. Чтобы отменить добавление записи, нажмите кнопку Вернуть перед нажатием клавиши ENTER или кнопки Закрыть.

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

Удаление записи выполняется с помощью клавиши Удалить. Причем предварительно нужная запись должна быть найдена.

Поиск записей. Для перемещения на одну запись нажмите на стрелки полосы прокрутки в диалоговом окне. Чтобы переместиться на 10 записей, нажмите полосу прокрутки между стрелками. Нажмите кнопку Далее для перехода к следующей записи списка и Назад — для перехода к предыдущей.

Чтобы задать условия поиска или условия сравнения, нажмите кнопку Критерии. Введите критерии в форме (при этом используются правила записи условий, рассмотренные в предыдущей работе). Чтобы найти совпадающие с критериями записи, нажмите кнопки Далее или Назад. Чтобы вернуться к правке формы, нажмите кнопку Правка.

Задание

1. Использовать список студентов из работы 3.

2. С помощью Формы:

• Добавить в список несколько записей в разные его места.

• Модифицировать часть записей, найдя их через Критерий в соответствии с фамилией.

• Удалить несколько записей, найдя их через Критерий в соответствии с фамилией.

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

4. Отфильтровать список, выбрав тех, кто сдал сессию, т.е. получил положительные оценки. Скопировать результат в третий лист.

5. Отфильтровать список, выбрав тех, кто получил только оценки 3 или 4. Скопировать результат в четвертый лист.

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

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

8. Все листы поименовать по смыслу.

9. Показать результаты преподавателю.