Фильтр данных по условиям списка. Расширенный фильтр в Excel и примеры его возможностей. Как сделать расширенный фильтр в Excel

05.09.2020

Фильтрация данных в списке

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

Все это упрощает процесс ввода и удаления записей, а также процесс поиска информации.

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

В Microsoft Excel доступны две команды для фильтрации списков:

Автофильтр , включая фильтр по выделенному, для простых условий отбора;
Расширенный фильтр для более сложных условий отбора.

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

Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.

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

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

Microsoft Excel обозначает отфильтрованные элементы голубым цветом

Когда условие отбора должно одновременно применяться к ячейкам двух и более столбцов;

Когда к ячейкам одного столбца необходимо применить три и более условий отбора:

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

Расширенный фильтр может использовать операции И, ИЛИ, а также составлять вычисляемые критерии.

Обе команды вызываются в результате выбора команды Данные/Фильтр.

С помощью команды Расширенный фильтр можно фильтровать список так же, как и с помощью команды Автофильтр , но при этом не отображаются раскрывающиеся списки для столбцов.

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

1 СоздатьБАЗУ ДАННЫХ (Исходный диапазон) .

2. СоздатьДиапазон условия, который задает условия поиска данных. Верхняя строка этого диапазона должна содержать заголовки полей, в точности повторяющие заголовки полей в исходном диапазоне (готовый список).

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

Условия всех столбцов каждой строки соединяются логической операцией «И », а затем все строки соединяются логической операцией «ИЛИ »

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

В поле ввода Диапазон условий указывается интервал ячеек на рабочем листе, который содержит ваши условия. В поле ввода Поместить результат в диапазон указывается интервал ячеек, в которые копируются строки. Это поле доступно только тогда, когда выбран переключатель Скопировать на другое место .

4 Проверить правильность автоматического заполнения всех диапазонов и при необходимости откорректировать их.

Примеры сложных условий отбора:

Несколько условий для одного столбца При наличии для одного столбца двух и более условий отбора введите эти условия отбора непосредственно друг под другом в отдельные строки Продавец Белов Батурин Рощин Одно условие для нескольких столбцов Для того чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Тип Продавец Продажи Фрукты Белов >1 000 Разные условия для разных столбцов Тип Продавец Продажи Фрукты Белов >1 000

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

Один из двух наборов условий для двух столбцов

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

Продавец Продажи Белов >3 000 Батурин >1 500 Более двух наборов условий для одного столбца

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

Дата публикования: 2015-10-09 ; Прочитано: 205 | Нарушение авторского права страницы | Заказать написание работы

сайт - Студопедия.Орг - 2014-2020 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.002 с) ...

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

Способ 1. Автофильтр

Отфильтровать список автофильтром - значит скрыть все строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в меню Данные - Фильтр - Автофильтр (Data - Filter - AutoFilter) .

В первой строке, содержащей заголовки столбцов, появятся кнопки со стрелками - кнопки автофильтра:

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

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

К сожалению, ни одна версия Excel, кроме Excel 2007 не позволяет выбрать из выпадающего списка более одного варианта.

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

Способ 2. Пользовательский автофильтр

Мы можем использовать автофильтр для выборки записей по более сложным условиям. Допустим, необходимо выбрать из списка строки, где оклад сотрудника лежит в диапазоне от 1000 до 2000 или меньше/больше определенной величины. Для выполнения такой фильтрации из выпадающего списка поля "Стоимость заказа" необходимо выбрать пункт Условие (Custom) . Появится окно :

При помощи выпадающих списков необходимо задать условие отбора и нажать кнопку ОК - Excel отобразит только те строки, которые удовлетворяют заданным условиям.

Пользовательский автофильтр способен также работать с текстом - для этого в списке условий есть операторы Начинается с... , Заканчивается на... , Содержит... , Не содержит... и т.д.

Способ 3. Расширенный фильтр

В отличие от Автофильтра и Пользовательского автофильтра - Расширенный фильтр практически не имеет ограничений на количество условий, налагаемых на список. Но (бесплатных пирожных не бывает!) требует некоторых подготовительных операций. А именно:

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

