Пример 4.4
С помощью команды Дополнительно отобрать из списка Контроль знаний записи о студентах группы ИМ-14012 с количеством условных единиц в поле Сумма МЛ, превышающем среднее арифметическое всех значений этого поля. Вывести в отфильтрованном списке все поля.
Поместите таблицу критериев над списком в диапазон ячеек B2:C3. Для этого:
в ячейку B2 таблицы критериев скопируйте заголовок поля Группа, а ячейку C2 оставьте пустой;
в ячейку B3 скопируйте из списка значение поля ИМ-14012;
в ячейку C3 введите вычисляемый критерий отбора – логическую формулу вида =N6>СРЗНАЧ($N$6:$N$72). В ячейке N6 находится первый элемент поля Сумма МЛ, который сравнивается со средним арифметическим всех элементов этого поля. Если в результате сравнения формула возвращает значение ИСТИНА, то первая запись попадает в отфильтрованную таблицу, в противном случае этого не происходит. Далее процесс сравнения продолжается, при этом программа автоматически меняет в формуле относительный адрес N6 на N7 и т. д.
Выполните команду Дополнительно. В диалоге команды:
проверьте правильность заполнения области Исходный диапазон;
введите ссылку на диапазон B2:C3 в область Диапазон условий;
установите переключатель Скопировать результат в другое место;
в области Поместить результат в диапазон укажите любую ячейку на свободном месте листа, например AD4.
Нажмите кнопку ОК.
Пример 4.5
С помощью команды Дополнительно отобрать из списка Контроль знаний записи о студентах групп ИМ-14011 и ИМ-14012. В отобранных записях значения в поле Сумма ИНФ должны быть больше 90. Вывести поля ФИО, Группа, Сумма МЛ. Результат фильтрации поместить в ячейки листа, расположенные вне списка (рис. 4.8).
Поместите таблицу критериев справа от списка в диапазон AD3:AE5. Из исходного списка в таблицу критериев скопируйте заголовок Группа и заголовок Сумма ИНФ. Под заголовками введите условия отбора.
|
|
Рис. 4.8. Таблица критериев и результат фильтрации списка
В таблице критериев присутствуют две пары, причем обе пары состоят из критерия по полю Группа и критерия по полю Сумма ИНФ. Критерии каждой пары должны быть связаны между собой с помощью логического оператора И, поэтому размещаются в одной строке. Сами пары критериев должны быть связаны между собой с помощью логического оператора ИЛИ, поэтому располагаются в разных строках (Группа ИМ-14011 и Сумма ИНФ>90 или Группа ИМ-14012 и Сумма ИНФ>90).
Скопируйте в ячейки диапазона АD7:АF7 заголовки тех полей списка, которые должны присутствовать в отфильтрованной таблице. Это поля: ФИО, Группа, Сумма МЛ.
В диалоге Расширенный фильтр выполните следующие действия:
в области Диапазон условий укажите диапазон AD3:AE5;
установите переключатель Скопировать результат в другое место;
в области Поместить результат в диапазон укажите диапазон АD7:АF7;
нажмите кнопку ОК.
4.1.3. Подведение промежуточных итогов
Подведение промежуточных итогов – это получение результатов вычислений по группам записей списка. Подведение итогов выполняется с помощью команды Промежуточные итоги на вкладке Данные (группа Структура). Команда Промежуточные итоги позволяет пользователю, не составляя формул и не преобразуя список, получить промежуточные и общие итоги в виде дополнительных строк списка, а также структурировать данный список.
Перед выполнением команды Промежуточные итоги необходимо определить “промежутки”, по которым будут подводиться итоги. Имеющийся список должен быть разбит на отдельные группы записей. На группы список можно разбить по любому полю, в котором есть повторяющиеся значения. Для этого выполняется сортировка списка по данному полю. В результате сортировки записи с одинаковыми значениями поля собираются в одну группу. Сколько разных значений присутствует в данном поле, столько групп будет обработано при выполнении команды Промежуточные итоги.
Список функций для подведения промежуточных и общих итогов приведен в табл. 4.1.
Таблица4.1