Цель: Освоить методику применения автофильтра и расширенного фильтра для извлечения данных из таблиц Excel.
План занятия:
Изучить на практике возможности команды "Автофильтр".
Отработать создание сложных условий отбора с помощью расширенного фильтра.
Решить самостоятельно задачи на фильтрацию и сортировку данных по предлагаемой таблице.
Порядок выполнения работы
Рассмотрим механизм реализации запросов к базе данных с помощью фильтра табличного процессора Microsoft Excel. Отфильтровать список – значит, скрыть все строки за исключением тех, которые удовлетворяют заданным условиям отбора. Excel предоставляет для этого две команды: Автофильтр, для простых условий отбора, и Расширенный Фильтр, для более сложных критериев. Фильтры могут быть использованы только для одного списка на листе.
Можно использовать автофильтр, чтобы найти заданное число (или заданный процент) наибольших или наименьших элементов в списке.
С помощью пользовательского автофильтра можно отфильтровать список по двум значениям в одном столбце или применить отличные от равенства операторы сравнения.
Можно использовать символ шаблона "*" в автофильтре, чтобы найти вхождение подстроки в заданный стоблец..
С помощью пользовательского автофильтра можно найти все текстовые значения в столбце, которые попадают в конкретный алфавитный диапазон.
Для удаления всех автофильтров и их кнопок необходимо убрать галочку рядом с командой Автофильтр. Для этого:
Если вы собираетесь использовать для задания условия отбора только один столбец, то можно вывести кнопку автофильтра лишь для него одного. Для этого необходимо выделить данный столбец. Затем в подменю Фильтр из меню Данные выбрать команду Автофильтр.
Если требуется наложить три и более условий, скопировать записи в другое место или отобрать данные на основе вычисленного значения, используется Расширенный фильтр.
Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки.
|
Кол-во |
Цена-у. е. |
|
>=4 |
|
|
>=850 |
Команда Расширенный фильтр, в отличие от команды Автофильтра, требует задания условий отбора строк в отдельном диапазоне рабочего листа.
Примеры условий отбора расширенного фильтра
|
Наименование |
|
Epson* |
|
Canon* |
|
Mustek* |
Пример 1. Для следующего диапазона условий будут отобраны строки, содержащие наименование оргтехники, начинающиеся с Epson, Canon, Mustek.
Пример 2. Для следующего диапазона условий будут отобраны строки, содержащие Ксерокс в столбце Категория, Canon * В столбце Наименование И имеющие цену больше 300 $.
|
Категория |
Наименование |
Цена-у. е. |
|
Ксерокс |
Canon* |
>300 |
Пример 3. Для следующего диапазона условий будут отобраны строки, удовлетворяющие одному из нескольких условий, наложенных на разные столбцы, содержащие либо Факс * в столбце Наименование, Либо Туби в столбце Диллер, Либо имеющие цену меньше 100.
|
Наименование |
Диллер |
Цена-у. е. |
|
Факс* |
|
|
|
|
Туби |
|
|
|
|
<100 |
Пример 4. Для следующего диапазона условий будут отобраны строки, содержащие Сканер в столбце Категория, Mustek * В столбце Наименование, Туби в столбце Диллер и имеющие цену меньше 120 $. А также строки, содержащие C* в столбце Наименование И имеющие цену меньше 300 $.
|
Категория |
Наименование |
Диллер |
Цена |
|
Сканер |
Mustek * |
Туби |
<120 |
|
|
C* |
|
<300 |
В условии фильтрации можно использовать возвращаемое формулой значение. При задании формул в условиях отбора не используйте в качестве заголовка условия заголовки столбцов списка. Заголовок вычисляемого условия может быть либо пустым, либо содержать текст, не повторяющий ни один из заголовков в списке. Даже, если заголовок условия является пустым, вы все равно должны включить его при задании диапазона условий в окне диалога Расширенный фильтр.
|
Вывести: Цены выше среднего |
|
=Е4>СРЗНАЧ($E$4:$E$24) |
Пример 5. Например, для следующего диапазона условий будут отображены строки, в которых цена оргтехники выше среднего.
Используемая в условии формула должна ссылаться либо на заголовок столбца, либо на соответствующее поле в первой записи. В приведенном примере G5 ссылается на соответствующее поле (столбец G) первой записи (строка 5) списка.
При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? Или #ЗНАЧ. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.
Примечание. Можно показать результат фильтрации, скрыв ненужные строки, установив переключатель Обработка в положение Фильтровать Список на месте. В этом случае необходимо, чтобы на листе было не менее трех пустых строк выше списка. Эти строки будут использованы в качестве диапазона условий отбора.
Задания для самостоятельной работы:
По предлагаемой таблице выполнить следующие задачи фильтрации:
Вывести на экран записи таблицы с датой размещения от 1 февраля 1997 г. по 1 марта 1997 г. и скопировать их на лист 2.
Вывести на экран заказы, отправленные в Берлин.
Вывести на экран заказы Инны Ясеневой, размещенные в 1996 году.
С помощью автофильтра отобразить все заказы, доставленные Ространсом в период с 1 апреля 1996 по 1 февраля 1997 г.
С помощью автофильтра отобразить все заказы, доставленные Почтой в Польшу.
С помощью автофильтра отобразить все заказы сотрудника Дарьи Вороновой, доставленные Ространсом в Германию.
С помощью автофильтра отобразить все заказы сотрудника Марии Беловой за период с 1 марта 1996года по 1 июля 1997 года.
С помощью автофильтра отобразить все заказы, пришедшие в Буэнос-Айрес.
Отобразить все заказы, стоимость доставки которых превышает 4 миллиона.
Вывести на экран записи таблицы с датой размещения от 1 декабря 1996 г. по 1 декабря 1998 г. и скопировать их на лист 2.
С помощью автофильтра отобразить все заказы сотрудника Андрея Кротова, доставленные в США или Францию в 1998 году.
Контрольные вопросы
Порядок использования Расширенного фильтра.
Формулы в расширенном фильтре.
Применение автофильтра к нескольким столбцам
Как используется автофильтр для нахождения первых десяти элементов?
Как применяется автофильтр для задания более сложных условий отбора?
Использование символов шаблона в пользовательском фильтре?
Отмена результатов фильтрации?