Тема 10. Вычисление общих и промежуточных итогов

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

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

Предположим, в распоряжении исследователя находится список жителей района (рис. 1), который довольно обширен.

Рис. 1. Список жителей района

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

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

· Скопируем таблицу на новый лист с именем Итоги (таблицу можно получить из файла или создать самостоятельно).

· Отсортируем в таблице столбцы по двум параметрам «Образование», «Участие в работе общественных организаций», окно с заполненными параметрами сортировки, показано на рис. 2.

Рис. 2. Пример сортировки данных в таблице по двум параметрам

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

- Промежуточные итоги. Вызов функции осуществляется с вкладки «Данные», группа – «Структура». (Для Excel 2003 необходимо выделить одну из ячеек таблицы, выбрать в строке Меню-Данные-Итоги). В результате появится диалоговое окно, представленное на рис. 3.

Рис. 3. Диалоговое окно «Промежуточные итоги» с выбранными параметрами для примера

· В окне Промежуточные итоги в строке: «При каждом изменении в:» следует выбрать из списка и установить условие (в примере – Образование).

· В строке «Операция» установить – Количество.

· В строках: «Добавить итоги по:» следует пометить Образование и Участие в общественных организациях.

· Пометка Итоги под данными в окне «Промежуточные итоги» может быть снята, тогда итоги будут отображаться над данными. После того, как будет нажата кнопка ОК, в таблице появятся строки с результатами промежуточных итогов. Обратите внимание, что на листе Excel слева появятся значки , если на них нажать, то исходные данные будут свёрнуты.

На рис. 4 структура второго уровня с данными свернута, что позволяет увидеть окончательный результат. На основе таблицы с итоговыми данными удобно построить диаграмму.

Рис. 4. Лист Excel с резултатами промежуточных и общих итогов

Задание 2. Построить диаграмму по промежуточным итогам.

Выделите только строки с промежуточными итогами, и воспользуйтесь Мастером построения диаграмм, для окончательного выполнения работы (пример представлен на рис. 5).

Рис. 5. Пример построения диаграммы с промежуточными итогами.

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

Вопросы для самоконтроля

1. Чем отличаются возможности Excel при использовании функции Промежуточные итоги от выполнения вычислений в таблице с помощью встраивания условий и других функций?

2. Как заполнить окно: Промежуточные итоги, когда имеется таблица с данными?

3. Как можно увидеть отдельные группировки промежуточных итогов?

4. Какие правила целесообразно соблюдать при построении итогов?