Excel отберет всех мужчин с окладом 1000 и более, неженатых и женщин не старше 1 января 1950 года рождения, незамужних, с детьми.

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

Флажок Только уникальные записи служит для отбора неповторяющихся данных .

Цель работы: выполнение сортировки данных, ознакомление со способом фильтрации записей списка, автофильтрацией, работой с формой данных.

Задание 1.

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

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

1. Откройте новую рабочую книгу и сохраните ее под именем «Сортировка» в своей рабочей папке.

2. Создайте таблицу представленную на рисунке 5.56.

Рисунок 5.56 – Начальная таблица с данными

3. Задайте параметры форматирования для таблицы.

Шрифт Times New Roman, размер шрифта 12 пт., для заголовков начертание полужирное и выравнивание по центру, перенос по словам, заливка серым цветом; для основной части. Напоминаем, что команды форматирования доступны на ленте Главная Þ Ячейки .

4. Для выполнения сортировки по полю фамилия покупателя поставьте курсор в любое место этого столбца и выполните команду Данные Þ Сортировка (рис. 5.51).

В открывшемся диалоговом окне в поле Сортировать по выберите «Фамилия покупателя». По возрастанию.

5. Повторите все шаги пункта 4 и задайте сортировку по «Сумме сделки», по убыванию.

6. Выполните повторно сортировку по полю «Дата сделки», по возрастанию.

7. Скопируйте таблицу на новый лист и на нем выполните сортировку по совокупности признаков. Для этого вызовите команду Данные Þ Сортировка. Установите Сортировать по фамилии в порядке возрастания, Затем по дате в порядке возрастания, В последнюю очередь, по сумме в порядке убывания.

8. С помощью команды Переименовать присвойте имена этим двум листам.

Задание 2 . Осуществите выборку информации из списка на основе команды Автофильтр.

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

1. На листе 4 создайте таблицу и заполните ее сведениями из таблицы 5.5.

2. Переименуйте Лист4, присвоив ему имя «Автофильтр №1».

3. Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные ÞФильтр. Рядом с названиями граф таблицы появятся стрелки направленные вниз, которые раскрывают список возможных значений. В графе «Пол» выберите «М» Скопируйте таблицу на лист 5 и переименуйте его в «Автофильтр №2».

4. На листе «Автофильтр №1» в графе «Пол» откройте список фильтрования и выберите «Все». Затем в графе «Дата рождения» выберите в списке фильтрования «Условие» и задайте условие (рис.5.57):

Таблица 5.5

Фамилия	Имя	Дата приема на работу	Дата рождения	Пол	Оклад	Возраст
Пашков	Игорь	16.05.74	15.03.49	М
Андреева	Анна	16.01.93	19.10.66	Ж
Ерохин	Владимир	23.10.81	24.04.51	М
Попов	Алексей	02.05.84	07.10.56	М
Тюньков	Владимир	03.11.88	19.07.41	М
Ноткин	Евгений	27.08.85	17.08.60	М
Кубрина	Марина	20.04.93	26.06.61	Ж
Гудков	Никита	18.03.98	05.04.58	М
Горбатов	Михаил	09.08.99	15.09.52	М
Быстров	Алексей	06.12.00	08.10.47	М
Крылова	Татьяна	28.12.93	22.03.68	Ж
Бершева	Ольга	14.12.01	22.12.74	Ж
Русанова	Надежда	24.05.87	22.01.54	Ж

Рисунок 5.57 – Задание условий фильтрации

5. Скопируйте отфильтрованную таблицу на лист 6 и переименуйте его в «Автофильтр №3. На листе «Автофильтр №1» отмените выборку.

Рисунок 5.58 – Пользовательский фильтр

6. В столбце «Фамилия» выберите в списке фильтрации «Условие» и задайте условие на отбор всех сотрудников, чья фамилия начинается на «Б» (рис.5.58).

7. Скопируйте отфильтрованный список на лист 7 переименуйте его в «Автофильтр №4».

