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

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

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

_ Скопируйте заголовки столбцов «I», «II», «III», «Итого» в соседние ячейки.

_ Т.к. по полям «I», «II», «III» необходимо провести фильтрацию по двум критериям, то добавьте дополнительные столбцы (см. рис. 8.4).

_ Задайте условия фильтрации: в первой строке в первом поле «I» задайте “>=17,5”, во втором поле «I» - “<=25”, в поле «Итого» “>75”; во второй строке в первом поле «II» задайте “>=17,5”, во втором поле «II» - “<=25”, в поле «Итого» “>75”; аналогично оформите третью строку. Т.о. получается логическое условие – (тройка по I контрольной работе И итоговый балл – хорошо или отлично) ИЛИ (тройка по II контрольной работе И итоговый балл – хорошо или отлично) ИЛИ (тройка по III контрольной работе И итоговый балл – хорошо или отлично).

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

_ Заполните поля (рис. 8.5).

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

_ Щелкните по кнопке ОК. В исходном списке скроются строки, не удовлетворяющие условиям критерия..

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

_ Командой Данные – Фильтр - Отобразить все верните список в исходное состояние.

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

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

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

_ Выделите диапазон условий и скопируйте его на новый лист.

_ Заполните окно команды Расширенный фильтр (рис. 8.6.).

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

Условия и результаты фильтрации для задания 6 приведены на рис. 8.7.

Задание 7. Подведите итоги: сколько в группе отличников (итоговый балл >= 91), хорошистов (балл >= 75 и < 91), троечников (балл >= 45 и < 75) и двоечников (балл < 45).

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

Формирование итогов

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

Перед использованием команды Итоги следует провести сортировку данных.

_ Добавьте после поля «Итоги» новое поле «Ученик». В ячейки поля введите формулу, позволяющую определить, кем – отличником, хорошистом, троечником или двоечником – является тот, или иной ученик (используйте функцию ЕСЛИ).

_ Отсортируйте весь список по убыванию итогового балла. Вторичную сортировку организуйте по полю «Фамилия» по возрастанию.

_ Выделите любую ячейку списка.

_ Дайте команду Итоги из меню Данные. По этой команде откроется диалоговое окно Промежуточные итоги (рис. 8.8). В нем задаются поле, при каждом изменении значения которого будут вычисляться итоговые значения (в нашем случае – «Ученик»), и операция (подсчет количества), которая будет применяться к значениям полей, отмеченных в списке Добавить итоги по («Ученик»).

Щелчок по кнопке ОК вставляет итоговые строки и структурирует список (фрагмент списка с итогами приведен на рисунке 8.9).

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

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

_ Щелкните по кнопке 1-го уровня. Рабочий лист отображает только общий итог по всем данным. Чтобы вернуть все записи списка щелкните либо кнопку «плюс» первого уровня, либо кнопку третьего уровня, отвечающего за все элементы базы данных.

_ Щелкните по кнопке второго уровня. Рабочий лист отображает только итоговые значения. Восстановите полный список.

_ Для приведения списка в исходное состояние, когда промежуточные итоги больше не нужны, дайте команду Данные, Итоги и, в открывшемся окне, щелкнуть по кнопке Убрать все.

Задания для самостоятельной работы:

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

2. Заполните таблицу, используя форму данных (не менее 20 строк).

3. Отсортируйте список в алфавитном порядке, используя меню Данные – Сортировка. Отсортируйте список по убыванию результирующего балла «Итого», при этом вторичная сортировка по убыванию должна идти по результату 1 работы, следующая – по результату 3 работы. Выбрать из списков всех абитуриентов с фамилиями, начинающимися с буквы «М».

4. Найдите всех слушателей с отчествами, начинающимися на букву «В», кто имеет тройки и двойки по 3 работе (<150 баллов) используя автофильтр.

5. Подведите итоги: сколько в группе отличников (итоговый балл >= 200), хорошистов (балл >= 150 и < 200), троечников (балл < 150).