<<
>>

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

Расширенный фильтр открывается кнопкой Дополнительно в группе Сортировка и фильтр на вкладке Данные (рис. 7.1.9).

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

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

Используются два типа критериев фильтрации записей списка:

■ критерий сравнения;

■ вычисляемый критерий.

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

■ формирование диапазона условий;

■ фильтрация записей списка.

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

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

■ метки критериев должны точно совпадать с названиями столбцов исходного списка;

■ ниже меток располагаются критерии сравнения типа:

а) точного значения;

б) значения, формируемого с помощью операторов отношения;

в) шаблона значения, включающего символы *, И (ИЛИ), ?.

Правила формирования диапазона условий:

1. Если условия указываются в каждом столбце на одной строке, то они связаны условием И.

2. Если условия записаны в нескольких строках, то они связаны условием ИЛИ.

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

ИМЯФУНКЦИИ (Исходный_диапазон;

Название_поля; Диапазон_условий)

Например, функция БСЧЕТ, которая позволяет подсчитывать количество положительных оценок, полученных по экономике, имеет вид

БСЧЕТ(А1:Е21, "Балл", Н1:Л2),

где БСЧЕТ — имя функции; А1:Р21 — исходный диапазон таблицы (рис. 7.1.7); "Балл" — название поля, к которому применяется функция; Н1^2 — диапазоны условий.

Мастер функций в категории Работа с базой данных содержит следующие широко используемые функции: БДСУММ — суммирует значения всех элементов выбранного поля, удовлетворяющих определенному условию; текстовые поля пропускаются;

ДСРЗНАЧ — находит среднее значение всех элементов поля, удовлетворяющих определенному условию; текстовые поля пропускаются;

БДПРОИЗВЕД — перемножает значения элементов в столбце списка или базы данных, удовлетворяющие определенному условию; текстовые поля пропускаются;

ДМИН, ДМАКС — находят соответственно минимальное и максимальное значение элементов выбранного поля.

ЗАДАНИЕ 8

1. Выберите из списка, предложенного на рис. 7.1.7, данные, используя следующие критерии:

■ для преподавателя Савиной выбрать сведения о сдаче задания на положительную оценку, вид занятий — Л (лекция);

■ для группы БА1 получите сведения о сдаче задания по предмету «экономика» на оценки «удовлетворительно» и «хорошо».

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

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

Технология работы

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

2. Для выборки данных по какому-либо критерию в Excel удобно использовать Автофильтр.

Установите курсор в область списка и выполните команду Главная, Сортировка и фильтр. Вменю кнопки Сортировка и фильтр выберите пункт Фильтр.

Сформируйте условия отбора для преподавателя Савиной, которой сданы задания на положительную оценку, вид занятия — Л. Для этого:

■ в столбце Преподаватель нажмите кнопку 0, из списка условий отбора уберите галочку напротив Выделить все, но поставьте ее напротив Савина;

■ в столбце Балл нажмите кнопку 0, из списка выберите Числовой фильтр, больше, откроется окно «Пользовательский автофильтр», в левом поле которого установлено «больше», а в правом наберите 52, нажмите О К;

■ в столбце Вид занятия нажмите кнопку 0, из списка условий отбора выберите вид занятия — Л.

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

3. Самостоятельно получите сведения о сдаче задания по предмету Экономика на оценки «удовлетворительно» и «хорошо» в группе БА1, используя технологию работы из п. 2.

4.Выделите блок ячеек списка, начиная от имен по- леи и вниз до конца записей таблицы, и скопируйте его на Лист4. Переименуйте Лист4 на Расширенный фильтр.

Сформируйте следующие условия отбора: «вывести записи, в которых балл по лабораторным (Л) или практическим (Пр) занятиям выше 52».

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

В расширенном фильтре условие отбора или критерий, который представляет собой названия столбцов и условие, требуется вынести отдельно от таблицы, для этого скопируйте названия столбцов «Вид занятия» и «Балл» в другую область на том же листе:

■ выделите ячейку Б1 (со словами « Вид занятия ») и скопируйте ее в буфер обмена, затем выделите ячейку Н1 и вставьте в нее содержимое буфера обмена;

■ аналогичным образом содержимое ячейки П скопируйте в ячейку II;

■ в ячейку Н2 запишите Л, а в ячейку 12 запишите >52;

■ в ячейку НЗ запишите Пр, а в ячейку 13 запишите >52.

Проведите фильтрацию данных исходной таблицы:

■ выделите любую ячейку внутри исходной таблицы;

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

■ в окне установите переключатель Фильтровать список на месте; —

■ в поле Исходный диапазон нажмите кнопку ¿ЯЦ, вы

делите список (ячейки А1:Г21) и щелкните кнопку

на панели;

■ аналогичным образом поступите в поле Диапазон условий, выделяя блок Н1:13;

■ в окне диалога щелкните кнопку ОК;

5. Самостоятельно с помощью расширенного фильтра выведите записи таблицы, в которых в группе БА1 по информатике оценка выше 93.

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

■ выделите ячейку Н1 и введите диапазон критерия в ячейках Н1:Л2 в соответствии с таблицей

Н I и
1 Номер группы Предмет Бапп
2 БА1 Экономика > 79

■ выделите ячейку Е22 и вызовите Мастер функций;

■ в списке Категория выделите опцию Работа с базой данных, а в списке Функции — ДСРЗНАЧ и нажмите кнопку ОК. Функция ДСРЗНАЧ находит среднее значение всех элементов поля, удовлетворяющих определенному условию;

■ в поле Диапазон базы данных введите диапазон списка А1:¥21;

■ в поле Название поля введите адрес ячейки Е1, в которой находится заголовок «Балл»;

■ в поле Критерий введите диапазон условий Н1:Л2;

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

7. Самостоятельно в ячейке Е23 подсчитайте суммарный балл, полученный в группе ЭУ2 у преподавателя Ершова. Воспользуйтесь функцией баз данных БДСУММ и сформи- руите диапазоны условии в соответствии с таблицей

Н 1
4 Номер группы Преподаватель
5 ЭУ2 Ершов

8. Самостоятельно подсчитайте количество лекций (Л), проведенных в группе ЭУ2. Для этого воспользуйтесь формулой массива БСЧЕТ, которая позволяет подсчитывать количество ячеек, удовлетворяющих критерию.

7.1.4.

<< | >>
Источник: Ю. И. КУДИНОВ, Ф.Ф. ПАЩЕНКО, А. Ю. КЕЛИНА. ПРАКТИКУМ ПО ОСНОВАМ СОВРЕМЕННОЙ ИНФОРМАТИКИ. 2011

Еще по теме Расширенный фильтр:

  1. 1.6. Вопросы-фильтры
  2. ВАРИКОЗНОЕ РАСШИРЕНИЕ ВЕН
  3. Расширение графического метода
  4. Самовоспитание как "расширение" сознания
  5. 3.1. РАСШИРЕНИЕ НЕЙРОЛОГИЧЕСКОГО КОНТАКТА
  6. 3.12.2. Техника расширенного восприятия
  7. Расширение внутреннего кругозора
  8. 7.2.2. Расширение полноты ответа
  9. 7.2.2. Расширение полноты ответа
  10. Метод расширения сознания
  11. Смените фильтры, смените ракурс
  12. 2. Расширение круга наследников по закону в российском наследственном праве
  13. 2. Расширение сети вольной русской прессы в 1860-е годы