Методические указания. Создание сводных многоуровневых таблиц

Создание сводных многоуровневых таблиц

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

Если необходимо создать многоуровневую таблицу с вычислением промежуточных итогов, Excel предоставляет функцию автоматического вычисления итогов. Причем, имеется возможность «вложения» итогов для групп, находящихся внутри больших групп. Например, если требуется подсчитать объемы продаж по регионам, а внутри этих групп по видам продукции.

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

Технология подведения промежуточных итогов:

1. Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог.

2. Укажите ячейку в этом списке.

3. Выберите команду Итоги меню Данные. Появится окно диалога (рис. 2).

Рис. 2 - Вид окна Промежуточные итоги

4. В поле списка При каждом изменении в укажите столбец, содержащий группы, по которым необходимо подвести итоги. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

5. Выберите функцию, необходимую для подведения итогов, из списка Операция (по умолчанию – СУММ).

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

В результате выполнения команды таблица будет дополнена строками с итогами по каждому виду продукции отдельно.

Чтобы «вложить» или вставить итог для групп, находящихся внутри уже существующих групп выполните следующие действия:

1. Отсортируйте список по двум или более столбцам, для которых необходимо подвести итог. Например, чтобы определить объем продаж по регионам и по видам продукции внутри каждого региона, в первую очередь следует отсортировать список по столбцу «регионы», а затем – по столбцу «вид продукции». При подведении промежуточных итогов, значения итогов по видам продукции вкладываются в итоги по регионам.

2. Вставьте автоматические итоги для первого столбца, содержащего группы суммируемых данных. Этот столбец при сортировке списка должен быть указан в поле Сортировать по.

3. После создания автоматических итогов в первом столбце, повторите процедуру для следующего столбца.

4. Выделите ячейку в списке.

5. Выберите команду Итоги в меню Данные. Затем укажите из списка При каждом изменении в столбец, по которому необходимо подвести итоги. Снимите флажок «Заменить текущие итоги» инажмите кнопку OK.

Лабораторная работа 2. Создание таблиц с использованием технологии

подведения промежуточных итогов.

Содержание задания: на основании данных, приведенных в таблице 4, рассчитайте:

- объем продаж за период по каждому наименованию продукции;

- размер вознаграждения за период по каждому продавцу (комиссионные от суммы выручки);

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

Таблица 4 – Журнал о продажах продукции

Размер коммисионных	5 %
Дата	Наименование	Продавец	Количество	Цена	Сумма	Размер вознаграждения
2 апр.	Optima P6	Долгов П.П.
2 апр.	Action K7	Петров И.И.
3 апр.	IDE	Борков Л.И.
3 апр	DVD	Петров И.И.
4апр.	Keyboard	Долгов П.П.
4 апр.	Keyboard	Петров И.И.
4 апр.	Optima P6	Петров И.И.
5 апр.	DVD	Борков Л.И.
5 апр.	Mouse	Петров И.И.
6 апр.	Action P6	Борков Л.И.
6 апр.	Optima P6	Долгов П.П.
6 апр.	Keyboard	Долгов П.П.
7 апр.	Action K7	Петров И.И.
7 апр.	IDE	Петров И.И.
7 апр.	Action P6	Долгов П.П.

1. Создайте в своем каталоге рабочую книгу с именем «Продажа».

2. На первом листе создайте таблицу 4, введите в нее исходные данные и расчетные формулы. Переименуйте лист в Журнал продаж.

3. Скопируйте Журнал на второй лист и отсортируйте по графе «Наименование». Подведите итоги по графам «Количество» и «Сумма», используя опцию «ИТОГИ…» из меню Данные. Переименуйте лист в Отчет по продукции.

4. Постройте диаграмму, характеризующую структуру выручки.

5. Скопируйте Журнал на третий лист и отсортируйте по графе «Продавец». Подведите итоги по графам «Сумма» и «Комиссионные», используя опцию «ИТОГИ…» из меню Данные. Переименуйте лист в Отчет по продавцам. Создайте диаграмму, отражающую соотношение полученного продавцами вознаграждения.

6. Скопируйте Журнал на четвертый лист и отсортируйте по графам «Регион» и «Наименование». Подведите итоги по графам «Количество» и «Сумма», используя опцию «ИТОГИ…» из меню Данные. Переименуйте лист в Отчет по регионам. Создайте диаграмму, отражающую соотношение продаж продукции по регионам.

Дата добавления: 2014-10-31