8. На листе «Автофильтр №1» для графы «Фамилия» задайте «Все», а в графе «Оклад» задайте «Первые 10…» где в диалоговом окне введите «Показать 5 наибольших элементов списка».

9. Сохраните файл.

Задание 3. Выполните отбор записей, из списка используя команду Расширенный фильтр.

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

1. Перейдите на лист 8 и переименуйте его в «Расширенный фильтр».

2. Скопируйте на этот лист таблицу из предыдущего задания (табл. 5.5), вставьте ее, начиная со строки 7. Первые 6 строк отводятся для задания условий.

3. Создадим диапазон условий. Предположим, нам требуется отобрать фамилии сотрудников, которые получают больше 5000 руб. Или чей возраст превышает 50 лет. Заполните условия, как показано на рисунке 5.59.

Рисунок 5.59 – Условия для расширенного фильтра

4. Выполните команду Данные Þ Дополнительно . Заполните диалоговое окно следующим образом (рис. 5.60):

Рисунок 5.60 – Окно параметров расширенного фильтра

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

5. Предположим нам требуется вывести только тех сотрудников, фамилии которых начинаются с букв А, Г или Н. Заполните диапазон условий (рисунок 5.61).

Рисунок 5.61 – Условия для расширенного фильтра

6. Выполните команду ДанныеÞДополнительно и заполните диалоговое окно (рисунок 5.62).

Рисунок 5.62 – Окно параметров расширенного фильтра

Просмотрите результаты отбора записей.

1. Выведите список всех сотрудников, заработная плата которых больше среднего уровня. Перед созданием этого фильтра введите в ячейку H2 формулу =СРЗНАЧ(F8:F20) для вычисления среднего оклада.

2. Затем в ячейку А2 вводим вычисляемое условие =F8>$H$2, ссылающееся на ячейку H2 (рисунки 5.63 и 5.64).

Рисунок 5.63 – Условия для расширенного фильтра

Рисунок 5.64 – Параметры расширенного фильтра

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

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

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

Фильтрация данных в электронных таблицах может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.

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

1) установить курсор внутри таблицы;

2) ввести команду меню ДАННЫЕÞФильтрÞАвтофильтр;

3) щелчком мыши по кнопке со стрелкой раскрыть список столбца, по которому будет производиться выборка;

4) указать требуемые значения или выбрать строку "условие" и задать критерии выборки в диалоговом окне Пользовательский автофильтр.

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

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

· значение, которое может выбираться из списка или содержать шаблонные символы подстановки. В качестве символов подстановки используются звездочка * - для указания произвольного количества символов или вопросительный знак? - для замены одного символа;

· оператор отношения (сравнения). При задании критериев выборки могут использоваться следующие операторы сравнения:

Равно <> Не равно

< Меньше < = Меньше или равно

> Больше > = Больше или равно

Для восстановления всех строк исходной таблицы нужно щелкнуть мышью по кнопке со стрелкой (синего цвета) и в раскрывшемся списке выбрать строку "все" или выполнить команду ДАННЫЕÞФильтрсÞОтобразить все.

Для отмены режима фильтрации нужно установить курсор внутри таблицы и снова ввести команду меню ДАННЫЕÞФильтр ÞАвтофильтр (убрать переключатель).

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

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

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

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

Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню ДАННЫЕÞФильтрÞРасширенный фильтр.

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

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

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

Если критерии отбора (условия фильтрации) вводятся в одной строке для разных столбцов, то они считаются связанными условием "И". Если критерии отбора записываются в разных строках, то они считаются связанными условием "ИЛИ".

После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕÞФильтрÞРасширенный фильтр и в диалоговом окне Расширенный фильтр указывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазона условий.

Записи можно фильтровать на месте или одновременно с выполнением фильтрации копировать в указанную область на текущем рабочем листе.

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

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

Общие сведения

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

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

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

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

Рис. 3.37. Диалоговое окно «Наложение условия по списку»

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

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

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

