1.2 Использование промежуточных итогов для анализа списка
Команда Данные ► Итоги выбирается, когда список отсортирован по заданным критериям. Команда Данные ► Итоги добавляет строки промежуточных итогов для каждой группы элементов списка, а также создает общие итоги. При этом можно использовать различные функции для вычисления итогов (например, СУММ или СРЗНАЧ). При выводе промежуточных итогов Excel создает структуру списка. Чтобы вывести интересующий пользователя уровень детализации данных, нужно щелкнуть мышью на соответствующем символе структуры.
Для расчета количества человек в каждой группе следует установить следующие критерии (рис. 1):
Рисунок 1
Подсчитать средние оценки по физике в каждой группе.
Чтобы удалить промежуточные итоги и структуру, нужно использовать кнопку Убрать все диалогового окна Промежуточные итоги. Для применения нескольких итоговых функций достаточно повторить команду Итоги для новой итоговой функции и снять флажок Заменить текущие итоги диалогового окна Промежуточные итоги.
1.2 Применение фильтров
Фильтр - это средство для отбора записей в таблице по некоторому критерию. В Excel имеются два типа фильтров: автофильтр и расширенный фильтр. Автофильтр показывает записи, совпадающие с критериями фильтрации, и скрывает не совпадающие. Расширенный фильтр способен сформировать новую таблицу из отфильтрованных записей.
1.2.1 Автофильтр
Для применения автофильтра необходимо выделить любую клетку внутри фильтруемой таблицы и обратиться к меню Данные/Фильтр.../Автофильтр. После обращения в заголовке таблицы должны появиться кнопки для раскрытия списков. Нажатие любой кнопки приводит к раскрытию списка элементов соответствующего столбца таблицы. Выбранный элемент является критерием фильтрации. Строки таблицы, в которых элементы столбца не совпадают с критерием будут скрыты, причем за совпавшими сохраняются их прежние порядковые номера. Выбор второго критерия в другом списке приведет к дополнительной фильтрации записей и т.д. В качестве примера рассмотрим применение автофильтра для таблицы с итогами сессии. На рисунке 2 показаны результаты фильтрации по условию "Оценка по информатике"=5.
Рисунок 2
Для задания более сложного условия фильтрации необходимо в соответствующем раскрывающемся списке выбрать "[Условие...]" и сформулировать его в открывшемся окне "Пользовательский автофильтр". Окно содержит поля для ввода знаков логических отношений и метки логических операций И и ИЛИ. Например, для отбора записей, соответствующих студентам, получившим по информатике 4 или 5, следует выполнить настройки, как показано на рисунке 3.
Рисунок 3
Отменить результаты фильтрации можно через Данные/Фильтр... и убрать флажок с меню Автофильтр. Достоинство автофильтра в простоте его применения. Недостаток: в отсутствии возможности формулировать сложные условия, связывающие условия фильтрации в разных столбцах операцией ИЛИ.
Выбрать записи
об отличниках
о студентах 156 группы, обучающихся без троек
с фамилиями, начинающимися с буквы К
4 наихудшие оценки по математике.
После получения результатов работы автофильтра они могут быть обработаны с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может быть инициирована нажатием кнопки автосуммирования и имеет следующий синтаксис:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(N; диапазон), где N может принимать следующие значения:
1 - вычисление среднего значения;
2 - счет чисел;
3 - счет значений;
4 - вычисление максимального значения;
5 - вычисление минимального значения;
6 – вычисление произведения;
7, 8 - вычисление стандартных отклонений;
9 - вычисление суммы;
10, 11 - вычисление дисперсии.
Диапазон задает область применения функции. Например, для вычисления значения среднего балла по информатике в группе 155 необходимо вначале отфильтровать студентов со значением поля Группа=155, а затем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ для полученного диапазона значений поля Оценки по информатике и N=1.