Практическая работа № 8 Тема: Создание сводной таблицы

Цель: Научится работать с макросами в MS Excel 2007

Задание:

1. Составить сводную таблицу о продаже товаров различными продавцами за три месяца.

2. Выполнить задание из [3]: практикум № 51 страница 325.

Сводная таблица - это таблица, которая используется для быстрого итогов или объединения больших объемов данных. Меняя строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детально данные области. Другими словами эти таблицы позволяют объединить данные с разных источников, таких как базы данных, таблицы, а так же внешних источников , например, Интернет. Рассмотрим создание сводной таблицы на примере. Пусть имеются данные о продажах товаров различными продавцами за три месяца, хранящиеся трёх таблицах (для каждого месяца отдельно).

Необходимо для удобства использования объединить эти три таблицы в одну интерактивную.

Ход работы:

1. Запустить табличный процессор MS Ехсеl:

Пуск -> Программы -> Microsoft Ехсе1.

Заранее сохранить файл как «Сводная таблица».

2. Подготовить рабочую область, для этого переименовать листы:

• «Лист1» - «Февраль»;

• «Лист2» - «Март»;

• «ЛистЗ» - «Апрель».

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

4. Заполняем лист «Февраль»:

• в ячейку А1 вводим «Февраль»;

• в ячейках с В1 по Е1 (В1:Е1) вводим фамилии продавцов;

• в ячейках с А2 по А4 (А2:А4) вводим названия отделов;

• в ячейках с В2 по Е4 (В2:Е4) указываем сумму прихода от каждого продавца.

• придаём таблице законченный вид. Для этого в системном меню выбираем Формат -> Автоформат.

Из списка форматов выбираем необходимый формат.

5. Аналогично пункту 4 создать таблицы на листах «Март» и «Апрель», лишь изменяя месяц в ячейке А1 и суммы приходов от каждого продавца.

6. Начать запись макроса. Для этого в системном меню выбираем Сервис -> Макрос -> Начать запись... На экране появится окно «Запись макроса»

• вводим имя макроса «Сводная_таблица»;

- нажимаем кнопку «ОК»;

• на экране появляется панель инструментов «Остановить запись»

7. Создать сводную таблицу. Для этого в системном меню выбираем Данные -> Сводная таблица.

Открывается окно Мастера сводных таблиц. На первом шаге необходимо указать источник данных сводной таблицы. Для этого установить радиокнопку на пункт «в нескольких диапазонах консолидации», т.е. сводная таблица будет формироваться по нескольким листам . После чего нажать кнопку «Далее».

На шаге 2а необходимо указать, как следует объединять диапазоны в одну сводную таблицу. Для этого установить радиокнопку на пункт «создать одно поле страницы», т.е. создать собственные поля страницы для просмотра полных или частичных консолидаций.

На шаге 2б указать диапазон, содержащий данные для сводной таблицы. Для этого в строке «Диапазон» нужно ввести его либо вручную (Февраль!$А$1 :$Е$4 -> Добавить, Март!$А$1:$Е$4 -> Добавить, Апрель!$А$1:$Е$4 -> Добавить ) либо использовать мышь. Тогда следует выбрать первый лист («Февраль»), выделить таблицу с левого верхнего угла до правого нижнего и нажать кнопку «Добавить». Аналогично поступить и с таблицами на двух оставшихся листах. После чего нажать кнопку «Далее».

На шаге 3, при нажатии кнопки «Макет...» определяется вид сводной таблицы. Для алгоритма суммирования следует поменять местами кнопки «Строка» и «Страница». Нажать кнопку «ОК». Возвратившись на шаг 3, указать местоположение сводной таблицы, т.е. установить радиокнопку на пункт «Новый лист». После чего нажать кнопку «Готово».

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

8. Придать таблице завершённый вид:

• выделить ячейку А1 (кнопка «Строка») и ввести новое содержание – «Отдел»;

• выделить ячейку В3 (кнопка «Столбец») и ввести новое содержание – «Продавец»;

• выделить ячейку А4 (кнопка «Страница 1») и ввести новое содержание – «Месяц»;

• выделить ячейку А5 (ячейка «Объект 1») и ввести новое значение – «Февраль»;

• выделить ячейку А6 (ячейка «Объект 2») и ввести новое значение - «Март»;

• выделить ячейку А7 (ячейка «Объект 3») и ввести новое содержание - «Апрель»;

• переименовать новый лист в «Сводная таблица».

9. Остановить запись макроса. Для этого на панели инструментов «Остановка записи» нажимаем кнопку «Стоп».

10. Запуск макроса «Сводная таблица» осуществляется из пункта системного меню Сервис -> Макрос -> Макросы...

11. Текст макроса можно просмотреть с помощью редактора VBA. Для этого необходимо выбрать пункт системного меню Сервис -> Макрос -> РедакторVisual Basic. В исходном тексте макроса, в строках помеченных звёздочкой, будет стоять «Лист4»,который необходимо изменить на «Лист1» для дальнейшего удобства запуска.