Пример. Для Кода предмета можно сформировать условия:>=п* - отобрать все записи, которые содержат код предмета, начинающийся с буквы п; >= п1 И <=п2 - отобрать все записи, которые содержат коды предметов п1 и п2; <>п1 - отобрать все записи, которые не содержат кода предмета п1. Можно задать условия отбора для нескольких столбцов независимо друг от друга, .фильтрация записей выполняется по всем условиям одновременно. Все записи, не прошедшие через фильтр, будут скрыты. Отфильтрованные записи можно выделить и скопировать в другое место, удалить. Отмена результата фильтрации и возврат к исходному состоянию списка производятся повторным вводом команды Данные, Автофильтр.

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

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

критерий сравнения;
вычисляемый критерий.

Обычно критерий фильтрации формируется в нескольких столбцах, и тогда его называют множественным критерием. Важной особенностью этого режима является необходимостьформирования заранее, до выполнения самой команды фильтрации, специального блока (области) для задания сложных поисковых условий, называемых областью критерия (диапазоном условия). Технология использования расширенного фильтра состоит из двух этапов:этап 1 - формирование области критериев поиска;этап 2 - фильтрация записей списка.Э т а п 1. Формирование диапазона условий для расширенного фильтра. Область критериев поиска содержит строку имен столбцов и произвольное число строк для задания поисковых условий. Рекомендуется скопировать первую строку с именами полей из области списка в область, где будет формироваться критерий отбора записей (на тот же или другой лист, в другую рабочую книгу). Далее ненужные имена столбцов из диапазона условий можно удалить. Критерий сравнения формируется при соблюдении следующих требований:

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

Правила формирования множественного критерия: 1. Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И.2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.Пример. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами: 1-й способ. Множественный критерий сравнения - все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка- оператор сравнения со значением константы. 3-й способ. Множественный критерий сравнения - условия (точные значения полей) записаны в двух строках, связка ИЛИ.

Номер группы	Код предмета	Оценка
133	п1	4
133	п1	5

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

Номер группы	Оценка1
133	=ИЛИ(G2<=CP3HAЧ($G$2:$G$I7);G2=5)

В области критериев присутствуют столбцы с заголовками: Номер группы.Оценка1. Последовательность действий по созданию данного вычисляемого критерия:

присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;
установить курсор в ячейку ввода;
вызвать Мастер функций - команда Вставка, Функция, выбор категории -Логические и выбор функции - ИЛИ;
ввод параметров функции ИЛИ:

Логическое!: G2<=CP3HAЧ($G$2:$G$17)(при вводе формулы использовать курсорный указатель на ячейки таблицы, вызов встроенной функции СРЗНАЧ, указание на абсолютные ссылки с помощью клавиши )Логическое2: G2=5После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ - результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому: Э т а п 2 .Фильтрация записей расширенным фильтром. После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 3.39). Фильтровать записи списка можно на месте либо копировать в указанную область на текущем рабочем листе. Для копии на другой лист или книгу следует установить курсор по месту копии, а затем выполнять команду фильтрации, указывая соответствующие исходный диапазон и диапазон условий.Исходный диапазон и диапазон условий включают все строки, в том числе и строку наименования столбцов. Если предполагается копирование результата в другое место, указывается левая верхняя ячейка области. Переключатель Только уникальные записи позволяет исключить дублирование записей.

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

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

ППП Excel 97 позволяет работать с отдельными записями списка с помощью экранной формы (рис.3,40). Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка,При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей - названия столбцов списка.Для просмотра записей используется полоса прокрутки либо кнопки <Назад> или <Далее>, выводится индикатор номера записи. При просмотре записей возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи с помощью кнопки <Вернуть> можно отменить.Для создания новой записи нажимаете) кнопка <Добавить>, выполняется заполнение пустых полей экранной формы; для переходи между полями формы используются курсор мыши либо клавиша <ТаЬ>. При повторном нажатии кнопки <Добавить> сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка <Удалить>, Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.С помощью экранной формы задаются критерии сравнения. Для этого нажимаете» кнопка <Критерии>, форма очищается для т да условий поиска в полях формы с помои кнопки <0чистить>, а название кнопки заменяется на название <Правка>. По ввода критериев сравнения нажимаются кнопки <Назад> или <Далее> для просмотра отфильтрованных записей в нужном направлении. 1И просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка <Правка>, для выхода из формы - кнопка <3акрыть>.

