Информационные системы и технологии в финансах. Практическое занятие. Фильтрация и сортировка данных

Цель: Освоить методику применения автофильтра и расширенного фильтра для извлечения данных из таблиц 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 году.

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

Порядок использования Расширенного фильтра.

Формулы в расширенном фильтре.

Применение автофильтра к нескольким столбцам

Как используется автофильтр для нахождения первых десяти элементов?

Как применяется автофильтр для задания более сложных условий отбора?

Использование символов шаблона в пользовательском фильтре?

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

Метки: