Вычисление промежуточных итогов

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

Таблица 7

 

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

 

Для выполнения задания скопируйте интервал ячеек A1:E13 таблицы с листа Итоги на новый лист Подведение итогов. Рабочим листом при выполнении первого задания будет лист с именем Подведение итогов.

n Для решения задачи необходимо выполнить сортировку данных таблицы листа Подведение итогов по фамилиям продавцов.

n Выделить интервал ячеек A3:E13.

n Выберите вкладку Данные в группе Структура кнопку Промежуточные итоги.

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

n Из поля списка Операция следует выбрать функцию Сумма.

n Необходимо указать столбец, ячейки которого используются для вычисления итогов. Маркируйте в поле Добавить итоги по элемент Оборот и исключите любые другие, если они выделены. Выполните щелчок на кнопке OK.

В результате выполнения функции таблица будет дополнена строками, в которых отобразятся итоги для каждого продавца (группы данных) отдельно. В последней из вставленных в таблицу строк содержится информация об общем итоге для всех продавцов (см. рис. 3).

Рис.3. Созданная итоговая таблица

При вычислении промежуточных итогов таблица структурируется (посмотрите на экран слева). С помощью уровней структуры можно обеспечить лучший визуальный контроль данных. Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке для второго уровня структуры, вследствие этого данные третьего уровня (отдельные значения будут скрыты). Чтобы снова вывести на экран отдельные значения, необходимо выполнить щелчок на кнопке третьего уровня.

Дополним таблицу еще одним показателем: количеством автомобилей, проданных конкретным продавцом.

Для этого следует повторно открыть диалоговое окно Промежуточные итоги.

В поле При каждом изменении в уже представлен элемент Продавец. Чтобы определить количество проданных автомобилей для каждого продавца, выберите в списке Операция элемент Количество значений. В качестве основы для подведения итогов в последнем поле может быть указан любой столбец, поскольку данные уже отсортированы по группам и необходимо определить всего лишь количество строк в группе. Выделите, например, в списке Добавить итоги по элемент Марка. Для того чтобы итоги по обоим критериям были представлены в таблице, перед нажатием кнопки OK следует отменить опцию Заменить текущие итоги.

Чтобы удалить строки с итогами, следует использовать кнопку Убрать все в диалоговом окне Промежуточные итоги.

Построение сводной таблицы

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

В качестве примера будем использовать данные табл. 7, которые находятся на листе с именем Итоги.

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

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

Создание сводной таблицы. Создание и обработка сводных таблиц осуществляется с помощью программы Мастер сводных таблиц. Для этого выделите на листе Итоги интервал ячеек A3:E13.

n Запустите программу Мастер сводных таблиц выбрав вкладку Вставка в группе щелкните на кнопку Сводная таблица. На экране появится первое окно Создание сводной таблицы. В этом окне следует выбрать источник данных для сводной таблицы, если они ранее не были выделены. Следует выбрать куда поместить отчет сводной таблицы (укажите На новый лист).

n В следующем окне следует определить структуру будущей сводной таблицы. В центре диалогового окна представлена так называемая область сведения, которая подразделяется на области полей страниц,строк, столбцов, и область данных. Найденные в источнике данных поля представлены справа. Каждое поле данных с помощью операции перетаскивания можно поместить в область сведения (сводную таблицу). Поместим поле Продавец в область полей страниц. Таким образом, мы задали представление данных для каждого продавца на «отдельной странице». Имена продавцов будут содержаться в поле списка. Для отображения на экране данных для конкретного продавца следует выбрать фамилию продавца из списка.

n Поместите поля Дата и Марка в область строк, а поле Год выпуска - в область столбцов, поле Оборот в область данных. Таким образом, на экране мы имеем сводную таблицу (смотри рис.4). Назовите этот лист Меню_сводн._табл.

Сохраните файл на диске.

Рис 4. Созданная сводная таблица

Для группировки отдельных элементов в качестве первого критерия используется дата продажи (т.е. данные о продажах за один день объединены в группу, и вычислена сумма оборота за день). Промежуточные итоги представлены под отдельными значениями группы.