Рис.3.40. Экранная форма для работы со списком записей

ЗАДАНИЕ 1

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

Проведите подготовительную работу - переименуйте новый лист на Автофильтр (см рис, 3.35).

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

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

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

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

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

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

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

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

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

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

4. Выберите из списка данные, используя критерий - для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь аналогичной п. 3 технологией фильтрации5.Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные» Фильтр, Автофильтр.6.Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

ЗАДАНИЕ 2

Выберите данные из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию. Для этого:1.Проведите подготовительную работу - переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных {см. рис.3.35).2. Скопируйте имена полей списка в другую область на том же листе.3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов! студентами группы 133 по предмету п1 на оценки 4 или 5.4.Произведите фильтрацию записей на том же листе.5.Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.6.Сформируйте в области условий отбора Вычисляемый критерий - для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий - л; результат отбора поместите на новый рабочий лист.7.Произведите фильтрацию записей на новом листе.8.Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.

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

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

переименуйте Лист4 - Расширенный фильтр
Расширенный фильтр.

Этап 1. Формирование диапазона условий по типу Критерий сравнения 2. Скопируйте все имена полей списка (см. рис. 3.35) в другую область на том же листе например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 - имена полей области критерия, J2:О5 - область значений критерия.3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 133 по предметуп1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

в столбец Номер группы - точное значение - 133;
в столбец Код предмета - точное значения - п1;
в столбец Оценка -условие->3

Этап 2. Фильтрация записей расширенным фильтром. 4. Произведите фильтрацию записей на том же листе:

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

Скопировать результат в другое место: установите флажокИсходный диапазон: A1:G17 Диапазон условия: J1:O5Поместить результат в диапазон: J6

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

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе, соблюдая технологию п.З и п.4.Этап 1. Формирование диапазона условий по типу Вычисляемый критерий. 6. Сформируйте в области условий отбора Вычисляемый критерий - для каждого преподавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий - л; результат отбора поместите на новый рабочий лист. Для этого:

в столбец Вид занятия введите точное значения - букву л;
переименуйте в области критерия столбец Оценка, например, на имя Оценка 2:
в столбец Оценка 1 введитевычисляемый критерий, например, вида

=G2>CP3HAЧ($G$2:$G$17)где G2 - адрес первой клетки с оценкой в исходном списке, $G$2: $G$I7 - блок ячеек с оценками, СРЗНАЧ - функция вычисления среднего значения.Этап 2. Фильтрация записей расширенным фильтром. 7. Произведите фильтрацию записей на новом листе;

установите курсор в область списка (базы данных);
выполните команду Данные, Фильтр, Расширенный фильтр;
в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например:

Скопировать результат в другое место: установите флажокИсходный диапазон: A1:G17Диапазон условия: Л:05Поместить результат в диапазон: перейдите на новый лист и щелкните мышью в любой ячейке

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

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

ЗАДАНИЕ 3

Используя Форму, выберите данные из списка. 1. Проведите подготовительную работу - переименуйте новый лист на Форма и скопируйте на него исходную базу данных (см. рис.3,35).2. Просмотрите записи списка с помощью формы данных, добавьте новые.3. Сформируйтеусловие отбора с помощью формы данных - для преподавателя выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий - л.4. Просмотрите отобранные записи.5. Сформируйте собственные условия отбора записей и просмотрите их,

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

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

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

2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопки <Назад> и <Далее>. С помощью кнопки <Добавить> добавьте новые записи.3. Сформируйте условие отбора - для преподавателя - а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий - л. Для этого:

нажмите кнопку <Критерии>, название которой поменяется на <Правка>;
в пустых строках имен полей списка введите критерии:
в строку Таб № препод. введите а1
в строку Вид занятия введите л;
в строку Оценка введите условие > 2

4. Просмотрите отобранные записи, нажимая на кнопку <Назад> или <Далее>.5. Аналогично сформируйте собственные условия отбора записей и просмотрите их.