Пошаговое выполнение работы

Практическая работа № 16.

 

Тема: Создание сводных таблиц в MS Excel

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

Теоретические сведения

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

Сводная таблица может быть создана на основании данных находящихся:

- в списке или базе данных Microsoft Excel;

- во внешнем источнике данных;

-

в нескольких диапазонах консолидации;

- в другой сводной таблице.

Рис.1 Макет сводной таблицы

Каждая сводная таблица состоит из 4 областей(рис.1): страница, строка, столбец, данные.

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

- Cтрока. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков строк в сводной таблице. Если в эту область помещено одно поле, то количество строк в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.

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

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

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

Постановка задачи

Задание 1. На основании следующей таблицы:

Менеджер	Месяц	Продукция	Доход	Расход	Прибыль	Регион
Иванов	январь	мясо	100,00	50,00		Страны СНГ
Иванов	февраль	мясо	100,00	50,00		Россия
Иванов	февраль	мясо	100,00	50,00		Россия
Иванов	апрель	мясо	100,00	50,00		Россия
Иванов	апрель	мясо	100,00	50,00		Россия
Петров	январь	мясо	100,00	50,00		Страны СНГ
Петров	февраль	мясо	100,00	50,00		Страны СНГ
Петров	февраль	мясо	100,00	50,00		Страны СНГ
Петров	апрель	мясо	100,00	50,00		Страны СНГ
Петров	апрель	мясо	100,00	50,00		Страны СНГ
Сидоров	май	рыба	100,00	50,00		Страны СНГ
Сидоров	январь	рыба	100,00	50,00		Россия
Иванов	февраль	рыба	100,00	50,00		Россия
Иванов	март	молоко	200,00	20,00		Россия
Петров	март	молоко	300,00	30,00		Страны СНГ
Сидоров	март	молоко	150,00	100,00		Страны СНГ

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

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

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

 

Пошаговое выполнение работы

1. Включите ПК

Задание 1.

2. Запустите Microsoft Excel

2.1 Запустите Microsoft Excel, используя команду Главного меню
После полной загрузки ОС запустите Microsoft Excel, щелкнув на кнопке Пуск и выбрав в главном меню команду Программы/Microsoft Office, Microsoft Office Excel 2003. В результате откроется окно приложения Microsoft Excel, в котором отображается пустая рабочая книга "Книга 1" с тремя рабочими листами.

2.2 Сохраните рабочую книгу Excel
Для сохранения рабочей книги в Excel выполните команду Файл/Сохранить, в окне диалога Сохранение документа введите имя файла: Расчет прибыли пр 16. Щелкните на кнопке ОК, сохранив рабочую книгу Excel в своей папке.

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

2.4 Рассчитайте значение поля «Прибыль», записав соответствующую формулу (от тдохода отнять расход).

3. Создайте сводную таблицу

3.1 Сделайте текущей любую ячейку созданной таблицы.

3.2 Выполните команду Данны е → Сводная таблица.

3.3 Установите флажок – В списке или базе данных Microsoft Excel.

3.4Укажите диапазон, содержащий построенную таблицу.

3.5 Укажите место размещения сводной таблицы и нажмите кнопку Макет.

3.6 Перетащите кнопки «Продукция» и «Менеджер» в область «Строка». При этом важен порядок перетаскивания – поле «Менеджер» будет вложенным по отношению к полю «Продукция». Затем в область «Столбец» перетащите кнопку «Месяц» и в область страниц – кнопку «Регион». В область данных перетащите кнопку «Прибыль».

Построенная сводная таблица будет иметь следующий вид:

Задание 2.

4. Скопируйте сводную таблицу задания 1 на другой лист или повторите процесс ее построения. Можно также создать копию листа со сводной таблицей.

4.1 Отметьте диапазон A4:C15. Для этого достаточно сделать текущей ячейку С4 (выделится столбец сводной таблицы за январь месяц) и нажав клавишу «Shift» щелкнуть по ячейке E4.

4.2 Выполните команды «Данные»→«Группа и структура»→«Группировать». В поле столбца появиться новое поле «Месяц 2» и в сводную таблицу добавится строка, в которой для выделенных трех столбцов присвоится название «Группа 1».

4.3 Выполните аналогичные действия для столбцов сводной таблицы за апрель и май месяцы. Для этих столбцов должно появится название «Группа 2».

4.4 Удалите поле месяц. Для этого захватите и перетащите его из области сводной таблицы.

4.5 Исправьте название «Месяц 2» на «Квартал», «Группа 1» – на «Первый», «Группа 2» – на «Второй».

4.6 Постройте диаграмму по полученной сводной таблице.

Полученная таблица должна иметь следующий вид:

Полученная диаграмма должна иметь следующий вид:

Задание 3.

Скопируйте первуюсводную таблицу на новый лист. Последовательно удаляя поля «Менеджер», «Месяц» и «Продукция» получите новые сводные таблицы. Поясните их смысл.